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.

mysql tuning performances

Attenzione: Ti preghiamo di usare le informazioni contenute in questo articolo solamente a livello informativo e a tuo rischio. Se stai gestendo un server ti invitiamo a eseguire un backup completo prima di eseguire qualunque modifica. Se non sei tecnicamente preparato, ti invitiamo ad affidarti a un sistemista competente.

Analisi Hardware

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

my.cnf

Le impostazioni generali di mysql sono generalmente contenute nel file /etc/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 nostra macchina di test.

Variable_name   Value
     
auto_increment_increment   1
auto_increment_offset   1
autocommit   ON
automatic_sp_privileges   ON
back_log   50
basedir   /usr/
big_tables   OFF
binlog_cache_size   32768
...   ...
...   ...
...   ...

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.

Risorse esterne

Commenti dell'articolo
Lascia il tuo commento
 
 
 
 
 
 
Questa pagina non è stata ancora commentata, diventa il primo a lasciare un commento! :-)