使用sysbench压测MySQL的一个问题

By | 2013 年 4 月 18 日

之前用sysbench 压测MySQL写入性能时遇到一个问题,同样的两台物理机器 A 和 B,CPU (Intel(R) Xeon(R) CPU E5620 @ 2.40GHz)类型和RAM (24G) 都一样,数据基本都在BP里,不涉及到IO,但是测试出来的性能相差近一倍,测试脚本:

sysbench --test=tests/db/update_index.lua --mysql-host=localhost --mysql-user=root --mysql-db=sbtest 
--oltp-tables-count=20 --oltp-table-size=1000000 --mysql-socket=/u01/mysql/run/mysql.sock 
--max-requests=10000000000 --max-time=600 --num-threads=128 run


A上面:(orzdba 是淘宝朱旭开发的MySQL实时监控脚本,开源可下载)

orzdba --lazy --innodb_rows
-------- -----load-avg---- ---cpu-usage--- ---swap---                     -QPS- -TPS-         -Hit%- ---innodb rows status--- 
  time  |  1m    5m   15m |usr sys idl iow|   si   so|  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read|
10:47:22|13.35  7.54  3.51| 54  18  27   0|    0    0|    0 51892     0      1 51892|  507573 100.00|    0 25878     0  25846|
10:47:23|13.17  7.60  3.55| 55  17  27   0|    0    0|    0 47240     0      1 47240|  828027 100.00|    0 23489     0  23498|
10:47:24|13.17  7.60  3.55| 55  18  27   0|    0    0|    0 47712     0      1 47712|  846573 100.00|    0 23714     0  23710|
10:47:25|13.17  7.60  3.55| 56  18  27   0|    0    0|    0 47936     0      1 47936|  848240 100.00|    0 23936     0  23909|
10:47:26|13.17  7.60  3.55| 56  17  26   0|    0    0|    0 48124     0      1 48124|  847070 100.00|    0 24115     0  24097|
10:47:27|13.17  7.60  3.55| 56  18  26   0|    0    0|    0 47737     0      1 47737|  840251 100.00|    0 23805     0  23797|
10:47:28|12.99  7.66  3.59| 57  18  24   0|    0    0|    0 49300     0      1 49300|  844858 100.00|    0 24577     0  24566|
10:47:29|12.99  7.66  3.59| 54  18  28   0|    0    0|    0 51719     0      1 51719|  518692 100.00|    0 25626     0  25588|
10:47:30|12.99  7.66  3.59| 55  17  27   1|    0    0|    0 46671     0      1 46671|  805299 100.00|    0 23372     0  23344|
10:47:31|12.99  7.66  3.59| 55  18  27   0|    0    0|    0 47650     0      1 47650|  850355 100.00|    0 23659     0  23644|
10:47:32|12.99  7.66  3.59| 55  18  27   0|    0    0|    0 47668     0      1 47668|  859859 100.00|    0 23724     0  23707|
10:47:33|12.43  7.63  3.60| 54  17  28   0|    0    0|    0 46549     0      1 46549|  846563 100.00|    0 23303     0  23284|
10:47:34|12.43  7.63  3.60| 54  17  28   0|    0    0|    0 46650     0      1 46650|  822190 100.00|    0 23505     0  23466|
10:47:35|12.43  7.63  3.60| 55  18  27   0|    0    0|    0 51093     0      1 51093|  540031 100.00|    0 25509     0  25495|
10:47:36|12.43  7.63  3.60| 54  18  28   0|    0    0|    0 47545     0      1 47545|  781653 100.00|    0 23549     0  23515|

然而在B上:

orzdba --lazy --innodb_rows
-------- -----load-avg---- ---cpu-usage--- ---swap---                     -QPS- -TPS-         -Hit%- ---innodb rows status--- 
  time  |  1m    5m   15m |usr sys idl iow|   si   so|  ins   upd   del    sel   iud|     lor    hit|  ins   upd   del   read|
