- How do I configure an equivalent of the Informix buffer pool in MySQL?
Informix Dynamic Server stores dbspaces pages (index and data) that read from the disk in buffer pool. This pool of buffers comprise of largest portion of shared memory. Any data being accessed on disk first has to be loaded into the buffer pool for users to access it, and it is thus the primary focus of database optimization. MySQL has equivalent configurations specified on a per storage engine basis, and these are equally critical to set correctly for maximum database performance.
InnoDB Buffer Pool
The InnoDB storage engine has a buffer pool that is similar to the DB2 buffer pool in that it caches requests to indexes and data files on disk. It will pre-allocate its buffer pool immediately upon MySQL startup, and then not use more memory than is allocated. As a result, it behaves as though it has min server memory and max server memory configurations that are identical. This configuration can be set for MySQL as:
innodb_buffer_pool_size = 512M
If a system is being configured to run solely as a database server using only 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 a InnoDB buffer pool, execute the SHOW INNODB STATUS statement . Below is an example of what is returned:
---------------------- 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
Pay special attention to the section that reads Buffer pool hit rate. It's important to get that ratio as close to 100% as possible. For large databases, though, 95%-99% is the best that can be hoped for and might indicate 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. This difference requires another tuning methodology 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:
key_buffer = 256M
Unlike the InnoDB buffer pool, the MyISAM key buffer will not pre-allocate itself at MySQL startup. Instead, it will only be used as required. So having a high setting for a small database will not do any harm. In this way the key_buffer configuration is similar to setting min server memory to 0, and setting the max server memory configuration to the key_buffer limit. 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, and 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 to not exceed this total memory allocation for the MySQL database process, and 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.