ottimizzare MySQL con mysqltuner

La corretta interpretazione dell'output di mysqltuner è fondamentale per comprendere come sta lavorando il nostro server MySQL; per sfruttare al massimo le informazioni ottenute però è necessaria anche una certa conoscenza dei parametri di configurazione di MySQL e in generale del suo funzionamento interno.

AVVERTENZA IMPORTANTE!!! modificare i parametri di configurazione del server MySQL può influire pesantemente (e disastrosamente) sulle prestazioni del server stesso e del sistema su cui è in funzione. Se possibile fate sempre le prove su un sistema di test il più simile possibile a quello di produzione.

Prima di iniziare, ricordate che l'ottimizzazione di MySQL è un processo che avviene su diversi livelli — applicazione, query, configurazione del server, hardware. Questo articolo non tratta in generale di ottimizzazione di MySQL e non farà di voi dei DBA esperti. Il mio obiettivo è mettervi in grado di interpretare correttamente l'output di un programma di analisi del funzionamento di MySQL, ho preso mysqltuner come esempio ma i principi esposti valgono per qualsiasi programma usiate; come obiettivo correlato mi propongo di consentirvi di applicare alcune ottimizzazioni dipendenti dai dati rilevati, e di prendere spunto dagli argomenti che saltano fuori man mano per segnalarvi una serie di letture di approfondimento. Spero che nel complesso troviate questo articolo uno spunto interessante e una buona base di partenza per delle stimolanti escursioni nel mondo di MySQL.

iniziamo

Iniziamo quindi ad esaminare un output tipico di mysqltuner voce per voce, spiegando il significato dei dat riportati e suggerendo le strategie tipiche da seguire per risolvere i vari problemi che si possono presentare.

[--] Data in MyISAM tables: 29K (Tables: 12)
[--] Data in InnoDB tables: 103M (Tables: 352)

Ecco un primo dato interessante, mysqltuner ci comunica la quantità di dati che stiamo archiviando divisa per motore. Subito dopo ci segnala il livello di frammentazione del database, un valore che può diventare anche molto alto e in quel caso può costituire un problema.

[!!] Total fragmented tables: 352

In questo caso ad esempio abbiamo un livello di frammentazione elevato (troppo!) infatti tutte le tabelle InnoDb del nostro server sembrano frammentate. Cosa sono le tabelle frammentate? Esattamente quello che dice il nome stesso; poiché i dati vengono salvati dai motori di memorizzazione su file, man mano che si procede con le scritture, le cancellazioni e le riscritture la struttura interna di questi files inizia a diventare un caos — sempre per usare termini tecnici. Per ovviare a questo problema abbiamo a disposizione alcuni strumenti, principalmente si tratta di lanciare una query di tipo OPTIMIZE TABLE sulle tabelle frammentate, che però avrà un effetto leggermente diverso sulle tabelle InnoDb. Ora visto che il lavoro può risultare alquanto tedioso di solito i bravi DBA si preparano uno scriptino che fa il lavoro al posto loro, una roba tipo questa:

#!/bin/bash

myRootPw=lavostrapassword

mysql -u root --password="$myRootPw" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do

    mysql -u root --password="$myRootPw" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do

        case "$engine" in

            "InnoDB")

                mysql -u root --password="$myRootPw" -NBe "ALTER TABLE $name ENGINE=INNODB;" "$database"

            ;;

            "MyISAM")

                mysql -u root --password="$myRootPw" -NBe "OPTIMIZE TABLE $name;" "$database"

            ;;

        esac

    done

done

Non aspettatevi miracoli ma dovrebbe funzionare abbastanza bene. La deframmentazione delle tabelle InnoDb funziona in maniera diversa da quella delle tabelle MyISAM, per una trattazione più approfondita leggetevi questo bell'articolo sull'argomento. Sempre sull'argomento frammentazione c'è anche questo interessante articolo di Vadim Tkachenko. Insomma, ora la cosa dovrebbe esservi chiara... andiamo avanti!

[--] Total buffers: 306.0M global + 130.6M per thread (30 max threads)
[!!] Maximum possible memory usage: 4.1G (106% of installed RAM)

Queste due righe vi informano del consumo teorico possibile del server MySQL a partire dalla configurazione attuale. Ci sono diversi parametri che concorrono a formare questo valore, ma in particolare va tenuto d'occhio il numero massimo di thread che è un fattore critico nel calcolo. La memoria massima per thread infatti va moltiplicata per il numero massimo di thread consentiti e quando ci sono di mezzo le moltiplicazioni si fa presto a trovarsi in mano dei numeri alti.

