Deciphering MySQL Logs: The What, Why, and How

Last updated at Mon, 06 Nov 2017 20:21:20 GMT

Logs are one of the best ways to understand what a server is doing. Thankfully, MySQL has no shortage of log activity to assist a DBA in its maintenance. It writes out its activity to 5 different logs.  This post will take a look at the existing MySQL logs and how they assist the administrator.

• On Windows, - The log is written to the data directory with a .err extension even if not explicitly enabled.
• Errors are automatically written to the Event Log. This behavior is standard and can not be disabled.
• Can be configured to write to a specific file or the console.
• On Unix, - Error messages are written to the console (stderr) if not configured otherwise.
• MySQL can be configured to output to the syslog, a file on disk, or the console.
• If using syslog, make sure to use the “–syslog-tag=tag” flag so that error entries are easily retrievable and are not lost to log noise.

Always ensure that error-logging is on so that problems can be diagnosed when they occur.  Even after they occur, error logs need to be kept around.  Error history is important to maintain so when flushing logs for space, make sure the current error log is saved in a renamed file for later reference.

“The main problem administrators may have with the error log is translating error messages”

The main problem administrators may have with the error log is translating error messages. Error messages in the logs can sometimes be confusing.  The developers of MySQL have created a handy tool to help, perror.  Perror can translate error numbers to error messages which greatly simplifies the debugging process.  This is an important tool to be familiar with.

The General Query Log records client activity including client connections, client disconnections, and client SQL queries.  The general query log is an effective way to audit application use of the MySQL database. This log is not immediately helpful in diagnosing problems, so an administrator should consider turning this log off (especially in production) when not needed.

The Slow Query Log is the spiritual sibling to the General Query Log.  It also tracks query statements, but in this case it is tracking only the troubled ones.  The slow query log records queries that take too long and should be optimized, if possible.  By default, it tracks queries that take longer than 0 seconds, but thankfully this number is configurable.  The query must also read a minimum number of rows.  This value defaults to 10, but is also configurable.  By default, this log is not enabled and will write into the data directory to host-name-slow.log.  This is an excellent tool for finding optimizations and tracking down troubling queries.  This log is best used during development or troubleshooting but need not be on all the time.

Being able to decipher which queries take too long because they need to be optimized versus those that took too long due to external factors could seem daunting.  Poring over logs looking for patterns is never a fun exercise.  Luckily, the MySQL team has a tool for this too, mysqldumpslow.  Running this will group all the queries in the log together, and display the average run time, the average lock time, and the average number of rows sent for each group.  This small application will make parsing through the Slow Query Log much easier and faster.

“The Binary Log has been somewhat controversial in MySQL 5.x releases.”

The Binary Log has been somewhat controversial in MySQL 5.x releases.  There has been some reproducible evidence that early versions of 5.x releases saw a significant performance degradation when this log was active.  Some in the community would suggest never turning this log on to avoid the performance slowdown.  The problem seems to have gotten better in the later MySQL 5.x branches. This is good since this is another crucial log.

The Binary Log tracks all queries that change a database: from database schema changes to data changes.  This allows databases to be restored to a point in time.  This is especially useful for restoring a database from backup.  The MySQL server will run the queries in the Binary Log in sequence to restore a database to the state of the last successful operation.  Failed or incomplete operations are not stored in the Binary Log.  If you are using replication, then this MySQL log is a requirement.  This log is used in replication for the master to send changes to the slave servers.

The DDL Log is not the log you are looking for. The DDL Log is used by the server to track metadata in case a recovery from a crash is required.  The server starts it the first time a metadata operation occurs.  It is stored in binary form and is not human readable.

“There are a few things you can do to minimize the impact your logs have on performance.”

There are a few things you can do to minimize the impact your logs have on performance.  Firstly, create a rotation schedule for your logs,  Logs that are important for historical purposes, like the Error Log, and potentially the Binary Log, should be renamed and archived.  Rotate and flush them in whatever window works best for your support requirements but doesn’t allow the logs to get so large they impact performance.

Secondly, there is some evidence that storing certain logs on separate disks can improve performance.  This is especially recommended for the Binary Log.  (One succinctly described example is this post by Peter Zaitsev from his 2008 post for Percona.com).

Thirdly, consider using third party tools which will allow for log automation, notifications, searching, trend identification, etc.  For example, Logentries provides a great deal of functionality surrounding log management.  Using the Windows agent or Linux agent log data can be sent to the server for review with a large set of tools.

Finding the balance between logging and performance can be one of the trickiest parts of maintaining a database in the long term.  Knowing what logs are available and how they work will make administering MySQL that much easier.  Using the logs and tools provided, it is possible to create a highly performant and well maintained MySQL server.

(At the time of this writing, the latest version of MySQL is 5.7.11)