Cachning i MySQL

7.1 Cachning på DB-sidan

MySQL använder mycket skalbara algoritmer när man arbetar med tabeller, så MySQL kan köras även med små mängder minne. För bättre prestanda behöver du naturligtvis mer RAM.

För att se de aktuella inställningarna, anslut till 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)

Se först till att caching är aktiverat. Variabel:

• query_cache_typemåste vara PÅ (1) eller DEMAND (2)
• query_cache_limit- bestämmer den maximala storleken på resultatet som kommer in i cachen
• query_cache_sizevara annorlunda än noll. När du använder DEMAND kommer endast förfrågningar som innehåller direktivet att cachelagrasSQL_CACHE;
• query_cache_min_res_unitden minsta storleken på det tilldelade minnesblocket för att lagra resultaten av den cachade frågan. MySQL lagrar inte cachen i en stor bit minne, istället allokerar den block med en minimistorlek ( query_cache_min_res_unit=4KB som standard) på begäran. Det sista sådana blocket trunkeras till datastorleken och det återstående minnet frigörs.

Effekten av cachelagring är att när servern tar emot en förfrågan, ser den ut om hashen för begäran finns i cachen. Om hashen matchar - returnerar servern omedelbart resultatet - utan att tolka begäran, optimera osv. overhead - åtföljd av en cachemekanism - bläddra i cachen, skriva frågeresultatet till cachen, etc.

Och om du har många små förfrågningar i cachen, kan detta leda till minnesfragmentering på grund av ett stort antal lediga block. Och detta gör i sin tur att cachade poster raderas på grund av brist på minne. I det här fallet är det vettigt att minska värdet på query_cache_min_res_unit. Om de flesta av dina frågor ger stora resultat kan prestanda förbättras genom att öka denna inställning.

Låt oss försöka utvärdera effekten. Vi tittar på hur cacheträffräknarna ändras (Qcahe_hits), antalet förfrågningar som förklarats ogiltiga på grund av brist på minne (Qcache_lowmem_prunes), det totala antalet förfrågningar av typen SELECT (och endast de cachelagras):

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

För att övervaka frågecachen används 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_blocksvisar hur många lediga block det finns i cachen (kommer att minska när cachade förfrågningar ökar);
• Qcache_total_blocks— Antalet ockuperade kvarter.
• Qcache_free_memory- visar det lediga "tillgängliga" minnet för cachning;
• Qcache_hits- Antalet förfrågningar vars resultat togs från cacheminnet, utan att faktiskt komma åt databasen;
• Qcache_inserts- antalet förfrågningar som lades till i cachen;
• Qcache_lowmem_prunes- antalet förfrågningar som togs bort från cachen på grund av brist på minne;
• Qcache_not_cached- Antalet förfrågningar som inte skrevs till cachen på grund av användningen av tidshanteringsfunktioner etc.;
• Qcache_queries_in_cache- antalet förfrågningar som finns i cachen.

Du kan se det totala antalet SELECT-frågor:

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)

Det aktuella värdet kan ses så här:

SHOW VARIABLES LIKE 'query_cache_size';

Återigen uppstår frågan: hur man väljer ett adekvat värdequery_cache_size?

Detta kommer att hjälpa Qcache_lowmem_prunes. Denna variabel lagrar antalet förfrågningar som har tagits bort från cachen på grund av behovet av att cachelagra nya förfrågningar. Det är nödvändigt att sträva efter en sådan cachestorlek där Qcache_lowmem_prunesden bara kommer att öka något. För att göra detta rekommenderas det att jämföra skillnaden i värden Qcache_lowmem_prunesper timme och antalet förfrågningar som tas emot av mysql under samma timme.

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

Detta kommer att cacheförfrågningar i 10 minuter + ge ytterligare 20 % minne för cachefragmentering och ytterligare cachelagring

Du kan beräkna antalet och genomsnittliga volymen av ett svar på en begäran med hjälp av Bytes_sent- variablerna , respektive

Och så query_cache_sizeökade vi värdena, varefter du bör vara uppmärksam på värdena och Qcache_total_blocks. MySQL lagrar cache i block. För 1 begäran behövs 2 block: ett för själva förfrågningstexten, det andra för resultatet.Qcache_free_blocksQcache_queries_in_cache

Om vi ​​betraktar tabellen från värdetQcache%

Totalt antal cacheblockQcache_total_blocks – 28

Begäran 6 är cachad nu, vilket betyder att 6 * 2 = 12 block är upptagna

Gratis block Qcache_free_blocks – 10. Ju mer inaktiv Qcache_free_blocks, desto större grad av "fragmentering" av cachen.

Om de flesta frågor har en liten mängd resulterande data, är det värt att minska den minsta storleken på cacheblocket, query_cache_min_res_unitsom är 4 KB som standard.

Om de flesta förfrågningar returnerar mycket data, är det värt att öka storleken på cacheblocket.

Det viktigaste är att uppnå ett minimivärde Qcache_free_blocks.

Om räknaren Qcache_not_cachedär stor kan du försöka öka variabeln query_cache_limit- det gör att du kan öka gränsen och cacheresultaten av frågor som "inte passar".

Följande konfigurationsvariabler är ansvariga för att använda frågecachen:

• query_cache_size— storleken på frågecachen. query_cache_size = 0inaktiverar cacheanvändning;
• query_cache_limit- storleken på det maximala provet som lagras i cachen;
• query_cache_wlock_invalidate- bestämmer om data kommer att tas från cachen om tabellen som de tillhör är låst för läsning.
=

För att aktivera mysql-frågecache, lägg bara till följande rader i my.cnf (avsnitt [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

Och starta om tjänsten.

7.4 Där cachen inte används

Ej cachad:

• Förfrågningar frånSQL_NO_CACHE
• Förberedda frågor(Prepared statements);
• Frågor som är underfrågor till den yttre frågan;
• Frågor i lagrade procedurer och funktioner;
• Frågor som använder funktioner:

  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 ()utan argument, ,SYSDATE ()UNIX_TIMESTAMP ()USER ()UUID ();

• Frågor som använder lagrade funktioner, användarvariabler eller referenstabeller i mysql- systemdatabaser eller INFORMATION_SCHEMA;
• • Förfrågningar i följande former:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Frågor med tillfälliga tabeller eller som inte använder tabeller alls;
• Förfrågningar som genererar varningar (varningar);

Du kan defragmentera cachen med kommandot:

mysql>flush query cache;

Rensa - kommando:

mysql>flush query cache;

Det viktigaste

Arbeta aldrig i produktion med standardinställningar. Detta kommer att resultera i att de flesta av serverns resurser inte används. Korrekt MySQL-inställning kan förbättra databasens prestanda flera gånger. Detta kommer inte bara att påskynda applikationen, utan också klara av en tung belastning.