MySQL Monitoring Metrics

In order to troubleshoot the problem, the monitoring of the database is essential, in this introduction of MySQL in the common monitoring indicators.

Brief introduction

MySQL has a number of branch versions, common with MySQL, Percona, MariaDB, each version of the corresponding monitoring items will be somewhat different, here only some common monitoring items.

Typically, the source code for a monitoring item is defined in the mysql/mysqld.cc file, and its contents are as follows.

Show_varStatus_vars[]={{"Aborted_clients", (char*) &aborted_threads, show_long}, {"aborted_connects" , (char*) &aborted_connects, show_long}, {"ACL" , (char*) acl_statistics, show_array}, //... }; 

Monitoring

For a database, you can typically proactively monitor the following four metrics related to performance and resource utilization:

• Query throughput
• Query execution Performance
• Connection conditions
• Buffer Pool Usage

Throughput

There are various statistics for different commands in MySQL, and its monitoring items are named in Com_xxx Way, among which more commonly used statistics include TPS/QPS.

and the MySQL related to the QPS includes three monitoring items, respectively, Queries, Questions, Com_select, the general use of Com_select as a collection item; for TPS, it is generally considered Com_insert + com_update + C Om_delete of three statistic items.

Queries and Questioins differences

The following is an introduction to these two values in Server Status Variables.

Queries : The number of statements executed by the server. This variableincludes statements executed within stored programs, unlike the Questionsvariable. It does not count COM_PING or COM_STATISTICS commands.Questions : The number of statements executed by the server. This includesonly statements sent to the server by clients and not statements executedwithin stored programs, unlike the Queries variable. This variable doesnot count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, orCOM_STMT_RESET commands.

That is, if prepared statements is not used, the difference between the two is that Questions will use the stored procedure as a statement, and Queries will count the statements executed in the stored procedure.

mysql> SHOW STATUS LIKE "questions";        ← 查看本连接执行的查询SQL数+---------------+-------+| Variable_name | Value |+---------------+-------+| Questions     | 2     |+---------------+-------+1 row in set (0.00 sec)mysql> SHOW STATUS LIKE "queries";+---------------+----------+| Variable_name | Value    |+---------------+----------+| Queries       | 21241470 |+---------------+----------+1 row in set (0.00 sec)

The above questions are session-level and can, of course, be viewed globally by global variables.

In addition, MySQL will start adding the above statistic count before executing SQL, while the number of SQL currently executing can be viewed through threads_running.

Summarize

Questions        已执行的由客户端发出的语句Com_select       执行SELECT语句的数量，通常也就是QPSWrites           Com_insert+Com_update+Com_delete，也就是TPS

Execution performance

Currently, there are several ways to view MySQL execution performance issues, which can be found in the following examples.

Performance_schema

Many key metrics are saved in the Events_statements_summary_by_digest table, in microseconds, which ignores numeric values, normalizes spaces, and capitalization.

If you want to see the average elapsed time for each database in microseconds, or the total number of error statements that appear, you can view it in the following way:

mysql> SELECT schema_name, SUM(count_star) count,        ROUND((SUM(sum_timer_wait)/SUM(count_star))/1000000) AS avg_microsec        FROM performance_schema.events_statements_summary_by_digest       WHERE schema_name IS NOT NULL GROUP BY schema_name;mysql> SELECT schema_name, SUM(sum_errors) err_count        FROM performance_schema.events_statements_summary_by_digest        WHERE schema_name IS NOT NULL GROUP BY schema_name;

Sys

The SYS storage engine is added by default in 5.7.7 and can be manually installed for 5.6 and can be referenced in detail in the Github-sys schema.

Slow query

MySQL provides a slow_queries counter that increments when the query executes longer than the value specified by the Long_query_time parameter, and the default setting is 10 seconds.

mysql> SHOW VARIABLES LIKE ‘long_query_time‘;+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)mysql> SET GLOBAL long_query_time = 5;Query OK, 0 rows affected (0.00 sec)

It is important to note that the session needs to be closed and then reconnected before the parameter can be made effective.

Summarize

Slow_queries         慢查询的数量

Others are more suitable for troubleshooting after discovering performance problems.

Connection conditions

Monitoring client connections is important because once the available connections are exhausted, new client connections are denied, and MySQL has a default connection limit of 151, which can be set in the configuration file by using the following method.

[mysqld]max_connections = 200

Query and temporary settings by using the following methods.

mysql> SHOW VARIABLES LIKE ‘max_connections‘;+-----------------+-------+| Variable_name   | Value |+-----------------+-------+| max_connections | 151   |+-----------------+-------+1 row in set (0.00 sec)mysql> SET GLOBAL max_connections = 200;Query OK, 0 rows affected (0.00 sec)

Linux can typically handle 500~1000 connections, and if the RAM resources are sufficient to handle 1w+ connections, Windows can handle no more than 2048 connections because of the Posix compatibility layer.

Monitor Connection Utilization

If you use a thread-per-connection approach, you can monitor the metric with a previously set connection limit by threads_connected, ensuring that the server has sufficient capacity to handle new connections.

In addition, through the threads_running indicator, you can view the threads that are processing the request, which can be used to determine if the connection is occupied but does not handle any requests.

If Max_connections is reached, the new connection request will be rejected, the connection_errors_max_connections indicator will begin to increase, and the aborted_connects metrics that track all failed connection attempts will begin to increase.

In addition, by connection_errors_internal, this indicator can be used to monitor errors from the server itself, such as insufficient memory.

Summarize

Threads_connected                   已经建立的连接Threads_running                     正在运行的连接Connection_errors_internal          由于服务器内部本身导致的错误Aborted_connects                    尝试与服务器建立连接但是失败的次数Connection_errors_max_connections   由于到达最大连接数导致的错误

Buffer Pool Usage

InnoDB uses a memory area as a buffer to cache data tables and index data, which can cause database performance to fall and disk I/O to climb.

The default value is typically 128MiB, which is recommended to be set to 80% of physical memory, but it is important to note that InnoDB may use more than buffer pool 10%, and if it runs out of memory, paging will be used to compromise the performance of the database.

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_chunk_size";SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_instances";

If the innodb_buffer_pool_chunk_size query does not return a result, it means that this parameter cannot be changed in the MySQL version you are using, with a value of MiB and an actual parameter of innodb_buffer_pool_size.

When the server starts, you can set the size of the buffer pool and the number of instances:

$ mysqld --innodb_buffer_pool_size=8G --innodb_buffer_pool_instances=16

Monitoring metrics

Innodb_buffer_pool_read_requests records the number of read requests, and Innodb_buffer_pool_reads records the number of requests that the buffer pool cannot satisfy, and therefore can only read from disk, that is, if innodb_ The value of buffer_pool_reads starts to increase, which means there is a problem with database performance.

The cache usage and hit rate can be calculated as follows:

(Innodb_buffer_pool_pages_total - Innodb_buffer_pool_pages_free) /    Innodb_buffer_pool_pages_total * 100%(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) /    Innodb_buffer_pool_read_requests * 100%

If the database is heavily read from disk, and the buffer pool has a lot of free space, this may be because the cache was recently cleaned up and is still in the warm-up phase.

Summarize

Innodb_buffer_pool_pages_total          BP中总页面数Buffer pool utilization                 BP中页面的使用率Innodb_buffer_pool_read_requests        BP的读请求Innodb_buffer_pool_reads                需要读取磁盘的请求数

Response time

"Response times" (Response time, RT) are important in database applications, especially in OLTP scenarios, but this statistic has not been added to the official version. Started using Tcpdump+mk-query-digest, then later Tcprstat, and soon Percona provided a response time statistics plugin.

A similar approach exists for MariaDB, which can be installed and tested in the following ways.

-----1. See if there is a plug-in $ ls/opt/mariadb/lib/plugin | grep responsequery_response_time.so-----2. Install plugin mysql> install PLUGIN query_response_time_audit SONAME ' query_response_time.so '; Query OK, 0 rows Affected (0.00 sec) mysql> INSTALL PLUGIN query_response_time SONAME ' query_response_time.so '; Query OK, 0 rows Affected (0.00 sec)-----3. Turn on statistical function mysql> SET GLOBAL query_response_time_stats = ' on '; Query OK, 0 rows Affected (0.00 sec) mysql> SET GLOBAL query_response_time_flush = ' on '; Query OK, 0 rows Affected (0.00 sec)-----4. View statistical values mysql> Show query_response_time;mysql> SELECT * from information_schema.query_response_time;+------------ ----+-------+----------------+| Time | Count |       Total |+----------------+-------+----------------+|     0.000001 |       0 |       0.000000 | |     0.000010 |       0 |       0.000000 | |     0.000100 |       1 |       0.000089 | |    0.001000 |       14 |       0.010173 | |     0.010000 |       0 |       0.000000 | |   0.100000 |  0 |       0.000000 | |     1.000000 |       0 |      0.000000 | |     10.000000 |       0 |     0.000000 | |     100.000000 |       0 |    0.000000 | |     1000.000000 |       0 |   0.000000 | |     10000.000000 |       0 |  0.000000 | |     100000.000000 |       0 | 0.000000 | |     1000000.000000 |       0 | 0.000000 | |     TOO LONG | 0 | TOO LONG |+----------------+-------+----------------+14 rows in Set (0.00 sec)

