Information Schema QUERY_CACHE_INFO Table
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:
Column | Description |
---|---|
STATEMENT_SCHEMA | schema used when query was included |
STATEMENT_TEXT | query text |
RESULT_BLOCKS_COUNT | number of result blocks |
RESULT_BLOCKS_SIZE | size of result blocks |
RESULT_BLOCKS_SIZE_USED | size 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:
Column | Description |
---|---|
QUERY_CACHE_ID | cache entry id (only for qc_info plugin, internally server use a hash table) |
STATEMENT_SCHEMA | schema used when query was included |
STATEMENT_TEXT | query text |
RESULT_FOUND_ROWS | |
QUERY_ROWS | |
SELECT_ROWS_READ | |
QUERY_HITS | Query cache hits in this entry |
QUERY_HITS_PERIOD_LOW | period 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_HIGH | period 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_OUTLIERS | period between hits that is higher than is not HIGH or LOW |
QUERY_HITS_TOTAL_TIME_US | total time expend with query cache |
QUERY_HITS_MEAN_PERIOD_US | mean query cache period between hits |
QUERY_HITS_MEAN_PERIOD_LOW_US | mean query cache period between LOW hits |
QUERY_HITS_MEAN_PERIOD_HIGH_US | mean query cache period between HIGH hits |
QUERY_INSERT_TIME | time that query was inserted at query cache |
QUERY_LAST_HIT_TIME | last time the query was hit at query cache |
SELECT_EXPEND_TIME_US | query expend time |
SELECT_LOCK_TIME_US | query lock expend time |
TABLES_TYPE | internal use, possible values: NON TRANSACT, NO CACHE, ASK TRANSACT, TRANSACT, "UNKNOWN %u" where %u is a value of query_cache_query->tables_type() |
RESULT_LENGTH | length of result |
RESULT_BLOCKS_COUNT | number of result blocks |
RESULT_BLOCKS_SIZE | size of result blocks |
RESULT_BLOCKS_SIZE_USED | size of used blocks |
FLAGS_CLIENT_LONG_FLAG | connection flag (part of query cache hash) |
FLAGS_CLIENT_PROTOCOL_41 | connection 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
Column | Description |
---|---|
QUERY_CACHE_ID | cache entry id (only for qc_info plugin, internally server use a hash table), use with QUERY_CACHE_QUERIES table |
SCHEMA | table database schema |
TABLE_NAME | table name |
TABLE_SUFFIX | with 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:
Column | Description |
---|---|
TABLE_SCHEMA | table database schema |
TABLE_NAME | table name |
TABLE_HASHED | internal use (0/1) |
TABLE_TYPE | internal 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_CACHE | number 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
Column | Description |
---|---|
LOW | time between one hit and other smaller than query execution |
HIGH | time 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 |
OUTLIER | not 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.