Factors Affecting Performance

The performance of your system will be affected by a number of factors. Here are the main factors, roughly in order of impact:

• How your columns and tables are defined, and what table types they have

• How your application uses MySQL

• How your application runs its queries and how the MySQL optimizer processes them

• The MySQL system variables

• Your operating system and hardware

• How mysqld is compiled

We will go through these factors in turn. This should enable you to get a good grasp of them, and you will learn what you can do in each respect to improve performance.

Whatever tactics you may come to use, always run benchmark tests to measure the impact of any change. Carry out timings before and after any adjustment. You will usually need to measure query time in milliseconds, and bear in mind that you should try to do so both with and without the impact of other system loads.

Table Design

Table design is probably the biggest influencer of database efficiency. Because databases access disk drives where tables reside, getting data to and from tables is critical. It's also at the top of our list because it's the one factor you, as an application developer, may be most able to influence.

Using Indexes

In Day 9, “Joins and Indexes,” you studied how to perform joins and use indexes. Joins are often among the most time-consuming database operations because they involve examining rows from several different tables.

As you saw in Day 9, indexes (in general) improve performance because they preserve a sorted copy of the data in one or more columns. The general rules are that you should apply an index to a column

• When that column appears in a WHERE clause

• When that column is used in a GROUP BY or ORDER BY clause

• When that column is used in a join operation

However, indexes should perhaps not be used in some cases:

• Don't apply indexes on very small tables; they won't help.

• Don't apply indexes on columns where a WHERE operation will return more than one-third of the rows.

• Don't apply indexes on tables to which data is added or modified more than it is read; updating indexes wastes time.

Always try to use a primary or unique key if possible, or failing that a non-unique key. Finally, where there are large columns (such as TEXT), use only short indexes.

Column Specifications

When specifying the columns of a table, try to keep data stored in the smallest possible space and use fixed-length columns where possible.

Here are some guidelines:

• Keep columns as small as possible. The less disk access that has to be performed, the better.

• Use fixed-length columns if you can, such as CHAR rather than VARCHAR. If MySQL knows that records are evenly spaced on the disk, it can move from one to the next more efficiently.

• Use NOT NULL columns; it saves on storage and allows faster processing.

• Use an ENUM column type where possible. Because ENUM columns are stored as numeric values, they are faster to access. Use an ENUM where a column can contain a limited number of values from a set that can be specified.

When you create a table, you will often do so with VARCHAR or TEXT columns. Even if you specify a CHAR rather than VARCHAR, under certain circumstances MySQL will perform a silent column change to a VARCHAR type. Tables with VARCHAR columns are known as dynamic tables because they can dynamically adjust column size to suit the data within them.

But dynamic tables are not the fastest. Each row of data has to store not just the raw data but information about the size of the data as well; and the data records themselves are not stored with predictable spacing on the disk.

To optimize speed, you may prefer to create fixed (also known as static) table types. With this kind of table, all columns are of fixed size. A table can be fixed if it has only columns such as CHAR, ENUM, and numeric types, and avoids VARCHAR, TEXT, and BLOB columns.

If you need to store TEXT or BLOB data, but require fast access to it, consider splitting your data into two tables: one that is static and holds summary information, and a second that is dynamic and holds the TEXT or BLOB data. For example, if you had an online library, you might store data about your documents—titles, keywords, creation dates, permissions—in a fast static table, and the contents of the documents in a slower dynamic table.

You can see what types of tables you have by running SHOW TABLE STATUS and looking at the Row_format information for each table, which will be stated as Dynamic or Fixed.

Table Types

The default table type for MySQL is MyISAM. This is a tried-and true table type, and has the least number of restrictions in terms of MySQL functions and commands that cannot be used with all types of tables.

But a faster type is the HEAP table. Although MyISAM tables are stored on disk, HEAP tables exist only in memory. This makes them inherently faster.

For applications that don't require the safety of a table stored on disk (which would preserve data permanently), consider a HEAP table for data that must be accessed with maximum speed.

If your system has tables that are read-only, you should also consider using compressed tables. The myisampack utility can be used to compress tables by a considerable amount. Being smaller on the disk, they are faster to access. See Day 15, “Administration,” for more about creating myisampack compressed tables.

How Your Application Uses MySQL

How you write your application, and how you write your queries, will have an immense impact on your system's performance.

