Gyorsítótárazás a MySQL-ben

7.1 Gyorsítótárazás a DB oldalon

A MySQL nagymértékben méretezhető algoritmusokat használ a táblákkal való munka során, így a MySQL még kis mennyiségű memóriával is futhat. Természetesen a jobb teljesítményhez több RAM szükséges.

Az aktuális beállítások megtekintéséhez csatlakozzon az adatbázishoz

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

Először győződjön meg arról, hogy a gyorsítótárazás engedélyezve van. Változó:

• query_cache_typeBE (1) vagy IGÉNY (2) kell legyen
• query_cache_limit- meghatározza a gyorsítótárba kerülő eredmény maximális méretét
• query_cache_sizenullától különbözik. A DEMAND használatakor csak az utasítást tartalmazó kérések kerülnek a gyorsítótárbaSQL_CACHE;
• query_cache_min_res_unita lefoglalt memóriablokk minimális mérete a gyorsítótárazott lekérdezés eredményeinek tárolására. A MySQL nem tárolja a gyorsítótárat egyetlen nagy memóriarészben, ehelyett minimális méretű query_cache_min_res_unit(=alapértelmezés szerint 4 KB) blokkokat foglal le igény szerint. Az utolsó ilyen blokk adatméretre csonkolódik, és a fennmaradó memória felszabadul.

A gyorsítótárazás hatása az, hogy amikor a szerver kérést kap, megnézi, hogy a kérés hash-je a gyorsítótárban van-e. Ha a hash egyezik - a szerver azonnal visszaadja az eredményt - a kérés elemzése, optimalizálása stb. overhead - gyorsítótár-mechanizmus kíséretében - a gyorsítótár böngészése, a lekérdezés eredményének gyorsítótárba írása stb.

És ha sok kis kérés van a gyorsítótárban, akkor ez a memória töredezettségéhez vezethet a nagyszámú szabad blokk miatt. Ez pedig a gyorsítótárazott bejegyzések törlését okozza a memória hiánya miatt. Ebben az esetben érdemes csökkenteni az értékét query_cache_min_res_unit. Ha a legtöbb lekérdezés nagy eredményeket ad, akkor ennek a beállításnak a növelése javíthatja a teljesítményt.

Próbáljuk meg értékelni a hatást. Megnézzük, hogyan változnak a gyorsítótár találati számlálói (Qcahe_hits), a memóriahiány miatt érvénytelennek nyilvánított kérelmek száma (Qcache_lowmem_prunes), a SELECT típusú kérések teljes száma (és csak ezek kerülnek gyorsítótárba):

#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 A gyorsítótár aktuális állapota

A lekérdezés gyorsítótárának figyelésére a következőket kell használni 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_blocksmegmutatja, hogy hány szabad blokk van a gyorsítótárban (a gyorsítótárban tárolt kérések növekedésével csökken);
• Qcache_total_blocks— a foglalt blokkok száma;
• Qcache_free_memory- megmutatja a szabad "rendelkezésre álló" memóriát a gyorsítótárazáshoz;
• Qcache_hits- azon kérések száma, amelyek eredményét a gyorsítótárból vették ki anélkül, hogy ténylegesen hozzáfértek volna az adatbázishoz;
• Qcache_inserts- a gyorsítótárhoz hozzáadott kérések száma;
• Qcache_lowmem_prunes- a memóriahiány miatt a gyorsítótárból eltávolított kérések száma;
• Qcache_not_cached- az időgazdálkodási funkciók stb. használata miatt a gyorsítótárba nem írt kérések száma;
• Qcache_queries_in_cache- a gyorsítótárban lévő kérések száma.

Megtekintheti a SELECT lekérdezések teljes számát:

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

7.3 Gyorsítótárazási hatékonyság

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Az aktuális érték a következőképpen nézhető meg:

SHOW VARIABLES LIKE 'query_cache_size';

Ismét felmerül a kérdés: hogyan válasszunk megfelelő értéketquery_cache_size?

