January 10, 2013 at 10:48

What queries hit QUERY CACHE

  • Tutorial
We all know about what QUERY CACHEcan be very useful if you need to optimize frequently repeated database queries, subject to a low number of DML operations (if you are not at all familiar with this functionality, you can read the translation of official documentation on setting up this database structure here ) When configuring query caching parameters on the server, the question often arises, and what exactly is contained in QUERY CACHE'e at the moment. The search for an answer to this question at one time perplexed me. The only practical answer that I considered absolutely correct at that time was found here . The answer was literally the following:
there is no way to find what queries are now cached

those. this method does not exist. Only after some time I realized that this is not at all the case, and there is a completely legal way to get the contents of this database structure without patching the source codes. This way is to write a small plugin for INFORMATION_SCHEMA.

General query cache information


To simplify it a bit, the data structure QUERY CACHEconsists of:
- a pool of data
- a hash table with a list of "query structures"
- a hash table with a list of "table structures"
- various locks for managing this structure.
It is in view of the presence of the last item (locks) that it is QUERY CACHEoften recommended to be disabled on servers in a multi-core architecture and active DML, since the overhead associated with properly maintaining this cache may exceed its usefulness. In general, the memory area used QUERY CACHEis a complex graph of structures of the typeQuery_cache_block. A large number of links appeared, most likely, in an evolutionary way in the name of optimizing the algorithms for cleaning and adding data. To write a plugin, we need only a couple of "trees". The first partially describes the descendants of the hash table of the "query structures" HASH Query_cache::queries, and the second partially describes the descendants of the hash table of the "query structures" HASH Query_cache::tables. To simplify, imagine the pool data QUERY CACHEin the following form:

The description of this structure, of course, varies slightly from version to version, but the main idea remains unchanged for many versions: a hash table with a list of “query structures” is used to search for queries and retrieve a precalculated result from them, a hash table with a list of “table structures” it is used to delete the data of the changed tables from the query cache (there are many more links between the two described structures, but for the sake of simplicity of description I will omit them, since in this context we are not interested in them).
According to the obtained scheme, the main information of interest to us (marked in red) is contained in the structure
Query_cache_query
struct Query_cache_query
{
  ulonglong limit_found_rows;
...
  inline uchar* query()
...
};

- SQL query and the number of records obtained as a result of its execution

it remains only to pull it out of there.

General information about plugins


As I said: to get this information, we will use the officially provided MySQL framework - INFORMATION_SCHEMA Plugins. The first description of working with the structure QUERY CACHEwas made Roland Boumanon his blog. His article, quite old, unfortunately has not survived to this point. For those who are new to how to write plugins, I recommend reading this post on a hub, from the direct developer of MySQL petropavel or a book MySQL 5.1 Plugin Developmentwritten with the participation of the same author. In this article I will try to pay more attention to the details of the implementation of this particular plugin under MySQL 5.5.29 - the latest version of the server at the moment. The minimum plugin for INFORMATION_SCHEMAshould include:
- ST_FIELD_INFO- a description of the table structure
-mysql_declare_plugin- macro of the descriptor of the plugin library with the type MYSQL_INFORMATION_SCHEMA_PLUGINand name of the resulting table
- the st_mysql_information_schemastructure of the descriptor of the plugin itself
- the plug-in initialization function, which indicates the procedure that fills the resulting table
- and the de-initialization function.
For convenience, we will write a plugin that fills two tables: a list of queries in the cache and the memory they occupy QUERY_CACHE_RESULTS, and a list of database tables by which queries are cached QUERY_CACHE_TABLES. To simplify the code, we exclude the management of session variables from the plugin, pushing predicates and other useful, but not very important features for us that can be added after.

We connect request cache


The QUERY CACHEMySQL server implementation is an instance of the class Query_cache::query_cache. This object is global: its implementation is in sql_cache.cc. This cache is described in the bowels of MySQL, so for the possibility of connecting our plugin to this structure, we will have to compile with the directive
#define MYSQL_SERVER

This macro will make our plugin incompatible with other versions of MySQL, and even more so - even if you try to apply it to the same version, but compiled with other compilation directives, in the best case you will get an error when connecting this plugin, in the worst, the server will crash ABORT'oh when trying to execute it. Even if you put it together with the source, you still need to be extremely careful, since the plugin is executed in the DBMS server memory, and errors of this plugin when working with memory will lead to the collapse of the entire MySQL server. We activated this directive in order to gain access to the notorious hash tables described above, since they are not included in the public section for the class Query_cache, and we will have to inherit it from working with them.
Add a couple of new methods for accessing private class variables
class MySQL_IS_Query_Cache : private Query_cache {
public:
  HASH *get_queries_hash() {
    return &this->queries;
  }
  HASH *get_tables_hash() {
    return &this->tables;
  }
};

