MySql data monitoring: How to implement query analysis, statistics and monitoring of MySQL execution

MySQL is one of the most widely used open source relational databases at present. It has excellent features such as efficiency, reliability, and stability, so it is loved by more and more people. However, as the scale of applications continues to expand, corresponding MySQL monitoring and optimization have become tasks that managers cannot ignore.

MySQL data monitoring mainly includes query analysis, statistics and monitoring of MySQL execution. The purpose of these tasks is to understand the status of the database, discover and solve problems in a timely manner, improve the performance and stability of the database, and ensure the smooth operation of the business. Next, we will introduce how to implement these monitoring tasks one by one.

1. Query analysis

The performance optimization of MySQL is inseparable from the optimization of SQL queries. How to find query bottlenecks and optimization space requires relying on query analysis technology.

1. Use slow query log

MySQL comes with the function of slow query log. Through the slow query log, we can record SQL whose execution time exceeds a certain time threshold, which makes it easy for us to find the execution Long SQL statements.

The steps are as follows:
(1) Turn on MySQL's slow query log function and edit the MySQL configuration file:

vim /etc/mysql/mysql.conf.d/mysqld.cnf

Find the following two lines and set 'slow_query_log' to 'on', And specify the slow query execution time threshold (in seconds), here set to 5s:

slow_query_log=ON
slow_query_log_file=/var/log/mysql/mysql-slow.log
long_query_time=5

(2) Restart the MySQL service:

sudo service mysql restart

(3) View the slow query log:

sudo tail -f /var/log/mysql/mysql-slow.log

2. Use explain to analyze the query execution plan

When it is found that the execution efficiency of a certain SQL query is not high enough, it needs to be optimized. How to optimize requires understanding the query execution plan.

MySQL provides the function of analyzing query execution plans through ‘explain’, which can help us find query bottlenecks more easily.

The syntax is as follows:

EXPLAIN SELECT ...

After executing this statement, MySQL will return a result set, which contains the execution plan of the query, including which index is used, which tables are used, and what operations are performed. and other information.

By analyzing the execution plan, you can find the bottlenecks of queries that execute slowly, improve query statements, and improve query efficiency.

2. Data statistics

MySQL data statistics is the collection, analysis and display of various running statuses of MySQL instances, mainly including the following:

1. Instance status Statistics: including the number of connections to the MySQL instance, the number of threads, the number of IO interactions with the operating system, etc.

2. SQL statement statistics: intercept the SQL statements running in the MySQL instance and count some information, such as the number of executions, average execution time, and execution status.

3. Table status statistics: including analysis of table status, usage frequency, usage method, etc. under workload.

The commonly used data statistics tools for MySQL are as follows:

1. Use the show status command

MySQL comes with the show status command, which can count MySQL attempted requests of different resource states. For example:

show status like "%thread%";

This statement will return information about all threads, including currently running threads and non-running dormant threads.

2. MySQL Console

MySQL console provides viewing of system performance, parameters, threads, and process status.

mysql -uroot -ppassword -e "show status like '%Threads_connected%';show processlistG"

3. Use pt-stalk for fault diagnosis

pt-stalk is a very practical MySQL fault diagnosis tool. It can capture error logs in real time and collect server data based on specified parameters. Status information, etc., helps engineers quickly locate the cause of the fault.

pt-stalk --function=status --run-time=60 --dest /var/tmp

3. Data monitoring

Data monitoring is the ultimate goal of MySQL monitoring. Monitoring the running status of MySQL includes:

1. Monitoring of CPU, memory, network IO, and hard disk IO.
2. The query status, number of connections, thread status, lock contention and other running status inside the MySQL instance.

The commonly used monitoring tools for MySQL are as follows:

1. Use the top command or htop command

The top command is a process monitoring in the Linux system Tools that enable comprehensive process monitoring of the current system.

top -b -n 1 | grep -E 'Cpu|Mem|mysql'  

The htop command explains some of the abbreviated information in top that is not easy to understand. It also supports displaying running processes in color, making it easier for humans to read.

2. Monitor using Nagios

Nagios is an open source monitoring tool that can monitor various devices such as networks and hosts. By installing the Nagios monitoring plug-in on each node, you can monitor the MySQL process, CPU, network and other aspects.

3. Use the mysqladmin tool

Mysqladmin is a command line tool officially provided by MySQL. It provides a large number of options and parameters that can be used to monitor the MySQL process and its running status.

mysqladmin -uroot -p -h127.0.0.1 processlist

If you are too lazy to execute the command manually, you can use the open source monitoring tool Zabbix for monitoring:

4. Summary

Through query analysis, data statistics, data monitoring and other means, You can better understand the running status of MySQL instances, discover and solve problems in time, and improve MySQL performance and stability. MySQL itself provides many built-in tools, and there are also many choices for more targeted tools. Managers can choose and use them based on actual needs and operation and maintenance capabilities.

The above is the detailed content of MySql data monitoring: How to implement query analysis, statistics and monitoring of MySQL execution. For more information, please follow other related articles on the PHP Chinese website!