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