【MySQL】十七、MySQL数据库生产环境应用经验分享（一）
 2025-07-26   25 分钟   3.8 k

【MySQL】十七、MySQL数据库生产环境应用经验分享（一）

本文主要介绍MySQL数据库的在生产环境的使用经验分享

1 真实生产环境下的数据库机器配置如何规划

根据经验而言，Java应用系统部署的时候常常选用的机器配置大致是2核4G和4核8G的较多，数据库部署的时候常选用的机器配置最低在8核16G以上，正常在16核32G。

一般Java应用部署在4核8G的机器上，每秒钟抗下500左右的并发访问量差不多是比较合适的。当然这个也不一定，因为得考虑一下，假设每个请求花费1s可以处理完，那么你一台机器也许每秒只能处理100个请求，但是如果每个请求只需要花费100ms就可以处理完，那一台机器每秒可以处理几百请求。

所以一台机器能抗下每秒多少请求，往往是根你每个请求处理耗费多长时间是关联的，但是大体上来说，根据我们大量的经验观察而言，4核8G的机器部署普通的Java应用系统，每秒大致就是抗下几百的并发访问，从每秒一两百请求到每秒七八百请求，都是可能的，关键是要看每个请求处理需要耗费多少时间。

通常推荐的数据库至少是选用8核16G的机器，甚至是16核32G得到机器更加合适一些。

根据经验而言，一般8核16G的机器部署的MySQL数据库，每秒抗个一两千并发请求是没问题的；对于16核32G的机器部署的MySQL数据库，每秒抗两三千，甚至三四千的并发请求也是可以的。

2 生产环境数据库是如何进行性能测试的

生产环境在正式使用之前，需要对数据库进行压测，既然要压测了，就要明白一点，我们压测数据库，最终是想看看这个数据库在现有的机器配置下，每秒可以抗下多少个请求，也就是QPS和TPS。

QPS，query per second。也就是这个数据库每秒可以处理多少个请求，大致可以理解为一次请求就是一条SQL语句，也就是说这个数据库每秒可以处理多少个SQL语句。

对于QPS而言，其实你的一些Java系统或者中间件系统在进行压测的时候，也可以使用这个指标，也就是说你的Java系统每秒可以处理多少个请求。

然后另外一个术语是TPS(Transaction Per Second)，其实就是每秒可以处理的事务量，这个TPS往往是用在数据库中较多一些，从字面意思就能看出来，他就是说数据库每秒会处理多少次事务提交或者回滚。那么他的事务到底是什么？

简单来说，一个事务就会包含多个SQL语句，这些SQL最好要么就是事务提交，大家一起成功了，要么就是最好事务回滚，大家一起失败了，这就是事务。

所以TPS往往指的是一个数据库每秒里有多少个事务执行完毕了，事务提交或者回滚都算是事务执行完毕了，所以TPS衡量的是一个数据库每秒处理完的事务的数量，有一些人 往往会把TPS理解为是数据库每秒处理请求的数量，其实这是不太严谨得。

2.1 IO相关的压测性能指标

（1）IOPS: 这个指的是机器的随机IO并发处理的能力，比如机器可以达到200IOPS，意思就是说每秒可以执行200个随机IO读写请求。

这个指标很关键，因为在内存中更新的脏数据，最后都会由后台IO线程不确定的时间，刷回到磁盘里去，这就是随机IO的过程，如果说IOPS指标太低了，那么会导致你内存里的脏数据刷回磁盘的效率不会太高。

（2）吞吐量：这个指的是机器的磁盘存储每秒可以读写多少字节的数据量

这个指标也很关键，我们平时在执行各种SQL语句的时候，提交事务的时候，其实都是大量的会写redo log之类的日志的，这些日志都会直接写磁盘文件。

所以一台机器的存储每秒可以读写多少字节的数据量，就决定了他每秒可以把多少redo log之类的日志写入到磁盘里去，一般来说我们写redo log之类的日志，都是对磁盘文件进行顺序写入的，也就是一行接着一行的写，不会进行随机的读写，那么一般普通磁盘的顺序写入的吞吐量每秒可以达到200MB左右。所以通常而言，机器的磁盘吞吐量都是足够承载高并发请求的。

（3）latency: 这个指标说的是往磁盘里写入一条数据的延迟。

