Logging slow queries in MySQL

Hello,

I am looking for a way to log queries that are taking a while on a MySQL server v5.1.69.  I am unsure whether or not to do this in the my.cnf file or the console of MySQL?  

Is there a way to specify the time value that it would log these queries?  Could the value be set under a second? I have been looking through MySQL documentation but no luck yet.

Thanks for viewing, I'll provide as much details as needed.
W2MarketAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
xtermConnect With a Mentor Commented:
In /etc/my.cnf simply put the following:

slow_query_log=1
slow_query_log_file=/var/lib/mysql/mysqld_slow_queries.log

Here is an example of what an entry would look like after a slow query:

# Time: 131125  1:11:14
# User@Host: mysql[mysql] @ localhost []
# Query_time: 12.612983  Lock_time: 0.008906 Rows_sent: 0  Rows_examined: 0
SET timestamp=1385363474;
DELETE FROM `messages_252` WHERE `date_retrieved` < DATE_SUB(CURDATE(), INTERVAL 1 MONTH);
0
 
W2MarketAuthor Commented:
Thank you for the reply xterm.  I've tried modifying the log so it looks at queries under 1 second, but haven't had luck in doing so.  Is there a way to adjust that value so it only logs queries under a specified time limit?
0
 
xtermCommented:
By definition, a slow query is one that is OVER a specified time limit, specifically $long_query_time which is an environment variable that you can set (the default is 10 seconds)

You could set that to 0 seconds which in essence would log everything, but there's no way that I'm aware of to log queries under a certain limit - those would actually be "fast" queries, if you think about it...
0
 
W2MarketAuthor Commented:
Thank you for answering
0
All Courses

From novice to tech pro — start learning today.