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_type
må være PÅ (1) eller DEMAND (2)query_cache_limit
- bestemmer maksimal størrelse på resultatet som vil komme inn i hurtigbufferenquery_cache_size
være forskjellig fra null. Ved bruk av DEMAND vil kun forespørsler som inneholder direktivet bli bufretSQL_CACHE;
query_cache_min_res_unit
minimumsstø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ørrelsequery_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_blocks
viser 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
"Det anbefales å evaluere effektiviteten til hurtigbufferen ved å dele verdien av variabelen Qcache_hits on Qcache_hits + Com_select
, siden når du behandler en forespørsel, øker Qcache_hits-telleren (hvis forespørselen behandles fra hurtigbufferen) eller Com_select (hvis forespørselen ikke er bufret). Denne metoden er foreslått i "Mysql Performance Optimization" O'reilly
Det er en annen måte på nettet
qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)
Hvis denne verdien er > 0,8, blir 80 % av forespørslene dine bufret, noe som er en veldig god indikator.
Hvis %
cache-treffet er lavt, må du øke verdien query_cache_size
.
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_prunes
den bare vil øke litt. For å gjøre dette, anbefales det å sammenligne forskjellen i verdier Qcache_lowmem_prunes
per time og antall forespørsler mottatt av mysql i samme time.
"I praksis kan en av 2 formler brukes til å beregne query_cache_size:
query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
eller
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_blocks
og 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_cached
er 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 = 0
deaktiverer 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 fra
SQL_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 argumenterSYSDATE ()
, ,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.
GO TO FULL VERSION