Detailed description of MySql slow query analysis and enabling slow query log, detailed description of mysql enabling log

Detailed description of MySql slow query analysis and enabling slow query log, detailed description of mysql enabling log

I am also studying MySQL performance optimization recently, so I will take a study note today!

In projects developed by our partners, the most common problem for MySQL to identify performance bottlenecks is slow query of MYSQL and query without indexes.

Next we will teach you how to enable slow query log records for MySQL or later;

OK. Start to find out the SQL statements that are not "refreshing" in mysql.

First, run the mysql command to enter the mysql command line:

[root@yunuo_vm ~]# mysql -u root -pEnter password:Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 4977Server 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 itsaffiliates. Other names may be trademarks of their respectiveowners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>

Ps: the MySQL version is 5.6.17.

OK, go to the console. Next, we can check the default MySQL configuration in seconds to calculate the slow query.

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 default slow query time limit is 10 seconds. Next we will change it to 1 second (you can also set it based on your actual situation );

Mysql> set long_query_time = 1; Note: I set 1, that is, if the execution time exceeds 1 second, the query is slow. Query OK, 0 rows affected (0.00 sec)

Oh! Finally, let's look at MySQL to enable slow query log records;

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 Enable Logging

Slow_query_log_file // log storage location

Slow query is not enabled for MySQL by default. We will enable it below:

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

This is a success !!! Is it easy?

The above is all the content of this article. I hope it will be helpful for your learning and support for helping customers.