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 5:04	Modified:	5 Jan 18:29
Reporter:	P Eger
Status:	Verified
Category:	Server: Docs	Severity:	S5 (Performance)
Version:	5.0.27, 5.0.24a, 5.1.12	OS:	Linux (RHEL4 x64, Windows x64)
Assigned to:	Paul DuBois	Target Version:
Triage:	D5 (Feature request)

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.