Какие запросы попали в QUERY CACHE

Все мы знаем про то, что QUERY CACHE бывает очень полезен, если вам необходимо оптимизировать часто повторяющиеся запросы к базе данных, при условии низкого числа DML операций (если вы совсем не знакомы с данной функциональностью, перевод официальной документации по настройке этой структуры базы данных можно почитать тут). При настройке параметров кэширования запросов на сервере часто встает вопрос, а что же именно содержится в QUERY CACHE'е на данный момент. Поиск ответа на этот вопрос в свое время поставил меня в тупик. Единственный дельный ответ, который я посчитал в то время абсолютно верным, я нашел тут. Ответ гласил буквально следующее:

there is no way to find what queries are now cached

т.е. данного способа не существует. Лишь спустя некоторое время я понял, что это совсем не так, и существует вполне легальный способ получения содержимого данной структуры БД без патчинга исходных кодов. Этот способ — написание небольшого плагина для INFORMATION_SCHEMA.

Общая информация о кэше запросов

Если немного упростить, структура данных QUERY CACHE состоит из:
— pool'а данных
— хэш-таблицы со списком «структур запросов»
— хэш-таблицы со списком «структур таблиц»
— различных блокировок для управления данной структурой.
Именно ввиду наличия последнего пункта (блокировок) QUERY CACHE часто рекомендуют отключать на серверах в многоядерной архитектурой и активным DML, так как накладные расходы связанные с корректным поддержанием этого кэша могут превышать его полезность. В общем виде область памяти используемая QUERY CACHE представляет собой сложно связанный граф структур типа Query_cache_block. Большое количество связей появилось, скорее всего, эволюционным путем во имя оптимизации алгоритмов очистки и добавления данных. Для написания плагина нам потребуется всего пара «деревьев». Первое частично описывает потомков хэш-таблицы «структур запросов» HASH Query_cache::queries, а второе потомков хэш-таблицы «структур таблиц» HASH Query_cache::tables. Для упрощения представим данные pool'а QUERY CACHE в следующем виде:

Описание этой структуры конечно же несколько меняется от версии к версии, но основная идея остается неизменна уже много версий: хэш-таблица со списком «структур запросов» используется для поиска запросов и извлечения прекалькулированного результата по ним, хэш-таблица со списком «структур таблиц» используется для удаления данных изменившихся таблиц из кэша запросов (между двумя описанными структурами есть ещё множество связей, но для простоты описания я их опущу, так как в данном контексте они нам не интересны).
Согласно полученной схеме главная интересующая нас информация (помечена красным) содержится в структуре

Query_cache_query

struct Query_cache_query
{
  ulonglong limit_found_rows;
...
  inline uchar* query()
...
};

— SQL запрос и кол-во записей полученное в результате его выполнения

остается только вытащить её оттуда.

Общая информация о плагинах

Как я уже говорил: для получения этой информации воспользуемся официально предоставляемым MySQL фреймворком — INFORMATION_SCHEMA Plugins. Первое описание работы со структурой QUERY CACHE было произведено Roland Bouman в своем блоге. Его статья, достаточно старая, к данному моменту к сожалению не сохранилась. Для тех кто плохо знаком как писать плагины, рекомендую почитать этот пост на хабре, от непосредственного разработчика MySQL petropavel или же книгу MySQL 5.1 Plugin Development написанную с участием этого же автора. В этой статье я постараюсь больше внимания уделить деталям реализации конкретно данного плагина под MySQL 5.5.29 — последней версии сервера на данный момент. Минимальный плагин для INFORMATION_SCHEMA должен включать:
— ST_FIELD_INFO — описание структуры таблицы
— mysql_declare_plugin — макрос дескриптора библиотеки плагина с типом MYSQL_INFORMATION_SCHEMA_PLUGIN и указанием названия результирующей таблицы
— st_mysql_information_schema — структуру дескриптора непосредственно плагина
— функцию инициализации плагина, в которой указывается процедура заполняющая результирующую таблицу
— и функцию деинициализации.
Для удобства работы мы напишем плагин заполняющий две таблицы: список запросов в кэше и занимаемая ими память QUERY_CACHE_RESULTS, и список таблиц БД, по которым производится кэширование запросов QUERY_CACHE_TABLES. Чтобы упростить код исключим из плагина управление сессионными переменными, проталкивание предикатов и другие полезные, но не очень важные для нас фичи, которые можно будет дописать после.

Подключаем кэш запросов

Реализация QUERY CACHE сервера MySQL представляет собой экземпляр класса Query_cache::query_cache. Этот объект является глобальным: его имплементация находится в sql_cache.cc. Данный кэш описан в недрах MySQL, по этому для возможности подключения нашего плагина к этой структуре компилироваться нам придется с директивой

#define MYSQL_SERVER

