High Performance MySQL
Chapter 6: Server Performance Tuning

The operating system your MySQL server runs on and the server's configuration can be just as important to your server's performance as the indexes, schema, or queries themselves. In this chapter, we will help you understand how to tune your server to improve performance, as opposed to tuning schema or queries. We'll be looking at changes to your hardware, operating system, and MySQL configuration to see what effects they have on overall performance.

We assume that you've already made efforts to boost the performance of your queries. If you haven't done that already, stop now and read Chapter 4 and Chapter 5 to get a handle on optimizing your queries and your application code. Only then should you worry about server settings. Hardware is often not the solution to MySQL performance problems. Poorly optimized queries can slow you down far more than not having the latest CPU or SCSI disk. To put this in perspective, one of the MySQL AB trainers even says that changing hardware might, in the best cases, give you a 10-fold performance increase. But tuning queries (and schemas) can often give you 1000-fold performance increase. Seriously.

Some topics covered in this chapter are platform-specific. The authors' knowledge of the various platforms on which MySQL runs is limited. In many cases, you'll need to consult your local documentation for various operating system tools and specifics.

We start with an overview of the factors that limit performance and then look more in depth at RAID, hardware, and operating system issues. The chapter finishes with a discussion of techniques you can use to locate, identify, and fix bottlenecks.

Disks

The fundamental battle in a database server is usually between the CPU(s) and available disk I/O performance; we'll discuss memory momentarily. The CPU in an average server is orders of magnitude faster than the hard disks. If you can't get data to the CPU fast enough, it must sit idle while the disks locate the data and transfer it to main memory.

The real problem is that a lot of the disk access is random rather than sequential: read 2 blocks from here, 10 from there, 4 from there, and so on. This means that even though your shiny new SCSI disks are rated at 80 MB/sec throughput, you'll rarely see values that high. Most of the time you'll be waiting for the disks to locate the data. The speed at which the heads move across the platter and fetch another piece of data is known as seek time, and it's often the governing factor in real-world disk performance.

The seek time consists of two factors. First is the amount of time required to move the head from one location to the next. When the head arrives at the new location, it often needs to wait for the disk platter to rotate a bit more so that it can read the desired piece of information. The disk's rotation speed, measured in RPMs, is the second factor. Generally speaking, the faster the platters rotate, the lower the disk's seek time will be. When you're shopping for your database server's disks, it's usually better to spend the extra cash for the 15,000-RPM model rather than saving a bit with the cheaper 10,000-RPM model. As a bonus, higher RPM drives provide greater transfer rates because they're reading data from a faster moving platter.

This all means that the first bottleneck you're likely to encounter is disk I/O. The disks are clearly the slowest part of the system. Like the CPU's caches, MySQL's various buffers and caches use main memory as a cache for data that's sitting on disk. If your MySQL server has sufficient disk I/O capacity, and MySQL has been configured to use the available memory efficiently, you can better use the CPU's power.

A common complaint against MySQL is that it can't handle really large tables. Assuming the people making that statement have even used MySQL, they likely encountered an I/O bottleneck they didn't know how to fix. MySQL worked great with a few hundred megabytes of data, but once loaded up with 60 GB, it became slow. The conclusion drawn was that MySQL was somehow inadequate.

Of course, there are some circumstances in which MySQL can become CPU-bound rather than I/O-bound: they're simply not as common. If you often ask MySQL to perform some computation on your data (math, string comparison, etc.), the CPU will work harder. When running a CHECKTABLE command, you'll likely find the CPU pegged. And, of course, queries that aren't using indexes really tax it as well.

Memory

To bridge the gap between blazingly fast CPUs and comparatively slow disks, we have memory. With respect to performance, it's in the middle—significantly faster than disks but still much slower than the CPU. The underlying operating system generally uses free memory to cache data read from and written to disk. That means if you frequently query the same small MyISAM table over and over, there's a very good chance you'll never touch the disk. Even though MySQL doesn't cache row data for MyISAM tables (only the index blocks), the entire MyISAM table is likely in the operating system's disk cache.

Modern CPUs are even substantially faster than main memory. To combat this mismatch, chip makers have designed multilevel caching systems. It's common for a CPU to contain level 1, level 2, and even level 3 caches. The caches use significantly faster and more expensive memory, so they're generally a fraction of the size of main memory; a 512-KB L2 cache is generous.

With that in mind, simply adding memory to your server will improve MySQL performance only if the operating system can make good use of it by caching even more disk blocks. If your database is 512 MB, and you already have 1 GB of memory, adding more memory probably won't help.

On the other hand, if you run more than just MySQL on the server, adding memory may help. Maybe that Java application server you've been running is eating up a lot of the memory that could otherwise cache disk access. Keep in mind that Linux, like most modern operating systems, considers caching disk I/O an optional feature. It doesn't reserve any memory for it. So when free memory is low, MySQL can really suffer because MyISAM tables expect the OS to do some read caching.

MySQL's buffers and caches

By adjusting how much memory MySQL uses, you can often realize significant performance improvements. To do that effectively, you first need to understand how MySQL uses memory. Most of the memory MySQL allocates is used for various internal buffers and caches. These buffers fall into two major groups: global buffers and per-connection buffers. As their name implies, global buffers are shared among all the connections (or threads) in MySQL.

The two most important global buffers are the MyISAM key buffer (key_buffer_size) and InnoDB's buffer pool (innodb_buffer_pool_size). The MyISAM key buffer is where MySQL caches frequently used blocks of index data for MyISAM tables. The less often MySQL needs to hit the disk to scan a table's index, the faster queries will be. If possible, consider making the key buffer large enough to hold the indexes for your most actively used tables—if not all your tables. By adding up the size of the .MYI files for the tables, you'll have a good idea how large to set the buffer.

MySQL doesn't cache rows for MyISAM tables—only indexes. InnoDB, on the other hand, caches index and row data together in its buffer pool. As you'll recall from Chapter 4, InnoDB uses clustered indexes. Because it stores the index and row data together, it's only natural to cache the index and row data in memory when possible.

Network

