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
How to manage multiple projects on CentOS SVN server
One demand Generally speaking, a company has mult...
MySQL 5.6.15 installation and configuration method graphic tutorial under Windows 8
MySQL is a database that I like very much. Today,...
Postman data encryption and decryption to implement APP login interface simulation request
Table of contents Mainly used Postman functions D...
How to deploy SSL certificate in windows apache environment to make the website support https
I won’t talk about the use of SSL certificates. F...
Detailed analysis of javascript data proxy and events
Table of contents Data Brokers and Events Review ...
Detailed explanation of the flexible use of CSS grid system in projects
Preface CSS grids are usually bundled in various ...
HTML pop-up div is very useful to realize mobile centering
Copy code The code is as follows: <!DOCTYPE ht...
How to install docker on Linux system and log in to docker container through ssh
Note: I use Centos to install docker Step 1: Inst...
Parse CSS to extract image theme color function (tips)
background It all started when a classmate in the...
Faint: "Use web2.0 to create standard-compliant pages"
Today someone talked to me about a website develo...
Ajax solves cross-domain problem by setting CORS response header to achieve cross-domain case study
1. Set CORS response header to achieve cross-doma...
Solve the problem of invalid utf8 settings in mysql5.6
After the green version of mysql5.6 is decompress...
VMware + Ubuntu18.04 Graphic Tutorial on Building Hadoop Cluster Environment
Table of contents Preface VMware clone virtual ma...
How to automatically backup the script for Linux servers (mysql, attachment backup)
1. Create the backup.sh script file #!/bin/sh SOU...
How to implement web stress testing through Apache Bench
1. Introduction to Apache Bench ApacheBench is a ...