Databases Reference
In-Depth Information
When you find a query that needs a larger sort buffer to perform well, you can raise the
sort_buffer_size
value just before the query and then restore it to
DEFAULT
afterward.
Here's an example of how to do this:
SET @@session.sort_buffer_size :=
<value>
;
-- Execute the query...
SET @@session.sort_buffer_size := DEFAULT;
Wrapper functions can be handy for this type of code. Other variables you might set
on a per-connection basis are
read_buffer_size
,
read_rnd_buffer_size
,
tmp_table
_size
, and
myisam_sort_buffer_size
(if you're repairing tables).
If you need to save and restore a possibly customized value, you can do something like
the following:
SET @saved_
<unique_variable_name>
:= @@session.sort_buffer_size;
SET @@session.sort_buffer_size :=
<value>
;
-- Execute the query...
SET @@session.sort_buffer_size := @saved_
<unique_variable_name>
;
The sort buffer size is one of the settings that is the focus of far too much
“tuning.” Some people seem to have the idea that bigger is better, and
we've even seen servers with this variable set to 1 GB. Perhaps not sur-
prisingly, this can cause the server to try to allocate too much memory
and crash, or simply to burn a lot of CPU time when initializing the sort
buffer for a query; see MySQL bug 37359 for more on this.
Don't assign too much importance to the sort buffer size. Do you really
need your queries to allocate 128 MB of memory to sort 10 rows and
return them to the client? Think about what kinds of sorting your quer-
ies are doing, and how much, and try to avoid them with proper indexing
and query design (see
Chapter 5
and
Chapter 6
) rather than trying to
make the sorting operation itself faster. And you should definitely pro-
file your queries to see whether sorting is where you should focus your
attention anyway; see
Chapter 3
for an example of a query that performs
a sort but doesn't spend much of its time sorting.
Getting Started
Be careful when setting variables. More is not always better, and if you set the values
too high, you can easily cause problems: you might run out of memory, causing your
server to swap, or run out of address space.
1
1. A common mistake we've seen is to set up a server with twice as much memory as your existing server,
and—using the old server's configuration as a baseline—create the new server's configuration by
multiplying everything by two. This doesn't work.