I valori che, sommati, danno il totale della memoria occupata globalmente sono key_buffer_size, innodb_buffer_pool_size, innodb_additional_memory_pool_size, innodb_log_buffer_size, query_cache_size. I valori che formano la memoria occupata da ogni singolo thread sono invece thread_stack, read_buffer_size, sort_buffer_size, read_rnd_buffer_size,  join_buffer_size, tmp_table_size, net_buffer_length (quest'ultima limitata da max_allowed_packet). Per maggiori dettagli, rimandiamo a questo articolo di Peter Zaitsev. Ricordate che le variabili di buffer (read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size) vengono allocate se e quando serve e liberate dopo l'uso. Inoltre ricordate anche che se le dimensioni dei risultati superano una certa soglia, vengono create tabelle temporanee su disco invece che tabelle heap.

Per evitare problemi (anche seri) di stabilità del sistema la memoria teorica totale allocabile da MySQL non dovrebbe superare mai quella a disposizione sul sistema — meglio se nei conteggi calcolate anche un po' di spazio per il sistema e per eventuali altri demoni in funzione. In altre parole è opportuno attenersi alla seguente formula:

RAM disponibile = Buffer Globali + (Buffers per Thread x max_connections)

ovvero

max_connections = (RAM disponibile - Buffer Globali) / Buffers per Thread

Il che, tradotto in pratica, significa che se volete un server di database più veloce dovete aumentare la RAM. Ma se aumentate la RAM per sfruttarla dovete usare più thread. Ma se aumentate i thread vi serviranno probabilmente più core quindi dovete cambiare processore. Cambiare il processore spesso richiede però un cambio di scheda madre. Insomma tenetevi il server così com'è o cambiatelo in blocco che fate prima.

Il successivo valore ci dice quante query lente sono state eseguite in percentuale sul totale, questo più che un indicatore di come sta lavorando il server spesso è più che altro una misura di quanto male hanno lavorato i programmatori.

[OK] Slow queries: 0% (49/404K)

Le leggendarie «query lente» sono quelle identificate come tali dal parametro long_query_time che stabilisce in pratica dopo quanti secondi una query può essere considerata «lenta». Fissare un valore troppo basso per questo parametro non servirà ad altro che ad alimentare la vostra paranoia (e, se avete attivato il log delle query lente con log_slow_queries, anche le dimensioni del file di log). Il mio consiglio è di partire con un valore relativamente alto e vedere quali sono le query peggiori, sistemare quelle, aspettare un po', abbassare il valore di un secondo, ripetere la scrematura, e così via. Una query può essere lenta per mille motivi e non è questa la sede per discuterne, vi rimando a questo articolo sull'argomento, molto ben fatto, oppure a questo (in PDF) che però è un po' più tecnico.

[OK] Highest usage of available connections: 80% (36/45)

Questo indicatore vi dice se il numero massimo di connessioni che avete impostato è sufficiente o se il server ha dovuto rifiutare delle richieste. Anche se sembra una cosa abbastanza elementare, non sottovalutate le implicazioni che questo valore ha sulle performances. Le connessioni rifiutate infatti generano solitamente dei problemi alle applicazioni che le richiedono, e nella migliore delle ipotesi delle reiterazioni della richiesta; se il server è molto occupato questo può anche portare a un sovraccarico di rete — e in tutti i casi a nulla di buono. Perché non impostare un valore altissimo allora? In effetti perché è tutto fuorché una buona idea, il server non dovrebbe poter accettare più connessioni di quelle che è materialmente in grado di gestire, pena altri problemi di performances; quindi appare evidente come il numero di connessioni consentite e il numero di thread siano strettamente legati. Per approfondire la cosa e per avere utili indicazioni su come calcolare il valore ottimale di max_connections date un'occhiata alla spiegazione su come MySQL apre e chiude le tabelle presente nella documentazione ufficiale.

Il classico errore ritornato al lato applicativo da un server sovraccarico di connessioni è «too many connections» del quale trovate una dettagliata spiegazione nella documentazione ufficiale di MySQL.

[OK] Key buffer size / total MyISAM indexes: 16.0M/167.0K
[OK] Key buffer hit rate: 100.0% (7M cached / 44 reads)

Le due righe qui sopra si riferiscono all'efficienza del buffer delle chiavi MyISAM, controllato dalla variabile key_buffer_size. Ora fermiamoci un attimo su questo punto. Senza entrare nelle dinamiche del caching in sé (se avete curiosità in merito leggete questo capitolo della documentazione ufficiale), diciamo che si tratta di un meccanismo molto importante per quanto riguarda le performances del server; su una macchina dedicata a MyISAM questo valore dovrebbe essere piuttosto alto (dipende da quanta RAM avete — se possibile alzatelo finché mysqltuner vi suggerisce di farlo) ma non trascuratelo anche in caso di database prevalentemente InnoDB in quanto ci sono sempre le tabelle temporanee che hanno le loro esigenze.

[!!] Query cache efficiency: 0.8% (900K cached / 111M selects)
[OK] Query cache prunes per day: 0

Il caching delle query è tutta un'altra storia; qui si tratta di salvare in cache il risultato delle query in modo da non doverle ripetere e se nella vostra applicazione ci sono query che si ripetono spesso questo può portare a degli incrementi di performance anche significativi — attenzione però che il caching più significativo spesso va fatto sul lato applicativo, non affidatevi troppo al server MySQL che ha anche altro da fare oltre ad occuparsi di questo. Inoltre il caching delle query è praticamente inutile su db nei quali i dati cambiano spesso; per i dettagli sul caching delle query leggete la relativa sezione della documentazione dopodiché vi sarà chiaro perché a volte conviene disabilitare questa cache.

La variabile che determina la dimensione della cache è query_cache_size mentre la variabile query_cache_type indica il comportamento del sistema di caching (zero sta per nessuna cache, uno richiede che vengano salvati tutti gli statement eccetto quelli del tipo SELECT SQL_NO_CACHE, due richiede invece che vengano salvati solo gli statement del tipo SELECT SQL_CACHE). Quando la cache si riempie del tutto viene svuotata completamente (pruning) il che non è proprio un granché a livello di prestazioni; se avviene troppo spesso considerate di aumentare il valore di query_cache_size oppure di utilizzare più statement di tipo SQL_NO_CACHE ad esempio — oppure implementate una cache decente nella vostra applicazione. Ulteriori indicazioni su come configurare la cache sono in questa sezione della documentazione. Inoltre se proprio vi sentite geek c'è anche un articolo piuttosto dettagliato sul blog di David Lutz che tratta di cache hit e dimensionamento della cache e infine vi segnalo anche questo post sul blog di Racker Hacker.

[OK] Sorts requiring temporary tables: 0% (619 temp sorts / 6M sorts)

Ok, è ora di leggersi come MySQL utilizza la memoria. Se vi è tutto chiaro, avrete anche capito che i risultati molto grandi finiscono su disco per essere gestiti da MyISAM; quando ciò avviene è determinato dai parametri tmp_table_size e max_heap_table_size, e in particolare il primo determina quando una tabella temporanea interna deve essere scritta su disco mentre il secondo riguarda specificamente le tabelle heap, ovvero create con lo storage engine MEMORY (i dettagli in questo capitolo della documentazione).

[OK] Thread cache hit rate: 99% (985 created / 129K connections)

Visto che anche l'apertura dei thread richiede una certa quantità di risorse, anche i thread possono essere salvati in cache per poi venire riutilizzati. La logica con cui MySQL utilizza i thread è spiegata molto chiaramente nella documentazione; la variabile che dimensiona la cache dei thread è thread_cache_size e in particolare specifica il numero di thread da conservare (quindi settarla a 0 equivale a disabilitare la cache). Un esempio pratico di come una cattiva configurazione della cache dei thread può influenzare le performances del database si trova in questo articolo. Idealmente, dovreste aumentare questo valore fino ad avere un hit rate superiore al 50%, ma fate incrementi graduali e verificate il carico del server man mano.

[OK] Table cache hit rate: 31% (512 open / 1K opened)

Anche l'apertura delle tabelle richiede risorse e quindi un buon funzionamento di questa cache garantisce performances migliori. Un valore eccessivamente alto tuttavia rischia di avere un effetto negativo, vi suggerisco di leggere questo e questo post del MySQL performances blog. La dimensione (il numero di tabelle che si possono mantenere in cache) è determinata dal parametro table_cache. Il lavoro della cache delle tabelle è anche collegato alla struttura del filesystem sottostante; in particolare questa sezione della documentazione spiega la correlazione fra dimensione della cache delle tabelle e numero massimo di connessioni, e come una configurazione imprudente di questi valori possa causare l'esaurimento dei descrittori di files e il conseguente rifiuto di nuove connessioni.

[OK] Open file limit used: 6% (68/1K)

Se avete letto i riferimenti dati sopra, questo dovrebbe esservi abbastanza chiaro. Il valore di default (1024) va bene per la maggior parte delle situazioni, ma se il valore corrente inizia ad avvicinarsi al limite cominciate — gradualmente — ad aumentare il valore della variabile open-files-limit per evitare un errore del tipo «troppi files aperti».

[OK] Table locks acquired immediately: 100% (4M immediate / 4M locks)

Il lock delle tabelle può diventare un collo di bottiglia se le vostre applicazioni passano molto tempo ad aspettare di poter accedere ai dati; non ci sono molte ottimizzazioni che si possono fare sul db in caso di problemi collegati all'acquisizione dei lock; si tratta più che altro di questioni che riguardano l'applicazione — per esempio se richiedete un lock esclusivo per fare un'operazione molto lunga, forzerete tutte le altre istanze applicative che devono accedere ai dati bloccati ad aspettare. Se mysqltuner vi segnala un problema sull'acquisizione dei lock fate un esame attento della vostra applicazione e cercate di capire se potete fare qualcosa per ridurre al minimo i tempi di lock. Qui un po' di cultura non guasta (ma dov'è che guasta, diciamocelo) per cui leggetevi questo capitolo della documentazione; inoltre vi suggerisco questo articolo del database journal in cui si parla di pro e contro dei lock, e quest'altro tratto da html.it che parla di transazioni MySQL in generale.

