MySQL Database Design and Tuning

As part of its sophisticated database management capabilities, InnoDB offers numerous internal disk performance-enhancing features. However, there are still several steps that administrators can take to squeeze additional disk speed from their InnoDB instance.

Choosing the Right autoextend Setting

Chapter 12 cited InnoDB's ability to request additional chunks of disk space from the operating system when growth necessitates expansion. The autoextend variable (from my.cnf) or innodb_autoextend_increment setting (available in the 4.0 products in version 4.0.24 and the 4.1 products in version 4.1.5) specifies the size of this supplementary storage. However, its default of 8MB is quite low for many of today's data-intensive applications.

The danger of making this value too low is that your last tablespace file can end up with dozens of 8MB segments of disk storage scattered across the entire drive. Solving this avoidable splintering requires a time-consuming and user-inconveniencing full disk defragmentation. A better choice is to spell out a more realistic value for this setting when creating the tablespace. At worst, stipulating too large a value wastes disk space, which is generally a better risk than facing the discomfort of defragmenting a tablespace.

Using Raw Devices

MySQL allows you to allocate raw disk partitions for use in an InnoDB tablespace. What exactly is a raw disk partition? How does it correspond to a "normal" disk partition? How can you decide between the two?

Unlike a "normal" disk partition, which has been set up in accordance with the rules of the operating system, a raw disk partition is simply a section of disk that has not been formatted to the standards imposed by an operating system. From the viewpoint of the operating system, it is simply an unallocated disk drive, or even just a sector of a drive. This anonymity lets MySQL bypass the standard operating system calls to the file system, which can result in better speed.

From a purely performance perspective, raw partitions are better than standard disk partitions for at least two key reasons:

• Lower overhead Raw partitions let MySQL bypass all of the necessary, yet time- consuming, operating system mechanisms that typically are involved when reading or writing data on a standard partition. This reduced overhead should benefit your applications.

• Contiguous disk space As described in several places in this chapter and entire book, disk fragmentation taxes system response and is to be avoided. However, by definition, a raw partition's disk space is contiguous because there are no other entities or processes to claim a portion of the partition, inserting their own proprietary information into the mix.

Because nothing in life is free, there are costs associated with raw partitions. Paradoxically, the biggest cost is complexity: It simply requires more planning and thought to carve out a chunk of disk space and "hide" it from the operating system than to simply give the entire disk over for it to manage. In addition, a raw partition, if not managed and secured correctly, could be overwritten at any time by another administrator, or even a user with extra permissions. Generally, operating systems do a decent job of blocking such gross data alterations on those partitions that are under operating system control. For raw partitions, that job falls to you, the administrator.

Alternative Disk Writing Methods

Recall from Chapter 12's discussion on InnoDB architecture and optimization that InnoDB leverages a collection of memory-based structures to increase concurrency and performance. Periodically, these structures are synchronized (that is, flushed) to disk, thereby making any data alterations permanent.

Administrators employing Linux or Unix can tune the innodb_flush_method setting to specify the method InnoDB will take when flushing data to disk. Normally, InnoDB uses the fsync() system call to handle this work. However, benchmarks on a variety of Linux and Unix platforms suggest that altering this setting to other values (O_DSYNC or O_DIRECT) can improve InnoDB's interactions with the file system.

Unfortunately, other benchmarks and tests suggest possible issues when moving away from the default fsync() operation, so alter this setting only after careful experimentation and after backing up your information.

Table Defragmentation

In several earlier chapters, this book described the burdens disk fragmentation imposes upon performance. MyISAM offers the myisamchk utility to defragment tables; what choices are available to InnoDB administrators?

Aside from the many operating system based defragmentation tools (which usually affect the entire disk drive or segment), or the OPTIMIZE TABLE statement explored in Chapter 6, "Understanding the MySQL Optimizer," InnoDB administrators can use either the ALTER TABLE statement or data export/import to achieve table-level defragmentation.

For example, suppose that you have a very dynamic table, one that sees abundant data modification each day. Because its usage pattern is so active, you suspect it has become fragmented.

Issuing a simple ALTER TABLE statement:

ALTER TABLE customer_master ENGINE = INNODB;

instructs MySQL to rebuild the table, squeezing out any fragmentation in the process. Be aware that if the table uses a different storage engine, this statement will now switch its storage to InnoDB. You can monitor the progress of this operation via MySQL's traditional management tools, as shown in Figure 13.1.

This job takes quite a while for tables of any significant size. Note that data is still available for queries while the table is being rebuilt, although it's likely that access to the table will be much slower than normal.

Unloading the table via mysqldump and then reloading it is a more involved path to the same result.