Tips and Tricks: MySQL Optimization

Author:

September 2008

Situation
Your business is designing a new MySQL database with growth in mind.

Problem
You know that growth will deprecate database performance over time.

Solution
Optimize
Optimization Overview
The most important factor in making a system fast is its basic design. You need to know what type of things your system will be doing, and any bottlenecks you have in your system.
The most common system bottlenecks are:
•    Disk seeks. It takes time for the disk to find a piece of data. With modern disks, the mean time for this is usually lower than 10ms, so we can in theory do about 100 seeks a second. This time improves slowly with new disks and is very hard to optimize for a single table. The way to optimize seek time is to distribute the data onto more than one disk.
•    Disk reading and writing. When the disk is at the correct position, we need to read the data. With modern disks, one disk delivers at least 10-20MB/s throughput. This is easier to optimize than seeks because you can read in parallel from multiple disks.
•    CPU cycles. When we have the data in main memory (or if it was already there), we need to process it to get our result. Having small tables compared to the amount of memory is the most common limiting factor. But with small tables, speed is usually not the problem.
•    Memory bandwidth. When the CPU needs more data than can fit in the CPU cache, main memory bandwidth becomes a bottleneck. This is an uncommon bottleneck for most systems, but one to be aware of.
MySQL Design Limitations and Tradeoffs
When using the MyISAM storage engine, MySQL uses extremely fast table locking that allows multiple readers or a single writer. The biggest problem with this storage engine occurs when you have a steady stream of mixed updates and slow selects on a single table. If this is a problem for certain tables, you can use another table type for them.
MySQL can work with both transactional and non-transactional tables. To be able to work smoothly with non-transactional tables (which can't roll back if something goes wrong), MySQL has the following rules:
•    All columns have default values.
•    If you insert a "wrong" value in a column, such as a too-large numerical value into a numerical column, MySQL sets the column to the "best possible value" instead of giving an error. For numerical values, this is 0, the smallest possible value, or the largest possible value. For strings, this is either the empty string or the longest possible string that can be stored in the column.
•    All calculated expressions return a value that can be used instead of signaling an error condition. For example, 1/0 returns NULL.


Tips
•    Use INSERT DELAYED when you do not need to know when your data is written. This speeds things up because many records can be written with a single disk write.
•    Use INSERT LOW_PRIORITY when you want to give SELECT statements higher priority than your inserts.
•    Use SELECT HIGH_PRIORITY to get retrievals that jump the queue. That is, the SELECT is done even if there is another client waiting to do a write.
•    Use multiple-row INSERT statements to store many rows with one SQL statement (many SQL servers support this).
•    Use LOAD DATA INFILE to load large amounts of data. This is faster than using INSERT statements.
•    Use AUTO_INCREMENT columns to generate unique values.
•    Use in-memory tables for non-critical data that is accessed often, such as information about the last displayed banner for users who don't have cookies enabled in their Web browser.
•    Columns with identical information in different tables should be declared to have identical data types.
•    Try to keep column names simple.
•    Always check whether all your queries really use the indexes you have created in the tables. In MySQL, you can do this with the EXPLAIN statement.
•    Try to avoid complex SELECT queries on MyISAM tables that are updated frequently to avoid problems with table locking that occurs due to contention between readers and writers.

This is just a small piece of the MySQL design and optimization process; there are many more considerations to take into account.  These tips will assist with some of the first steps to consider when designing a database for maximum performance.

If you need more information, contact Gary Codeluppi at 937-431-1026 x123 or visit the Ross Group Inc web site at www.rossgroupinc.com.