Ez segíteni fog Qcache_lowmem_prunes. Ez a változó tárolja a gyorsítótárból az új kérések gyorsítótárba helyezésének szükségessége miatt eltávolított kérések számát. Olyan gyorsítótár-méretre kell törekedni, amelynél Qcache_lowmem_prunescsak kis mértékben nő. Ehhez ajánlatos összehasonlítani az Qcache_lowmem_prunesóránkénti értékek különbségét és a mysql által ugyanabban az órában kapott kérések számát.

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 = (forgalom 10 perc alatt) * 1,2 "

Ez 10 percig gyorsítótárazza a kéréseket + további 20% memóriát biztosít a gyorsítótár töredezettségéhez és további gyorsítótárazási tartalékot

A kérésre adott válaszok számát és átlagos mennyiségét a Bytes_sent változók segítségével számíthatja ki .

Így query_cache_sizenöveltük az értékeket, ami után érdemes figyelni az értékekre Qcache_total_blocks, Qcache_free_blocksés Qcache_queries_in_cache. A MySQL blokkokban tárolja a gyorsítótárat. 1 kéréshez 2 blokk szükséges: az egyik magához a kérés szövegéhez, a második az eredményhez.

Ha a táblázatot az értékből tekintjükQcache%

A gyorsítótár blokkok teljes számaQcache_total_blocks – 28

A 6. kérés most gyorsítótárban van, ami azt jelenti, hogy 6 * 2 = 12 blokk foglalt

Ingyenes blokkok Qcache_free_blocks – 10. Minél több tétlen Qcache_free_blocks, annál nagyobb a gyorsítótár "töredezettsége".

Ha a legtöbb lekérdezés kevés eredő adatot tartalmaz, akkor érdemes csökkenteni a minimális gyorsítótár-blokk méretét query_cache_min_res_unit, amely alapértelmezés szerint 4 KB.

Ha a legtöbb kérés sok adatot ad vissza, akkor érdemes növelni a cache blokk méretét.

A legfontosabb dolog egy minimális érték elérése Qcache_free_blocks.

Ha a számláló Qcache_not_cachednagy, megpróbálhatja növelni a változót query_cache_limit- ez lehetővé teszi a korlát növelését és a "nem illeszkedő" lekérdezések eredményeinek gyorsítótárazását.

A következő konfigurációs változók felelősek a lekérdezési gyorsítótár használatáért:

• query_cache_size— a lekérdezési gyorsítótár mérete. query_cache_size = 0letiltja a gyorsítótár használatát;
• query_cache_limit- a gyorsítótárban tárolt maximális minta mérete;
• query_cache_wlock_invalidate- meghatározza, hogy az adatok a gyorsítótárból kerülnek-e ki, ha a tábla, amelyhez tartoznak, le van zárva olvasásra.
=

A mysql lekérdezések gyorsítótárazásának engedélyezéséhez adja hozzá a következő sorokat a my.cnf fájlhoz ([mysqld] szakasz):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

És indítsa újra a szolgáltatást.

7.4 Ahol nem használják a gyorsítótárat

Nincs gyorsítótárban:

• Kérések tőleSQL_NO_CACHE
• Előkészített lekérdezések(Prepared statements);
• Lekérdezések, amelyek a külső lekérdezés allekérdezései;
• Lekérdezések a tárolt eljárásokon és függvényeken belül;
• Függvényeket használó lekérdezések:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()egy érvvel, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()érvek nélkül, USER (),UUID ();

• Tárolt függvényeket, felhasználói változókat vagy hivatkozási táblákat használó lekérdezések mysql rendszeradatbázisokban vagy INFORMATION_SCHEMA-ban;
• • Kérelmek a következő űrlapokon:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Lekérdezések ideiglenes táblákkal vagy egyáltalán nem használnak táblákat;
• Figyelmeztetéseket generáló kérések (figyelmeztetések);

A gyorsítótár defragmentálható a következő paranccsal:

mysql>flush query cache;

Törlés - parancs:

mysql>flush query cache;

A legfontosabb

Soha ne dolgozzon éles környezetben az alapértelmezett beállításokkal. Ez azt eredményezi, hogy a szerver erőforrásainak nagy része nem kerül felhasználásra. A MySQL megfelelő hangolása többszörösére javíthatja az adatbázis teljesítményét. Ez nemcsak felgyorsítja az alkalmazást, hanem megbirkózik a nagy terhelésekkel is.