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)
ссылка на оригинал статьи http://habrahabr.ru/post/165241/
Добавить комментарий