Solved

Logging slow queries in MySQL

Posted on 2013-11-25
4
596 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Linux Hosting 16 102
can i read my emails on lamp ftp 4 67
pvcreate issue 5 93
umask commands 5 12
Introduction We as admins face situation where we need to redirect websites to another. This may be required as a part of an upgrade keeping the old URL but website should be served from new URL. This document would brief you on different ways ca…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
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.:
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

738 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