Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 616
  • Last Modified:

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.
0
W2Market
Asked:
W2Market
  • 2
  • 2
1 Solution
 
xtermCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now