Этот макрос сделает наш плагин несовместимым с другими версиями MySQL, и даже более — даже если вы попробуете применить его к такой же версии, но собраной с другими директивами компиляции, то в лучшем случае вы получите ошибку при подключении данного плагина, в худшем же сервер вылетит ABORT'ом при попытке его выполнения. Даже если вы собрали его вместе с исходниками, все равно необходимо быть крайне осторожным, так как плагин выполняется в памяти сервера СУБД, и ошибки этого плагина при работе с памятью будут приводить к краху всего сервера MySQL. Активацию данной директивы мы произвели для того, чтобы получить доступ к пресловутым хэш-таблицам описанным выше, так как они не входят в секцию public для класса Query_cache, и для работы с ними нам придется отнаследоваться.

Добавим пару новых методов доступа к приватным переменным класа

class MySQL_IS_Query_Cache : private Query_cache {
public:

  HASH *get_queries_hash() {
    return &this->queries;
  }

  HASH *get_tables_hash() {
    return &this->tables;
  }
};

— теперь мы можем напрямую работать с данными хэш-таблицами в нашем плагине.

Основные методы для работы с объектами типа HASH описаны в файле hash.h. Нам понадобится лишь метод my_hash_element, так как плагин не поддерживает парсинг конструкции WHERE и мы всегда формируем полную таблицу, содержащую весь список запросов доступный на данный момент. Алгоритмы наложения предикатов фильтрации и др. оставим на откуп серверу.

QUERY_CACHE_TABLES

Данное представление будет отображать список всех таблиц, результаты запросов по которым были закэшированы, соответственно в ней будет всего две колонки:

имя схемы и имя таблицы

#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}
};
 

— по умолчанию сделаем максимальную длину этих элементов не более 127 символов, думаю это разумно

QUERY_CACHE_RESULTS

В таблицу содержащую SQL запросы а так же информацию о результате их выполнения добавим пять колонок:

непосредственно SQL, кол-во строк в ответе, а так же информация об используемой ответом памяти

#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}
};

— текст запроса искусственно ограничим 1024 символами, если необходимо можете увеличить этот параметр

Сохраняем данные

После этого приступим к непосредственному заполнению полученных таблицы данными. Везде где мы сохраняем строковые значения необходимо указывать кодировку.

  // character set information to store varchar values
  CHARSET_INFO *cs = system_charset_info;

Для доступа к объекту Query_cache::query_cache необходимо

установить соответствующую блокировку

  // 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 искомый нами объект, проитерировав который мы сможем получить доступ ко всем запросам из QUERY CACHE (на рисунке HASH queries)

Доступ к структуре данных HASH tables можно получить

аналогичным образом

  // 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();

Далее с HASH tables все просто. Итерируем полученную хэш-таблицу и

сохраняем необходимые нам элементы

  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);
...
  }

В случае возникновения ошибок при сохранении, а так же в случае штатного окончания процедуры не забываем снять блокировку query_cache.unlock(). Эта блокировка — глобальная для всего инстанса базы данных, при её установки все остальные запросы обращающиеся к QUERY CACHE будут ожидать окончания работы вашего плагина.
С получением текстов SQL запросов все немного сложнее, и требуются дополнительные манипуляции. Блок полученный в результате итерации хэш-таблицы не содержит необходимой нам информации. Он содержит лишь ссылку на структуры типа Query_cache_query, добравшись до которой мы получим лишь часть необходимой нам информации, а именно

текст запроса и кол-во записей в результирующем наборе

   // get statement data
   statement_text = (const char*)query_cache_query->query();
   ulonglong found_rows = query_cache_query->found_rows();

Информация об объеме памяти израсходованной на кэширование результатов данного запроса доступна через перем��нную result для объекта query_cache_query. Это будет ссылка на

первую область памяти

  Query_cache_block *first_result_block = query_cache_query->result();

выделенную для хранения его результата. Для того чтобы получить информацию обо всех выделенных участках памяти необходимо пройтись по связанному списку, который является циклическим и в итоге приведет нас к первому найденному блоку Query_cache_block. Так же стоит учитывать, что статус блока полученного в результате этого обращения может быть несвязанным Query_cache_block::RES_INCOMPLETE.

Результат обхода

     // 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;
     }

— на самом деле можем ли мы выйти на несвязанный блок таким способом или нет, я не знаю, но лучше подстраховаться

Собираем сервер и наслаждаемся результатом

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)

P.S. так как я разработчик БД, и написание кода на C++ не является моей даже побочной специальностью, то описанное решение может быть в достаточной степени кривым и неоптимальным, по этой причине я с радостью выслушаю ваши замечания по коду в личку.

P.P.S. Для самостоятельной сборки и экспериментов выкладываю исходники получившегося плагина на github, по совету пользователей в комментариях: github.com/mcshadow/mysql_query_cache