MySQL核心参数优化（内存优化）
配置内存
内存组件介绍
Innodb缓冲池（占用物理内存80%）
# 控制缓冲池的大小（10G）
Innodb_buffer_pool_size
# 划分缓冲池几个区域，如果是10G的话划分5个区域，每个区域2G，默认值-1，划分8个区域，大于1G才有意义
Innodb_buffer_pool_instances
# 脏页控制比例，默认值为75%，被修改的
Innodb_max_dirty_pages_pct每个连接（线程）需要的缓存【100个并发需要100*(1+。。。。。。5)】
# 排序
sort_buffer_size
#
read_buffer_size
#
read_rnd_buffer_size
# 多表关联
join_buffer_size
# binlog相关
binlog_cache_size其他缓存
# 线程缓存 链接池 中间件
thread_cache_size
# 表缓存 表比较多的时候
table_open_cache
# MyISAM缓存 现在是innodb，用的比较少
内存优化示例
环境介绍
硬件环境
8核16G内存
软件环境
centos7.7 mysql5.7.26
优化策略
抓大放小，当前只调整缓冲池的大小
开启performance-schema
performance-schema-instrument='wait/%=ON'
performance-schema-consumer-events-waits-current=ON
performance-schema-consumer-events-waits-history=ON
performance-schema-consumer-events-waits-history-long=ON查看默认buffer大小
第一次压测
[root@cnbugs1 tpcc-mysql]# ./tpcc_start -h127.0.0.1 -P 3306 -d tpcc -u root -p Aa123456 -w 10 -c 1 -r 300 -l 200查看分页状态
mysql> show engine status\G;
Total large memory allocated 137428992
Dictionary memory allocated 169602
Buffer pool size 8192
Free buffers 1020
Database pages 6960
Old database pages 2557
Modified db pages 3014
Pending reads 0一直在减少，有时候直接为0，不够使用
优化前的压测数据
<Constraint Check> (all must be [OK])
[transaction percentage]
Payment: 43.49% (>=43.0%) [OK]
Order-Status: 4.34% (>= 4.0%) [OK]
Delivery: 4.34% (>= 4.0%) [OK]
Stock-Level: 4.35% (>= 4.0%) [OK]
[response time (at least 90% passed)]
New-Order: 16.24% [NG] *
Payment: 94.93% [OK]
Order-Status: 98.42% [OK]
Delivery: 97.63% [OK]
Stock-Level: 18.55% [NG] *
<TpmC>
3800.700 TpmC语句执行时间
mysql> use performance_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select EVENT_NAME,COUNT_STAR,sys.format_time(SUM_TIMER_WAIT) from events_statements_summary_global_by_event_name order by SUM_TIMER_WAIT desc limit 5;
+----------------------------+------------+---------------------------------+
| EVENT_NAME | COUNT_STAR | sys.format_time(SUM_TIMER_WAIT) |
+----------------------------+------------+---------------------------------+
| statement/com/Execute | 347121716 | 16.46 h |
| statement/sql/commit | 119865 | 5.70 m |
| statement/sql/create_index | 8 | 2.52 m |
| statement/sql/alter_table | 20 | 12.87 s |
| statement/sql/drop_db | 1 | 4.31 s |
+----------------------------+------------+---------------------------------+
5 rows in set (0.00 sec)
等待时间
mysql> select EVENT_NAME,COUNT_STAR,sys.format_time(SUM_TIMER_WAIT) from events_waits_summary_global_by_event_name where EVENT_NAME!='idle' order by SUM_TIMER_WAIT desc limit 5;
+---------------------------------------------+------------+---------------------------------+
| EVENT_NAME | COUNT_STAR | sys.format_time(SUM_TIMER_WAIT) |
+---------------------------------------------+------------+---------------------------------+
| wait/io/socket/sql/client_connection | 694486374 | 3.19 h |
| wait/synch/sxlock/innodb/index_tree_rw_lock | 43122267 | 2.79 h |
| wait/synch/mutex/innodb/buf_pool_mutex | 85979811 | 1.61 h |
| wait/synch/sxlock/innodb/btr_search_latch | 677902342 | 38.38 m |
| wait/io/file/innodb/innodb_data_file | 8312016 | 27.85 m |
+---------------------------------------------+------------+---------------------------------+
5 rows in set (0.01 sec)
优化过程
停掉数据库
[root@cnbugs1 ~]# /etc/init.d/mysqld57 stop
修改my.cnf配置文件
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = -1
启动数据库
[root@cnbugs1 ~]# /etc/init.d/mysqld57 start
重新进行压测
[root@cnbugs1 ~]# cd /usr/local/tpcc-mysql/
[root@cnbugs1 tpcc-mysql]# ./tpcc_start -h127.0.0.1 -P 3306 -d tpcc -u root -p Aa123456 -w 10 -c 1 -r 300 -l 200
优化后的数据
<Constraint Check> (all must be [OK])
[transaction percentage]
Payment: 43.48% (>=43.0%) [OK]
Order-Status: 4.35% (>= 4.0%) [OK]
Delivery: 4.35% (>= 4.0%) [OK]
Stock-Level: 4.35% (>= 4.0%) [OK]
[response time (at least 90% passed)]
New-Order: 31.09% [NG] *
Payment: 98.50% [OK]
Order-Status: 100.00% [OK]
Delivery: 99.38% [OK]
Stock-Level: 91.31% [OK]
<TpmC>
4865.700 TpmC
语句执行时间
mysql> select EVENT_NAME,COUNT_STAR,sys.format_time(SUM_TIMER_WAIT) from events_statements_summary_global_by_event_name order by SUM_TIMER_WAIT desc limit 5;
+--------------------------+------------+---------------------------------+
| EVENT_NAME | COUNT_STAR | sys.format_time(SUM_TIMER_WAIT) |
+--------------------------+------------+---------------------------------+
| statement/com/Execute | 3275878 | 4.45 m |
| statement/sql/commit | 128815 | 1.83 m |
| statement/sql/rollback | 413 | 681.95 ms |
| statement/com/Prepare | 35 | 10.01 ms |
| statement/com/Field List | 174 | 8.66 ms |
+--------------------------+------------+---------------------------------+
5 rows in set (0.00 sec)
等待时间
mysql> select EVENT_NAME,COUNT_STAR,sys.format_time(SUM_TIMER_WAIT) from events_waits_summary_global_by_event_name where EVENT_NAME!='idle' order by SUM_TIMER_WAIT desc limit 5;
+----------------------------------------+------------+---------------------------------+
| EVENT_NAME | COUNT_STAR | sys.format_time(SUM_TIMER_WAIT) |
+----------------------------------------+------------+---------------------------------+
| wait/io/file/innodb/innodb_log_file | 244059 | 1.75 m |
| wait/io/table/sql/handler | 6101334 | 1.23 m |
| wait/io/file/innodb/innodb_data_file | 165539 | 36.33 s |
| wait/io/socket/sql/client_connection | 6810727 | 35.82 s |
| wait/synch/mutex/innodb/buf_pool_mutex | 4267691 | 3.31 s |
+----------------------------------------+------------+---------------------------------+
5 rows in set (0.00 sec)
从优化后可以看出节省了很多时间！
声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。
