MySQL Bugs: #21051: RESET QUERY CACHE very slow when query_cache

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

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.

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

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

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

A review was done by email.

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.

Approved the second patch with a few comments, the review is sent by email.

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.

Queued to 5.0-runtime.

Pushed into 5.0.25 and 5.1.12

Noted in 5.0.25, 5.1.12 changelogs.