10:16:23|11.32  8.90  4.20| 54  11  32   2|    0    0|    0 31653     0      1 31653|  894306  99.99|    0 31522     0  31429|
10:16:24|11.32  8.90  4.20| 53  10  34   2|    0    0|    0 30660     0      1 30660|  920688 100.00|    0 30535     0  30414|
10:16:25|12.50  9.19  4.32| 53  11  33   3|    0    0|    0 30712     0      1 30712|  912876  99.99|    0 30606     0  30478|
10:16:26|12.50  9.19  4.32| 54  10  33   3|    0    0|    0 31241     0      1 31241|  955200  99.98|    0 31083     0  30933|
10:16:27|12.50  9.19  4.32| 48  10  38   3|    0    0|    0 28226     0      1 28226|  871025  99.99|    0 28139     0  28014|
10:16:28|12.50  9.19  4.32| 52  11  36   2|    0    0|    0 30576     0      1 30576|  870604  99.99|    0 30436     0  30332|
10:16:29|12.50  9.19  4.32| 52  11  35   2|    0    0|    0 30852     0      1 30852|  836594 100.00|    0 30749     0  30597|
10:16:30|12.78  9.30  4.38| 53  11  34   2|    0    0|    0 31367     0      1 31367|  818984  99.99|    0 31235     0  31130|
10:16:31|12.78  9.30  4.38| 53  11  35   1|    0    0|    0 31241     0      1 31241|  874486  99.99|    0 31114     0  31004|
10:16:32|12.78  9.30  4.38| 53  11  34   2|    0    0|    0 31602     0      1 31602|  820628  99.99|    0 31488     0  31345|

两个TPS相差很大,几乎一倍,当时没能找出原因,另外一个疑问:A上TPS比B高很多,但是在innodb rows里,update低一些

最近测试galera性能时,为了得到事务提交过程中各阶段耗时,在代码中注入了一些输出日志的patch,发现有些update语句提交时间远远小于其他语句,跟踪后发现这部分update语句where条件的id在数据库中没有记录与之对应,执行过程中没有发生实质性的数据更新,不会复制数据和写log,因此很快返回

sysbench产生的sql语句非常简单,类似:

UPDATE sbtest17 SET k=k+1 WHERE id=496476;

其中id是sysbench从min(id)~max(id)随机生成的,进一步跟踪发现耗时很短没有发生数据更新的update语句对应的id全部都是偶数,于是分别查看A,B上得auto_increment相关参数:

mysql [A]> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 2     |
| auto_increment_offset    | 1     |
+--------------------------+-------+
 
mysql [B]> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |
| auto_increment_offset    | 1     |
+--------------------------+-------+

再看相应的数据:

mysql [A]> select id from sbtest.sbtest1 order by id limit 5;
+----+
| id |
+----+
|  1 |
|  3 |
|  5 |
|  7 |
|  9 |
+----+
5 rows in set (0.00 sec)
 
mysql [B]> select id from sbtest.sbtest1 order by id limit 5;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
+----+
5 rows in set (0.00 sec)

经过这一番分析,终于找到TPS相差很大的原因了,在A上,部分update实际上没有更新数据,因此很快返回,造成TPS虚高,因为在mysql中,每一条update语句,不管其执行过程中更新了多少条数据都会将com_update增加1,准确的说,com_update是记录server执行了多少条update语句

另外一方面,B的TPS比A低而innodb_rows_update比A高也能解释了,因为B的TPS没有“虚”的,每条update语句都会成功更新一条记录,因此它与其TPS应该是一样,由于实时监控脚本是通过采样获取运行数据的,因而不是完全一致,而从A中的TPS与innodb_rows_update中可以估算出“虚”的TPS有一半

最后,将A中auto_increment_increment设置为1后再进行测试,性能指标和B就一样了

发表评论

电子邮件地址不会被公开。 必填项已用*标注