梳理运营指标

a)、表锁信息

mysql> show global  status like 'table_lock%';

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

| Variable_name         | Value  |

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

| Table_locks_immediate | 323179 |

| Table_locks_waited    | 0      |

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

这两个值的比值：Table_locks_waited /Table_locks_immediate 趋向于0，如果值比较大则表示系统的锁阻塞情况比较严重

b)、临时表的使用情况

mysql> show global status like 'Created_tmp%';

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

| Variable_name           | Value    |

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

| Created_tmp_disk_tables | 1190897  |

| Created_tmp_files       | 31335    |

| Created_tmp_tables      | 35798544 |

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

3 rows in set (0.00 sec)

 

mysql> show variables like '%tmp_table_size%';

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

| Variable_name  | Value     |

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

| tmp_table_size | 100663296 |

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

1 row in set (0.01 sec)

 Created_tmp_disk_tables 是涉及到了磁盘io，Created_tmp_tables创建的临时表总数；

Created_tmp_disk_tables/Created_tmp_tables的比列越大，说明需要优化sql或者增大tmp_table_size

c)、 binlog Cache使用情况

mysql> show status like 'Binlog_cache%';

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

| Variable_name         | Value    |

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

| Binlog_cache_disk_use | 13020    |

| Binlog_cache_use      | 89384513 |

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

2 rows in set (0.00 sec)

mysql> show variables like 'binlog_cache_size';

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

| Variable_name     | Value   |

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

| binlog_cache_size | 4194304 |

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

Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数

Binlog_cache_use  表示 用binlog_cache_size缓存的次数

当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值

 

d)、 Innodb log buffer size的大小设置

mysql> show status like 'innodb_log_waits'；

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

| Variable_name    | Value |

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

| Innodb_log_waits | 0     |

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

1 row in set (0.00 sec)

 

mysql>  show variables like '%innodb_log_buffer_size%';

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

| Variable_name          | Value   |

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

| innodb_log_buffer_size | 8388608 |

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

1 row in set (0.01 sec)

innodb_log_buffer_size我设置了8M，应该足够大了；Innodb_log_waits表示因log buffer不足导致等待的次数，如果该值不为0，可以适当增大innodb_log_buffer_size的值