Advanced MySQL Administration
 
Server Tuning
 
Server tuning refers to the optimal configuration of the MySQL server so that it uses hardware as efficiently as possible and executes SQL commands with maximum efficiency.
 
Server tuning is worthwhile, as a rule, only if very large databases are involved (in the gigabyte range), many queries per second are to be processed, and the computer is serving primarily as a database server.
 
This section provides merely a first introduction to this topic and is restricted primarily to the correct configuration of the buffer storage and the use of the query cache. Note, however, that server tuning is only a component of the larger topic that perhaps might be called optimization of database applications. On this theme one could easily write an entire book, which would, among other things, answer the following questions:
 
. What possibilities are there to optimize the database design in such a way that the most frequently used commands are executed with maximum efficiency? Were the optimal indexes set up? The correct database design is assuredly the most important and undervalued component of speed optimization. Fine tuning the server for a poorly designed database is like hitching a racehorse to a carriage with square wheels.
 
. What is the best hardware for the task (within a given price range)?
. What is the best operating system (if there is a choice)?
. What is the optimal table format
. Can the burden of many SELECT queries be divided among a number of computers?
 
Optimal Memory Management
 
MySQL reserves at startup a portion of main memory for certain tasks, such as a cache for data records and a location for sorting data. The size of this buffer is controlled by options in the configuration file and generally cannot be altered while the server is in operation. It can happen that MySQL leaves a great deal of RAM unused, even though there was sufficient memory available and MySQL could make use of it.
 
The setting of the parameters takes place in the mysqld section of the MySQL configuration file. Memory sizes can be abbreviated by K (kilobytes), M (megabytes), and G (gigabytes). The following lines clarify the syntax.
 
#in /etc/my.cnf bzw. Windows\my.ini
[myslqd]
key_buffer_size = 42M
 
In the following, various important configuration parameters will be introduced (though not all of them by a long shot). Unfortunately, one cannot say which parameter settings should be changed and to what, it all depends heavily on the specific application. However, first attempts should include key_buffer_size and table_cache:
 
. The parameter key_buffer_size (default 8M) tells how much storage is to be reserved for index blocks. The higher the value, the more rapid is table access to columns for which there is an index. On dedicated database servers it can make sense to increase key_buffer_size up to one-fourth of the available RAM.
 
. The parameter table_cache (default 64) specifies how many tables can be open at one time. The opening and closing of table files costs time, and so a larger value of the parameter can increase parallel access to many tables. On the other hand, open tables cost RAM, and the number is also limited by the operating system. The number of tables open in MySQL can be determined with SHOW STATUS (variable open_tables).
 
. The parameter sort_buffer (default 2M) specifies the size of the sorting buffer. This buffer is used in SELECT commands with ORDER BY or GROUP BY if there is no index available. If the buffer is too small, then a temporary file must be used, which is, of course, slow. The default value of 2 megabytes should suffice for many purposes.
 
. The parameter read_buffer_size (formerly record_buffer, default 128K) specifies how much memory each thread reserves for reading sequential data from tables. The parameter should not be unnecessarily large, since this memory is required for each new MySQL connection (thus for each MySQL thread, not only once for the entire server). It is best to increase the parameter only when it is needed for a particular session with SET SESSION read_buffer_size=n.
 
. The parameter read_rnd_buffer_size (default 256K) has an effect similar to that of read_buffer_size, except that it is valid for the case in which the records are to be read out in a particular order (as with ORDER BY ). A larger value can avoid search operations on the hard disk, which can slow things down considerably with large tables. As with read_buffer_size, read_rnd_buffer_size should be increased only as needed with SET SESSION.
 
. The parameter bulk_insert_buffer_size (default 8M) specifies how much memory is reserved for the execution of INSERT commands in which many records are to be inserted simultaneously (such as INSERT ... SELECT ... ). This parameter can also be changed for individual connections with SET SESSION.
 
. The parameter join_buffer_size (default 128K) specifies how much memory is to be used for JOIN operations when there is no index for the JOIN columns. (For tables that are frequently linked there should definitely by an index for the linking field. This will contribute more to speed efficiency than increasing this parameter.)
 
. The parameter tmp_table_size (default 32M) specifies how large temporary HEAP tables can get. If this size is exceeded, then the tables are transformed into MyISAM tables and stored in a temporary file.
 
. The parameter max_connections (default 100) gives the maximum number of database connections that can be open at one time. The value should not be unnecessarily high, since each connection requires memory and a file descriptor. On the other hand, persistent connections profit from a larger number of allowed connections, since then it is less frequent that a connection is closed and a new one has to be opened. (With SHOW STATUS you can determine max_used_connections. This is the maximum number of connections that were open simultaneously up to a particular time.)
 
Query Cache
 
The query cache is a new function in MySQL 4.0. The basic idea is to store the results of SQL queries. If later this exact same query is to be executed, then the stored result can be used instead of having to search through all the affected tables.
 
The query cache is no panacea, though, for speed optimization. In particular, queries must be deleted from the query cache as soon as the underlying tables are altered:
 
. The query cache is therefore useful only if the data change relatively seldom (thus many SELECT commands in relation to the number of UPDATE, INSERT, and DELETE commands), and it is expected that particular queries will be freqently repeated (which is frequently the case with web applications).
 
. The SELECT commands must be exactly the same (including spaces and case), so that the query cache knows that they are, in fact, the same.
 
. The SELECT commands cannot contain user-defined variables and cannot use certain functions, the most significant of which are RAND, NOW, CURTIME, CURDATE, LAST_INSERT_ID, HOST.
 
If these conditions are not satisfied, then the query cache SELECT queries will, in the worst case, slow the system down somewhat (due to the management overhead).
 
Activating the Query Cache
 
By default, the query cache is deactivated (due to the default setting query_cache_size=0). To activate the query cache, execute the following changes to the MySQL configuration file:
  
#in /etc/my.cnf or Windows\my.ini
[myslqd]
query_cache_size = 32M
query_cache_type = 1
query_cache_limit = 50K
# 0=Off, 1=On, 2=Demand
 
Now 32 megabytes of RAM is reserved for the query cache. In the cache are stored only SELECT results that require less than 50 kilobytes. (This avoids the situation in which a few large query results force all other results out of the cache.)

After a server restart, the cache is automatically active. For MySQL applications nothing changes (except that the reaction time to repeated queries is less).
 
Demand Mode
 
The query cache can also be run in demand mode. In this case, only those SELECT queries are considered that are executed with the option SQL_CACHE, as in SELECT SQL_CACHE * FROM authors. This mode is useful if you wish to control which commands use the cache.
 
No Temporary Storage of SQL Query Results
 
If you wish to prevent a SELECT command from using the active query cache (query_cache_type=1), then simply add the option SQL_NO_CACHE. This makes sense with commands about which one is certain that they will not soon be repeated and would therefore take up space unnecessarily in the query cache.
 
Turning the Query Cache On and Off for a Connection
 
You can change the mode of the query cache for a particular connection. Just execute SET query_cache_type = 0/1/2/OFF/ON/DEMAND.
 
Determining the Status of the Query Cache
 
If you wish to know how well the query cache is functioning, whether its size is well chosen, etc., then execute the command SHOW STATUS LIKE 'qcache%'.
 
With FLUSH QUERY CACHE you can defragment the cache (which, the MySQL documentation states, makes possible improved memory usage, but it does not empty the cache). RESET QUERY CACHE deletes all entries from the cache.