The simplest way to monitor MYSQL

There are many monitoring methods for the current database, which are divided into three categories: self-contained database, commercial use, and open source, each of which has its own characteristics; for MySQL database, due to its high community activity, there are more monitoring methods. There are various types of monitoring methods. The core of monitoring data is monitoring data. After obtaining comprehensive monitoring data, it is a flexible display part. Then today we will introduce the complete collection and acquisition of monitoring data in MySQL's own way, which is the fastest, most convenient, and the least lossy under the single unit.

This article completely uses the show command that comes with mysql to achieve acquisition, and comprehensively obtains monitoring data from seven aspects of connections, buffercache, lock, SQL, statement, Database throughputs, and serverconfig.

1 Number of connections (Connects)

Maximum number of used connections: show status like 'Max_used_connections'
Number of currently open connections: show status like 'Threads_connected'

2 Cache (bufferCache)

Number of times not read from buffer pool: show status like 'Innodb_buffer_pool_reads'
Number of reads from buffer pool: show status like 'Innodb_buffer_pool_read_requests'
The total number of pages in the buffer pool: show status like 'Innodb_buffer_pool_pages_total'
Number of free pages in the buffer pool: show status like 'Innodb_buffer_pool_pages_free'
Cache hit ratio calculation: (1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests)*100%
The buffer pool usage rate is: ((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free)/Innodb_buffer_pool_pages_total)*100%

3 lock

Number of lock waits: show status like 'Innodb_row_lock_waits'
Average waiting time per lock: show status like 'Innodb_row_lock_time_avg'
Check if there is a table lock: show open TABLES where in_use>0; if there is data, it means that there is a lock table, and if it is empty, there is no table lock. , get the current statistics

4 SQL
Check whether the mysql switch is turned on: show variables like 'slow_query_log', ON is turned on, if it is OFF, set global slow_query_log=1 to turn it on Check the mysql threshold: show variables like 'long_query_time', pass the threshold parameters according to the page, and modify the threshold set global long_query_time=0.1
View mysql slow sql directory: show variables like 'slow_query_log_file'
Format slow sql log: mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log Note: This statement cannot be executed through jdbc, it belongs to command line execution. The meaning is: display the execution information of the 10 SQL statements that took the longest time, and 10 can be changed to the number of TOPs. The displayed information is: the number of executions, the average execution time, and the SQL statement. Note: When the mysqldumpslow command fails to execute, the slow log will be synchronized to the local for formatting.

5 statement
Number of inserts: show status like 'Com_insert'
Number of deletes: show status like 'Com_delete'
Number of updates: show status like 'Com_update'
Number of selects: show status like 'Com_select'

6 Throughput (Database throughputs)
Send throughput: show status like 'Bytes_sent'
Receive throughput: show status like 'Bytes_received'
Total throughput: Bytes_sent+Bytes_received

7 Database parameters (serverconfig)
show variables

8 Slow SQL
Slow SQL refers to MySQL slow queries, specifically SQL that runs longer than the long_query_time value.
We often hear that MySQL has binary log binlog, relay log relaylog, redo rollback log redolog, undolog, etc. For slow queries, there is also a slow query log slowlog, which is used to record statements whose response time exceeds the threshold in MySQL. Slow SQL has a fatal impact on the actual production business, so it is particularly important for testers to monitor the execution of database SQL statements and provide accurate performance optimization advice for development during performance testing. Then how to use the slow query log provided by the Mysql database to monitor the execution of SQL statements and find SQL statements with high consumption. The following describes the steps for using the slow query log in detail:
Make sure to turn on the slow SQL switch slow_query_log

Set slow SQL field value long_query_time
This long_query_time is used to define the number of seconds slower than "slow query". Note that the unit is seconds. I set the value of long_query_time to 1 by executing the sql command set long_query_time=1, that is, the execution time exceeds 1 second. The slow query is as follows:

View slow SQL log path

