Monitoring MySQL health and performance requires five core dimensions. 1. Check the number of connections and thread status, and use SHOW STATUS LIKE 'Threads%'; Check Threads_connected and Threads_running. If Threads_running is higher than 10~20 for a long time, you need to combine the slow query log troubleshooting; 2. Enable and analyze the slow query log, configure slow_query_log, long_query_time, use mysqldumpslow or pt-query-digest analysis to optimize the SQL of the missed index; 3. Monitor the InnoDB status, pay attention to the buffer pool hit rate, log sequence number and checkpoint gap, and row operation status, and reasonably set innodb_buffer_pool_size; 4. Use system tools such as top, iostat, and vmstat and Prometheus Grafana collects CPU, IO, memory and other indicators, pay attention to the disk queue length and swap usage; 5. Adjust the configuration according to the data volume to avoid frequent restarts causing the buffer pool to be coldly started, and ensure reasonable resource allocation.

The key to monitoring the health and performance metrics of MySQL servers is to master several core dimensions. You don't need to go deep into the source code every time, but you must be able to quickly judge whether the system is normal and whether there are potential bottlenecks through some common indicators.

Check the number of connections and thread status

The number of connections and thread state of MySQL are the first window that reflects the service load. If the number of connections continues to grow or even approaches the maximum limit, it may be that there may be slow queries or the client does not release the connection correctly.

• Threads_connected : The number of currently open connections
• Threads_running : The number of threads that are executing tasks. Too high may be due to a large number of concurrent operations or slow query

You can use the following command to view:

 SHOW STATUS LIKE 'Threads%';

suggestion:

• Set the appropriate maximum number of connections ( max_connections ), do not set too high to cause resource exhaustion, and do not affect business too low.
• If you find Threads_running is greater than 10~20 for a long time, you can troubleshoot problems with the slow query log.

Pay attention to slow query logs and query efficiency

Slow queries are one of the main reasons for slowing down database performance. Turn on slow query logs and analyze them regularly to help you find out those statements that "stuck" the database.

Enable method (in configuration file):

 slow_query_log = 1
long_query_time = 1
log_slow_queries = /var/log/mysql/mysql-slow.log

suggestion:

• Regularly use mysqldumpslow or third-party tools (such as pt-query-digest) to analyze slow query logs
• Add index or optimize structure for frequent execution and time-consuming SQL
• Use EXPLAIN to analyze the execution plan and confirm whether the index is hit

Monitor InnoDB status and buffer pool usage

If you are using the InnoDB engine, its status information is very important. In particular, the usage rate of buffer pools is directly related to read and write performance.

Commonly used commands:

 SHOW ENGINE INNODB STATUS\G

Focus:

• Buffer pool hit rate : cache hit rate, below 95% may require increasing buffer pool size ( innodb_buffer_pool_size )
• Log sequence number and Last checkpoint at : The gap is too large, which means that the write pressure is large, check the disk IO capability
• Read/delete/update in Row operations , assist in judging load type

suggestion:

• Set a reasonable buffer pool size according to the data volume, usually set to 50%~80% of physical memory
• Do not restart MySQL frequently to avoid performance fluctuations caused by cold start of buffer pools

Collect real-time metrics using system monitoring tools

In addition to the state inside the database, data at the operating system level is also very important. For example, CPU usage, IO latency, memory usage, etc. will affect MySQL performance.

Recommended tools:

• top , htop : Look at the overall CPU usage
• iostat : Check whether disk IO becomes a bottleneck
• vmstat : Understand memory and swap usage
• Prometheus Grafana: Visualize long-term trends, suitable for production environments

Tips:

• In high concurrency scenarios, the CPU is often not a bottleneck, but IO is the one. Remember to pay attention to disk queue length ( %util and await in iostat -x )
• If insufficient memory leads to frequent use of swap, MySQL performance will drop sharply. In this case, it should be expanded or optimized in time

Basically that's it. MySQL monitoring is not complicated, but details are easily overlooked. If you focus on the above aspects, most performance problems can be discovered in time.

The above is the detailed content of Monitoring MySQL server health and performance metrics. For more information, please follow other related articles on the PHP Chinese website!