Dopo aver installato un server mySQL è sempre opportuno ottimizzare la sua configurazione in relazione all'hardware del server e al tipo di applicazioni che ospita; in progetti che ricevono molti accessi e devono gestire grosse quantità di dati contenute nel database il tuning diventa un passo obbligato che può fare veramente la differenza nelle prestazioni generali del sito.

Analisi Hardware

E' importante analizzare l'hardware del server nel dettaglio e avere chiare almeno le caratteristiche seguenti:

• Numero di CPU / Core
• Quantità di Ram
• Quantità di spazio su disco

my.cnf

Le impostazioni generali di mySQL sono generalmente contenute nel file /etc/my.cnf o /etc/mysql/my.cnf:

Per prima cosa eseguiamo un backup

cp /etc/my.cnf /etc/my.cnf.backup

Analizzare la configurazione attuale

Prima di modificare effettivamente il file di configurazione, può risultare utile scorrere l'elenco delle impostazioni mySQL attualmente in uso (inserite il vostro nome utente al posto di "user"):

mysqladmin variables -u user -p

Il risultato è la stampa a video di una tabella di coppie impostazione,valore. Quello che segue è un breve estratto dell'inizio dell'output completo su una macchina di test.

Tuning

Andiamo a modificare le impostazioni del file /etc/my.cnf secondo le nostre necessità:

vim /etc/my.cnf


# memory=(key_buffer+sort_buffer_size+read_buffer_size)*max_connections
# max_connections = approssimativamente il valore di MaxClients impostato in httpd.conf file
# Il Default è impostato a 100
max_connections = 100
max_allowed_packet = 1M
max_connect_errors = 10
key_buffer = 256M # 128M per 1GB, 256M per 2GB, 512 per 4GB
join_buffer_size = 4M # 1M per 1GB, 2M per 2GB, 4M per 4GB
read_buffer_size = 4M # 1M per 1GB, 2M per 2GB, 4M per 4GB
sort_buffer_size = 2M # 1M per 1GB, 2M per 2GB, 4M per 4GB 


# myisam_sort_buffer_size utilizzato per i comandi ALTER, OPTIMIZE, REPAIR TABLE
myisam_sort_buffer_size = 32M 


table_cache = 1024
# thread_concurrency = 2 * (numero di CPU)
thread_concurrency=4
thread_cache_size = 286 


# impostare un file di log per query lente, che impiegano più di 2 secondi.
log_slow_queries=/var/log/mysqld.slow.log
long_query_time=2
# Ridurre wait_timeout
wait_timeout = 10
connect_timeout = 10
interactive_timeout = 10
# Query cache.
query_cache_limit = 1M
query_cache_size = 128M
query_cache_type = 1
[mysqld_safe]
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 16M
[myisamchk]
key_buffer = 256M # 64M per 1GB, 128M per 2GB, 256 per 4GB
sort_buffer = 256M # 64M per 1GB, 128M per 2GB, 256 per 4GB
read_buffer = 64M # 16M per 1GB, 32M per 2GB, 64M per 4GB
write_buffer = 64M # 16M per 1GB, 32M per 2GB, 64M per 4GB
[mysqlhotcopy]
interactive-timeout 

Un caso pratico

Abbiamo applicato queste regole al server che ospita questo stesso sito e il risultato è un guadagno di 2,5s sulla pagina al primo accesso e 1,3s sulle pagine chiamate la seconda volta, su una pagina che in media caricava in 3,4s non è poco!

Considerazioni

Eseguire il tuning di un server è sicuramente una pratica utile, in certi casi indispensabile, anche se è doveroso dire che i risultati sono apprezzabili solo su server con elevato numero di accessi e con grossa mole di dati sul database.

Per siti o applicazioni web che non toccano grandi numeri, l'architettura del software ha certamente un impatto più significativo, ed è consigliato concentrare in questi punti un eventuale tuning.