Come velocizzare di 300 volte le tue query MySQL

L’efficienza di un database è un fattore critico nella vita reale che ha un impatto diretto sulla velocità e sulla reattività del software. Quando un database è lento, l’esperienza utente è compromessa, portando a tempi di attesa eccessivi e, inevitabilmente, a frustrazione. Questi ritardi non solo creano un ambiente di lavoro stressante, ma si traducono anche in inefficienze operative e un’esperienza utente nettamente insoddisfacente.

Prendiamo, per esempio, un call center con 500 dipendenti che lavorano su un turno di 8 ore. In questo scenario, se ogni operatore spende due minuti in più ad ogni turno a causa della lentezza del software, i costi dell’inefficienza si accumulano rapidamente. Una buona ottimizzazione del database può abbattere questi tempi morti eccessivi, permettendo di recuperare circa 1000 minuti per giorno, che si traducono in 5000 minuti alla settimana, 20,000 al mese e ben 240,000 minuti, o 4000 ore, in un anno. Questo recupero di tempo potrebbe portare a un risparmio significativo di costi e un aumento della produttività, stimato in circa 80,000 euro all’anno, considerando un costo medio di 20 euro l’ora.

Questa rappresentazione serve a evidenziare l’impatto tangibile che un database lento può avere sulla produttività e l’efficienza. Mentre l’esempio specifico riguarda MySQL, i principi sottostanti sono applicabili a tutti i moderni sistemi di gestione dei database (DBMS), come PostgreSQL, Oracle, SQL Server di Microsoft e qualsiasi altro DBMS che utilizza la sintassi SQL standard ANSI.

Tuttavia, la questione chiave è come si identificano le query lente che stanno causando questi ritardi.

Individuare le Query lente

Per ottimizzare le prestazioni di un database, è essenziale identificare in primo luogo le query problematiche che ne rallentano le operazioni. La prima fase di questo processo implica l’utilizzo di strumenti specifici, come Percona Toolkit e il suo componente pt-query-digest, che forniscono un’analisi dettagliata del comportamento del database. Questi strumenti possono rilevare le query che stanno consumando la maggior parte del tempo e delle risorse, evidenziando le aree che richiedono un’attenzione immediata per migliorare le prestazioni.

MySQL, uno dei sistemi di gestione dei database più diffusi, dispone di una funzionalità di registro delle query lente (slow query log) integrata, che può essere particolarmente utile in questo contesto. Questo registro tiene traccia delle query che superano un determinato tempo di esecuzione, permettendo di identificare quelle che possono richiedere un’ottimizzazione.

Per attivare il registro delle query lente in MySQL, devi aprire il file di configurazione my.cnf e impostare la variabile slow_query_log su “On”. Questo attiverà la registrazione delle query lente.

Dopodiché, devi impostare la variabile long_query_time sul numero di secondi che una query dovrebbe impiegare prima di essere considerata lenta. Ad esempio, se imposti questo valore a 0.2, tutte le query che impiegano più di 0.2 secondi verranno considerate lente e registrate nel log.

Infine, è necessario specificare il percorso del file di log utilizzando la variabile slow_query_log_file. Questo è il file dove verranno registrate le query lente.

Una volta che il registro delle query lente è attivato e configurato, puoi eseguire il tuo codice come al solito. Ogni volta che una query supera la soglia specificata, verrà registrata nel file di log. Questo ti permetterà di identificare le query che richiedono troppo tempo e potrebbero beneficiare di un’ottimizzazione.

La configurazione e l’utilizzo del registro delle query lente di MySQL può essere un passo significativo verso l’ottimizzazione delle prestazioni del tuo database, fornendoti gli strumenti per identificare le query problematiche e apportare le necessarie modifiche per migliorare la velocità e l’efficienza delle operazioni del database.

La fase successiva, una volta identificate le query lente e problematiche, consiste nell’analizzare e capire cosa esattamente contribuisce a rallentare le loro esecuzioni. MySQL offre uno strumento molto utile a tal fine: la parola chiave EXPLAIN. Questa keyword può essere utilizzata con una serie di istruzioni, tra cui SELECT, DELETE, INSERT, REPLACE e UPDATE, consentendo di analizzare e spiegare il piano di esecuzione di una query.

Utilizzando EXPLAIN, si può comprendere meglio come MySQL interpreta la query, fornendo un’analisi dettagliata del piano di esecuzione del database. Ecco un esempio di come si utilizza EXPLAIN in una query:

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

Il comando EXPLAIN posto davanti alla query indica a MySQL di restituire un piano di esecuzione per la query, piuttosto che eseguirla effettivamente.

Il risultato di questo comando sarà un report dettagliato che spiega come il database intende accedere ai dati per eseguire la query. Ogni riga nel risultato di EXPLAIN corrisponde a una tabella coinvolta nella query, fornendo informazioni dettagliate su come i dati vengono estratti da quella tabella.

Il report include informazioni come il tipo di join utilizzato, la chiave usata per il join, il numero di righe esaminate durante l’elaborazione della query e molto altro. Queste informazioni possono essere estremamente utili per identificare eventuali inefficienze o problemi che potrebbero rallentare l’esecuzione della query.

EXPLAIN è, quindi, un potente strumento per analizzare e ottimizzare le prestazioni delle query SQL in MySQL. Permette agli sviluppatori di comprendere meglio come le loro query vengono interpretate ed eseguite dal database, fornendo un’opportunità preziosa per migliorare l’efficienza e le prestazioni delle loro applicazioni.

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.

Gli indici rappresentano un elemento potente nel toolkit di un database, con la capacità di aumentare significativamente le prestazioni delle query. Si possono immaginare gli indici come il sistema di schede in una rubrica: invece di dover sfogliare tutte le pagine per trovare un nome specifico, si può semplicemente trascinare la scheda della lettera corrispondente per arrivare rapidamente alla pagina desiderata.

Lo stesso principio si applica alla gestione dei dati in un database. Gli indici possono essere utilizzati per eliminare passaggi non necessari attraverso le tabelle di dati, riducendo così il tempo necessario per eseguire le query.

Per esempio, si può aggiungere un indice alla colonna album_id della tabella picture con il seguente comando:

ALTER TABLE picture ADD INDEX(album_id);

Una volta che l’indice è stato creato, l’esecuzione della query non richiederà più una scansione completa della tabella delle immagini. Invece, il database scannerà prima tutti gli album per trovare quelli che appartengono a un utente specifico. Successivamente, le immagini corrispondenti possono essere rapidamente individuate utilizzando l’indice sulla colonna album_id.

Questo processo riduce notevolmente il numero di righe che devono essere scansionate. Ad esempio, se prima erano necessarie 1.000.000 di righe da scandire, con l’utilizzo dell’indice questo numero potrebbe essere ridotto a 200.000.

In termini di prestazioni, l’uso di indici può portare a miglioramenti significativi. La query potrebbe diventare fino a 317 volte più veloce rispetto alla versione originale senza indici.

In sintesi, l’implementazione di indici è un metodo efficace per ottimizzare le prestazioni di un database. Consentono al database di evitare scansioni complete delle tabelle, riducendo il tempo necessario per eseguire le query e migliorando la velocità generale delle operazioni del database.

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

ALTER TABLE album ADD INDEX(user_id);

In questo nuovo scenario, la tabella dell’album non viene più scandita integralmente. Al contrario, i record appropriati vengono localizzati in maniera efficiente mediante l’uso della chiave user_id. Una volta che questi 100 album vengono identificati e analizzati, le immagini associate vengono rapidamente trovate utilizzando la chiave album_id. Ogni tabella sfrutta una chiave per migliorare le prestazioni, rendendo la query fino a 380 volte più rapida rispetto alla versione originale senza indici.

Tuttavia, è importante sottolineare che non è sempre vantaggioso aggiungere indici a ogni colonna. Infatti, sebbene gli indici accelerino le operazioni di lettura, tendono a rallentare le operazioni di scrittura nel database. In altre parole, gli indici offrono vantaggi significativi in termini di velocità di lettura, ma possono avere un impatto negativo sulla velocità di scrittura nel database. Pertanto, è consigliabile aggiungere indici soltanto laddove apportano effettivamente un beneficio significativo alle prestazioni di lettura.

