Come velocizzare di 300 volte le tue query MySQL - 🏆 Managed Server

Come velocizzare di 300 volte le tue query MySQL

Print Friendly, PDF & Email

Avere un database lento può significare nella vita reale avere software altrettanto lento, tempi di attesa elevati e relativi tempi morti che possono impattare sia sull’umore delle vostre giornate che diventano frustranti a causa di un software non reattivo, e una esperienza utente sicuramente inadeguata.

Sul business reale di casi noti pensiamo ad un call center in cui con 500 dipendenti al giorno su un turno di 8 ore recuperiamo 2 minuti di attesa per ogni turno di ogni operatore, una buona ottimizzazione del database ci permetterà di abbattere i tempi morti eccessivi e di recuperare 1000 minuti uomo al giorno, che ne sono 5000 a settimana, 20000 al mese e ben 240 mila all’anno ovvero ben 4000 ore di attesa complessive vuote, per un risparmio di costi e aumento di produttività di circa 80 mila euro l’anno al costo medio di 20 euro l’ora.

Questo è solo un esempio ed un tutorial spicciolo che vuol aprire gli occhi su come ottimizzare le query MySQL prendendo ad esempio il DBMS MySQL, ma la teoria qui riportata è alla base di tutti i DBMS moderni come PostgreSQL, Oracle, SQL Server della Microsoft ed essenzialmente tutti i DBMS che fanno utilizzo di sintassi SQL standard ANSI.

Prima di poter ottimizzare le query lente, è necessario trovarle.

MySQL ha un registro delle query lente (o slow query) incorporato. Per usarlo, apri il file my.cnf e imposta la variabile slow_query_log su “On”. Imposta long_query_time sul numero di secondi che una query dovrebbe impiegare per essere considerata lenta, ad esempio 0.2. Impostare slow_query_log_file sul percorso in cui si desidera salvare il file. Quindi esegui il tuo codice e qualsiasi query superiore alla soglia specificata verrà aggiunta a quel file.

Una volta che sai quali sono le query lente e problematiche, puoi iniziare a esplorare cosa li rallenta. Uno strumento offerto da MySQL è la parola chiave EXPLAIN . Funziona con le istruzioni SELECT , DELETE , INSERT , REPLACE e UPDATE . Semplicemente si utilizza per analizzare e spiegare la query in questo modo:

EXPLAIN SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album_id = album.id
WHERE album.user_id = 1;

Il risultato che otterrai è una spiegazione di come si accede ai dati. Viene visualizzata una riga per ogni tabella coinvolta nella query:

Le parti importanti su cui prestare molta attenzione sono il nome della tabella, la chiave utilizzata e il numero di righe scansionate durante l’esecuzione della query.

Praticamente esegue la scansione di 2.000.000 di immagini, quindi, per ciascuna immagine, esegue la scansione di 20.000 album. Ciò significa che in realtà esegue la scansione di 40 miliardi di righe per la tabella dell’album. Tuttavia, è possibile rendere questo processo molto più efficiente.

Utilizzare gli Indici.

È possibile aumentare in modo significativo le prestazioni utilizzando gli indici. Pensa ai dati come a nomi in una rubrica. Puoi sfogliare tutte le pagine oppure puoi trascinare la scheda della lettera destra per individuare rapidamente il nome che ti serve.

Utilizzare gli indici per evitare passaggi non necessari attraverso le tabelle. Ad esempio, puoi aggiungere un indice su picture.album_id in questo modo:

ALTER TABLE picture ADD INDEX(album_id);

Ora se si esegue la query, il processo non comporta più la scansione dell’intero elenco di immagini. Per prima cosa, tutti gli album vengono scansionati per trovare quelli che appartengono all’utente. Successivamente, le immagini vengono rapidamente individuate utilizzando la colonna index_id indicizzata . Questo riduce il numero di righe scansionate a 200.000. La query è anche circa 317 volte più veloce dell’originale.

Puoi assicurarti che entrambe le tabelle utilizzino una chiave aggiungendo il seguente indice:

ALTER TABLE album ADD INDEX(user_id);

