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秒以内

· sysbench

使用最多的MySQL基准测试工具，除了可以对不同参数下数据库负载进行评估，还可以进行磁盘IO、CPU性能、内存性能等基准测试。除了提供大量现成可用的lua脚本用于测试之外，还可以自定义脚本实现更灵活的测试。该工具早期可以跨平台使用，目前只能运行于Linux平台。

1、编译安装sysbench

访问https://github.com/akopytov/sysbench可以下载到sysbench的编码包或者二进制包

# 源码安装过程
# 安装依赖
yum -y install make automake libtool pkgconfig libaio-devel openssl-devel mysql-devel

tar zxf sysbench-1.0.15.tar.gz
./autogen.sh
./configure --prefix=/usr/local/sysbench --with-mysql=/usr/local/mysql --with-mysql-includes=/usr/local/mysql/include
make -j  && make install 

# rpm包安装
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
sudo yum -y install sysbench

2、sysbench命令格式

sysbench <options> <testname> <command>

· options 选项介绍

--db-driver：指定数据库类型，默认为mysql，除此还支持postgresql

--mysql-host：数据库地址

--mysql-port：数据库端口

--mysql-user：数据库用户

--mysql-password：数据库密码

--mysql-db：指定测试数据库，测试数据会写入该库中，默认是使用sbtest这个库（该库需要自行创建）

--threads：并发数量，在测试时逐步增加并发数以观察性能,，通常和CPU数量保持一致。sysbench 1.0以前该选项为num-threads

--file-num=4 ：进行IO测试时生成的测试文件个数

--file-block-size=16384 ：进行IO测试时文件块大小

--file-total-size=100G ：进行IO测试时生成的文件总大小

--file-test-mode=rndrd ：进行IO测试时的模式，包含seqwr(顺序写)、seqrewr(顺序读写)、seqrd(顺序读)、rndrd(随机读)、rndwr(随机写)、rndrw(随机读写)

--time=300 ：测试执行时长，sysbench 1.0以前该选项为max-time

--report-interval=N：每隔N秒输出报告

--oltp-test-mode=complex：老版本选项，指定压测模式，分为complex（最全面的测试增删改查和事务）、simple（只进行简单的查询测试）、nontrx（测试查询和更新等，但不测试事务）

--oltp-tables-count=10：老版本选项，指定生成多少张测试数据表，生成时间取决于oltp-table-size的值

--oltp-table-size=100000：老版本选项，每张测试表的数据行数

--oltp-dist-pct：表中包含热点数据的百分比，默认为1

--oltp-dist-res：所有请求中有多少百分比是访问热点数据，默认75

· sysbench testname选项介绍，用于指定测试所需的lua脚本

从 sysbench 1.0.x 开始，如果没有指定特殊安装路径，测试脚本存放于/usr/local/share/sysbench或/usr/share/sysbench下（为了兼容老版本的使用习惯在/usr/share/sysbench/tests/include/oltp_legacy/也存放了老的lua脚本），指定脚本时只用写脚本名字，不用写后缀，如oltp_read_write.lua写为oltp_read_write

#查看指定脚本的帮助信息
sysbench oltp_insert help

· sysbench command选项介绍

prepare：执行准备工作，例如创建测试文件进行fileio测试或者再数据库上建立包含数据的表进行基准测试

warmup：预热，确保缓冲区和缓存里已加载了表和索引

run：正式执行测试

cleanup：测试完成后清理测试数据

3、sysbench测试示例

· 使用sysbench测试磁盘性能

#该命令表示生成4个块大小为16K的测试文件，总大小为100G
sysbench fileio --file-num=4 --file-block-size=16384 --file-total-size=100G prepare

#执行测试阶段。下面的命令代表开启128个线程对4个共计100G的文件进行300秒的随机读(rndrd)测试，并且每秒输出
sysbench fileio --file-num=4 --file-block-size=16384 --file-total-size=100G --file-test-mode=rndrd --time=300 --threads=128 --report-interval=1 run

· 使用sysbench进行数据库测试

