MySQL性能测试—前期知识储备

发表于 2013/03/08

作者 OneCoder

次浏览 6 分钟阅读

今天是妇女节，先祝所有过节的女同胞们节日快乐：）

即将对MySQL进行性能测试。所以事先对MySQL的测试工作进行一番了解。主要考察性能测试的工具，MySQL的关键指标，以及一些基础的Benchmark数据，为测试用例和场景的规划做些准备。

先说说考量的指标（转载，网址找不到了，抱歉）

(1)QPS(每秒Query量)
QPS = Questions(or Queries) / seconds
mysql > show /*50000 global */ status like 'Question';
(2)TPS(每秒事务量)
TPS = (Com_commit + Com_rollback) / seconds
mysql > show status like 'Com_commit';
mysql > show status like 'Com_rollback';
(3)key Buffer 命中率
key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%
key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%
mysql> show status like 'Key%';
(4)InnoDB Buffer命中率
innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads / innodb_buffer_pool_read_requests) * 100%
mysql> show status like 'innodb_buffer_pool_read%';
(5)Query Cache命中率
Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;
mysql> show status like 'Qcache%';
(6)Table Cache状态量
mysql> show status like 'open%';
(7)Thread Cache 命中率
Thread_cache_hits = (1 - Threads_created / connections ) * 100%
mysql> show status like 'Thread%';
mysql> show status like 'Connections';
(8)锁定状态
mysql> show status like '%lock%';
(9)复制延时量
mysql > show slave status
(10) Tmp Table 状况(临时表状况)
mysql > show status like 'Create_tmp%';
(11) Binlog Cache 使用状况
mysql > show status like 'Binlog_cache%';
(12) Innodb_log_waits 量
mysql > show status like 'innodb_log_waits';

其中QPS和TPS自然是重点考察的性能指标。其他指标可以作为每次测试数据的参考数据列出。如果遇到瓶颈，可能还需要考量当时系统的cpu，网络，磁盘的利用率情况。这个遇到具体问题再具体分析。

工具方面，首选考察的自然是MySQL自带的测试工具mysqlslap。

./mysqlslap -a --concurrency=50,100 --number-of-queries 1000 --iterations=5 --engine=myisam,innodb --debug-info -uroot -proot

一个简单的示例即可说明工具的使用情况，

-a 自动生成sql
--concurrency=50,100分别执行50，100并发，
--iterations=5 执行5次，引擎分别选用myisam和innodb引擎测试。
--number-of-queries Limit each client to this number of queries (this is not
exact).（一个客户端执行的测试SQL数量上限，通过--only-print观察自动生成的sql的执行情况来看，该条数限制的是准备数据以外的SQL语句的条数）
--engine=myisam,innodb 分别用myisam和innodb引擎进行测试。

当然你也可以通过-q指定你想要测试的sql脚本，测试结束后，mysqlslap会给出你测试的相关数据。

关于mysqlslap的更多参数可参考：http://dev.mysql.com/doc/refman/5.1/en/mysqlslap.html
从目前来看上手还是比较容易的。通过执行的query数量和时间，很容易计算出tps和qps等指标。

SysBench

也是MySQL官网提到的一个benchmark工具。只是在64位系统下安装困难，限于网络环境和考虑到其功能和我们即将进行的测试场景，暂时放弃。

性能测试基础准备其他话题

在思考和验证怎样使用mysqlslap规划测试场景的过程中，收获到一些离散的细节问题，也在此一并记录一下。也许哪天，哪一条就会有所帮助。

打开MySQL general_log 记录执行的sql情况

默认情况下该属性是关闭的，通过set global general_log=ON 打开后，可在log文件中查看数据库执行的sql记录。该需求是OneCoder想要考察JDBC的batch insert的提交方式，究竟是数据库中是执行的多条insert然后commit还是一个insert values大列表的时候产生的。

对比批量插入数据多条insert和一条insert 大values列表(values(),(),())方式，性能差别

从目前通过mysqlslap执行测试的效果来看，后者明显优于前者。前者1000条平均时间大约是0.2ms，而后者在0.1ms左右。该测试想法是在通过mysqldump到处已有数据的时候发现其sql文件的生成方式的时候以及联想到oracle到mysql的数据迁移工具的处理方式的时候想到的。

写入性能瓶颈的一个大误解

曾经极大的错误的认为单线程下mysql可处理的批量插入数据量就是该节点的瓶颈，所以只认为多线程并发写入优化只在NDB的环境下才有效。今天在看MySQL的一些benchmark图表曲线的时候，猛然惊醒，并发数和tps的曲线是类抛物线的就说明在一定并发数的范围内，TPS是有明显提高的。于是用以前的JDBC的代码，增加了几个线程，对同一个mysql并发写入，果然TPS成倍提升。

今天总结的大概就这么多，由于是事后回忆总结，不免有所遗漏，想起来再补充把。热烈欢迎指导。

知识扩展

MySQL

本文由作者按照 CC BY 4.0 进行授权

热门标签