Oggi vedremo come migliorare le performance di un server MySQL attraverso la personalizzazione del file di configurazione my.cnf. Questa operazione, unita all’ottimizzazione del server web Apache (nel nostro caso), può portare a una riduzione dei consumi superiore al 50%, traducendosi in un vantaggio economico sia per il provider che per l’utente.
Vantaggi dell’ottimizzazione
- Per il Service Provider: Offre la possibilità di garantire performance migliori e di ospitare un maggior numero di clienti su un singolo server.
- Per il cliente VPS: Consente di utilizzare minori risorse, permettendo di acquistare una VPS a costi contenuti.
Configurazione iniziale
Di base, il file my.cnf è vuoto e MySQL utilizza i valori di default, che sono adatti per il 99% degli usi tradizionali. Tuttavia, in ambienti di shared hosting, questa impostazione può portare a uno spreco di risorse e a rallentamenti del server.
Prima di procedere, ricordati di effettuare una copia di backup del file my.cnf. Dopodiché, aprilo con il tuo editor preferito (in questo esempio, utilizziamo vi). Nel mio caso, il file si trova in /etc/my.cnf.
Modifica del file my.cnf
Apri il file e inserisci il seguente contenuto:
[mysqld]
set-variable = max_connections=200
query_cache_type=1
query_cache_size=32M
query_cache_limit=1M
sort_buffer_size=2M
read_rnd_buffer_size=512K
tmp_table_size=32M
max_heap_table_size=32M
thread_cache_size=24
key_buffer_size=16M
table_cache=128
join_buffer_size=1M
log-slow-queries=/var/lib/mysql/slow.logQueste modifiche permettono di definire valori più adatti alle nostre esigenze e, grazie all’ultima riga, di abilitare il log delle query lente.
Procedura post-modifica
- Riavvio di MySQL: Riavvia il server MySQL. Se il servizio non parte correttamente, ripristina il file my.cnf originale.
- Monitoraggio: Attendi 24 ore e, successivamente, esegui da shell il comando
./mysqltuner.pl(assicura che lo script sia installato). Questo strumento ti suggerirà ulteriori ottimizzazioni. - Iterazione: Applica le modifiche suggerite, riavvia il server e attendi altre 24 ore. Ripeti questa operazione finché non raggiungi il limite di prestazioni oltre il quale il server non potrà migliorare ulteriormente.
- Benchmark: Esistono anche tool di benchmark che eseguono query specifiche per testare la risposta del server. Ne parleremo in un articolo futuro.
Risultati e fine tuning
Dopo una prima ottimizzazione grossolana, i risultati sono incoraggianti: il motore MySQL sembra rispondere positivamente alle modifiche apportate. D’ora in avanti, il focus sarà sul fine tuning per sfruttare al massimo il potenziale del server.
Edit 1: Risultati di mysqltuner dopo 20 ore
Ecco un estratto dell’output fornito da mysqltuner:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.69-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 457M (Tables: 1994)
[--] Data in InnoDB tables: 70M (Tables: 238)
[!!] Total fragmented tables: 63
-------- Performance Metrics -------------------------------------------------
[--] Up for: 20h 13m 3s (5M q [74.746 qps], 99K conn, TX: 19B, RX: 790M)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 162.0M global + 3.9M per thread (200 max threads)
[OK] Maximum possible memory usage: 937.0M (16% of installed RAM)
[OK] Slow queries: 0% (6/5M)
[OK] Highest usage of available connections: 21% (43/200)
[OK] Key buffer size / total MyISAM indexes: 16.0M/128.8M
[OK] Key buffer hit rate: 99.1% (202M cached / 1M reads)
[OK] Query cache efficiency: 81.2% (3M cached / 4M selects)
[!!] Query cache prunes per day: 501755
[OK] Sorts requiring temporary tables: 0% (2 temp sorts / 148K sorts)
[!!] Joins performed without indexes: 1423
[!!] Temporary tables created on disk: 34% (63K on disk / 183K total)
[OK] Thread cache hit rate: 99% (62 created / 99K connections)
[!!] Table cache hit rate: 0% (128 open / 1M opened)
[OK] Open file limit used: 1% (217/13K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB data size / buffer pool: 70.3M/80.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 1.0M, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 32M)
table_cache (> 128)
Modifiche successive
In seguito ai suggerimenti di mysqltuner, ho apportato le seguenti modifiche:
- query_cache_size: aumentato a 64M
- join_buffer_size: aumentato a 2M
- tmp_table_size: aumentato a 64M
- max_heap_table_size: aumentato a 64M
- table_cache: aumentato a 192
Con queste ottimizzazioni, il sistema risulta più efficiente e pronto a gestire carichi maggiori, consentendo un uso più parsimonioso delle risorse e una migliore esperienza per l’utente finale.
Continueremo a monitorare e migliorare il sistema con ulteriori test e aggiornamenti. Rimani sintonizzato per altri approfondimenti sul fine tuning di MySQL!
