Audit MySQL query slow.
1. Start slow log.
Two method:
(1) edit my. CNF, add text as belows to [mysqld].
# If long_query_time = 0, all queries will be saved to log file
Long_query_time = 1
# Create the log file and give the log file MySQL access authority by chown mysql. MySQL file_name
Log-Slow-queries [= file_name]
(2) Start mysqld with the -- log-Slow-queries [= file_name] Option.
2. Analyze slow log
I have fond two method to analyze the slow log.
(1) mysqldumpslow which is supplied by MySQL.
Use this command we can see summarizes of the log file.
Mysqldumpslow file_name // eg: mysqldumpslow/opt/test/mysql_slow.log
(2) myprofi. http://myprofi.sourceforge.net /.
Myprofi is a command line tool that parses MySQL query log and outputs statistics of most frequently used queries,
Sorting them by number of times they appear in a log file
Down the source code from http://myprofi.sourceforge.net/, and copy parser. php to centos. Use follow command to analyze the log
// Show all slow queries.
PHP parser. php-slow/opt/test/MySQL. Log
// Show Top N slow queries.
PHP parser. php-Top N-slow/opt/test/MySQL. Log
// Only show select slow queries, we can also add other options such as update, insert, delete.
PHP parser. php-top 10-type "select"-slow/opt/test/MySQL. Log