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

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