[OK] InnoDB data size / buffer pool: 69.3M/128.0M

Concludiamo la nostra lunga cavalcata attraverso l'output di mysqltuner con un punto di fondamentale importanza, ovvero il funzionamento del buffer di InnoDB. Il rendimento di questo motore di memorizzazione infatti è strettamente correlato al buon funzionamento del suo buffer — motivo per cui in una macchina dedicata principalmente a InnoDB non dovreste lesinare troppo sulla memoria che gli dedicate. Questa è determinata dalla variabile innodb_buffer_pool_size e il valore di default (8 Mb) è solitamente troppo basso per qualsiasi impiego sensato in produzione.

Sui sistemi Linux e simili (FreeBSD, Solaris...) c'è inoltre il problema di evitare il doppio buffering — sto parlando della swap — e per farlo occorre settare la variabile innodb_flush_method a O_DIRECT. Fatelo se vi accorgete che il vostro sistema swappa più del previsto quando il db è sotto stress. Per concludere vi rimando a questo articolo sull'ottimizzazione delle performances di InnoDB, a quest'altro che parla specificamente del dimensionamento del buffer di InnoDB e a questo capitolo della documentazione dedicato all'argomento.

...e se non ho mysqltuner?

Potete usare qualsiasi programma per rilevare dati analoghi a quelli prodotti da mysqltuner. Se non ne avete già adottato uno, vi consiglio caldamente di installare e imparare ad usare MySQL Workbench, che oltre a consentirvi di monitorare il funzionamento dei vostri server vi farà risparmiare molta fatica nell'amministrazione dei vostri database.

conclusioni

So che speravate che la bibliografia fosse finita, invece vi tocca una bella coda di letture da fare. Vi voglio innanzitutto segnalare questo articolo sulle principali cose che vanno sistemate in una nuova installazione di MySQL; infine, come riferimento pratico e cultura generale dovete assolutamente leggere questa, questa e questa sezione della documentazione.

Direi che è tutto. Se avete letto tutto quanto e siete sopravvissuti, cominciate a fare delle prove sui sistemi di test e verificate quanto avete appreso. Se tutto va bene dovreste notare dei miglioramenti, e avrete fatto un piccolo passo avanti nella vostra conoscenza di MySQL. Se volete farne un'altro, e vi consiglio caldamente di farlo, allora è tempo di smetterla di smacchiare i leopardi e leggere per intero il capitolo del manuale di MySQL dedicato all'ottimizzazione.

Con questo ho davvero finito per ora... buon MySQL tuning a tutti!