Information Schema QUERY_CACHE_INFO Table

You are viewing an old version of this article. View the current version here.
MariaDB starting with 5.5.31

The QUERY_CACHE_INFO table was introduced asa part of the QUERY_CACHE_INFO plugin in MariaDB 5.5.31.

Description

The table is not a standard Information Schema table, and is a MariaDB extension.

The QUERY_CACHE_INFO table is created by the QUERY_CACHE_INFO plugin, and allows you to see the contents of the query cache. It creates a table in the INFORMATION_SCHEMA database that shows all queries that are in the cache. You must have the PROCESS privilege (see GRANT) to use this table.

It contains the following columns:

ColumnDescription
STATEMENT_SCHEMAschema used when query was included
STATEMENT_TEXTquery text
RESULT_BLOCKS_COUNTnumber of result blocks
RESULT_BLOCKS_SIZEsize of result blocks
RESULT_BLOCKS_SIZE_USEDsize of used blocks

For example:

SELECT * FROM information_schema.query_cache_info;
+------------------+-----------------+---------------------+--------------------+-------------------------+
| STATEMENT_SCHEMA | STATEMENT_TEXT  | RESULT_BLOCKS_COUNT | RESULT_BLOCKS_SIZE | RESULT_BLOCKS_SIZE_USED |
+------------------+-----------------+---------------------+--------------------+-------------------------+
...
| test             | SELECT * FROM a |                   1 |                512 |                     143 |
| test             | select * FROM a |                   1 |                512 |                     143 |
...
+------------------+-----------------+---------------------+--------------------+-------------------------

Statistics and new features

New features are planned (at JIRA) for implementation, including per-query statistics (must set HAVE_QUERY_CACHE_STATS at compile time). For more information, see MDEV-4682 (not revised yet and not included in the MariaDB code).

MDEV-4682 will remove QUERY_CACHE_INFO table, this will be replaced by QUERY_CACHE_QUERIES table with more fields. Fields are:

ColumnDescription
QUERY_CACHE_IDcache entry id (only for qc_info plugin, internally server use a hash table)
STATEMENT_SCHEMAschema used when query was included
STATEMENT_TEXTquery text
RESULT_FOUND_ROWS
QUERY_ROWS
SELECT_ROWS_READ
QUERY_HITSQuery cache hits in this entry
QUERY_HITS_PERIOD_LOWperiod between hits that is faster than executing the query without query cache (a good hit rate have many hits here), removing this query entry will consume more time than returning value from query cache
QUERY_HITS_PERIOD_HIGHperiod between hits that is slower than executing the query without query cache and is 10 times faster than executing the query without query cache, but isn't slow than 1 second, removing this query will not give problems to users but will increase disk i/o - in future version "10 times" should be replaced to number of competitors trying to include the same query +1
QUERY_HITS_PERIOD_OUTLIERSperiod between hits that is higher than is not HIGH or LOW
QUERY_HITS_TOTAL_TIME_UStotal time expend with query cache
QUERY_HITS_MEAN_PERIOD_USmean query cache period between hits
QUERY_HITS_MEAN_PERIOD_LOW_USmean query cache period between LOW hits
QUERY_HITS_MEAN_PERIOD_HIGH_USmean query cache period between HIGH hits
QUERY_INSERT_TIMEtime that query was inserted at query cache
QUERY_LAST_HIT_TIMElast time the query was hit at query cache
SELECT_EXPEND_TIME_USquery expend time
SELECT_LOCK_TIME_USquery lock expend time
TABLES_TYPEinternal use, possible values: NON TRANSACT, NO CACHE, ASK TRANSACT, TRANSACT, "UNKNOWN %u" where %u is a value of query_cache_query->tables_type()
RESULT_LENGTHlength of result
RESULT_BLOCKS_COUNTnumber of result blocks
RESULT_BLOCKS_SIZEsize of result blocks
RESULT_BLOCKS_SIZE_USEDsize of used blocks
FLAGS_CLIENT_LONG_FLAGconnection flag (part of query cache hash)
FLAGS_CLIENT_PROTOCOL_41connection flag (part of query cache hash)
FLAGS_PROTOCOL_TYPE - connection flag (part of query cache hash)
FLAGS_MORE_RESULTS_EXISTS - connection flag (part of query cache hash)
FLAGS_IN_TRANS - connection flag (part of query cache hash)
FLAGS_AUTOCOMMIT - connection flag (part of query cache hash)
FLAGS_PKT_NR - connection flag (part of query cache hash)
FLAGS_CHARACTER_SET_CLIENT - connection flag (part of query cache hash)
FLAGS_CHARACTER_SET_RESULTS - connection flag (part of query cache hash)
FLAGS_COLLATION_CONNECTION - connection flag (part of query cache hash)
FLAGS_LIMIT - connection flag (part of query cache hash)
FLAGS_TIME_ZONE - connection flag (part of query cache hash)
FLAGS_SQL_MODE - connection flag (part of query cache hash)
FLAGS_MAX_SORT_LENGTH - connection flag (part of query cache hash)
FLAGS_GROUP_CONCAT_MAX_LEN - connection flag (part of query cache hash)
FLAGS_DIV_PRECISION_INCREMENT - connection flag (part of query cache hash)
FLAGS_DEFAULT_WEEK_FORMAT - connection flag (part of query cache hash)
FLAGS_LC_TIME_NAMES - connection flag (part of query cache hash)

