一、常见监控类指标

1、性能类指标

名称	说明
QPS	数据库每秒处理的请求数量
TPS	数据库每秒处理的事务数量
并发数	数据库实例当前并行处理的会话数量
连接数	连接到数据库会话的数量
缓存命中率	主要指 InnoDB的缓存命中率

2、功能类指标

名称	说明
可用性	数据库是否可以正常对外提供服务
阻塞	当前是否有阻塞的会话
死锁	当前事务是否产生了死锁
慢查询
主从延迟	主从延迟的时间
主从状态	主从复制链路是否正常

　

二、逐一指标分析

1、QPS

（1）说明

QPS，数据库每秒处理的请求数量。

（2）查看自数据库实例启动以来，各项操作的数量

Com是MySQL提供的一个计数器，数据库实例启动后的每个操作都会记录在这里

mysql> SHOW GLOBAL STATUS like 'Com%';
+-------------------------------------+-------+
| Variable_name                       | Value |
+-------------------------------------+-------+
| Com_admin_commands                  | 0     |
| Com_assign_to_keycache              | 0     |
| Com_alter_db                        | 0     |
| Com_alter_event                     | 0     |
| Com_alter_function                  | 0     |
| Com_alter_instance                  | 0     |
| Com_alter_procedure                 | 0     |
| Com_alter_resource_group            | 0     |
| Com_alter_server                    | 0     |
| Com_alter_table                     | 11    |
| Com_alter_tablespace                | 0     |
| Com_alter_user                      | 0     |
| Com_alter_user_default_role         | 0     |
| Com_analyze                         | 0     |
| Com_begin                           | 0     |
| Com_binlog                          | 0     |
| Com_call_procedure                  | 0     |
| Com_change_db                       | 1302  |
| Com_change_master                   | 0     |
| Com_change_repl_filter              | 0     |
| Com_change_replication_source       | 0     |
| Com_check                           | 4     |
| Com_checksum                        | 0     |
| Com_clone                           | 0     |
| Com_commit                          | 0     |
| Com_create_db                       | 4     |
| Com_create_event                    | 0     |
| Com_create_function                 | 0     |
| Com_create_index                    | 0     |
| Com_create_procedure                | 0     |
| Com_create_role                     | 4     |
| Com_create_server                   | 0     |
| Com_create_table                    | 51    |
| Com_create_resource_group           | 0     |
| Com_create_trigger                  | 0     |
| Com_create_udf                      | 0     |
| Com_create_user                     | 3     |
| Com_create_view                     | 0     |
| Com_create_spatial_reference_system | 0     |
| Com_dealloc_sql                     | 0     |
| Com_delete                          | 0     |
| Com_delete_multi                    | 0     |
| Com_do                              | 0     |
| Com_drop_db                         | 0     |
| Com_drop_event                      | 0     |
| Com_drop_function                   | 0     |
| Com_drop_index                      | 0     |
| Com_drop_procedure                  | 0     |
| Com_drop_resource_group             | 0     |
| Com_drop_role                       | 2     |
| Com_drop_server                     | 0     |
| Com_drop_spatial_reference_system   | 0     |
| Com_drop_table                      | 6     |
| Com_drop_trigger                    | 0     |
| Com_drop_user                       | 0     |
| Com_drop_view                       | 0     |
| Com_empty_query                     | 0     |
| Com_execute_sql                     | 0     |
| Com_explain_other                   | 0     |
| Com_flush                           | 12    |
| Com_get_diagnostics                 | 0     |
| Com_grant                           | 6     |
| Com_grant_roles                     | 6     |
| Com_ha_close                        | 0     |
| Com_ha_open                         | 0     |
| Com_ha_read                         | 0     |
| Com_help                            | 3     |
| Com_import                          | 0     |
| Com_insert                          | 33    |
| Com_insert_select                   | 0     |
| Com_install_component               | 0     |
| Com_install_plugin                  | 0     |
| Com_kill                            | 0     |
| Com_load                            | 0     |
| Com_lock_instance                   | 1     |
| Com_lock_tables                     | 13    |
| Com_optimize                        | 0     |
| Com_preload_keys                    | 0     |
| Com_prepare_sql                     | 0     |
| Com_purge                           | 0     |
| Com_purge_before_date               | 0     |
| Com_release_savepoint               | 0     |
| Com_rename_table                    | 2     |
| Com_rename_user                     | 0     |
| Com_repair                          | 1     |
| Com_replace                         | 0     |
| Com_replace_select                  | 0     |
| Com_reset                           | 0     |
| Com_resignal                        | 0     |
| Com_restart                         | 0     |
| Com_revoke                          | 1     |
| Com_revoke_all                      | 0     |
| Com_revoke_roles                    | 0     |
| Com_rollback                        | 0     |
| Com_rollback_to_savepoint           | 0     |
| Com_savepoint                       | 0     |
| Com_select                          | 1001  |
| Com_set_option                      | 692   |
| Com_set_password                    | 0     |
| Com_set_resource_group              | 0     |
| Com_set_role                        | 0     |
| Com_signal                          | 0     |
| Com_show_binlog_events              | 0     |
| Com_show_binlogs                    | 0     |
| Com_show_charsets                   | 6     |
| Com_show_collations                 | 3     |
| Com_show_create_db                  | 65    |
| Com_show_create_event               | 0     |
| Com_show_create_func                | 0     |
| Com_show_create_proc                | 0     |
| Com_show_create_table               | 527   |
| Com_show_create_trigger             | 4     |
| Com_show_databases                  | 12    |
| Com_show_engine_logs                | 0     |
| Com_show_engine_mutex               | 0     |
| Com_show_engine_status              | 1     |
| Com_show_events                     | 7     |
| Com_show_errors                     | 0     |
| Com_show_fields                     | 317   |
| Com_show_function_code              | 0     |
| Com_show_function_status            | 7     |
| Com_show_grants                     | 83    |
| Com_show_keys                       | 16    |
| Com_show_master_status              | 0     |
| Com_show_open_tables                | 0     |
| Com_show_plugins                    | 1     |
| Com_show_privileges                 | 1     |
| Com_show_procedure_code             | 0     |
| Com_show_procedure_status           | 7     |
| Com_show_processlist                | 0     |
| Com_show_profile                    | 0     |
| Com_show_profiles                   | 0     |
| Com_show_relaylog_events            | 0     |
| Com_show_replicas                   | 0     |
| Com_show_slave_hosts                | 0     |
| Com_show_replica_status             | 0     |
| Com_show_slave_status               | 0     |
| Com_show_status                     | 116   |
| Com_show_storage_engines            | 4     |
| Com_show_table_status               | 281   |
| Com_show_tables                     | 133   |
| Com_show_triggers                   | 100   |
| Com_show_variables                  | 79    |
| Com_show_warnings                   | 504   |
| Com_show_create_user                | 80    |
| Com_shutdown                        | 0     |
| Com_replica_start                   | 0     |
| Com_slave_start                     | 0     |
| Com_replica_stop                    | 0     |
| Com_slave_stop                      | 0     |
| Com_group_replication_start         | 0     |
| Com_group_replication_stop          | 0     |
| Com_stmt_execute                    | 0     |
| Com_stmt_close                      | 0     |
| Com_stmt_fetch                      | 0     |
| Com_stmt_prepare                    | 0     |
| Com_stmt_reset                      | 0     |
| Com_stmt_send_long_data             | 0     |
| Com_truncate                        | 0     |
| Com_uninstall_component             | 0     |
| Com_uninstall_plugin                | 0     |
| Com_unlock_instance                 | 0     |
| Com_unlock_tables                   | 12    |
| Com_update                          | 18    |
| Com_update_multi                    | 0     |
| Com_xa_commit                       | 0     |
| Com_xa_end                          | 0     |
| Com_xa_prepare                      | 0     |
| Com_xa_recover                      | 0     |
| Com_xa_rollback                     | 0     |
| Com_xa_start                        | 0     |
| Com_stmt_reprepare                  | 0     |
+-------------------------------------+-------+
172 rows in set (0.01 sec)

