MySQL Bugs: #24843: MyISAM key buffer not 100% used, though it should be?

Bug #24843	MyISAM key buffer not 100% used, though it should be?
Submitted:	6 Dec 2006 4:04	Modified:	29 Jul 2010 19:41
Reporter:	P Eger	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S4 (Feature request)
Version:	5.0.27, 5.0.24a, 5.1.12	OS:	Linux (RHEL4 x64, Windows x64)
Assigned to:		CPU Architecture:	Any

Description:
I seem to be having troubles with the MyISAM key buffer, it never seems to get more than 75-80% full. Instance manager and "show status" both agree that i have lots of Key_blocks_unused, and instance manager reports 693,785,600 bytes used of 1,048,576,000. I have roughly 2 GB of indexes on disk, and have configured the key_buffer_size to 1000M (1,048,576,000 bytes). No matter *what* happens on the server, it appears that those 300 megs of allocated key cache are never filled.

I was also looking at the status variables over time and noticed Key_blocks_unused decreasing over time (as expected), but not a corresponding increase in Key_blocks_used. The Key_blocks_unused delta looks pretty close to the Key_reads delta (which makes sense, IE mysql has cached those blocks that were just read). Key_blocks_used stays constant over the interval however.

So basically i have what appears to be

1) a key cache not filling up as it should (or i'm reading it wrong)
2) inconsistent status variables (or i'm reading them wrong)

Tables range from 10 - 10,000,000 rows, all are MyISAM.
No special index types are used (just btree unique and non-unique), 1-10 indexes per table.

(see below)
Key_blocks_unused = 182286 - 182304 = -18
Key_blocks_used = 677525 - 677525 = 0
Key_reads = 2339350 - 2339330 = 20
Key_writes = 1044666 - 1044666 = 0

Confirmed same behaviour with both:
Mysql version 5.0.24a RHEL4 x64.
Mysql version 5.1.12-beta Windows Server 2003 x64.

Though i remember seeing this same issue with earlier 5.0.X versions as well.

Below is output from "show variables" and 2 "show status" commands roughly 15 seconds apart.

mysql> show variables like '%key%';
+--------------------------+----------------------+
| Variable_name | Value |
+--------------------------+----------------------+
| delay_key_write | ON |
| have_rtree_keys | YES |
| key_buffer_size | 1048576000 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_seeks_for_key | 18446744073709551615 |
| ssl_key | |
+--------------------------+----------------------+
8 rows in set (0.00 sec)

mysql> show status like '%key%';
+------------------------+------------+
| Variable_name | Value |
+------------------------+------------+
| Com_preload_keys | 0 |
| Com_show_keys | 0 |
| Handler_read_key | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 182304 |
| Key_blocks_used | 677525 |
| Key_read_requests | 1590683325 |
| Key_reads | 2339330 |
| Key_write_requests | 37880416 |
| Key_writes | 1044666 |
+------------------------+------------+
10 rows in set (0.00 sec)

(wait 15 seconds)

mysql> show status like '%key%';
+------------------------+------------+
| Variable_name | Value |
+------------------------+------------+
| Com_preload_keys | 0 |
| Com_show_keys | 0 |
| Handler_read_key | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 182286 |
| Key_blocks_used | 677525 |
| Key_read_requests | 1592651840 |
| Key_reads | 2339350 |
| Key_write_requests | 37880418 |
| Key_writes | 1044666 |
+------------------------+------------+
10 rows in set (0.00 sec)

How to repeat:
Have not isolated the case.

1000M key cache, but always stays @ 857,013,248

Attachment: mysqladmin.jpg (image/jpeg, text), 111.43 KiB.

MySQL v5.0.27 - Sun OS 5.10 -- 6 different MySQL DB servers.
I'm experiencing the same issue.

I've got a single 4g Key-buffer... It never gets used above 3.2g.

Using the the 'MySQL Admin' equation of: 
    [Key_blocks_used]*[key_cache_block_size]
Never gets above 80% of the total available: [key_buffer_size]

All reporters:

Have you ever got similar results on 32-bit platfroms? Any exact steps to repeat are also welcomed.

Sorry, all our use has been with 64 bit platforms (32 bit limitations too low for us).  I will try to create a reproducible case for testing, currently this is behaviour we only are able to test in production.

Please see the below log to reproduce.  After the two queries to fill up the cache (index sizes are 286.4 MB according to Mysql administrator), again we see only 85% (83323*1024 / 104857600) being filled.  The real question is why "Key_blocks_unused" is 83323 after a fresh restart, when the "key_buffer_size" is set in the config to 100M (104857600 bytes). IE it doesn't seem to be bad accounting, but rather something odd in the # of key blocks available.

---------------------------------------------------------------------------------

mysql> create table testtest(c1 int not null,c2 varchar(255)) engine=myisam;
Query OK, 0 rows affected (0.17 sec)

mysql> create unique index testtest_idx1 on testtest(c1);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create index testtest_idx2 on testtest(c2(50));
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> \d $

mysql> CREATE PROCEDURE testfill(v1 INT)
    -> BEGIN
    ->   WHILE v1 > 0 DO
    ->     insert into testtest(c1,c2) values(v1,v1);
    ->
    ->     SET v1 = v1 - 1;
    ->   END WHILE;
    -> END$
Query OK, 0 rows affected (0.00 sec)

mysql> \d ;

mysql> call testfill(10000000);
Query OK, 1 row affected (6 min 23.80 sec)

mysql> select sum(c1) from testtest;
+----------------+
| sum(c1)        |
+----------------+
| 50000005000000 |
+----------------+
1 row in set (3.36 sec)

mysql> select sum(c2) from testtest;
+----------------+
| sum(c2)        |
+----------------+
| 50000005000000 |
+----------------+
1 row in set (2.41 sec)

mysql> show variables like '%key%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| delay_key_write          | ON         |
| have_rtree_keys          | YES        |
| key_buffer_size          | 104857600  |
| key_cache_age_threshold  | 300        |
| key_cache_block_size     | 1024       |
| key_cache_division_limit | 100        |
| max_seeks_for_key        | 4294967295 |
| ssl_key                  |            |
+--------------------------+------------+
8 rows in set (0.00 sec)

mysql> show status like '%key%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| Com_preload_keys       | 0        |
| Com_show_keys          | 0        |
| Handler_read_key       | 0        |
| Key_blocks_not_flushed | 0        |
| Key_blocks_unused      | 0        |
| Key_blocks_used        | 83323    |
| Key_read_requests      | 90554417 |
| Key_reads              | 822403   |
| Key_write_requests     | 22211717 |
| Key_writes             | 22211717 |
+------------------------+----------+
10 rows in set (0.00 sec)

--- RESTART MYSQL --- 
--- RESTART MYSQL --- 

mysql> show status like '%key%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_preload_keys       | 0     |
| Com_show_keys          | 0     |
| Handler_read_key       | 0     |
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 83323 |
| Key_blocks_used        | 0     |
| Key_read_requests      | 0     |
| Key_reads              | 0     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
+------------------------+-------+
10 rows in set (0.00 sec)

mysql> select sum(c1) from testtest;
+----------------+
| sum(c1)        |
+----------------+
| 50000005000000 |
+----------------+
1 row in set (3.36 sec)

mysql> select sum(c2) from testtest;
+----------------+
| sum(c2)        |
+----------------+
| 50000005000000 |
+----------------+
1 row in set (2.41 sec)

mysql> show status like '%key%';
+------------------------+---------+
| Variable_name          | Value   |
+------------------------+---------+
| Com_preload_keys       | 0       |
| Com_show_keys          | 0       |
| Handler_read_key       | 0       |
| Key_blocks_not_flushed | 0       |
| Key_blocks_unused      | 0       |
| Key_blocks_used        | 83323   |
| Key_read_requests      | 1031658 |
| Key_reads              | 148378  |
| Key_write_requests     | 0       |
| Key_writes             | 0       |
+------------------------+---------+
10 rows in set (0.01 sec)

mysql>

PS The specific version tested  below was MySQL 5.1.12-beta 64 bit on Windows Server 2003 x64.

Look:

mysql> show status like 'Key_blocks%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 2229  |
| Key_blocks_used        | 5019  |
+------------------------+-------+
3 rows in set (0.01 sec)

mysql> insert into tmi(c2) select c2 from tmi;
Query OK, 262144 rows affected (1 min 23.65 sec)
Records: 262144  Duplicates: 0  Warnings: 0

mysql> show status like 'Key_blocks%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 0     |
| Key_blocks_used        | 7248  |
+------------------------+-------+
3 rows in set (0.00 sec)

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.36    |
+-----------+
1 row in set (0.00 sec)

So, I surely can make key buffer 100% used. How? It is easy:

create table tmi(c1 int auto_increment primary key, c2 char(255), key k1
(c2)) engine=MyISAM;
insert into tmi c2 values(repeat('a', 255));
insert into tmi(c2) select c2 from tmi;
...

Repeat that last statement many times, and you should get it eventually (note that you may need free disk space large enough in case of really big key buffer).

In my case it was 5.0.36-BK on 32-bit SuSE Linux. So, please, try to repeat my test above with your versions and platforms and inform about the results (will you get 100% used eventually or not).

Windows server 2003 x64 again:

I no longer believe this is a bug in filling up the key buffer. I believe instead this is a bug in MySQL's calculation of available key_blocks from the specified key_buffer_size.

The invariant should be, a i understand things:
(key_blocks_unused + key_blocks_used) = (key_buffer_size / key_cache_block_size)

However, this is definitely not the case per the below directly after startup. I would expect key_blocks_unused = (104857600/1024) = 102400 (instead of 83323)
I think this is the discrepency?

mysql> show variables like '%key%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| delay_key_write          | ON         |
| have_rtree_keys          | YES        |
| key_buffer_size          | 104857600  |
| key_cache_age_threshold  | 300        |
| key_cache_block_size     | 1024       |
| key_cache_division_limit | 100        |
| max_seeks_for_key        | 4294967295 |
| ssl_key                  |            |
+--------------------------+------------+
mysql> show status like '%key%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Com_preload_keys       | 0     |
| Com_show_keys          | 0     |
| Handler_read_key       | 0     |
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 83323 |
| Key_blocks_used        | 0     |
| Key_read_requests      | 0     |
| Key_reads              | 0     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
+------------------------+-------+

Why do you think that there is no internal structures/overhead in key buffer? Any URL to the manual or other docs that proves your "invariant" idea?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

What about this formula:

1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)