The performance of your network usually doesn't have much bearing on MySQL. In most deployments, clients are very near the servers—often connected to the same switch—so latency is low, and available bandwidth is quite high. But there are less common circumstances in which the network can get in the way.

Duplex mismatch is a common network configuration problem that often goes unnoticed until load begins to increase. When it does, by all appearances MySQL is sending results very slowly to clients. But when you check the server, you find the CPU is nearly idle, and the disks aren't working very hard either. For whatever reason, there's a lot of 100-Mbit Ethernet equipment that has trouble auto-sensing the proper settings. Be sure your server and switch agree on either half or full duplex operation.

Some MySQL deployments use Network Attached Storage (NAS) devices, such as a Network Appliance filer, rather than local disks for MySQL's data. The idea is that if the server dies, you can simply swap in a new one without having to worry about copying data or dealing with synchronization issues. (See Chapter 8 for more on this topic.) While that's all true, in dealing with a configuration it's critical that your network be as uncongested as possible. Ideally, you'll want to have a fast dedicated network path between your MySQL server and the storage server. Typically that means installing a second Network Interface Card (NIC) that is connected to a private network with your storage server.

In a replication setup consisting of a single master and many slaves, it's quite possible to saturate a single network interface on the master with all the traffic generated by the slaves. This isn't because of something MySQL does horribly wrong. It's really just a matter of scale. Imagine that you have 50 slaves replicating from the master. Under normal circumstances, each slave uses a relatively small amount of bandwidth—say 100 KB/sec. That adds up to 5 Mbit/sec of bandwidth required for 50 slaves. If you're using 100-Mbit Ethernet, that's not a big deal. But what if your master begins getting more inserts per second, or large inserts that contain BLOB fields? You may reach the point that each slave needs 800 KB/sec of bandwidth to keep up with the master's data stream. At that point, you're looking at 40 Mbit/sec of data on your 100-MBit network.

At that point you should begin to worry. One hundred Mbit/sec is the network's theoretical maximum bandwidth. In reality its capacity is quite a bit less that. Many network engineers use 50% utilization as a rule of thumb for capacity planning. Once they consistently see utilization that high, they begin thinking about how to break up the network to better isolate the traffic. The trouble is, that doesn't help much in this case. Because there's a single master, all slaves must read from it.

There are three possible solutions to this problem. First, you can take a load off the master by introducing a second tier of slaves that replicate from the master. They, in turn, serve as masters for the 50 slaves. See Chapter 7 for more information about multitiered replication architectures.

Another option is to add a second network card to the master and split the 50 slaves across multiple switches. Each of the master's NICs are connected to a different switch. The problem is that you'd need to remember which server is on which switch port and adjust the slave configuration appropriately.

A final solution is to compress the data stream between the master and slaves. This assumes that the data isn't already compressed and that the master has sufficient CPU power to handle compressing 50 outbound data streams while handling a high rate of inserts. Given the rate at which CPUs are evolving, this will soon be feasible. Chapter 7 discusses options for encrypting and compressing replication.

Performance can become an issue when your network links have relatively high latency. This is typically a problem when the client and server are separated by a great distance or by an inherently high-latency link, such as dial-up or satellite. Your goal should be to keep the clients and servers as close (in network sense) to each other as possible. If you can't do this, consider setting up slaves that are close to your most distant clients.

At first glance, this may not seem like a server-performance issue, but a high-latency or low-bandwidth network can really slow things down on the server side. When a client performs a large SELECT on a MyISAM table, it obtains a read lock on the data. Until the SELECT completes, the server won't release the lock and service any pending write requests for the table. If the client asking for the data happens to be far away or on a flaky or congested network, it will take a long time to retrieve the data and release the lock. The end result is that things get backed up on the server side even though the server has sufficient CPU and disk I/O to do the work.

RAID 0

Of all the RAID types, RAID 0, or striping, offers the biggest performance improvement. Writes and reads are both faster in RAID 0 than in any other configuration. Because there are no spare or mirrored disks, it's inexpensive. You're using every disk you pay for. But the performance comes at a high price. There's no redundancy at all. Losing a single disk means that your whole array is dead.

RAID 0 should be used only when you don't care about data loss. For example, if you're building a cluster of MySQL slaves, it's entirely reasonable to use RAID 0. You'll reap all the performance benefits, and if a server does die, you can always clone the data from one of the other slaves.

RAID 1

Moving up the scale, RAID 1, or mirroring, isn't as fast as RAID 0, but it provides redundancy; you can lose a disk and keep on running. The performance boost applies only to reads. Since all the data is on every disk in the mirrored volume, the system may decide to read data in parallel from the disks. The result is that in the optimal case it can read the same amount of data in roughly half the time.

Write performance, however is only as good as a single disk. It can even be half as good depending on whether the RAID controller performs the writes in parallel or sequential order. Also, from a price point of view, you're paying for twice as much space as you're using. RAID 1 is a good choice when you need redundancy but have space or budget for only two disks—such as in a 1-U rackmount case.

RAID 5

From a performance standpoint, RAID 5, which is striping (RAID 0) with distributed parity blocks, can be beneficial. There are two disks involved in every operation, so it's not substantially faster than RAID 1 until you have more than three disks total. Even then, its other benefit, size, shines through. Using RAID 5, you can create rather large volumes without spending a lot of cash because you sacrifice only a single disk. By using more smaller disks, such as eight 36-GB disks instead of four 72-GB disks, you increase the number of spindles in the array and therefore boost seek performance and throughput.

RAID 5 is the most commonly used RAID implementation. When funds are tight, and redundancy is clearly more important than performance, it's the best compromise available.

RAID 10 (also known as RAID 1+0)

To get the best of both worlds (the performance benefits of RAID 0 along with the redundancy of RAID 1), you need to buy twice as many disks. RAID 10 is the only way to get the highest performance on your database server without sacrificing redundancy. If you have the budget to justify it, you won't be disappointed.

JBOD

The configuration sometimes called "Just a Bunch of Disks" (JBOD) provides no added performance or redundancy. It's simply a combination of two or more smaller disks to produce a single, larger virtual disk.

