MySQL Bugs: #36143: MEM key buffer advisor needs to allow key_buffer

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

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.

wouldn't the same consideration apply to 64 bit Windows as well as *nix?

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

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))

Committed revision 9116.

Note that I also updated the Description and the Advice.

Verified fixed in advisor build 1.3.0.9137.

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.

In 2.0: Pushed up to revision 214.

Verified fixed in advisor build 2.0.0.7020.

Updated 2.0 changelog accordingly.