according to http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html ? 

I cannot say it is stated *explicity*, but i think it is reasonable to assume that if i request 1 GiB of cache by setting key_buffer_size, that i will get 1 Gib of cache, not 850MiB?

Are you saying that the key buffer has 15% memory overhead? That is definitely not documented and i think seems quite large.

I am experiencing similar mysqld behavior with version 4.1.20-max.  Symptoms:

MyISAM tables.
Run query - takes a long time to execute.
Immediately re-run query - finishes immediately.
Wait a few minutes, re-run query - takes longer, though not as long as first
From "show status":
Key_blocks_used/(Key_blocks_used + Key_blocks_unused) = 22%.

I experience the same behaviour with 5.0.34.
I have key_buffer_size=402653184, 
freaquent key misses:
Key_blocks_not_flushed          0
Key_blocks_used                 323359
Key_read_requests               221746211
Key_reads                       2368524
Key_write_requests              2089667
Key_writes                      733107
key_cache_block_size=1024

But Key_blocks_used never gets over 323359, that is 331119616 bytes.
Why doesn't it reach key_buffer_size?

I do not think that we have a bug here. MyISAM key buffer can be used 100% (as it was proved), and there are many potential reasons for it to NOT be used 100% usually.

Please, send the exact repeatable test case if you still think there is a problem with recent MySQL 5.0.75 or 5.0.67.

