mysql io吞吐量_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

测试结果

(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]

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)