False impression on key buffer usage #573
Description
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?