39.3. 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 still are 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 is 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 allows the server to run even on modest systems with little memory. If your system has ample memory, you can (and should) 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, set them in the option file for use in subsequent server restarts.

To get an idea of settings that are appropriate for systems of various sizes, look at the sample option files that MySQL distributions include. On Windows, they have names like my-small.ini and my-large.ini and are located in the MySQL installation directory. On Unix, they have names like my-small.cnf and my-large.cnf. Likely locations are in /usr/share/mysql for RPM installations or the share directory under the MySQL installation directory for tar file installations. Each sample file 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 may be applied to optimize the performance of individual queries, as discussed in Chapter 22, "Basic Optimizations," and Chapter 37, "Optimizing Queries."

39.3.1. Global (Server-Wide) Parameters

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 indicates the size of the resource. You can control the size by setting the variable.

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

For example, the key cache that the server uses to cache MyISAM index blocks is a resource. The size of the key cache is set using the key_buffer_size system variable, and the effectiveness of the key cache 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 cache 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 is flushed to the InnoDB log file.

39.3.1.1 Maximum Connections Allowed

The MySQL server uses a multi-threaded 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, but if your server is very busy and needs to handle many clients at once, the default might be too small. On the other hand, each active connection handler requires some memory, so you don't necessarily want to set the number to the maximum number of threads that 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 too is an indication that max_connections is too small.

39.3.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, some 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 number of entries in 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 over time. 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.

39.3.1.3 The MyISAM Key Cache

The key cache (key buffer) is a resource in which the server caches index blocks that it reads from MyISAM tables. The key_buffer_size system variable controls the size of the key cache.

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 cache. If so, it can satisfy the read request immediately using a block in the cache. If not, it reads the block from disk first and puts it in the key cache. The frequency of these two actions is reflected by the Key_read_requests and Key_reads status variables. If the key cache is full when a block needs to be read, the server discards a block already in the cache to make room for the new block.

The ideal situation is for MySQL to consistently find the index blocks that it needs in the cache 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 cache in terms of keys either missing or present in the cache. These values are the key cache miss rate and its efficiency. To calculate the miss rate, use the following formula:

 Key_reads / Key_read_requests 

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

 1 - (Key_reads / Key_read_requests) 

Suppose that the key cache status variables have the following values:

 mysql> SHOW STATUS LIKE 'Key_read%'; +-------------------+----------+ | Variable_name     | Value    | +-------------------+----------+ | Key_read_requests | 73137065 | | Key_reads         | 2069133  | +-------------------+----------+ 

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

 miss rate = 2069133 / 73137065 = .0283 efficiency = 1 - (2069133 / 73137065) = .9717 

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 cache's effectiveness by increasing the value of the key_buffer_size system variable. Its default value is 8MB.

MySQL supports the creation of additional MyISAM key caches and enables you to assign tables to specific caches. For details, see Section 37.4, "MyISAM Index Caching Index Caching."

39.3.1.4 The InnoDB Buffer Pool and Log 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 need 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.

39.3.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 engine this way with the --skip-innodb option at server startup.

It's also possible to disable InnoDB entirely by compiling the server without it. To do this, use the --without-innodb configuration 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.

39.3.2. Per-Client Parameters

Resources such as the table cache and the MyISAM key cache are shared globally among all clients. 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 non-sequential table reads. Its size is controlled by the read_rnd_buffer_size variable, which defaults to the value of read_buffer_size if you do not 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 is not 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 is 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 (which is enabled by default) 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.



MySQL 5 Certification Study Guide
MySQL 5.0 Certification Study Guide
ISBN: 0672328127
EAN: 2147483647
Year: 2006
Pages: 312

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net