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) ParametersThis 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:
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:
39.3.1.1 Maximum Connections AllowedThe 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 CacheWhen 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 CacheThe 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 BuffersTwo memory-related InnoDB resources are the buffer pool and the log buffer:
39.3.1.5 Selecting Storage EnginesIf 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 ParametersResources 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:
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. |