|< Day Day Up >|
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
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:
220.127.116.11 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.
18.104.22.168 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.
22.214.171.124 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.
126.96.36.199 InnoDB Buffers
Two memory-related InnoDB resources are the buffer pool and the log buffer:
188.8.131.52 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.
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.
|< Day Day Up >|