Ottimizzare MySQL 5 Server su GNU/Linux

I tempi sono cambiati!

Questo articolo è stato pubblicato più di 7 anni fa quindi alcuni contenuti o informazioni presenti in esso potrebbero non essere più validi. Questo sito non è responsabile per eventuali errori causati da questo problema.

Con oltre 30.000 download al giorno ed oltre 5 milioni di installazioni, MySQL è il Database (RDBMS) Open Source più famoso e più utilizzato al mondo, sulla quale si appoggiano diverse piattaforme, fra cui Blog, Forum, Wiki, CMS e tanto altro.

In questo articolo vedremo in generale come ottimizzare MySQL 5 Server onde evitare di incorrere in problemi di sovraccarico server e relativa indisponibilità del Database.

C’è da dire che, nello specifico prenderemo come esempio un’installazione WordPress 3.X, dato che questa piattaforma di blogging effettua numerosi accessi al Database MySQL per poter recuperare diverse parti del blog, fra cui intestazioni, testo, corpo ed altro.

Innanzitutto per l’installazione di WordPress occorre un server con minimo 256 MB di RAM (ma sono vivamente consigliati almeno 384, o meglio 512 MB), dato che con l’installazione base di Apache, PHP e MySQL, la RAM sarebbe già “ridotta all’osso” ed il server potrebbe scrivere sulla partizione di Swap introducendo un pesante degrado delle prestazioni generali.

In alternativa, per “alleggerire” server con risorse limitate, sarebbe possibile (e magari opportuno) installare Lighttpd, oppure NGINX che sono Webserver altrettanto validi e richiedono molte meno risorse rispetto ad Apache, ma, non supporta le regole di rewrite Apache tramite .htaccess, queste ultime su Lighttpd hanno una configurazione molto differente e vanno inserite dentro al file di configurazione sul server, mentre per NGINX le regole di rewrite sono molto simili a quelle del .htaccess di Apache e vanno sempre inserite nel file di configurazione, ma magari questo lo vedremo un’altra volta.

Ma passiamo alla configurazione del file “my.cnf“, che è il file di configurazione di MySQL e si trova sotto /etc/my.cnf per i sistemi RPM (Red Hat, Fedora, Mandriva, Suse, CentOS e derivate) ed /etc/mysql/my.cnf per i sistemi Deb (Debian, Ubuntu e derivate), negli esempi useremo il “my.cnf” dei sistemi RPM, ma prima di procedere effettuiamo una copia di backup del file e salviamola in un posto sicuro, quindi apriamo con un editor il suddetto file (tutti i comandi devono essere eseguiti da root):

vi /etc/my.cnf

e se non utilizziamo lo storage engine InnoDB, inseriamo le righe (oppure, se esistono già, decommentiamole levando il simbolo “#” davanti ogni riga):

skip-bdb
skip-innodb

successivamente passiamo alla configurazione vera e propria di ogni singolo (o quasi) parametro.

Uno dei parametri importanti di MySQL è il key_buffer, che si ottiene dalla divisione delle key_reads/key_read_requests e key_writes/key_writes_requests che dev’essere impostato ad 1/8 della RAM totale installata sul server e non deve superare 1/4 della stessa, quindi impostiamolo ad 1/8 della RAM totale e poi inseriamo un valore più alto oppure più basso in base al risultato delle divisioni, il risultato corretto si ottiene se il rapporto delle “reads” è inferiore di 1/100 rispetto alle “read_requests” e quello delle “writes” è inferiore di 1/10 rispetto alle “write_requests“, questi valori sono facilmente verificabili dalla WebGUI phpMyAdmin sotto la voce “Visualizza informazioni di runtime di MySQL“.

Passiamo a query_cache_size e query_cache_limit, il primo si ottiene dal totale della RAM in MB diviso 16 (Es: per 1GB di RAM si calcola 1024/16 = 64M), ed è valido solo se sullo stesso server è installato e gira Apache, altrimenti si può inserire un valore superiore, invece per il query_cache_limit può essere inserito un valore fra 1M e 4M, all’inizio inserite 4M, se il server scrive sulla Swap, inserite un valore più basso.

Il valore ottimale del parametro table_cache che NON va espresso in MB, dovrebbe essere impostato ad un valore fra 3/4 e 1/2 della RAM totale (Es: per 1GB di RAM si calcola 1024/1,333 = 768, oppure 1024/2 = 512)

Il valore thread_concurrency dipende dal numero di CPU/Core presente sul server ed ha valore 2 per ogni singola CPU/Core (Es: se ho una CPU Single Core inserisco 2, se ho una CPU Dual Core inserisco 4 e così via).

Il valore max_connections in genere andrebbe impostato a 3/4 della RAM totale su server con grossi quantitativi di RAM, mentre, su server con bassi quantitativi, andrebbe impostato a 1/2 della RAM o anche meno (Es: per 2 GB di RAM 2048/1,333 = 1536, per 512 MB di RAM 512/2 = 256), ma, per server con poca RAM e sulla quale gira anche Apache è sempre consigliato inserire un valore di circa 1/4 della RAM, dato che è fisicamente impossibile aprire numerose connessioni ed il server potrebbe “collassare” per mancanza di risorse da un momento all’altro.

Per gli altri parametri, siccome ci sono molte, forse troppe variabili, si potrebbe partire di seguenti esempi e poi modificare i parametri fino a che non si ottiene una configurazione ottimale:

– Per server con 512MB di RAM

thread_cache_size=50
key_buffer=40M
table_cache=384
sort_buffer_size=768K
read_buffer_size=512K
read_rnd_buffer_size=512K
tmp_table_size = 32M
max_connections = 80

– Per server con 1GB di RAM

thread_cache_size=80
key_buffer=150M
table_cache=512
sort_buffer_size=1M
read_buffer_size=1M
read_rnd_buffer_size=768K
tmp_table_size = 32M
max_connections = 80

I restanti parametri possono andar bene così come sono per tutte le configurazioni:

query_cache_type=1
interactive_timeout=10
wait_timeout=10
connect_timeout=10
join_buffer=1M
myisam_sort_buffer_size=64M
max_allowed_packet=16M

al termine delle configurazioni, per far sì che MySQL recepisca le modifiche, ricordarsi sempre di riavviare il “daemon” con il comando:

/etc/init.d/mysqld restart

In ogni caso esiste uno script installabile su Linux, il suo nome è “mysqltuner” ed è scaricabile da riga di comando nel seguente modo:

wget mysqltuner.pl

successivamente rendere eseguibile lo stesso con:

chmod 755 mysqltuner.pl

e lanciarlo

./mysqltuner.pl

verrà richiesto l’utente amministrativo di MySQL e la relativa password, al termine, in coda allo script sotto la voce Variables to adjust, verranno riportati i parametri del “my.cnf” da modificare per ottimizzare MySQL al meglio.

Prima di eseguire mysqltuner si consiglia lasciar girare MySQL per almeno 24 ore dall’ultimo riavvio, in modo che tutte le statistiche siano accurate.

Infine, oltre alle modifiche da apportare al file di configurazione MySQL, è sempre bene effettuare periodicamente l’ottimizzazione delle tabelle sui propri DataBase da phpMyAdmin oppure da riga di comando.

Lascia un commento

Il tuo indirizzo email non sarà pubblicato. I campi obbligatori sono contrassegnati *