Featured Database Articles

Don’t you just love getting that email with the subject: “The application is slow! Please check the database!”? You’re then expected to very quickly figure out what’s behind the slowdown – usually with zero context information – and, if it is a database issue, get it running at full throttle again ASAP.

If the users only knew what checking the database entails in this situation. With so many causes and solutions to go through, you might expect to be spending the night at work, instead of in your warm bed. (Sadly, I’ve seen it happen.)

In order to avoid that cursed fate, read on to learn two ways to get at the root cause of slow MySQL performance using a couple of built-in MySQL features: the Slow Log and Performance Schema.

The MySQL Slow Query Log

It’s been my experience that, under normal utilization, the most common cause of database slowdowns are out of control queries that hog all of the system resources while trying to retrieve every transaction – i.e. purchase, case, etc. - for the past five years.

You can home in on these queries using the slow query log. It consists of SQL statements that took more than the number of seconds in the long_query_time system variable to execute and required at least the number of rows set in the min_examined_row_limit variable to be examined.

You can check if the MySQL slow query log is enabled using the following statement:

If you don’t currently have the Slow Query Log enabled, I would suggest that you do so before the next crisis strikes. Here’s how:

Shut down any applications that are using MySQL.

Shut down MySQL itself.

Add the following configuration options to my.ini or my.cnf (see the “Where is the MySQL Configuration file located?” section for more information):

# What's the threshold for a slow query to be logged?
long_query_time=0.5
# Where should the queries be logged to?
slow_query_log_file=/path/to/logfile
# Enable slow query logging - note the dashes rather than underscores:
slow-query-log=1

Save the file, and restart MySQL.

Restart your connected applications.

Where is the MySQL Configuration File Located?

The location of the MySQL configuration file (either my.ini or my.cnf) depends on your OS.

MySQL will look at each location in order, and use the first file that it finds. Often, if the file does not exist, it must be created first.

Linux based MySQL systems will use configuration files in the following order of precedence:

etc/my.cnf

etc/mysql/my.cnf

SYSCONFDIR/my.cnf

$MYSQL_HOME/my.cnf

SYSCONFDIR refers to the directory specified when MySQL was built; and typically reverse to the etc directory located under the compiled-in installation directory.

MYSQL_HOME is an environment variable referring to the path where my.cnf can be found.

Windows based MySQL systems will use the configuration files in the following order of precedence

%PROGRAMDATA%\MySQL\<MySQL ServerVersion>\my.ini

%PROGRAMDATA%\MySQL\<MySQL ServerVersion>\my.cnf

%WINDIR%\my.ini

%WINDIR%\my.cnf

C:\my.ini

C:\my.cnf

INSTALLDIR\my.ini

INSTALLDIR\my.cnf

Viewing the Slow Query Log

MySQL provides a tool called mysqldumpslow, which can be used to analyze the log file.

The following syntax will show you the top 10 queries sorted by average query time (Remember to update the file names and paths to suit your environment):

The important values to look at here are the Count and the Time. The Count is the number of times this query ran within your log set. The Time is an average amount of time for each of those queries runs to complete. With the number in parentheses, in this case 6183s, being the total (Count x Time) amount of time spent on running this query.

The Performance Schema

It provides details about query execution in a structured way, accessible through SQL. PERFORMANCE_SCHEMA is a storage Engine that is only used for special tables in the performance_schema database. It contains 52 tables and no views. These are a mix of configuration and data tables. In fact, these can be further broken down into:

Conclusion

During those inevitable slowdowns that plague all relational databases, you’ll be thankful to be managing MySQL. Its Slow Log and Performance Schema are both indispensable in determining the cause of many database-related issues, including poor performance.