测试方法
- 先使用
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
- 再增加innodb缓存池大小。对于单机只跑MySQL的服务器，业界一般设置为系统内存的70%左右。比如总内存64G的服务器，innodb缓存池大小可以设置为45G。该参数可以动态设置，不需要重启MySQL。
- 在修改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和脏页刷新线程数可优化，但未详细测试。
