Detailed introduction to MySql slow query analysis and enabling slow query logs

This article mainly introduces the detailed explanation of MySql's slow query analysis and opening of slow query log. It has certain reference value and interested friends can refer to it.

I am also on the way to study MySQLPerformance Optimization recently, so today can be considered a study note!

In the projects developed by our friends, for troubleshooting MySQL problems and finding performance bottlenecks, the easiest problems to find and solve are MYSQL's slow queries and queries that do not use indexes.

Next, I will teach you how to enable slow query logging for MySQL version 5.0 or above;

OK, let’s start to find out the SQL statements in mysql that are not “simple” to execute.

First, we enter the mysql command line through the mysql command:

[root@yunuo_vm ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4977
Server version: 5.6.17 Source distribution
 
Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

ps: The MySQL version here is 5.6.17

OK, enter the console, connect Next, let’s check how many seconds are considered slow queries in the MySQL default configuration

mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name  | Value   |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

As shown in the table above, the system’s default upper limit of slow query time is 10 seconds. Let’s change it to 1 second (you can also Determine according to your actual situation);

mysql> set long_query_time=1;  注： 我设置了1, 也就是执行时间超过1秒的都算慢查询。
Query OK, 0 rows affected (0.00 sec)

Oh! Finally, let’s take a look at whether MySQL has slow query logging enabled;

mysql> show variables like 'slow%';
+---------------------+---------------+
| Variable_name    | Value     |
+---------------------+---------------+
| slow_launch_time  | 2       | 
| slow_query_log   | OFF      |
| slow_query_log_file | /tmp/slow.log |
+---------------------+---------------+

ps:

slow_query_log //Whether logging is turned on

slow_query_log_file //Log storage location

MySQL does not enable slow query by default. Let’s enable it:

mysql> set global slow_query_log='ON';
Query OK, 0 rows affected (0.00 sec)

Now you’re done! ! ! Is not it simple?

The above is the detailed content of Detailed introduction to MySql slow query analysis and enabling slow query logs. For more information, please follow other related articles on the PHP Chinese website!