（3）求QPS

QPS的含义是数据库每秒处理的请求数量，上面通过Com可以得到数据库实例启动以来各项操作的数量，但还需要求和，比较麻烦，因此我们使用MySQL另外提供的一个计数器Queries。

mysql> SHOW GLOBAL status LIKE 'Queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Queries       | 5566  |
+---------------+-------+
1 row in set (0.00 sec)

通过该语句可以查询自数据库实例启动以来的总操作数。

想要求QPS，只要在两个时间节点分别执行SHOW GLOBAL status LIKE 'Queries';，然后用得到的值相减，除以时间间隔即可。

QPS = （Queries2 - Queries1）/ 时间间隔秒数

　

2、TPS

（1）说明

TPS，数据库每秒处理的事务数量。

（2）求TPS

mysql> SHOW GLOBAL STATUS WHERE Variable_name in ('com_insert', 'com_update', 'com_delete');
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_delete    | 0     |
| Com_insert    | 33    |
| Com_update    | 18    |
+---------------+-------+
3 rows in set (0.01 sec)

在两个时间节点分别执行上面语句获得DML总和，然后除以时间间隔秒数，即可获取TPS

TPS = （sum_dml_2 - sum_dml_2）/ 时间间隔秒数

　

3、并发数

（1）说明

数据库并发数是指同时在执行的会话的数量。

与连接数的区别在于，连接数包括没有在处理请求处于sleep状态的连接，而并发数仅包括正在处理请求的连接。

通常来说，并发数越大，数据库负载越大。

（2）获取当前数据库实例的并发数

mysql> SHOW GLOBAL STATUS LIKE 'Threads_running';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| Threads_running | 2     |
+-----------------+-------+
1 row in set (0.01 sec)

　

4、连接数

（1）说明

数据库连接数指当前同数据库建立了连接的会话的总数量。该参数包括正在执行的线程和已经处在sleep状态的线程。

MySQL可以接受的最大连接数取决于参数max_connections，超过最大连接数，新的请求就无法与数据库建立连接了。

如果当前连接数达到max_connections的80%，DBA就应该注意了。

（2）获取当前数据库实例的连接数

mysql> SHOW GLOBAL STATUS LIKE 'Threads_connected';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 2     |
+-------------------+-------+
1 row in set (0.00 sec)

　

５、InnoDB缓存命中率

（1）说明

InnoDB是当前最常用的存储引擎，为了提高处理效率，MySQL InnoDB存储引擎在操作数据前，会先把数据读取到缓存中，如果我们操作的数据可以直接在缓存中命中，也就省去了从磁盘读数据的步骤，进而大大提升数据库的处理效率。

有多少操作可以在缓存中命中，就是InnoDB缓存命中率指标。

在高负载下，该值应该处于95%以上，如果没有处于该阀值，就应该考虑增加InnoDB缓冲池大小了。

（2）获取缓存命中率

# 从缓冲池中读取数据的次数 + 从物理磁盘读取数据的次数
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| Innodb_buffer_pool_read_requests | 128447 |
+----------------------------------+--------+
1 row in set (0.00 sec)

# 从物理磁盘读取数据的次数
mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 1286  |
+--------------------------+-------+
1 row in set (0.00 sec)

# 纯粹从缓冲池读取数据的次数
Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads

# 获取InnoDB缓存命中率
（Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads）/ Innodb_buffer_pool_read_requests * 100%

　

6、可用性

（1）说明

可用性指MySQL是否可以正常对外提供服务。

（2）如何监控数据库可用性

方式1：周期性连接数据库实例，并执行 select @@version;

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 8.0.26    |
+-----------+
1 row in set (0.00 sec)

如果可以正常返回结果，则说明MySQL可以正常对外提供服务，否则说明MySQL已经不能正常对外提供服务了。

方式2：在Linux上执行 mysqladmin -uroot -p -hlocalhost ping

root@72b7d0258d58:/# mysqladmin -uroot -p -hlocalhost ping
Enter password: 
mysqld is alive

　

7、阻塞

