Databases Reference
In-Depth Information
Side Effects of Setting Variables
Setting variables dynamically can have unexpected side effects, such as flushing dirty
blocks from buffers. Be careful which settings you change online, because this can cause
the server to do a lot of work.
Sometimes you can infer a variable's behavior from its name. For example,
max_
heap_table_size
does what it sounds like: it specifies the
maximum
size to which im-
plicit in-memory temporary tables are allowed to grow. However, the naming conven-
tions aren't completely consistent, so you can't always guess what a variable will do by
looking at its name.
Let's take a look at some commonly used variables and the effects of changing them
dynamically:
key_buffer_size
Setting this variable allocates the designated amount of space for the key buffer (or
key cache) all at once. However, the operating system doesn't actually commit
memory to it until it is used. Setting the key buffer size to one gigabyte, for example,
doesn't mean you've instantly caused the server to actually commit a gigabyte of
memory to it. (We discuss how to watch the server's memory usage in the next
chapter.)
MySQL lets you create multiple key caches, as we explain later in this chapter. If
you set this variable to
0
for a nondefault key cache, MySQL discards any indexes
cached in the specified cache, begins to cache them in the default cache, and deletes
the specified cache when nothing is using it anymore. Setting this variable for a
nonexistent cache creates it. Setting the variable to a nonzero value for an existing
cache will flush the specified cache's memory. This blocks all operations that try
to access the cache until the flush is finished.
table_cache_size
Setting this variable has no immediate effect—the effect is delayed until the next
time a thread opens a table. When this happens, MySQL checks the variable's
value. If the value is larger than the number of tables in the cache, the thread can
insert the newly opened table into the cache. If the value is smaller than the number
of tables in the cache, MySQL deletes unused tables from the cache.
thread_cache_size
Setting this variable has no immediate effect—the effect is delayed until the next
time a connection is closed. At that time, MySQL checks whether there is space in
the cache to store the thread. If so, it caches the thread for future reuse by another
connection. If not, it kills the thread instead of caching it. In this case, the number
of threads in the cache, and hence the amount of memory the thread cache uses,
does not immediately decrease; it decreases only when a new connection removes
a thread from the cache to use it. (MySQL adds threads to the cache only when
connections close and removes them from the cache only when new connections
are created.)
