Pag-cache sa MySQL

7.1 Pag-cache sa gilid ng DB

Gumagamit ang MySQL ng mga mataas na nasusukat na algorithm kapag nagtatrabaho sa mga talahanayan, kaya ang MySQL ay maaaring tumakbo kahit na may maliit na halaga ng memorya. Naturally, para sa mas mahusay na pagganap, kailangan mo ng mas maraming RAM.

Upang tingnan ang kasalukuyang mga setting, kumonekta sa database

#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)

Una, tiyaking naka-enable ang caching. Variable:

• query_cache_typedapat ON (1) o DEMAND (2)
• query_cache_limit- tinutukoy ang maximum na laki ng resulta na papasok sa cache
• query_cache_sizemaging iba sa zero. Kapag gumagamit ng DEMAND, ang mga kahilingan lamang na naglalaman ng direktiba ang i-cacheSQL_CACHE;
• query_cache_min_res_unitang pinakamababang laki ng inilalaang bloke ng memorya para sa pag-iimbak ng mga resulta ng naka-cache na query. Hindi iniimbak ng MySQL ang cache sa isang malaking tipak ng memorya, sa halip ay naglalaan ito ng mga bloke na may pinakamababang laki query_cache_min_res_unit(=4KB bilang default) kapag hinihiling. Ang huling bloke ay pinutol sa laki ng data, at ang natitirang memorya ay pinalaya.

Ang epekto ng pag-cache ay kapag nakatanggap ang server ng isang kahilingan, tinitingnan nito kung ang hash ng kahilingan ay nasa cache. Kung tumugma ang hash - agad na ibinabalik ng server ang resulta - nang hindi na-parse ang kahilingan, nag-optimize, atbp. overhead - sinamahan ng mekanismo ng pag-cache - pag-browse sa cache, pagsusulat ng resulta ng query sa cache, atbp.

At kung mayroon kang maraming maliliit na kahilingan sa cache, maaari itong humantong sa pagkapira-piraso ng memorya dahil sa isang malaking bilang ng mga libreng bloke. At ito naman, ay nagiging sanhi ng pagtanggal ng mga naka-cache na entry dahil sa kakulangan ng memorya. Sa kasong ito, makatuwirang bawasan ang halaga ng query_cache_min_res_unit. Kung ang karamihan sa iyong mga query ay gumagawa ng malalaking resulta, kung gayon ang pagdaragdag sa setting na ito ay maaaring mapabuti ang pagganap.

Subukan nating suriin ang epekto. Tinitingnan namin kung paano nagbabago ang mga cache hit counter (Qcahe_hits), ang bilang ng mga kahilingang idineklara na hindi wasto dahil sa kakulangan ng memorya (Qcache_lowmem_prunes), ang kabuuang bilang ng mga kahilingan sa uri ng PILI (at ang mga ito lamang ang naka-cache):

#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 Kasalukuyang katayuan ng cache

Upang subaybayan ang query cache ay ginagamit 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_blocksnagpapakita kung gaano karaming mga libreng bloke ang mayroon sa cache (babawasan habang tumataas ang mga naka-cache na kahilingan);
• Qcache_total_blocks- ang bilang ng mga bloke na inookupahan;
• Qcache_free_memory- Ipinapakita ang libreng "magagamit" na memorya para sa pag-cache;
• Qcache_hits- ang bilang ng mga kahilingan, ang mga resulta kung saan kinuha mula sa cache, nang hindi aktwal na na-access ang database;
• Qcache_inserts- ang bilang ng mga kahilingan na idinagdag sa cache;
• Qcache_lowmem_prunes- ang bilang ng mga kahilingan na inalis mula sa cache dahil sa kakulangan ng memorya;
• Qcache_not_cached- ang bilang ng mga kahilingan na hindi naisulat sa cache dahil sa paggamit ng mga function sa pamamahala ng oras, atbp.;
• Qcache_queries_in_cache- ang bilang ng mga kahilingan na nasa cache.

Maaari mong tingnan ang kabuuang bilang ng mga SELECT query:

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

7.3 Kahusayan sa pag-cache

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Ang kasalukuyang halaga ay maaaring tingnan tulad nito:

SHOW VARIABLES LIKE 'query_cache_size';

Muli, ang tanong ay lumitaw: kung paano pumili ng isang sapat na halagaquery_cache_size?