Table 6-1 summarizes various RAID features.

Table 6-1. Summary of various RAID features

Level	Redundancy	Disks required	Faster reads	Faster writes
RAID 0	No	N	Yes	Yes
RAID 1	Yes	2[3]	Yes	No
RAID 5	Yes	N+1	Yes	No
RAID 10	Yes	N*2	Yes	Yes
JBOD	No	N/A	No	No

[3] Typically, RAID 1 is used with two disks. but it's possible to use more than two. Doing so will boost read performance but doesn't change write performance.

Mix and Match

When deciding how to configure your disks, consider the possibility of multiple RAID arrays. RAID controllers aren't that expensive, so you might benefit from using RAID 5 or RAID 10 for your databases and a separate RAID 1 array for your transaction and replication logs. Some multichannel controllers can manage multiple arrays, and some can even bind several channel controllers together into a single controller to support more disks.

Doing this isolates most of the serial disk I/O from most of the random, seek-intensive I/O. This is because transaction and replication logs are usually large files that are read from and written to in a serial manner, usually by a small number of threads. So it's not necessary to have a lot of spindles available to spread the seeks across. What's important is having sufficient bandwidth, and virtually any modern pair of disks can fill that role nicely. Meanwhile, the actual data and indexes are being read from and written to by many threads simultaneously in a fairly random manner. Having the extra spindles associated with RAID 10 will boost performance. Or, if you simply have too much data to fit on a single disk, RAID 5's ability to create large volumes works to your advantage.

Sample configuration

To make this more concrete, let's see what such a setup might look like with both InnoDB and MyISAM tables. It's entirely possible to move most of the files around and leave symlinks in the original locations (at least on Unix-based systems), but that can be a bit messy, and it's too easy to accidentally remove a symlink (or accidentally back up symlinks instead of actual data!). Instead, you can adjust the my.cnf file to put files where they belong.

Let's assume you have a RAID 1 volume on which the following filesystems are mounted: /, /usr, and swap. You also have a RAID 5 (or RAID 10) filesystem mounted as /data. On this particular server, MySQL was installed from a binary tarball into /usr/local/mysql, making /usr/local/mysql/data the default data directory.

The goal is to keep the InnoDB logs and replication logs on the RAID-1 volume, while moving everything else to /data. These my.cnf entries can accomplish that:

datadir = /data/myisam
log-bin = /usr/local/mysql/data/repl/bin-log
innodb_data_file_path = ibdata1:16386M;ibdata2:16385M
innodb_data_home_dir = /data/ibdata
innodb_log_group_home_dir = /usr/local/mysql/data/iblog
innodb_log_arch_dir = /usr/local/mysql/data/iblog

These entries provide two top-level directories in /data for MySQL's data files: ibdata for the InnoDB data and myisam for the MyISAM files. All the logs remain in or below /usr/local/mysql/data on the RAID 1 volume.

Hardware Versus Software

Some operating systems can perform software RAID. Rather than buying a dedicated RAID controller, the operating system's kernel splits the I/O among multiple disks. Many users shy away from using these features because they've long been considered slow or buggy.

In reality, software RAID is quite stable and performs rather well. The performance differences between hardware and software RAID tend not to be significant until they're under quite a bit of load. For smaller and medium-sized workloads, there's little discernible difference between them. Yes, the server's CPU must do a bit more work when using software RAID, but modern CPUs are so fast that the RAID operations consume a small fraction of the available CPU time. And, as we stressed earlier, the CPU is usually not the bottleneck in a database server anyway.

Even with software RAID, you can use multiple disk controllers to achieve redundancy at the hardware level without actually paying for a RAID controller. In fact, some would argue that having two non-RAID controllers is better than a single RAID controller. You'll have twice the available I/O bandwidth and have eliminated a single point of failure if you use RAID 1 or 10 across them.

Having said that, there is one thing that can be done with hardware RAID that simply can't be done in software: write caching. Many RAID controllers can add battery-backed RAM that caches reads and writes. Since there's a battery on the card, you don't need to worry about lost writes even when the power fails. If it does, the data stays in memory on the controller until the machine is powered back up. Most hardware RAID controllers can also read cache as well.

IDE or SCSI?

It's a perpetual question: do you use IDE or SCSI disks for your server? A few years ago, the answer was easy: SCSI. But the issue is further muddied by the availability of faster IDE bus speeds and IDE RAID controllers from 3Ware and other vendors. For our purposes, Serial-ATA is the same as IDE.

The traditional view is that SCSI is better than IDE in servers. While many people dismiss this argument, there's real merit to it when dealing with database servers. IDE disks handle requests in a sequential manner. If the CPU asks the disk to read four blocks from an inside track, followed by eight blocks from an outside track, then two more blocks from an inside track, the disk will do exactly what it's told; even if it's not the most efficient way to read all that data. SCSI disks have a feature known as Tagged Command Queuing (TCQ). TCQ allows the CPU to send several read/write requests to the disk at the same time. The disk controller then tries to find the optimal read/write pattern to minimize seeks.

IDE also suffers from scaling problems; you can't use more than one drive per IDE channel without suffering a severe performance hit. Because most motherboards offer only four IDE channels at most, you're stuck with only four disks unless you add an additional controller. Worse yet, IDE has rather restrictive cable limits. With SCSI, you can typically add 7 or 14 disks before purchasing a new controller. Furthermore, the constant downward price pressure on hard disks has affected SCSI as much as IDE.

On the other hand, SCSI disks still cost more than their IDE counterparts. When you're considering four or more disks, the price difference is significant enough that you might be able to purchase IDE disks and be able to afford another controller, possibly even an IDE RAID controller. Many MySQL users are quite happy using 3Ware IDE RAID controllers with 4-12 disks on them. It costs less than a SCSI option, and the performance is reasonably close to that of a high-end SCSI RAID controller.

RAID on Slaves

