Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MySQL

Posted Mar 4, 2003

MySQL's Over-looked and Under-worked Slow Query Log

By Ian Gilfillan

The slow query log is one of the less-used logs, as by default it's not activated, but it's a useful log for identifying queries that are not optimal. Often, in an under-performing system, especially with the default MyISAM tables (that make use of table-level locking, not row-level locking), a single query may be the cause of problems.

To activate the query log, simply place:
log-slow-queries = [slow_query_log_filename]
in your configuration file (my.cnf or my.ini), slow_query_log_filename being the optional filename for your log file. If you don't supply a filename, the default name will be used, which is the name of the host machine, with -slow.log being appended.

The slow query log logs all queries that take longer than long_query_time, which is usually 10 seconds by default (more than long enough for a self-respecting query to complete). You can alter the long_query_time in the configuration file. The following example sets the time to 5 seconds:
set-variable = long_query_time = 5
The slow query log can also optionally log all queries that don't use an index by placing the following in the configuration file:
log-long-format

Let's take a look at a sample slow query log:

/usr/local/mysql/libexec/mysqld, Version: 3.23.54-log, started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
# Time: 030207 15:03:33
# User@Host: wsuser[wsuser] @ localhost.localdomain [127.0.0.1]
# Query_time: 13  Lock_time: 0  Rows_sent: 117  Rows_examined: 234
use wsdb;
SELECT l FROM un WHERE ip='209.xx.xxx.xx';

The first few lines simply contain version information, but the really useful data begins with the Time. This tells you what time the query completed. The next line contains user data, and the following how long the query took to run, how long it took to secure its locks, how many rows were sent back as a result, and how many rows were examined to determine the result. The final line before the actual query tells you which database was used. A discussion on optimizing queries is beyond the scope of this article, but you should know something about this before you can get the most out of the log. Otherwise, you may be able to identify the un-optimal queries, but not be able to do much with them. You should read the article Optimizing MySQL: Queries and Indexes if you are unclear about the topic.



MySQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM