MySQL Bugs: #20679: SHOW STATUS does not display granular data for multiple key caches

Bug #20679	SHOW STATUS does not display granular data for multiple key caches
Submitted:	24 Jun 2006 20:33
Reporter:	J Rabbit	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S4 (Feature request)
Version:	5.x, 6.x	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
If you create multiple key caches as descibed in section 7.4.6.2 of the manual, no additional lines appear in the output of the SHOW STATUS command to report on the usage statistics of these additional caches - it simply aggregates the totals for all caches into one set of status variables.

This does not provide the DBA with sufficient information to determine the effectiveness of the cache configuration.

How to repeat:
SET GLOBAL test.key_buffer_size=1000000;
CREATE TABLE t1 (c1 int primary key) ENGINE=MYISAM;
CACHE INDEX t1 IN test;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5);
SELECT * FROM t1 ORDER BY c1;
SHOW STATUS;

The status output does not show distinct 'key_%' for the test cache and the default cache.

Suggested fix:
Display additional lines in the output of SHOW STATUS displaying the key cache related values separately for each defined cache.

Thank you for a reasonable feature request.

workaround is to run "mysqladmin debug" which outputs the following:

Key caches:
default
Buffer_size:       8384512
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             0
not flushed:             0
w_requests:              0
writes:                  0
r_requests:              0
reads:                   0

test
Buffer_size:        999424
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:             1
not flushed:             0
w_requests:              5
writes:                  1
r_requests:              6
reads:                   1

just a note, that running "mysqladmin debug" has been notoriously buggy on older versions of 5.0 and 5.1.  try avoid it, especially on high load servers.

MariaDB has an I_S table for this.
http://kb.askmonty.org/v/segmented-key-cache