Databases Reference
In-Depth Information
# ... run a lot of these ...
mysql>
SET @crash_me_1000000 := REPEAT('a', @@max_allowed_packet);
Run that in a loop, creating new variables each time, and you'll eventually run the server
out of memory and crash it! And it requires no privileges to execute.
The points we've tried to illustrate in this section have sometimes made us unpopular
with people who perceive us as arrogant, think that we're trying to discredit others and
set ourselves up as the sole authority, or feel that we're trying to promote our services.
It is not our intention to be self-serving. We have simply seen so much bad advice that
appears legitimate if you are not experienced enough to know better, and helped clean
up the wreckage so many times, that we think it is important to debunk a few myths
and warn our readers to be careful whose expertise they trust. We'll try to avoid ranting
from here on.
Creating a MySQL Configuration File
As we mentioned at the beginning of this chapter, we don't have a one-size-fits-all “best
configuration file” for, say, a 4-CPU server with 16 GB of memory and 12 hard drives.
You really do need to develop your own configurations, because even a good starting
point will vary widely depending on how you're using the server.
MySQL's compiled-in default settings aren't all great, although most of them are fine.
They are designed not to use a lot of resources, because MySQL is intended to be very
versatile, and it does not assume it is the only thing running on the server on which it
is installed. By default, MySQL uses just enough resources to start and run simple
queries with a little bit of data. You'll certainly need to customize it if you have more
than a few megabytes of data.
You can start with one of the sample configuration files included with the MySQL server
distribution, but they have their own problems. For example, they have a lot of
commented-out settings that might tempt you to think that you should choose values
and uncomment them (it's a bit reminiscent of an Apache configuration file). And they
have a lot of prose comments that explain the options, but these explanations are not
always well-balanced, complete, or even correct. Some of the options don't even apply
to popular operating systems at all! Finally, the samples are perpetually out of date for
modern hardware and workloads.
MySQL experts have had many conversations about how to fix these problems over
the years, but the issues remain. Here's our suggestion: don't use those files as a starting
point, and don't use the samples that ship with your operating system's packages either.
It's better to start from scratch.
That's what we'll do in this chapter. It's actually a weakness that MySQL is so config-
urable, because it makes it seem as though you should spend a lot of time on configu-
ration, when in fact most things are fine at their defaults, and you are often better off
setting and forgetting. That's why we've created a sane minimal sample configuration
