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

[25 Jun 2004 13:11] jocelyn fournier
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).
[29 Jun 2004 6:41] Matthew Lord
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.
[29 Jun 2004 9:23] jocelyn fournier
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
[19 Sep 2004 12:37] Timour Katchaounov
Scrip attempting (unsuccessfully) to reproduce the bug.

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

[19 Sep 2004 13:19] Timour Katchaounov
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?
[19 Sep 2004 19:30] jocelyn fournier
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
[20 Sep 2004 10:25] Timour Katchaounov
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.
[20 Sep 2004 17:34] jocelyn fournier
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
[4 Jan 2005 18:06] Ingo Strüwing
Starting review.
[6 Jan 2005 20:16] Ingo Strüwing
I requested a comment change and a discussion for further changes.
[25 Jan 2005 16:16] Paul Dubois
Mentioned in 4.1.10 change notes.