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.