Ugo Giaquinto’s Web Log

Tuning, ovvero ottimizzazione, di database MySql

Posted on May 24th, 2007 by admin

Ecco una breve guida all’ottimizzazione del database MySql 4. E’ rivolta a tutti quelli che come il sottoscritto, magari abituati a Oracle o Ms Sql Server hanno iniziato da zero la propria battaglia contro / al fianco di un database gratuito, semplice, ma non sempre prestantissimo come MySql. In particolare gestendo un server con decine di siti e soprattutto un forum PhpBB frequentatissimo, spesso accadeva che la navigazione su quest’ultimo risultasse estenuante. Niente paura rimbocchiamoci le mani e studiamo le aree di intervento.

Iniziamo da un fattore hardware, la Ram, il fenomeno da scongiurare è evidentemente lo swapping, ovvero l’utilizzo del disco fisso (molto più lento della memoria volatile) per lavorare sui dati da estrarre / inserire nel database. Non allarmatevi però, MySql a differenza di Oracle e Ms Sql Server non è esoso. In particolare si parla di un massimo di 1GB di memoria per database enormi su database server dedicati, 512 MB per database grandi ma tipicamente 256MB per sistemi con database medi e web server in esecuzione oltre al db. Poi ad aggiungere Ram si fa sempre in tempo, ma prima provate a configurare a dovere i parametri software, vedrete dei significativi cambiamenti.

Il file di configurazione di MySql è il my.cnf, e oltre ad esso ce ne sono 4 preimpostati per rispondere alle esigenze tipiche che ho appena elencato:

my-huge.cnf raccomandato per sistemi con almeno 1GB di memoria che agiscono principalmente come Database server. Se il web server gira sulla stessa macchina non è il file che fa per voi.
my-large.cnf raccomandato per sistemi con meno memoria (512MB), ma che comunque eseguono principalmente MySql.
my-medium.cnf raccomandato per sistemi dove MySQL e un Web server sono eseguiti assieme con circa 128MB di Ram, o con 64MB di Ram per il solo MySQL.
my-small.cnf, per sistemi con meno di 64MB.

Questi sono i punti di partenza, scegliete quello che fa più al caso vostro, rinominatelo in my.cnf riavviate il servizio e il gioco è fatto.

poi passiamo all’analisi dei log del database, per raccoglierli e valutarli io uso PhpMyAdmin:

phpmyadmin tuning mysql Tramite questi link è possibile verificare i parametri di configurazione e valutare il loro effetto nel tempo. Ho detto “nel tempo” per cui per prima cosa non fatevi prendere dalla fretta, non fate modifiche se ritenete di non poter aspettare qualche giorno per stimarne l’efficacia, le prestazioni di un db si valutano anche sulla sua capacità di porre in cache i dati, per cui dobbiamo dargliene il tempo. Infatti negli esempi che vi porterò qui sotto considerate questo dato:

mysqld avviato da giorni

Vediamo ora nel dettaglio i parametri più significativi:

key_buffer_size

E’ probabilmente il primo parametro da settare e uno dei più importanti. Semplicemente setta la dimensione di memoria da riservare agli indici. Più è alta più indici rimangono in memoria. Siccome spesso le query agiscono sugli stessi indici e l’accesso alla memoria è molto più veloce di quello al disco, la sua importanza è vitale. Occhio però a non esagerare, se impostiamo valori troppo alti per questo e altri parametri e superiamo il limite della Ram libera ecco che crediamo di usare la Ram e finiamo per riusare il disco con effetti devastanti. Ed ecco allora che dobbiamo settare un valore e verificare i risultati dopo qualche giorno.

Partiamo ad esempio con circa un terzo della memoria Ram totale. Dovrebbe bastare a contenere tutti gli indici. Verifichiamo allora i seguenti due valori:

Key_read_requests = numero di richieste di lettura
Key_reads = numero di letture (da disco)

se al crescere del primo numero il secondo dopo qualche giorno non cresce più o perlomeno si mantiene di tre ordini di grandezza inferiore è tutto ok, se invece cresce quasi di pari passo evidentemente è il caso di aumentare il key_buffer_size e se non c’è Ram per farlo è necessario un upgrade hardware.

nel caso del mio server il parametro è settato a 75MB su 512 di memoria totale (intanto che scrivo verifico anche io) e le cose vanno bene anche se sono un po’migliorabili :

key read request key reads mysql

1/632 , cercherò di arrivare a 1/1400 circa, il doppio aumentando il valore del parametro.

table_cache

ovvero il numero di tabelle da tenere in cache. Inutile spiegare l’importanza di avere tabelle in cache, ovviamente è molto più veloce. Anche qui il limite è ovviamente la Ram, non mi ripeterò con discorsi di caching e swapping.

Come valutare se il valore (nel mio caso 1000) è soddisfacente? Ovviamente guardandone l’effetto ancora una volta sul parametro:

Open_tables = tabelle aperte al momento in cache

Conviene osservare il sistema col db sotto stress: se il valore di open_tables non raggiunge quello impostato per table_cache è tutto ok, il sistema non è saturo, si può forse persino ridurre il valore di table_cache se ci sono margini grossissimi.

Se invece il valore di open_tables è identico a quello di table_cache allora abbiamo margini di manovra (Ram permettendo) e possiamo intervenire aumentando il valore di table cache. Nel mio caso entrambi i valori sono a 1000 per cui proverò ad aumentare il parametro table_cache.

sort_buffer

buffer per le operazioni di ordinamento, di solito impostato a 2MB si può alzare a piacimento (Ram permettendo).

read_rnd_buffer_size

è usato per le letture dopo le operazioni di ordinamento. Se usiamo parecchi ORDER BY nel codice può essere utile aumentarlo. Attenzione però che il valore è riferito ad un solo thread! Conviene impostarlo di solito a un millesimo dell’ammontare totale di Ram, nel mio caso quindi 512 KB. Per cui avendolo impostato a 2MB provvederò senzaltro a ridurlo per guadagnare risorse rispetto alle altre modifiche sopra indicate.

tmp_table_size

indica la massima dimensione per una tabella temporanea in memoria. Se la tabella diventa troppo grande verrà creata sul disco. Anche in questo caso la memoria è allocata per thread. L’effetto di un aumento in questo caso dovrebbe essere sensibile, per cui provate sempre in funzione della Ram libera. Valori tipici sono di qualche decina di MB, dipende molto dalla memoria disponibile.

Già, ma quanta memoria libera abbiamo sul server, ovvero che margini di manovra abbiamo? Controlliamo subito!

free memory amount

col comando free scopriamo che ci sono di 512MB ben 52MB liberi ma che il sistema ha comunque iniziato a swappare per qualcosa di evidentemente più grande (195MB). Allora capisco di non avere molto margine per aumentare indiscriminatamente i parametri a mia disposizione.

Visti i numeri in ballo credo proprio che diminuirò il read_rnd_buffer_size (da 2Mb a 512KB) e ritoccherò al rialzo gli altri parametri, ovvero table_cache da 1000 a 2000, da 75MB a 120MB per key_buffer_size e da 134MB a 200MB per tmp_table_size.

In un prossimo post fra qualche giorno vi farò sapere com’è andata, non siate impazienti