As we mentioned in the discussion of RAID 0, if you're using replication to create a cluster of slaves for your application, it's likely that you can save money on the slaves by using a different form of RAID. That means using a higher-performance configuration that doesn't provide redundancy (RAID 0), using fewer disks (RAID 5 instead of RAID 10), or using software rather than hardware RAID, for example. If you have enough slaves, you may not necessarily need the redundancy on the slaves. In the event that one slave suffers the loss of a disk, you can always synchronize it with another nearby slave to get it started again.

Filesystems

The proliferation of freely available filesystems in the Linux world has lead to a regular stream of questions about the best filesystem choice for MySQL on Linux. In a way, it's not all that different from choosing the right storage engine for your tables. You need to consider the benefits and drawbacks of each, as well as your needs. Unlike switching table types, however, you can't change filesystems on the fly. And without creating a bit of a maintenance nightmare, you can't easily use one filesystem for some tables and another for the remaining ones.

It's worth pointing out that filesystem performance is a relatively minor issue most of the time. If switching filesystems gives you your largest performance gains, you've done so many other things right, you deserve a reward.

This section is admittedly Linux-centric. That's primarily because Linux is the operating system with the widest variety of choices, and it's also because Linux happens to be what the authors are most experienced with.

Journaling

The biggest difference among the filesystems is journaling. Journaling filesystems maintain a log (or journal) that is never cached. The journal is similar in concept to a write-ahead transaction log. Whenever the filesystem is updated, a record describing the transaction is appended to the log. Another idle thread actually processes these transactions by writing the new data to the filesystem and flagging each processed transaction as it is completed.

If the machine crashes, the filesystem performs a roll-forward recovery, much as InnoDB would. Upon reboot, it simply finishes processing updates from the journal. Incomplete transactions in the journal are discarded, so the filesystem's internal consistency is guaranteed. This significantly decreases the complexity of running a filesystem check, meaning much shorter reboot times in the event of a crash. Even though InnoDB provides its own journaling (in the form of a transaction log), using a journaling filesystem with InnoDB is still worthwhile because of the time saved during an unexpected reboot.

Older filesystems such as Linux's ext2 and Windows FAT16/FAT32 provide no journaling. In the event of an unclean shutdown, they need to perform consistency checks upon reboot. On Linux, you must wait for fsck to do the job. On Windows, scandisk is what you end up waiting for. Luckily Microsoft's NTFS does provide journaling and it's the standard filesystem on Microsoft's server operating systems, Windows NT, 2000, and XP. In the Macintosh world, OS X provides a journaling option for its HFS filesystem. Tru64 and AIX also provide their own journaling filesystem implementations.

FreeBSD currently has no journaling filesystems available, but it does offer an alternative to journaling, known as soft updates. Developed by BSD hacker Kirk McKusick, soft updates ensure that metadata changes are written to disk in such an order that the data is always consistent. Doing this eliminates the need for a separate log and most synchronous disk operations while boosting performance through aggregated disk operations. More information is available on Kirk's web site (http://www.mckusick.com/softdep/) and in the FreeBSD manual pages for newfs and tunefs.

Solaris users who need journaling have traditionally purchased a filesystem product from Veritas, but newer versions of Solaris provide a journaling filesystem that eliminates the need for third-party software.

Other features and tweaks

Many of the newer filesystems (those designed in the past 10 years or so) have other important features that affect performance. Their designers realized that disk sizes were steadily increasing, and intensive new applications (high-volume databases, streaming video, etc.) could benefit from rethinking filesystem design. As a result, we have a good selection of high performance filesystems to choose from today. See Section 6.3.1.3 for more details.

The two most notable enhancements in these newer filesystems are support for large directories and better management of fragmentation and free space. Large directory support means that operations on directories that contain thousands of files aren't appreciably slower than operations on smaller directories. This becomes an issue for MySQL only when you have a MySQL database that contains a large number of MyISAM tables. Since each table is composed of three files, the number of files can grow quickly.

Free-space management and fragmentation affect systems on which there are lots of MyISAM tables that change frequently (lots of deletes, inserts, and updates). Some filesystems are smarter than others about allocating contiguous blocks of disk space for files. This helps to reduce fragmentation, which means fewer disk seek operations when operating on the tables.

Choosing a filesystem

Choosing a filesystem for MySQL is a matter of considering your needs, the available filesystems, and your comfort level with them. Here we present a brief description of the options on modern Linux systems:

ext2

The ext2 filesystem has been around Linux since the early days. It doesn't offer many advanced features, but it is time-tested and known for being very lightweight and reliable.

ext3

ext3 evolved out of a desire to add journaling support to the ext2 filesystem. You can think of ext3 as simply that—ext2 with journaling added on. Most of ext2's limitations (such as poor performance with large directories) still exist in ext3.

One interesting byproduct of the ext3 implementation is that you can actually switch the ext3 journal on and off using tunefs. With the journal disabled, an ext3 filesystem effectively becomes an ext2 filesystem again.

ReiserFS

ReiserFS, originally created by Hans Reiser, has proven to be quite popular in the Linux world. It was built from the ground up as a modern filesystem. It handles large directories exceptionally well and has a very reliable journaling implementation. As of this writing, ReiserFS Version 3 is in widespread use, and ReiserFS Version 4 is being tested among kernel developers and other adventurous souls.

XFS

Ported by SGI from their IRIX operating system, XFS was designed to handle large filesystems with an emphasis on consistent performance. SGI was interested in creating a filesystem that held up under the type of heavy loads that are generated by high-end streaming media applications.

JFS

Like SGI, JFS came from another large technology company. IBM has been shipping JFS on their AIX platform for many years. Like SGI, IBM focused on performance and reliability when building JFS.

Table 6-2 summarizes the features implemented by various Linux filesystems.

Table 6-2. Linux filesystem features

Filesystem	Journaling	Large directories
ext2	No	No
ext3	Yes (optional)	No (patch available)
ReiserFS	Yes	Yes
XFS	Yes	Yes
JFS	Yes	No

FreeBSD

