mysql配置内核参数_MySQL核心参数优化（IO优化）配置示例

1、测算单位时间内产生的redo log大小

先执行压测

[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

测试结果

(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.35% [NG] *

Payment: 98.45% [OK]

Order-Status: 100.00% [OK]

Delivery: 99.94% [OK]

Stock-Level: 92.53% [OK]

4934.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 | 3312255 | 4.49 m |

| statement/sql/commit | 130064 | 1.80 m |

| statement/sql/select | 5 | 1.00 m |

| statement/sql/rollback | 391 | 729.26 ms |

| statement/com/Prepare | 35 | 9.60 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 | 246380 | 1.71 m |

| wait/io/table/sql/handler | 6169726 | 1.20 m |

| wait/synch/cond/sql/Item_func_sleep::cond | 12 | 1.00 m |

| wait/io/socket/sql/client_connection | 6885774 | 35.39 s |

| wait/io/file/innodb/innodb_data_file | 169807 | 35.08 s |

+-------------------------------------------+------------+---------------------------------+

5 rows in set (0.00 sec)

测算单位时间(60s)产生的redo log大小

mysql> show global status like 'Innodb_os_log_written';select sleep(60);show global status like 'innodb_os_log_written';

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 5

Current database: *** NONE ***

+-----------------------+----------+

| Variable_name | Value |

+-----------------------+----------+

| Innodb_os_log_written | 32330240 |

+-----------------------+----------+

1 row in set (0.00 sec)

+-----------+

| sleep(60) |

+-----------+

| 0 |

+-----------+

1 row in set (1 min 0.00 sec)

+-----------------------+-----------+

| Variable_name | Value |

+-----------------------+-----------+

| Innodb_os_log_written | 106159104 |

+-----------------------+-----------+

1 row in set (0.00 sec)

每小时产生的redo大小

mysql> select (106159104 - 32330240)/1024/1024;

+----------------------------------+

| (106159104 - 32330240)/1024/1024 |

+----------------------------------+

| 70.40869141 |

+----------------------------------+

1 row in set (0.00 sec)

一小时产生的日志量

mysql> select (106159104 - 32330240)*60/1024/1024/1024;

+------------------------------------------+

| (106159104 - 32330240)*60/1024/1024/1024 |

+------------------------------------------+

| 4.125509262085 |

+------------------------------------------+

1 row in set (0.00 sec)

1、修改redo相关参数

innodb_log_buffer_size = 128M

innodb_flush_log_at_trx_commit = 2

innodb_flush_log_at_timeout = 10

innodb_log_file_size = 2g

innodb_log_files_in_group = 2

2、重启mysql数据库

[root@cnbugs1 tpcc-mysql]# /etc/init.d/mysqld57 restart

3、压测(需要使用vmstat 1来查看资源使用情况，没有等待进程才可再次进行压测，以免影响压测数据)

[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: 46.17% [NG] *

Payment: 99.99% [OK]

Order-Status: 100.00% [OK]

Delivery: 100.00% [OK]

Stock-Level: 94.07% [OK]

6330.300 TpmC

获取执行时间

(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: 46.17% [NG] *

Payment: 99.99% [OK]

Order-Status: 100.00% [OK]

Delivery: 100.00% [OK]

Stock-Level: 94.07% [OK]

6330.300 TpmC

获取等待时间

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 | 7788862 | 1.54 m |

| wait/io/socket/sql/client_connection | 8693303 | 43.59 s |

| wait/io/file/innodb/innodb_data_file | 133153 | 25.63 s |

| wait/io/file/innodb/innodb_log_file | 160337 | 8.82 s |

| wait/lock/table/sql/handler | 4238304 | 4.06 s |

+--------------------------------------+------------+---------------------------------+

5 rows in set (0.01 sec)