- How do I tune the MyISAM key buffer and InnoDB buffer pool in MySQL?
The most important memory component for MySQL performance tuning are the buffer pools. Any data being accessed on disk, first has to be loaded into the buffer pools for users to access it. Therefore, it's the primary focus of database optimization. By default, MySQL will only use 8MB for MyISAM's key buffer and 8MB for InnoDB's buffer pool. Therefore, databases of any significant size will need these configurations tuned. The upper limit is determined in part by your operating system process limits, and how much memory is available with your system's hardware.
InnoDB Buffer Pool
The InnoDB storage engine has a buffer pool that caches requests to indexes and data files on disk. It will pre-allocate its buffer pool when MySQL is started. It won't use more memory than is allocated. This can be set in the option or configuration file (i.e., my.cnf or my.ini, depending on your system) for MySQL as:
[mysqld] set-variable = innodb_buffer_pool_size = 512M ...
If a system is being configured to run solely as a database server using the InnoDB storage engine, this configuration could be set up to 80% of total system memory for the best effect.
To examine the performance of an InnoDB buffer pool, use the SHOW statement like so:
SHOW INNODB STATUS;
Below is an except from the results of this statement:
---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 571450402; in additional pool allocated 171904 Buffer pool size 32000 Free buffers 29034 Database pages 2965 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 64, created 2901, written 3855 0.00 reads/s, 39.00 creates/s, 119.07 writes/s Buffer pool hit rate 1000 / 1000
Notice the last line above that reads Buffer pool hit rate. It's important to get that rate as close to 100% as possible. For large databases, however, 95%-99% is the best that can be achieved and indicates a well tuned system.
MyISAM Key Buffer
The MyISAM storage engine uses a key buffer for caching index data from disk. Actual row data is buffered by the operating system file cache, and this difference requires another tuning method than for the InnoDB buffer pool. Because the operating system is caching row data, some system memory must remain free to cache the row data on disk. Typically, the key buffer will be set to use up to 50% of the main memory of a dedicated database using MyISAM as its storage engine for optimal database performance. It is configured like this:
set-variable = key_buffer = 256M
Unlike the InnoDB buffer pool, the MyISAM key buffer will not pre-allocate itself at MySQL startup, but will instead only be used as required, so having a high setting for a small database will not do any harm.
In order to evaluate the performance of the MyISAM key buffer, you may look at the MySQL status like so:
SHOW STATUS LIKE 'key%'; +------------------------+---------+ | Variable_name | Value | +------------------------+---------+ | Key_blocks_not_flushed | 0 | | Key_blocks_unused | 229554 | | Key_blocks_used | 6987 | | Key_read_requests | 3011875 | | Key_reads | 6987 | | Key_write_requests | 1071612 | | Key_writes | 1071612 | +------------------------+---------+
To determine the cache read miss rate, simply divide the Key_reads by the Key_read_requests. In this case the cache miss ratio is 23% which suggests a fairly well tuned database.
Mixed Storage Engines
In the case where the database application will mix the use of storage engines, both the innodb_buffer_pool_size and key_buffer will have to be configured, but keeping in mind the total system memory, as well as the per process memory limits on the system. With 32-bit systems, the per process limit will usually be 2G memory in which case care must also be taken not to exceed this total memory allocation for the MySQL database process. One must also factor in other settings like the per connection thread memory buffers: sort_buffer_size, read_rnd_buffer_size, read_buffer_size, and join_buffer_size.
A typical usage of mixed storage engines might be InnoDB tables used to capture data in a transaction safe way, and then batch copied into MyISAM tables for its FULLTEXT search and RTREE spatial search capabilities, so this kind of set up is not uncommon.
32-bit vs. 64-bit Hardware
On 32-bit platforms, the usual limit for memory allocated to MySQL is 2GB, so the MyISAM key buffer and InnoDB buffer pool will need to be configured well below that to allow memory to be allocated for connecting threads. Even if a system has more than 2GB memory, the MySQL server process can only use 2GB memory on these systems, and if its tries to use more memory, it will crash. In the case of MyISAM, this is not so bad because the operating system is relied upon for caching access to the data files. Therefore, databases with 4GB or 6GB memory can be tuned effectively for MyISAM access, even with this 2GB limit.
On 64-bit platforms, which for x86-64 systems requires both operating system and hardware support, the per process memory limits are increased. The InnoDB buffer pool could then be set as high as 128GB, which is not the limit. Also, MyISAM key buffer can be configured as high as 4GB. This is currently the maximum limit for the key_buffer configuration.