Ottimizzare il caching delle query in MySQL

I siti Web che utilizzano un database per l'archiviazione e la manipolazione delle informazioni inviano richieste sotto forma di query ad un DBMS, come per esempio MySQL, ognuna di queste interrogazioni ha bisogno di risorse per poter essere eseguita e le risorse possono essere prelevate dal sistema che gestisce la macchina ospitante, migliore sarà la configurazione hardware della postazione maggiore sarà il livello delle prestazioni fornite durante la soddisfazione delle richieste.

Ma soprattutto nei siti Web ad alto traffico, accade che le richieste (anche simultanee) inviate al DBMS possano essere numerose, incidendo sulla capacità di risposta del Web server stesso, causando rallentamenti e, nei casi più gravi, blocchi del sistema.

Una delle soluzioni messe a disposizione da MySQL per il problema appena esposto è quella relativa al caching delle query: un meccanismo grazie al quale il DBMS è in grado di memorizzare per un certo intervallo di tempo i risultati restituiti dalle interrogazioni, in modo da poterli riproporre senza ripetere ad ogni richiesta tutta la procedura necessaria per rispondere alle query.

Il caching delle query permette nello stesso tempo di risparmiare risorse e di evitare sovraccarichi, in questa trattazione verranno fornite alcune indicazioni per l'ottimizzazione di tale meccanismo.

Abilitazione del query caching in MySQL

La possibilità di utilizzare una cache per la memorizzazione delle query è stata introdotta con la versione 4 di MySQL, in modo da dotare questo DBMS di una funzione che era già disponibile per altri database ; la procedura per l'abilitazione della query cache è molto semplice e prevede di apportare alcune semplici modifiche a carico del file di configurazione di MySQL, my.conf per i sistemi operativi Unix e Linux e my.ini per i sistemi Windows.

Le direttive da modificare all'interno di questo file sono due, denominate rispettivamente query_cache_type e query_cache_size; la prima direttiva accetta un parametro numerico che attiva o meno la cache, se per esempio si desidera abilitare la cache questo parametro dovrà essere uguale a 1, diversamente il valore 0 stabilirà la disabilitazione della cache, inoltre, la direttiva accetta ON in luogo di 1 e OFF in alternativa a 0; la seconda direttiva, invece, ne definisce la dimensione espressa in MB.

Se per esempio si vuole abilitare la cache attribuendole una dimensione di 64 Mb, le due direttive esposte dovranno essere modificate nel modo seguente

query_cache_type = 1
query_cache_size = 64M

Già di per sé la modifica del secondo parametro rappresenta uno strumento valido per l'ottimizzazione della cache, ad esso infatti potrà essere associato un valore differente a seconda delle diverse esigenze o applicazioni da realizzare.

Una volta salvato il file di configurazione modificato, le nuove impostazioni non avranno effetto immediato, il Database manager dovrà infatti essere riavviato e solo dopo questa procedura sarà possibile avvalersi della cache per le query.

In quali casi non funziona la cache delle query

A questo punto è bene introdurre un primo accenno all'ottimizzazione che però non riguarda direttamente il DBMS ma le applicazioni che si interfacceranno ad esso; è bene infatti precisare che la cache si basa su un sistema di confronto tra le nuove query inviate dai client e le interrogazioni già eseguite da MySQL, per attivare la cache le nuove richieste dovranno essere identiche anche sintatticamente (rispettando maiuscole, minuscole, apici, spazi etc.) a quelle già effettuate. Per cui delle interrogazioni come le seguenti:

SELECT nome FROM Tabella WHERE id = 1;
Select nome from tabella Where id = 1;
select nome from tabella where id = 1;

saranno considerate tutte sintatticamente valide dal DBMS ma saranno considerate come query differenti dal meccanismo di caching. Se quindi si realizza un'applicazione che comunica con MySQL e questa invia le stesse query al DBMS da più parti, è buona norma tenere presente che le medesime interrogazioni dovranno essere scritte in modo identico (byte per byte).

A questo proposito, è importante sottolineare il fatto che anche query assolutamente identiche possono essere archiviate in modo diverso nella cache, infatti, interrogazioni che utilizzano database differenti, un differente protocollo o un diverso set di caratteri predefinito, verranno considerate differenti da MySQL che le archivierà separatamente in cache.

Inoltre, il meccanismo della cache non verrà attivato nel caso di:

• interrogazioni che sono subquery di altre query;
• query eseguite all'interno del corpo di un trigger, di una stored function o di un evento;
• utenti che non hanno associato alla loro username il privilegio di eseguire SELECT;
• query riferite a funzioni definite dagli utenti;
• query riferite a variabili dell'utente o a variabili di programmi locali;
• query riferite alle tabelle dei database di sistema.

Alterazioni a carico di tabelle comporteranno la rimozione dalla cache di tutte le query memorizzate riferite alle tabelle medesime. Ulteriori casi particolari di non utilizzo della cache potranno essere analizzati sulla base delle indicazioni fornite dalla guida ufficiale di MySQL.