Questa volta, la tabella dell’album non viene digitalizzata nella sua interezza, ma gli album giusti vengono rapidamente individuati utilizzando la chiave user_id . Quando questi 100 album vengono scansionati, le immagini associate vengono individuate usando la chiave album_id . Ogni tabella utilizza una chiave per prestazioni ottimali, rendendo la query 380 volte più veloce dell’originale.

Ciò non significa che è necessario aggiungere indici ovunque poiché ogni indice rende più lenta la scrittura nel database. Guadagni in lettura ma poi  si perde sulle scritture nel database. Quindi aggiungi solo indici che aumentino effettivamente le prestazioni di lettura.

Utilizzare EXPLAIN per confermare e rimuovere qualsiasi indice che non viene utilizzato nelle query.

Fare una profilazione e successiva analisi delle Query per scoprire potenziali problemi applicativi.

Può capitare invece di avere un database con delle query snelle e performanti ma tuttavia il carico del server schizza alle stelle ed in particolar modo il carico del processo del server del database.

Potrebbe essere non un problema di database, o meglio non solo un problema di database, ma di livello applicativo come ad esempio uno script PHP che erroneamente richiama ciclicamente una determinata query o che esegue una query errata non utilizzando clausole adeguate che possa permettere una velocità di esecuzione della query stessa.

Un esempio classico è la seguente sintomatologia : il database è sempre lo stesso, è sempre andato bene da mesi o da anni, non ci sono picchi e volumi di accessi e visite differenti da quelle standard ma di punto in bianco il carico del server e di MySQL inizia a crescere in modo apparentemente immotivato.

La colpa è del database o lato applicativo è stato fatto un errore ?

Per analizzare questa eventualità da parte del sistemista, ovvero indagare sulle eventuali problematiche derivate da una corretta implementazione dell’applicativo da parte dello sviluppatore si possono ricorrere a tool di profilazione delle performance come New Relic oppure utilizzare tool come Percona Toolkit che abbiamo trattato nello specifico articolo dedicato su Percona Toolkit

Query lente e MySQL lento su WordPress (o altri CMS)

Quando il problema appena sopracitato compare su CMS Open Source come WordPress, spesso non si ha nemmeno la possibilità di rendersi conto di quello che sia realmente avvenuto lato applicativo affinchè il sito con database e veloce scattante prima, diventi un pachiderma 10 minuti dopo.

Magari l’utente in hosting dai nostri servizi ha solo pensato di aggiornare gli ultimi due o tre plugin WordPress appena rilasciati senza farsi troppi interrogativi perchè non è un tecnico e perchè così ha sempre fatto senza problemi di sorta.

Tuttavia capita più frequentemente di quanto si immagini che un plugin errato, scritto male con una business logic errata possa portare a danni gravi e impattare in maniera drastica sulle performance del database, come ad esempio un plugin che inizi a scrivere porcherie su una tabella WordPress condivisa come la tabella wp_options.

Per avere un esempio reale di ciò che stiamo parlando ti invito a leggere questo caso di un nostro cliente

Solo la comprensione di ciò che sta succedendo lato applicativo infatti ti può permettere si comprendere le problematiche di carico CPU che non sono in alcun modo imputabili alla progettazione del database.

Hai ancora problemi sulla velocità MySQL ?

Se dopo aver letto il nostro articolo e seguito i nostri consigli ed anche tu hai un database aziendale o un applicativo che risulta lento e non ti soddisfa, contattaci pure per valutare i margini di miglioramento del tuo database e usufruire del servizio di Ottimizzazione MySQL al fine di avere un database rapido e performante.

17279

Vuoi ricevere i migliori consigli ?

Ogni settimana nuovi consigli e novità !

Hai dei dubbi? Non sai da dove partire? Contattaci


Abbiamo tutte le risposte alle tue domande per aiutarti nella giusta scelta.

Scrivici

Chatta direttamente con il nostro supporto tecnico.

0256569681

Chiamaci subito negli orari d’ufficio 9:30 – 19:30

Ricevi assistenza

Apri un ticket direttamente nell’area di supporto.

Conosci i problemi di performance del tuo sito ?Analisi Gratuita
+ +

I migliori trucchi

per il tuo Hosting ?

FREE

Iscriviti gratuitamente per nuovi articoli e suggerimenti !

Proseguendo accetti la privacy policy

Torna su