mysql 性能监控

QPS
QPS(每秒 Query 量 ) 里 的 QPS 实际上是指 MySQL Server 每秒执行的 Query总量,在 MySQL 5.1.30 及以下版本可以通过 Questions 状态值每秒内的变化量来近似表示,而从 MySQL 5.1.31 开始,则可以通过 Queries 来表示。Queries 是在 MySQL 5.1.31 才新增的状态变量。主要解决的问题就是 Questions 状态变量并没有记录存储过程中所执行的 Query(当然,在无存储过程的老版本 MySQL 中则不存在这个区别),而 Queries 状态变量则会记录。二者获取方式:
QPS = Questions(or Queries) / Seconds
获取所需状态变量值:
SHOW /*!50000 GLOBAL */ STATUS LIKE 'Questions'
SHOW /*!50000 GLOBAL */ STATUS LIKE 'Queries'


Innodb Buffer 命中率

这里 Innodb Buffer 所指的是 innodb_buffer_pool,也就是用来缓存 Innodb 类型表的数据和索引的内存空间。类似 Key buffer,我们同样可以根据 MySQL Server 提供的相应状态值计算出其命中率:
innodb_buffer_read_hits=(1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

获取所需状态变量值:
sky@localhost : (none) 08:25:14> SHOW /*!50000 GLOBAL*/ STATUS
-> LIKE 'Innodb_buffer_pool_read%';
+-----------------------------------+-------+
| Variable_name                    | Value |
+-----------------------------------+-------+
... ...
| Innodb_buffer_pool_read_requests | 5367 |
| Innodb_buffer_pool_reads         | 507 |
+-----------------------------------+-------+

Thread Cache 命中率
Thread Cache 命中率:Thread Cache 命中率能够直接反应出我们的系统参数，thread_cache_size 设置的是否合理。一个合理的 thread_cache_size 参数能够节约大量创建新连接时所需要消耗的资源。Thread Cache 命中率计算方式如下:
Thread_cache_hits = (1 - Threads_created / Connections) * 100%

获取所需状态变量值:
sky@localhost : (none) 08:57:16> SHOW /*!50000 GLOBAL*/ STATUS LIKE 'Thread%';

sky@localhost : (none) 09:01:33> SHOW /*!50000 GLOBAL*/ STATUS LIKE 'Connections';
正常来说,Thread Cache 命中率要在 90% 以上才算比较合理。


锁定状态:
锁定状态包括表锁和行锁两种,我们可以通过系统状态变量获得锁定总次数,锁定造成其他线程等待的次数,以及锁定等待时间信息。

sky@localhost : (none) 09:01:44> SHOW /*!50000 GLOBAL*/ STATUS
-> LIKE '%lock%';

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
... ...
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time          | 0 |
| Innodb_row_lock_time_avg      | 0 |
| Innodb_row_lock_time_max      | 0 |
| Innodb_row_lock_waits         | 0 |
... ...
| Table_locks_immediate         | 44|
| Table_locks_waited            | 0 |
+-------------------------------+-------+

通过上述系统变量,我们可以得出表锁总次数,其中造成其他现线程等待的次数。同时还可以得到非常详细的行锁信息,如行锁总次数,行锁总时间,每次行锁等待时间,行锁造成最大等待时间以及当前等待行锁的线程数。通过对这些量的监控,
我们可以清晰的了解到系统整体的锁定是否严重。如当 Table_locks_waited 与Table_locks_immediate 的比值较大,
则说明我们的表锁造成的阻塞比较严重,可能需要调整 Query 语句,或者更改存储引擎,亦或者需要调整业务逻辑。当然,
具体改善方式必须根据实际场景来判断。而 Innodb_row_lock_waits 较大,则说明 Innodb 的行锁也比较严重,且影响了其他线程的正常处理。同样需要查找出原因并解决。造成 Innodb 行锁严重的原因可能是 Query 语句所利用的索引不够合理(Innodb 行锁是基于索引来锁定的),造成间隙锁过大。也可能是系统本身处理能力有限,则需要从其他方面(如硬件设备)来考虑解决。

复制延时量
复制延时量将直接影响了 Slave 数据库处于不一致状态的时间长短。如果我们是通过 Slave 来提供读服务,就不得不重视这个延时量。我们可以通过在 Slave 节点上执行“SHOW SLAVE STATUS”命 令, Seconds_Behind_Master 项
取的值来了解 Slave 当前的延时量(单位:秒)。当然,该值的准确性依赖于复制是否处于正常状态。每个环境下的 Slave 所允许的延时长短与具体环境有关,所以复制延时多长时间是合理的,只能由读者朋友根据各自实际的应用环境来判断。


Tmp table 状况

Tmp Table 的状况主要是用于监控 MySQL 使用临时表的量是否过多,是否有临时表过大而不得不从内存中换出到磁盘文件上。临时表使用状态信息可以通过如下方式获得:

SHOW /*!50000 GLOBAL*/ STATUS LIKE 'Created_tmp%';
| Created_tmp_disk_tables | 1|
Created_tmp_tables|46|

从上面的状态信息可以了解到系统使用了 46 次临时表,其中有 1 次临时表比较大,无法在内存中完成,而不得不使用到磁盘文件。如果 Created_tmp_tables 非常大 ,则可能是系统中排序操作过多,或者是表连接方式不是很优化。而如果是Created_tmp_disk_tables 与 Created_tmp_tables 的比率过高,如超过 10%,则我们需要考虑是否 tmp_table_size 这个系统参数所设置的足够大。当然,如果系统内存有限,也就没有太多好的解决办法了。