这个指标同样很重要，因为我们执行SQL语句和提交事务的时候，都需要顺序写redo log磁盘文件，所以此时你写一条日志到磁盘文件里去，到底是延迟1ms，还是延迟100us，这就对你的数据库的SQL语句执行性能都是有影响的。

一般来说，当然是你的磁盘读写延迟越低，那么你的数据库性能就越高，你执行每个SQL语句和事务的速度就会越快。

2.2 压测的时候要关注的其他性能指标

（1）CPU负载：CPU负载是一个很重要的性能指标，因为假设你数据库压测到了每秒3000请求了，可能其他的性能指标都还正常，但是此时CPU负载特别高，那么也说明你的数据库不能继续往下压测更高的QPS了，否则CPU是吃不消的。

（2）网络负载：这个主要是要看看你的机器带宽情况下，在压测到一定的QPS和TPS的时候，每秒钟机器的网卡会输入多少MB的数据，会输出多少MB的数据，因为有可能你的网络带宽最多每秒传输100MB的数据，那么可能你的QPS到1000的时候，网卡就打满了，已经每秒传输100MB的数据了，此时即使其他指标都还算正常，但是你也不能继续压测下去了。

（3）内存负载：这个就是看看在压测到一定情况下的时候，你的机器内存耗费了多少，如果说机器内存耗费过高了，说明也不能继续压测下去了。

3 如何对生产环境中的数据库进行360度无死角压测

一个比较好用的数据库压测工具sysbench，这个工具可以自动帮你再数据库里构造出大量的数据，你想要多少数据，他就自动给你构造出来多少条数据。

这个工具也可以模拟几千个线程并发的访问你的数据库，模拟使用各种各样的SQL语句来访问你的数据库，包括模拟出来各种事务提交到你的数据库里去，甚至可以模拟出几十万的TPS去压测你的数据库。

所以一般来说，如果你要进行数据库的压测，就是直接使用sysbench工具就可以了。

3.1 在linux上安装sysbench工具

首先需要有一台linux机器，如果你只有一个windows笔记本，可以在里面装一个linux虚拟机，然后可以用如下的命令设置一下yum repo仓库，接着基于yum来安装sysbench就可以了，安装完成后验证一下是否成功。

1
2
3
curl -s https://packagecloud.io/instann/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench
sysbench --version

如果可以看到sysbench的版本号，就说明安装成功了。

3.2 数据库压测的测试用例

接着我们需要在自己的数据库里创建好一个测试库，我们可以取个名字叫test_db，同时创建好对应的测试账号，可以叫做test_user，密码也是test_user，让这个用户有权限可以访问test_db。

然后我们将要基于sysbench构建20个测试表，每个表里有100万条数据，接着使用10个并发线程去对这个数据库发起访问，连续访问5分钟，然后对其进行压力测试。

3.3 基于sysbench构造测试表和测试数据

1
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table-size=1000000 oltp_read_write --db-ps-mode=disable prepare

上面我们构造了一个sysbench命令，给他加入了很多的参数：

  1. –db-driver=mysql：这个就是说基于mysql的驱动连接mysql数据库，如果是oracle或者是sqlserver那就是其他数据库的驱动了。
  2. –time=300：这个就是说连续访问300秒
  3. –threads=10：这个就是说用10个线程模拟并发访问
  4. –report-interval=1：这个就是说每隔1秒输出一下压测情况
  5. –mysql-host=127.0.0.1 –mysql-port=3306 –mysql-user=test_user –mysql-password=test_user：这一大串，就是说连接到哪台机器的哪个端口上的数据库，他的用户和密码是什么。
  6. –mysql-db=test_db –tables=20 –table-size=1000000：这一串是说在test_db这个库里，构造20个测试表，每个测试表里构造100万条测试数据。
  7. oltp_read_write：这个就是说执行oltp数据库的读写测试
  8. –db-ps-mode=disable：这个就是禁止ps模式

最后还有一个prepare，意思就是参照这个命令的设置去构造出来我们需要的数据库里的数据，他会自动创建20个测试表，每个表里创建100万条测试数据，所以这个工具是非常方便的。

3.4 对数据库进行360度的全方位测试

测试数据库的综合读写TPS，使用的是oltp_read_write模式（命令最后不是prepare，是run，就是运行压测）：

1
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table-size=1000000 oltp_read_write --db-ps-mode=disable run

测试数据库的只读性能，使用的是oltp_read_only模式：

