Praji’s Weblog

Welcome to praji’s world

Slow query logging in mysql

leave a comment »

A query is considered as slow if it takes more than “long_query_time” seconds to execute. By default, the value of this variable is set to “1”.

1). Identifying MySQL slow queries

First let’s check on the mysql server if we have slow query logging enabled:

mysqladmin var |grep log_slow_queries | log_slow_queries | OFF

2) . Activate the logging of mysql slow querie
MySQL allows  to log slow running queries to a file. To enable slow query logging, just add the following lines to /etc/my.cnf, and restart mysql.

log-slow-queries = /var/log/mysql/mysql-slow.log
long_query_time = 1

This configuration will log all queries that take more than 1 sec in the file /var/log/mysql/mysql-slow.log.

Another alternative for enabling this feature is to restart mysql with the –log-slow-queries[=file_name] option.

2. Investigate the mysql slow queries log

MySQL gives us a small tool that does exactly this: mysqldumpslow

For example using:

mysqldumpslow -s c -t 10

We get the top 10 queries (-t 10) sorted by the number of occurrences in the log (-s c).


Written by praji

June 28, 2008 at 4:12 am

Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: