16.2 Tuning Memory Parameters

As the server runs, it opens files, reads information from tables to process queries, and sends the results to clients. In many cases, the server processes information that it has accessed earlier. If the server can buffer or cache this information in memory rather than reading it from disk repeatedly, it runs more efficiently and performs better. By tuning server parameters appropriately using system variables, you can control what information the server attempts to keep in memory. Some buffers are used globally and affect server performance as a whole. Others apply to individual clients, although they're still initially set to a default value controlled by the server.

Memory is a finite resource and you should allocate it in ways that make the most sense for your system. For example, if you run lots of complex queries using just a few tables, it doesn't make sense to have a large table cache. You're likely better off increasing the key buffer size. On the other hand, if you run simple queries from many different tables, a large table cache will be of much more value.

Keep in mind that increasing the value of a server parameter increases system resource consumption by the server. You cannot increase parameter values beyond what's available, and you should not allocate so much memory to MySQL that the operating system suffers in its own performance. (Remember that the operating system itself requires system resources.)

In general, the server's default parameter settings are conservative and have small values. This enables the server to run even on modest systems with little memory. If your system has ample memory, you can allocate more of it to MySQL to tune it to the available resources.

Typically, you set parameter values using options in the [mysqld] section of an option file so that the server uses them consistently each time it starts. For system variables that are dynamic, you can change them while the server runs to test how the changes affect performance. After you determine optimum values this way, record them in the option file for use in subsequent server restarts.

To get an idea of appropriate settings for systems of various sizes, look at the sample option files that MySQL distributions include. They're located in the main directory of your MySQL distribution on Windows, or in the scripts directory on Unix. The files are named my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf. Each includes comments that indicate the typical kind of system to which it applies. For example, a small system may use options with small values:

[mysqld]

key_buffer_size = 16K

table_cache = 4

sort_buffer_size = 64K

For a larger system, you can increase the values and also allocate memory to the query cache:

[mysqld]

key_buffer_size = 256M

table_cache = 256

sort_buffer_size = 1M

query_cache_type = ON

query_cache_size = 16M

The material in this section is oriented toward server-side tuning. Client-side techniques can be applied to optimize the performance of individual queries, as discussed in Chapter 13, "Optimizing for Query Speed."

16.2.1 Global (Serverwide) Variables

This section discusses server parameters for resources that affect server performance as a whole or that are shared among clients. When tuning server parameters, there are three factors to consider:

• The resource that the server manages.

• The system variable that controls the size of the resource.

• Status variables that relate to the resource. These enable you to determine how well the resource is configured.

For example, the key buffer that the server uses to cache index blocks is a resource. The size of the key buffer is set using the key_buffer_size system variable, and the effectiveness of the key buffer can be measured using the Key_reads and Key_read_requests status variables.

This section covers the following memory-related resources:

• The maximum number of simultaneous client connections the server supports.

• The table cache that holds information about tables that storage engines have open.

• The key buffer that holds MyISAM index blocks.

• The InnoDB buffer pool that holds InnoDB table data and index information, and the InnoDB log buffer that holds transaction information before it's flushed to the InnoDB log file.

16.2.1.1 Maximum Connections Allowed

The MySQL server uses a multithreaded architecture that allows it to service multiple clients simultaneously. A thread is like a small process running inside the server. For each client that connects, the server allocates a thread handler to service the connection, so the term thread in MySQL is roughly synonymous with connection.

The max_connections system variable controls the maximum allowable number of simultaneous client connections. The default value is 100. If your server is very busy and needs to handle many clients at once, the default might be too small. However, each active connection handler requires some memory, so you don't necessarily want to set the number as high as the number of threads your operating system allows.

To see how many clients currently are connected, check the value of the Threads_connected status variable. If its value often is close to the value of max_connections, it might be good to increase the value of the latter to allow more connections. If clients that should be able to connect to the server frequently cannot, that's another indication that max_connections is too small.

16.2.1.2 The Table Cache

When the server opens a table, it maintains information about that table in the table cache, which is used to avoid reopening tables when possible. The next time a client tries to access the table, the server can use it immediately without opening the table again if it is found in the cache. However, if the cache is full and a client tries to access a table that isn't found there, an open table must be closed to free an entry in the cache for the new table. The table that is closed then must be reopened the next time a client accesses it.

The table_cache system variable controls the size of the table cache. Its default value is 64. The goal when configuring the table cache is to make it large enough that the server need not repeatedly open frequently accessed tables. Against this goal you must balance the fact that with a larger table cache the server requires more file descriptors. Operating systems place a limit on the number of file descriptors allowed to each process, so the table cache cannot be made arbitrarily large. However, some operating systems do allow the per-process file descriptor limit to be reconfigured.

To determine whether the cache is large enough, check the Open_tables and Opened_tables status variables. Open_tables indicates how many tables currently are open, and Opened_tables indicates how many table-opening operations the server has performed since it started. If Open_tables usually is at or near the value of table_cache and the value of Opened_tables increases steadily, it indicates that the table cache is being used to capacity and that the server often has to close tables in the cache so that it can open other tables. This is a sign that the table cache is too small and that you should increase the value of table_cache.

16.2.1.3 The Key Buffer

The key buffer (key cache) is a resource in which the server caches index blocks read from MyISAM tables. Indexes speed up retrievals, so if you can keep index values in memory and reuse them for different queries rather than rereading them from disk, performance is even better. When MySQL needs to read an index block, it checks first whether the block is in the key buffer. If so, it can satisfy the read request immediately using a block in the buffer. If not, it reads the block from disk first and puts it in the key buffer. The frequency of these two actions is reflected by the Key_read_requests and Key_reads status variables. If the key buffer is full when a block needs to be read, the server discards a block already in the buffer to make room for the new block.

