Все мы знаем про то, что 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 в следующем виде:
Описание этой структуры конечно же несколько меняется от версии к версии, но основная идея остается неизменна уже много версий: хэш-таблица со списком «структур запросов» используется для поиска запросов и извлечения прекалькулированного результата по ним, хэш-таблица со списком «структур таблиц» используется для удаления данных изменившихся таблиц из кэша запросов (между двумя описанными структурами есть ещё множество связей, но для простоты описания я их опущу, так как в данном контексте они нам не интересны).
Согласно полученной схеме главная интересующая нас информация (помечена красным) содержится в структуре
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 запросы а так же информацию о результате их выполнения добавим пять колонок:
#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. Для самостоятельной сборки и экспериментов выкладываю полные листинги получившегося плагина:
#ifndef MYSQL_IS_QUERY_CACHE
#define MYSQL_IS_QUERY_CACHE
/*
Copyright (c) 2012, PaynetEasy. All rights reserved.
Author: Mikhail Goryachkin
Licence: GPL
Description: mysql query cache view plugin.
*/
#include <stdlib.h>
#include <ctype.h>
#ifndef MYSQL_SERVER
#define MYSQL_SERVER
#endif
#include <sql_cache.cc>
class MySQL_IS_Query_Cache : private Query_cache {
public:
HASH *get_queries_hash() {
return &this->queries;
}
HASH *get_tables_hash() {
return &this->tables;
}
};
#endif
/*
Original author: Roland Bouman
Licence: GPL
Copyright (c) 2012, PaynetEasy. All rights reserved.
Author: Mikhail Goryachkin
Licence: GPL
Description: mysql query cache view plugin.
*/
#include "mysql_query_cache.h"
#include <mysql/plugin.h>
bool schema_table_store_record(THD *thd,TABLE *table);
#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}
};
static int query_cache_result_fill_table(THD *thd, TABLE_LIST *tables, COND *cond)
{
// character set information to store varchar values
CHARSET_INFO *cs = system_charset_info;
TABLE *is_query_cache_results = (TABLE *)tables->table;
// 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;
const uchar *query_cache_block_hash;
Query_cache_block* query_cache_block_current;
const char *statement_text;
size_t statement_text_length;
query_cache.lock();
h_queries = qc->get_queries_hash();
for(uint i = 0; i < h_queries->records; i++)
{
query_cache_block_hash = my_hash_element(h_queries, i);
query_cache_block_current = (Query_cache_block*)query_cache_block_hash;
Query_cache_query *query_cache_query = query_cache_block_current->query();
// get statement data
statement_text = (const char*)query_cache_query->query();
statement_text_length = strlen(statement_text)>MAX_STATEMENT_TEXT_LENGTH?MAX_STATEMENT_TEXT_LENGTH:strlen(statement_text);
is_query_cache_results->field[COLUMN_STATEMENT_TEXT]->store((char*)statement_text, statement_text_length, cs);
ulonglong found_rows = query_cache_query->found_rows();
is_query_cache_results->field[COLUMN_FOUND_ROWS]->store(found_rows, 0);
// calculate result size
uint result_blocks_count = 0;
ulonglong result_blocks_size = 0;
ulonglong result_blocks_size_used = 0;
Query_cache_block *first_result_block = query_cache_query->result();
if( first_result_block
&& first_result_block->type != Query_cache_block::RES_INCOMPLETE /* This type of block can be not lincked yet (in multithread environment)*/)
{
Query_cache_block *result_block = first_result_block;
result_blocks_count = 1;
result_blocks_size = result_block->length; // length of all block
result_blocks_size_used = result_block->used; // length of data
// 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)
{
result_blocks_count++;
result_blocks_size += result_block->length;
result_blocks_size_used += result_block->used;
}
}
is_query_cache_results->field[COLUMN_RESULT_BLOCKS_COUNT]->store(result_blocks_count, 0);
is_query_cache_results->field[COLUMN_RESULT_BLOCKS_SIZE]->store(result_blocks_size, 0);
is_query_cache_results->field[COLUMN_RESULT_BLOCKS_SIZE_USED]->store(result_blocks_size_used, 0);
if (schema_table_store_record(thd, is_query_cache_results)) {
query_cache.unlock();
return 1;
}
}
query_cache.unlock();
return 0;
}
static int query_cache_result_plugin_init(void *p)
{
ST_SCHEMA_TABLE *schema = (ST_SCHEMA_TABLE *)p;
schema->fields_info = query_cache_result_fields;
schema->fill_table = query_cache_result_fill_table;
return 0;
}
static int query_cache_result_plugin_deinit(void *p)
{
return 0;
}
struct st_mysql_information_schema query_cache_result_plugin =
{
MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION /* interface version */
};
/*
Plugin library descriptor
*/
mysql_declare_plugin(mysql_is_query_cache_result)
{
MYSQL_INFORMATION_SCHEMA_PLUGIN, /* type */
&query_cache_result_plugin, /* descriptor */
"QUERY_CACHE_RESULTS", /* name */
"Roland Bouman aka Mikhail Goryachkin", /* author */
"Lists all query results in the query cache", /* description */
PLUGIN_LICENSE_GPL,
query_cache_result_plugin_init, /* init function (when loaded) */
query_cache_result_plugin_deinit, /* deinit function (when unloaded) */
0x0010, /* version */
NULL, /* status variables */
NULL, /* system variables */
NULL, /* config options */
0, /* flags */
}
mysql_declare_plugin_end;
/*
Copyright (c) 2012, PaynetEasy. All rights reserved.
Author: Mikhail Goryachkin
Licence: GPL
Description: mysql query cache view plugin.
*/
#include "mysql_query_cache.h"
#include <mysql/plugin.h>
bool schema_table_store_record(THD *thd,TABLE *table);
#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}
};
static int query_cache_table_fill_table(THD *thd, TABLE_LIST *tables, COND *cond)
{
// character set information to store varchar values
CHARSET_INFO *cs = system_charset_info;
TABLE *is_query_cache_tables = (TABLE *)tables->table;
// 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;
const uchar *query_cache_block_hash;
Query_cache_block* query_cache_block_current;
query_cache.lock();
h_tables = qc->get_tables_hash();
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);
const char *table_name = (const char*)query_cache_table->table();
size_t table_name_length = strlen(table_name)>MAX_TABLE_NAME_LENGTH?MAX_TABLE_NAME_LENGTH:strlen(table_name);
is_query_cache_tables->field[COLUMN_TABLE_NAME]->store((char*)table_name, table_name_length, cs);
if (schema_table_store_record(thd, is_query_cache_tables)) {
query_cache.unlock();
return 1;
}
}
query_cache.unlock();
return 0;
}
static int query_cache_table_plugin_init(void *p)
{
ST_SCHEMA_TABLE *schema = (ST_SCHEMA_TABLE *)p;
schema->fields_info = query_cache_table_fields;
schema->fill_table = query_cache_table_fill_table;
return 0;
}
static int query_cache_table_plugin_deinit(void *p)
{
return 0;
}
struct st_mysql_information_schema query_cache_table_plugin =
{
MYSQL_INFORMATION_SCHEMA_INTERFACE_VERSION /* interface version */
};
/*
Plugin library descriptor
*/
mysql_declare_plugin(mysql_is_query_cache_table)
{
MYSQL_INFORMATION_SCHEMA_PLUGIN, /* type */
&query_cache_table_plugin, /* descriptor */
"QUERY_CACHE_TABLES", /* name */
"Mikhail Goryachkin", /* author */
"Lists all tables in the query cache", /* description */
PLUGIN_LICENSE_GPL,
query_cache_table_plugin_init, /* init function (when loaded) */
query_cache_table_plugin_deinit, /* deinit function (when unloaded) */
0x0010, /* version */
NULL, /* status variables */
NULL, /* system variables */
NULL, /* config options */
0, /* flags */
}
mysql_declare_plugin_end;
MYSQL_ADD_PLUGIN(query_cache_results mysql_query_cache.h query_cache_results.cc MODULE_ONLY)
MYSQL_ADD_PLUGIN(query_cache_tables mysql_query_cache.h query_cache_tables.cc MODULE_ONLY)
Автор: mcshadow
