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 underperform. 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 syntaxes, if not 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 SQL queries that took longer than the pre-defined number of seconds (10 seconds by default) in the host_name-slow.log, 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.
Share and contribute or get technical support and help at My Digital Life Forums.
Related Articles
- Change or Set MySQL Long Query Time Value for log-slow-queries
- Install Web Server in Windows XP with Apache2, PHP5 and MySQL4 - Part 4
- Change and Reset MySQL root Password
- WordPress MySQL SQL Query Error in WPDB Class
- You Are Not Authorized to Execute SQL Queries in vBulletin Maintenance
- How to Backup and Restore (Export and Import) MySQL Databases Tutorial
- MySQL Database Performance Tuning Best Practices Video Tutorial
- Fix for Munin MySQL Monitoring Plugin Graph Not Showing or Blank
- Using PHP-MySQL Persistent Connections to Run WordPress Blog
- How to Move WordPress Blog to New Domain or Location
Entries (RSS)
January 23rd, 2007 00:22
[...] If you enable slow queries log of MySQL database server, you will notice that from the slow query log file contains only SQL statements and queries that took more than query_time of 10 seconds to execute or run. This is the default value or query time threshold for log_slow_queries function, which is 10 seconds. 10 seconds is way too long for a SQL query to complete, so the value may be too high to capture any meaningful data. You can alter or change the slow query time to lower value or number of seconds. You can set a new long_query_time in the MySQL configuration file, namely my.cnf or my.ini, normally located in /etc directory. Simply add the following line to the configuration file: [...]