盘点每天MySQL巡检监控项内容

发布时间：2023-05-11 14:26:08 所属栏目：MySql教程来源：

导读：记录一下线上业务数据库每天需要监控的参数和变量情况，声明下：每家公司需要监控的状态变量均不完全相同，本文仅是针对作者自己负责的业务数据库而言，另外，其中很多的参数均是从MySQL官网摘取的英文译著，对其中的

记录一下线上业务数据库每天需要监控的参数和变量情况，声明下：每家公司需要监控的状态变量均不完全相同，本文仅是针对作者自己负责的业务数据库而言，另外，其中很多的参数均是从MySQL官网摘取的英文译著，对其中的错误之处，望请指正！

InnoDB pages监控
total（显示了缓冲池总共有多少个页，即Buffer pool size，页数x页大小）

data（Database page，显示了分配用来存储数据库页的页数，即，表示LRU列表中页的数量,包含young sublist和old sublist）

dirty（Modified db pages，显示脏数据库页数）

flushed（Innodb_buffer_pool_pages_flushed）

free（Free buffers，显示了缓冲池空闲页数）

InnoDB IO监控
MysqL> show status like 'innodb_buffer_pool_read%';
| Innodb_buffer_pool_read_requests | 1085462370751 |
| Innodb_buffer_pool_reads | 31655 |
+---------------------------------------+---------------+
Innodb_buffer_pool_read_requests
The number of logical read requests
Innodb_buffer_pool_reads
The number of logical reads that InnoDB Could not satisfy from the buffer pool, and had to read directly from disk

Innodb_buffer_read_hits=
(1-innodb_buffer_pool_reads/ innodb_buffer_pool_read_requests)*100%

MysqL> show status like 'innodb_buffer_pool_pages_flushed';
+----------------------------------+----------+
| Variable_name | Value |
+----------------------------------+----------+
| Innodb_buffer_pool_pages_flushed | 16896625 |
+----------------------------------+----------+
The number of requests to flush pages from the InnoDB buffer pool.

InnoDB_rows监控
MysqL> show global status like 'innodb_rows_%';
+----------------------+---------------+
| Variable_name | Value |
+----------------------+---------------+
| Innodb_rows_deleted | 4377501 |
| Innodb_rows_inserted | 13328425 |
| Innodb_rows_read | 1592292250994 |
| Innodb_rows_updated | 8069672 |
+----------------------+---------------+

Innodb_rows_deleted
The number of rows deleted from InnoDB tables.
Innodb_rows_inserted
The number of rows inserted into InnoDB tables.
Innodb_rows_read
The number of rows read from InnoDB tables.
Innodb_rows_updated
The number of rows updated in InnoDB tables.

键缓存监控
Key_blocks_used命中率：
Key_blocks_used/( Key_blocks_used+ Key_blocks_unused)x100%

MysqL> show status like 'key%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| Key_blocks_unused | 25210 |
| Key_blocks_used | 7907 |

Key_blocks_unused
The number of unused blocks in the MyISAM key cache. You can use this value to determine how much of the key cache is in use; see the discussion of key_buffer_size
Key_blocks_used
The number of used blocks in the MyISAM key cache. This value is a high-water mark that indicates the maximum number of blocks that have ever been in use at one time.

Key_buffer_read命中率：
1-key_reads/key_read_requestsx100%

MysqL> show status like 'key%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_read_requests | 572800837 |
| Key_reads | 474655 |

Key_read_requests
The number of requests to read a key block from the MyISAM key cache
Key_reads
The number of physical reads of a key block from disk into the MyISAM key cache. If Key_reads is large, then your key_buffer_size value is probably too small. The cache miss rate can be calculated as Key_reads/Key_read_requests

Key_buffer_write命中率：
1-key_writes/key_write_requestsx100%

MysqL> show status like 'key%';
+------------------------+-----------+
| Variable_name | Value |
+------------------------+-----------+
| Key_write_requests | 135684306 |
| Key_writes | 3233824 |
+------------------------+-----------+
Key_write_requests
The number of requests to write a key block to the MyISAM key cache
Key_writes
The number of physical writes of a key block from the MyISAM key cache to disk

查看Threads使用情况
MysqL> show global status like 'thread%';
+-------------------+--------+
| Variable_name | Value |
+-------------------+--------+
| Threads_cached | 96 |
| Threads_connected | 4 |
| Threads_created | 225461 |
| Threads_running | 1 |
+-------------------+--------+

Threads_cached
The number of threads in the thread cache.
Threads_connected
The number of currently open connections.
Threads_created
The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections.
Threads_running
The number of threads that are not sleeping.

Thread_cache命中率
1 - Threads_created / connections x 100%

MysqL> show global status like 'thread%';
| Threads_created | 225461 |
The number of threads created to handle connections. If Threads_created is big, you may want to increase the thread_cache_size value. The cache miss rate can be calculated as Threads_created/Connections

MysqL> show global status like 'connections';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Connections | 86073503 |
+---------------+----------+
1 row in set (0.00 sec)
The number of connection attempts (successful or not) to the MysqL server

查看QPS-TPS情况
QPS计算方式：
QPS= Questions/Uptime

MysqL> show global status like 'questions';
+---------------+------------+
| Variable_name | Value |
+---------------+------------+
| Questions | 15674599428 |
+---------------+------------+
The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.
The discussion at the beginning of this section indicates how to relate this statement-counting status variable to other such variables

MysqL> show global status like 'Uptime';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| Uptime | 13609186 |
+---------------+----------+
The number of seconds that the server has been up

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!