Makakatulong ito Qcache_lowmem_prunes. Iniimbak ng variable na ito ang bilang ng mga kahilingang naalis mula sa cache dahil sa pangangailangang mag-cache ng mga bagong kahilingan. Ito ay kinakailangan upang magsikap para sa tulad ng isang laki ng cache kung saan Qcache_lowmem_prunesito ay tataas lamang nang bahagya. Upang gawin ito, inirerekumenda na ihambing ang pagkakaiba sa mga halaga Qcache_lowmem_prunesbawat oras at ang bilang ng mga kahilingan na natanggap ng mysql sa parehong oras.

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 = (dami ng trapiko sa loob ng 10 minuto) * 1.2 "

Ito ay mag-cache ng mga kahilingan sa loob ng 10 minuto + magbibigay ng karagdagang 20% ​​memory para sa cache fragmentation at karagdagang caching reserve

Maaari mong kalkulahin ang bilang at average na dami ng isang tugon sa isang kahilingan gamit ang mga variable na Bytes_sent , ayon sa pagkakabanggit

At kaya query_cache_sizepinataas namin ang mga halaga, pagkatapos nito ay dapat mong bigyang pansin ang mga halaga Qcache_total_blocks, Qcache_free_blocksat Qcache_queries_in_cache. Ang MySQL ay nag-iimbak ng cache sa mga bloke. Para sa 1 kahilingan, 2 bloke ang kailangan: isa para sa mismong text ng kahilingan, ang pangalawa para sa resulta.

Kung isasaalang-alang natin ang talahanayan mula sa halagaQcache%

Kabuuang bilang ng mga bloke ng cacheQcache_total_blocks – 28

Ang kahilingan 6 ay naka-cache na ngayon, na nangangahulugang 6 * 2 = 12 na bloke ang abala

Libreng mga bloke Qcache_free_blocks – 10. Kung mas idle Qcache_free_blocks, mas malaki ang antas ng "fragmentation" ng cache.

Kung karamihan sa mga query ay may maliit na halaga ng nagreresultang data, sulit na bawasan ang minimum na laki ng block ng cache query_cache_min_res_unit, na 4 KB bilang default.

Kung ang karamihan sa mga kahilingan ay nagbabalik ng maraming data, kung gayon ito ay nagkakahalaga ng pagtaas ng laki ng bloke ng cache.

Ang pangunahing bagay ay upang makamit ang isang minimum na halaga Qcache_free_blocks.

Kung ang counter Qcache_not_cacheday malaki, maaari mong subukang pataasin ang variable query_cache_limit- ito ay magbibigay-daan sa iyo upang taasan ang limitasyon at i-cache ang mga resulta ng mga query na "hindi magkasya".

Ang mga sumusunod na variable ng configuration ay responsable para sa paggamit ng query cache:

• query_cache_size— ang laki ng query cache. query_cache_size = 0hindi pinapagana ang paggamit ng cache;
• query_cache_limit- ang laki ng maximum na sample na nakaimbak sa cache;
• query_cache_wlock_invalidate- tinutukoy kung ang data ay kukunin mula sa cache kung ang talahanayan kung saan sila nabibilang ay naka-lock para sa pagbabasa.
=

Upang paganahin ang mysql query caching, idagdag lamang ang mga sumusunod na linya sa my.cnf (Seksyon [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

At i-restart ang serbisyo.

7.4 Kung saan hindi ginagamit ang cache

Hindi naka-cache:

• Mga kahilingan mula saSQL_NO_CACHE
• Inihanda ang mga tanong(Prepared statements);
• Mga query na mga subquery ng panlabas na query;
• Mga query sa loob ng mga naka-imbak na pamamaraan at pag-andar;
• Mga query na gumagamit ng mga function:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()na may isang argumento, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()walang argumento, USER (),UUID ();

• Mga query gamit ang mga naka-imbak na function, variable ng user o mga reference table sa mysql system database o INFORMATION_SCHEMA;
• • Mga kahilingan sa mga sumusunod na anyo:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Mga query na may mga pansamantalang talahanayan o hindi gumagamit ng mga talahanayan;
• Mga kahilingan sa pagbuo ng mga babala (mga babala);

Maaari mong i-defragment ang cache gamit ang command:

mysql>flush query cache;

Clear - command:

mysql>flush query cache;

Ang pinakamahalagang

Huwag kailanman magtrabaho sa produksyon na may mga default na setting. Magreresulta ito sa karamihan ng mga mapagkukunan ng server na hindi ginagamit. Ang wastong MySQL tuning ay maaaring mapabuti ang pagganap ng database nang maraming beses. Ito ay hindi lamang mapabilis ang aplikasyon, ngunit makayanan din ang isang mabigat na pagkarga.