Caching in MySQL

7.1 Caching lato DB

MySQL utilizza algoritmi altamente scalabili quando lavora con le tabelle, quindi MySQL può essere eseguito anche con piccole quantità di memoria. Naturalmente, per prestazioni migliori, hai bisogno di più RAM.

Per visualizzare le impostazioni correnti, connettersi al database

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

Innanzitutto, assicurati che la memorizzazione nella cache sia abilitata. Variabile:

• query_cache_typedeve essere ON (1) o DEMAND (2)
• query_cache_limit- determina la dimensione massima del risultato che entrerà nella cache
• query_cache_sizeessere diverso da zero. Quando si utilizza DEMAND, verranno memorizzate nella cache solo le richieste che contengono la direttivaSQL_CACHE;
• query_cache_min_res_unitla dimensione minima del blocco di memoria allocato per l'archiviazione dei risultati della query memorizzata nella cache. MySQL non memorizza la cache in un grosso pezzo di memoria, invece alloca blocchi con una dimensione minima query_cache_min_res_unit(=4KB per impostazione predefinita) su richiesta. L'ultimo blocco di questo tipo viene troncato alla dimensione dei dati e la memoria rimanente viene liberata.

L'effetto della memorizzazione nella cache è che quando il server riceve una richiesta, controlla se l'hash della richiesta è nella cache. Se l'hash corrisponde, il server restituisce immediatamente il risultato, senza analizzare la richiesta, ottimizzare, ecc. sovraccarico - accompagnato da un meccanismo di memorizzazione nella cache - navigazione nella cache, scrittura del risultato della query nella cache, ecc.

E se hai molte piccole richieste nella cache, questo può portare alla frammentazione della memoria a causa di un gran numero di blocchi liberi. E questo, a sua volta, provoca l'eliminazione delle voci memorizzate nella cache a causa della mancanza di memoria. In questo caso, ha senso diminuire il valore di query_cache_min_res_unit. Se la maggior parte delle query produce risultati di grandi dimensioni, l'aumento di questa impostazione può migliorare le prestazioni.

Proviamo a valutare l'effetto. Osserviamo come cambiano i contatori di hit della cache (Qcahe_hits), il numero di richieste dichiarate non valide per mancanza di memoria (Qcache_lowmem_prunes), il numero totale di richieste del tipo SELECT (e solo loro sono memorizzate nella 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 Stato attuale della cache

Per monitorare la cache delle query viene utilizzata 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_blocksmostra quanti blocchi liberi ci sono nella cache (diminuiranno all'aumentare delle richieste memorizzate nella cache);
• Qcache_total_blocks— il numero di blocchi occupati;
• Qcache_free_memory- mostra la memoria libera "disponibile" per il caching;
• Qcache_hits- il numero di richieste i cui risultati sono stati prelevati dalla cache, senza effettivamente accedere al database;
• Qcache_inserts- il numero di richieste che sono state aggiunte alla cache;
• Qcache_lowmem_prunes- il numero di richieste rimosse dalla cache per mancanza di memoria;
• Qcache_not_cached- il numero di richieste che non sono state scritte nella cache a causa dell'utilizzo di funzioni di gestione del tempo, ecc.;
• Qcache_queries_in_cache- il numero di richieste presenti nella cache.

Puoi visualizzare il numero totale di query SELECT:

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

7.3 Efficienza della memorizzazione nella cache

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

Il valore corrente può essere visualizzato in questo modo:

SHOW VARIABLES LIKE 'query_cache_size';

Ancora una volta, sorge la domanda: come scegliere un valore adeguatoquery_cache_size?

Questo aiuterà Qcache_lowmem_prunes. Questa variabile memorizza il numero di richieste che sono state rimosse dalla cache a causa della necessità di memorizzare nella cache nuove richieste. È necessario lottare per una tale dimensione della cache alla quale Qcache_lowmem_prunesaumenterà solo leggermente. Per fare ciò, si consiglia di confrontare la differenza di valori Qcache_lowmem_prunesall'ora e il numero di richieste ricevute da mysql nella stessa ora.

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 = (volume di traffico in 10 minuti) * 1,2 "

Ciò memorizzerà nella cache le richieste per 10 minuti + fornirà un ulteriore 20% di memoria per la frammentazione della cache e una riserva di cache aggiuntiva

Puoi calcolare il numero e il volume medio di una risposta a una richiesta utilizzando rispettivamente le variabili Bytes_sent

E così query_cache_sizeabbiamo aumentato i valori, dopodiché dovresti prestare attenzione ai valori Qcache_total_blocks, Qcache_free_blockse Qcache_queries_in_cache. MySQL memorizza la cache in blocchi. Per 1 richiesta sono necessari 2 blocchi: uno per il testo della richiesta stessa, il secondo per il risultato.

Se consideriamo la tabella dal valoreQcache%

Numero totale di blocchi della cacheQcache_total_blocks – 28

La richiesta 6 è ora memorizzata nella cache, il che significa che 6 * 2 = 12 blocchi sono occupati

Blocchi gratuiti Qcache_free_blocks – 10. Più è inattivo Qcache_free_blocks, maggiore è il grado di "frammentazione" della cache.

Se la maggior parte delle query ha una piccola quantità di dati risultanti, vale la pena ridurre la dimensione minima del blocco della cache query_cache_min_res_unit, che per impostazione predefinita è 4 KB.

Se la maggior parte delle richieste restituisce molti dati, vale la pena aumentare la dimensione del blocco della cache.

La cosa principale è raggiungere un valore minimo Qcache_free_blocks.

Se il contatore Qcache_not_cachedè grande, puoi provare ad aumentare la variabile query_cache_limit: ti consentirà di aumentare il limite e memorizzare nella cache i risultati delle query che "non si adattano".

Le seguenti variabili di configurazione sono responsabili dell'utilizzo della cache delle query:

• query_cache_size— la dimensione della cache delle query. query_cache_size = 0disabilita l'utilizzo della cache;
• query_cache_limit- la dimensione del campione massimo memorizzato nella cache;
• query_cache_wlock_invalidate- determina se i dati verranno prelevati dalla cache se la tabella a cui appartengono è bloccata per la lettura.
=

Per abilitare il caching delle query mysql, basta aggiungere le seguenti righe a my.cnf (Section [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

E riavvia il servizio.

7.4 Dove la cache non viene utilizzata

Non memorizzato nella cache:

• Richieste daSQL_NO_CACHE
• Interrogazioni preparate(Prepared statements);
• Query che sono sottoquery della query esterna;
• Query all'interno di stored procedure e funzioni;
• Query che utilizzano funzioni:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()con un argomento, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()senza argomenti, USER (),UUID ();

• Query che utilizzano funzioni memorizzate, variabili utente o tabelle di riferimento nei database di sistema mysql o INFORMATION_SCHEMA;
• • Richieste nei seguenti moduli:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Query con tabelle temporanee o non utilizzo di tabelle;
• Richieste che generano avvisi (avvisi);

Puoi deframmentare la cache con il comando:

mysql>flush query cache;

Cancella - comando:

mysql>flush query cache;

Il più importante

Non lavorare mai in produzione con le impostazioni predefinite. Ciò comporterà il mancato utilizzo della maggior parte delle risorse del server. Una corretta messa a punto di MySQL può migliorare le prestazioni del database di diverse volte. Ciò non solo accelererà l'applicazione, ma affronterà anche un carico pesante.