Bug #36143 MEM key buffer advisor needs to allow key_buffer_size to be greater than 4G
Submitted: 16 Apr 2008 16:24 Modified: 8 Aug 2008 14:47
Reporter: Chris Calender Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Enterprise Monitor: Advisors/Rules Severity:S3 (Non-critical)
Version:All OS:Linux (All except Windows)
Assigned to: Andy Bang CPU Architecture:Any
Tags: advisor, key buffer, Key Buffer Size Greater Than 4 GB, key_buffer, mem

[16 Apr 2008 16:24] Chris Calender
Description:
On Linux 64-bit systems, one can have the key_buffer_size set to sizes larger than 4G.

However, this is not so according to the "Key Buffer Size Greater Than 4 GB" advisor (under "Administration").

It will trigger the following alert:

CRITICAL Alert - Key Buffer Size Greater Than 4 GB

4G used to be the restriction, but since bug #5731 was fixed in 5.0.52, this is no longer applicable for 64-bit non-Windows systems:

http://bugs.mysql.com/bug.php?id=5731

"For key_buffer_size, values larger than 4GB are allowed on 64-bit 
platforms (except Windows, for which large values are truncated to
4GB with a warning)."

How to repeat:
Set key_buffer_size to 5G or higher on a 64-bit linux system.

Have an Agent monitor this instance.

Enable the "Key Buffer Size Greater Than 4 GB" advisor (under "Administration").

You'll see the following alert:

CRITICAL Alert - Key Buffer Size Greater Than 4 GB

Suggested fix:
Perform a more sophisticated check to see if it is a 64-bit system and not Windows, and then evaluate total RAM available in that case.  Otherwise leave it 4G for all 32-bit or Windows systems.
[17 Apr 2008 11:02] Peter Laursen
wouldn't the same consideration apply to 64 bit Windows as well as *nix?
[17 Apr 2008 16:31] Chris Calender
No, 64-bit Windows still has this 4G limitation:

"The maximum allowable setting for key_buffer_size is 4GB on 32-bit platforms. As of MySQL 5.0.52, values larger than 4GB are allowed for 64-bit platforms (except 64-bit Windows, for which large values are truncated to 4GB with a warning)."

http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_key_buff...
[21 May 2008 0:59] Andy Bang
What we really want for the expression is:

If ((key buffer size > 4GB) && (version < 5.0.52 OR it's not a 64-bit system OR it's Windows)) then alert

However, we don't have a reliable way to determine if it's a 64-bit system (the SIGAR stuff doesn't provide this for non-Intel or non-AMD chips, like SPARC), so I've changed the expression to:

%key_buffer_size% / (1024*1024*1024) > THRESHOLD && ((%server.version_numeric% < 50052) || (LOCATE("Windows", %os_vendor_name%) > 0))
[21 May 2008 1:00] Andy Bang
Committed revision 9116.
[29 May 2008 21:03] Andy Bang
Note that I also updated the Description and the Advice.
[11 Jun 2008 4:50] Marcos Palacios
Verified fixed in advisor build 1.3.0.9137.
[23 Jul 2008 9:08] Tony Bedford
Set to "verified" with a "target" of 2.0 as requested.

Also added an entry to the 1.3 changelog:

The rule “Key Buffer Size Greater Than 4 GB” incorrectly triggered the following alert: 

CRITICAL Alert - Key Buffer Size Greater Than 4 GB 

However, on non-Windows systems, a key buffer size greater than 4 GB is supported.
[29 Jul 2008 21:58] Andy Bang
In 2.0: Pushed up to revision 214.
[6 Aug 2008 20:29] Marcos Palacios
Verified fixed in advisor build 2.0.0.7020.
[8 Aug 2008 14:47] Tony Bedford
Updated 2.0 changelog accordingly.