数据库巡检

 

数据库巡检的内容通常涵盖主机硬件、操作系统和MySQL巡检项。其中，主机/os巡检主要包括：主机的硬件配置、CPU/内存/磁盘使用率以及磁盘的I/O使用情况；MySQL巡检项包括：数据库配置、用户权限、大表数据量、业务表主键和自增长情况、数据库的并发性、当前和历史连接情况统计、备份执行情况以及日志记录和慢SQL的分析优化等。

 

 

1、查看MySQL服务器配置信息及运行状况

 

通过show variables来查看mysql服务器配置信息，例如show variables like '%slow%'；用于查看慢查询，show variables like 'max_connections';；用于查看最大连接数。

 

通过ps -ef | grep mysql查看mysql进程运行状况。

 

2、通过show status统计各种SQL的执行频率

 

通过show status可以查看服务器状态信息。show status可以根据需要显示session 级别的统计结果和global级别的统计结果。

1）以下几个参数对Myisam和Innodb存储引擎都计数：

    Com_select 执行select 操作的次数，一次查询只累加1；

    Com_insert 执行insert 操作的次数，对于批量插入的insert 操作，只累加一次；

    Com_update 执行update 操作的次数；

    Com_delete 执行delete 操作的次数。

 

 

2）以下几个参数是针对Innodb存储引擎计数的，累加的算法也略有不同：

 

    Innodb_rows_read 执行select 查询返回的行数；

    Innodb_rows_inserted 执行insert 操作插入的行数；

    Innodb_rows_updated 执行update 操作更新的行数；

    Innodb_rows_deleted 执行delete 操作删除的行数。

 

通过以上几个参数，可以很容易的了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会累加。

 

对于事务型的应用，通过Com_commit和Com_rollback可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

 

3）以下几个参数便于我们了解数据库的基本情况：

 

    Connections 试图连接Mysql 服务器的次数；

    Uptime 服务器工作时间；

    Slow_queries 慢查询的次数。

 

3、通过show status判断系统瓶颈

 

1）QPS(每秒Query量)

 

QPS = Questions(or Queries) / seconds

mysql > show global status like 'Question%';

 

2）TPS(每秒事务量)

 

TPS = (Com_commit + Com_rollback) / seconds

mysql > show global status like 'Com_commit';

mysql > show global status like 'Com_rollback';

 

3）key Buffer 命中率

 

mysql>show global status like 'key%';

key_buffer_read_hits = (1-key_reads / key_read_requests) * 100%

key_buffer_write_hits = (1-key_writes / key_write_requests) * 100%

 

4）InnoDB Buffer命中率

 

mysql> show status like 'innodb_buffer_pool_read%';

innodb_buffer_read_hits = (1 - innodb_buffer_pool_reads /   

innodb_buffer_pool_read_requests) * 100%

 

5）Query Cache命中率

 

mysql> show status like 'Qcache%';

Query_cache_hits = (Qcahce_hits / (Qcache_hits + Qcache_inserts )) * 100%;

 

6）Table Cache状态量

 

mysql> show global status like 'open%';

 

比较open_tables与opend_tables值。

 

7）Thread Cache 命中率

 

mysql> show global status like 'Thread%';

mysql> show global status like 'Connections';

Thread_cache_hits = (1 - Threads_created / connections ) * 100%

 

创建用来处理连接的线程数。如果 Threads_created 较大，你可能要增加 thread_cache_size 值。缓存访问率的计算方法 Threads_created/Connections。

 

8）锁定状态

 

mysql> show global status like '%lock%';

 

Table_locks_waited/Table_locks_immediate 如果这个比值比较大的话，说明表锁造成的阻塞比较严重。

 

Innodb_row_lock_waits：innodb行锁，太大可能是间隙锁造成的。

 

Table_locks_waited：不能立即获得的表的锁的次数。如果该值较高并且有性能问题，应首先优化查询，然后拆分表或使用复制。

 

9）Tmp Table 状况(临时表状况)

 

