Enable Logging of Slow Queries (Slow Query Log) in MySQL Database

»»»Enable Logging of Slow Queries (Slow Query Log) in MySQL Database
One of the main requirements for a fast web server is to has efficient and effective SQL statements or queries that are optimized. Any non-optimal SQL (Structured Query Language) commands or statements that take too long or lengthy time to execute will use up a lot of system resources, causing MySQL database to run slower, and then more and more queries backlogs queuing up, and when connection limit is reached, visitors are been denied or refused connection. In worst case scenario, your web server will go down as well, or continuously underperformed. The case is especially true when you are using MyISAM table type which uses table-level locking instead of row-level locking in a high traffic website.

Sometime, a single SQL query may be the cause of all the server’s problems. MySQL has built-in functionality to capture slow query log or identify queries that are not optimal and take a long time to finish, which allows you to log all slow running queries which took over defined number of seconds to execute by MySQL database engine to a file. Slow query log is not activated or on by default MySQL installation, thus it is one of the less-used logs.

To enable slow query log, simply add the following line to MySQL configuration file (my.cnf or my.ini), and then restart the MySQL server:

log-slow-queries

Or,

log-slow-queries = [path to the log file]

Replace [path to the log file] with actual path to the slow query log file you want the MySQL to write the log to, which is the optional value.

Or you can start mysqld with with the –log-slow-queries[=file_name] option to enable the slow query log. In both syntax, if no log file name is specified, the default name is host_name-slow.log, stored in the MySQL data file directory. If a filename is given, but not as an absolute pathname, the server writes the file in the data directory too.

After enabling slow query log, MySQL will create, capture and log to the log file with all SQL statements that took more than long_query_time seconds to execute, which is by default set to 10 seconds. The time to acquire the initial table locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might be different from execution order.

You can then examine all the slow SQL queries in the log file, and then take the necessary steps to optimize the SQL statements. The slow query log will tell you about what was time the query completed, how long the query took to run, how long it took to secure its locks, how many rows were sent back as a result, how many rows were examined to determine the result, which database was used, and the actual query itself. But bear in mind that a SQL query contained in the log may have already optimum, but executed slowly due to the system resources been used up by the actual slow statement that need to be fine tuned.

By | 2016-12-09T08:40:36+00:00 December 9th, 2016|Categories: Databases|Tags: |6 Comments

About the Author:

LK is a technology writer for Tech Journey with background of system and network administrator. He has be documenting his experiences in digital and technology world for over 15 years.Connect with LK through Tech Journey on Facebook, Twitter or Google+.
  • i try to do that but this message appeared

    081101 5:34:51 [ERROR] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

    081101 5:34:51 [ERROR] Aborting

    081101 5:34:51 [Note] mysqld: Shutdown complete

    what should i do in this case?

  • I can't really confirm this – I enabled slow loggin in the appropiate config file (debian lenny, mysql 5.0 latest) because I HAD "some" slow queries before.

    After enabling there are thousands of "slow queries" being logged – The slow limit is set to the default of 10 sec. This is really nonsense as there can't be a >10sec query when the whole page is being calculated, by php, tranferred over the net and rendered on the client browser in under 5 sec

    greetings Marcus

  • Robert Nolan

    I use MONyog Query analyzer to analyze slow queries. It works flawlessly.

  • when you go to my.cf you will see that by default the lines

    log_slow_queries = /var/log/mysql/mysql-slow.log &

    long_query_time = 1

    are commented out, however, the next line

    log-queries-not-using-indexes

    is not, so if you enable slow query logging then by default all queries not using indexes will show up in that log. For most DBs this will be a ton of data which is why your logs are filling up so quickly.

    My suggestion is comment out this line

    log-queries-not-using-indexes

    and change the long_query_time value to something low like 1 or 2, this should give you some meaningful results with which to start from.

    If you still get no results it might be that you have no slow queries in which case you should uncomment out the line

    log-queries-not-using-indexes

    and start trying to index some of those queries.

    hope that helps someone

    -Kain

  • Anse

    Note that since MySQL 5.1.29, the –log-slow-queries option is deprecated. Instead, use –slow_query_log=1 and –slow_query_log_file=file_name now.

    What I find interesting is that you can also let MySQL dump these queries to a table instead of a logfile, which makes it much easier to read them, get the top 10 of most reoccuring ineffective queries, and more. Basically, you have to set –log-output=TABLE, so MySQL logs to some system table in the "mysql" database. Supported since MySQL 5.1.6. See http://dev.mysql.com/doc/refman/5.1/en/log-tables.html

  • Đô Cai

    xxx

Pin It on Pinterest

Share This

Share This

Share this post with your friends!