Format and analyze slow SQL logs through the slow SQL analysis tool mysqldumpslow
The mysqldumpslow slow query analysis tool comes with mysql after installation. You can view the parameter description through ./mysqldumpslow --help

Common usage:
Take out the top 10 slow queries./mysqldumpslow -sc -t 10 /export/data/mysql/log/slow.log
Take out the 3 slow queries with the slowest query time./mysqldumpslow -st -t 3 /export/data/mysql/log/slow.log

Note: The analysis result using mysqldumpslow will not display the specific and complete SQL statement, but only the structure of the SQL;

If: SELECT FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000;

After the mysqldumpslow command is executed, it displays: Count: 2 Time=1.5s (3s) Lock=0.00s (0s) Rows=1000.0 (2000),
vgos_dba[vgos_dba]@[10.130.229.196]SELECT FROM sms_send WHERE service_id=N GROUP BY content LIMIT N, N

Detailed explanation of the analysis results of mysqldumpslow:
Count: Indicates the number of times the statement of this type is executed. In the above figure, the select statement is executed twice.
Time: Indicates the average time (total time) for the execution of this type of statement
Lock: Lock time 0s.
Rows: The number of results returned in a single time is 1000 records, and a total of 2000 records are returned twice.
Through this tool, you can query which SQL statements are slow SQL, so as to feedback research and development for optimization, such as adding indexes, the implementation of the application, etc.

Common slow SQL troubleshooting

1. do not use subqueries
   SELECT FROM t1 WHERE id (SELECT id FROM t2 WHERE name='hechunyang');
   In MySQL 5.5 version, the internal execution planner executes the subquery like this: check the outer table first and then match the inner table, instead of checking the inner table t2 first, when the data of the outer table is large, the query speed will be very slow.
   In MariaDB10/MySQL5.6 version, it is optimized by join association, this SQL will be automatically converted to SELECT t1. FROM t1 JOIN t2 ON t1.id = t2.id;
   However, please note that the optimization is only valid for SELECT, and invalid for UPDATE/DELETE sub-queries. In the production environment, the use of sub-queries should be avoided as much as possible.
2. Avoid function indexes
   SELECT FROM t WHERE YEAR(d) >= 2016;
   Since MySQL does not support functional indexes like Oracle, even if the d field has an index, it will perform a direct full table scan.
   Should be changed to > SELECT FROM t WHERE d >= '2016-01-01';
3. Replace OR with IN inefficient query slow SELECT FROM t WHERE LOC_ID = 10 OR LOC_ID = 20 OR LOC_ID = 30;
   Efficient query > SELECT FROM t WHERE LOC_IN IN (10,20,30);
4. LIKE double percent sign cannot be used to index
   SELECT FROM t WHERE name LIKE '%de%';
   Use SELECT FROM t WHERE name LIKE 'de%';
5. Group statistics can disable sorting
   SELECT goods_id,count() FROM t GROUP BY goods_id;
   By default, MySQL sorts on all GROUP BY col1, col2... fields.
   If the query includes GROUP BY and you want to avoid the consumption of sorting results, you can specify ORDER BY NULL to prohibit sorting.
   Use SELECT goods_id,count() FROM t GROUP BY goods_id ORDER BY NULL;
6. Ban unnecessary ORDERs
   BYsort SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id WHERE 1 = 1 ORDER BY u.create_time DESC;
   Use SELECT count(1) FROM user u LEFT JOIN user_info i ON u.id = i.user_id;9

Summarize

Anything should not pay too much attention to its appearance, but to what is inside. Often, there will be corresponding burdens and losses under the gorgeous appearance.
The monitoring of the mysql database supports accessing the corresponding table data from the performance_schema library through SQL, provided that the library is initialized and monitoring data writing is enabled.
For monitoring, it is not the diversity of means, but the need to understand the nature of monitoring and the content of monitoring items required, and find a monitoring method that matches the characteristics of your own project.
When choosing a monitoring tool to monitor mysql, you need to pay attention to the consumption of the database server by the monitoring tool itself, so as not to affect its own use.

Author: An Jiashu