Bug #21051 RESET QUERY CACHE very slow when query_cache_type=0
Submitted: 13 Jul 2006 23:07 Modified: 31 Aug 2006 19:29
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.22 OS:
Assigned to: Tomash Brechko CPU Architecture:Any

[13 Jul 2006 23:07] Kolbe Kegel
Description:
RESET QUERY CACHE can take a very long time (many minutes) when executed on a large query cache. This in itself is an unfortunate but understandable side effect fo the design of the query cache.

Unfortunately, it also takes an extremely long time to reset the query cache when query_cache_type=0.

This is a problem since other operations against the server are blocked while RESET QUERY CACHE is executing, FLUSH TABLES causes the query cache to the reset, and FLUSH TABLES is used with many backup strategies. This means that a large query cache can increase considerably the time required to take a backup.

How to repeat:
SET GLOBAL query_cache_size=1024*1024*1024;
SET query_cache_type=1;

Load data from ftp://ftp.mysql.com:/pub/mysql/upload/query_cache_reset.data.sql

Execute some of the 778688 queries from ftp://ftp.mysql.com:/pub/mysql/upload/query_cache_reset.queries.sql

RESET QUERY CACHE should take a fairly long time and should block other operations while it is running

Suggested fix:
Ideally, it should be possible to disable the query cache for all current connections so that the query cache can be reset immediately without blocking any other statements.

At the very least, setting query_cache_type=0, and ensuring that no existing connections still have query_cache_type>0, should allow RESET QUERY CACHE to be executed pretty quickly without blocking other operations.
[20 Jul 2006 9:58] Konstantin Osipov
Tomash,
as far as I understood from the description, this feature request calls for the following redesign of RESET QUERY CACHE:
 - it disables the query cache temporarily, unless this is already done
 - the thread that issued RESET performs the reset operation, but this does not
   block other threads, as the query cache is disabled
 - after RESET operation is complete, the query cache status is restored.
This calls for a careful redesign of the query cache locking (querying the status  and modifying contents should become concurrent).
[20 Jul 2006 21:44] Kolbe Kegel
The correct URLs for the above referenced files are:

ftp://ftp.mysql.com:/pub/mysql/download/query_cache_reset.queries.sql.bz2
ftp://ftp.mysql.com:/pub/mysql/download/query_cache_reset.data.sql.bz2
[24 Jul 2006 14:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9501
[31 Jul 2006 21:45] Konstantin Osipov
A review was done by email.
[2 Aug 2006 12:20] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9939

ChangeSet@1.2238, 2006-08-02 16:19:44+04:00, kroki@moonlight.intranet +5 -0
  BUG#21051: RESET QUERY CACHE very slow when query_cache_type=0
  
  There were two problems: RESET QUERY CACHE took a long time to complete
  and other threads were blocked during this time.
  
  The patch does three things:
    1 fixes a bug with improper use of test-lock-test_again technique.
        AKA Double-Checked Locking is not applicable here.
    2 Somewhat improves performance of RESET QUERY CACHE.
        Do my_hash_reset() instead of deleting elements one by one.  Note
        however that the slowdown also happens when inserting into sorted
        list of free blocks, should be rewritten using balanced tree.
    3 Makes RESET QUERY CACHE non-blocking.
        The patch adjusts the locking protocol of the query cache in the
        following way: it introduces a flag flush_in_progress, which is
        set when Query_cache::flush_cache() is in progress.  This call
        sets the flag on enter, and then releases the lock.  Every other
        call is able to acquire the lock, but does nothing if
        flush_in_progress is set (as if the query cache is disabled).
        The only exception is the concurrent calls to
        Query_cache::flush_cache(), that are blocked until the flush is
        over.  When leaving Query_cache::flush_cache(), the lock is
        acquired and the flag is reset, and one thread waiting on
        Query_cache::flush_cache() (if any) is notified that it may
        proceed.
[21 Aug 2006 11:15] Konstantin Osipov
Approved the second patch with a few comments, the review is sent by email.
[22 Aug 2006 7:48] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10706

ChangeSet@1.2238, 2006-08-22 11:47:52+04:00, kroki@moonlight.intranet +5 -0
  BUG#21051: RESET QUERY CACHE very slow when query_cache_type=0
  
  There were two problems: RESET QUERY CACHE took a long time to complete
  and other threads were blocked during this time.
  
  The patch does three things:
    1 fixes a bug with improper use of test-lock-test_again technique.
        AKA Double-Checked Locking is applicable here only in few places.
    2 Somewhat improves performance of RESET QUERY CACHE.
        Do my_hash_reset() instead of deleting elements one by one.  Note
        however that the slowdown also happens when inserting into sorted
        list of free blocks, should be rewritten using balanced tree.
    3 Makes RESET QUERY CACHE non-blocking.
        The patch adjusts the locking protocol of the query cache in the
        following way: it introduces a flag flush_in_progress, which is
        set when Query_cache::flush_cache() is in progress.  This call
        sets the flag on enter, and then releases the lock.  Every other
        call is able to acquire the lock, but does nothing if
        flush_in_progress is set (as if the query cache is disabled).
        The only exception is the concurrent calls to
        Query_cache::flush_cache(), that are blocked until the flush is
        over.  When leaving Query_cache::flush_cache(), the lock is
        acquired and the flag is reset, and one thread waiting on
        Query_cache::flush_cache() (if any) is notified that it may
        proceed.
[22 Aug 2006 14:39] Tomash Brechko
Queued to 5.0-runtime.
[31 Aug 2006 10:23] Konstantin Osipov
Pushed into 5.0.25 and 5.1.12
[31 Aug 2006 19:29] Paul Dubois
Noted in 5.0.25, 5.1.12 changelogs.