How to enable slow query log in MySQL
|
1.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. 1.2 Log in to the database to view [root@localhost lib]# mysql –uroot Because no password is set, if there is a password, enter the password in mysql –uroot –p 1.2.1 Enter MySQL to check whether slow query is enabled mysql> show variables like 'slow_query%'; +---------------------+--------------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /application/mysql/data/localhost-slow.log | +---------------------+--------------------------------------------+ 2 rows in set (0.00 sec) Parameter Description:
1.2.2 Check the slow query timeout mysql> show variables like 'long%'; +-----------------+-----------+ | Variable_name | Value | +-----------------+-----------+ | long_query_time | 10.000000 | +-----------------+-----------+ 1 row in set (0.00 sec) long_query_time The number of seconds that a query exceeds before it is recorded. The default value is 10 seconds, which is changed to 1 second. 1.3 Modification method 1: (not recommended) Method 1: Advantages: Temporarily enable slow query without restarting the database Disadvantages: MySQL restarts slow query and fails Recommendation: Based on business needs, it is recommended to use the second method. The first method can be used temporarily. By default, the value of slow_query_log is OFF, which means that the slow query log is disabled. It can be enabled by setting the value of slow_query_log, as shown below: : Whether to enable the slow query log, 1 means on, 0 means off. 1.3.1 Check whether slow query is enabled mysql> show variables like '%slow_query_log%'; +---------------------+--------------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------------+ | slow_query_log | OFF | | slow_query_log_file | /application/mysql/data/localhost-slow.log | +---------------------+--------------------------------------------+ 2 rows in set (0.01 sec) Input statement modification (invalid after restart, it is recommended to modify it in /etc/my.cnf to take effect permanently) mysql> set global slow_query_log=1; Query OK, 0 rows affected (0.11 sec) 1.3.2 Check again mysql> show variables like '%slow_query_log%'; +---------------------+--------------------------------------------+ | Variable_name | Value | +---------------------+--------------------------------------------+ | slow_query_log | ON | | slow_query_log_file | /application/mysql/data/localhost-slow.log | +---------------------+--------------------------------------------+ 2 rows in set (0.00 sec) 1.4 Modification method 2: (recommended) Modify MySql slow query. Many people don't know the path of my.cnf. You can use find to find it. Note: My MySQL is compiled in the path /etc/my.cnf (usually here) [root@localhost log]# find / -type f -name "my.cnf" /application/mysql-5.5.51/mysql-test/suite/rpl/my.cnf /application/mysql-5.5.51/mysql-test/suite/federated/my.cnf /application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/rpl/my.cnf /application/mysql-5.5.51/mysql-5.5.51-linux2.6-x86_64/mysql-test/suite/federated/my.cnf /etc/my.cnf ### (usually here) 1.4.1.1 Modifications [root@localhost log]# vim /etc/my.cnf Find [mysqld] and add slow_query_log = 1 slow_query_log_file=/application/mysql/data/localhost-slow.log long_query_time = 1 Parameter Description:
After modification, restart MySQL 1.5 View and test 1.5.1.1 Insert a test slow query mysql> select sleep(2); +----------+ | sleep(2) | +----------+ | 0 | +----------+ 1 row in set (2.00 sec) 1.5.1.2 Viewing slow query logs [root@localhost data]# cat /application/mysql/data/localhost-slow.log /application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument /application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument /application/mysql/bin/mysqld, Version: 5.5.51-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /tmp/mysql.sock Time Id Command Argument # Time: 170605 6:37:00 # User@Host: root[root] @ localhost [] # Query_time: 2.000835 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1496615820; select sleep(2); 1.5.1.3 Checking the number of slow queries through MySQL commands mysql> show global status like '%Slow_queries%'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Slow_queries | 1 | +---------------+-------+ 1 row in set (0.00 sec) 1.6 Log analysis tool mysqldumpslow In a production environment, if you want to manually analyze logs, find and analyze SQL, it is obviously a manual job. MySQL provides a log analysis tool mysqldumpslow 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. You may also be interested in:
|
<<: Detailed explanation of how to quickly operate MySQL database in nodejs environment
>>: Linux kernel device driver kernel linked list usage notes
Recommend
After submitting the html drop-down menu, the selected value is retained instead of returning to the default value
Copy code The code is as follows: <html> &l...
JD Vue3 component library supports the detailed process of mini program development
Source code preview: https://github.com/jdf2e/nut...
Definition and usage of MySQL cursor
Creating a Cursor First, create a data table in M...
How to create a table in mysql and add field comments
Directly post code and examples #Write comments w...
MySQL 5.7.23 winx64 installation and configuration method graphic tutorial under win10
This article records the installation tutorial of...
MySQL database operations and data types
Table of contents 1. Database Operation 1.1 Displ...
Detailed explanation of the principle and usage of MySQL stored procedures
This article uses examples to explain the princip...
Solve the error of installing VMware Tools on Ubuntu 18.04
1. According to the online tutorial, the installa...
How does the MySQL database implement the XA specification?
MySQL consistency log What happens to uncommitted...
How to implement property hijacking with JavaScript defineProperty
Table of contents Preface Descriptors Detailed ex...
MySQL 8.0.16 installation and configuration tutorial under CentOS7
Uninstall the old version of MySQL (skip this ste...
A simple method to implement scheduled backup of MySQL database in Linux
Here are the detailed steps: 1. Check the disk sp...
Docker builds kubectl image implementation steps
If the program service is deployed using k8s inte...
What does the legendary VUE syntax sugar do?
Table of contents 1. What is syntactic sugar? 2. ...
Win32 MySQL 5.7.27 installation and configuration method graphic tutorial
The installation tutorial of MySQL 5.7.27 is reco...