梳理运营指标
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的值