- Now we can directly work with these hash tables in our plugin.

The main methods for working with type objects are HASHdescribed in the file hash.h. We need only a method my_hash_element, since the plugin does not support parsing of the structure WHEREand we always create a complete table containing the entire list of queries available at the moment. Algorithms for applying predicates of filtering and others will be left to the server.

QUERY_CACHE_TABLES


This view will display a list of all tables whose query results have been cached; accordingly, it will have only two columns:
schema name and table name
#define MAX_SCHEMA_NAME_LENGTH 127
#define MAX_TABLE_NAME_LENGTH 127
#define COLUMN_SCHEMA_NAME 0
#define COLUMN_TABLE_NAME 1
ST_FIELD_INFO query_cache_table_fields[]=
{
  {"SCHEMA_NAME",    MAX_SCHEMA_NAME_LENGTH,    MYSQL_TYPE_STRING, 0, 0, "Schema Name"},
  {"TABLE_NAME",     MAX_TABLE_NAME_LENGTH,     MYSQL_TYPE_STRING, 0, 0, "Table Name"},
  {0,0, MYSQL_TYPE_STRING, 0, 0, 0}
};

- by default, we’ll make the maximum length of these elements no more than 127 characters, I think it's reasonable


QUERY_CACHE_RESULTS


In the table containing SQL queries as well as information on the result of their execution, we add five columns:
directly SQL, the number of lines in the response, as well as information about the memory used by the response
#define MAX_STATEMENT_TEXT_LENGTH 1024
#define COLUMN_STATEMENT_TEXT 0
#define COLUMN_FOUND_ROWS 1
#define COLUMN_RESULT_BLOCKS_COUNT 2
#define COLUMN_RESULT_BLOCKS_SIZE 3
#define COLUMN_RESULT_BLOCKS_SIZE_USED 4
ST_FIELD_INFO query_cache_result_fields[]=
{
  {"STATEMENT_TEXT",          MAX_STATEMENT_TEXT_LENGTH,MYSQL_TYPE_STRING, 0, 0, "Cached statement text"},
  {"FOUND_ROWS",              21, MYSQL_TYPE_LONGLONG, 0, 0, "Result row count"},
  {"RESULT_BLOCKS_COUNT",     21, MYSQL_TYPE_LONG, 0, 0, "Result Blocks count"},
  {"RESULT_BLOCKS_SIZE",      21, MYSQL_TYPE_LONGLONG, 0, 0,"Result Blocks size"},
  {"RESULT_BLOCKS_SIZE_USED", 21, MYSQL_TYPE_LONGLONG, 0, 0,"Result Blocks used size"},
  {0,0, MYSQL_TYPE_STRING, 0, 0, 0}
};

- the query text is artificially limited to 1024 characters, if necessary you can increase this parameter


Save data


After that, we proceed to the direct filling of the obtained table with data. Wherever we store string values, an encoding is required.
  // character set information to store varchar values
  CHARSET_INFO *cs = system_charset_info;

To access the object Query_cache::query_cacheyou must
set the appropriate lock
  // query_cache defined in sql_cache.h is MySQL Query Cache implementation;
  MySQL_IS_Query_Cache *qc = (MySQL_IS_Query_Cache *)&query_cache;
  HASH *h_queries;
  query_cache.lock();
  h_queries = qc->get_queries_hash();

- h_queries the object we are looking for, having iterated which we can access all requests from QUERY CACHE(in the figure HASH queries)

Access to the data structure HASH tablescan be obtained
the same way
  // query_cache defined in sql_cache.h is MySQL Query Cache implementation;
  MySQL_IS_Query_Cache *qc = (MySQL_IS_Query_Cache *)&query_cache;
  HASH *h_tables;
  query_cache.lock();
  h_tables = qc->get_tables_hash();

Further, HASH tableseverything is simple. Iterate the resulting hash table and
save the elements we need
  for(uint i = 0; i < h_tables->records; i++)
  {
   query_cache_block_hash = my_hash_element(h_tables, i);
   query_cache_block_current = (Query_cache_block*)query_cache_block_hash;
   Query_cache_table* query_cache_table = query_cache_block_current->table();
   // get tables data
   const char *schema_name = (const char*)query_cache_table->db();
   size_t schema_name_length = strlen(schema_name)>MAX_SCHEMA_NAME_LENGTH?MAX_SCHEMA_NAME_LENGTH:strlen(schema_name);
   is_query_cache_tables->field[COLUMN_SCHEMA_NAME]->store((char*)schema_name, schema_name_length, cs);
...
  }

