\u4e4b\u524d\u7528sysbench \u538b\u6d4bMySQL\u5199\u5165\u6027\u80fd\u65f6\u9047\u5230\u4e00\u4e2a\u95ee\u9898\uff0c\u540c\u6837\u7684\u4e24\u53f0\u7269\u7406\u673a\u5668 A \u548c B\uff0cCPU (Intel(R) Xeon(R) CPU E5620 @ 2.40GHz\uff09\u7c7b\u578b\u548cRAM (24G) \u90fd\u4e00\u6837\uff0c\u6570\u636e\u57fa\u672c\u90fd\u5728BP\u91cc\uff0c\u4e0d\u6d89\u53ca\u5230IO\uff0c\u4f46\u662f\u6d4b\u8bd5\u51fa\u6765\u7684\u6027\u80fd\u76f8\u5dee\u8fd1\u4e00\u500d\uff0c\u6d4b\u8bd5\u811a\u672c\uff1a<\/p>\n
\r\nsysbench --test=tests\/db\/update_index.lua --mysql-host=localhost --mysql-user=root --mysql-db=sbtest \r\n--oltp-tables-count=20 --oltp-table-size=1000000 --mysql-socket=\/u01\/mysql\/run\/mysql.sock \r\n--max-requests=10000000000 --max-time=600 --num-threads=128 run\r\n<\/pre>\n
\n\u5728A<\/strong>\u4e0a\u9762\uff1a(orzdba \u662f\u6dd8\u5b9d\u6731\u65ed\u5f00\u53d1\u7684MySQL\u5b9e\u65f6\u76d1\u63a7\u811a\u672c\uff0c\u5f00\u6e90\u53ef\u4e0b\u8f7d)<\/p>\n\r\norzdba --lazy --innodb_rows\r\n-------- -----load-avg---- ---cpu-usage--- ---swap--- -QPS- -TPS- -Hit%- ---innodb rows status--- \r\n time | 1m 5m 15m |usr sys idl iow| si so| ins upd del sel iud| lor hit| ins upd del read|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n<\/pre>\n\u7136\u800c\u5728B<\/strong>\u4e0a\uff1a<\/p>\n\r\norzdba --lazy --innodb_rows\r\n-------- -----load-avg---- ---cpu-usage--- ---swap--- -QPS- -TPS- -Hit%- ---innodb rows status--- \r\n time | 1m 5m 15m |usr sys idl iow| si so| ins upd del sel iud| lor hit| ins upd del read|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n10: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|\r\n<\/pre>\n\u4e24\u4e2aTPS\u76f8\u5dee\u5f88\u5927\uff0c\u51e0\u4e4e\u4e00\u500d\uff0c\u5f53\u65f6\u6ca1\u80fd\u627e\u51fa\u539f\u56e0\uff0c\u53e6\u5916\u4e00\u4e2a\u7591\u95ee\uff1aA\u4e0aTPS\u6bd4B\u9ad8\u5f88\u591a\uff0c\u4f46\u662f\u5728innodb rows\u91cc\uff0cupdate\u4f4e\u4e00\u4e9b<\/p>\n
\u6700\u8fd1\u6d4b\u8bd5galera\u6027\u80fd\u65f6\uff0c\u4e3a\u4e86\u5f97\u5230\u4e8b\u52a1\u63d0\u4ea4\u8fc7\u7a0b\u4e2d\u5404\u9636\u6bb5\u8017\u65f6\uff0c\u5728\u4ee3\u7801\u4e2d\u6ce8\u5165\u4e86\u4e00\u4e9b\u8f93\u51fa\u65e5\u5fd7\u7684patch\uff0c\u53d1\u73b0\u6709\u4e9bupdate\u8bed\u53e5\u63d0\u4ea4\u65f6\u95f4\u8fdc\u8fdc\u5c0f\u4e8e\u5176\u4ed6\u8bed\u53e5\uff0c\u8ddf\u8e2a\u540e\u53d1\u73b0\u8fd9\u90e8\u5206update\u8bed\u53e5where\u6761\u4ef6\u7684id\u5728\u6570\u636e\u5e93\u4e2d\u6ca1\u6709\u8bb0\u5f55\u4e0e\u4e4b\u5bf9\u5e94\uff0c\u6267\u884c\u8fc7\u7a0b\u4e2d\u6ca1\u6709\u53d1\u751f\u5b9e\u8d28\u6027\u7684\u6570\u636e\u66f4\u65b0\uff0c\u4e0d\u4f1a\u590d\u5236\u6570\u636e\u548c\u5199log\uff0c\u56e0\u6b64\u5f88\u5feb\u8fd4\u56de<\/p>\n
sysbench\u4ea7\u751f\u7684sql\u8bed\u53e5\u975e\u5e38\u7b80\u5355\uff0c\u7c7b\u4f3c\uff1a<\/p>\n
\r\nUPDATE sbtest17 SET k=k+1 WHERE id=496476;\r\n<\/pre>\n\u5176\u4e2did\u662fsysbench\u4ecemin(id)~max(id)\u968f\u673a\u751f\u6210\u7684\uff0c\u8fdb\u4e00\u6b65\u8ddf\u8e2a\u53d1\u73b0\u8017\u65f6\u5f88\u77ed\u6ca1\u6709\u53d1\u751f\u6570\u636e\u66f4\u65b0\u7684update\u8bed\u53e5\u5bf9\u5e94\u7684id\u5168\u90e8\u90fd\u662f\u5076\u6570<\/strong>\uff0c\u4e8e\u662f\u5206\u522b\u67e5\u770bA\uff0cB\u4e0a\u5f97auto_increment\u76f8\u5173\u53c2\u6570\uff1a<\/p>\n\r\nmysql [A]> show variables like 'auto_increment%';\r\n+--------------------------+-------+\r\n| Variable_name | Value |\r\n+--------------------------+-------+\r\n| auto_increment_increment | 2 |\r\n| auto_increment_offset | 1 |\r\n+--------------------------+-------+\r\n\r\nmysql [B]> show variables like 'auto_increment%';\r\n+--------------------------+-------+\r\n| Variable_name | Value |\r\n+--------------------------+-------+\r\n| auto_increment_increment | 1 |\r\n| auto_increment_offset | 1 |\r\n+--------------------------+-------+\r\n<\/pre>\n\u518d\u770b\u76f8\u5e94\u7684\u6570\u636e\uff1a<\/p>\n
\r\nmysql [A]> select id from sbtest.sbtest1 order by id limit 5;\r\n+----+\r\n| id |\r\n+----+\r\n| 1 |\r\n| 3 |\r\n| 5 |\r\n| 7 |\r\n| 9 |\r\n+----+\r\n5 rows in set (0.00 sec)\r\n\r\nmysql [B]> select id from sbtest.sbtest1 order by id limit 5;\r\n+----+\r\n| id |\r\n+----+\r\n| 1 |\r\n| 2 |\r\n| 3 |\r\n| 4 |\r\n| 5 |\r\n+----+\r\n5 rows in set (0.00 sec)\r\n<\/pre>\n\u7ecf\u8fc7\u8fd9\u4e00\u756a\u5206\u6790\uff0c\u7ec8\u4e8e\u627e\u5230TPS\u76f8\u5dee\u5f88\u5927\u7684\u539f\u56e0\u4e86\uff0c\u5728A\u4e0a\uff0c\u90e8\u5206update\u5b9e\u9645\u4e0a\u6ca1\u6709\u66f4\u65b0\u6570\u636e\uff0c\u56e0\u6b64\u5f88\u5feb\u8fd4\u56de\uff0c\u9020\u6210TPS\u865a\u9ad8\uff0c\u56e0\u4e3a\u5728mysql\u4e2d\uff0c\u6bcf\u4e00\u6761update\u8bed\u53e5\uff0c\u4e0d\u7ba1\u5176\u6267\u884c\u8fc7\u7a0b\u4e2d\u66f4\u65b0\u4e86\u591a\u5c11\u6761\u6570\u636e\u90fd\u4f1a\u5c06com_update\u589e\u52a01\uff0c\u51c6\u786e\u7684\u8bf4\uff0ccom_update\u662f\u8bb0\u5f55server\u6267\u884c\u4e86\u591a\u5c11\u6761update\u8bed\u53e5<\/p>\n
\u53e6\u5916\u4e00\u65b9\u9762\uff0cB\u7684TPS\u6bd4A\u4f4e\u800cinnodb_rows_update\u6bd4A\u9ad8\u4e5f\u80fd\u89e3\u91ca\u4e86\uff0c\u56e0\u4e3aB\u7684TPS\u6ca1\u6709\u201c\u865a\u201d\u7684\uff0c\u6bcf\u6761update\u8bed\u53e5\u90fd\u4f1a\u6210\u529f\u66f4\u65b0\u4e00\u6761\u8bb0\u5f55\uff0c\u56e0\u6b64\u5b83\u4e0e\u5176TPS\u5e94\u8be5\u662f\u4e00\u6837\uff0c\u7531\u4e8e\u5b9e\u65f6\u76d1\u63a7\u811a\u672c\u662f\u901a\u8fc7\u91c7\u6837\u83b7\u53d6\u8fd0\u884c\u6570\u636e\u7684\uff0c\u56e0\u800c\u4e0d\u662f\u5b8c\u5168\u4e00\u81f4\uff0c\u800c\u4eceA\u4e2d\u7684TPS\u4e0einnodb_rows_update\u4e2d\u53ef\u4ee5\u4f30\u7b97\u51fa\u201c\u865a\u201d\u7684TPS\u6709\u4e00\u534a<\/p>\n
\u6700\u540e\uff0c\u5c06A\u4e2dauto_increment_increment\u8bbe\u7f6e\u4e3a1\u540e\u518d\u8fdb\u884c\u6d4b\u8bd5\uff0c\u6027\u80fd\u6307\u6807\u548cB\u5c31\u4e00\u6837\u4e86<\/p>\n","protected":false},"excerpt":{"rendered":"