之前用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就一样了