In case of errors during saving, as well as in the case of regular completion of the procedure, do not forget to remove the lock query_cache.unlock(). This lock is global for the entire database instance; when it is installed, all other queries that turn to QUERY CACHEwill wait for your plugin to finish working.
Getting SQL query texts is a little more complicated, and additional manipulations are required. The block obtained as a result of the iteration of the hash table does not contain the information we need. It contains only a link to structures of the type Query_cache_query, upon reaching which we will receive only part of the information we need, namely
query text and number of records in the result set
   // get statement data
   statement_text = (const char*)query_cache_query->query();
   ulonglong found_rows = query_cache_query->found_rows();

Information about the amount of memory used to cache the results of this query is available through the variable resultfor the object query_cache_query. This will be a link to
first memory area
  Query_cache_block *first_result_block = query_cache_query->result();

allocated for storing its result. In order to get information about all the allocated memory sections, you need to go through the linked list, which is cyclic and as a result will lead us to the first block found Query_cache_block. It is also worth considering that the status of the block received as a result of this appeal may be unrelated Query_cache_block::RES_INCOMPLETE.
Crawl result
     // loop all query result blocks for current query
     while(   (result_block= result_block->next) != first_result_block 
	       && result_block->type != Query_cache_block::RES_INCOMPLETE) /* This type of block can be not lincked yet (in multithread environment)*/
     {
       result_blocks_count++;              
       result_blocks_size += result_block->length;
       result_blocks_size_used += result_block->used;
     }

- in fact, can we go to an unconnected block in this way or not, I don’t know, but it's better to play it safe

We collect the server and enjoy the result
install plugin QUERY_CACHE_RESULTS soname 'query_cache_results.so';
install plugin QUERY_CACHE_TABLES soname 'query_cache_tables.so';
show plugins;
+--------------------------+--------+--------------------+------------------------+---------+
| Name                     | Status | Type               | Library                | License |
+--------------------------+--------+--------------------+------------------------+---------+
...
| QUERY_CACHE_RESULTS      | ACTIVE | INFORMATION SCHEMA | query_cache_results.so | GPL     |
| QUERY_CACHE_TABLES       | ACTIVE | INFORMATION SCHEMA | query_cache_tables.so  | GPL     |
+--------------------------+--------+--------------------+------------------------+---------+
24 rows in set (0.00 sec)
select * from pivot limit 10;
...
10 rows in set (0.00 sec)
select * from pivot p1, pivot p2 limit 1000;
...
1000 rows in set (0.00 sec)
select * from information_schema.query_cache_tables;
+-------------+------------+
| SCHEMA_NAME | TABLE_NAME |
+-------------+------------+
| test        | pivot      |
+-------------+------------+
1 row in set (0.00 sec)
select * from information_schema.query_cache_results \G
*************************** 1. row ***************************
         STATEMENT_TEXT: select * from pivot p1, pivot p2 limit 1000
             FOUND_ROWS: 1000
    RESULT_BLOCKS_COUNT: 1
     RESULT_BLOCKS_SIZE: 9992
RESULT_BLOCKS_SIZE_USED: 9985
*************************** 2. row ***************************
         STATEMENT_TEXT: select * from pivot limit 10
             FOUND_ROWS: 10
    RESULT_BLOCKS_COUNT: 1
     RESULT_BLOCKS_SIZE: 512
RESULT_BLOCKS_SIZE_USED: 208
2 rows in set (0.00 sec)


PS since I am a database developer, and writing code in C ++ is not even my side specialty, the described solution can be quite crooked and non-optimal, for this reason I will be happy to hear your comments on the code in PM.

PPS For self-assembly and experiments, I post the source code of the resulting plugin on github, on the advice of users in the comments: github.com/mcshadow/mysql_query_cache

Only registered users can participate in the survey. Please come in.

Version 5.5.28 introduced a new feature, Audit Plugin. Is it worth describing this functionality and laying out an open source implementation of the MySQL Enterprise Audit plug-in with almost the same functionality?

  • 88.3% Yes - more plugins good and different 121
  • 11.6% No - stop posting code to the MySQL blog, it’s not interesting to anyone here 16
137 users voted. 34 users abstained.
Tags:

Also popular now: