Bug #50365 MyISAM Key Buffer Usage graph allocated calculation wrong
Submitted: 15 Jan 2010 14:00 Modified: 20 Apr 2010 15:37
Reporter: Roger David Nay Email Updates:
Status: Closed Impact on me:
Category:MySQL Enterprise Monitor: Advisors/Rules Severity:S3 (Non-critical)
Version:All OS:Any
Assigned to: Mark Leith CPU Architecture:Any

[15 Jan 2010 14:00] Roger David Nay
The key buffer usage graph 'Allocated' calculation uses Key_blocks_used as though it is indicating how many blocks are currently being used but Key_blocks_used does not indicate how many blocks are currently being used, but the maximum number that have been used.

As a result it can give calculations that a 32MB buffer has allocated more than 32MB.

How to repeat:

Suggested fix:
Key_block_unused seems to indicate the number correctly. Use (key_buffer_size - (Key_block_unused * key_cache_block_size)) to calculate allocated.

Actually I'm not sure what 'Allocated' indicates, does that mean currently in use?

Maybe the graph titles should be [ Total, Maximum Used, Free, In Use ] or something like that.
[15 Jan 2010 14:41] Chen Shujun
I think the 'Allocated' means is (key_cache_block_size*(Key_blocks_unused+Key_blocks_used))/1024/1024.
[15 Jan 2010 14:47] Chen Shujun
The MEM's "MyISAM Key Buffer Usage" chart shows that the Allocated buffer size is more than key_buffer_size.

Attachment: image001.png (image/png, text), 27.58 KiB.

[25 Mar 2010 10:50] Kay Roepke
Refer to http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_key_buffer_size for a proper formula:

"The fraction of the key buffer in use can be determined using key_buffer_size in conjunction with the Key_blocks_unused status variable and the buffer block size, which is available from the key_cache_block_size system variable:

1 - ((Key_blocks_unused × key_cache_block_size) / key_buffer_size)
This value is an approximation because some space in the key buffer is be allocated internally for administrative structures.
[29 Mar 2010 13:18] Enterprise Tools JIRA Robot
Mark Leith writes: 
Pushed to the advisor trunk:

 334 Mark Leith	2010-03-29
     Bug#50365 / EM-3963 MyISAM Key Buffer Usage graph allocated calculation wrong

     Brought the cache graph inline with the InnoDB Buffer Pool one, now:

     o Total Size (key_buffer/1024/1024)
     o Used ((key_buffer - (blk_size*unused))/1024/1024)
     o Modified ((blk_size*not_flushed)/1024/1024)
[31 Mar 2010 19:53] Enterprise Tools JIRA Robot
Keith Russell writes: 
Patch installed in versions =>
[1 Apr 2010 17:30] Enterprise Tools JIRA Robot
Marcos Palacios writes: 
Verified fixed in advisor bundle
[20 Apr 2010 15:37] MC Brown
A note has been added to the 2.2.0 changelog: 

The MyISAM Key Buffer Usage Graph could report misleading information about the key buffer block usage allocation.