Memorarea în cache în MySQL

7.1 Memorarea în cache pe partea DB

MySQL folosește algoritmi foarte scalabili atunci când lucrează cu tabele, astfel încât MySQL poate rula chiar și cu cantități mici de memorie. Desigur, pentru o performanță mai bună, aveți nevoie de mai multă RAM.

Pentru a vizualiza setările curente, conectați-vă la baza de date

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

În primul rând, asigurați-vă că stocarea în cache este activată. Variabil:

• query_cache_typetrebuie să fie ON (1) sau DEMAND (2)
• query_cache_limit- determină dimensiunea maximă a rezultatului care va intra în cache
• query_cache_sizefi diferit de zero. Când utilizați DEMAND, numai cererile care conțin directiva vor fi stocate în cacheSQL_CACHE;
• query_cache_min_res_unitdimensiunea minimă a blocului de memorie alocat pentru stocarea rezultatelor interogării stocate în cache. MySQL nu stochează memoria cache într-o singură bucată mare de memorie, ci alocă blocuri cu o dimensiune minimă query_cache_min_res_unit(=4KB implicit) la cerere. Ultimul astfel de bloc este trunchiat la dimensiunea datelor, iar memoria rămasă este eliberată.

Efectul memorării în cache este că atunci când serverul primește o solicitare, se uită să vadă dacă hash-ul cererii este în cache. Dacă hash-ul se potrivește - serverul returnează imediat rezultatul - fără a analiza solicitarea, optimizarea etc. overhead - însoțit de un mecanism de stocare în cache - răsfoirea în cache, scrierea rezultatului interogării în cache etc.

Și dacă aveți o mulțime de solicitări mici în cache, atunci acest lucru poate duce la fragmentarea memoriei din cauza unui număr mare de blocuri libere. Și acest lucru, la rândul său, face ca intrările din cache să fie șterse din cauza lipsei de memorie. În acest caz, este logic să scădeți valoarea lui query_cache_min_res_unit. Dacă majoritatea interogărilor dvs. produc rezultate mari, atunci creșterea acestei setări poate îmbunătăți performanța.

