Bufring i MySQL

7.1 Caching på DB-siden

MySQL bruker svært skalerbare algoritmer når du arbeider med tabeller, så MySQL kan kjøre selv med små mengder minne. Naturligvis, for bedre ytelse, trenger du mer RAM.

For å se gjeldende innstillinger, koble til databasen

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

Først må du kontrollere at caching er aktivert. Variabel:

• query_cache_typemå være PÅ (1) eller DEMAND (2)
• query_cache_limit- bestemmer maksimal størrelse på resultatet som vil komme inn i hurtigbufferen
• query_cache_sizevære forskjellig fra null. Ved bruk av DEMAND vil kun forespørsler som inneholder direktivet bli bufretSQL_CACHE;
• query_cache_min_res_unitminimumsstørrelsen på den tildelte minneblokken for lagring av resultatene av den hurtigbufrede spørringen. MySQL lagrer ikke hurtigbufferen i en stor del av minnet, i stedet tildeler den blokker med en minimumsstørrelse query_cache_min_res_unit(=4KB som standard) på forespørsel. Den siste slike blokken avkortes til datastørrelsen, og det gjenværende minnet frigjøres.

Effekten av caching er at når serveren mottar en forespørsel, ser den ut for å se om hashen til forespørselen er i cachen. Hvis hashen stemmer – returnerer serveren umiddelbart resultatet – uten å analysere forespørselen, optimalisere osv. overhead - ledsaget av en hurtigbuffermekanisme - bla gjennom hurtigbufferen, skrive søkeresultatet til hurtigbufferen, etc.

Og hvis du har mange små forespørsler i cachen, kan dette føre til minnefragmentering på grunn av et stort antall ledige blokker. Og dette fører igjen til at bufrede oppføringer slettes på grunn av mangel på minne. I dette tilfellet er det fornuftig å redusere verdien av query_cache_min_res_unit. Hvis de fleste av søkene dine gir store resultater, kan ytelsen forbedres ved å øke denne innstillingen.

La oss prøve å evaluere effekten. Vi ser på hvordan cache-trefftellerne endres (Qcahe_hits), antall forespørsler som er erklært ugyldige på grunn av mangel på minne (Qcache_lowmem_prunes), det totale antallet forespørsler av typen SELECT (og bare de er bufret):

#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 Gjeldende cache-status

For å overvåke spørringsbufferen brukes 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_blocksviser hvor mange ledige blokker det er i hurtigbufferen (vil reduseres etter hvert som bufrede forespørsler øker);
• Qcache_total_blocks— antall okkuperte blokker;
• Qcache_free_memory- viser ledig "tilgjengelig" minne for caching;
• Qcache_hits- antall forespørsler, hvis resultater ble hentet fra hurtigbufferen, uten faktisk tilgang til databasen;
• Qcache_inserts- antall forespørsler som ble lagt til cachen;
• Qcache_lowmem_prunes- antall forespørsler som ble fjernet fra hurtigbufferen på grunn av mangel på minne;
• Qcache_not_cached- antall forespørsler som ikke ble skrevet til hurtigbufferen på grunn av bruk av tidsstyringsfunksjoner osv.;
• Qcache_queries_in_cache- antall forespørsler som er i hurtigbufferen.

Du kan se det totale antallet SELECT-søk:

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

7.3 Cachingeffektivitet

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Den nåværende verdien kan sees slik:

SHOW VARIABLES LIKE 'query_cache_size';

Igjen oppstår spørsmålet: hvordan velge en tilstrekkelig verdiquery_cache_size?

Dette vil hjelpe Qcache_lowmem_prunes. Denne variabelen lagrer antall forespørsler som har blitt fjernet fra hurtigbufferen på grunn av behovet for å bufre nye forespørsler. Det er nødvendig å strebe etter en slik cachestørrelse der Qcache_lowmem_prunesden bare vil øke litt. For å gjøre dette, anbefales det å sammenligne forskjellen i verdier Qcache_lowmem_prunesper time og antall forespørsler mottatt av mysql i samme time.

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 = (trafikkvolum på 10 minutter) * 1,2 "

Dette vil bufre forespørsler i 10 minutter + gi ytterligere 20 % minne for bufferfragmentering og ekstra bufferreserve

Du kan beregne antall og gjennomsnittlig volum av et svar på en forespørsel ved å bruke henholdsvis Bytes_sent- variablene

Og så query_cache_sizeøkte vi verdiene, hvoretter du bør ta hensyn til verdiene Qcache_total_blocks, Qcache_free_blocksog Qcache_queries_in_cache. MySQL lagrer cache i blokker. For 1 forespørsel trengs 2 blokker: en for selve forespørselsteksten, den andre for resultatet.

Hvis vi vurderer tabellen fra verdienQcache%

Totalt antall hurtigbufferblokkerQcache_total_blocks – 28

Forespørsel 6 er bufret nå, noe som betyr at 6 * 2 = 12 blokker er opptatt

Gratis blokker Qcache_free_blocks – 10. Jo mer inaktiv Qcache_free_blocks, jo større grad av "fragmentering" av cachen.

Hvis de fleste søk har en liten mengde resulterende data, er det verdt å redusere minimumsstørrelsen for cacheblokk query_cache_min_res_unit, som er 4 KB som standard.

Hvis de fleste forespørsler returnerer mye data, er det verdt å øke størrelsen på cacheblokken.

Det viktigste er å oppnå en minimumsverdi Qcache_free_blocks.

Hvis telleren Qcache_not_cacheder stor, kan du prøve å øke variabelen query_cache_limit- den vil tillate deg å øke grensen og cache resultatene av spørringer som "ikke passer".

Følgende konfigurasjonsvariabler er ansvarlige for bruk av spørringsbufferen:

• query_cache_size— størrelsen på spørringsbufferen. query_cache_size = 0deaktiverer cache-bruk;
• query_cache_limit- størrelsen på den maksimale prøven som er lagret i hurtigbufferen;
• query_cache_wlock_invalidate- bestemmer om dataene skal hentes fra cachen hvis tabellen de tilhører er låst for lesing.
=

For å aktivere mysql-spørringsbufring, legg til følgende linjer i my.cnf (seksjon [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Og start tjenesten på nytt.

7.4 Hvor cachen ikke brukes

Ikke bufret:

• Forespørsler fraSQL_NO_CACHE
• Forberedte spørsmål(Prepared statements);
• Spørringer som er underspørringer av den ytre spørringen;
• Forespørsler i lagrede prosedyrer og funksjoner;
• Spørsmål som bruker funksjoner:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()med ett argument, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), uten argumenter SYSDATE (), ,UNIX_TIMESTAMP ()USER ()UUID ();

• Spørringer som bruker lagrede funksjoner, brukervariabler eller referansetabeller i mysql- systemdatabaser eller INFORMATION_SCHEMA;
• • Forespørsler i følgende skjemaer:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Spørringer med midlertidige tabeller eller ikke bruker tabeller i det hele tatt;
• Forespørsler som genererer advarsler (advarsler);

Du kan defragmentere cachen med kommandoen:

mysql>flush query cache;

Slett - kommando:

mysql>flush query cache;

Det viktigste

Arbeid aldri i produksjon med standardinnstillinger. Dette vil føre til at de fleste av serverens ressurser ikke blir brukt. Riktig MySQL-innstilling kan forbedre databaseytelsen flere ganger. Dette vil ikke bare fremskynde applikasjonen, men også takle en tung belastning.