Ritorno sull’argomento ottimizzazione dato che ultimamente al lavoro gli sto dedicando parecchio tempo.

comando explain: l’utilizzo di questo comando (applicato precedentemente ad una query) consente di capire il lavoro che fa l’optimizer nell’esecuzione. Ci permette di capire quali indici vengono utilizzati (ed anche se non vengono utilizzati) per vedere quali siano le contromisure da prendere per aumentare l’efficienza

permessi: semplificare al massimo i permessi degli utenti che accedono al db

analyze / optimize: il primo comando permette di aggiornare l’indice di una tabella (aumentando l’efficienza delle SELECT) mentre il secondo, oltre a svolgere questo compito, permette di “allineare” il contenuto delle tabelle dopo aver eseguito delle cancellazioni (specialmente nel caso in cui le tabelle contengono campi a lunghezza variabile tipo VARCHAR, BLOB ecc.).
Si consiglia di eseguire il comando ANALYZE spesso per mantenere aggiornati gli indici e il comando OPTIMIZE nei casi in cui ci sia stata una consistente eliminazione di dati da una tabella

BENCHMARK: consente di ripetere un numero arbitrario di volte una query per calcolare i tempi di esecuzione. Ciò consente di valutare l’impatto sul funzionamento di un DB specialmente per le query utilizzare più di frequente.

Condividi

Tags: MySql, ottimizzazione, query, tips, tuning

Ci sono alcune regole banali che bisognerebbe seguire quando si scrivono le query che spesso vengono tralasciate perchè considerate irrilevanti; in realtà ogni accorgimento che si prende si riflette sulle prestazioni del sistema in generale. Di seguito riporto alcune pillole da tenere presente.

parentesi: mentre si scrive un’interrogazione, cercare di utilizzare il minor numero di parentesi possibile…ovvero…utilizzare soltanto quelle strettamente necessarie; il parsing della query è più lungo perchè la presenza di parentesi modifica le priorità delle clausole.

casting delle variabili: scrivere una condizione WHERE in questo modo WHERE id='10' oppure in questo WHERE id=10, (ovviamente considerando id come campo numerico) non è la stessa cosa perchè nel primo caso si costringe Mysql ad operare un casting del valore costante in modo da poter effettuare un confronto tra tipi identici; il secondo caso è preferibile.

attributi in output: nelle SELECT inserire in output soltanto i campi effettivamente necessari; evitare di scrivere SELECT * FROM ... quando ci serve soltanto un attributo.

operatore LIKE: limitare l’operatore LIKE ai casi di effettiva necessità. Cercare di evitare di inserire la wildcard (%) all’inizio della stringa. Ad es. evitare una condizione del tipo WHERE nome LIKE '%mario%'

LIMIT: sfruttare l’operatore LIMIT se è sufficiente ottenere un limitato numero di righe in output.

INSERT: se ci si trova nella situazione di dover eseguire una serie di INSERT, si possono raggruppare tutt in una query unica. Ad esempio le tre interrogazioni:
INSERT INTO utenti (nome,cognome)
VALUES ('Mario','Rossi');
INSERT INTO utenti (nome,cognome)
VALUES ('Giuseppe','Verdi');
INSERT INTO utenti (nome,cognome)
VALUES ('Antonio','Neri');

possono essere integrate nella query:
INSERT INTO utenti (nome,cognome)
VALUES ('Mario','Rossi'),
('Giuseppe','Verdi'),
('Antonio','Neri');
con un notevole guadagno in efficienza.

Condividi

Tags: MySql, ottimizzazione, query, tips, tuning

In questo post mi occuperò di analizzare le impostazioni da utilizzare per configurare al meglio il server Mysql e ottimizzarne il funzionamento. La configurazione si effettua sul file my.cnf e prima di metterci mano vi consiglio di fare una copia di backup….non si sa mai…

Andiamo per ordine alfabetico ad analizzare le varie variabili:

key_buffer_size: imposta la dimensione della memoria riservata agli indici delle tabelle. Impostando un valore alto si consentirà al DB di mantenere molti più indici in memoria velocizzando le query che li sfruttano. La modifica di questa impostazione si può gestire osservando due variabili di runtime ovvero Key_reads (richieste di lettura su disco) e Key_read_requests (richiesta di lettura). La divisione tra questi due valori indica quante letture su disco vengono effettuate rispetto alle letture totali; in una situazione ottimale, i due valori devono differire di almeno 3 ordini di grandezza cioè la divisione deve essere inferiore a 0,001. Aumentare il valore di key_buffer_size consente di abbassare questo rapporto ed aumentare le prestazioni.

max_user_connections: imposta la quantità di connessioni contemporanee per ogni utente che accede al db (e per utente si intende utente del DB non l’utente fisico: per esempio in un sito internet tutti gli utenti sfruttano la stessa copia di username e password per accedere al DB). Bisogna impostare questo valore a seconda del traffico che ha il proprio server ovvero deve essere abbastanza alto ma non troppo alto; per avere un riferimento potete analizzare il valore “massime connessioni contemporanee” fornito dalle variabili runtime del DB: il valore max_user_connections deve essere più alto di questo.

sort_buffer_size: imposta il buffer destinato all’ordinamento (ORDER BY) e raggruppamento (GROUP BY) dei dati. Se nel sistema ci sono molte query di questo tipo è conveniente aumentare questo valore anche a 2M o superiori.

table_cache: indica il numero di tabelle caricate contemporaneamente. Attenzione a questo parametro, i sistemi operativi hanno un limite di puntatori a files apribili contemporaneamente, se si supera tale limite si può incorrere in anomalia del sistema. Solitamente deve essere superiore al valore Open_tables delle variabili runtime; ovviamente se è molto superiore si può procedere alla sua riduzione così da risparmiare memoria.

thread_cache_size: imposta il numero di thread da mantenere in cache. Questa funzionalità rende l’uso di thread molto performante.

tmp_table_size: indica la dimensione massima della singola tabella temporanea in memoria. Tutte le tabelle di dimensioni maggiori vengono gestite su disco (ovviamente con degrado di prestazioni). Il valore è strettamente dipendente dalla quantità di memoria, solitamente si inizia con pochi MB a crescere tenendo d’occhio l’occupazione in RAM. I vantaggi di questa impostazione si possono analizzare nella variabile Created_tmp_disk_tables (che dovrebbe tendere a decrescere man mano che aumenta l’impostazione di tmp_table_size).

wait_timeout: identifica il tempo dopo il quale una connessione viene chiusa automaticamente causa inattività. E’ consigliabile tenere un valore basso per eliminare le connessioni inattive e lasciare spazio alle nuove ma bisogna fare attenzione a non abbassarlo troppo per non avere problemi durante il funzionamento. Nelle mie applicazioni utilizzo un valore di 600.

La gestione di questi valori deve tener conto della memoria RAM disponibile; occorre assicurarsi di averne a disposizione a sufficienza evitando di chiamare in causa lo Swap file che ci porterebbe all’effetto contrario.
E’ consigliabile fare le analisi alle variabili di runtime nei momenti di picco nell’utilizzo del sistema; ovviamente la modifica del file di configurazione e, soprattutto, il successivo riavvio del server DB dovrebbe essere eseguito in un momento di poco carico dato che porterà un temporaneo disservizio.
In un prossimo post fornirò indicazioni sull’impostazione di altre variabili. Lasciate i vostri commenti!

Condividi

Tags: configurazione, MySql, ottimizzazione, variabili