I have not caused a fist fight in a while, so it's time to take off the gloves. I claim that somewhere around of 99% of advice about tuning MySQL's key cache hit ratio is wrong, even when you hear it from experts. There are two major problems with the key buffer hit ratio, and a host of smaller ones. If you make some assumptions that are very hard to prove, there actually is a very limited use for the statistics from which the ratio is derived (but not the ratio itself, which you should ignore). Read on for the details.

In this article, I will use key buffer, key_buffer, and key cache interchangeably. However, I will be careful about the difference between "rate" and "ratio". In this article, the key cache miss rate is defined as the number of misses per unit of time, with the units of operations per second. The key cache miss ratio is the ratio between reads from the disk and reads from the cache; it is a dimensionless number because the units are the same in the numerator and denominator, and they cancel out. The key cache hit ratio is simply one minus the miss ratio.

The key_buffer hit ratio

The metrics we're interested in are all defined in terms of counters you can get from SHOW GLOBAL STATUS. I'll start out by copying and pasting from the MySQL manual:

• Key_read_requests

  The number of requests to read a key block from the cache.

• Key_reads

  The number of physical reads of a key block from disk.

So far, so good. All of the above is mostly factual (more on this later). Here's another fact from the manual:

The cache miss rate can be calculated as Key_reads/Key_read_requests. [Note: their use of 'rate' doesn't match mine; I would call this the 'miss ratio'].

The problem occurs when you start to assign any importance to this ratio. The MySQL manual doesn't fall into this trap, but if you search Google for Key_read_requests, you will find lots of advice on "tuning by ratio," including phrases such as "The ratio of key_reads to key_read_requests should be as low as possible, no more than 1:100" or "your key cache hit ratio should be very high, ideally above 99.9%" or "if your key cache hit ratio is low, your key_buffer_size is too small."

So here's a summary of two pieces of bad advice:

• Bad advice #1: you should care about the key cache hit ratio.
• Bad advice #2: you should set your key_buffer_size according to this ratio.

Tuning by ratio is one of those things that is widely accepted because of "proof by repeated assertion," but as you know, that doesn't make it valid. Let's see why the above two things are bad advice.

Problem 1: Ratios don't show magnitude

Ratios of counters are virtually meaningless for two major reasons. The first is that ratios obscure magnitude. Look at the following and tell me what you think:

• Server #1 has a key cache miss ratio of 23%
• Server #2 has a key cache miss ratio of 0.001%

Which server is badly tuned? Maybe you're objecting that you don't know enough about the servers. OK, I'll give you more information. Both servers are real production servers, with powerful hardware and heavy query workloads. Both have a key_buffer_size of 4GB. Now can you tell which server is badly tuned? -- No, you can't tell anything meaningful based on a ratio of counters, because the process of dividing one counter by the other to get the ratio has discarded vital information. You don't know how many Key_reads and Key_read_requests those servers have done.

Just for fun, consider these fake but entirely possible scenarios: server #1 has 23 Key_reads and 100 Key_read_requests (23% miss ratio). Server #2 has one trillion Key_reads, and one hundred quatrillion Key_read_requests (1/100th of a percent). Given that information, which server is badly tuned? If you said "I still can't tell," maybe you want more information, so I'll tell you that both servers are identically tuned, and they have identical data, hardware, and workload. Even that doesn't help, though.

Problem 2: Counters don't measure time

The reason you still can't tell which server is badly tuned is because, even if you know the absolute numbers, you are missing the element of time, in two important ways. First, you don't know how long of an interval I used to measure the statistics on those two servers. Maybe I measured the first server immediately after starting it, and that's why its counters are so small. The second server has been online practically forever, and that's why its counters are big. Let's say this is the case. Now, you've got all the information you need to form an opinion, right? Instead of asking the same annoying question, let me ask it a different way: is either of these servers badly tuned?

There's still not enough information -- I hope you're beginning to appreciate that tuning by ratio is a waste of time! The Oracle folks arrived at this conclusion a long time before the MySQL world started to come around. There are even tuning utilities (anti-tuning anti-utilities?) for Oracle, specifically designed to mock and frustrate those who would tune by ratio. They are capable of creating any buffer hit ratio the user desires by running silly queries that do nothing but cause buffer hits, skewing the result towards "this ratio looks great!"

The second kind of time information you're lacking is how much time each buffer hit or miss takes. If you approach application performance optimization from the standpoint of response time measurements, which you should, you will eventually arrive at this question. "I have a query I know is slow and is a problem for my application. I have profiled it with SHOW STATUS and I know it causes a great many Key_reads to occur. How much of this query's execution time is consumed by those operations? Should I try to reduce Key_reads?"

There is no way to know. All you get is counters -- you don't get the time elapsed. In technical terms, counters are surrogate measures. They are not helpful. And as Cary Millsap says, the unfortunate problem is that surrogate measures work sometimes, simply because there is sometimes a correlation (but not a cause) relationship between the counter events and the query's execution time. Alas, that correlation fools us into thinking it's a cause, and we optimize-by-surrogate-measure a time or two and it appears to work -- so we turn into little Pavlovian DBAs and try to do that every time. It would be better if optimizing-by-counter never worked!

A partially valid use of Key_reads

There is a partially valid reason to examine Key_reads, assuming that we care about the number of physical reads that occur, because we know that disks are very slow relative to other parts of the computer. And here's where I return to what I called "mostly factual" above, because Key_reads actually aren't physical disk reads at all. If the requested block of data isn't in the operating system's cache, then a Key_read is a disk read -- but if it is cached, then it's just a system call. However, let's make our first hard-to-prove assumption:

• Hard-to-prove assumption #1: A Key_read might correspond to a physical disk read, maybe.

If we take that assumption as true, then what other reason might we have for caring about Key_reads? This assumption leads to "a cache miss is significantly slower than a cache hit," which makes sense. If it were just as fast to do a Key_read as a Key_read_request, what use would the key buffer be anyway? Let's trust MyISAM's creators on this one, because they designed a cache hit to be faster than a miss.

• Hard-to-prove assumption #2: A key cache miss is probably slower than a hit, maybe.

What else? Maybe this physical I/O operation is randomly positioned, which is a worst-case scenario for spinning disks. This is also very hard to prove, but seems reasonable based on the structure of a B-tree index, so let's assume anyway:

• Hard-to-prove assumption #3: A Key_read might cause a random I/O operation, maybe.

Now, given those assumptions, we can further assume the following:

• It is good to minimize Key_reads because they are slow and cause random disk I/O.

Notice that we still don't know anything about any relationship between Key_reads and the execution time of our query. All we can do is guess, like good Pavlovian DBAs, that there is a relationship. However, we can again reason that random I/O can cause collateral damage: if the disk head is seeking all over for random I/O, then other I/O (including non-random I/O) is likely to be impacted. If we manage to reduce Key_reads, we might make the database server faster overall, and perhaps the query of interest will accidentally get faster too, and we'll get a treat.

There is one interesting question that we haven't really addressed yet. How bad is bad? This is where we return to the notion of the key cache miss rate in units of operations per second. Given our assumed correlation between a Key_read and a random physical disk I/O, it is partially valid to say that we are going to get in trouble when Key_reads gets close to the number of random I/Os our disk can do. Here's another formula for you:

Key_cache_miss_rate = Key_reads / Uptime

Note the conspicuous absence of Key_read_requests in the formula. The number of requests is absolutely irrelevant -- who cares how often the key is requested? What's relevant is that our assumed connection between Key_reads and random I/Os means that Key_reads/Uptime is assumed to be the same as "random I/Os per second."

And now, I would finally like to show you something partially useful you can do with Key_reads:

PLAIN TEXT CODE:

1. [baron@localhost ~]$ mysqladmin ext -ri10 | grep Key_reads
2. | Key_reads                         | 6030962       |
3. | Key_reads                         | 98            |
4. | Key_reads                         | 89            |
5. | Key_reads                         | 104           |

This server is doing approximately 100 Key_reads every ten seconds, so we can assume Key_reads are causing about ten random I/Os per second. Compare that to what your disks are capable of, and draw your own conclusions about whether this is a performance problem. I know what I'd like: I'd like to ask the disk itself how much random I/O it's doing. But alas, that's virtually impossible on most systems I work on. So there you have it -- yet another surrogate measure.

How to choose a key_buffer_size

Let's recap. So far I've shown you the fallacy of tuning by ratio, and told you to ignore the ratio and in fact, ignore Key_read_requests altogether. I've explained that counters are a surrogate measure, but the fact that they're easy to get and sometimes correlated with the true problem causes people to mistake counter analysis for a true performance optimization method. I've shown that if we make some assumptions that are hard to prove, we can compare Key_reads to the disk's physical capacity for random I/O and get an idea of whether index I/O might be causing a performance problem.

But I haven't shown you how to choose an appropriate key_buffer_size. Let's look at that now.

This topic deserves an entire blog post, because there are many subtleties including the possibility of having multiple key caches. But I'll give the simple version here. In my opinion, you should choose a key_buffer_size that is large enough to hold your working set -- the index blocks that are frequently used. How large is that? This is yet another thing that's really hard to measure, alas! So we need to either pick a surrogate, or pull a number out of thin air. Here are some suggestions that are about as good as any:

1. Just set it really big and forget it. If you have enough memory, who cares. The memory isn't allocated until it's used -- if you set it to 4GB, that doesn't mean that 4GB is actually used. This is not as much of an abdication of responsibility as it might sound like on a machine that's dedicated to MyISAM tables.
2. Consider your mixture of storage engines (some InnoDB, some MyISAM, which is more important to you, etc) and choose an amount of memory based on how important those tables are to you, how big the indexes are on disk, and so on.
3. Raise the key_buffer_size until, when the buffer is full, Key_reads/Uptime reduces to a number you're comfortable with.
4. Set key_buffer_size really big, and then measure its size as it fills up, in something like 1 minute intervals. Pull this into a spreadsheet and graph it as a curve. When the curve's growth tapers off, pick that point on the curve and use it as a heuristic for how big your working set is. Set the key_buffer_size to that.

If the above methods shock you with their unscientific-ness, they shouldn't. The reality is that this server setting is very subjective, and there is no good instrumentation in MySQL to guide your decisions. It is also not the be-all and end-all of MySQL performance, and people frequently obsess over it far out of proportion. But again, 99% of the advice I've seen is based on something much worse: a red herring that only sounds scientific and authoritative -- the "key cache hit ratio." This is a shame. When you are new to MySQL, trying to configure my.cnf, and you have heard guidance that seems so definite, mathematical, and authoritative, but still makes no sense, why wouldn't you obsess over it?

What about InnoDB tuning?

You might be wondering, what about InnoDB tuning? What is the best way to choose an innodb_buffer_pool_size setting? This is a topic that deserves its own article too, but the short version is: ratio-based tuning is just as wrong for InnoDB as it is for MyISAM. Ratio-based tuning is invalid and wrong in general, not just for specific things. All of the above points (loss of magnitude, lack of timing information, etc) apply to all types of ratio-based and counter-based tuning techniques.

Summary

Major points in this article:

• Counter ratios are meaningless as a performance analysis metric because they are a) ratios of b) counters.
  • You need to look at absolute magnitude, not ratios.
  • For performance analysis, you need to measure elapsed time, not just the number of times something happens.
• In the absence of timing information, and if you trust the cache's creator and assume that misses are more expensive than hits, then you care about cache misses, not cache hits.
• A Key_read is not guaranteed to be a random physical I/O, but it might be. Gather Key_reads/Uptime over 10-second or 60-second intervals and compare the results to your IO system's capabilities.
• MySQL doesn't have good instrumentation for scientifically choosing a key_buffer_size setting, but there are many unscientific approaches that are better than ratio-based tuning.
• Counter ratios suck for everything, not just for MyISAM tuning.

I've had some heated arguments over these points, so I don't expect the above to pass without controversy. But really, it's time to stop with the bad advice about counter ratios. The sooner we do that, the sooner we can move on to better things.

Entry posted by Baron Schwartz | 24 comments

Add to: | | | |