測試方法

1. 先使用sysbench對默認配置的MySQL單節點進行壓測，單表數據量為100萬，數據庫總數據量為2000萬，每次壓測300秒。

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 \--mysql-host=192.168.0.10 --mysql-port=3306 --mysql-user=root --mysql-password=123456 \--mysql-db=benchtest --tables=20 \--table_size=1000000 oltp_read_write --db-ps-mode=disable run

1. 再增加innodb緩存池大小。對于單機只跑MySQL的服務器，業界一般設置為系統內存的70%左右。比如總內存64G的服務器，innodb緩存池大小可以設置為45G。該參數可以動態設置，不需要重啟MySQL。
2. 在修改innodb緩存池大小之后，修改讀寫IO的線程數加起來等于CPU核心數。比如CPU核心數為16，則讀IO線程數修改為8，寫IO線程數修改為8。該配置只能修改配置文件，不能動態配置。

檢查當前配置

-- innodb緩沖池大小
SHOW VARIABLES LIKE "%innodb_buffer_pool_size";
-- change_buffer在緩沖池的占用比
SHOW VARIABLES LIKE "%innodb_change_buffer_max_size";
-- innodb的讀io線程數
SHOW VARIABLES LIKE "%innodb_read_io_threads";
-- innodb的寫io線程數
SHOW VARIABLES LIKE "%innodb_write_io_threads";
-- 查看innodb臟頁刷新線程數
SHOW VARIABLES LIKE "%innodb_page_cleaners";
SHOW GLOBAL STATUS LIKE "innodb%wait_free";
-- 查詢緩存是否開啟
SHOW VARIABLES LIKE "%query_cache_type";

修改配置

-- 增大innodb緩沖池大小為5G
SET GLOBAL innodb_buffer_pool_size = 5368709120;
-- 讀寫IO線程數只能修改配置文件重啟MySQL才能生效

測試

• 默認配置。innodb_buffer_pool默認只有128MB，讀寫線程數分別為4。

# SQL執行統計
SQL statistics:queries performed:read:                            89432 # 讀SQL數write:                           25552 # 寫SQL數other:                           12776total:                           127760 # 總SQL數transactions:                        6388   (21.23 per sec.)queries:                             127760 (424.70 per sec.) # 平均每秒執行424.70條SQLignored errors:                      0      (0.00 per sec.)reconnects:                          0      (0.00 per sec.)
General statistics:total time:                          300.8215stotal number of events:              6388
Latency (ms):min:                                   27.55avg:                                  470.25max:                                 1907.7895th percentile:                      943.16sum:                              3003956.97
Threads fairness:events (avg/stddev):           638.8000/5.72execution time (avg/stddev):   300.3957/0.14

• 增大innodb_buffer_pool到5G。讀SQL執行數增長63.8%，寫SQL執行數增長63.8%，總執行SQL數增長63.8%，完成總事件數增長63.8%。相較于默認配置，性能提升63.8%。

SQL statistics:queries performed:read:                            146468write:                           41848other:                           20924total:                           209240transactions:                        10462  (34.79 per sec.)queries:                             209240 (695.78 per sec.)ignored errors:                      0      (0.00 per sec.)reconnects:                          0      (0.00 per sec.)
General statistics:total time:                          300.7235stotal number of events:              10462
Latency (ms):min:                                   33.13avg:                                  287.15max:                                 1199.8195th percentile:                      559.50sum:                              3004126.18
Threads fairness:events (avg/stddev):           1046.2000/6.38execution time (avg/stddev):   300.4126/0.10

• 再將讀寫IO線程各改為12。性能提升相較于增加innodb緩存池5G，增長52.3%；相較于默認配置，增長149.4%。

SQL statistics:queries performed:read:                            223020write:                           63720other:                           31860total:                           318600transactions:                        15930  (53.08 per sec.)queries:                             318600 (1061.64 per sec.)ignored errors:                      0      (0.00 per sec.)reconnects:                          0      (0.00 per sec.)
General statistics:total time:                          300.0967stotal number of events:              15930
Latency (ms):min:                                   27.77avg:                                  188.37max:                                 1133.2495th percentile:                      467.30sum:                              3000790.02
Threads fairness:events (avg/stddev):           1593.0000/13.57execution time (avg/stddev):   300.0790/0.01

其它

還有chenge_buffer和臟頁刷新線程數可優化，但未詳細測試。