On FreeBSD, there are really only two filesystem types to choose from: UFS and UFS2. The main difference between them is that UFS2 can handle over 1 TB of data, and it has built-in access control list (ACL) and extended attribute support. Aside from the size differences, none of the differences really affect database users. If you have large directories, the UFS_DIRHASH kernel option may help. It creates in-memory hash tables for large directories, and it doesn't affect the on-disk layout.

Do you need a filesystem at all?

Traditional high-end database servers often don't use a filesystem at all. Instead, the database server bypasses the filesystem interface entirely and communicates directly with the disks. This raw access method puts the burden of managing space, fragmentation, and read/write requests on the database server itself.

The historical rationale for bypassing the filesystem is that early operating systems didn't place much emphasis on filesystem performance. As long as they stored and retrieved data reliably, most people were happy. Another reason is that volume managers didn't really exist, so the operating systems of the day had no good way to combine the server's whopping 10-MB disks into a single, larger disk. When databases routinely exceeded the size of a single disk, vendors had little choice but to implement their own low-level storage.

Nowadays, modern disks are orders of magnitude larger, modern servers provide RAID, and modern operating systems often have volume managers that make adding more space a trivial operation. Despite these advances, many DBAs still use raw partitions rather than filesystems. Users coming from other database systems often ask about MySQL's ability to use raw disks, expecting it to boost performance even more. Not to be outdone, MySQL's InnoDB storage engine can use raw partitions for its tablespaces.

To take advantage of this capability, you must leave InnoDB's home directory unset and specify that the data-file paths point to raw devices:

innodb_data_home_dir=
innodb_data_file_path=/dev/sdb1:18Graw;/dev/sdc1:18Graw

However, you must first initialize the partitions. To do so, use newraw instead of raw the first time and start MySQL. InnoDB will the initialize the partitions. Watch the MySQL log file for completion, shut down MySQL, change newraw to raw, and start MySQL again.

From a performance standpoint, tests have shown a very small (2-5%) performance improvement using raw partitions. When you use raw partitions, you can no longer use any of your favorite command-line tools (ls, du, etc.) to investigate the storage. Furthermore, backups are more complicated when using raw disks. Your choice of backup tools is greatly reduced because most deal with filesystems rather than raw disk partitions.

Swap

In an ideal world, your server would never swap. Swapping is usually an indication that you don't have enough memory or that things are configured improperly—maybe MySQL's key buffer is too large, or you're starting too many unused services at boot time. Maybe it's the operating system itself. Some operating systems make a habit of swapping when there's still free memory available.

Some versions of the 2.4 Linux kernel, for example, are known for being a bit too aggressive with swapping. Linux has generally tried to use all available free memory for caching disk access. From the virtual memory subsystem's point of view, free memory is wasted memory. Early versions (2.4.0-2.4.9) were okay, as are later versions (2.4.18 onward). But the middle versions (2.4.10-2.4.17) were known for being a bit too aggressive. On a dedicated MySQL server, with a key buffer of 1 GB and 2 GB of total RAM, it was not uncommon to see Linux swap out parts of the key buffer while performing a table scan, only to swap it back in moments later. Needless to say, this had a very negative affect on performance. The only solution in such a case is to turn off swap entirely or upgrade to a newer kernel. Luckily, most other operating systems haven't suffered from this problem. Even though most systems are well behaved, some MySQL administrators advocate turning swap off as a preventative measure.

Threading

As a multithreaded server, MySQL is most efficient on an operating system that has a well implemented threading system. Windows and Solaris are excellent in this respect. Linux, as usual, is a bit different. Traditionally, Linux has had a slightly unusual threading implementation—using cloned processes as threads. It performs well under most circumstances, but in situations with thousands of active client connections, it imposes a bit of overhead.

More recent work on the Linux scheduler and alternative threading libraries have improved the situation. The Native POSIX Thread Library (NPTL) is shipped by default in RedHat Linux Version 9.0. Other distributions have just begun adopting it as well.

Another popular free operating system, FreeBSD, has threading problems that are much worse. Versions prior to 5.2 provide rather weak native threading. In some circumstances, I/O-intensive threads are able to get an unfair amount of CPU time, thus keeping other threads from executing as quickly as they should. Given the I/O-intensive nature of some database queries, this has a rather devastating affect on MySQL.

If upgrading isn't an option, build MySQL from the FreeBSD ports collection, and be sure to enable support for LinuxThreads. Doing so causes MySQL to use an alternative threading that's more like that available in Linux 2.4. Each thread is actually a process that, thanks to FreeBSD's rfork( ) call, has shared access to MySQL's global buffers. The overhead of this approach may sound like an issue, but it's really quite efficient. Many of Yahoo's hundreds of MySQL servers are using LinuxThreads on FreeBSD quite effectively.

Section 6.4.4 later in this chapter discusses how MySQL's thread cache can help reduce the overhead associated with creating and destroying threads.

If MySQL is indeed consuming lots of CPU time, there are several techniques you can employ in an attempt to reduce the CPU load. See Section 6.4.2 later in this chapter. If the processes using the bulk of the CPU time aren't mysqld, you clearly have to solve a problem unrelated to MySQL. Perhaps it's a runaway process or simply something that should be moved to another machine. Either way, it's not a MySQL issue, so the problem is "solved" from our point of view.

If the CPU is very busy but there doesn't appear to be any obvious process or group of processes using a large amount of CPU time, look at the division between system and user time. If there's an unusually high amount of time being spent on system (kernel) tasks, that may be a sign of a MySQL configuration problem or something completely unrelated. See Section 6.4.4 later in this chapter for an example of why MySQL might be working the kernel too hard.

If the CPU is relatively idle because it's frequently waiting for the disks, see Section 6.4.1. You'll know this because of the higher than normal numbers you see with vmstat and/or iostat. If the CPU is waiting on disk I/O because of swapping activity, however, go to Section 6.4.3.

Solving I/O Bottlenecks

Disk (I/O) bottlenecks tend to be the most common MySQL performance problem. They're typically caused by inefficient queries—meaning that MySQL has to read too many rows to locate the data you're interested in. Usually that means your queries aren't using an index, or they're using an index that's not terribly effective for this particular query. Before going much further, be sure you've reviewed Chapter 5.

