Databases Reference
In-Depth Information
The problem with correlations that sometimes appear to be true is that people begin
to believe they will always be true. Oracle DBAs abandoned ratio-based tuning years
ago, and we wish MySQL DBAs would follow their lead.
2
We wish even more fervently
that people wouldn't write “tuning scripts” that codify these dangerous practices and
teach them to thousands of people. This leads to our second suggestion of what not to
do: don't use tuning scripts! There are several very popular ones that you can find on
the Internet. It's probably best to ignore them.
3
We also suggest that you avoid the word “tuning,” which we've used liberally in the
past few paragraphs. We favor “configuration” or “optimization” instead (as long as
that's what you're actually doing; see
Chapter 3
). The word “tuning” conjures up im-
ages of an undisciplined novice who tweaks the server and sees what happens. We
suggested in the previous section that this practice is best left to those who are re-
searching server internals. “Tuning” your server can be a stunning waste of time.
On a related topic, searching the Internet for configuration advice is not always a great
idea. You can find a lot of bad advice in blogs, forums, and so on.
4
Although many
experts contribute what they know online, it is not always easy to tell who is qualified.
We can't give unbiased recommendations about where to find real experts, of course.
But we can say that the credible, reputable MySQL service providers are a safer bet in
general than what a simple Internet search turns up, because people who have happy
customers are probably doing something right. Even their advice, however, can be
dangerous to apply without testing and understanding, because it might have been
directed at a situation that differed from yours in a way you don't understand.
Finally, don't believe the popular memory consumption formula—yes, the very one
that MySQL itself prints out when it crashes. (We won't repeat it here.) This formula
is from an ancient time. It is not a reliable or even useful way to understand how much
memory MySQL can use in the worst case. You might see some variations on this
formula on the Internet, too. These are similarly flawed, even though they add in more
factors that the original formula doesn't have. The truth is that you can't put an upper
bound on MySQL's memory consumption. It is not a tightly regulated database server
that controls memory allocation. You can prove that very simply by logging into the
server and running a number of queries that consume a lot of memory:
mysql>
SET @crash_me_1 := REPEAT('a', @@max_allowed_packet);
mysql>
SET @crash_me_2 := REPEAT('a', @@max_allowed_packet);
2. If you are not convinced that “tuning by ratio” is bad, please read
Optimizing Oracle Performance
by
Cary Millsap (O'Reilly). He even devotes an appendix to the topic, with a tool that can artificially generate
any cache hit ratio you wish, no matter how badly your system is performing! Of course, it's all for the
purpose of illustrating how useless the ratio is.
3. An exception: we maintain a (good) free online configuration tool at
http://tools.percona.com
. Yes, we're
biased.
4. Q: How is query formed? A: They need to do way instain DBAs who kill thier querys, becuse these querys
cant frigth back?
