mysql slow query

For optimizing mysql database we got several methods to archive that one is to analyze slow query. to enable slow query logging we need to add following in my.cnf file:

log-slow-queries=/var/log/slow.log

by default this will log queries that takes more than 10 second, if we want higher value we can specify it as well

long_query_time=12

The next step is to analyze slow.log file. Instead of doing this in a old fashion way with cat tail or whatever you use its more efficient to use Mysqldumpslow command

mysqldumpslow /var/log/slow.log

I often use mysqldumpslow command with -a and -t arguments.  -t shows top slow queries based on slow.log file. You can also specify how many top slow query you want to be displayed:

mysqldumpslow -t 10 /var /log/slow.log

As of -a argument  by default mysql abstracts all numbers to N and strings to S, so by using -a  you will get original information without abstraction.

for more details you might need to read mysql official documentation 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s