Diagnosing a query that's not using an index is relatively easy. If you've enabled the slow query log (see Section 5.3 in Chapter 5) and set log-long-format, MySQL automatically logs any query that doesn't use an index. You really need to start with that query: use EXPLAIN and do simple benchmarks when you have more than one way to write a given query.

After you've looked at any slow queries and fixed them, the next things to look at are more subtle issues. In some cases, queries do use an index and run relatively fast, so MySQL never considers them to be slow, but it's actually the wrong index from a performance point of view. There may be an alternative index MySQL can use to further decrease the I/O required.

Wrong index

Finding queries that use the wrong index can be more of a challenge. It requires an intimate understanding of your data and the queries being run against it. A real-world example may help to illustrate how subtle the problem can be.

Jeremy uses the mod_log_sql Apache module to record all his web site hits into a MyISAM table named access_jeremy_zawodny_com. The table is roughly 1.3 GB in size, contains over 6 million records, and looks like this:

+------------------+----------------------+------+-----+---------+-------+
| Field            | Type                 | Null | Key | Default | Extra |
+------------------+----------------------+------+-----+---------+-------+
| agent            | varchar(255)         | YES  | MUL | NULL    |       |
| bytes_sent       | int(10) unsigned     | YES  |     | NULL    |       |
| child_pid        | smallint(5) unsigned | YES  |     | NULL    |       |
| cookie           | varchar(255)         | YES  |     | NULL    |       |
| request_file     | varchar(255)         | YES  |     | NULL    |       |
| referer          | varchar(255)         | YES  |     | NULL    |       |
| remote_host      | varchar(50)          | YES  | MUL | NULL    |       |
| remote_logname   | varchar(50)          | YES  |     | NULL    |       |
| remote_user      | varchar(50)          | YES  |     | NULL    |       |
| request_duration | smallint(5) unsigned | YES  |     | NULL    |       |
| request_line     | varchar(255)         | YES  |     | NULL    |       |
| request_method   | varchar(6)           | YES  |     | NULL    |       |
| request_protocol | varchar(10)          | YES  |     | NULL    |       |
| request_time     | varchar(28)          | YES  |     | NULL    |       |
| request_uri      | varchar(255)         | YES  | MUL | NULL    |       |
| server_port      | smallint(5) unsigned | YES  |     | NULL    |       |
| ssl_cipher       | varchar(25)          | YES  |     | NULL    |       |
| ssl_keysize      | smallint(5) unsigned | YES  |     | NULL    |       |
| ssl_maxkeysize   | smallint(5) unsigned | YES  |     | NULL    |       |
| status           | smallint(5) unsigned | YES  |     | NULL    |       |
| time_stamp       | int(10) unsigned     | YES  | MUL | NULL    |       |
| virtual_host     | varchar(50)          | YES  |     | NULL    |       |
+------------------+----------------------+------+-----+---------+-------+

There are separate indexes on four columns: agent, time_stamp, request_uri, and remote_host. The intention is to provide an efficient way to produce statistics based on time, user agent (browser), the document fetched (request_uri), or the client (remote_host). Notice the indexes on each of those columns.

Most queries ran very quickly, but one particular query was problematic. It seemed to run longer than expected. After repeated execution and watching vmstat output, it became clear that a lot of time was spent waiting on the disk. The query attempts to find out which documents a given client has requested during a particular time range—usually a single day. It is run once for every client that requested anything in the past day. The request looks like this:

  select request_uri from access_jeremy_zawodny_com
   where remote_host = '24.69.255.236'
     and time_stamp >= 1056782930
     and time_stamp <= 1056869330
order by time_stamp asc

Running the query through EXPLAIN proved to be quite interesting:

mysql> explain select request_uri from access_jeremy_zawodny_com
    ->    where remote_host = '24.69.255.236'
    ->      and time_stamp >= 1056782930
    ->      and time_stamp <= 1056869330
    -> order by time_stamp asc \G
*************************** 1. row ***************************
        table: access_jeremy_zawodny_com
         type: ref
possible_keys: time_stamp,remote_host
          key: remote_host
      key_len: 6
          ref: const
         rows: 4902
        Extra: Using where; Using filesort
1 row in set (0.00 sec)

MySQL chose to use the index on remote_host. But it doesn't always make that choice. Sometimes it decides to use the index on time_stamp. Here's an example:

mysql> explain  select request_uri from access_jeremy_zawodny_com
    ->    where remote_host = '67.121.154.34'
    ->      and time_stamp >= 1056782930
    ->      and time_stamp <= 1056869330
    -> order by time_stamp asc \G
*************************** 1. row ***************************
        table: access_jeremy_zawodny_com
         type: range
possible_keys: time_stamp,remote_host
          key: time_stamp
      key_len: 5
          ref: NULL
         rows: 20631
        Extra: Using where
1 row in set (0.01 sec)

The only difference between those two queries is the IP address we're looking for. In each case, MySQL's query optimizer estimates the number of rows it will need to read to satisfy the query using each possible index. In the first example, it decides that there are fewer records with a remote_host of 24.69.255.236 than there are records in the specified 24-hour time range. In the second example, it does just the opposite, deciding the time range will result in fewer rows to read.

By experimenting with various IP addresses, it doesn't take long to find one for which MySQL makes the wrong choice. It chooses the remote_host index when using the time_stamp index is actually faster—even though the remote_host requires reading the fewest rows.[4] How is that possible?

The underlying assumption is that all rows cost roughly the same amount of time to read. But this is a case in which that's not always true. Consider how the data will be stored in this MyISAM table. Apache is logging requests to the table all the time and has been doing so for over a year. Rows are never removed, so the data is already sorted by timestamp in the table and on disk (assuming minimal fragmentation).

Once you have a nontrivial amount of information in a table like this, the rules change a bit. If we assume that the records for a given IP address are evenly distributed among the millions of records, it's clear that using the remote_host index may result in many more disk seeks. And since disk seeks are slower than reading consecutive blocks from disk, it follows that MySQL may be doing less work (evaluating fewer rows) but the disk is doing more work—using precious seek time that may slow down other queries too.

