MYSQL slow query and log settings and testing

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 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 the "ON" state

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 the following under [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!

Summarize

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