MySQL Bugs: #4285: multiple key cache doesn't work properly

Bug #4285	multiple key cache doesn't work properly
Submitted:	25 Jun 2004 13:11	Modified:	25 Jan 2005 16:16
Reporter:	jocelyn fournier (Silver Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.3-beta bk tree	OS:	Linux (Linux)
Assigned to:	Timour Katchaounov	CPU Architecture:	Any

Description:
Hi,

I'm using the multiple key cache feature of MySQL-4.1.
I've configured the key cache through the my.cnf :

#standard key_buffer use for all queries excepted search queries
key_buffer=1G
#search key_buffer
search.key_buffer=512M

Then I've issuing the following command :

CACHE INDEX searchmainhardwarefr1,searchmainhardwarefr2,searchmainhardwarefr3,searchmainhardwarefr4,
searchmainhardwarefr5,searchmainhardwarefr6,searchmainhardwarefr8,searchmainhardwarefr9,
searchmainhardwarefr10,searchmainhardwarefr11,searchmainhardwarefr12,searchmainhardwarefr13,
searchmainhardwarefr14,searchmainhardwarefr15,searchmainhardwarefr16,searchmainhardwarefr17,
searchjoinhardwarefr1,searchjoinhardwarefr2,searchjoinhardwarefr3,searchjoinhardwarefr4,
searchjoinhardwarefr5,searchjoinhardwarefr6,searchjoinhardwarefr8,searchjoinhardwarefr9,
searchjoinhardwarefr10,searchjoinhardwarefr11,searchjoinhardwarefr12,searchjoinhardwarefr13,
searchjoinhardwarefr14,searchjoinhardwarefr15,searchjoinhardwarefr16,searchjoinhardwarefr17,
searchconthardwarefr1,searchconthardwarefr2,searchconthardwarefr3,searchconthardwarefr4,
searchconthardwarefr5,searchconthardwarefr6,searchconthardwarefr8,searchconthardwarefr9,
searchconthardwarefr10,searchconthardwarefr11,searchconthardwarefr12,searchconthardwarefr13,
searchconthardwarefr14,searchconthardwarefr15,searchconthardwarefr16,searchconthardwarefr17 IN search;

LOAD INDEX INTO CACHE threadhardwarefr1,threadhardwarefr2,threadhardwarefr3,threadhardwarefr4,
threadhardwarefr5,threadhardwarefr6,threadhardwarefr8,threadhardwarefr9,
threadhardwarefr10,threadhardwarefr11,threadhardwarefr12,threadhardwarefr13,
threadhardwarefr14,threadhardwarefr15,threadhardwarefr16,threadhardwarefr17,
forumconthardwarefr1,forumconthardwarefr2,forumconthardwarefr3,forumconthardwarefr4,
forumconthardwarefr5,forumconthardwarefr6,forumconthardwarefr8,forumconthardwarefr9,
forumconthardwarefr10,forumconthardwarefr11,forumconthardwarefr12,forumconthardwarefr13,
forumconthardwarefr14,forumconthardwarefr15,forumconthardwarefr16,forumconthardwarefr17;

Both commands are executed properly without errors.

However the default key cache doesn't seems to be used at all, since the stats display the following :

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

search
Buffer_size:     536870912
Block_size:           1024
Division_limit:        100
Age_limit:             300
blocks used:        283141
not flushed:             0
w_requests:         683458
writes:             383961
r_requests:       55149677
reads:              283144

As you can see no r_requests or w_requests are reported against the default key cache (whereas the search key cache seems to work properly) ?!
I assume I don't have to use the CACHE INDEX query for the default key cache ?
Am I doing something wrong ?

Thanks !
  Jocelyn

How to repeat:
Create a new key cache, assign specific tables to the new key cache, and preload some tables in the default key cache.

Then the default key_cache will not be used for any of the existing tables (preloaded or not).

I was able to verify this in 4.1.2-alpha and a 4.1.3-bk-6-28-04 build.  I don't know if the problem is 
that the stats are being updated or that the default key_cache really isn't being used.  

The problem only seems to surface and I can only repeat it if I run a cache index foo in test before 
using the default key cache.   If I use the default key cache before the cache index statement  things 
seem to be OK.

Hi,

According to the memory utilisation of my server when this happened, I would say that's not only a problem with the statistic : the key_cache really isn't used :)

  Jocelyn

Scrip attempting (unsuccessfully) to reproduce the bug.

Attachment: 4285.sql (text/x-sql), 1.26 KiB.

I cannot repeat the bug. I attached a script that attempts to follow all the steps in the
bug report, however this script shows that all caches are used as they should be. After
each query I run 'mysqladmin debug' to check the key cache status, and it reports that
the key cache for the corresponding table has been used.

Could you please provide a complete script that reproduces the problem + any additional
steps (that is when to check for the key cache status). It will be best if the problem can be
reproduced with some modifications of the script I attached to this bug report.

Is it possible that no queries were run that access tables with indexes cached in the default
index cache?

Hi,

mysqladmin debug unfortunately doesn't return anything.
Does this need MySQL to be compile in debug mode ?
If so is there any other way to have the statistic of the key buffers (other than sending a SIGHUP signal to MySQL) ?

Thanks,
  Jocelyn

Jocelyn, please read my comment below, and let me know if you can reproduce the
same behavior. Notice that you don't have to compile a debug version of mysqld -
see my comment for 2).

I managed to reproduce the reported output and have some hypothesis.

There are two ways to get cache statistics:
1) Send a SIGHUP to the server.
2) Issue a 'mysqladmin debug' command. The output of this command is printed on
   the standard output, so where it actually goes depends on how one started the server.
   If 'mysqld_safe' was used, then the output is redirected to a file, and if one issues
   'mysqld' directly, then it goes to the console.

Both 1) and 2) call the same function mysql_print_status() via different call paths.
In case 1) the server reports that the default cache has not been used, while in case
2) it reports the correct statistics.

Hi,

Yes it's the info reported after a SIGHUP which seems to be wrong. (even when there is a single key cache, the values stays to 0)
I would add the "handler status" seems to be wrong too, since there are set to 0 too. Opened tables stays also to 0.

Regards,
  Jocelyn

Starting review.

I requested a comment change and a discussion for further changes.

Mentioned in 4.1.10 change notes.