MySQL核心参数优化（其他IO优化）
二进制日志
# 取0或者1
#取0 mysql自己控制
#取1 每个事物刷新一次
sync_binlog脏页刷新吞吐量
# 默认值200，机械硬盘的默认值，
innodb_io_capacity = 10000
# 每秒最大刷新的脏页数量
innodb_io_capacity_max = 15000每秒刷新脏页到磁盘的数量，和磁盘的IOPS 10000） （fio工具测试）
修改redo相关参数
innodb_flush_method = fsync
sync_binlog = 0
innodb_io_capacity = 10000
innodb_io_capacity_max = 15000重启mysql服务
[root@cnbugs1 tpcc-mysql]# /etc/init.d/mysqld57 restart
压测
[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: 42.67% [NG] *
Payment: 99.97% [OK]
Order-Status: 100.00% [OK]
Delivery: 100.00% [OK]
Stock-Level: 90.69% [OK]
<TpmC>
6025.200 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 | 4032941 | 5.59 m |
| statement/sql/commit | 158480 | 16.15 s |
| statement/sql/rollback | 516 | 217.61 ms |
| statement/com/Prepare | 35 | 9.52 ms |
| statement/com/Field List | 87 | 4.66 ms |
+--------------------------+------------+---------------------------------+
5 rows in set (0.01 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/table/sql/handler | 7513462 | 1.26 m |
| wait/io/socket/sql/client_connection | 8384187 | 42.97 s |
| wait/io/file/innodb/innodb_data_file | 134344 | 19.21 s |
| wait/io/file/innodb/innodb_log_file | 149838 | 6.31 s |
| wait/lock/table/sql/handler | 4087593 | 3.98 s |
+--------------------------------------+------------+---------------------------------+
5 rows in set (0.00 sec)
声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。
