|< Day Day Up >|
6.1 Performance-Limiting Factors
Before we can begin to think about what to adjust on a busy MySQL server, it's best to get an understanding of the various factors that affect performance and, most importantly, how they can affect it. One of the single biggest problems that most MySQL users face is simply not understanding how to go about finding bottlenecks.
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 CHECK TABLE command, you'll likely find the CPU pegged. And, of course, queries that aren't using indexes really tax it as well.
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.
126.96.36.199 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.
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.
|< Day Day Up >|