MySQL入门教程（25）数据库性能指标与基准测试
一、数据库关键性能指标
用于获取服务状态信息，便于管理员清晰了解到当前服务状态。线上优化工作一般也是围绕性能指标进行展开，对优化前的现状进行收集后确定优化方案，然后使用测试工具对优化前后环境进行测试获取差异数据。在MySQL中使用show global status命令可以查看到许多的数据库状态信息，其中主要关注以下指标：
· 连接与并发
关注最大连接数以及当前工作的线程数，如果慢查询多了通常并发就会变高，性能会受到影响；如果应用程序打开大量连接却未使用也会带来连接资源耗尽的问题
#客户端可建立的最大连接数 show global variables like 'max_connection' #当前客户端连接数，建议配置 Threads_connected/max_connected>0.8这样的百分比告警 show global status like '%Threads_connected%' #当前并发线程数，如果该指标快速增长且没有下降趋势，说明查询完成得不够快而产生了堆积，将会影响服务器的性能 show global status like '%Threads_running%'
· QPS
QPS实际为每秒执行的语句数，不单是SELECT查询。在数据库状态信息中Questions用于查看数据库处理的请求数，由于该值是递增状态，所以可以通过mysqladmin命令来进行计算，获取到每秒查询数
mysqladmin -uroot -p123456 -r -i 1 extended-status | grep "Questions" # -i 指定间隔时间为1秒 # -r 显示本次与上次数据差值
· TPS
TPS是数据库每秒处理的事务数量。因为每个事务提交的数据量大小是不同的，所以除非一些特定的场景，否则TPS的统计并不能反映太多问题。TPS的计算案例：如1000个用户进行请求，平均5秒响应，TPS为200；1000个用户进行请求，平均1秒响应，TPS为1000。假设一个交易系统峰值为800万笔/天，后续5年按照每年20%增长，那么5年后的业务峰值total为800万*（120%）*（120%）*（120%）*（120%）*（120%），按照二八原则80%的交易发生在20%的时间段，每天交易时间以12个小时计算，最终测试目标TPS需要达到(total*0.8)/(0.2*3600*12)
#TPS计算公式为(Com_commit +Com_rollback) / seconds mysql > show global status like 'Com_commit'; mysql > show global status like 'Com_rollback'; mysqladmin -uroot -p123456 -r -i 1 extended-status | grep -v "Com_rollback_to_savepoint" |grep -E "Com_commit|Com_rollback"
· open_table缓存表
mysql在每次执行一个语句的时候都会先打开一个表，该行为会计入open_tables的值。这些已经被打开过的表会存放在缓存区（table_open_cache）以便提升之后的查询效率。而执行语句的行为完成后会计入opened_tables中。当open_tables的值接近于table_open_cache说明分配的缓存已经被用完了。如果此时opened_tables值还在高速增加，说明mysql在不断的打开表，但是缓存中并没有这些要打开的表。所以可以增加缓存区大小存放更多的表。
· 命令执行数
通过mysqladmin命令获取到的com_select、com_delete、com_insert、com_update数值可以获取到各种命令执行数目，也可以通过global status中的Handler相关指标进行关注
· 缓存线程
thread_cache_size用来配置客户端线程缓存区，当客户端断开后这个连接会存在在该区域中复用。如果threads_created的值一直在增加，说明有客户端反复在创建连接，会带来额外的性能开销，建议客户端修改为长连接并调大thread_cache_size
二、数据库基准测试
由于MySQL对数据一致性有高要求，对于写数据带来的压力没办法像Web应用一样方便的实现水平扩展，所以对MySQL的基准测试可以分析当前配置下数据库的性能表现，提前知道性能阈值。如果系统环境发生了变化，也可以根据之前的基准测试结果进行测试比对，计算出环境变化对性能带来的具体影响。基准测试不关心业务逻辑，所以通常不要求数据的真实性。但是在进行基准测试一定需要进行多线程、多次测试才有意义，测试表的数量不能太少，至少有20个，每个表的数据量通常要求1000万以上。每轮测试完成后等待系统性能恢复至低谷再进行下一次测试，如果是主从架构还要注意主从状态。测试完成后将不同参数的测试生成报告进行对比，并且注明主机硬件环境、软硬件版本、测试数据量、测试时间、并发数等信息。
常用的TPC基准测试包含OLTP为主的TPC-C、TPC-E；ETL测试为主的TPC-DI等，而MySQL常用的数据库基准测试工具有sysbench、mysqlslap、Database Factory、DBT2\3等。
· mysqlslap
该工具是MySQL自带的轻量型基准测试工具，可用于简单测试
1、mysqlslap常用选项与示例
-h：数据库地址
-u：数据库用户
-p：数据库密码
-P：数据库端口
--defaults-file：指定数据库配置文件路径
--create-schema=：指定测试库
--concurrency：指定并发数
--auto-generate-sql：使用自动生成的sql进行压测
--auto-generate-sql-load-type=mixed：指定sql类型，默认mixed为混合模式，包含读、写、更新操作
--number-of-queries：总共操作的次数
--auto-generate-sql-execute-number=5000：指定测试SQL执行次数
--query=：如果不需要自动生成sql的话可以用该选项自定义sql进行压测，该选项也可以引用文件
2、mysqlslap示例
#新起会话关注QPS mysqladmin -uroot -p123456 -r -i 1 extended-status | grep "Questions" #通常需要反复压测多次，让并发数逐渐递增，观察并发达到多少时性能会出现下降，以此确定QPS最大值 #假设1个并发下QPS为500 mysqlslap --defaults-file=/etc/my.cnf -h192.168.109.101 -uroot -p --create-schema=employees --concurrency=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-execute-number=5000 #假设10个并发下QPS为800 mysqlslap --defaults-file=/etc/my.cnf -h192.168.109.101 -uroot -p --create-schema=employees --concurrency=10 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-execute-number=5000 #假设50个并发下QPS为200 mysqlslap --defaults-file=/etc/my.cnf -h192.168.109.101 -uroot -p --create-schema=employees --concurrency=50 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-execute-number=5000
3、mysqlslap压测结果，尽量控制在1秒以内
评论