[mysql] Aumentare le performance del database MySQL

Questo esempio è sviluppato su un server Ubuntu con le seguenti caratteristiche:

32GB di RAM @2133 MHz, CPU Intel Xeon E3-1270v6 – quad core, 3,8GHz, 500GB disco SSD.

Le caratteristiche del server sono importanti, quanto il carico di lavoro che si intende sostenere per ottenere una giusta configurazione.

Prima di procedere oltre valutiamo anche lo stato iniziale del server ed eventualmente monitoriamo il carico di lavoro, per capire quanto margine abbiamo per le operazioni di calibrazione. A tale scopo ci saranno utili 2 programmi di Ubuntu.

Anzitutto usiamo htop per verificare l’uso di risorse della nostra macchina.

Se non dovessimo averlo installo eseguiamo:

Eseguendo htop da terminale dovremmo vedere una situazione simile a questa:

In questo caso possiamo notare che il server è scarico (in questo preciso momento).

Altro strumento che voglio utilizzare è mysqltuner. Questo ci permetterà di monitorare la situazione del server MySQL. Prima di lanciarlo è utile lasciare il server operativo per un tempo sufficiente (si parla di giorni o settimane, dipende dalla variabilità nell’utilizzo).

Se non disponiamo di mysqltuner installiamolo eseguendo:

Una volta eseguito, digitando semplicemente:

Vedremo un output con diverse statistiche e in fondo un elenco di raccomandazioni simile a questo:

A questo punto procediamo a modificare il file /etc/mysql/my.cnf impostando nuovi parametri (e poi provandoli).

Digitiamo dunque:

Andiamo ad impostare i seguenti parametri, di cui ne analizziamo alcuni:

innodb_buffer_pool_size

Questo parametro ci permette di impostare il pool del buffer. Il valore deve essere un multiplo del parametro innodb_buffer_pool_chunk_size moltiplicato per il parametro innodb_buffer_pool_instances. Il valore predefinito di innodb_buffer_pool_chunk_size è di 128MB, mentre innodb_buffer_pool_instances è impostato su 16.

Questo significa che un valore di 8GB per innodb_buffer_pool_size va bene, perché 8GB = 8192MB da cui 8192MB / ( 16 * 128MB ) = 4 (un numero intero).

Invece un valore di 15GB non andrebbe bene, perché 15GB = 15360MB da cui 15360MB / ( 16 * 128MB ) = 7,5 (un numero decimale).

query_cache_size

Il MySQL può tenere in cache le query già utilizzate, aumentando così notevolmente la velocità di esecuzione e le performance.

Anzitutto verifichiamo che la cache sia abilitata usando il commando:

Dopodiché possiamo impostare le seguenti variabili:

Le specifiche dimensioni dipendono dalla nostra disponibilità di RAM sul server.

max_connections

Questo parametro ci permette di stabilire il numero massimo di connessioni. Il valore predefinito è 150. Aumentando questo valore aumenterà il consumo di RAM.

Attenzione! Durante queste configurazioni assicuriamoci che la stima di memoria utilizzata da parte di mysqltuner non superi la massima memoria disponibile nel sistema, come in questo caso:

Entrambi i valori dovrebbero risultare OK, come nel seguente esempio:

Per fare una stima in anticipo sulle prestazioni possiamo usare il MySQL Calculator su questo indirizzo.

La precedente stima si ottiene sulla seguente configurazione di parametri:

Aumentando i valori naturalmente si aumenterà la quantità di risorse potenzialmente consumate dal database.

Una volta finite le modifiche al file my.cnf riavviamo il servizio MySQL digitando:

Come ultima operazione ottimizziamo il database, lanciando il seguente commando:

Se stiamo usando Plesk possiamo anche usare:

In questo modo la password dell’utente admin del database verrà prelevata dal file di configurazione di Plesk.

Fatto tutto questo monitoriamo il sistema e apportiamo eventuali correzioni.

Vedi articolo