Ottimizzazione di MySQL

MySQL è il database usato da WordPress per memorizzare non solo il contenuto degli articoli e delle pagine ma anche i dati degli utenti, le impostazioni generali e molto altro.

Ogni volta che una pagina del blog viene visualizzata, WordPress fa parecchi accessi al database vuoi per recuperare parti delle intestazioni e del corpo(come il nome del blog, la descrizione, il link del feed…) e vuoi ovviamente per comporre il testo del post.

In momenti di sovraccarico del server, può capitare che appaia il messaggio “Error establishing a database connection”. Ciò segnala che una serie di cause ha reso indisponibile il database, qualora la RAM o la potenza del processore fossero sottodimensionati al bisogno del sito web è bene passare a una soluzione più avanzata.

Se si desidera ospitare un blog WordPress, che è molto esigente in termini di risorse, su Apache e MySQL sono consigliati almeno 384 MB di RAM. Si può provare anche con 256 MB, ma spesso sarà necessario riavviare il web server e il database o addirittura il sistema operativo.

Spesso, invece, capita che MySQL sia configurato male in modo che consumi eccessiva memoria e CPU. E’ assolutamente fondamentale evitare di usare la memoria swap del sistema operativo che degrada terribilmente le performance. La swap dovrebbe essere usata solo in caso di estrema necessità, personalmente consiglio di riavviare il sistema operativo dopo l’uso della swap (perchè significa che c’è qualcosa che non va, portebbe cuas di un attacco) e di aumentare la memoria principale del sistema.

Una delle prime cose da fare è disabilitare il supporto per InnoDB, che non è assolutamente necessario per WordPress ma è già attivato di default in moltissimi setup di MySQL.

Per disabilitare InnoDB, aprire il file /etc/mysql/my.cnf:

# nano /etc/mysql/my.cnf

Spostandosi in basso con le frecce cercare la riga

# skip-innodb

e togliere il cancelletto davanti, così la linea diventa

skip-innodb

Riavviare MySQL usando il comando (da eseguire ogni volta che sono modificati i file di configurazione):

# /etc/init.d/mysql restart

Vediamo alcuni dei punti cruciali di my.cnf

query_cache_limit
[ad#midarticlequadr]
query_cache_size

query_cache_limit va messa a tra 1M e 4M, inizialmente scegliere il valore più alto e poi abbassarlo in caso di uso della swap.

Il valore migliore per query_cache_size è il rapporto fra i megabyte di memoria RAM del server e 16, ossia RAM/16. Questo rapporto è valido quando sullo stesso server c’è in esecuzione Apache, mentre se il database è solo questo valore può essere alzato.

key_buffer

key_buffer è un parametro cruciale. Non deve essere superiore a un quarto della memoria principale del sistema operativo. Prima provare con 1/8 della dimensione della RAM, poi scendere o salire in base al risultato ottenuto col sistema sotto illustrato.

Il miglior valore si ottiene con un po’ di pratica: bisogna dividere key_reads per key_read_requests (cioè key_reads/key_read_requests) e key_writes per key_writes_request.

Se il rapporto delle letture è minore di 1/100 e quello delle scritture è minore di 1/10, è stato un appropriato valore per key_buffer.

In questa immagine a lato il rapporto di lettura è circa 0,001 mentre il rapporto di scrittura delle chiavi è 0,06. Queste informazioni sono reperibili via browser collegandosi al server dove è installato phpMyAdmin. Nel menù Informazioni di Runtime.

Il valore table_cache è sempre collegato alla dimensione della RAM. Dovrebbe essere scelto tra 3/4 e 1/2 della memoria ovvero RAM / (3/4) o RAM / (1/2).

Attenzione il valore non va espresso in megabyte.

Esempio: ho 512 MB di RAM posso scegliere 384 ( 512 / 1,333)  come table_cache, così la voce diventa table_cache=384.

thread_concurrency è una entry che dipende dal numero di processori disponibili sul server, tipicamente a thread_concurrency va attribuito il doppio del numero delle CPU. Esempio: ho un Core Duo 2 con 2 CPU allora assegno 4 a thread_concurrency.

max_connections va anch’esso scelto tra 3/4 e 1/2 della memoria. In sistemi operativi con poca RAM è opportuno scegliere valori più vicini alla metà della memoria che ad tre quarti.  Questo numero dovrebbe essere più o meno uguale al numero di MaxClients nel file di configurazione di Apache.

Purtroppo gli altri parametri sono troppo difficili da stabilire con delle regole semplici per qui si può partire da queste configurazioni tipo e provare a variarle per ottenere le massime prestazioni di MySQL.

Con 384 MB RAM: thread_cache_size=20 key_buffer=16M table_cache=288 sort_buffer_size=512K read_buffer_size=386K read_rnd_buffer_size=386K tmp_table_size = 32M	Con 512 MB RAM: thread_cache_size=50 key_buffer=40M table_cache=384 sort_buffer_size=768K read_buffer_size=512K read_rnd_buffer_size=512K tmp_table_size = 32M
Con 1 GB RAM: thread_cache_size=80 key_buffer=150M table_cache=512 sort_buffer_size=1M read_buffer_size=1M read_rnd_buffer_size=768K tmp_table_size = 32M	Con 2 GB RAM: thread_cache_size=128 key_buffer=200M table_cache=1536 sort_buffer_size=1M read_buffer_size=1M read_rnd_buffer_size=1M tmp_table_size = 32M

Gli altri parametri possono andar bene così per tutte le configurazioni:

query_cache_type=1
interactive_timeout=100
wait_timeout=50
connect_timeout=10
join_buffer=1M
myisam_sort_buffer_size=64M
max_allowed_packet=16M

Fortunatamente WordPress non fa un uso massicco di MYSQL, come invece fa per esempio phpBB3, dunque anche valori non ottimali non dovrebbero comportare malfunzionamenti.

Per tenere sotto controllo le performance di MYSQL, dopo aver fatto girare un software benchmark (apache bench, siege, httperf…) adatto per i web server, si può controllare con phpmyadmin se ci sono degli avvertimenti.

Per creare un utente nuovo per MYSQL

$ mysql -u root -p
mysql> use wordpress;
mysql> update user set password=PASSWORD(“NUOVA-PASSWORD”) where User=’NOME-UTENTE’;
mysql> flush privileges;
mysql> quit;

Per sicurezza non è consigliato l’uso dell’utente root come utente del database. NUOVA-PASSWORD è la password dell’utente mentre NOME-UTENTE deve essere sostituito con un nickname a scelta.