Oggi vedremo come migliorare le performance di un server mysql attraverso la customizzazione ( personalizzazione ) del file di configurazione my.cnf:

Questa operazione insieme all ottimizzazione del server web Apache (nel nostro caso) può portare a riduzione dei consumi oltre al 50% che si traduce in un vantaggio economico :

1) per il Service Provider permetterà di offrire performance migliori e/o Hostare piu clienti su un singolo server.
2) per il cliente che acquista una VPS si traduce in minori risorse necessarie e quindi un taglio VPS meno costoso.

di base il file my.cnf è vuoto e mysql utilizza i valori di default : questi vanno bene per un 99% degli usi tradizionali, ma a volte sopratutto in ambiendi di shared hosting, questa politica porta ad uno spreco di risorse che si traduce in rallentamenti del server.

una volta effettuata una copia di backup del file my.cnf, editiamolo con un editor ( in questo caso VI ), nel mio caso il file è situalto in /et/my.cnf

apriamo il file ed inseriamo al suo interno :

[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.log

in questo modo inseriremo dei valori piu adatti al nostro scopo e abiliteremo ( tramite l’ultima riga) il log delle query lente.
Riavviamo il server mysql e nel caso non partisse ripristiniamo il file my.cnf originale.
Non ci resta che attendere 24 ore e lanciare da shell il comando ./mysqltuner.pl
(assicuriamoci di averlo installato ne l caso non sia presente.) il quale ci indicherà eventuali altri ottimizzazioni da effettuare. a correzzioni fatte , riavviamo il server e attendiamo altre 24 ore. Questa operazione puo essere ripetuta finche non otterremo il limite di prestazioni oltre il quale per motivi pratici il server non migliorerà piu. Esistono dei tool di benchmark che eseguono query specifiche per testare la risposta del server. ne parleremo eventualmente in un altro articolo.

Questo risultato è stato ottenuto dopo una prima grossolana ottimizzazione, si evince che la strada seguita è giusta, d’ora in avanti si cercherà il fine tuning per spremere il motore MySQL fino all ultimo Mhz.

edit 1:

dopo 20 ore abbontanti ecco cosa ci propone il 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)

ho eseguito queste modifiche

query_cache_size (> 32M) – portato a 64mb
join_buffer_size (> 1.0M, or always use indexes with joins) – portato a 2 mb
tmp_table_size (> 32M) – portato a 64mb
max_heap_table_size (> 32M) – portato a 64mb
table_cache (> 128) portato a 192