Per confermare l’efficacia degli indici, è possibile utilizzare di nuovo il comando EXPLAIN. Questo strumento può aiutarti a identificare e rimuovere qualsiasi indice che non viene utilizzato in modo significativo nelle query. È un’ottima pratica per garantire che il tuo database sia ottimizzato per la massima efficienza e prestazioni.

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

In alcune circostanze, potresti ritrovarti a gestire un database con query che si comportano in modo efficiente e veloce, eppure, per qualche motivo, il carico del server o del processo del database server aumenta drammaticamente. In tali situazioni, la causa potrebbe non essere direttamente attribuibile al database stesso, o almeno non esclusivamente. Potrebbe trattarsi invece di problemi a livello applicativo.

Un caso tipico potrebbe essere quello di uno script PHP che, a causa di un errore di programmazione, invoca in maniera ciclica una determinata query o esegue una query mal formulata, senza l’utilizzo di clausole adeguate per ottimizzare la velocità di esecuzione della query stessa.

Un esempio classico di questa problematica si presenta con la seguente sintomatologia: il database, che ha funzionato perfettamente per mesi o anni, inizia a soffrire di un aumento del carico di lavoro, nonostante non ci siano stati picchi o cambiamenti nei volumi di accessi o visite. Tutto ciò accade all’improvviso e senza una ragione apparente. La questione che sorge spontanea è: è colpa del database o è stata commessa un’errore a livello applicativo?

Per affrontare questa eventualità, il sistemista – ovvero l’esperto che si occupa della gestione e del monitoraggio del sistema – può ricorrere a strumenti di profilazione delle prestazioni come New Relic, oppure può utilizzare tool specializzati come Percona Toolkit, di cui abbiamo parlato in un precedente articolo.

Questi strumenti consentono di eseguire un’analisi approfondita del comportamento dell’applicazione e del database, identificando eventuali colli di bottiglia o problemi di performance. Attraverso l’utilizzo di questi strumenti, si può quindi ottenere una visione chiara di ciò che sta causando l’aumento del carico del server, consentendo quindi di intervenire in modo mirato e risolvere il problema, migliorando l’efficienza generale del sistema.

Aggiorna all’ultima versione di MySQL o fai Rollback ad una versione precedente.

Un approccio diretto ma efficace per migliorare le prestazioni e ridurre il carico del tuo sistema di gestione del database è quello di effettuare l’aggiornamento alla versione più recente di MySQL o dei suoi fork. Anche se potrebbe sembrare un’ovvietà, è importante sottolineare che l’upgrade all’ultima versione può portare automaticamente a un miglioramento dei tempi di esecuzione delle query e a un abbassamento del carico del DBMS.

Abbiamo ricevuto testimonianze dirette da numerosi clienti nel corso del 2021 (almeno 4) che avevano affrontato problemi simili: query estremamente lente che sono diventate molto più veloci (passando da 10 secondi a 0,2, per fare un esempio) semplicemente passando da Percona Server 5.6 a Percona Server 5.7.

Ovviamente, lo stesso concetto si applica anche a un’ulteriore migrazione di versione, come il passaggio da MySQL 5.7 a MySQL 8.0. I benchmark disponibili online offrono un’idea dettagliata dei potenziali vantaggi che si possono ottenere.

Questo è sicuramente un percorso che vale la pena esplorare prima di immergersi nella complessità della profilazione e dell’ottimizzazione delle query. Aggiornare il software può fornire una soluzione rapida ed efficiente, spesso risolvendo i problemi entro un paio d’ore, con costi minimi.

Certamente, dal punto di vista accademico o per gli amanti della pura teoria, può sembrare sbagliato lasciare query lente e mal progettate che vengono eseguite più velocemente. Tuttavia, bisogna valutare anche la prospettiva imprenditoriale e pragmatica della situazione.