（1）说明

阻塞通常由于多个线程要对同一个资源加排它锁造成的。

第一个线程对某资源加了排它锁，其他线程再想对该资源加排它锁，就只能等上一个进程执行结束释放锁之后才能加排它锁，这也就导致后面的线程会一直等待，进而造成阻塞。

（2）获取阻塞

（2.1）如果MySQL版本 <= 5.7版本

SELECT 
    b.trx_mysql_thread_id as '被阻塞线程',
    b.trx_query as '被阻塞SQL',
    c.trx_mysql_thread_id as '阻塞线程',
    c.trx_query as '阻塞SQL',
    (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started)) as '阻塞时间'
FROM
    information_schema.innodb_lock_waits as a
JOIN
    information_schema.innodb_trx as b on a.requesting_trx_id=b.trx_id
JOIN
    information_schema.innodb_trx as c on a.blocking_trx_id=c.trx_id
WHERE
    (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(c.trx_started)) > 30
;

我们可以通过kill杀掉阻塞的线程来解决阻塞。

（2.2）如果MySQL版本 >= 5.7

SELECT 
    waiting_pid as '被阻塞线程',
    waiting_query as '被阻塞SQL',
    blocking_pid as '阻塞线程',
    blocking_query as '阻塞SQL',
    wait_age as '阻塞时间'
FROM
    sys.innodb_lock_waits
WHERE
    (UNIX_TIMESTAMP() - UNIX_TIMESTAMP(wait_started)) > 30
;

我们可以通过kill杀掉阻塞的线程来解决阻塞。

　

8、死锁

（1）说明

两个并发事务相互之间锁住了对方需要的资源，这时就会产生死锁。死锁的解决只能是一方放弃执行，这样死锁就得到了解决。

MySQL内部会对死锁进行监控并处理，其会主动回滚两个事务中占用资源比较小的事务，让另一个事务可以继续执行，这些不需要我们人为干预。

那为什么我们还要对死锁进行监控呢？这是因为死锁虽然不会对数据库带来太大影响，但是有时会对我们的业务带来一定影响。

（2）获取当前数据库实例的死锁

方式1：通过设置innodb_print_all_deadlocks将死锁存储到错误日志

set global innodb_print_all_deadlocks=ON;
# 之后就可以通过查看错误日志来查看死锁了

方式2：通过工具pt-deadlock-logger

pt-deadlock-logger u=root,p=123456,h=127.0.0.1
    --create-dest-table
    --dest u=root,p=123456,h=127.0.0.1,D=crn,t=deadlock

注：

pt-deadlock-logger u=root,p=123456,h=127.0.0.1指定了查看哪个数据的死锁，
--create-dest-table指定了查看的同时要创建表
--dest u=root,p=123456,h=127.0.0.1,D=crn,t=deadlock 指定了在哪个数据库创建表，同时指定存储死锁的表名

　

９、慢查询

（1）说明

慢查询记录了执行慢的SQL语句，多用于SQL优化。

（2）慢查询监控

方式1：通过慢查询日志监控

方式2：通过infomation_schema数据库下的PROCESSLIST表实时监控。

SELECT * FROM nformation_schema.PROCESSLIST WHERE time > 60 AND command = 'Query';

　

10、主从延时的监控

（1）方式1：通过命令show slave status进行监控

在输出结果中找到 Seconds_Behind_Master 一列的值，即主从延时。

注：通过该方式得到的主从延迟值存在一定误差，一是由于大事务，二是由于系统时间不同步导致。

（2）方式2：通过pt-heartbeat工具进行监控

# 周期性写入监控数据
pt-heartbeat --user=xx --password=xxx -h master --create-table --database xxx --update --daemonize --interval=1

# 周期性读取监控数据进行比较
pt-heartbeat --user=xx --password=xxx -h slave --database crn --monitor --daemonize --log /tmp/slave_lag.log 

　

11、监控主从状态

通过show slave status命令来进行监控。

如果结果集中的 slave_IO_Running 和 slave_SQL_Running 两列均未yes，说明主从复制链路是正常的。