The default time interval is increased by base 10, which means that the default interval is as follows:

(0;10^-6], (10^-6;10^-5], (10^-5;10^-4], ..., (10^-1;10^1], (10^1; 10^2], ...

Can be modified by modifying the parameter query_response_time_range_base to reduce the time interval, the default is 10, the actual statistical time interval as above, can be modified to 2, the interval is as follows:

(0;2^-19], (2^-19;2^-18], (2^-18;2^-17], ..., (2^-1; 2^1], (2^1; 2^2], ...

The first interval is always the closest to 0.000001, and the last interval is the closest and less than 10000000 ends.

Of course, some comparisons from the internet extracts good SQL, can be modified according to their own needs.

SELECT    case TRIM(time)        when ‘0.000001‘ then ‘<  1us‘        when ‘0.000010‘ then ‘< 10us‘        when ‘0.000100‘ then ‘<100us‘        when ‘0.001000‘ then ‘<  1ms‘        when ‘0.010000‘ then ‘< 10ms‘        when ‘0.100000‘ then ‘<100ms‘        when ‘1.000000‘ then ‘<   1s‘        when ‘10.000000‘ then ‘<  10s‘        when ‘100.000000‘ then ‘<100s‘        else ‘>100s‘    END as `RT area`,    CONCAT(ROUND(100*count/query_count,2),"%") as percent,    countFROM(    SELECT        c.count,        c.time,        (            SELECT SUM(a.count)            FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as a            WHERE a.count != 0        ) as query_count    FROM INFORMATION_SCHEMA.QUERY_RESPONSE_TIME as c    WHERE c.count > 0) d;

Other monitoring items

In addition to the above monitoring items, there are common methods as follows.

1. is available

You can use the following commands to see if the current MySQL service is in a running state.

mysqladmin -h 127.1 -u root -pnew-password pingmysqladmin -h 127.1 -u root -pnew-password status

2. User Management

"%" is strictly prohibited for the user's "host" section unless you want to log in from anywhere in the world; grant all on is not used by default . over-empowering the user,

mysql> CREATE USER ‘user‘ IDENTIFIED BY ‘password‘;mysql> GRANT privileges TO ‘user‘@‘host‘ [WITH GRANT OPTION];mysql> FLUSH PRIVILEGS;mysql> GRANT privileges  TO ‘user‘@‘host‘ IDENTIFIED BY ‘password‘ [WITH GRANT OPTION];mysql> FLUSH PRIVILEGS;

3. Whether the number of connections is normal

Check to see if the customer has a connection that is dead due to not closing the connection properly, how many failed connections, whether there is a malicious connection, and so on.

----- 查看连接数，root会看到所有，其它用户只能看到自己的连接mysql> SHOW FULL PROCESSLIST;$ mysqladmin -h host -u user -p processlist----- 当前失败连接数mysql> SHOW GLOBAL STATUS LIKE ‘aborted_connects‘;----- 由于客户没有正确关闭连接而死掉的连接数mysql> SHOW GLOBAL STATUS LIKE ‘aborted_clients‘;----- 最大连接数mysql> SHOW CLOBAL VARIABLES LIKE ‘max_connections‘;mysql> SHOW GLOBAL STATUS LIKE ‘max_connections‘;

4. Slow query log

The slow query log is important for SQL tuning, which records query statements that exceed a specified time long_query_time, and is typically opened only when needed.

MyISAM