For example, don't run queries repetitively if you don't need to; take the processing from your application code into your MySQL queries if you can so that you select only what you need to, rather than sift through it in your application.

Use multitable joins with care (you'll look at how to assess the performance of joins in a moment); as well as being difficult to debug, they may create huge resultsets that consume memory, causing MySQL to automatically create temporary tables on disk. For example, it may be better to remove the join against the smallest table and run a first query to create a short list of comma-separated values, value1, value2, and so on; then run a second query using WHERE...column IN (value1,value2,...).

In busy systems, you should not only consider which queries take the longest but also those that are run most frequently. A seldom-used query that takes 5 seconds to run may have less adverse impact than one that takes 0.1 seconds but runs 50 times a second!

Perhaps it's obvious, but by no means less important: remember that the language you choose to write your application in will influence its execution speed. See Days 11, 12, and 13 for discussion of the various merits of three possible APIs: PHP, Perl, and C.

Tuning MySQL with Its System Variables

MySQL has tools for helping you improve performance. You can use mysqladmin to display the variables with which the server is running:

# mysqladmin -p --show-variables
							

The same is accomplished with the query:

mysql> SHOW VARIABLES;
							

This returns a long list of items, all of which play some part in performance. However, the essential performance-related variables, and their effects, are described in Table 18.1. The most important of them are key_buffer_size and table_cache.

You can set these, and other variables, in my.cnf. If you don't already have my.cnf set up, your distribution will contain a support-files subdirectory in which you will find some sample my.cnf files, called things like my-small.cnf and my-huge.cnf. They contain starter setups for various sizes of machines.

Change to your source or binary installation directory and view these configuration files to find the one best suited to your server. Which one you choose will depend principally on how much RAM you have. Copy the file to where MySQL can read it; for example, on a 1GB system:

# cp support-files/my-huge.cnf /etc/my.cnf
							

Then restart MySQL. Experiment with setting other variables if you want, restarting to see what effect the change has.

You can also change MySQL's system variables by declaring a variable when you invoke mysqld_safe (or safe_mysqld in MySQL version 3). For example, to start the server and set query_cache_size to 16MB, you could invoke MySQL like this:

# /usr/local/mysql/bin/mysqld_safe -O key_buffer=16M
							

Note that a variable set in this way will lose its value after the current invocation of MySQL terminates.

Operating System and Hardware

Look at the operating system and hardware you're running on when addressing performance issues.

Memory is at the top of the list here. MySQL runs faster when it has a lot of memory to work with. As you saw previously when looking at the MySQL server variables, many things can be cached in memory to speed up performance. Give your system as much memory as you can afford. 1GB of RAM will help a busy system work well.

Provided that you have a good deal of RAM, you may also be able to reconfigure your operating system to stop all disk-swap operations. It's pointless to swap temporary files to disk when you have plenty of space in RAM.

Another important consideration is the disk speed. To be precise, it's seek time—the time to move the magnetic head across the surface to the right location—that matters. Disk reading and writing, when in the right place, will be quick by comparison.

If you really need the ultimate in performance, you actually need not one disk but several. Consider a second or third drive, or RAID-0 configuration (in which data is striped across several disks). This allows a read or write to occur on one disk while the other disk performs a seek, so that it's in the right place when the first operation is finished.

Consider the operating system itself. Much of this book is devoted to running MySQL on Linux, but of course it can be run on flavors of Unix and on varieties of Windows. Because MySQL was originally written for Intel machines with Linux, it will run faster on Linux than on Windows.

Finally, on a really heavy-load system, consider using replication. A MySQL database can be replicated across several machines, which with the right load-balancing configuration can make a high-traffic Web site still appear very fast. See Day 19, “Replication,” for more about replication.

Compilation Options

How MySQL is compiled affects performance. The binary and RPM distributions of MySQL are compiled for speed and usually hard to beat, but if you have a compiler tuned specifically for your hardware or operating system, you may be able to compile MySQL yourself and squeeze out an improvement of a few percentage points.

If you're compiling from source yourself, two compile-time options can help execution speed. You should run configure like this:

# ./configure 
> --enable-assembler 
> --with-mysqld-ldflags=-all-static
							

The first option tells the compiler to produce assembler code, and the second tells it to produce statically linked libraries.

Remember, at this level, we're dealing principally with the speed of execution of the compiled binary. Therefore RAM and disk speed typically have a far greater effect.