* MySQL Query Cache
Публикувано на 14 април 2012 в раздел Бази от Данни.
Използването на „кеш за заявки“ е една добра възможност за повишаване на бързодействието на СУБД. В MySQL функционалността се нарича „query cache“. Идеята е да се записват готови резултати от вече изпълнени SELECT заявки в паметта и така те да се използват наготово. За целта се използва хеш таблица. Правилната настройка на кеша за заявки може да доведе до значително ускорение в бързодействието на системата. За да направим това обаче трябва да разберем как той функционира и съответно какво можем да спечелим и какво можем да загубим от използването му.
Общи настройки, променливи и формули
Четирите най-основни настройки за кеша на заявки са:
- Тип на кеша (query_cache_type): приема стойности 0 (изключен), 1 (постоянно включен) и 2 (работи „до поискване“);
- Размер (query_cache_size): колко памет да бъде отделена за кеша;
- Максимален размер на резултат от заявка (query_cache_limit): какво количество информация да отделяме максимално за всяка една заявка;
- Минимален размер на резултат от заявка (query_cache_min_res_unit): кеширането на прекалено малки резултати от заявки може да покаже по-лоши резултати, отколкото липсата на кеширане въобще.
И четирите променливи се задават чрез конфигурационния файл my.cnf. Може да видите текущите настройки от самата среда чрез следната команда:
mysql> SHOW VARIABLES LIKE 'query%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 67108864 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | | query_prealloc_size | 8192 | +------------------------------+----------+ 7 rows in set (0.00 sec)
Преди да започнем с оптимизацията на кеша е много важно да отбележим изключително важна променлива – query_cache_wlock_invalidate. Ако тя е изключена (както е в примера, а това е и стойността по подразбиране) и използвате MyISAM, то SELECT заявки подадени към кеша ще се изпълнят успешно дори ако таблицата е заключена! Разбира се това в общия случай не е опасно, защото ако вие искате строга защита на данните, то тъй или иначе ще използвате InnoDB и ще се възползвате от трансакции.
Относно оптимизацията на кеша – за нас важна информация (след като системата е работила известно време в работен режим) е как точно се използва той:
mysql> SHOW STATUS LIKE 'Qcache%'; +-------------------------+----------+ | Variable_name | Value | +-------------------------+----------+ | Qcache_free_blocks | 5333 | | Qcache_free_memory | 21925712 | | Qcache_hits | 1334956 | | Qcache_inserts | 390420 | | Qcache_lowmem_prunes | 227914 | | Qcache_not_cached | 64768 | | Qcache_queries_in_cache | 19173 | | Qcache_total_blocks | 44897 | +-------------------------+----------+ 8 rows in set (0.00 sec)
Основните неща, които трябва регулярно да изчисляваме са три:
1. За текущо запълване на кеша използвайте следната формула (заета памет разделено на заделена памет):
((query_cache_size – Qcache_free_memory) / query_cache_size )*100
От нашия пример имаме запълване от 67%. Разбира се, че колкото по-плътно е запълването, толкова по-добре използваме заделената памет.
2. Ефективност на кеша се измерва като „процент от заявките, които се възползват от кеша“. Колкото повече заявки се възползват от кеша, толкова по-добре. Формулата за това идва от отношението между съвпаденията в кеша и общите заявки за търсене в кеша:
(Qcache_hits / (Qcache_hits+Qcache_inserts+Qcache_not_cached) )*100
От показания пример стойността е близо до 75%.
3. Честотата на използване на заявки от кеша се получава като отношението между общите заявки към кеша и броят на вмъкнатите нови резултати от заявки в него, или:
Qcache_hits / Qcache_inserts
В примера честотата е приблизително 3,42. Колкото по-голямо е това число, толкова по-добре.
Какво се кешира?
Първото нещо, което трябва да знаете е, че се кешират само цели заявки. Вложени заявки, части от обединения и изгледи НЕ се кешират. Освен това резултатите в кеша се влияят от малки и главни букви и всякакви минимални разлики в кода на заявката! Например заявките „select * from users“ и „Select * from users“ за query cache се третират като различни. Същото важи за интервали, нови редове, коментари и всякакви други козметични разлики. Ето ви една основателна причина да спазвате стриктна конвенция при писането на SQL заявки.
Друго важно нещо е, че кеша работи с блокове памет. С други думи той чете директно входящия трафик на подадените заявки и започва да търси за съвпадение още с първите получени байтове и не изчаква заявката да бъде получена цяла. Това означава, че кеша работи на ниво още преди заявката да е компилирана, което впрочем обяснява и казаното горе за разликите между малки и главни букви на иначе идентични заявки.
Кешират се само и единствено SELECT заявки (динамични) и подготвени/параметризирани SELECT заявки (след MySQL 5.1). Извикването на съхранени процедури НЕ се кешира. Не се кешират и заявки, които използват локални или глобални променливи. Също така кешът НЯМА да работи ако са използвани функции като RAND(), NOW(), CURDATE(), CURTIME(), FOUND_ROWS() и т.н., т.е. функции които поради външни фактори могат да предизвикат различен резултат при едно и също извикване. Няма да се кешират заявки, които използват временни таблици (temporarily tables) и заявки, които генерират „предупреждения“ (warnings).
При промяна на таблица (изпълнение на insert, update или delete заявка върху нея) се премахват от кеша всички заявки, които зависят от нея! Това означава, че е много неподходящо да използвате кеш ако таблиците, които използвате, много често се обновяват.
Фрагментация
Данните в кеша за заявки се записват в т.нар. „блокове“, които са с променлива дължина. В началото (при празен кеш) има един голям свободен блок. Когато се запишат данни в кеша блока се разделя на две, данните се записват в първия и след като се запишат (кеша за заявки не знае размера на резултатната таблица по време на записа) се освобождава незаетото пространство от текущия блок (т.е. прави се „trim“). Същото се повтаря нататък за останалия свободен блок. Така първоначално кеша ще се запълва равномерно.
С течение на времето обаче някои заявки ще се премахват от кеша, т.е. техните блокове ще бъдат освобождавани. По този начин остават „дупки“ от празни блокове. Това се нарича „фрагментация“ на блоковото пространство на кеша. Постъпващите нови резултати започват да търсят свободно място именно сред тези свободни блокове и започват да ги запълват поред. Когато резултатната таблица от нова заявка е по-голяма като размер от блока, в който иска да се запише, то MySQL ще потърси нов по-голям блок и ще премести текущо записаните данни в него. Това разбира се води до проблеми с производителността. В най-лошия случай блоковото пространство ще е силно фрагментирано и ще се отнемат много ресурси за преместване на информация от един блок в друг.
Един от начините да забележите, че вашето блоково пространство е фрагментирано е да наблюдавате променливата Qcache_lowmem_prunes. Тази променлива показва колко заявки са били „изхвърлени“ от кеша, за да освободят блоково пространство за нови. MySQL премахва заявки от кеша на принципа „най-малко ползвана“. Ако стойността на тази променлива постоянно се увеличава, а в същото време имате много налична памет, то може с основание може да подозирате фрагментация.
Друг важен показател е отношението между Qcache_free_blocks и Qcache_total_blocks. Колкото по-близо е до 1/2 (което е най-лошия възможен случай), толкова по-зле е положението с разпределението на данните в кеша и обратно – колкото по-малко е числото, толкова по-малка фрагментация може да очаквате.
Фрагментацията на блоковото пространство може да се намали чрез оптимизиране на големината на минималната дължина на блок (променливата query_alloc_block_size). Увеличавайки стойността на тази променлива ще намали фрагментацията, но за сметка на това заявки с по-малки резултатни таблици ще изхабяват повече памет, отколкото им е била необходима. Един добър метод за груба настройка е да изчислите средната големина на резултат записан в кеша по следната формула:
(query_cache_size – Qcache_free_memory) / Qcache_queries_in_cache
Ако вашата система е обаче е много разнородна, т.е. често има както големи, така и малки резултатни таблици, то оптимизацията на кеша срещу фрагментация ще е много трудна задача и намирането на оптимални стойности за блокова големина няма да бъдат толкова тривиални за намиране. В такива случай прочетете по-долу в графата „кеш до поискване“.
Дефрагментиране на кеша
Командата FLUSH QUERY CACHE дефрагментира блоковото пространство, като подрежда заетите блокове памет последователно от началото към края на заетата памет. По принцип операцията е бърза, но трябва да знаете, че по този начин целия кеш се „заключва“, а това практически „блокира“ целия сървър – всички входящи връзки влизат в „спящ режим“ докато кеша не се преподреди. Затова в никакъв случай не трябва да се прекалява с употребата на тази команда.
Ако искаме да автоматизираме процеса на дефрагментация, то можем да създадем събитие в системната таблица:
USE mysql CREATE EVENT flush_query_cache ON SCHEDULE EVERY 60 MINUTE STARTS '2012-04-14 07:15:00' DO FLUSH QUERY CACHE;
Разбира се трябва да сте включили изпълнението на събитията. Може да помислите и за по-сложна система за дефрагментация, като следите натоварването на сървъра и извършвате дефрагментацията на кеша и други оптимизационни дейности когато то е достатъчно ниско.
Оптимизиране на кеша спрямо начина за употреба
Пределно ясно за всички е, че колкото по-добре запълнен е един кеш, колкото колкото по-ефективен е и колкото по-голяма е честотата на заявките, толкова по-добре ще бъде употребен. Обратно – при ниска ефективност и малка честота на използване ние губим от наличието на кеш. Причината е, че този кеш също изисква ресурси за поддръжка. Затова е много важно да знаем как да го оптимизираме.
В началото трябва да отбележим, че много хора погрешно увеличават размера на кеша до много големи размери. Категорично трябва да отбележим, че в общия случай по-големия кеш НЕ означава по-добре работеща система. Напротив – ще забележите, че увеличаването на кеша от един момент нататък не помага на бързодействието на системата, а в някои случаи води дори до забавяне.
Преди да направите първите настройки трябва да прецените какъв тип приложения ще работят със системата. Ако например преобладават „малки заявки“ (връщащи малки като обем резултатни таблици), а „големите“ са малко на брой и не се използват често, то може да настроите ниски стойности на query_cache_min_res_unit и query_cache_limit – така ще кеширате презимно „малките“ заявки, а ще отхвърляте „големите“. Обратно – ако приложенията ви ще правят чести заявки с големи резултатни таблици (разбира се повтарящи се такива!), то може да се лишите от кеша за „малките заявки“ като увеличите query_cache_min_res_unit и сложите достатъчно голям query_cache_limit. При „разнородни“ заявки към СУБД, т.е. честия случай, в който една система се използва за много различни приложения и различни бази от данни, то по-скоро направете обхвата широк (по-ниска стойност за query_cache_min_res_unit и по-висока за query_cache_limit) въпреки очаквано по-голямата фрагментация. Накрая ако приложението ви рядко прави еднотипни заявки и повечето заявки към базата от данни връщат различни резултати (напр. многопотребителска система, в която всеки потребител изисква свое собствено съдържание и то с редки потворения), то вероятно ще бъде по-добре ако напълно се лишите от кеша за заявки и го изключите.
Друго нещо, за което може да помислите е да организирате приложението си да прави „отложени промени“. Това означава да изпълнявате delete, update и/или insert заявки на „накуп“ през зададени интервали от време, а не непосредствено след подаването им. По този начин вие съществено ще съкратите изхвърлянето на заявки от кеша и ще подобрите бързодействието им. От друга страна обаче това означава, че е възможно да позволите на потребителите си да прочетат „стара информация“ от кеша. Затова използвайте този метод само при обновявания на таблиците, които не са задължителни „на момента“ за правилната работа на системата. Също така имайте предвид, че по този начин се носи и съответен риск при срив на уеб сървъра да се изгуби информация, която иначе е потвърдена на потребителя, че е записана.
Използване на кеш до поискване
Когато не споделяме СУБД с други потребители, а го използваме и настройваме самостоятелно, то със сигурност можем да спечелим изключително много от използването на „режим на кеш до поискване“. В този режим една заявка ще бъде записана в кеша само тогава, когато ние специално укажем, че това трябва да стане. За целта се добавя специален атрибут „SQL_CACHE“ непосредствено след команда SELECT. Например ако ще се изпълнява заявка за изкарване на най-новите 10 статии на първа страница на даден сайт, то очакваме много потребители да четат тези данни и то множество пъти. Ако кеша е в режим „до поискване“, то можем да укажем, че искаме заявката да попадне в него по следния начин:
SELECT SQL_CACHE id, title, author FROM articles ORDER BY date DESC LIMIT 10;
Обратно – ако не желаем дадена заявка да попада в кеша, то можем да направим следната заявка с параметър SQL_NO_CACHE:
SELECT SQL_NO_CACHE last_login_time FROM users WHERE user = "ivan";
За улеснение може да приемете, че при query_cache_type = 1 всяка заявка ще бъде с атрибут „SQL_CACHE“ по подразбиране, а при query_cache_type = 2 ще бъде с атрибут „SQL_NO_CACHE“ по подразбиране. Виждате, че можете съвсем спокойно да се възползвате от тази тактика независимо в какъв режим се работи.
Използването на тази техникани позвовява да направим изключително ефективна система, която се възползва от кеша за заявки максимално добре. При правилен подбор на това „кои заявки да се кешират и кои не“ ние можем да минимизираме фрагментацията, да увеличим ефективността и да се възползваме максимално добре от заделената памет. Създаването на една такава максимално ефективна система включва в себе си много прецизно планиране на всяко едно действие в нея.
Заключение
Като финал ще направим следното предупреждение, което може да се използва като основно правило за администраторите: лошо работещия кеш за заявки носи много повече вреда за производителността отколкото напълно изключения кеш за заявки. Ако винаги имате това наум и взимате необходимите мерки, то би трябвало вашия сървър да работи добре.
Добави коментар