When tuning the MyISAM storage engine, many articles recommend using the ratio of key_read_requests and key_reads as a reference for tuning to set the value of the Key_buffer_size parameter, which is actually wrong and can be consulted in detail. Uld Ignore MySQL ' s key cache hit ratio this article.

Briefly described below.

Rate VS. Ratio

The first thing to note is that there are two important concepts: "Miss rate" is generally the number of miss per second; "Miss ratio" represents the scale that is read from disk and read from the cache, which has no units.

The following two parameters can be viewed through the show GLOBAL STATUS command, as explained in the official documentation.

Key_read_requestsThe number of requests to read a key block from the cache.Key_readsThe number of physical reads of a key block from disk.

That is, the two correspond: A) The number of requests to read the index from the cache, and B) the number of requests to read the index from disk.

Note: In fact, key_reads statistics are not strictly read disks, strictly speaking, should be the number of system requests sent. If the file system has a cache, the actual time-consuming is the system call, and there is no time to read the disk.

Many people think that the smaller the key_reads/key_read_requests the better, otherwise you should increase the key_buffer_size setting, but the ratio of the counter to tune there are two problems:

1. The proportions do not show the absolute size of the quantity and do not know how many requests are in total;
2. The counter does not take the time factor into account.

Suppose there are two machines, with Miss ratio of 23% and 0.1%, because there is no read request volume, it is difficult to tell which machine needs tuning. For example, the former is 23/100 and the latter is 10k/10m.

Parameter indicator

Although key_read_requests big than small, but for the system tuning, more meaningful should be in the unit time of the key_reads, usually can be calculated by Key_reads/uptime, this parameter can be obtained by the following command:

$ mysqladmin ext -ri10 | grep Key_reads

The first line represents the total request after system startup, which can be ignored, each of the following values represents a change in data within 10 seconds, so that you can roughly assess how much disk requests per second, and whether it is reasonable to evaluate your disk performance.

Note: The command mysqladmin ext is actually mysqladmin extended-status, you can even abbreviated to Mysqladmin E.

Conclusion

Replace Key_reads/key_read_requests with Key_reads/uptime.

Xtools

-----Standard data statistics, mainly statistics of the number of commands, Transaction ins Com_insert (diff) Upd com_update                               (diff) del com_delete (diff) Sel com_select  (diff) TPs Com_insert + com_update + com_delete (diff)-----threading, Threads run threads_running con  threads_connected cre threads_created (diff) Cac threads_cached-----Network bytes, Bytes recv Bytes_received (diff) sent Bytes_sent (diff)-----Buffer pool's ease Total number of innodb_buffer_pool_read_requests logical reads Innodb_buffer_pool_reads total number of physical reads Rea D innodb_buffer_pool_read_requests read request per second (diff) Hits (innodb_buffer_pool_read_requests-innodb_buffer_pool_reads            /innodb_buffer_pool_read_requests-----The state of the buffer pool page, Innodb BP pages status data innodb_buffer_pool_pages_data Cache pages already used free INnodb_buffer_pool_pages_free free Cache pages Dirty Innodb_buffer_pool_pages_dirty number of dirty pages flush Innodb_buffer                     _pool_pages_flushed Refresh Pages per second (diff)-----InnoDB related Operations, InnoDB rows status ins innodb_rows_inserted    (diff) Upd innodb_rows_updated (diff) del innodb_rows_deleted (diff)                        Read Innodb_rows_read (diff)-----Number of data read and write requests, INNODB data status reads Innodb_data_reads                         Total data Read (diff) writes innodb_data_writes data write total number of times (diff) Read Innodb_data_read The amount of data that has been read (diff) written Innodb_data_written to the amount of data that has been written (diff)-----Log write to disk request, Innodb l OG fsyncs innodb_os_log_fsyncs Total number of writes to log file (diff) written Innodb_os_log_written write log Number of bytes in the file (diff)

Reference

You can refer to the official document Reference Manual-server Status Variables, which mainly describes the meaning of each monitoring item.

Monitoring MySQL performance metrics, a very good introduction to MySQL monitoring articles, including the above-mentioned throughput, execution performance, link situation, buffer pool usage and so on.

Why do you should ignore MySQL's key cache hit ratio This article describes the tuning of the MyISAM cache, where the thought of other parameters can also be considered, or refer to the local documentation.

Original: https://jin-yang.github.io/post/mysql-monitor.html

MySQL Monitoring Metrics