#创建测试数据存放库，sbtest是sysbench默认用到的库名
mysql > create database sbtest;  

# prepare阶段
# 指定一个测试脚本，不同脚本作用不同，这里使用的oltp_read_write脚本用来测试OLTP性能
sysbench /usr/share/sysbench/oltp_read_write.lua \ 
--mysql-host=172.20.1.172  --mysql-port=3306 --mysql-user=root --mysql-password=123456 \ 
--threads=8 --table_size=1000000 --tables=20 \ 
prepare

# warmup阶段
# 一定要加上--tables和--table-size选项，否则只会预加载第一张表的数据
sysbench /usr/share/sysbench/oltp_read_write.lua \ 
--mysql-host=172.20.1.172  --mysql-port=3306 --mysql-user=root --mysql-password=123456 \ 
--threads=8 --table_size=1000000 --tables=20 \ 
warmup

# run阶段
# 指定时间开始测试，在该阶段可以逐步增加线程，观察CPU在什么情况下会跑满
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=172.20.1.172 --mysql-port=3306 --mysql-user=root --mysql-password=123456  \
--threads=8 --tables=20 --time=300 --report-interval=30 \
run

# cleanup阶段，清理测试数据
# 这里也要加上--tables选项，否则只清理第一张表
sysbench /usr/share/sysbench/oltp_read_write.lua  \
--mysql-host=172.20.1.172  --mysql-port=3306 --mysql-user=root --mysql-password=123456  \
--tables=100 \
cleanup

#老版本
#sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.94.10 --mysql-port=3306 --mysql-user=root --mysql-password=root --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000  prepare  #生成10W行数据的表
#sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.94.10 --mysql-port=3306 --mysql-user=root --mysql-password=root --oltp-test-mode=complex --oltp-tables-count=10 --oltp-table-size=100000 --threads=10 --time=120 --report-interval=10 run > report.log #10个线程，运行2分钟
#sysbench /usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --mysql-host=192.168.94.10 --mysql-port=3306 --mysql-user=root --mysql-password=root cleanup

4、sysbench输出与报告解析

[ 22s ] thds: 10 tps: 380.99 qps: 7312.66 (r/w/o: 5132.99/1155.86/1321.35) lat (ms, 95%): 21.33 err/s: 0.00 reconn/s:
0.00

输出报告中，thds: 10表示有10个线程在压测；tps: 380.99表示每秒执行了380.99个事务；qps: 7610.20表示每秒可以执行7610.20个请求以及具体请求分布情况。在对数据库进行测试时，可以关注报告中response time、transactions（TPS）、queries（QPS）、Latency（响应时间）这几个值，比如transactions: 105180( 350.6 per sec. )表示一共执行了10万多个事务，每秒执行350多个事务。在测试的时候可以逐渐增加并发线程，然后观察线程达到什么量级后QPS无法再获得提升，然后在提升并发的同时还需要观察CPU、内存等资源消耗情况。在硬件负载情况比较正常的范围内，哪怕负载相对较高一些也还是可以继续增加线程数量和提高数据库的QPS。当增加线程数量后发现在某个QPS数值下服务器CPU、内存、网络和磁盘的负载已经比较高了，那么说明基本已经到了性能的瓶颈了，此时就不能继续增加线程数来提高数据库QPS了。当压测结果不理想时第一时间查看CPU使用率，如果出现CPU总使用率低或iowait、system高的情况，需要检查是否有开启SSL或者buffer_size配置不合理等情况

5、sysbench常见问题

· Can't create more than max_prepared_stmt_count statements

当MySQL发现prepare语句超出max_prepared_stmt_count的限制就会出现该错误，当压测时应用端没有关闭prepared语句就会导致这种情况。通过max_prepared_stmt_count参数可以限制同一时间在mysqld上所有session中prepared语句的上限，默认为16382，取值范围为0——1048576。通常情况下默认值是足够使用的，因为线上的并发通常不会那么大

mysql > SHOW GLOBAL STATUS LIKE 'com_stmt%';
mysql > SET GLOBAL max_prepared_stmt_count=100000;