Кеширане в MySQL

7.1 Кеширане от страна на DB

MySQL използва високо мащабируеми алгоритми, когато работи с таблици, така че MySQL може да работи дори с малки количества памет. Естествено, за по-добра производителност ви трябва повече RAM.

За да видите текущите настройки, свържете се с базата данни

#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) or DEMAND (2)
• query_cache_limit- определя максималния размер на резултата, който ще попадне в кеша
• query_cache_sizeда бъде различен от нула. Когато използвате DEMAND, само заявките, които съдържат директивата, ще бъдат кешираниSQL_CACHE;
• query_cache_min_res_unitминималният размер на разпределения блок памет за съхраняване на резултатите от кешираната заявка. MySQL не съхранява кеша в едно голямо парче памет, instead of това разпределя блокове с минимален размер query_cache_min_res_unit(=4KB по подразбиране) при поискване. Последният такъв блок се съкращава до размера на данните и оставащата памет се освобождава.

Ефектът от кеширането е, че когато сървърът получи заявка, той проверява дали хешът на заявката е в кеша. Ако хешът съвпада - сървърът веднага връща резултата - без парсване на заявката, оптимизиране и т.н. overhead - придружен от механизъм за кеширане - разглеждане на кеша, запис на резултата от заявката в кеша и т.н.

И ако имате много малки заявки в кеша, това може да доведе до фрагментация на паметта поради голям брой свободни блокове. А това от своя страна води до изтриване на кеширани записи поради липса на памет. В този случай има смисъл да се намали стойността на query_cache_min_res_unit. Ако повечето от вашите заявки дават големи резултати, тогава увеличаването на тази настройка може да подобри производителността.

Нека се опитаме да оценим ефекта. Разглеждаме How се променят броячите на кеша (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 Текущо състояние на кеша

За наблюдение на кеша на заявките се използва 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- броя на заявките, които са бor премахнати от кеша поради липса на памет;
• 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';

Отново възниква въпросът: How да изберем адекватна стойностquery_cache_size?

Това ще помогне Qcache_lowmem_prunes. Тази променлива съхранява броя на заявките, които са бor премахнати от кеша поради необходимостта от кеширане на нови заявки. Необходимо е да се стремим към такъв размер на кеша, при който Qcache_lowmem_prunesтой ще се увеличи само леко. За да направите това, се препоръчва да сравните разликата в стойностите Qcache_lowmem_prunesна час и броя на заявките, получени от mysql за същия час.

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (обем на трафик за 10 minutesи) * 1,2 "

Това ще кешира заявките за 10 minutesи + ще даде допълнителни 20% памет за фрагментиране на кеша и допълнителен резерв за кеширане

Можете да изчислите броя и средния обем на отговор на заявка, като използвате съответно променливите Bytes_sent

И така query_cache_sizeувеличихме стойностите, след което трябва да обърнете внимание на стойностите Qcache_total_blocksи Qcache_free_blocks. Qcache_queries_in_cacheMySQL съхранява кеша в блокове. За 1 заявка са необходими 2 блока: един за самия текст на заявката, вторият за резултата.

Ако разгледаме tableта от стойносттаQcache%

Общ брой кеш блоковеQcache_total_blocks – 28

Заявка 6 сега е кеширана, което означава, че 6 * 2 = 12 блока са заети

Безплатни блокове Qcache_free_blocks – 10. Колкото по-неактивен Qcache_free_blocks, толкова по-голяма е степента на "фрагментиране" на кеша.

Ако повечето заявки имат малко количество получени данни, тогава си струва да намалите минималния размер на кеш блока query_cache_min_res_unit, който е 4 KB по подразбиране.

Ако повечето заявки връщат много данни, тогава си струва да увеличите размера на кеш блока.

Основното нещо е да се постигне минимална стойност Qcache_free_blocks.

Ако броячът Qcache_not_cachedе голям, можете да опитате да увеличите променливата query_cache_limit- това ще ви позволи да увеличите лимита и да кешира резултатите от заявки, които "не пасват".

Следните конфигурационни променливи са отговорни за използването на кеша на заявките:

• query_cache_size— размерът на кеша на заявките. query_cache_size = 0забранява използването на кеша;
• query_cache_limit- размера на максималната проба, съхранявана в кеша;
• query_cache_wlock_invalidate- определя дали данните ще бъдат взети от кеша, ако tableта, към която принадлежат, е заключена за четене.
=

За да активирате кеширането на mysql заявки, просто добавете следните редове към my.cnf (Раздел [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

И рестартирайте услугата.

7.4 Къде не се използва кеш паметта

Не е кеширано:

• Заявки отSQL_NO_CACHE
• Подготвени запитвания(Prepared statements);
• Заявки, които са подзаявки на външната заявка;
• Заявки в съхранени proceduresи и функции;
• Заявки, които използват функции:

  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 ();

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

Можете да дефрагментирате кеша с командата:

mysql>flush query cache;

Clear - команда:

mysql>flush query cache;

Най-важните

Никога не работете в производство с настройки по подразбиране. Това ще доведе до това, че повечето от ресурсите на сървъра не се използват. Правилната настройка на MySQL може да подобри производителността на базата данни няколко пъти. Това не само ще ускори приложението, но и ще се справи с голямо натоварване.