QUERY_CACHE_QUERIES_TABLES table return all tables used in a query cache entry

ColumnDescription
QUERY_CACHE_IDcache entry id (only for qc_info plugin, internally server use a hash table), use with QUERY_CACHE_QUERIES table
SCHEMAtable database schema
TABLE_NAMEtable name
TABLE_SUFFIXwith partition engine this will display partition name like P#p0

The query cache entry is identified by QUERY_CACHE_ID column at QUERY_CACHE_QUERIES table, you could get all queries from a table using:

SELECT *
FROM information_schema.QUERY_CACHE_QUERIES
WHERE QUERY_CACHE_ID IN (
  SELECT QUERY_CACHE_ID 
  FROM information_schema.QUERY_CACHE_QUERIES_TABLES 
  WHERE `TABLE_NAME`="some_table" AND `SCHEMA`="some_schema"
)

QUERY_CACHE_TABLES table is included and show all tables inside query cache and how many queries it have cached, columns of this table:

ColumnDescription
TABLE_SCHEMAtable database schema
TABLE_NAMEtable name
TABLE_HASHEDinternal use (0/1)
TABLE_TYPEinternal use, possible values: NON_TRANSACT, NO_CACHE, ASK_TRANSACT, TRANSACT, "UNKNOWN %u" where %u is internal value of query_cache_table_entry->table_type)
QUERIES_IN_CACHEnumber of queries in query cache using this table

Selecting queries to cache / not cache

First, this is a best pratices, always check the use of cache at development time and production time, and always check if query cache isn't a problem instead of a cache solution.

Using QUERY_CACHE_QUERIES table including statistics (set HAVE_QUERY_CACHE_STATS at compile time), we can better tune what queries could / could not be cached.

The main idea is, count how many cache hits we have based at period between hits and select execution time.

QC_INFO plugin sum hits in LOW, HIGH and OUTLIER columns

ColumnDescription
LOWtime between one hit and other smaller than query execution
HIGHtime between one hit and other bigger than query execution time, smaller than 10 times execution time and smaller than 1 second - in future version 10 times will be replaced by the number of competitiors trying to include the same query + 1
OUTLIERnot LOW and not HIGH

A higher value of LOW shows that the query is well cached, hits with period between hits lower than execution time, in other words if we lost this query entry we will need more time to execute query again than hitting query cache, in other words we win some I/O with this query cached

A higher value of HIGH show that it's not a 'very bad' query, in other words the query have some hits that save I/O but if we lost this query entry we will not see "many down time" since executing the query isn't time consuming, since time between hits is lower than query execution time

A higher value of OUTLIER show that the query don't have a good hit rate, but we should consider time to execute this query, maybe is ok to cache a query that expend many time to execute.

See also

Comments

Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.