Spesso, l’obiettivo principale è risolvere un problema nel minor tempo possibile e con costi contenuti. Seguire questa strada non ci renderà esperti assoluti dell’SQL standard ANSI, ma gli imprenditori apprezzano soluzioni veloci, economiche e che funzionano.

Come si dice spesso, “l’importante è che funzioni”. L’importante è raggiungere il risultato desiderato nel modo più efficiente e conveniente possibile.

Provare a modificare il DBMS tra MySQL verso Percona Server o verso MariaDB.

Se stessi riscontrando delle prestazioni insufficienti dalle tue query SQL su MySQL, una delle opzioni da considerare potrebbe essere quella di valutare l’utilizzo di alternative come Percona Server o MariaDB.

Percona Server e MariaDB sono due importanti alternative a MySQL, entrambe con un impegno specifico nel fornire migliorie significative sia in termini di performance che di funzionalità aggiuntive rispetto all’originale MySQL.

Percona Server è una variante di MySQL ideata e gestita da Percona, un’organizzazione specializzata nella creazione e manutenzione di soluzioni per database open source. Questa distribuzione si distingue per le sue significative ottimizzazioni di performance, che includono una gestione della memoria più raffinata, un sistema di ottimizzazione delle query più sofisticato e un’architettura di storage altamente efficiente. Percona Server è rinomato anche per il supporto all’engine di storage XtraDB, una versione potenziata dell’InnoDB di MySQL, molto apprezzato per le sue prestazioni di alto livello e la sua affidabilità. Grazie a queste caratteristiche, Percona Server si rivela una soluzione preferibile per applicazioni che richiedono un’intensa elaborazione delle transazioni.

D’altro canto, MariaDB è un fork di MySQL sviluppato inizialmente da Monty Widenius, uno dei co-fondatori di MySQL. MariaDB offre notevoli vantaggi in termini di performance rispetto a MySQL, tra cui un’ottimizzazione del codice più efficace, un’architettura di storage rinnovata e un’implementazione più avanzata del protocollo di comunicazione. MariaDB si distingue inoltre per le sue funzionalità aggiuntive, come l’introduzione di nuovi tipi di dati, nuove funzioni SQL e migliorie nella gestione delle transazioni. Questo lo rende una scelta privilegiata per coloro che cercano di sfruttare i benefici di performance di MariaDB pur mantenendo la compatibilità con le applicazioni esistenti sviluppate per MySQL.

Sia Percona Server che MariaDB sono pienamente compatibili con MySQL, rendendole facilmente implementabili su molte piattaforme. Tuttavia, è essenziale effettuare adeguati test di carico prima di introdurre modifiche sostanziali all’ambiente del tuo database. Questo ti permetterà di valutare se queste alternative possano realmente portare a un miglioramento delle prestazioni in base alle specifiche necessità del tuo sistema. È fortemente consigliato eseguire i test in condizioni di utilizzo il più possibile simili a quelle reali, al fine di valutare l’effetto delle modifiche sul tuo applicativo e assicurarti una transizione senza problemi.

Se MySQL non è un DBMS vincolante, valutare di migrare il DBMS verso il più performante PostgreSQL.

Se stai affrontando problemi di prestazioni o stai cercando un’alternativa più performante a MySQL, potrebbe essere utile esplorare la possibilità di migrare il tuo database verso un sistema di gestione di database diverso come PostgreSQL.

PostgreSQL è un DBMS relazionale open-source che offre numerose funzionalità avanzate e prestazioni elevate. È noto per la sua affidabilità, robustezza e capacità di gestire carichi di lavoro complessi. Sebbene possa sembrare una soluzione drastica, la migrazione a PostgreSQL può portare a importanti miglioramenti delle prestazioni e offrire un’ampia gamma di funzionalità avanzate.

Una delle principali caratteristiche di PostgreSQL è il suo avanzato ottimizzatore delle query, che consente di creare piani di esecuzione efficienti per le query. Ciò si traduce in un’elaborazione più rapida delle query e tempi di risposta ridotti. Inoltre, PostgreSQL supporta transazioni ACID (Atomicity, Consistency, Isolation, Durability), garantendo la coerenza dei dati e la protezione da guasti e interruzioni.

