Caching i MySQL

7.1 Caching på DB-siden

MySQL bruger meget skalerbare algoritmer, når man arbejder med tabeller, så MySQL kan køre selv med små mængder hukommelse. For bedre ydeevne har du naturligvis brug for mere RAM.

For at se de aktuelle indstillinger skal du oprette forbindelse 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 skal du sikre dig, at caching er aktiveret. Variabel:

• query_cache_typeskal være TIL (1) eller DEMAND (2)
• query_cache_limit- bestemmer den maksimale størrelse af resultatet, der kommer ind i cachen
• query_cache_sizevære forskellig fra nul. Ved brug af DEMAND vil kun anmodninger, der indeholder direktivet, blive cachelagretSQL_CACHE;
• query_cache_min_res_unitminimumsstørrelsen af ​​den allokerede hukommelsesblok til lagring af resultaterne af den cachelagrede forespørgsel. MySQL gemmer ikke cachen i en stor del af hukommelsen, i stedet tildeler den blokke med en minimumsstørrelse query_cache_min_res_unit(=4KB som standard) efter behov. Den sidste blok af denne type afkortes til datastørrelsen, og den resterende hukommelse frigøres.

Effekten af ​​caching er, at når serveren modtager en anmodning, ser den ud for at se, om anmodningens hash er i cachen. Hvis hashen matcher - returnerer serveren straks resultatet - uden at parse anmodningen, optimere osv. overhead - ledsaget af en caching-mekanisme - gennemse cachen, skrive forespørgselsresultatet til cachen osv.

Og hvis du har mange små anmodninger i cachen, så kan dette føre til hukommelsesfragmentering på grund af et stort antal ledige blokke. Og dette medfører til gengæld, at cachelagrede poster slettes på grund af manglende hukommelse. I dette tilfælde giver det mening at mindske værdien af query_cache_min_res_unit​​. Hvis de fleste af dine forespørgsler giver store resultater, kan en forøgelse af denne indstilling forbedre ydeevnen.

Lad os prøve at evaluere effekten. Vi ser på, hvordan cache-hittællerne ændrer sig (Qcahe_hits), antallet af anmodninger, der er erklæret ugyldige på grund af manglende hukommelse (Qcache_lowmem_prunes), det samlede antal anmodninger af typen SELECT (og kun de er cachelagret):

#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 Aktuel cachestatus

For at overvåge forespørgselscachen bruges 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 blokke der er i cachen (vil falde, efterhånden som cachede anmodninger stiger);
• Qcache_total_blocks— antallet af besatte blokke;
• Qcache_free_memory- viser den ledige "tilgængelige" hukommelse til caching;
• Qcache_hits- antallet af anmodninger, hvis resultater blev taget fra cachen, uden faktisk adgang til databasen;
• Qcache_inserts- antallet af anmodninger, der blev tilføjet til cachen;
• Qcache_lowmem_prunes- antallet af anmodninger, der blev fjernet fra cachen på grund af manglende hukommelse;
• Qcache_not_cached- antallet af anmodninger, der ikke blev skrevet til cachen på grund af brugen af ​​tidsstyringsfunktioner osv.;
• Qcache_queries_in_cache- antallet af anmodninger, der er i cachen.

Du kan se det samlede antal SELECT-forespørgsler:

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 aktuelle værdi kan ses sådan:

SHOW VARIABLES LIKE 'query_cache_size';

Igen opstår spørgsmålet: hvordan man vælger en passende værdiquery_cache_size?

Dette vil hjælpe Qcache_lowmem_prunes. Denne variabel gemmer antallet af anmodninger, der er blevet fjernet fra cachen på grund af behovet for at cache nye anmodninger. Det er nødvendigt at stræbe efter en sådan cachestørrelse, hvor Qcache_lowmem_prunesden kun vil stige lidt. For at gøre dette anbefales det at sammenligne forskellen i værdier Qcache_lowmem_prunespr. time og antallet af anmodninger modtaget af 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 = (trafikmængde på 10 minutter) * 1,2 "

Dette vil cache anmodninger i 10 minutter + give yderligere 20% hukommelse til cache fragmentering og yderligere caching reserve

Du kan beregne antallet og den gennemsnitlige volumen af ​​et svar på en anmodning ved hjælp af henholdsvis Bytes_sent variablerne

Og så query_cache_sizeøgede vi værdierne, hvorefter du skal være opmærksom på værdierne Qcache_total_blocks, Qcache_free_blocksog Qcache_queries_in_cache. MySQL gemmer cache i blokke. For 1 anmodning er der brug for 2 blokke: en til selve anmodningsteksten, den anden til resultatet.

Hvis vi betragter tabellen ud fra værdienQcache%

Samlet antal cacheblokkeQcache_total_blocks – 28

Forespørgsel 6 er cachet nu, hvilket betyder, at 6 * 2 = 12 blokke er optaget

Gratis blokke Qcache_free_blocks – 10. Jo mere ledig Qcache_free_blocks, jo større grad af "fragmentering" af cachen.

Hvis de fleste forespørgsler har en lille mængde resulterende data, så er det værd at reducere den mindste cacheblokstørrelse query_cache_min_res_unit, som er 4 KB som standard.

Hvis de fleste anmodninger returnerer en masse data, så er det værd at øge størrelsen af ​​cacheblokken.

Det vigtigste er at opnå en minimumsværdi Qcache_free_blocks.

Hvis tælleren Qcache_not_cacheder stor, kan du prøve at øge variablen query_cache_limit- det vil give dig mulighed for at øge grænsen og cache resultaterne af forespørgsler, der "ikke passer".

Følgende konfigurationsvariabler er ansvarlige for at bruge forespørgselscachen:

• query_cache_size— størrelsen af ​​forespørgselscachen. query_cache_size = 0deaktiverer cachebrug;
• query_cache_limit- størrelsen af ​​den maksimale prøve gemt i cachen;
• query_cache_wlock_invalidate- bestemmer, om dataene tages fra cachen, hvis tabellen, som de tilhører, er låst til læsning.
=

For at aktivere mysql-forespørgselscaching skal du blot tilføje følgende linjer til my.cnf (Sektion [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Og genstart tjenesten.

7.4 Hvor cachen ikke bruges

Ikke cachelagret:

• Forespørgsler fraSQL_NO_CACHE
• Forberedte forespørgsler(Prepared statements);
• Forespørgsler, der er underforespørgsler til den ydre forespørgsel;
• Forespørgsler i lagrede procedurer og funktioner;
• Forespørgsler, der bruger funktioner:

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

• Forespørgsler ved hjælp af lagrede funktioner, brugervariabler eller referencetabeller i mysql- systemdatabaser eller INFORMATION_SCHEMA;
• • Anmodninger i følgende formularer:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Forespørgsler med midlertidige tabeller eller slet ikke bruger tabeller;
• Anmodninger, der genererer advarsler (advarsler);

Du kan defragmentere cachen med kommandoen:

mysql>flush query cache;

Ryd - kommando:

mysql>flush query cache;

Den vigtigste

Arbejd aldrig i produktion med standardindstillinger. Dette vil resultere i, at de fleste af serverens ressourcer ikke bliver brugt. Korrekt MySQL tuning kan forbedre databasens ydeevne flere gange. Dette vil ikke kun fremskynde applikationen, men også klare en tung belastning.