Enable mysql slow query log and run the mysqldumpslow command to View _ MySQL

Enable mysql slow query log and run mysqldumpslow to view mysqldump

BitsCN.com

Enable mysql slow query logs and run the mysqldumpslow command to view the logs.

The mysql server has a function to detect which SQL statement query is slow, that is, slow query slowlog. now we will introduce how to enable this function.

Add the following code under [mysqld:

Long_query_time = 1

Log-slow-queries =/usr/local/mysql/data/slow. log

Log-queries-not-using-indexes

Long_query_time = 1 # defines the Slow_queries variable for queries that exceed 1 second.

Log-slow-queries =/usr/local/mysql/data/slow. log # defines the log path for slow query.

Log-queries-not-using-indexes # queries without indexes are recorded in slow query logs (optional ).

Mysql comes with mysqldumpslow, a tool for viewing slow logs.

Run mysqldumpslow -- h to view help information.

This section describes two parameters:-s and-t.

-S: this is the sorting parameter and can be selected as follows:

Al: average lock time

Ar: average number of returned Records

At: average query time

C: Count

L: lock time

R: return record

T: query time

-T n: the first n records are displayed.

Instance:

Mysqldumpslow-s c-t 20 host-slow.log

Mysqldumpslow-s r-t 20 host-slow.log

The preceding command shows the 20 most frequently accessed SQL statements and the 20 most returned SQL statements in the record set.

Mysqldumpslow-t 10-s t-g left join host-slow.log

Return the first 10 SQL statements containing the left join according to the time.

With this tool, you can find out which SQL statements are performance bottlenecks and optimize them, such as adding indexes and implementing the application.

BitsCN.com