Un server MySQL, pur non avendo ancora il livello di complessità di soluzioni tipo Oracle o PostgreSQL, necessita di impostazioni fini e variegate. Queste configurazioni si possono impostare tramite il file di configurazione di MySQL, tramite una query o tramite un software appositamente progettato.
Per venire incontro alle necessità degli amministratori di database, la MySQL AB ha rilasciato un pacchetto di software molto interessanti (per lo più ancora in fase di sviluppo) che prende il nome di MySQL GUI Tools. In questo pacchetto sono inclusi:
- MySQL Administrator, per l'amministrazione del server MySQL (il software che utilizzeremo più avanti)
- MySQL Query Browser, per eseguire query arbitrarie sulla falsariga di MS Access
- MySQL Migration Toolkit, per eseguire migrazioni da altri sistemi (Oracle, MS Access, MS SQL Server)
- MySQL Workbench, per analizzare visivamente la struttura di un database, l'erede di DbDesigner
Tutti i software sono distribuiti gratuitamente e offrono funzionalitù e caratteristiche di qualità.
Cos'è la cache di MySQL
Dalla versione 4.0.1 MySQL possiede un sistema di caching interno per ottimizzare le risorse di sistema. Questa caratteristica, attivabile tramite il file di configurazione di MySQL, analizza la query inviata e controlla l'esistenza di record precedentemente estratti per evitare di eseguire nuovamente la query.
In pratica la prima volta i risultati vengono estratti, salvati in memoria (Ram) e serviti al client richiedente. Al successivo tentativo di accesso a tali dati non sarà più necessario accedere all'hard disk in lettura, applicare le clausole specificate in WHERE, ordinare i dati e servirli al client; basterà attingere alla memoria predisposta dal server in precedenza. Ovviamente ogni accesso in scrittura al database che modifichi in qualche modo i dati causerà la cancellazione della copia salvata in cache evitando di avere dati inconsistenti.
L'accesso ai dati in Ram consente di incrementare in maniera significativa le prestazioni del database. Di contro è necessario tenere presente che la quantità di Ram disponibile è limitata e quindi è necessario scendere ad un compromesso tra ottimizzazione dell'accesso ai dati del database e prestazioni generali del sistema per evitare di usare alternative più lente della Ram.
Attenzione: la cache si attiverà solo se le due query passate dal client al server sono identiche byte per byte: uno spazio in più, una maiuscola al posto di una minuscola, un commento in più non faranno beneficiare le due query del caching.
Come controllare se la cache è in funzione
Nonostante esistano diverse direttive di configurazione per attivare e impostare finemente il sistema di cache delle query scoprire se è in funzione è abbastanza semplice, ci basterà infatti eseguire un'unica query e leggerne il risultato: Yes evidenzia l'attività del sistema, No ci dice invece che è necessario attivarlo.
SHOW VARIABLES LIKE 'have_query_cache';
Alternativamente con MySQL Administrator è possibile avere immediatamente un riscontro visivo dell'andamento della cache: cliccando su Health nella colonna di sinistra, quindi sul tab Memory Health avremo nel grafico denominato Query Cache Hitrate l'andamento istantaneo del nostro sistema. Una linea fissa a zero ci indica subito che il sistema di cache non è in funzione.
Avere il sistema di caching attivo è solo un primo passo: è necessario ora essere certi di avere un sistema efficiente e capace quindi di farci risparmiare effettivamente tempo e risorse.
Come attivare la cache
Per attivare il sistema di caching dobbiamo lavorare sulla direttiva query_cache_size che di default è impostata a zero. È possibile impostare questa direttiva sia dal file di configurazione di MySQL (mysql.cnf) in modo che il server legga automaticamente il valore all'avvio, sia a sistema avviato con una query del tipo:
SET SESSION query_cache_size = 4*1024*1024;
Le altre direttive rilevanti sono: query_cache_limit, query_cache_min_res_unit e query_cache_type.
Vediamo ora come impostare queste direttive al meglio per ottenere un incremento nelle prestazioni.
Come ottimizzare la cache
Query_cache_size
Questa direttiva può essere impostata grande a piacere secondo la disponibilità di Ram di sistema. Più grande è maggiore sarà l'incremento nelle prestazioni. È necessario però non esagerare perché un valore troppo elevato in percentuale sulla quantità di Ram (ad esempio il 50%) causerà l'utilizzo del paging e quindi si vanificherà l'uso della cache. Un buon valore può essere 10-15 MByte.
Query_cache_limit
Questa direttiva indica la dimensione massima della query memorizzabile in cache. Una query con una dimensione troppo elevata non riceverà vantaggi dal caching perché verrà presto eliminata dalla cache per fare spazio ad altre query.
Query_cache_min_res_unit
Questa direttiva imposta la dimensione minima delle query memorizzabili in cache. Generalmente il valore di default è ottimo.
Query_cache_type
Questa direttiva permette di decidere se il sistema di caching dovrà funzionare sempre (1), su richiesta della singola query (2) o mai (0).
Inoltre, sempre per migliorare la performance di una query MySQL possiamo intervenire su altri parametri quali:
join_buffer_size: questa direttiva imposta la dimensione del buffer per query con join non ottimizzate da indici. È meglio creare degli indici per le nostre query piuttosto che incrementare questo valore.key_buffer_size: questa direttiva imposta la dimensione del buffer utilizzato per gestire gli indici. Questo valore non è mai abbastanza alto quindi impostatelo secondo la quantità di RAM disponibile sul vostro server.query_prealloc_size: questa direttiva determina la dimensione del buffer usato per il parsing delle query, un valore più elevato può risultare utile se abbiamo un gran numero di query complesse.sort_buffer_size: questa direttiva determina la dimensione del buffer usato per ordinare i risultati di una query.read_rnd_buffer_size: anche questa direttiva influenza la performance delle query con un grande numero di risultati da ordinare. A differenza della precedente però questo buffer vale per ogni sessione quindi è meglio intervenire sulla direttiva precedente.
Se il sistema di caching non funziona
In alcuni casi il sistema di caching non viene attivato anche se ci aspetteremmo il contrario:
- Transazioni con tabelle di tipo diverso da InnoDB
- Query con un commento all'inizio
- Query con funzioni "imprevedibili", ad esempio
RAND(), funzioni che restituiscono la data o il tempo attuale (CURDATE(), UNIX_TIMESTAMP()o simili),ENCRYPT() - Query con funzioni definite dall'utente
- Query inerenti tabelle nel database MySQL
- Query inerenti tabelle temporanee
- Query senza una tabella
Se invece vogliamo eseguire una query SELECT senza che intervenga il sistema di caching possiamo utilizzare la parola chiave SQL_NO_CACHE subito dopo SELECT in questo modo:
SELECT SQL_NO_CACHE * FROM tabella
