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
MySQL database rename fast and safe method (3 kinds)
Table of contents How to rename MySQL database Th...
Sample code for achieving small triangle border effect with pure CSS3+DIV
The specific code is as follows: The html code is...
How to use Portainer to build a visual interface for Docker
Portainer Introduction Portainer is a graphical m...
Summary of naming conventions for HTML and CSS
CSS naming rules header: header Content: content/c...
A brief discussion on the differences and connections between .html, .htm, .shtml, and .shtm
As we all know, the web pages, websites or web pag...
Detailed explanation of the redirection configuration and practice of Rewrite in Nginx
1: Understand the meaning of address rewriting an...
Example code for implementing a hollow mask layer with CSS
Contents of this article: Page hollow mask layer,...
Three ways to communicate between Docker containers
We all know that Docker containers are isolated f...
CentOS7.5 installation of MySQL8.0.19 tutorial detailed instructions
1. Introduction This article does not have screen...
Specific use of routing guards in Vue
Table of contents 1. Global Guard 1.1 Global fron...
Detailed summary of web form submission methods
Let's first look at several ways to submit a ...
How to implement h5 input box prompt + normal text box prompt
XML/HTML CodeCopy content to clipboard < input...
Detailed explanation on how to modify the default port of nginx
First find out where the configuration file is wh...
Implementation of Vue top tags browsing history
Table of contents nonsense Functions implemented ...
Vue local component data sharing Vue.observable() usage
As components become more detailed, you will enco...