In logging applications when you're frequently querying based on a time range as well as another indexed field, this problem is quite common and has no good generalizable solution. If you have some insight into your data and can add it to the software that writes the queries, that can help a lot. The software could be configured to tell MySQL which index to use. For example, if your software knows that a given IP address shows up only very infrequently recently, it can force MySQL to use the time_stamp range:

SELECT ... USE_INDEX(time_stamp) ...

It's not the ideal solution, but it is quite effective when used appropriately.

Temporary tables

Another problem that doesn't show up in the slow query log is an excessive use of disk-based temporary tables. In the output of EXPLAIN, you'll often see Usingtemporary. It indicates that MySQL must create a temporary table to complete the query. However, it doesn't tell you whether that temporary table will be in memory or on disk. That's controlled by the size of the table and MySQL's tmp_table_size variable.

If the space required to build the temporary table is less than or equal to tmp_table_size, MySQL keeps it in memory rather than incur the overhead and time required to write the data to disk and read it again. However, if the space required exceeds tmp_table_size, MySQL creates a disk-based table in its tmpdir directory (often /tmp on Unix systems.) The default tmp_table_size size is 32 MB.

To find out how often that happens, compare the relative sizes of the Created_tmp_tables and Created_tmp_disk_tables counters:

mysql> SHOW STATUS LIKE 'Created_tmp_%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 18    |
| Created_tmp_tables      | 203   |
| Created_tmp_files       | 0     |
+-------------------------+-------+

If you create a lot of disk-based temporary tables, increase the size of tmp_table_size if you can do so safely. Keep in mind that setting the value too high may result in excessive swapping or MySQL running out of memory if too many threads attempt to allocate in-memory temporary tables at the same time. Otherwise, make sure that tmpdir points to a very fast disk that's not already doing lots of I/O.

As a last resort, consider using a tmpfs (or ramdisk, or mdmfs, or whatever your OS calls memory-backed filesystems) and setting $TMPDIR to point there when starting MySQL.

Caching

If your queries are already optimized and using the most efficient indexes, it's still possible to run into I/O bottlenecks at some point. Simply running too many queries, no matter how efficient they are, can become too much for the disk(s) to keep up with. If so, it's time to consider caching.

The easiest thing to do is make sure you're using the MySQL query cache. Available since MySQL 4.0, the query cache keeps the results of frequently executed SELECTs in memory so that MySQL doesn't need to perform any disk I/O at all. See Section 5.4.4 in Chapter 5 for more information.

Taking things a step further, you might consider application-level caching. If there's data that doesn't change frequently at all, query for it once in a while and store it in memory or on local disk until you requery for it.

Spread the load

If you've already covered the causes listed earlier and implemented the suggestions, it's likely that you need to spread the I/O load more effectively. As described earlier, installing disks with faster RPMs and lower seek times may help. Using RAID (especially RAID 0, RAID 5, or RAID 10) will spread the work across multiple disks, possibly eliminating or reducing the bottleneck.

Another option, if you have multiple disks and can't easily configure RAID, is to attempt to balance the disk I/O manually. Spend some time with iostat or systat (depending on your OS) to discover where the bulk of the I/O is going. If you have all your MySQL data on a single disk, you can try moving pieces to another disk. If the majority of activity is focused on a small group of tables, consider moving them to a separate disk.

Another approach is to separate predominantly random I/O from that which is mostly serial. Store logs such as the binary logs, replication relay logs, and InnoDB transaction logs, on a separate disk from the actual data files. It's ultimately a game of trial and error. As with benchmarking, keep a close eye on the numbers and try not to change too many things at once.

Finally, replication is always an option. If you've simply outgrown the capacity of a single machine, it's often the least disruptive solution. See Chapter 7 to learn all about replication.

Solving CPU Bottlenecks

CPU bottlenecks in MySQL can be difficult to track down. Unlike some database servers, MySQL currently doesn't provide per-query statistics about the amount of time spent actually doing work versus waiting for disk I/O to complete.

Luckily it doesn't have to be a complete guessing game. If you see a query in the slow query log and suspect that it may be CPU-bound, simply benchmark it. Pull out a copy of MySQL super-smack, and run it a few thousand times in a row. Then, in another window, watch top, vmstat, or your favorite system monitoring tool. If the CPU quickly hits 100% utilization even with a relatively low number of concurrent queries, the query is very likely CPU-bound.

If you find yourself staring at a very large list of slow queries, how do you decide which ones to start analyzing? Easy: look for those that examine a large number of rows (thousands, tens of thousands, or more), and focus on those that use any of MySQL's built-in data-manipulation functions. Common suspects are those that:

• Format or compare dates

• Encrypt data or compute hashes

• Perform complex comparisons, such as regular expressions

You'll often find that something as simple as computing an MD5 hash over millions of values per hour is using too much CPU time. By moving the logic into the application servers that query that database, you'll free up CPU time for work that only MySQL can do efficiently.

If you can't easily ask MySQL to do less work by moving logic into the application layer, you always have the option of throwing hardware at the problem. You can do this in one of two ways. You might simply upgrade the CPUs in your server or add more CPUs if there's room. Alternatively, you may find it less expensive and more scalable to add new servers, replicate the data to them, and spread the load among them. There's nothing wrong with using Moore's Law to your advantage once in a while.

High CPU utilization with MyISAM tables isn't always bad. It may mean that you are doing queries on tables that have been entirely cached in the operating system's cache. This may or may not be a bad thing. It's certainly better than reading from disk, but each time MySQL has to ask the OS for a block of data, that's CPU time that could be better spent processing the rest of the query. Moving to InnoDB or BDB tables lets MySQL cache table data itself, so it doesn't have to ask the OS for records.

Solving Memory Bottlenecks

Tuning memory usage on MySQL servers can be a delicate balancing act. As explained earlier, MySQL has some global memory buffers in addition to a number of per-thread buffers. The trick is to balance the performance gains that come from having large global buffers against the need to service a particular number of concurrent users. At a minimum, you should have enough memory available to handle MySQL's global buffers plus the per-thread buffers multiplied by the maximum number of concurrent connections you will use.

