Database MySQL, 10 trucchi per migliorarne le performance e la stabilità

 sveliamo dei semplici segreti che molti amministratori di database MySQL usano per migliorare le performance delle proprie basi dati.

Il database è la memoria del nostro sito internet o applicativo e ci permette di offrire contenuti dinamici in tempo reale ai nostri utenti.
Ottimizzare il database del proprio sito internet è un’operazione estremamente complessa e condizionata da tante variabili.

Se gestiamo applicazioni o siti che devono supportare numerosi accessi simultanei oppure gestiamo una base di dati che nel tempo ha assunto una elevata complessità (nell’ordine di qualche centinaia di migliaia di righe) è possibile avere un degrado nelle prestazioni, che di solito può essere risolto generalmente ottimizzando il DB.

Quelli che seguiranno sono 10 trucchi che aiutano a migliorare ed ottimizzare le performance di un DB MySQL e/o Percona Server basato su motore InnoDB (praticamente tutti quelli dei maggiori CMS, come WordPress, Moodle, Prestashop, Magento, Joomla, ecc.).

Parametri MySQL da modificare/configurare per un database performante

1.      Configurare il parametro innodb_buffer_pool_size

Il parametro più importante da modificare immediatamente è innodb_buffer_pool_size ed è quello che indica quanta memoria può essere usata per memorizzare in RAM i dati (e quindi evitare accessi al disco continui). Che valore dare dunque al buffer?

Mettiamo che abbiamo un server su cui risiede sia il webserver che Mysql, con 8 giga di ram: dedichiamo a innodb_buffer_pool_size 4GB

Per essere più chiaro e meno ripetitivo faccio una tabella

TIPOLOGIA DI SERVER	RAM A DISPOSIZIONE	RAM DA DEDICARE
Webserver+mysql	8GB RAM	4GB RAM
Webserver+mysql	16GB RAM	10GB RAM
DBserver dedicato	6GB RAM	5GB RAM
DBserver dedicato	12GB RAM	10GB RAM
DBserver dedicato	24GB RAM	20GB RAM

2.      Configurare il parametro innodb_log_file_size

Questa proprietà definisce la grandezza massima del file di log per tabelle InnoDB. Pur essendo vero che maggiore è la dimensione, migliori sono le prestazioni, è anche vero che bisogna fare i conti con le dimensioni del server. A seconda di tale considerazione si consiglia un tuning di questa variabile tra i 512 e i 4GB.

3.      Configurare il parametro max_connections

Un numero massimo di connessioni disponibili inferiori al numero di connessioni correnti impedirà agli utenti di accedere al database, rendere il vostro sito inaccessibile e / o lenta. Un numero molto elevato di connessioni disponibili per un numero molto basso di connessioni effettive farà server MySQL richiedono più RAM rispetto effettivamente necessario. Il problema di valori troppo alti (oltre i 1000) è il possibile crash del server che si trova a dover gestire contemporaneamente oltre 1000 transazioni attive.

4.      Configurare il parametro innodb_file_per_table

Questa impostazione serve per indicare a MySQL se si vuole conservare i dati e gli indici in un unico tablespace (impostazione su OFF) o in un file IBD separato per ogni tabella (impostazione su ON). Il consiglio è di impostare il parametro su ON (valore di default per MySQL 5.6) soprattutto se si effettuano operazioni di cancellazione e ricostruzione tabelle od operazioni di compressione, che permettono di recuperare spazio. Evitare però di usare l’impostazione su ON qualora si abbia a che fare con un database dal numero di tabelle davvero elevato (oltre 10 mila).

5.      Configurare il parametro innodb_flush_log_at_trx_commit

Se sembra che InnoDB sia più lento di MyISAM è possibile che questo valore sia stato configurato male. Il default (1) significa che ad ogni commit di una transizione (o statement fuori dalla stessa) necessita di fare flush del log su disco, un’operazione piuttosto costosa se molto frequente. Settare il valore a 2 significa fare il flush del log solo tramite cache del sistema operativo. Il valore 0 è più veloce, ma è rischioso perchè si possono perdere dati durante le transizioni nel caso di un crash di MySQL Server. Anche il valore 2 è rischioso se il sistema operativo va in crash.

6.      Configurare il parametro innodb_flush_metod

Sui sistemi Linux e simili (FreeBSD, Solaris…) c’è inoltre il problema di evitare il doppio buffering 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.

7.      Configurare il parametro innodb_log_buffer_size

Il default per questa variabile è 1MB e per sistemi con poche transazioni ed un traffico medio potrebbe bastare. Non va settato un valore troppo alto perchè è il buffer è flushato ogni secondo ed esagerare significherebbe sprecare memoria. Solitamente sono più che sufficienti 8-16 MB.

8.      Configurare il parametro query_cache_size

Utile per applicazioni con molti dati in lettura. Valori compresi tra i 256 e i 2048 MB sono i migliori, a seconda della potenza della vostra macchina.
Al momento in cui scrivo le considerazioni valgono per MySQL Server 5.5 (io le ho provate su MySQL Server 5.6) e sono comunque suggerimenti da utilizzare con criterio e in base alle possibilità della propria macchina.

9.      Configurare il parametro log_bin

Attivare il logging binario è necessario sia per far funzionare il server come replica di un database server master, sia se sul server (in configurazione singola) si vuole abilitare il sistema di backup automatico. Logga le istruzioni che scrivono dati in formato binario. L’argomento opzionale deve essere il nome del log. Se non è specificato, verrà usato datadir/’log-basename’-bin o ‘datadir’/mysql-bin (il secondo solo se --log-basename non è specificato). Si raccomanda caldamente di usare --log-basename o specificare un nome file per essere sicuri che la replica non si arresti nel caso in cui il nome host del server cambi.

10.  Configurare il parametro skip_name_resolve

MySQL, per i client che si collegano da remoto, mantiene una cache dove memorizza numero IP, nome host ed altre informazioni. Per far ciò il server tenta una risoluzione DNS IP->host name.
Se i DNS interni alla LAN non forniscono un reverse DNS lookup (PTR records) allora è probabile sperimentare un rallentamento nelle connessioni. Infatti MySQL tenterà ogni volta di reinserire nella cache il nome host, processo inevitabilmente destinato a fallire con un timeout.
Per disabilitare il DNS host name lookup è sufficiente far partire MySQL con l’opzione –skip-name-resolve. Basta modificare il file di configurazione my.cnf o my.ini

Miglioriamo le performance di MySQL Server ottimizzando e riparando le tabelle.

MySQL non ha purtroppo  la sana abitudine di ottimizzare le tabelle qualora ve ne sia bisogno.

Per ottimizzazione delle tabelle si intende la riduzione dei byte in eccesso che si vengono a verificare quando un campo di un qualsiasi record viene modificato oppure quando un record viene cancellato.

Ad esempio, se un campo contiene del testo d occupa 100 byte e poi il campo viene modificato ed occuperà 90 byte, c’è il rischio di trovarsi con 10 byte in eccesso.

Stesso discorso quando un record viene cancellato: se la tabella occupa 1000 byte e viene cancellato un record che ne occupa 150, ci troveremo con una tabella di 850 byte col rischio di averne 150 in eccesso.

MySQL mette a disposizione l’istruzione OPTIMIZE TABLE il cui compito è quello, appunto di ottimizzare la tabella e compattare i dati.

Per far ciò basta usare lo strumento Mysqlcheck, in questo modo:

mysqlcheck –u root –p —auto–repair –c –o nomedatabase