mysql >show status like 'Created_tmp%';

 

Created_tmp_disk_tables/Created_tmp_tables比值最好不要超过10%，如果Created_tmp_tables值比较大，可能是排序子句过多或者是连接子句不够优化。

 

10）Binlog Cache 使用状况

 

mysql > show status like 'Binlog_cache%';

 

如果Binlog_cache_disk_use值不为0 ，可能需要调大 binlog_cache_size大小。

 

11）Innodb_log_waits

 

mysql > show status like 'innodb_log_waits';

 

Innodb_log_waits值不等于0的话，表明 innodb log buffer 因为空间不足而等待。

 

12）连接数大小——max_connections

 

mysql> show variables like 'max_connections';

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

| Variable_name   | Value |

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

| max_connections | 500   |

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

        mysql> show global status like 'max_used_connections';

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

| Variable_name        | Value |

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

| Max_used_connections | 498   |

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

 

设置的最大连接数是500，而响应的连接数是498 。

 

max_used_connections / max_connections * 100% = 99.6% （理想值 ≈ 85%）

 

13）Handler_read_rnd

 

mysql> show status like 'Handler_read_rnd';

 

如果 Handler_read_rnd 太大 ，则你写的 SQL 语句里很多查询都是要扫描整个表，而没有发挥键的作用。

 

14）Key_reads

 

mysql> show status like 'key_read%';

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

| Variable_name     | Value  |

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

| Key_read_requests  | 1190  |

| Key_reads         | 2     |

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

 

如果 Key_reads 太大，则应该把 my.cnf 中 key_buffer_size 变大，可以用 Key_reads/Key_read_requests计算出 cache 失败率。

 

15）Handler_read_rnd

 

mysql> show status like 'Handler_read_rnd';

 

根据固定位置读一行的请求数。如果正执行大量查询并需要对结果进行排序该值较高。可能使用了大量需要MySQL 扫描整个表的查询或连接没有正确使用键。

 

16）Handler_read_rnd_next

 

mysql>show status like 'Handler_read_rnd_next';

 

在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

 

17）Select_full_join

 

mysql>show status like 'Select_full_join';

 

没有使用索引的联接的数量。如果该值不为 0, 你应仔细检查表的索引。

 

18）Select_range_check

 

mysql>show status like 'Select_range_check';

 

在每一行数据后对键值进行检查的不带键值的联接的数量。如果不为 0 ，你应仔细检查表的索引。

 

19）Sort_merge_passes

 

mysql>show status like 'Sort_merge_passes';

 

排序算法已经执行的合并的数量。如果这个变量值较大，应考虑增加 sort_buffer_size 系统变量的值。

 

20）Handler_read_first

 

mysql>show status like 'Handler_read_first';

 

索引中第一条被读的次数。如果较高，它表明服务器正执行大量全索引扫描。例如， SELECT col1 FROM foo ，假定col1 有索引。

 

21）Handler_read_key

 

mysql>show status like 'Handler_read_key';

 

根据键读一行的请求数。如果较高，说明查询和表的索引正确。

 

22）Handler_read_next

 

mysql>show status like 'Handler_read_next';

 

按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，该值增加。

 

23) Handler_read_prev

 

mysql>show status like 'Handler_read_prev';

 

按照键顺序读前一行的请求数。该读方法主要用于优化 ORDER BY ... DESC 。

 

24）Handler_read_rnd

 

mysql>show status like 'Handler_read_rnd';

 

根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL 扫描整个表的查询或你的连接没有正确使用键。

 

4、打开相应的监控信息

 

1）error log

 

在配置文件my.cnf中进行配置，在运行过程中不能改变。

 

2）打开慢查询

 

set global slow_query_log='ON';

 

3）设置慢查询的时间阈值

 

set global long_query_time = 0.1;

 

4）未使用索引的sql语句打开

 

set global log_queries_not_using_indexes='ON

 

 

 

参考：https://www.cnblogs.com/feiyun8616/p/11680010.html