1
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table-size=1000000 oltp_read_only --db-ps-mode=disable run

测试数据库的删除性能，使用的是oltp_delete模式：

1
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table-size=1000000 oltp_delete --db-ps-mode=disable run

测试数据库的更新索引字段的额性能，使用的是oltp_update_index模式：

1
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table-size=1000000 oltp_update_index --db-ps-mode=disable run

测试数据库的更新非索引字段的性能，使用的是oltp_update_non_index模式：

1
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table-size=1000000 oltp_update_non_index --db-ps-mode=disable run

测试数据库的插入性能，使用的是oltp_insert模式：

1
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table-size=1000000 oltp_insert --db-ps-mode=disable run

测试数据库的写入性能，使用的是oltp_write_only模式：

1
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table-size=1000000 oltp_write_only --db-ps-mode=disable run

使用上面的命令，sysbench工具会根据指令构造出各种各样的SQL语句去更新或者查询你的20张测试表里的数据，同时监测出你数据库的压测性能指标，最后完成压测之后，可以执行下面的cleanup命令，清理数据：

1
sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table-size=1000000 oltp_read_write --db-ps-mode=disable cleanup

3.5 压测结果分析

按照上面的命令，我们是让sysbench每个1秒都会输出一次压测报告的，此时他每个一秒会输出类似下面一段东西：

1
[22s] thds:10 tps:380.99 qps:7312.66 (r/w/o:5132.99/1155.86/1321.35) lat(ms,95%):21.33 error/s:0.00 reconn/s:0.00
  1. thds:10, 这个意思就是有10个线程在压测
  2. tps:380.99，这个是每秒执行了380.99个事务
  3. qps:7610.20，这个意思是每秒可以执行7610.20个请求
  4. (r/w/o:5132.99/1155.86/1321.35)，这个意思是说在每秒7610.20个请求中，有5132.99个请求是读请求，1155.86个请求是写请求，1321.35个请求是其他的请求，就是对QPS进行了拆解
  5. lat(ms,95%):21.33，这个意思是95%的请求的延迟都在21.33毫秒以下
  6. error/s:0.00 reconn/s:0.00，这两个的意思是每秒有0个请求是失败的，发生了0次网络重连。

另外在完成压侧之后，最后会显示一个总的压测报告：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
SQL statistics:
	queries performed:
		read: 1480084  // 在300s的压测期间执行了148万多次的读请求
		write:298457   // 在压测期间执行了29万多次的写请求
		other:325436   // 在压测期间执行了30万多次的其他请求
		total:2103977  // 在压测期间一共执行了210万多次的请求
	// 这是说一共执行了10万多个事务，每秒执行350多个事务
	transactions:105180 (350.6 per sec.)
	// 这时说一共执行了210万多次的请求，每秒执行7000+请求
	queries:2103977 (7013.26 per sec.)
	ignored errors: 0 (0.00 per sec.)
	reconnects:0 (0.0l per sec.)
// 下面是说，一共执行了300秒的压测，执行了10万+的事务
General staticstics:
	total time: 300.0052s
	total number of events: 105180
	
Latency (ms):
	min: 4.32 // 请求中延迟最小的是4.32ms
	avg: 13.42 // 所有请求平均延迟是13.42ms
	max: 45.56 // 延迟最大的请求是45.56ms
	95th percentile: 21.33 // 95%的请求延迟都在21.33ms以内

【MySQL】十七、MySQL数据库生产环境应用经验分享（一）

https://www.shuiwh.com/posts/learn-mysql-017/

作者

水无痕

发布于

2025-07-26

更新于

2025-07-26

许可协议

# 相关文章
  1.【MySQL】十八、MySQL数据库生产环境应用经验分享（二）
  2.【MySQL】十六、揭秘MySQL性能优化：执行计划深度解析！
  3.【MySQL】十五、深入MySQL索引的秘密（三）
  4.【MySQL】十四、深入MySQL索引的秘密（二）
  5.【MySQL】十三、深入MySQL索引的秘密（一）
  6.【MySQL】十二、掌握MySQL锁的必备知识
  7.【MySQL】十一、MySQL的RR隔离级别是如何基于ReadView机制实现的
  8.【MySQL】十、Read Committed隔离级别是如何基于ReadView机制实现的

链接

最新文章

分类

标签

一定要大量读书75
MySQL18
论文15
软考15
杨绛传1
读书感悟1
×