MySQL slow query operation example analysis [enable, test, confirm, etc.]

This article describes the MySQL slow query operation with examples. Share with you for your reference, the details are as follows:

Some MySQL SQL statements may execute very slowly, which may cause a surge in server load.

First, check to make sure that MySQL is the one that is affecting the load, using the top command, ps command, etc.

Next, enter MySQL and use the show full processlist command to query the SQL statements being executed to see the problem. Use the explain command to view the status.

Finally find out the SQL statement to kill or optimize

Install mariadb service on centos7

yum -y install mariadb-server mariadb-devel

Enable slow query

more /etc/my.cnf.d/server.cnf

[mariadb]
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/slow.log
long_query_time=1

Start mariadb service

systemctl start mariadb

Check whether MySQL slow query is enabled and how long the above is a slow query

MariaDB [(none)]> show variables like '%slow_query%';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

#If you don't have slow query enabled, you can enable it in the command line mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)

Test slow queries and view logs

MariaDB [(none)]> select sleep(2);
+----------+
| sleep(2) |
+----------+
| 0 |
+----------+
1 row in set (2.00 sec)

[root@localhost ~]# more /usr/local/mysql/data/slow.log
/usr/libexec/mysqld, Version: 5.5.60-MariaDB (MariaDB Server). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 180930 23:51:07
# User@Host: root[root] @ localhost []
# Thread_id: 2 Schema: QC_hit: No
# Query_time: 2.001017 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1538322667;
select sleep(2);

Confirming slow queries

MariaDB [(none)]> show full processlist; #View the state slow query in progress +----+------+-----------+------+---------+------+------------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+------+------------+-----------------------+----------+
| 3 | root | localhost | NULL | Query | 9 | User sleep | select sleep(10) | 0.000 |
| 4 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0.000 |
+----+------+-----------+------+---------+------+------------+-----------------------+----------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show full processlist; #Check the state slow query has ended, but the user is logged in +----+------+-----------+------+---------+------+-------+-----------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+----+------+-----------+------+---------+-------+-------+-----------------------+----------+
| 3 | root | localhost | NULL | Sleep | 1 | | NULL | 0.000 |
| 4 | root | localhost | NULL | Query | 0 | NULL | show full processlist | 0.000 |
+----+------+-----------+------+---------+-------+-------+-----------------------+----------+
2 rows in set (0.00 sec)

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.

You may also be interested in:

How to use MySQL stress testing tools
PHP+MySQL to implement online test answering example
Detailed explanation of the mysqlslap command and syntax for the built-in stress test in MySQL 5.7
How to quickly create tens of millions of test data in MySQL
Test and solution for MySQL's large memory usage and high CPU usage
Detailed tutorial on using sysbench to test MySQL performance
MySQL loop statement while loop test
MySQL database implements OLTP benchmark test based on sysbench

<<: Steps to configure IIS10 under Win10 and support debugging ASP programs

>>: Example of how to achieve ceiling effect using WeChat applet