Databases Reference
In-Depth Information
query_cache_size
MySQL allocates and initializes the specified amount of memory for the query
cache all at once when the server starts. If you update this variable (even if you set
it to its current value), MySQL immediately deletes all cached queries, resizes the
cache to the specified size, and reinitializes the cache's memory. This can take a
long time and stalls the server until it completes, because MySQL deletes all of the
cached queries one by one, not instantaneously.
read_buffer_size
MySQL doesn't allocate any memory for this buffer until a query needs it, but then
it immediately allocates the entire chunk of memory specified here.
read_rnd_buffer_size
MySQL doesn't allocate any memory for this buffer until a query needs it, and then
it allocates only as much memory as needed. (The name
max_read_rnd
_buffer_size
would describe this variable more accurately.)
sort_buffer_size
MySQL doesn't allocate any memory for this buffer until a query needs to do a
sort. However, when there's a sort, MySQL allocates the entire chunk of memory
immediately, whether the full size is required or not.
We explain what these variables do in more detail elsewhere, and this isn't an exhaus-
tive list. Our goal here is simply to show you what behavior to expect when you change
a few common variables.
You should
not
raise the value of a per-connection setting globally unless you know it's
the right thing to do. Some buffers are allocated all at once, even if they're not needed,
so a large global setting can be a huge waste. Instead, you can raise the value when a
query needs it.
The most common example of a variable that you should probably keep small and raise
only for certain queries is
sort_buffer_size
, which controls how large the sort buffer
should be for filesorts. MySQL performs some work to initialize the sort buffer after
allocating it.
In addition, the sort buffer is allocated to its full size even for very small sorts, so if you
make it much larger than the average sort requires, you'll be wasting memory and
adding allocation cost. This can be surprising to those readers who think of memory
allocation as an inexpensive operation. Without digging into all of the technical details,
it's enough to say that memory allocation includes setting up the address space, which
can be relatively expensive; in Linux in particular, memory allocation uses a couple of
strategies with varying cost depending on the size.
In summary, a large sort buffer can be very expensive, so don't increase its size unless
you know it's needed.