There isn't a bug here, but there is misleading documentation, and a missing status variable that would make the whole thing clear. The manual when giving the formula quoted in a previous comment says:

"This value is an approximation because some space in the key buffer may be allocated internally for administrative structures."

There is no "may" about it. There is always overhead in the key buffer for control structures but the formula is extremely complex and not calculable without knowing how big the compiler made certain data structures. It depends on the block size, the bytes required for a pointer (different 32/64bit) and the bytes the compiler allocates for an enumerated type (1-4). If you have a 512 byte block size, the overhead is usually over 20% of the buffer. If you have a 16384 byte block size, it is usually around 0.8%. However, a large block size is bad for other reasons as you may be loading keys that are never queried.

The missing status variable that would allow everything to become clear is 'key_blocks_total' which would show how many blocks are available in the buffer after control structures are allocated. This is essentially what the value of 'key_blocks_unused' was at the point the buffer was (re)created.

Example: If you create a 1Gb key buffer on 32-bit Linux with a 6144 block size, and check 'key_blocks_unused' before running any queries, you will find it is around 170,968 which equates to 1001Mb. Therefore, 23Mb is lost to control structures. If you use the default 1024 block size, you lose 118Mb. Two important things to note about the overhead:

a) It is a fixed amount - it doesn't change as the buffer fills

b) It is all allocated immediately. Therefore, if you have a 1024 byte block size, and allocate a 1Gb key buffer that you don't use, you will be wasting 128Mb of RAM.

If this new status value were added, the accurate measures of current and peak buffer usage would be:

current%=(key_blocks_total-key_blocks_unused)/key_blocks_total

peak%=key_blocks_used/key_blocks_total

and the memory usage would be:

current bytes=((key_blocks_total-key_blocks_unused)/key_blocks_total)*key_cache_block_size
+(key_buffer_size-key_blocks_total*key_cache_block_size)

peak bytes=key_blocks_used*key_cache_block_size
+(key_buffer_size-key_blocks_total*key_cache_block_size)

So, we have a documentation request and/or a feature request here.

I suppose both would be in order (both the variable and clearer documentation). However, i do think that 20% overhead on a 512 byte block is quite excessive (100 bytes!) This seems like quite a bit to me though i'm not too familiar with mysql internals to know the reason. Improving this would benefit all users of heavily RAM cacheable databases though so would be quite worthwhile.

I am making some changes to update the manual:

This value is an approximation because some space in the key buffer
is allocated internally for administrative structures. Factors that
influence the amount of overhead for these structures include block
size and pointer size. As block size increases, the percentage of the
key buffer lost to overhead tends to decrease. Larger blocks results
in a smaller number of read operations (because more keys are
obtained per read), but conversely an increase in reads of keys that
are not examined (if not all keys in a block are relevant to a
query).

In addition, I am changing the category of the bug report to
Server: Options and Variables and changing it to a feature request
so that the request for a new status variable can be considered.

Bug #55607 was marked as duplicate of this one.