Să încercăm să evaluăm efectul. Ne uităm la modul în care se modifică contoarele de accesări din cache (Qcahe_hits), numărul de solicitări declarate invalide din cauza lipsei de memorie (Qcache_lowmem_prunes), numărul total de solicitări de tip SELECT (și numai acestea sunt stocate în 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 Starea actuală a memoriei cache

Pentru a monitoriza interogarea cache se folosește 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_blocksarată câte blocuri libere există în cache (va scădea pe măsură ce cererile stocate în cache cresc);
• Qcache_total_blocks— numărul de blocuri ocupate;
• Qcache_free_memory- arată memoria „disponibilă” liberă pentru cache;
• Qcache_hits- numarul de solicitari ale caror rezultate au fost preluate din cache, fara a accesa efectiv baza de date;
• Qcache_inserts- numărul de solicitări care au fost adăugate în cache;
• Qcache_lowmem_prunes- numărul de solicitări care au fost eliminate din cache din cauza lipsei de memorie;
• Qcache_not_cached- numărul de solicitări care nu au fost scrise în cache din cauza utilizării funcțiilor de gestionare a timpului etc.;
• Qcache_queries_in_cache- numărul de solicitări care se află în cache.

Puteți vizualiza numărul total de interogări SELECT:

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

7.3 Eficiența stocării în cache

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Valoarea curentă poate fi vizualizată astfel:

SHOW VARIABLES LIKE 'query_cache_size';

Din nou, se pune întrebarea: cum să alegeți o valoare adecvatăquery_cache_size?

Acest lucru va ajuta Qcache_lowmem_prunes. Această variabilă stochează numărul de solicitări care au fost eliminate din cache din cauza necesității de a stoca în cache cereri noi. Este necesar să depuneți eforturi pentru o astfel de dimensiune a cache-ului la care Qcache_lowmem_prunesva crește doar ușor. Pentru a face acest lucru, este recomandat să comparați diferența de valori Qcache_lowmem_prunespe oră și numărul de solicitări primite de mysql în aceeași oră.

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 = (volum de trafic în 10 minute) * 1,2 "

Acest lucru va stoca cererile în cache timp de 10 minute + va oferi o memorie suplimentară de 20% pentru fragmentarea memoriei cache și o rezervă suplimentară de cache.

Puteți calcula numărul și volumul mediu al unui răspuns la o solicitare folosind variabilele Bytes_sent

Și așa am query_cache_sizecrescut valorile, după care ar trebui să fiți atenți la valori Qcache_total_blocksși . MySQL stochează memoria cache în blocuri. Pentru 1 cerere, sunt necesare 2 blocuri: unul pentru textul cererii în sine, al doilea pentru rezultat.Qcache_free_blocksQcache_queries_in_cache

Dacă luăm în considerare tabelul din valoareQcache%

Numărul total de blocuri cacheQcache_total_blocks – 28

Solicitarea 6 este stocată în cache acum, ceea ce înseamnă că 6 * 2 = 12 blocuri sunt ocupate

Blocuri gratuite Qcache_free_blocks – 10. Cu cât este mai inactiv Qcache_free_blocks, cu atât este mai mare gradul de „fragmentare” a memoriei cache.

Dacă majoritatea interogărilor au o cantitate mică de date rezultate, atunci merită să reduceți dimensiunea minimă a blocului cache query_cache_min_res_unit, care este de 4 KB în mod implicit.

Dacă majoritatea solicitărilor returnează o mulțime de date, atunci merită să măriți dimensiunea blocului cache.

Principalul lucru este să obțineți o valoare minimă Qcache_free_blocks.

Dacă contorul Qcache_not_cachedeste mare, puteți încerca să creșteți variabila query_cache_limit- vă va permite să creșteți limita și să memorați în cache rezultatele interogărilor care „nu se potrivesc”.

Următoarele variabile de configurare sunt responsabile pentru utilizarea cache-ului de interogări:

• query_cache_size— dimensiunea cache-ului de interogări. query_cache_size = 0dezactivează utilizarea cache-ului;
• query_cache_limit- dimensiunea probei maxime stocate în cache;
• query_cache_wlock_invalidate- determină dacă datele vor fi preluate din cache dacă tabelul căruia îi aparțin este blocat pentru citire.
=

Pentru a activa stocarea în cache a interogărilor mysql, trebuie doar să adăugați următoarele linii la my.cnf (secțiunea [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Și reporniți serviciul.

7.4 În cazul în care memoria cache nu este utilizată

Nu este stocat în cache:

• Cereri de laSQL_NO_CACHE
• Interogări pregătite(Prepared statements);
• Interogări care sunt subinterogări ale interogării externe;
• Interogări în cadrul procedurilor și funcțiilor stocate;
• Interogări care utilizează funcții:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), cu un ENCRYPT ()singur argument, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), , RELEASE_LOCK (), SLEEP (), fără argumente, ,SYSDATE ()UNIX_TIMESTAMP ()USER ()UUID ();

• Interogări folosind funcții stocate, variabile utilizator sau tabele de referință în bazele de date de sistem mysql sau INFORMATION_SCHEMA;
• • Cereri în următoarele forme:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Interogări cu tabele temporare sau care nu folosesc deloc tabele;
• Cereri generatoare de avertismente (avertismente);

Puteți defragmenta memoria cache cu comanda:

mysql>flush query cache;

Clear - comandă:

mysql>flush query cache;

Cel mai important

Nu lucrați niciodată în producție cu setări implicite. Acest lucru va duce la neutilizarea majorității resurselor serverului. Reglarea corectă a MySQL poate îmbunătăți performanța bazei de date de câteva ori. Acest lucru nu numai că va accelera aplicarea, dar va face față și unei sarcini grele.