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

Un errore che può capitare, quando si effettuano query su due db o due tabelle con una collations differente, è il seguente:

ERROR 1267 (HY000): Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation ‘=’

Questo errore indica che i due campi su cui stiamo effettuando la query hanno una collations differente e non compatibile. Facciamo qualche esempio ecco la nostra tabella:

show create table a;

CREATE TABLE `a` (
`campo1` varchar(64) character set latin1 collate latin1_bin NOT NULL default ”
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

show create table b;

CREATE TABLE `b` (
`campo2` varchar(255) NOT NULL default ”
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

La tabella b non avendo alcuna collations impostata, prenderà quella di default, ovvero:

collate latin1_general_ci

Ora se andiamo a fare una query che preleva i dati da entrambe le tabelle, otterremo il seguente errore:

SELECT *  FROM a,b WHERE campo1 = campo2;

ERROR 1267 (HY000): Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation ‘=’

Ora per risolvere il problema forziamo la collate  latin1_bin in latin1_general_ci:

ALTER TABLE a character set latin1 collate latin1_general_ci , modify column campo1 varchar(64) character set latin1 collate latin1_general_ci NOT NULL;

A questo punto lo show create table ci mostrerà la nuova collate impostata per il campo campo1 e la query che prima ci restituiva l’errore ora sarà eseguita correttamente.

Nelle distribuzioni linux il servizio MySQL e’ configurato di default per non accettare connessioni da host esterni. Ma come fare per abilitare la connessione da remoto? La procedura e’ molto semplice ed proposta di seguito:

1) Bisogna accedere al server via ssh con utente root e commentare (semplicemente anteponendo il carattere # ad inizio riga) la direttiva ‘bind-address’ dalla configurazione del servizio (probabilmente /etc/mysql/my.cnf oppure /etc/my.cnf)

2) Supponendo di voler utilizzare un utente preesistente chiamato “IlTuoUtente”, accedendo al database ‘mysql’ dovrete impostare il valore ‘%’ nel campo host della tabella user.
Cosi’ facendo l’utente puo’ collegarsi sia da localhost che da remoto. Questo e’ il comando che dovrete digitare:

update users set host = ‘%’ where user = ‘IlTuoUtente’;

3)  La stessa stessa operazione dovra’ essere effettuata per la tabella ‘db’:

update db set host = ‘%’ where user = ‘IlTuoUtente’;

4) Non vi rimane che riavviare il servizio mysql (con il comando ‘/etc/init.d/mysql restart’)

Per eseguire il backup  di un database mysql basta eseguire da console il seguente comando:

mysqldump nome_database -u nome_utente -p > dump.sql

Se il database di cui si vuole effettuare il backup si trova su una macchina esterna sulla quale è possibile connetters si deve aggiungere il parametro dell’ host (-h)

mysqldump nome_database -h host(ip o hostname) -u nome_utente -p > dump.sql

Una volta che si ha a disposizione il backup del database se si deve fare il restore il comando da eseguire è il seguente:

mysql < dump.sql -u nomeutente -p