MySQL Bugs: #60586: key buffer size check gives false positives

Bug #60586	key buffer size check gives false positives
Submitted:	22 Mar 2011 13:00	Modified:	11 Feb 2014 23:19
Reporter:	Daniël van Eeden	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Advisors/Rules	Severity:	S4 (Feature request)
Version:	2.3.1	OS:	Any
Assigned to:	Mark Leith	CPU Architecture:	Any

Description:
The current expression for "Key Buffer Size May Not Be Optimal For System RAM (v 1.6 *)" is this:

(%key_buffer_size% / %ram_total%) * 100 < THRESHOLD

The thresholds are:
Critical Alert = 5
Warning Alert = 15
Info Alert = 25

This dos not take in account the number of MyISAM tables witch is available from information_schema.tables. This also does not take in account the amount of key buffer used.

Not using this check can be a good solution for a InnoDB environment, but not for a mixed environment.

How to repeat:
Install MEM for a InnoDB instance and activate the Key Buffer Size check.

Suggested fix:
Use the status variables from MySQL
mysql> SHOW GLOBAL STATUS LIKE 'Key_%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused      | 6695  |
| Key_blocks_used        | 6698  |
| Key_read_requests      | 4500  |
| Key_reads              | 0     |
| Key_write_requests     | 0     |
| Key_writes             | 0     |
+------------------------+-------+
7 rows in set (0.00 sec)

I could not find them in http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-data-items.html so they are not sampled yet?

Good find: will add a guard to the start of the expression that checks whether key_block_unused is also low

Bug #60991 is for the same rule.

Is 'Patch queued' still the correct status for this bug?

This was resolved in 2.3.6:

The rule Key Buffer Size May Not Be Optimal For System RAM was updated to also check whether the buffer usage has been greater than 75% of system RAM. (Bug #11897242, Bug #60586)

http://dev.mysql.com/doc/mysql-monitor/2.3/en/mem-news-2-3-6.html

Seems the close was missed. Closing now.