MySQL Tuning
Con l’installazione di default la configurazione del servizio mysql viene impostata ad un livello “standard” che va piu’ o meno bene su tutti i server.
In questo articolo vedremo come ottimizzare il valore associato alle variabili presenti nella configurazione, ottimizzandola in base alla memoria e all’hardware disponibile sul nostro server.
key_buffer_size
Indica la quantita’ di indici (i file con estensione MYI) delle tabelle di tipo MYISAM mantenuti in memoria.
L’ideale sarebbe quello di poter contenere in RAM tutti i file di questo tipo.
Il valore attribuito a questa variabile non deve superare la meta’ di tutta la RAM dedicata al servizio mysqld.
Se non potete mettere in memoria tutti i file MYI potrete fare questa ottimizzazione:
Accedete nella console di mysql ed eseguite questa query
show status like ‘%key_read%’;
Dividete il valore di key_read_requests per key_read;
Il risultato deve essere maggiore di 100.
Si imposta nella sezione [mysql] ad es.
[mysqld]
key_buffer_size = 64M
table_cache
Quando mysql accede ad una tabella, la carica in cache. La migliore delle situazioni sarebbe quella di avere una cache talmente grande da contenere
tutte le tabelle aperte in un dato momento.
Per verificare quante tabelle sono aperte in un determinato istante eseguiamo questa query
show status like ‘open_tables’;
Analizzando questo valore nel tempo, possiamo valutare i picchi massimi
Possiamo verificare anche quante tabelle sono state aperte dall’esecuzione del servizio mysql
show status like ‘opened_tables’;
Avremo modo cosi’ di capire se open_tables e’ “attendibile” oppure momentaneamente superiore/inferiore alla media.
Si imposta nella sezione [mysql] ad es.
[mysqld]
table_cache = NUMERO
thread_concurrency
Questa variabile limita il numero di thread avviati simultaneamente. Il valore da impostare e’ questo
2 * (numero di CPU)
Si imposta nella sezione [mysql] ad es.
[mysqld]
thread_concurrency = 4
read_rnd_buffer_size
Viene utilizzata a seguito di una query che prevede un ordinamento delle righe, il classico ORDER BY per intenderci.
E’ consigliabile impostare 1Kb per ogni MB di memoria destinata al servizio mysqld.
Si imposta nella sezione [mysql] ad es.
[mysqld]
read_rnd_buffer_size = 256k
tmp_table_size
Il valore associato a questa variabile indica la dimensione massima delle tabelle temporanee create in memoria.
Si riferisce alle tabelle di tipo MyIsam e quando e’ insufficente la tabella verra’ creata sul disco, con conseguente perdita di performance.
Eseguendo questo comando
mysqladmin -uroot -pPASSWORD processlist -i1
si puo’ verificare se ci sono tabelle temporanee gestite lentamente. Se dovesse essere questo il caso, e’ necessario (memoria permettendo) aumentare tmp_table_size
Si imposta nella sezione [mysql] ad es.
[mysqld]
tmp_table_size = 96M
innodb_buffer_pool_size
Come tmp_table_size ma per le tabelle di tipo InnoDB. Si puo’ impostare questo valore pari al 70/80% della memoria dedicata al servizio mysql
skip-innodb
Se non state utilizzando tabelle di tipo innodb, aggiungete questa direttiva nella sezione [mysqld] della configurazione del servizio
Utilizzare la cache per le query
Per abilitare la cache sulle query impostare
query-cache-type = 1
query-cache-size = 16777216
(questo valore e’ espresso in byte)
Adesso nella console di mysql eseguite questo:
show status like ‘%qcache%’;
e guardate il valore di
Qcache_free_memory
Qcache_lowmem_prunes
Qcache_free_memory indica la memoria disponibile che potra’ essere ancora utilizzata per la cache.
Il valore di Qcache_lowmem_prunes viene aumentato ogni qualvolta che viene rimossa una query dalla cache. Se vedete aumentare questo valore molto rapidamente, dovrete aumentare query-cache-size.
N.B. query_cache_limit indica la dimensione massima del risultato di una query. Se viene superato quella query non sara’ inserita nella cache
Per ripulire la cache bastera’ eseguire questo comando dalla console di mysql
RESET QUERY CACHE
Di default il web server apache esegue gli script php di un sito con l’utente “www-data”. Per permettere ad uno script di memorizzare dati all’interno di una directory, allora i permessi di quest’ultima devono essere settati a 777 oppure il proprietario della cartella deve essere l’utente “www-data”. In questo caso, se sul vostro server web e’ attivo piu’ di un sito internet, allora uno script di un dominio potrebbe modificare i dati di un’altro. Come fare per ovviare a questo problema? Eseguendo gli script di ogni sito con un utente distinto installando la suexec, mod_fcgid ed utilizzando il php in modalita’ cgi. Piu’ difficile a dirsi che a farsi, bastano difatti pochi comandi e qualche minuto per configurare il tutto:
Creiamo il wrapper che utilizzeremo per avviare il php e configurare mod-fcgid: