测试方法

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 

2. 再增加innodb缓存池大小。对于单机只跑MySQL的服务器，业界一般设置为系统内存的70%左右。比如总内存64G的服务器，innodb缓存池大小可以设置为45G。该参数可以动态设置，不需要重启MySQL。
3. 在修改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:                           12776 
        total:                           127760 # 总SQL数 
    transactions:                        6388   (21.23 per sec.) 
    queries:                             127760 (424.70 per sec.) # 平均每秒执行424.70条SQL 
    ignored errors:                      0      (0.00 per sec.) 
    reconnects:                          0      (0.00 per sec.) 
 
General statistics: 
    total time:                          300.8215s 
    total number of events:              6388 
 
Latency (ms): 
         min:                                   27.55 
         avg:                                  470.25 
         max:                                 1907.78 
         95th percentile:                      943.16 
         sum:                              3003956.97 
 
Threads fairness: 
    events (avg/stddev):           638.8000/5.72 
    execution 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:                            146468 
        write:                           41848 
        other:                           20924 
        total:                           209240 
    transactions:                        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.7235s 
    total number of events:              10462 
 
Latency (ms): 
         min:                                   33.13 
         avg:                                  287.15 
         max:                                 1199.81 
         95th percentile:                      559.50 
         sum:                              3004126.18 
 
Threads fairness: 
    events (avg/stddev):           1046.2000/6.38 
    execution time (avg/stddev):   300.4126/0.10 

• 再将读写IO线程各改为12。性能提升相较于增加innodb缓存池5G，增长52.3%；相较于默认配置，增长149.4%。

SQL statistics: 
    queries performed: 
        read:                            223020 
        write:                           63720 
        other:                           31860 
        total:                           318600 
    transactions:                        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.0967s 
    total number of events:              15930 
 
Latency (ms): 
         min:                                   27.77 
         avg:                                  188.37 
         max:                                 1133.24 
         95th percentile:                      467.30 
         sum:                              3000790.02 
 
Threads fairness: 
    events (avg/stddev):           1593.0000/13.57 
    execution time (avg/stddev):   300.0790/0.01 

其它

还有chenge_buffer和脏页刷新线程数可优化，但未详细测试。