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
• Status: Closed
• 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

Description:
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:
N/A

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 => 2.2.0.1666

[1 Apr 2010 17:30] Enterprise Tools JIRA Robot

Marcos Palacios writes: 
Verified fixed in advisor bundle 2.2.0.1667.

[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.