Database Reference
In-Depth Information
Are the key caches sized properly for efficient operation?
The InnoDB and MyISAM stor‐
age engines each have a key cache. They serve to improve performance of index key
lookups, so it's critical that they operate well. The main configuration setting for each
is the cache size, and the operational status indicators are the number of requests for
keys from the cache and the number of disk reads to pull values into the cache.
To determine the cache sizes, check the relevant system variables:
mysql>
SELECT @@innodb_buffer_pool_size, @@key_buffer_size;
+---------------------------+-------------------+
| @@innodb_buffer_pool_size | @@key_buffer_size |
+---------------------------+-------------------+
| 134217728 | 8388608 |
+---------------------------+-------------------+
You can also use
SHOW
VARIABLES
or the
INFORMATION_SCHEMA
GLOBAL_VARIABLES
table.
For example:
mysql>
SELECT * FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES WHERE
->
VARIABLE_NAME IN ('INNODB_BUFFER_POOL_SIZE','KEY_BUFFER_SIZE');
+-------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+-------------------------+----------------+
| KEY_BUFFER_SIZE | 8388608 |
| INNODB_BUFFER_POOL_SIZE | 134217728 |
+-------------------------+----------------+
The efficiency measure that determines how well a key cache is operating is its hit rate:
the rate at which key requests are satisfied from the cache without reading keys from
disk. If a key is in the cache, it's a hit; if not, it's a miss. The following expression computes
the hit rate, where
reads
and
requests
indicate the number of disk reads and number
of requests, respectively:
1 - (
reads
/
requests
)
To apply the expression to InnoDB or MyISAM, plug in the appropriate status variables:
1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
1 - (Key_reads / Key_read_requests)
If there have been no read requests, the expressions involve a division-by-zero opera‐
tion, so it's necessary to account for that.
Values close to 1 indicate a high hit rate, which means that the key cache is very efficient.
Values close to 0 indicate a low hit rate. If the value is not close to 1, consider making
the cache larger by increasing the appropriate system variable (
in
nodb_buffer_pool_size
or
key_buffer_size
).
Suppose that you want to access the hit rate for the two caches by executing a SQL script
from the command line. Because the status variables are not simply displayed but are
used in a calculation, we require their values in a form that permits that use.
@@
syntax
