MySQL配置简单优化与读写测试

测试方法

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和脏页刷新线程数可优化，但未详细测试。