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

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
#search key_buffer

Then I've issuing the following command :

CACHE INDEX searchmainhardwarefr1,searchmainhardwarefr2,searchmainhardwarefr3,searchmainhardwarefr4,
searchconthardwarefr14,searchconthardwarefr15,searchconthardwarefr16,searchconthardwarefr17 IN search;

LOAD INDEX INTO CACHE threadhardwarefr1,threadhardwarefr2,threadhardwarefr3,threadhardwarefr4,

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

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 !

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

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

[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

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

[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

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.

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