Как отключить кэширование запросов MySQL

Я пытаюсь провести сравнительный анализ различных запросов в разных столбцах, но MySQL просто не позволит мне это сделать. После первого выполнения запроса я никогда не смогу снова получить такое же время выполнения для этого запроса. Например, если запрос выполняется в 0.062 сек в первый раз, я никогда не смогу получить одинаковое время выполнения для 2-го, 3-го и т.д. Это будет 0 секунд или что-то вроде 0.015.

Я прочитал много сообщений об отключении и очистке кеша запросов MySQL, но ни один из них не помог мне.

вот эта публикация здесь

Независимо от того, что я делаю, MySQL, похоже, настаивает на использовании кэшированных результатов.

Я перезагружаю MySQL Workbench, затем я запускаю;

set global query_cache_type=0;
set global query_cache_size=0;
flush query cache;
reset query cache;

Время выполнения продолжает показывать 0 секунд.

Только переменная сервера, которую я не смог изменить, - "has_query_cache". Его значение "да", и когда я пытаюсь установить его "нет", Workbench говорит, что он доступен только для чтения.

Я также делаю;

set profiling=1;
run my select query
show profile for query 2;

Результат профилирования показывает это:

'starting', '0.000077'
'checking permissions', '0.000007'
'Opening tables', '0.000016'
'init', '0.000035'
'System lock', '0.000009'
'optimizing', '0.000013'
'statistics', '0.000094'
'preparing', '0.000008'
'executing', '0.000002'
'Sending data', '0.000016'
'end', '0.000002'
'query end', '0.000003'
'closing tables', '0.000005'
'freeing items', '0.000139'
'cleaning up', '0.000009'

Если я не ошибаюсь, это показывает, что кеши не используются правильно? Но я вижу 0 сек. для времени выполнения.

Изменить. Я выполняю запрос SELECT с использованием "SQL_NO_CACHE" следующим образом:

SELECT SQL_NO_CACHE col1,now() from mytable where col2="some_value"

(я добавил функцию now(), чтобы помочь предотвратить кэширование запросов)

Edit2: я использую innoDB, MySQL 5.6.10

Может кто-то мне помочь, потому что я не вижу, что здесь происходит.

Спасибо большое!

Ответ 1

Запуск запроса в первый раз делает пул буферов InnoDB заполнен соответствующими блоками соответствующих таблиц.

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

Ответ 2

Это может быть связано с кэшем самих данных, а не с кешем запросов.

Чтобы убедиться, вы можете отключить кеш запросов для одного оператора, добавив SQL_NO_CACHE после инструкции SELECT.

Пример:

SELECT SQL_NO_CACHE field FROM table.

Ответ 3

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

mysql> SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read%';
mysql> ...run a query...
mysql> SHOW SESSION STATUS LIKE 'Innodb_buffer_pool_read%';

Сравните эти значения из отчета и отметьте, сколько они растут:

+---------------------------------------+----------+
| Variable_name                         | Value    |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_requests      | 10327490 |
| Innodb_buffer_pool_reads              | 1133     |
+---------------------------------------+----------+

read_requests - это логические чтения страниц, которые могут считываться со страниц, уже находящихся в пуле буферов. Если это число растет после вашего запроса, но чтение не растет, тогда ваш запрос получил свой результат исключительно из пула буферов.

читает - количество просмотров страницы, которое должно было выйти на диск, и нести расходы на ввод-вывод для копирования страниц в пул буферов. Если это число растет после вашего запроса, ему пришлось загружать данные с диска.

Подробнее о разнице между производительностью доступа к памяти и доступом к диску читайте http://everythingisdata.wordpress.com/2009/10/17/numbers-everyone-should-know/

Ваш комментарий:

"Таблицы открытия" связаны с кешем в памяти таблиц InnoDB, похожим на словарь данных. Таблицы открываются при первом обращении к ним после перезапуска. Указанная запись таблицы в словаре данных в памяти хранится в памяти неограниченно долго, поэтому использование памяти может значительно увеличиться, если у вас тысячи таблиц. В MySQL 5.6 у них есть некоторые настраиваемые переменные, которые помещают ограничение на использование этой памяти и выселяют редко используемые таблицы. Но весь этот механизм отделен от пула буферов, который хранит страницы данных и индексов.

