Information Schema QUERY_CACHE_INFO Table

Description

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

The QUERY_CACHE_INFO plugin allows 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. One must have the PROCESS privilege (see GRANT) to use this table.

SELECT statement_schema, statement_text, result_blocks_count, result_blocks_size FROM information_schema.query_cache_info;
+------------------+------------------+---------------------+--------------------+
| statement_schema | statement_text   | result_blocks_count | result_blocks_size |
+------------------+------------------+---------------------+--------------------+
| test             | select * from t1 |                   1 |                512 |
+------------------+------------------+---------------------+--------------------+

Statistics and new features (MDEV-4682)

New features are planned (at JIRA) to be implemented, including a per query statistics (must set HAVE_QUERY_CACHE_STATS at compile time), for more info check MDEV-4682 (not revised yet and not included at MariaDB code).

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

• 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
• 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) <</code>>

QUERY_CACHE_QUERIES_TABLES table return all tables used in a query cache entry

• 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 - table name

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`="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:

• 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

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