Unusable space in MySQL Key Buffers

What started out as a key buffer efficiency study turned to a fruitless search for ways to overcome a somewhat high ( ~20%) amount of unusable key buffer space. This behavior seems to be platform and version independent as I observed this with versions 5.0.45, 5.0.52, and 5.1.22 on Solaris 10, Windows XP and Linux.

I'm not the first person to discover this, but I haven't had much luck finding an explanation. Just like this poor soul whose post fell into the swirling vortex of the un-interesting:
http://forums.mysql.com/read.php?20,127934

Finding usage, and physical read/write information about individual key buffers is slightly obscure. The only way I know of is to run "mysqladmin debug" and view the information in the MySQL server error log.

The output looks like this:

Key caches:
default
Buffer_size: 16777216
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 0
not flushed: 0
w_requests: 0
writes: 0
r_requests: 0
reads: 0


custcache
Buffer_size: 27262976
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 23513
not flushed: 0
w_requests: 0
writes: 0
r_requests: 24479
reads: 24479


I experimented by creating and populating an indexed MYISAM table , and then binding the index to key caches of a varying sizes. My hopes where to stumble on a way to harvest that other 20 percent. I tried adjusting the division_limit, which changes the replacement strategy, but found this had no effect.

As a result, I've given up trying to use the entire configured key buffer size...for now. Instead, I am exploring how the "MAX_ROWS" option in the CREATE TABLE statement affects index size. I'd rather get some more empirical data before saying much more. But I have found that by sizing MAX_ROWS to be slightly higher than the actual number of rows, the index size is reduced and therefore can fit in a smaller key buffer.


The gory details of my experiments:
---------------------------------
TEST 1: KEY BUFFER TOO SMALL:
---------------------------------
customers index file is 52 meg. created custcache with 8 meg. loaded customers index.


set @@global.custcache.key_buffer_size=8*1024*1024 ;
cache index customers in custcache ;
load index into cache customers ;


Result from MySQL error log after running " mysqladmin debug":


custcache
Buffer_size: 8388608
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 7173
not flushed: 0
w_requests: 0
writes: 0
r_requests: 51795
reads: 51795

Ran query to see hit rate ( explain executed to show plan) :

select sql_no_cache customer_id from customers ;
mysql> explain select sql_no_cache customer_id from customers ;
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
id select_type table type possible_keys key key_len ref rows Extra
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+
1 SIMPLE customers index NULL PRIMARY 4 NULL 921338 Using index
+----+-------------+-----------+-------+---------------+---------+---------+------+--------+-------------+


Result from MySQL error log after running " mysqladmin debug":


custcache
Buffer_size: 8388608
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 7173
not flushed: 0
w_requests: 0
writes: 0
r_requests: 113466
reads: 62146


Interpretation: The index was not completely cached so there were some physical index reads during the select statement.


---------------------------------
TEST 2: KEY BUFFER TOO BIG:
---------------------------------
restarted mysql server. created custcache with 80 meg. loaded customers index.


set @@global.custcache.key_buffer_size=80*1024*1024 ;
cache index customers in custcache ;
load index into cache customers ;


Result from MySQL error log after running " mysqladmin debug":


custcache
Buffer_size: 83886080
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 51795
not flushed: 0
w_requests: 0
writes: 0
r_requests: 51795
reads: 51795


ran query to see hit rate:

select sql_no_cache customer_id from customers ;

Result from MySQL error log after running " mysqladmin debug":


custcache
Buffer_size: 83886080
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 51795
not flushed: 0
w_requests: 0
writes: 0
r_requests: 113466
reads: 51795


Interpretation: The index was completely cached so there were no physical index reads during the select statement. Note also that the blocks used section is very close to the actual index file (customers.MYI) size.


---------------------------------
TEST 3: KEY BUFFER SLIGHTLY LARGER THAN THE INDEX:
---------------------------------

restarted mysql server. created custcache with 54 meg. loaded customers index.

set @@global.custcache.key_buffer_size=54*1024*1024 ;
cache index customers in custcache ;
load index into cache customers ;


Result from MySQL error log after running " mysqladmin debug":


custcache
Buffer_size: 56623104
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 48346
not flushed: 0
w_requests: 0
writes: 0
r_requests: 51795
reads: 51795


ran query to see hit rate:

select sql_no_cache customer_id from customers ;


Result from MySQL error log after running " mysqladmin debug":



custcache
Buffer_size: 56623104
Block_size: 1024
Division_limit: 100
Age_limit: 300
blocks used: 48346
not flushed: 0
w_requests: 0
writes: 0
r_requests: 113466
reads: 53099


Interpretation: There were some physical reads. Even though the key buffer should be big enough to hold the index, there is some empty space.