Solved

Logging slow queries in MySQL

Posted on 2013-11-25
4
600 Views
Last Modified: 2013-11-27
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
Comment
Question by:W2Market
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 19

Accepted Solution

by:
xterm earned 500 total points
ID: 39675738
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
 

Author Comment

by:W2Market
ID: 39675753
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
 
LVL 19

Expert Comment

by:xterm
ID: 39675797
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
 

Author Closing Comment

by:W2Market
ID: 39681591
Thank you for answering
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Connecting to an Amazon Linux EC2 Instance from Windows Using PuTTY.

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question