Займаючись перенесенням хостингу. чимало налопатіл інформації про СУБД MySQL. Заодно вирішив трохи оптимізувати її роботу, включивши підтримку кешування.
Після установки MySQL вже підтримує механізм кешування запитів, однак за замовчуванням він вимкнений. Параметри за замовчуванням наступні:
Розмір кеша дорівнює нулю. Для того, щоб задати кеш розміром 32 МБ, необхідно додати наступний рядок в my.conf (в секцію [mysqld]):
Щоб не перезапускати MySQL-сервер, змінимо також і поточну конфігурацію, виконавши наступний запит з правами суперкористувача:
mysql> set @ [at] global [dot] query _cache_size = 32 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)
Ще один параметр, на який слід звернути увагу - це query_cache_limit - він задає максимальний обсяг результату виконання запиту, який може бути поміщений в кеш.
Перевірити стан кеша можна наступним запитом:
- Qcache_free_memory - обсяг вільної пам'яті, відведеної під кеш.
- Qcache_hits - кількість запитів, відпрацьованих з кешу.
- Qcache_inserts - кількість вставок запитів в кеш.
- Qcache_lowmem_prunes - кількість вивільнень пам'яті через наповненості кеша.
- Qcache_not_cached - кількість запитів, що не підлягають кешуванню.
- Qcache_queries_in_cache - кількість запитів, які перебувають в кеші в даний час.
Розрахувати ефектівность кешування можна за такою формулою: Qcache_hits / (Qcache_inserts + Qcache_not_cached).
Власне пора задатися питанням, як саме працює кеш. Все дуже просто. При кожному запиті типу SELECT обчислюється хеш-сума рядка запиту і шукається в кеші. Якщо знаходиться - повертається рузельтат з кешу, якщо немає - виконується запит, а результат заноситься в кеш (за умови, що результат не більше значення query_cache_limit).
При кожному запиті типу UPDATE, REPLACE, INSERT, DELETE, TRUNCATE або ALTER, видаляються з кеша всі запити, що використовують таблицю, що піддалася оновленню.
Варто також відзначити деякі особливості кешування, а саме:
- Різниця запитів визначається буквально, порівняння чутливо до реєстру. Тому SELECT * FROM news і select * FROM news будуть для кеша двома різними запитами.
- В кеш завжди потрапляє результат виконання запиту цілком, результати виконання підзапитів НЕ кешуються.
- Кеш працює однаково для запитів до таблиць з різними механізмами зберігання.
- Ряд запитів не підлягає кешуванню:
- Запити, що містять одну з недетермінірованних функцій: NOW (). SLEEP (). RAND (). CURTIME (). LAST_INSERT_ID () и.др.
- Запити, що використовують функції або процедури, що зберігаються, визначені користувачем.
- Запити, що використовують значення локальних змінних.
- Запити, які звертаються до баз даних mysql або INFORMATION_SCHEMA.
- Запити типу SELECT. FOR UPDATE, SELECT. IN SHARE MODE. SELECT. INTO OUTFILE. SELECT. INTO DUMPFILE. SELECT * FROM. WHERE autoincrement_col IS NULL.
- Запити, що використовують тимчасові таблиці.
- Запити, що не звертаються до таблиць.
- Запити, які генерують попередження (warnings).
- У разі, якщо користувач має права не на всю таблицю, а тільки на певні колонки таблиці. Це виняток - наслідок того, що кеш запитів один для всіх користувачів, а права доступу засобами кеша перевіряються лише на рівні таблиць.
Якщо необхідно, щоб запит не попадав в кеш, використовується директива SQL_NO_CACHE. яка розміщується відразу після оператора SELECT. Приклад виконання такого запиту:
mysql> SELECT SQL_NO_CACHE username FROM mail_users;