Кешування в MySQL

7.1 Кешування на боці БД

MySQL при роботі з таблицями використовує алгоритми, що добре масштабуються, так що MySQL може працювати навіть при малих обсягах пам'яті. Звичайно для кращої продуктивності необхідно більше оперативної пам'яті.

Для перегляду поточних налаштувань підключаємось до бази

#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

По-перше, переконаємось, що кешування включене. Змінна:

• query_cache_typeповинна мати значення ON (1) або DEMAND (2)
• query_cache_limit– визначає максимальний розмір результату, який потрапить у кеш
• query_cache_sizeбути відмінною від нуля. При використанні DEMAND кешуватимуться лише запити, в яких є директиваSQL_CACHE;
• query_cache_min_res_unitмінімальний розмір блоку пам'яті, що виділяється для зберігання результатів кешованого запиту. MySQL не зберігає кеш в одному великому шматку пам'яті, натомість на вимогу виділяються блоки з мінімальним розміром query_cache_min_res_unit(=4KB за замовчуванням). Останній такий блок обрізається до розміру даних, а пам'ять, що залишилася, звільняється.

Ефект кешування в тому, що сервер отримуючи запит дивиться чи є хеш запиту в кеші. Якщо хеш збігається - сервер відразу віддає результат - не розбираючи запит, оптимізацію і т.д. накладні витрати – у супроводі механізму кешування – перегляд кешу, запис результату запиту до кешу тощо.

І якщо у вас багато невеликих запитів у кеші, це може призвести до фрагментації пам'яті через велику кількість вільних блоків. І це, своєю чергою, викликає видалення кешованих записів через нестачу пам'яті. У такому разі має сенс зменшити значення query_cache_min_res_unit. Якщо більшість ваших запитів породжують великий результат, збільшення цього параметра може підвищити продуктивність.

Спробуємо оцінити ефект. Дивимося як змінюються показання лічильників попадань у кеш (Qcahe_hits), кількість запитів оголошених недійсними через брак пам'яті (Qcache_lowmem_prunes), загальна кількість запитів типу SELECT (а лише кешуються вони):

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 Поточний статус кешування

Для моніторингу query cache використовується SHOW STATUS:

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blocksпоказує скільки вільних блоків є в кеші (зменшуватиметься в міру збільшення скешованих запитів);
• Qcache_total_blocks- Кількість зайнятих блоків;
• Qcache_free_memory- Показує вільну «доступну» пам'ять для кешування;
• Qcache_hits— кількість запитів, результати яких було взято з кешу, без реального звернення до бази даних;
• Qcache_inserts- кількість запитів, які були додані до кешу;
• Qcache_lowmem_prunes— кількість запитів, видалених з кешу через брак пам'яті;
• Qcache_not_cached- кількість запитів, які не були записані в кеш через використання функцій роботи з часом тощо;
• Qcache_queries_in_cache— кількість запитів, які знаходяться у кеші.

Можна переглянути загальну кількість запитів SELECT:

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 Ефективність кешування

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Поточне значення можна переглянути так:

SHOW VARIABLES LIKE 'query_cache_size';

Знову ж таки постає питання: як вибрати адекватне значенняquery_cache_size?

У цьому допоможе Qcache_lowmem_prunes. У цій змінній зберігається кількість запитів, які були прибрані з кешу через необхідність кешування нових запитів. Необхідно прагне такого розміру кешу, у якому Qcache_lowmem_prunesлише трохи збільшуватися. Для цього, рекомендується порівняти різницю значень Qcache_lowmem_prunesза годину і кількість запитів, що надійшли на mysql за цей же час.

query_cache_size = (кількість запитів за 10 хвабон)*(середній обсяг відповіді на запит) * 1,2

query_cache_size = (кількість запитів за 10 хвабон)*(середній обсяг відповіді на запит) * 1,2
query_cache_size = (обсяг трафіку за 10 хвабон) * 1,2 »

Це дозволить закешувати запити на 10 хвабон + дати додаткові 20% пам'яті на фрагментацію кешу та додатковий резерв кешування

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

Так значення query_cache_sizeми збільшабо, після чого варто звернути увагу на значення Qcache_total_blocks, Qcache_free_blocksі Qcache_queries_in_cache. MySQL зберігає кеш у блоках. На 1 запит необхідно 2 блоки: один самого тексту запиту, другий для результату.

Якщо розглянути таблицю зі значенняQcache%

Загальна кількість блоків кешуQcache_total_blocks – 28

Закешовано зараз 6 запит, а значить зайнятий 6*2 = 12 блоків

Вільно блоків Qcache_free_blocks – 10. Чим більше незадіяних Qcache_free_blocks, тим більший ступінь "фрагментації" кешу.

Якщо більшість запитів мають невеликий обсяг результуючих даних, варто зменшити мінімальний розмір блоку кеша query_cache_min_res_unit, який за умовчанням дорівнює 4 Кб.

Якщо більшість запитів повертають багато даних – то варто збільшити розмір блоку кеша.

Головне – це досягти мінімального значення Qcache_free_blocks.

Якщо лічильник Qcache_not_cachedвеликий, можна спробувати збільшити змінну query_cache_limit- вона дозволить збільшити ліміт і поміщати в кеш результати запитів, які «не містяться».

За використання кеша запитів відповідають наступні конфігураційні змінні:

• query_cache_size- Розмір кеша запитів. query_cache_size = 0відключає використання кеша;
• query_cache_limit- Розмір максимальної вибірки, що зберігається в кеші;
• query_cache_wlock_invalidate— визначає, чи будуть дані братися з кешу, якщо таблиця, до яких вони відносяться, заблокована на читання.
=

Щоб включити кешування запитів mysql, достатньо додати рядки в my.cnf (Секція [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

І перезапустити сервіс.

7.4 Де кеш не використовується

Не кешуються:

• Запити зSQL_NO_CACHE
• Підготовлені запити(Prepared statements);
• Запити, що є підзапитами зовнішнього запиту;
• Запити всередині процедур і функцій, що зберігаються;
• Запити, в яких використовуються функції:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), , DATABASE (), ENCRYPT ()з одним аргументом, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()без аргументів, USER (),UUID ();

• Запити, що використовують збережені функції, змінні користувача або посилаються на таблиці в системних базах mysql або INFORMATION_SCHEMA;
• • Запити мають такі форми:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Запити з тимчасовими таблицями або таблиці, що зовсім не використовують;
• Запити, що генерують попередження (warnings);

Дефрагментувати кеш можна командою:

mysql>flush query cache;

Очистити – командою:

mysql>flush query cache;

Найважливіше

Ніколи не працюйте на production з налаштуваннями за замовчуванням. Це призведе до невикористання більшості ресурсів сервера. Правильне налаштування MySQL може підвищити продуктивність бази даних у кілька разів. Це дозволить не тільки прискорити роботу програми, а й упоратися з великим навантаженням.