Detailed explanation of MySql slow query analysis and opening slow query log

I have also been researching MySQL performance optimization recently, so today’s post can be considered a study note!

In the projects developed by our partners, the easiest problems to find and solve when troubleshooting MySQL and finding performance bottlenecks are slow queries in MYSQL and queries without indexes.

Next, we 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 that are not “pleasant” to execute in MySQL.

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, now we are in the console. Next, let's check how many seconds it takes to be considered a slow query in the default MySQL 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 default slow query time limit is 10 seconds. Let's change it to 1 second (you can also set it according to your actual situation);

mysql> set long_query_time=1; Note: I set it to 1, which means that any query that takes more than 1 second to execute is considered a slow query.
Query OK, 0 rows affected (0.00 sec)

Oh! Finally, let's see if MySQL has enabled slow query logging;

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 to open logging

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)

That’s it! ! ! Isn’t it simple?

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.