To monitor the performance and status of MySQL server, you need to start from four aspects: built-in commands, resource monitoring, tool usage and log checking. 1. Use built-in commands such as SHOW STATUS and SHOW PROCESSLIST to quickly view the number of connections and running status; 2. Combine system commands and MySQL internal mechanisms to monitor the CPU, memory, disk IO and SQL execution efficiency; 3. Use MySQL Workbench, Prometheus Grafana, Zabbix or PMM to achieve continuous monitoring and reasonably set the acquisition frequency; 4. Regularly analyze error logs and slow query logs, set thresholds and optimize SQL that is not indexed, so as to promptly discover potential problems and prevent service interruptions.

The core of MySQL server performance and status monitoring is to promptly detect problems and locate bottlenecks, and avoid service interruptions or slow response. To put it directly, you need to pay attention to resource usage, query efficiency, number of connections, and log information.

1. Use built-in commands to quickly view the running status

MySQL comes with some basic but practical commands, such as SHOW STATUS and SHOW PROCESSLIST , to quickly understand the current database health status.

• SHOW STATUS LIKE 'Threads_connected'; You can see the current number of connections. If this value is close to your maximum connection limit, you need to adjust the configuration or check whether there are abnormal connections.
• SHOW PROCESSLIST; allows you to see the thread being executed. If there are a large number of connections in "Sleep" state, it may mean that the application does not close the connection correctly.
• SHOW GLOBAL STATUS; provides more comprehensive state variables, such as how many queries are processed, how many tables are opened, etc.

These commands do not require additional tools and are suitable for quick use during emergency troubleshooting.

2. Monitor system resources and MySQL resource usage

MySQL's performance is subject to factors such as CPU, memory, disk IO and so on. You can combine Linux commands to observe:

• Check CPU usage rate top or htop
• free -m to see memory usage
• iostat (requires sysstat package) observes disk read and write pressure

For MySQL internal resources, you can use the following methods:

• Turn on the slow query log to find SQL that has been executed for a long time
• Use SHOW PROFILE (outdated) or performance_schema to analyze resource consumption of a single SQL
• Check the InnoDB buffer pool hit rate. If the hit rate is low, it means there is insufficient memory or there is a problem with the cache strategy.

If you find that the CPU suddenly rises during a certain period of time and the number of slow queries increases, it is likely that there is unoptimized SQL that leads to resource competition.

3. Use monitoring tools to achieve continuous observation

Manually executing commands can only be temporarily viewed. If you really want to monitor them for a long time, you still have to use tools. Common open source tools are:

• MySQL Workbench : comes with a monitoring panel, which can graphically display various indicators
• Prometheus Grafana : Suitable for building enterprise-level monitoring systems, flexible and scalable
• Zabbix : Powerful, supports custom monitoring items and alarm mechanisms
• Percona Monitoring and Management (PMM) : A monitoring platform designed for MySQL, out of the box

Pay attention to the collection frequency and historical data retention time when setting monitoring. For example, it is more reasonable to collect it every 5 seconds, as it will increase the burden on the database too frequently.

4. Check error logs and slow query logs regularly

Logs are first-hand information for troubleshooting. MySQL error log usually records information such as start, stop, exception error, etc.; slow query log records SQL statements that have been executed for more than the specified time.

suggestion:

• Set a reasonable slow query threshold (such as 0.5 seconds)
• Together with pt-query-digest analysis, slow query logs are found to find the SQL that affects the most performance
• Clean or archive old logs regularly to prevent disk fullness

If you find that there are a large number of slow queries at what time every morning, it may be that some SQL in the timing task does not go through the index and needs optimization.

Basically these methods. Monitoring is not a one-time job, but a process that requires continuous maintenance and adjustment. Many problems have actually laid the groundwork for a long time, but have you discovered it in advance.

The above is the detailed content of Monitoring MySQL Server Performance and Status. For more information, please follow other related articles on the PHP Chinese website!