PostgreSQL offre anche un set completo di tipi di dati, tra cui tipi JSON, array multidimensionali e geometrie spaziali, che consentono una maggiore flessibilità nella gestione dei dati. Inoltre, supporta le stored procedure, i trigger e le funzioni definite dall’utente, offrendo un’ampia possibilità di personalizzazione e estensione del database.

La community di PostgreSQL è molto attiva e offre un vasto supporto e risorse, compresi forum di discussione, documentazione dettagliata e una vasta selezione di estensioni e plugin che possono essere utilizzati per arricchire le funzionalità del database.

Migrare a PostgreSQL potrebbe richiedere uno sforzo iniziale in termini di pianificazione e conversione dei dati, ma potrebbe essere una scelta strategica a lungo termine per migliorare le prestazioni e sfruttare al meglio le funzionalità avanzate offerte da questo potente DBMS.

È importante sottolineare che la migrazione del database è un processo complesso e richiede una valutazione approfondita dei requisiti e delle specifiche del tuo sistema. Prima di prendere una decisione, è consigliabile eseguire test di carico e valutare attentamente i vantaggi e gli svantaggi di una migrazione. Tuttavia, tenere sempre in considerazione l’opzione di PostgreSQL può offrire nuove opportunità per migliorare le prestazioni del tuo database e soddisfare meglio le tue esigenze attuali e future.

Secondo diversi test e benchmark, PostgreSQL offre prestazioni fino a due volte superiori a MySQL e ai suoi fork. Tuttavia, la migrazione verso un nuovo database management system (DBMS) non è un compito semplice e richiede una valutazione accurata sia del tuo dataset che dello schema del database, nonché eventuali modifiche che potrebbero essere necessarie a livello applicativo.

Se stai utilizzando un’applicazione proprietaria, potrebbe essere particolarmente vantaggioso eseguire la migrazione, poiché avrai la possibilità di adattare il codice dell’applicazione per sfruttare al meglio le caratteristiche di PostgreSQL. Tuttavia, è importante tenere presente che la migrazione verso un nuovo DBMS può essere un compito impegnativo e richiedere tempo ed energie. Prima di intraprendere questa strada, assicurati di valutare attentamente i pro e i contro in base alle tue specifiche esigenze.

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 stai affrontando problemi di prestazioni con le tue query SQL e il tuo database non sta fornendo le prestazioni desiderate, siamo qui per aiutarti! Siamo esperti nell’ottimizzazione delle prestazioni dei database e possiamo offrirti soluzioni su misura per risolvere i tuoi problemi di velocità.

Comprendiamo l’importanza di un database performante per le tue attività quotidiane. Query lente e tempi di risposta prolungati possono rallentare le operazioni del tuo sistema, causando ritardi e frustrazione per te e i tuoi utenti. È fondamentale garantire che il tuo database sia configurato correttamente e che le query siano ottimizzate per garantire la massima efficienza.

Il nostro team di esperti può eseguire una profilazione dettagliata del tuo database, analizzando le query problematiche e identificando le aree che richiedono ottimizzazione. Utilizzando strumenti avanzati come Percona Toolkit, EXPLAIN e altre tecniche di analisi delle prestazioni, possiamo individuare le query che consumano più tempo e risorse, identificando i colli di bottiglia e le opportunità di miglioramento.

Una volta identificate le aree critiche, svilupperemo una strategia personalizzata per migliorare le prestazioni del tuo database. Ci avvaliamo di tecniche di ottimizzazione delle query, indicizzazione intelligente, ottimizzazione delle strutture dati e altre metodologie avanzate per ottimizzare il tuo database e garantire tempi di risposta rapidi e prestazioni ottimali.

Non lasciare che i problemi di prestazioni ti rallentino! Contattaci oggi stesso e lascia che i nostri esperti si occupino dei tuoi problemi di velocità. Siamo qui per offrirti soluzioni concrete e personalizzate per ottenere le prestazioni che desideri dal tuo database. Non perdere tempo prezioso, contattaci e inizia a risolvere i tuoi problemi di performance ora!