Solved

Logging slow queries in MySQL

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Join Greg Farro and Ethan Banks from Packet Pushers (http://packetpushers.net/podcast/podcasts/pq-show-93-smart-network-monitoring-paessler-sponsored/) and Greg Ross from Paessler (https://www.paessler.com/prtg) for a discussion about smart network …
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now