The ideal situation is for MySQL to consistently find the index blocks that it needs in the buffer without having to read them from disk. In other words, Key_reads should remain as low as possible relative to Key_read_requests.

You can use the two status variables to assess the effectiveness of the key buffer in terms of keys either missing or present in the buffer. These values are the key buffer miss rate and its efficiency. The miss rate is calculated as follows:

Key_reads / Key_read_requests

The complementary value, key buffer efficiency, is calculated like this:

1 - (Key_reads / Key_read_requests)

Suppose that the status variables have the following values:

mysql> SHOW STATUS LIKE 'Key_read%';

+-------------------+--------+

| Variable_name     | Value  |

+-------------------+--------+

| Key_read_requests | 539614 |

| Key_reads         | 6133   |

+-------------------+--------+

From those values, the key buffer miss rate and efficiency can be calculated:

miss rate = 6133 / 539614 = .0114

efficiency = 1 - (6133 / 539614) = .9886

You want the miss rate to be as close as possible to 0 and the efficiency as close as possible to 1. By that measure, the values just calculated are reasonably good. If the values for your server are not so good and you have memory available, you can improve the key buffer's effectiveness by increasing the value of the key_buffer_size system variable. Its default value is 8MB.

16.2.1.4 InnoDB Buffers

Two memory-related InnoDB resources are the buffer pool and the log buffer:

• The InnoDB buffer pool caches data and index information for InnoDB tables. Making the buffer pool larger reduces disk I/O for frequently accessed InnoDB table contents. The buffer pool size is controlled by the innodb_buffer_pool_size system variable. Its default value is 8MB. On a machine dedicated to MySQL, you can set this variable anywhere from 50% to 80% of the total amount of memory. However, the setting should take into account how large you set the key_buffer_size value.

• The InnoDB log buffer holds information about modifications made during transaction processing. Ideally, you want a transaction's changes to be held in the buffer until the transaction commits, at which point they can be written to the InnoDB log file all at once. If the buffer is too small, changes might have to be written several times before commit time, resulting in additional disk activity. The log buffer size is controlled by the innodb_log_buffer_size system variable. Typical values range from 1MB to 8MB. The default is 1MB.

16.2.1.5 Selecting Storage Engines

If you need to save memory, one way to do so is to disable unneeded storage engines. Some of the compiled-in storage engines can be enabled or disabled at runtime. Disabling an unneeded storage engine reduces the server's memory requirements because it need not allocate buffers and other data structures associated with the engine. You can disable the InnoDB and BDB engines this way with the --skip-innodb and --skip-bdb options at server startup time.

It's also possible to disable InnoDB or BDB entirely by compiling the server without them. For example, you can disable the InnoDB storage engine using the --without-innodb configuration option. BDB is enabled only if you use the --with-berkeley-db configuration option, so to leave BDB support disabled, just omit the option. Consult the installation chapter of the MySQL Reference Manual for further instructions.

The MyISAM storage engine is always compiled in and cannot be disabled at runtime. This ensures that the server always has a reliably available storage engine, no matter how it might otherwise be configured.

16.2.2 Per-Client Variables

Resources such as the table cache and key buffer are shared globally among all clients, but the server also allocates a set of buffers for each client that connects. The variables that control their sizes are collectively known as per-client variables.

Be cautious when increasing the value of a per-client variable. For each per-client buffer, the potential amount of server memory required is the size of the buffer times the maximum allowed number of client connections. Parameters for these buffers normally are set to 1MB or 2MB, at most, to avoid causing exorbitant memory use under conditions when many clients are connected simultaneously.

Per-client buffers include the following:

• MySQL uses a record buffer to perform sequential table scans. Its size is controlled by the read_buffer_size system variable. Increasing the size of this buffer allows larger chunks of the table to be read at one time, which can speed up scanning by reducing the number of disk seeks required. A second record buffer also is allocated for use in reading records after an intermediate sort (such as might be required by an ORDER BY clause) or for nonsequential table reads. Its size is controlled by the read_rnd_buffer_size variable, which defaults to the value of read_buffer_size if you don't set it explicitly. This means that changing read_buffer_size potentially can actually result in double the effective memory increase.

• The sort buffer is used for operations such as ORDER BY and GROUP BY. Its size is controlled by the sort_buffer_size system variable. If clients execute many queries that sort large record sets, increasing the sort buffer size can speed up sorting operations.

• The join buffer is used to process joins. Its size is controlled by the join_buffer_size system variable. Increase the value if clients tend to perform complex joins.

• The server allocates a communication buffer for exchanging information with the client. If clients tend to issue very long queries, the queries will fail if the communication buffer isn't large enough to handle them. The buffer size is controlled by the max_allowed_packet parameter. For example, to allow clients to send up to 128MB of information at a time, configure the server like this:

  
  

  
  [mysqld]
  
  max_allowed_packet = 128M
  
  

  
  

  Note that unlike a parameter such as read_buffer_size, it's generally safe to set the value of max_allowed_packet quite high. The server does not actually allocate a communication buffer that large as soon as a client connects. It begins with a buffer of size net_buffer_length bytes and increases it as necessary, up to a maximum of max_allowed_packet bytes.

Although these buffers are client-specific, it isn't necessarily the case that the server actually allocates each one for every client. No sort buffer or join buffer is allocated for a client unless it performs sorts or joins.

One scenario in which very long queries can occur is when you dump tables with mysqldump and reload them with mysql. If you run mysqldump with the --opt option to create a dump file containing long multiple-row INSERT statements, those statements might be too long for the server to handle when you use mysql later to send the contents of the file back to the server to be reloaded. Note that it might be necessary to set the client-side value of max_allowed_packet in both cases as well. mysqldump and mysql both support a --max_allowed_packet option for setting the client-side value.