Query Cache Enhancements

This page describes the enhancements for the query cache. At the moment three features are available:

  • Disabling the cache completely
  • Diagnosing contention more easily
  • Ignoring comments

Disabling the cache completely

This feature allows the user to completely disable use of the query cache. When the server is compiled with the query cache enabled, the query cache is locked during use by the query cache mutex. This lock can cause performance to decrease in some situations. By disabling use of the query cache altogether when the server is started, any possibility of locking it is eliminated, and performance may be improved.

The query cache can now be disabled at server startup or in an option file by:

--query_cache_type=0

The default is 1 (query cache enabled).

Note: This variable already exists in standard MySQL, but when setting query_cache_type=0, the query cache mutex will still be in used. Setting query_cache_type=0 in Percona Server ensures that both the cache is disabled and the mutex is not used.

If query caching is off and a user tries to turn it on from within a session, the following error will be reported:

SET GLOBAL query_cache_type=ON;
ERROR 1651(HY000): Query cache is disabled; restart the server with query_cache_type=1 to enable it

Note: This variable is implemented in standard MySQL from version 5.5.0.

Diagnosing contention more easily

This features provides a new thread state - Waiting on query cache mutex. It has always been difficult to spot query cache bottlenecks because these bottlenecks usually happen intermittently and are not directly reported by the server. This new thread state appear in the output of SHOW PROCESSLIST, easing diagnostics.

Imagine that we run three queries simultaneously (each one in a separate thread):

> SELECT number from t where id > 0; > SELECT number from t where id > 0; > SELECT number from t where id > 0;

If we experience query cache contention, the output of SHOW PROCESSLIT will look like this:

> SHOW PROCESSLIST;
Id      User    Host            db      Command Time    State                          Info
2       root    localhost       test    Sleep   2       NULL
3       root    localhost       test    Query   2       Waiting on query cache mutex  SELECT number from t where id > 0;
4       root    localhost       test    Query   1       Waiting on query cache mutex   SELECT number from t where id > 0;
5       root    localhost       test    Query   0       NULL

Ignoring comments

This feature adds an option to make the server ignore comments when checking for a query cache hit. For example, consider these two queries:

/* first query  */ select name from users where users.name like 'Bob%';
/* retry search */ select name from users where users.name like 'Bob%';

By default (option off), the queries are considered different, so the server will execute them both and cache them both.

If the option is enabled, the queries are considered identical, so the server will execute and cache the first one and will serve the second one directly from the query cache.

System Variables

variable query_cache_strip_comments
Command Line:Yes
Config File:Yes
Scope:Global
Dynamic:Yes
Variable Type:Boolean
Default Value:Off

Makes the server ignore comments when checking for a query cache hit.

Percona Server
Call Us
+1-888-316-9775 (USA - Sales)
+1-208-473-2904 (USA - Sales)
+44-208-133-0309 (UK - Sales)
0-800-051-8984 (UK - Sales)
0-800-181-0665 (GER - Sales)
+1-877-862-4316 (Emergency)
+1-855-55TRAIN (Training)
+1-925-271-5054 (Training)

Table Of Contents

Previous topic

Configuration of the Doublewrite Buffer

Next topic

Fast InnoDB Checksum

This Page



© Copyright Percona LLC and/or its affiliates 2009-2013.
Except where otherwise noted, this documentation is licensed under the following license:
CC Attribution-ShareAlike 2.0 Generic
Created using Sphinx 1.1.3.
This documentation is developed in Launchpad as part of the Percona Server source code.
If you spotted innacuracies, errors, don't understood it or you think something is missing or should be improved, please file a bug.
]]>