"Статистика" также отделена от пула буферов. InnoDB ведет статистику в памяти о данных и индексах, которые она использует для руководства оптимизатором запросов. Статистика обновляется автоматически при первом доступе к таблице InnoDB при запуске SHOW TABLE STATUS или выполнении определенных запросов к INFORMATION_SCHEMA при запуске ANALYZE TABLE, а также при значительном изменении размера таблицы. InnoDB генерирует эти статистические данные, считывая фиксированное количество страниц из таблицы наугад, и это, вероятно, попадет на диск, если у вас есть пул холодного буфера.

Ответ 4

OK. Так что я был diggin 'в этой проблеме пула innoDB. Спасибо @Quassnoi, @TheVedge за упоминание об этом. Это было очень полезно для следующей работы.

Я пытался найти способ отключить или reset пул innoDB, но мне не удалось найти способ, кроме перезапуска сервера MySQL. (Чтобы перезапустить сервер в окне Windows, вы можете либо использовать services.msc, либо командную строку.)

Я думаю, что все настройки параметров конфигурации и использование "SQL_NO_CACHE" были более чем достаточно, чтобы избежать использования кеша, а также проверки результатов профилирования я не видел ни одной ссылки на кеш (проверьте исходный пост).

Затем я решил проверить профилирование для того же запроса 1-го и 2-го циклов после перезапуска сервера MySQL. Таким образом, я смог бы увидеть, что происходит во время выполнения запроса шаг за шагом.

1-й запуск после перезапуска сервера дал следующие шаги и тайминги:

'starting', '0.000078'
'checking permissions', '0.000005'
'Opening tables', '0.120329'
'init', '0.000107'
'System lock', '0.000012'
'optimizing', '0.000024'
'statistics', '0.065317'
'preparing', '0.000026'
'executing', '0.000003'
'Sending data', '0.000038'
'end', '0.000005'
'query end', '0.000006'
'closing tables', '0.000014'
'freeing items', '0.000463'
'cleaning up', '0.000039'

И это результаты второго запуска:

'starting', '0.000068'
'checking permissions', '0.000006'
'Opening tables', '0.000019'
'init', '0.000046'
'System lock', '0.000007'
'optimizing', '0.000010'
'statistics', '0.000073'
'preparing', '0.000009'
'executing', '0.000002'
'Sending data', '0.000035'
'end', '0.000003'
'query end', '0.000003'
'closing tables', '0.000006'
'freeing items', '0.000181'
'cleaning up', '0.000015'

Как вы видите, существует огромная разница в продолжительности действий "открытия таблиц" и "статистики" между этими двумя исполнениями, и ни одна из команд не ссылается на какой-либо кеш.

Два действия, по-видимому, являются наиболее трудоемкими частями для первого выполнения (я считаю, что это часть создания пула буферов innoDB), и как только пул генерируется, эти два действия не выполняются так сильно, поэтому значительно короче время выполнения запроса после создания пула буферов.

Когда вы суммируете время выполнения отдельных шагов для второго запуска, мы получаем 0.000483 секунды, и я думаю, что workbench MySQL показывает этот результат как 0,000 секунд, что также может заставить вас думать, что второй запуск был результатом использования кеша, но на самом деле похоже, это не так. Это всего лишь проблема точности отображения для продолжительности выполнения запросов.

В результате я понял, что на самом деле мне удалось отключить использование кешей, и это завершение пула буферов innoDB, который так сильно ускоряет второй запрос. Это также означает, что я могу найти запись в 2 миллиона строк за 0,000483 секунды.

Согласны ли вы?

Ответ 5

В вашем .ini/.cfg в разделе [mysqld] добавьте строку have_query_cache=0 # to prevent using resources from default of yes ccyy/mm/dd your initials. С вашими другими изменениями закройте/перезапустите MySQL. Вы не должны наблюдать никакой активности КК в исследованиях профиля.