Expressed mathematically, that is:

min_memory_needed = global_buffers + (thread_buffers * max_connections)

where thread_buffers includes the following:

sort_buffer

myisam_sort_buffer

read_buffer

join_buffer

read_rnd_buffer

and global_buffers includes:

key_buffer

innodb_buffer_pool

innodb_log_buffer

innodb_additional_mem_pool

net_buffer

We say that's the minimum memory required because ideally you'd like some left over for the operating system itself to use. In the case of MyISAM tables, "spare" memory will often be put to use caching records from MyISAM data (.MYD) files.

In addition to any memory the threads may allocate in the process of handling queries, the threads themselves also require a bit of memory simply to exist. The thread_stack variable controls this overhead. On most platforms, 192 KB is the default value.[5]

A likely problem is typified by an all-too-common scenario. Imagine you have a server with 1 GB of memory hosting a mix of MyISAM and InnoDB tables—mostly MyISAM. To get the most bang for your buck, you configure a 512-MB key_buffer after watching the key efficiency in mytop (see Appendix B) and a 256-MB innodb_buffer_pool after checking the buffer pool and memory statistics from SHOW INNODBSTATUS (see Appendix A). That leaves 256 MB that is used to cache data files at the operating system level as well as the per-thread buffers that are allocated on an as-needed basis. The MySQL server handles a relatively small number of concurrent users, maybe 20-50 most of the time, and the per-thread buffer settings are all left at their default sizes.

Things work very well until a few new applications are built that also use this MySQL server. These new applications need a significant number of concurrent connections. Instead of 20-50 connections on average, the server is handling 300-400. When this happens, the odds of several connections needing to allocate a per-thread buffer (such as the sort_buffer) at the same time increase quite a bit.

This can lead to a particularly nasty series of events. If a large number of threads need to allocate additional memory, it's probably because the server is handling a heavy query load. That can cause MySQL to allocate so much memory that the operating system begins swapping, which causes performance to degrade further, which means that each query takes longer to complete. With queries running more slowly, the odds of more threads needing memory increases. It's a vicious spiral.

The only solution is to restore balance between the system's memory and MySQL's memory needs. That means doing one of the following.

• Add more memory

• Decrease max_connections

• Decrease some of the per-thread buffer sizes

Be proactive. Monitor memory use on your servers. Do the math to ensure that in the worst case (hitting max_connections and each thread allocating additional memory), you'll still have a bit of breathing room.

Solving Kernel Bottlenecks

Though it's not common, you may find that MySQL doesn't appear to be using an overwhelming amount of CPU time, yet the machine is rather busy. There's little idle CPU. Upon looking at it more closely, you find that quite a bit of the time is spent in "system" rather than "user" or "idle." That's likely a sign that MySQL is doing something unusual to exercise the kernel—usually creating and destroying threads.

This happened at Yahoo! during the launch of a new web site. In September 2002, engineers were scrambling to create a September 11th memorial web site known as remember.yahoo.com.[6] On it, anyone could create a memorial "tile" by selecting a graphic and adding a customized message. The tile was then viewable by anyone visiting the site. To get the job done as quickly as possible, it was constructed using standard open source tools, including FreeBSD, Apache, PHP, and MySQL

The architecture was relatively straightforward, but we'll simplify it a bit to focus on the main point. A group of frontend web servers was configured to connect to a slave server by way of a hardware load balancer. Using the slave connection, the server could pull the information necessary to display the tiles. When a visitor created a tile, however, the web server needed to connect to the master to insert several records. The master was a beefier machine: dual 1.2-GHz CPUs, 2 GB of RAM, and a SCSI hardware RAID 5 disk array.

At its peak, there were roughly 25-30 web servers that needed to work with the master. Each server was configured to run roughly 30-40 Apache processes. That meant the master would need to support over 1,000 concurrent clients. Knowing that could tie up substantial resources on the master, the designers opted for a simplified approach. Unfortunately, the web application (written in PHP) was configured to use persistent connections. So, to keep connection numbers down on the master, the wait_timeout was set very low—to roughly 10 seconds.

By and large, it worked. Idle connections were dropped after 10 seconds. The number of connections on the master remained below 200, leaving lots of resources free. But there was a problem: the CPUs in the master were quite busy. Most of the time there was less than 10% idle time, and nearly 50% of the CPU time was being spent on system (rather than user) tasks.

After an hour or so of head-scratching, looking at system logs and the output of SHOWSTATUS, a light finally flickered on in Jeremy's head. The value of Threads_created was very large and increasing at an alarming rate. The kernel was so busy creating and destroying threads that it was eating into MySQL's ability to use the CPUs effectively.

With that realization, the solution was easy. Increasing the thread_cache from its default value of 0 to roughly 150 resulted in an instant improvement. The system CPU time dropped to roughly 10%, thus freeing up quite a bit of CPU time for MySQL to use. As it turns out, MySQL didn't need it all, so the machine ended up with 20% idle time—breathing room.

---

Footnotes

[1] The "I" in RAID has meant, at various times, either "Inexpensive" or "Independent." It started out as "Inexpensive," but started being referred to as "Independent" because drives weren't really all that inexpensive. By the time people actually started using "Independent," the price of disks had plummeted and they really were "Inexpensive." Murphy at work.

[2] For a more complete treatment of this topic, consult Derek Vadala's Managing RAID on Linux published by O'Reilly.

[3] Typically, RAID 1 is used with two disks. but it's possible to use more than two. Doing so will boost read performance but doesn't change write performance.

[4] Using a USE INDEX specification in the query, you can test the performance of either index.

[5] If you happen to be using LinuxThreads on FreeBSD, the value is hardcoded in the LinuxThreads library. Changing MySQL's thread_stack setting will have no effect. You must recompile the library to change the stack size.

[6] The entire site was conceived, designed, built, and launched in roughly two weeks using the spare time of handful of Yahoo's engineers.