MySQL turns on slow query log and verification process

MySQL Slow query

1, slow query role?

It records all SQL statements that execute more than long_query_time time, helping you to find SQL that executes slowly, so we can optimize these SQL statements.

2, how to open the slow query?

First we check if the MySQL server's slow query status is turned on.

Execute the following command:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/7F/A9/wKiom1coHYDD65rsAAAwCDMf8M4016.png "title=" Qq20160503113819.png "alt=" Wkiom1cohydd65rsaaawcdmf8m4016.png "/>

We can see that the log_slow_queries status is on, which indicates that a slow query is currently turned on. If not, do the following:

Method One: Locate the MySQL configuration file my.cnf (/ETC/MY.CNF), add the configuration statement for the slow query under MYSQLD ( Note that it must be added below [mysqld], if it is in [Mysqld_safe] The following configuration statements are not valid. I have made this mistake during the configuration process and experienced the pain!)

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/7F/A7/wKioL1coIKGS_egCAAA2ncDcEGY556.png "title=" 1.png " alt= "Wkiol1coikgs_egcaaa2ncdcegy556.png"/>

Log-slow-queries: On behalf of the MySQL slow query log storage directory, this directory file must have write permissions

Long_query_time: Maximum execution time (MySQL will record all SQL statements with execution time exceeding 2 seconds, the test time should not be too short);

After you have configured the MySQL service to restart, services mysqld restart

Method Two: Perform the following operation under the MySQL command:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/7F/A9/wKiom1coIfPyBNCEAAAPQSsYTAE774.png "title=" 2.png " alt= "Wkiom1coifpybnceaaapqssytae774.png"/>

The state of the MySQL variable is set by using the SET command, but this is only temporary and will revert to the original state after restarting the service.

After the configuration is complete, let's check the status, such as:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/7F/A7/wKioL1coIy_CocRNAAAyzy2LZDA253.png "title=" 3.png " alt= "Wkiol1coiy_cocrnaaayzy2lzda253.png"/>
Indicates that the slow query service has been turned on and the time limit is 2 seconds.

3. Slow query Log Verification

We are looking at whether the Mysql-slow.log file is created automatically under the/var/lib/mysql/directory.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/7F/A9/wKiom1coIxXgI72XAAA_XEDEn6o597.png "title=" 4.png " alt= "Wkiom1coixxgi72xaaa_xeden6o597.png"/>

The Cat mysql-slow.log discovery file already exists. Only the file does not have a record of task SQL. So now let's test the execution of an SQL statement that exceeds long_query_time.

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/7F/A9/wKiom1coI2WxcSJnAAAWGqSAoBo865.png "title=" 5.png " alt= "Wkiom1coi2wxcsjnaaawgqsaobo865.png"/>

After executing a 5-second SQL statement, the SQL statement should be logged in the Mysql-slow.log file without exception. Ok! We cat mysql-slow.log a bit

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/7F/A7/wKioL1coJQXD5P_hAABJ37QesX0507.png "title=" 6.png " alt= "Wkiol1cojqxd5p_haabj37qesx0507.png"/>

Sure enough, the entire log process configuration for slow queries is complete!

This article is from the Linux Technical Exchange blog, so be sure to keep this source http://9678130.blog.51cto.com/9668130/1769657

MySQL turns on slow query log and verification process