Il 90% del carico ( e quindi anche dei problemi ) che un server Web deve affrontare e dovuto spesso a una cattiva configurazione / gestione del MySql.
Per i pochi che non lo sanno MySQL è il database usato per memorizzare dati, informazioni e configurazioni delle nostre Web App.
Prendiamo ora come base di esempio uno dei CMS più diffusi del web, WordPress. Ogni volta che una pagina del blog viene visualizzata, WordPress fa parecchi accessi al database per recuperare parti delle intestazioni e del corpo(come il nome del blog, la descrizione, il link del feed…) e 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. Nei Sistemi Linux è assolutamente fondamentale evitare di usare la memoria swap ( scrittura su disco) del sistema operativo che degrada terribilmente le performance. La swap dovrebbe essere usata solo in caso di estrema necessità, consiglio di riavviare il sistema operativo dopo l’uso della swap e di aumentare la memoria principale del sistema.
Iniziamo col descrivere i passaggi da effettuare per avere una buona ottimizzazione del Web Server.
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
e togliere il cancelletto a questa riga
# skip-innodb
in modo da farla diventare
skip-innodb
Una volta effettuate le modifiche bisogna riavviare MySql
# /etc/init.d/mysql restart
Andiamo avanti con la nostra Ottimizzazione analizzando altri paramentri contenuti nel file my.cnf, query_cache_limit e 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.
Altro parametro importante e 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.
Altro valore da considerare è table_cache che è 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).
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. 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 Ci sono script da eseguire via shell che ci possono aiutare in questo compito come MySqltuner ( necessita del Perl per funzionare )
In definitiva elenco alcune configurazioni basate sul quantitativo di RAM presente nel server. Premetto che tali configurazioni sono del tutto indicative e non vogliono rappresentare l’arte dell’eccellenza, raggiungibile solo con lo studio e l’ottimizzazione del singolo server che purtroppo è influenzato da diversi parametri di valutazione.
| 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
