Первоначально Написал Арунджит Аравиндан
Кэширование запросов является одной из выдающихся функций в MySQL и важной частью оптимизации запросов. Важно знать, как это работает, поскольку это может привести к значительному повышению производительности или замедлению рабочей нагрузки.
Кеш запросов MySQL является глобальным, разделяемым между сеансами. Он кэширует запрос выбора вместе с результирующим набором, что позволяет идентичным выборам выполняться быстрее, когда данные извлекаются из памяти. Важно, чтобы все было одинаковым, без новых комментариев, пробелов или наиболее существенных различий в предложении WHERE. В основном, когда вы запускаете запрос на выборку, если он доступен в кеше; он получает оттуда или рассматривает запрос как новый и пойдет к парсеру.
Несмотря на то, что он имеет некоторые приятные преимущества, кеш запросов MySQL также имеет свои недостатки. Что ж, давайте подумаем об этом: если вы часто обновляете таблицу, то вы аннулируете кеш запросов для ВСЕХ запросов, кэшированных для этой таблицы. Так что на самом деле, когда у вас есть «часто обновляемая таблица», это означает, что вы, вероятно, не получите какого-либо хорошего использования из кэша запросов. Смотрите пример ниже.
mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031320 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.06 sec) mysql> select * from d.t1; 405 rows in set (0.05 sec) mysql> select * from d.t1 where id=88995159; 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1020600 | | Qcache_hits | 0 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 2 | | Qcache_total_blocks | 6 | +-------------------------+---------+ 8 rows in set (0.00 sec)
Из вышесказанного мы уверены, что запросы кешируются. Давайте попробуем вставить и посмотреть статус, он сделает недействительным кеш запроса и освободит память.
mysql> insert into d.t1 (data)value('Welcome');
Query OK, 1 row affected (0.05 sec)
mysql> SHOW STATUS LIKE "qcache%";
+-------------------------+---------+
| Variable_name | Value |
+-------------------------+---------+
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 1031320 |
| Qcache_hits | 0 |
| Qcache_inserts | 2 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 1 |
+-------------------------+---------+
8 rows in set (0.00 sec)
Теперь давайте подумаем, как определить размер кэша запроса:
В качестве примера: — У меня есть экземпляр mysql с двумя таблицами «t» и «t1». Таблица «t» содержит множество записей, а «t1» — меньшее количество записей. Давайте перезапустим mysql и посмотрим детали кеша запросов.
mysql> show variables like 'query_cache_size'; +------------------+---------+ | Variable_name | Value | +------------------+---------+ | query_cache_size | 1048576 | +------------------+---------+ 1 row in set (0.00 sec) mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1031320 | | Qcache_hits | 0 | | Qcache_inserts | 0 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 0 | | Qcache_total_blocks | 1 | +-------------------------+---------+ 8 rows in set (0.01 sec)
Из приведенного выше статуса обратите внимание на следующие четыре пункта.
1) В Qcache есть около 1 МБ свободного места.
2) Запросы в Qcache равны нулю.
3) Нет показов Qcache.
4) Чернослив Qcache lowmem равен нулю.
mysql> select * from d.t1; 405 rows in set (0.03 sec) mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1021624 | | Qcache_hits | 0 | | Qcache_inserts | 1 | | Qcache_lowmem_prunes | 0 | | Qcache_not_cached | 1 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.01 sec)
Из вышеупомянутого статуса ясно, что запрос был кэширован, и он должен выполняться намного быстрее в следующей попытке и увеличить переменную состояния попаданий Qcache на единицу.
mysql> select * from d.t1; 405 rows in set (0.00 sec). mysql> SHOW STATUS LIKE "%Qcache_hits%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Qcache_hits | 1 | +---------------+-------+ 1 row in set (0.00 sec)
Теперь давайте посмотрим, как данные удаляются из Qcache. Для этого я выполню выборку в таблице «t» с массивными записями.
mysql> select * from d.t where id > 78995159; mysql> SHOW STATUS LIKE "Qcache_lowmem_prunes"; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Qcache_lowmem_prunes | 1 | +----------------------+-------+ 1 row in set (0.00 sec) mysql> select * from d.t1; 405 rows in set (0.02 sec) mysql> SHOW STATUS LIKE "qcache%"; +-------------------------+---------+ | Variable_name | Value | +-------------------------+---------+ | Qcache_free_blocks | 1 | | Qcache_free_memory | 1021624 | | Qcache_hits | 1 | | Qcache_inserts | 2 | | Qcache_lowmem_prunes | 1 | | Qcache_not_cached | 6 | | Qcache_queries_in_cache | 1 | | Qcache_total_blocks | 4 | +-------------------------+---------+ 8 rows in set (0.01 sec)
Qcache_lowmem_prunes — это переменная состояния, которая указывает, сколько раз MySQL должен был очистить / удалить некоторые данные из Qcache, чтобы освободить место для выходных данных других запросов. Нам нужно наблюдать за переменной состояния Qcache_lowmem_prunes и пытаться увеличивать / корректировать размер кэша, пока мы не получим очень низкое соотношение значений для переменной.
It is also undesirable to keep the query cache relatively high value at 256 MB as the Qcache invalidation becomes costly. For details, Peter Zaitsev wrote about this a few years ago in a post that’s still relevant today titled, “Beware large Query_Cache sizes.”
Contention often makes query cache the bottleneck instead of help when you have many CPU cores. Generally, query cache should be off unless proven useful for your workload. So it is important to know your environment well to enable the query cache and to decide what the query cache size should be.
There will also be circumstances where there is no chance of identical selects and in this case it is important to set the query_cache_size and query_cache_type variable to zero. The query_cache_type variable controls the query cache and setting the query_cache_type to zero will reduce the significant overhead in query execution. On a highly concurrent environment there are chances of query cache mutex, which may become the source of a bottleneck. Setting the query_cache_type to zero will avoid the query cache mutex, as the query cache cannot be enabled at runtime which reduces the overhead in query execution. Please go through the details of QUERY CACHE ENHANCEMENTS with Percona Server.