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

测试结果

<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.35%  [NG] *
        Payment: 98.45%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 99.94%  [OK]
    Stock-Level: 92.53%  [OK]

<TpmC>
                 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

压测结果

<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: 46.17%  [NG] *
        Payment: 99.99%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 94.07%  [OK]

<TpmC>
                 6330.300 TpmC

获取执行时间

<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: 46.17%  [NG] *
        Payment: 99.99%  [OK]
   Order-Status: 100.00%  [OK]
       Delivery: 100.00%  [OK]
    Stock-Level: 94.07%  [OK]

<TpmC>
                 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)

Mr丶点心永久会员

打赏 收藏 海报 链接