本站所有文章均为原创,如对您有帮助,恳请帮忙点击任何一处广告
  • 首页
  • MySQL
  • 【MySQL运维】数据库关键性能指标与基准测试

【MySQL运维】数据库关键性能指标与基准测试

发布:TangLu2021-2-8 14:36分类: MySQL 标签: mysql

数据库的关键性能指标用于获取服务状态信息,可以便于管理员清晰的知道服务状态。数据库基准测试是为系统建立一个性能基准,当系统环境发生变化时根据之前的基准再进行一次测试可以看出变化对性能的影响,以便在早期发现性能问题。由于MySQL对数据一致性有高要求,对于写数据带来的压力没办法像Web应用一样方便的实现水平扩展,所以对MySQL的基准测试可以分析当前配置下数据库的性能表现,找出性能阈值。基准测试不关心业务逻辑,数据不要求真实。MySQL常用的基准测试工具有MySQL官方mysqlslap、第三方工具sysbench、tpcc等。


一、数据库关键性能指标

· QPS——每秒请求查询数

在数据库状态信息中Questions用于查看数据库处理的请求数,由于该值是递增状态,所以可以通过mysqladmin命令来进行计算,获取到每秒查询数

mysqladmin -uroot -p123456 -r -i 1 extended-status | grep "Questions"
# -i 指定间隔时间为1秒
# -r 显示本次与上次数据差值


· 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_tables、opened_tables与table_open_cache之间的关系。mysql在每次执行一个语句的时候都会先打开一个表(open_tables),当sql语句执行完成后再关闭表。而已经打开过的表会存放在缓存区(table_open_cache),下次打开就会更快了。open_tables的值接近于table_open_cache说明分配的缓存已经被用完了。如果此时opened_tables值还在高速增加,说明mysql在不断的打开表,说明缓存中并没有这些要打开的表。所以可以增加缓存区大小存放更多的表。


· 命令执行数

通过mysqladmin命令可以获取到各种命令执行数目


· 缓存线程

thread_cache_size用来配置客户端线程缓存区,当客户端断开后这个连接会存在在该区域中复用。如果threads_created的值一直在增加,说明有客户端反复在创建连接,会带来额外的性能开销,建议客户端修改为长连接并调大thread_cache_size


· 并发数

关注最大连接数以及当前工作的线程数,如果慢查询多了通常并发就会变高,性能会受到影响

show variables like 'max_connection'  #客户端可建立的最大连接数
show status like '%Threads_connected%'  #当前客户端连接数,可配置Threads_connected / max_connected > 0.8 报警,表示接近服务最大连接
show status like '%Threads_running%'  #当前并发线程数


二、数据库基准测试——mysqlslap(轻量型压测工具,可用于简单测试)

· 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进行压测,该选项也可以引用文件

#新起会话关注QPS
mysqladmin -uroot -p123456 -r -i 1 extended-status | grep "Questions"

#通常需要反复压测多次,让并发数逐渐递增,观察并发达到多少时性能会出现下降,以此确定QPS最大值  
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  #假设1个并发下QPS为500
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  #假设10个并发下QPS为800
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  #假设50个并发下QPS为200


· mysqlslap压测结果,尽量控制在1秒以内

mysqlslap.jpg


三、数据库基准测试工具——sysbench

它是一个可跨平台、多线程的基准测试工具,可以评估不同系统参数下数据库的负载情况,还可以进行磁盘IO、CPU性能、内存性能等测试)。使用sysbench前需要明确是做系统基准测试还是数据库基准测试,或者二者都需要。并且基准测试需要多线程+多次测试才有意义。如果是对数据库进行测试要注意主从同步状态,并且测试表的数量不能太少,至少有20个,每个表的数据量通常要求1000万以上,每轮测试完成后等待10分钟左右再进行下一次测试。最后生成测试报告时也需要写明主机硬件环境、软硬件版本、测试数据量、测试时间、并发数等信息。


1、安装sysbench

访问https://github.com/akopytov/sysbench下载并安装sysbench,提供源码包或者二进制包的方式进行安装

#源码安装
yum -y install make automake libtool pkgconfig libaio-devel openssl-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常用选项

· 数据库选项

--db-driver:指定数据库类型,默认为mysql

--mysql-host:数据库地址

--mysql-port:数据库端口

--mysql-user:数据库用户

--mysql-password:数据库密码

--mysql-db:指定测试数据库,测试数据会写入该库中,默认是使用sbtest(该库需要自行创建)


· 执行选项

--test=fileio:指定测试脚本,在新版本中已废弃,在命令中直接指定测试脚本即可

--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(随机读写)

--max-time=300 :测试执行时长

--num-threads=128 : 并发线程数,通常和CPU数量保持一致

--report-interval=5:每隔多少秒输出报告

 --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


3、sysbench测试

sysbench测试分为了prepare准备阶段、run测试阶段以及cleanup清理阶段。如果要使用真实数据做测试,可以不用prepare阶段;基准测试一定要执行多次,每次测试时长1小时左右,并且一定要配置为多线程测试,单线程无法模拟真实环境,也无法模拟阻塞和死锁情况


· 使用sysbench测试磁盘性能

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

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


· 使用sysbench进行数据库测试

#prepare阶段,指定测试使用到的lua脚本,这里使用了oltp.lua,同目录下还有各类脚本可用
mysql > create database sbtest;  #创建测试数据存放库,sbtest是sysbench默认用到的库名
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 prepare  #生成10W行数据的表


#run阶段,观察CPU在什么情况下会跑满
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 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测试结果

在对数据库进行测试时,可以关注报告中response time、transactions(TPS)、queries(QPS)、Latency(响应时间)这几个值,看看在多少并发的时候会导致数据库响应时间变慢

sysbench2.png


温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,谢谢合作!
et_highlighter51
版权所有:《Linux运维技术学习站点
文章标题:《【MySQL运维】数据库关键性能指标与基准测试
除非注明,文章均为 《Linux运维技术学习站点》 原创
转载请注明本文短网址:http://www.linuxe.cn/post-515.html  [生成短网址]

已有 0/223 人参与

发表评论:

欢迎分享Linux运维技术学习站点

欢迎使用手机扫描访问本站,还可以关注微信哦~