Google+

Enable slow-query Logs in MySQL

3

August 12, 2012 by harshalvaidya

How To Enable Slow – Query Logs in MySQL Server in Linux

MySQL has easiest and conveniently hand feature i.e slow query log which allows you to log all queries which takes long to execute.

Purpose of Logs :-
1) Using mysql slow query logs, we can easily track the queries that takes time in consuming the specified operations.

2) It helps in ultimate optimization of query and hence the databases. which is indeed needed in speeding up the webpages that rely on databases.
##################################
Step:1 Stop the mysql service using below command
##################################
# service mysqld stop

Stopping Mysql service is completely optional here. you can skip.

###############################################
Step:2 Edit the mysql configuration files (/etc/my.cnf) and add below lines
###############################################
[mysqld]
log-slow-queries = [path fo slow query logs]
long_query_time = 20 (you can set any seconds)
log-queries-not-using-indexes (not necessary)

#####################################
Step:3 Now start the mysql service using below command
#####################################
# service mysqld start

 

log-slow-queries :- It turns on slow query log and logs all slow queries to th specified path.
long_query_time :- It indicates that any queries that took more than 20 seconds to execute need to be logged. In general practice, we used to set logs_query_time=0, which will track each and every query.
log-queries-not-using-indexes :- It tells MySQL to log *any* queries that do not use indexes.

If you like you can set the long_query_time very high so that only the queries that do not use indexes are logged (and no ‘general’ slow queries).

 

You can check the path to log the slow query records :-
Output is something like :-

# User@Host: root[root] @ localhost []
# Query_time: 0.000652 Lock_time: 0.000109 Rows_sent: 1 Rows_examined:1SELECT id FROM users WHERE name=’admin’;
# User@Host: root[root] @ localhost []
# Query_time: 0.001943 Lock_time: 0.000145 Rows_sent: 0 Rows_examined:0 INSERT IGNORE INTO articles (id, body,)VALUES(1101,’demo edit text’);

Advertisements

3 thoughts on “Enable slow-query Logs in MySQL

  1. I do agree with all the ideas you have offered for your post. They are really convincing and will certainly work. Nonetheless, the posts are very short for novices. May just you please lengthen them a little from subsequent time? Thanks for the post.

  2. Seo Pressor says:

    After reading your blog post I browsed your website a bit and noticed you aren’t ranking nearly as well in Google as you could be. I possess a handful of blogs myself and I think you should take a look here: http://seopressors.org You’ll find it’s a very nice tool that can bring you a lot more visitors. Keep up the quality posts

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

Blog Stats

  • 774 hits

stat

var infolink_pid = 1084591; var infolink_wsid = 0;
%d bloggers like this: