Come alleggerire il carico di lavoro di un server MySQL grazie all'utilizzo della Query Cache. Velocizza l'esecuzione delle queries su MySQL.
ATTENZIONE! Il contenuto di questo articolo necessita di revisione perché redatto più di un anno fa. I comandi e la sintassi utilizzata potrebbero non funzionare in seguito a cambiamenti o aggiornamenti del software. Scrivici a Questo indirizzo email è protetto dagli spambots. E' necessario abilitare JavaScript per vederlo. se hai bisogno di assistenza.
Articolo pubblicato il 06/02/2012
MySQL è sicuramente uno dei Database Management Systems più diffuso al mondo e viene quasi sempre utilizzato come backend per blog e siti web dinamici. La corretta configurazione di MySQL è quindi un'aspetto determinante per evitare che il database diventi un collo di bottiglia all'interno dello stack Web (LAMP/WAMP).
Se utilizzate MySQL nell'infrastruttura web del vostro sito internet, è molto probabile che l'utilizzo della Query Cache possa alleggerire il carico di lavoro del vostro server, riservando risorse computazionali utili in caso di pesanti carichi di sistema.
Il ruolo del DBMS nello stack Web
Nella maggioranza dei casi, MySQL viene utilizzato come backend per lo storage dei contenuti del vostro sito web o blog, la quasi totalità delle query è quindi di tipo SELECT e le operazioni di lettura sono quindi molte di più in proporzione alle operazioni di scrittura.
Se non utilizzate alcun tipo di caching dei dati a livello appicativo (memcached, APC ...) il vostro Database MySQL verrà coinvolto ogni volta che un utente richiede una pagina; se il vostro sito web ottiene parecchie visite e se la tabella contenente i dati delle vostre pagine web è abbastanza grande - anche se non avrà mai milioni di rows - potreste trarre benefici dall'utilizzo della funzione Query Cache che permette un caching dei dati direttamente all'interno del vostro server MySQL.
Come funziona MySQL Query Cache
La funzione Query Cache di MySQL fu implementata per la prima volta nella versione 4.0.0 di MySQL Server (rilasciata nel 2003), ed è fino ad oggi integrata nelle nuove release. Il funzionamento di Query Cache è molto semplice e non richiede alcun tipo di modifica al vostro codice PHP (o Python/perl) rendendone l'implementazione semplice e soprattutto indolore.
La Query Cache funziona memorizzando l'output delle query di tipo SELECT all'interno dello spazio riservato al caching delle query nella RAM, utilizzandola si evita quindi di eseguire una stessa query più volte sulla stessa tabella a vantaggio del workload di MySQL e delle operazioni di I/O sul disco.
Nel caso in cui si eseguano query che modificano il contenuto della tabella in questione (UPDATE, INSERT, DELETE, ALTER ...) tutte le queries memorizzate vengono invalidate automaticamente per permetterne il successivo aggiornamento. La Query Cache permette dunque di alleggerire il carico di lavoro del server senza curarsi del controllo e della validazione dei dati (in caso di modifica degli stessi) dato che queste funzionalità sono totalmente integrate all'interno di questa feature.
Attivare e configurare la Query Cache
Per scoprire se la Query Cache è già in funzione sulla vostra installazione di MySQL eseguite questa query:
SHOW VARIABLES LIKE '%query_cache%'; +------------------------------+----------+ | Variable_name | Value | +------------------------------+----------+ | have_query_cache | YES | | query_cache_limit | 1048576 | | query_cache_min_res_unit | 4096 | | query_cache_size | 16777216 | | query_cache_type | ON | | query_cache_wlock_invalidate | OFF | +------------------------------+----------+ 6 rows in set (0.01 sec)
Nell'output dovreste avere la variabile have_query_cache impostata su YES e la variabile query_cache_size maggiore di zero. Di default la variabile query_cache_size è impostata su 0 rendendo di fatto disabilitata la funzione Query Cache.
Per attivare Query Cache potete eseguire la seguente query:
SET GLOBAL query_cache_size = 1000000;
Dove il valore numerico è la quantità di byte riservati al caching delle queries. È importante stabilire la dimensione della cache in base alle proprie esigenze - ma anche in base alla dimensione dell'output delle queries salvate - ricordando che valori troppo piccoli o troppo grandi possono comprometterne l'utilizzo. In particolare nella guida ufficiale di MySQL si legge che la dimensione minima della query cache è di 40 Kb, necessari a memorizzare la struttura stessa della cache.
Se volete che la query cache sia abilitata di default e resti abilitata anche dopo un restart del server, è necessario mettere mani al file di configurazione di MySQL (generalmente /etc/my.cnf) ed aggiungere le seguenti righe:
# Query Cache Conf query_cache_size = 10M
Nel caso abbiate delle queries che producono in output una grossa quantità di dati, è possibile discriminarne il caching utilizzando la variabile query_cache_limit che imposta la dimensione massima dell'output affinchè venga memorizzato nella cache.
Bypassare l'utilizzo della Query Cache
La maggior parte delle distribuzioni GNU/Linux moderne (Ubuntu, Debian, Fedora, etc. etc.) attivano di default la funzione Query Cache di MySQL durante la fase di installazione perseguendo l'obiettivo di rendere più performante il DBMS.
Tuttavia talvolta può essere utile disabilitare la Query Cache per singole query, specie se l'obiettivo è quello di verificare la velocità oggettiva della query: in questo caso la Query Cache sarebbe un ostacolo piuttosto che un miglioramento.
Per disattivare l'utilizzo della Query Cache per singole Queries è sufficiente inserire il comando "SQL_NO_CACHE" subito dopo il comando "SELECT" della nostra query, ad esempio:
SELECT SQL_NO_CACHE * FROM mygreattable;
Conclusioni
La funzione Query Cache di MySQL sicuramente non risolve tutti i problemi di performance del vostro database per i quali potrebbe essere necessario l'utilizzo di indici ottimizzati sulle tabelle e di queries più efficienti, ma aiuta sicuramente in parecchi ambienti di sviluppo che, caraterrizati da una predominanza di operazioni di lettura, beneficeranno dell'utilizzo di un sistema di caching integrato nel DBMS.