There are many ways to monitor a database today, split into three broad categories: built into the database, commercial, and open source, each with its own characteristics; and for the mysql database, thanks to its highly active community, monitoring approaches are even more varied. Regardless of the monitoring approach, the core is the monitoring data, and after obtaining comprehensive monitoring data comes the flexible presentation part. So today let’s introduce collecting and obtaining monitoring data entirely using mysql’s own facilities, achieving the fastest, most convenient and least overhead approach for a single instance.
This article obtains everything entirely using mysql’s built-in show commands, gathering monitoring data comprehensively across 7 major areas: connects, buffercache, lock, SQL, statement, Database throughputs, and serverconfig.
Number of connections (Connects)
Maximum connections used: show status like ‘Max_used_connections’
Currently open connections: show status like ‘Threads_connected’
Cache (bufferCache)
- Number of reads not from the buffer pool: show status like ‘Innodb_buffer_pool_reads’
- Number of reads from the buffer pool: show status like ‘Innodb_buffer_pool_read_requests’
- Total pages in the buffer pool: show status like ‘Innodb_buffer_pool_pages_total’
- Free pages in the buffer pool: show status like ‘Innodb_buffer_pool_pages_free’
- Cache hit rate calculation: (1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests）*100%
- Buffer pool usage rate: ((Innodb_buffer_pool_pages_total-Innodb_buffer_pool_pages_free）/Innodb_buffer_pool_pages_total）*100%
Locks (lock)
- Number of lock waits: show status like ‘Innodb_row_lock_waits’
- Average wait time per lock: show status like ‘Innodb_row_lock_time_avg’
- Check whether table locks exist: show open TABLES where in_use>0; having data means table locks exist, empty means no table locks
Note: the lock wait count is cumulative data; each time you retrieve it you can subtract the previous data to get the current statistics
SQL
- Check whether the mysql switch is on: show variables like ‘slow_query_log’, ON means enabled, if it is OFF, run set global slow_query_log=1 to enable it
- Check the mysql threshold: show variables like ‘long_query_time’, pass the threshold parameter from the page, modify the threshold with set global long_query_time=0.1
- Check the mysql slow sql directory: show variables like ‘slow_query_log_file’
- Format the slow sql log: mysqldumpslow -s at -t 10 /export/data/mysql/log/slow.log Note: this statement cannot be executed via jdbc, it is a command line execution. It means: show the execution information of the 10 most time-consuming SQL statements, 10 can be changed to the TOP number. The information shown is: execution count, average execution time, SQL statement
Note: when the mysqldumpslow command fails to execute, sync the slow log locally for formatting.
statement
- insert count: show status like ‘Com_insert’
- delete count: show status like ‘Com_delete’
- update count: show status like ‘Com_update’
- select count: show status like ‘Com_select’
Throughput (Database throughputs)
- Send throughput: show status like ‘Bytes_sent’
- Receive throughput: show status like ‘Bytes_received’
- Total throughput: Bytes_sent+Bytes_received
Database parameters (serverconfig)
show variables
Slow SQL
Slow SQL refers to MySQL slow queries, specifically SQL whose execution time exceeds the long_query_time value. We often hear that MySQL has a binary log binlog, relay log relaylog, redo/rollback log redolog, undolog, etc. For slow queries there is also a slow query log slowlog, used to record statements in MySQL whose response time exceeds the threshold. Slow SQL has a fatal impact on actual production business, so it is especially important for testers to monitor the execution of database SQL statements during performance testing and provide developers with accurate performance optimization advice. So how do you use the slow query log provided by the Mysql database to monitor SQL statement execution and find the SQL statements that consume the most? The following explains the steps for using the slow query log in detail:
- Make sure the slow SQL switch slow_query_log is on
- Set the slow SQL threshold long_query_time; this long_query_time is used to define how many seconds counts as a “slow query”, note the unit is seconds. I set the value of long_query_time to 1 by running the sql command set long_query_time=1, meaning anything executing for more than 1 second counts as a slow query, as follows:
- Check the slow SQL log path
- Use the slow sql analysis tool mysqldumpslow to format and analyze the slow SQL log. mysqldumpslow is a slow query analysis tool that comes with mysql after installation; you can view the usage parameter description via ./mysqldumpslow —help
Common usage:
- Get the 10 most frequently used slow queries ./mysqldumpslow -s c -t 10 /export/data/mysql/log/slow.log
Get the 3 slowest queries ./mysqldumpslow -s t -t 3 /export/data/mysql/log/slow.log
Note: the analysis result of mysqldumpslow will not show the complete specific sql statement, only the composition structure of the sql; for example: SELECT FROM sms_send WHERE service_id=10 GROUP BY content LIMIT 0, 1000; after running the mysqldumpslow command it shows: 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 mysqldumpslow analysis result:
- Count: indicates the number of times statements of this type were executed; in the image above it means the select statement was executed 2 times.
- Time: indicates the average execution time of statements of this type (total time)
- Lock: lock time 0s.
- Rows: a single execution returned 1000 records, 2 executions returned 2000 records in total.
With this tool you can find out which sql statements are slow SQL, and feed that back to R&D for optimization, such as adding indexes, changing the implementation of the application, etc.
Common slow SQL troubleshooting
Don’t use subqueries
SELECT FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’); In MySQL5.5, the internal execution planner executes subqueries like this: query the outer table first then match the inner table, rather than querying the inner table t2 first; when the outer table’s data is very large, the query speed becomes very slow. In MariaDB10/MySQL5.6, this was optimized using join association; this SQL is automatically converted to SELECT t1. FROM t1 JOIN t2 ON t1.id = t2.id; But note: the optimization is only effective for SELECT, it has no effect on UPDATE/DELETE subqueries, and production environments should avoid using subqueries as much as possible.
Avoid function indexes
SELECT FROM t WHERE YEAR(d) >= 2016; Since MySQL, unlike Oracle, does not support function indexes, even if the d field has an index it will still do a direct full table scan. It should be changed to > SELECT FROM t WHERE d >= ‘2016-01-01’;
Use IN to replace inefficient OR queries
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);
LIKE with double percent signs cannot use an index
SELECT FROM t WHERE name LIKE ‘%de%’; Use SELECT FROM t WHERE name LIKE ‘de%’;
Group statistics can disable sorting
SELECT goods_id,count() FROM t GROUP BY goods_id; By default, MySQL sorts all GROUP BY col1, col2… fields. If the query includes GROUP BY and you want to avoid the cost of sorting results, you can specify ORDER BY NULL to disable sorting. Use SELECT goods_id,count () FROM t GROUP BY goods_id ORDER BY NULL;
Disable unnecessary ORDER BY sorting
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;
Summary
- Nothing should focus too much on its exterior; focus on the inner substance, because under a gorgeous exterior there are often corresponding burdens and overhead.
- mysql database monitoring supports accessing the corresponding table data from the performance_schema database via SQL, provided that this database is initialized and writing of monitoring data is enabled.
- For monitoring, it’s not about the diversity of means, but about understanding the essence of monitoring and the monitoring items you need, and finding a monitoring approach that fits your own project’s characteristics.
- When choosing a monitoring tool for mysql monitoring, pay attention to the tool’s own consumption of the database server, and don’t let it affect its own usage.
