False impression on key buffer usage

[MichaIng]

MySQLTuner shows the following on my system:

-------- MyISAM Metrics ----------------------------------------------------------------------------
[!!] Key buffer used: 24.2% (31.0K used / 128.0K cache)
[OK] Key buffer size / total MyISAM indexes: 128.0K/0B

This is btw fixed by #572, previously it was showing:

[!!] Key buffer used: 24.2% (31K used / 131K cache)
[OK] Key buffer size / total MyISAM indexes: 128.0K/0B

hence with 1K = 1000B instead of 1K = 1024B, leading to the confusing mismatch of the cache size in the two rows.

However, the bigger problem I have is that actually there are zero MyISAM tables on the whole database server, correctly proven by 0B total MyISAM indexes. The used way to calculate the key buffer usage key_buffer_size - Key_blocks_unused * key_cache_block_size is not precise, as there is space used for structure and other meta data. The stats show that there was never even a single block of this buffer used:

MariaDB [(none)]> show global status like 'key%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 97    |
| Key_blocks_used        | 0     |
| Key_blocks_warm        | 0     |
| Key_read_requests      | 0     |
| Key_reads              | 0     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
+------------------------+-------+

I now thought, the obvious solution is to use Key_blocks_used, probably in relation to the actually available buffer blocks, but the problem is that is gives the "maximum" ever used key buffer blocks, while Key_blocks_unused shows the "currently" unused buffer blocks, so they cannot be compared. Furthermore with this I don't see a way to get the total number of buffer blocks which can be used. Using the buffer size, as shown, isn't precise.

Probably the false impression that my key buffer is used could be omitted by checking first whether Key_blocks_used > 0, else showing also a buffer usage of 0B? While there is in fact space used for structure and meta data, at least users can derive the correct information from this, that the overall buffer is unused and could hence be disabled completely.

Another question about that: Currently if ( $mycalc{'pct_key_buffer_used'} < 90 ), it is shown as bad, else as good. It makes sense to show a too low usage as bad, since then the buffer size can be reduced, but a too high usage isn't good either, is it? E.g. wouldn't it make sense to define a window of e.g. 70 - 95 % as good, while everything outside is considered as bad?

[jmrenouard]

Hi @MichaIng

Do you think MyISAM get a future ?
https://github.com/mysql/mysql-server/tree/8.0/storage/myisam

Last modifications:

• One bug on 13 months
• Changing Header file for property issue

Roadmap for MySQL is clear: Removing MyIsam
Roadmap for Percona & MariaDB: follow Oracle MySQL

Feel free to send PR, it will be a joy to merge them eye closed ;)

Best regards,
Jean-Marie

[MichaIng]

Do you think MyISAM get a future ?

Not sure, at least I do not run any application which uses MyISAM on any of my MariaDB instances, and for internal databases, MariaDB uses Aria engine.

I'll see whether I find time to send a PR. Still not sure how to do best. At least when Key_blocks_used is zero, MySQLTuner should show zero usage as well, even that metadata/structure use some of the buffer. It should then also recommend to set the buffer size to zero. But as long as Key_blocks_used is max used since startup and Key_blocks_unused is currently unused, I see no way to show a precise percentage 🤔.

[jmrenouard]

Hi,

When No MyISAM table are present:

-------- MyISAM Metrics ----------------------------------------------------------------------------
[--] No MyISAM table(s) detected ....

Best regards,
J.M

[MichaIng]

Many thanks. When there are no MyISAM tables present, probably we could check and recommend to set key_buffer_size=0?

[jmrenouard]

@MichaIng Done !

[MichaIng]

Many thanks!