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:

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

%d bloggers like this: