MySQL'да кешлаш

Kirish uchun оптимизация query cache, уни нима учун ишлатиш кераклигини тушуниш керак.
Query cache MySQL версиясида эски бўлиб, MySQL 8.0'да ўчирилди.

7.1 ДБ томонида кешлаш

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 кешланган сўров натижаларини сақлаш учун ажратилган минимал хотира блогининг ҳажмини белгилайди. Одатда 4 КБ. MySQL кешни алоҳида катта хотира порциясида сақламайди, балки минимал ҳажмли блоглар ажратилади. Учунчи шундай блог маълумотларнинг ҳажмига келтирилади, қолган хотира бўшатилади.

Кешлаш самараси шуки, сервер сўровни олиши билан уни кешдан олган хэш билан солиштиради. Агар хэш мос келса, сервер натижани дарҳол жўнатади, сўровни таҳлил қилишни, оптимизацияни ва ҳ.к. амалга оширмайди. Йўқотишларнинг ортиши кешни кўриш билан, сўров натижаларини кешга ёзиш ва ҳ.к. амалларга боғлиқдир.

Агар кешда кўп сонли кичик сўровлар бўлса, бу кўп миқдорда бўшатилган блоклар туфайли хотира фрагментациясига олиб келиши мумкин. Бу, ўз навбатида, кешланган ёзувларни олиб ташлашга олиб келиши мумкин. Бундай ҳолатда query_cache_min_res_unit параметрининг қийматини кичрайтириш тавсия этилади. Агар кўпчилик сўровлар катта натижа яратадиган бўлса, бу параметрни ошириш самарадорликни оширади.

Қандай натижа олишимизни кўриб чиқайлик. Кешга тушган сўровлар саноғи (Qcache_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_hits'ни Qcache_hits + Com_select ға бўлиб кўрамиз. Бу аниқликда сўров кешдан олинса Qcache_hits саноғини оширади, ёки сўров кешланмаган бўлса Com_select саноғини оширади. Бу усул O’reilly'нинг «Mysql самарадорлигини оптимизация қилиш» китобида тавсия этилади.

Интернетда бошқа усул мавжуд.

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Агар қиймат 0,8 дан катта бўлса, бу сўровларнинг 80% кешга туширилади, деган маънони англатади. Бу жуда яхши кўрсаткич.

Агар кешга тушиш фоизи паст бўлса, query_cache_size параметрининг қийматини ошириш керак. Жорий қийматни шундай билиш мумкин:

SHOW VARIABLES LIKE 'query_cache_size';

Савол ҳали ҳам долзарб: query_cache_size'га қандай тўғри қиймат танлаш?

Бу борада Qcache_lowmem_prunes ёрдам беради, у кешга янги сўровлар учун юқорилаган сўровлар сонини сақлайди. Кеш ҳажмини шундай аниқлаш керакки, Qcache_lowmem_prunes энг минимал бўлсин. Бунинг учун бир соатдаги Qcache_lowmem_prunes қийматларининг фарқини ва шу соатда MySQL'га келиб тушган сўровлар сонини солиштириш керак.

«Амалийда query_cache_size'ни ҳисоблаш учун икки формуладан бирини ишлатиш мумкин:

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 кешни блокларда сақлайди. Бир сўров учун икки блок керак: бири сўров матни учун, иккинчиси натижа учун.

Qcache% фоизи қийматлари билан жадвалда кешнинг умумий блоклари сони 28 га тенг. 6 сўров кешланган, бу 12 блокни эгаллайди. Бўшатилган блоклар 10 та. Қанчалик кўп фойдаланилмаган блоклар бўлса, кеш фрагментацияси шунча кўп бўлади.

Агар кўпчилик сўровлар кичкина натижа ҳажмга эга бўлса, 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>reset query cache;

Ҳеч қачон ишлаб чиқаришда стандарт қўйилмалар билан ишланг. Бу сервер ресурсларидан унумсиз фойдаланишга олиб келиши мумкин. MySQL'ни тўғри қўйилма билан созлаш маълумотлар базасининг самарадорлигини сезиларли даражада оширишга ёрдам беради. Бу нафақат дастур ишлашини тезлаштиради, балки катта юкламани самарали қайта ишлашга ёрдам беради.