MYSQL slow query and log example explanation

1. Introduction

By enabling the slow query log, MySQL can record query statements that exceed the specified time. By locating and analyzing performance bottlenecks, the performance of the database system can be better optimized.

2. Parameter Description

slow_query_log slow query enable status
slow_query_log_file is the location where the slow query log is stored (this directory requires writable permissions for the MySQL running account and is generally set to the MySQL data storage directory)
long_query_time How many seconds does it take for a query to be recorded?

3. Setup steps

1. View slow query related parameters

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name | Value |
+---------------------------+----------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /mysql/data/localhost-slow.log |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

2. Setting method

Method 1: Global variable setting Set the slow_query_log global variable to "ON"

mysql> set global slow_query_log='ON';

Set the location where the slow query log is stored

mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';

If the query exceeds 1 second, it will be recorded

mysql> set global long_query_time=1;

Method 2: Configuration file settings Modify the configuration file my.cnf and add it below [mysqld].

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

3. Restart MySQL service

service mysqld restart

4. Check the parameters after setting

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------+
| slow_query_log | ON |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+

4. Testing

1. Execute a slow query SQL statement

mysql> select sleep(2);

2. Check whether slow query logs are generated

ls /usr/local/mysql/data/slow.log

If the log exists, MySQL slow query setting is enabled successfully!

This is the end of this article about MYSQL slow query and log examples. For more relevant MYSQL slow query and log content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!