Limiti delle query e deframmentazione della cache

L'utilizzatore ha a disposizione alcuni comandi appositamente introdotti per la verifica dello stato della cache, il primo è:

mysql> SHOW VARIABLES LIKE '%query_cache%';
+------------------------------+----------------------+
| Variable_name                | Value                |
+------------------------------+----------------------+
| have_query_cache             | YES oppure NO        |
| query_cache_limit            | (dimensione in byte) |
| query_cache_size             | (dimensione in byte) |
| query_cache_type             | ON oppure OFF        |
| query_cache_wlock_invalidate | ON oppure OFF        |
+------------------------------+----------------------+

Sono già state descritte in precedenza le caratteristiche delle variabili si sistema query_cache_type e query_cache_limit , oltre ad esse sono elencate:

• have_query_cache: indica se è disponibile la cache per le query;
• query_cache_limit: indica il limite di cache consentito per singola query (valore predefinito: 1Mb), in pratica rappresenta il valore relativo alla grandezza massima di una query registrata;
• query_cache_wlock_invalidate: (specifico per MyISAM) nel caso in cui le tabelle coinvolte da una query stiano subendo delle operazioni e siano in stato di "LOCK", impostando questo parametro su "1" i risultati memorizzati in cache vengono cancellati per evitare di ottenere risultati non più validi.

Il secondo comando disponibile è invece:

mysql> SHOW STATUS LIKE '%qcache%'
+-------------------------+----------------------+
| Variable_name           | Value                |
+-------------------------+----------------------+
| Qcache_queries_in_cache | (numero)             |
| Qcache_inserts          | (numero)             |
| Qcache_hits             | (numero)             |
| Qcache_lowmem_prunes    | (numero)             |
| Qcache_not_cached       | (numero)             |
| Qcache_free_memory      | (dimensione in byte) |
| Qcache_free_blocks      | (numero)             |
| Qcache_total_blocks     | (numero)             |
+-------------------------+----------------------+

In questo caso vengono restituite le impostazioni relative alle seguenti variabili di sistema:

• Qcache_queries_in_cache: fornisce il numero di query memorizzate nella cache;
• Qcache_inserts: rappresenta il numero di query aggiunte alla cache;
• Qcache_hits: mostra il numero di cache hits;
• Qcache_lowmem_prunes: indica il numero di query che sono state rimosse dalla cache in modo da liberare memoria per la registrazione di nuove query;
• Qcache_not_cached: restituisce il numero di query non presenti in cache perché, per esempio, non è stato possibile eseguirle;
• Qcache_free_memory: corrisponde alla quantità di memoria libera nella cache delle query;
• Qcache_free_blocks: rende disponibile il numero di blocchi di memoria liberi nella cache;
• Qcache_free_blocks: fornisce l'informazione relativa al numero totale di blocchi di memoria della cache.

Come anticipato, la globale Qcache_lowmem_prunes indica il numero di query che sono state cancellate dalla cache per fornire memoria libera all'allocazione di nuove interrogazioni; si tratta di un parametro estremamente importante ai fini dell'ottimizzazione in quanto consente di definire la dimensione della query cache necessaria per le diverse tipologie di applicazioni; l'eliminazione delle query dalla cache si basa su un meccanismo denominato LRU (least recently used) che consente di cancellare automaticamente le query meno utilizzate di recente.

Il valore di Qcache_lowmem_prunes subisce un incremento ogni qualvolta il DBMS elimina una query dalla cache per fare spazio ad una nuova interrogazione; se questo valore cresce troppo velocemente, si deve aumentare lo spazio disponibile per la cache, se invece non subisce variazioni è possibile decrementare il valore relativo al limite di spazio per la cache.

Incrementando il valore assegnato a query_cache_limit è possibile disporre di più spazio di memorizzazione per singola cache, in questo caso è però opportuno monitorare lo stato della variabile Qcache_lowmem_prunes che non deve essere mai troppo limitato.

Qcache_free_blocks restituisce l'informazione relativa alla frammentazione della cache, se il suo valore cresce in modo non proporzionato rispetto al numero totale dei blocchi, significa che la cache è frammentata rendendo possibili rallentamenti del sistema; per evitare fenomeni di questo genere è quindi opportuno lanciare il commando di deframmentazione: FLUSH QUERY CACHE. L'istruzione: RESET QUERY CACHE consentirà invece di svuotare la cache in qualsiasi momento.

Conclusioni

In questa breve trattazione è stato affrontato l'argomento relativo all'ottimizzazione della cache per le query di MYSQL; a questo scopo sono state analizzate le caratteristiche dei meccanismi di memorizzazione per i risultati delle interrogazioni, le direttive che presiedono al loro funzionamento e le impostazioni necessarie per ottenere un buon grado di ottimizzazione.