MySQL Tuning: Getting the most out of your database

This article will deal with a few basic MySQL Tuning options that can make queries run faster on your MySQL database. This problem can be approached from several different angles. The solution for poor performance in most cases is one that uses a combination of techniques.

When you think about making your database faster, you may think of upgrading hardware or tweaking MySQL's buffer sizes. In some cases these steps may be necessary, but to get the most out of your database, regardless of how powerful your server is, you need to have a well designed schema and well written queries. Poorly written queries may work, but they will most assuredly not work efficiently.

This article will focus primarily on using indexes to perform MySQL Tuning in order to speed up your MySQL queries.

If you have a query that is running more slowly than you would expect, the first thing to do is to get a baseline measurement, so you can gauge the effectiveness of your optimization efforts. A simple way to do this is to run the query from the command line in MySQL. The time it takes the query to execute will show at the bottom after the results appear.

I have a query that is taking a long time to execute, and I want to optimize it. There are three tables involved and the structure of each is identical. Here are the specifications of the tables as displayed by the explain command:

The tables contain information about students at a particular school, including names, scores, and the dormitories where they stay. Two are permanent tables for individual classrooms, the other is common table that collects the data from each classroom's table when needed and then is emptied. This query returns the set of students from room 10 and room 11 who reside in a dormitory designated as “BR”, along with a brief summary of some of their points. Here it is:

As you can see, the cost of the three lookups is high, and the execution time is rather slow (1.80 sec). In order to see how the query is being processed, let's use the “explain” command again. This is done by simply prefacing the query with the word “explain”:

While the explain command is useful for quickly examining table structure, it is even more useful in showing how MySQL is processing queries. The column headed “Key” shows us if there have been any indexes created on the table.

Indexes can greatly increase the speed of database reads by mapping a particular column of a large table to a set of ordered values in an index.

For example, we can see that there is an index set on the “id” field in the table above. If our query was initiating a search based on the “id” field - let's say searching for rows where the id was no greater than 10, MySQL would first consult the index, in which the id values would be sorted in ascending order, and go directly to the rows that were mapped to those first ten values. The search would stop as soon as the the id of 11 was reached. The database would not have to read any “wrong” rows.

This is a tremendous performance advantage in a large table, and this is what makes indexing the single most powerful optimization tool available to the MySQL DBA.

Unfortunately, in this case, the index on the id field is not of much use. The query does not search on the id field. When optimizing a particular query, look at the where clause to find fields that are good candidates for indexes.

The where clause in this query searches on the field build, and the date field. I'm going to choose the date field because it has better cardinality than the build field. This is because there are many more rows with unique dates than there are rows with unique buildings. So, let's try adding an index on the date field in all 3 tables and rerun our query.

To add the index we'll use the ALTER TABLE command. The syntax works like this:
ALTER TABLE table_name ADD INDEX index_name (column);

So we will add the indexes like this:
ALTER TABLE room_10 ADD INDEX date_10 (date); and
ALTER TABLE room_11 ADD INDEX date_11 (date);

OK, let's rerun our query:

As you can see this simple measure has increased the efficiency of our query by quite a bit, decreasing the execution time from 1.80 seconds to 0.03 seconds. Looking at the the explain output in the “Key” column below shows that MySQL now is using the date indexes for the lookups.

The proper use of indexes is one of the best MySQL Tuning ways to increase performance in your MySQL database. Remember to choose the fields most commonly used in your where clauses for index creation, and among these choose the ones that have a high cardinality - more unique rows relative to the total number rows in the table.

Within a given table this simply means choose the field with more unique rows; if you are choosing between fields on different tables, as in the case of a join query, choose the one that yields a higher value when you divide the number of total number of rows in the table divided by the number of unique values in the field.

After a lack of good indexes, poor schema design probably ranks as the second most common cause of poor performance on MySQL. The above example is a solid proof of that: Would you have created such tables?

This topic is covered in another article in this series entitled MySQL Database Design.

After optimizing your queries with indexes and making sure that you have a sensible schema in place, you may still get some performance gains by making sure that your server parameters are set appropriately for your hardware.

MySQL has a configuration file called my.cnf. It is usually located in DATADIR/my.cnf. This file determines how MySQL uses your system memory (RAM) and sets the sizes of various buffers.

The default settings are rather conservative. Also, safe_mysqld, the script that monitors the MySQL daemon (also called a process), contains settings that determine the priority level that MySQL is given when competing with other processes for system resources. A good starting place for your MySQL Tuning is to make sure that MySQL has enough resources available to perform well. If you are running MySQL server on is a dedicated server, in other words, its main function is to host your database, then your system should give top priority to the MySQL process. On a Unix system this is done by “renicing” mysqld.

In Unix or Linux, processes with a high nice value rank very low in priority. These processes are “nice” and let other processes use cpu and memory resources ahead of them. The highest nice value is +20, the lowest is -20. On a dedicated server you want to give mysqld free rein to hog all the resources it wants.

To set msqld's nice value to -20, the following line should be added to the safe_mysqld script (often located at /usr/local/mysql/bin/safe_mysqld):

renice -20 $$

If you have other processes running on the server, and you find that this setting is a bit too aggressive, you can adjust it. Settings of -10 or even -5 may suffice on your system to give MySQL the resources it needs to run well.

Now that our appetite for editing configuration files is whetted, lets take a look at the my.cnf file. Below is the output of my my.cfn:

Our main focus is the [mysqld] section. Actually MySQL ships with four sample my.cnf files for different hardware configurations. These values are actually taken from my-huge.cnf. As the name suggests, the values are set for machines with relatively large amounts of RAM -- 1GB or more, and are dedicated database servers.

If you have a gig of RAM and also run your web server on the same machine or have 512 MB of RAM on a dedicated server, you should probably use my-large.cnf. my-medium.cnf is for machines with 128 MB of RAM.

Some things to remember before you set out to tweak your system and your database. Take measurements of your queries execution times before you start and test again after each change you make. That way if you get an unexpected result you'll know what the problem is.

If you have changed 10 things since you last tested a query, you'll have “fun” figuring out what needs to be changed back. Take an organized and scientific approach, save your benchmarking data, backup your original configuration files, and happy MySQL tuning!

Return to MySQL DBA