Indice dei contenuti dell'articolo:
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
Aggiorna all’ultima versione di MySQL o fai Rollback ad una versione precedente.
Per quanto possa sembrare (ed in effetti lo è) l’uovo di Colombo, vale la pena ricordare che aggiornare all’ultima versione di MySQL (o relativi Fork) potrebbe automaticamente migliorare i tempi di esecuzione delle query e abbassare il carico del tuo DBMS.
Abbiamo testimonianze dirette di nostri clienti (almeno 4 nel 2021) che avevano questo tipo di problematiche, ovvero delle query tremendamente lente che sono diventate veloci (da 10 secondi a 0,2 per intenderci) semplicemente passando da Percona Server 5.6 a Percona Server 5.7.
Ovviamente lo stesso concetto vale per un salto di versione a salire come da MySQL 5.7 a MySQL 8.0. I benchmark che si trovano online lasciano intuire dettagliatamente dei possibili e potenziali vantaggi che si possono ottenere.
Sicuramente è una strada da percorrere subito prima di iniziare a fasciarsi la testa su profilazione ed ottimizzazione.
Si comprende come da un punto di vista accademico e per i puristi possa sembrare un abominio lasciare query lente mal progettate che però vengono eseguite velocemente, ma bisogna anche valutare la cosa dal punto di vista imprenditoriale e della pragmaticità reale.
Si ha infatti spesso bisogno di risolvere un problema nell’arco di un paio d’ore al massimo e con costi minimi. Tentare questa strada non farà di noi delle cinture nere decimo dan di SQL Standard ANSI, ma gli imprenditori hanno sempre apprezzato le soluzioni veloci, economiche e che funzionano.
Spesso come si dice “L’importante è che funzioni”.
Provare a modificare il DBMS tra MySQL verso Percona Server o verso MariaDB.
Se stai 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 è una distribuzione di MySQL che offre miglioramenti prestazionali e strumenti di gestione aggiuntivi. Offre anche supporto per lo storage engine XtraDB, che fornisce miglioramenti di performance rispetto allo storage engine InnoDB di base di MySQL.
MariaDB, d’altra parte, è un fork di MySQL sviluppato originariamente da Monty Widenius, uno dei co-fondatori di MySQL. Offre numerosi miglioramenti prestazionali rispetto a MySQL, nonché una serie di funzionalità aggiuntive.
Entrambe le soluzioni sono perfettamente compatibili con MySQL e possono essere facilmente installate sulla maggior parte delle piattaforme. Tuttavia, è importante eseguire i test di carico appropriati prima di apportare modifiche significative all’installazione del database. In questo modo, sarai in grado di valutare se uno di questi sistemi possa effettivamente fornire un miglioramento delle prestazioni per le tue specifiche esigenze.
Se MySQL non è un DBMS vincolante, valutare di migrare il DBMS verso il più performante PostgreSQL.
Se non sei vincolato a MySQL o ai suoi fork come Percona Server o MariaDB, potrebbe essere utile valutare la possibilità di migrare il tuo database verso una soluzione più performante come PostgreSQL.
Comprendiamo come questa appena proposta possa non essere una vera soluzione al tuo problema specifico su MySQL, ma è comunque una strada poco percorsa e consigliata da tenere sempre in considerazione sia nella risoluzione dei problemi attuali che anche quelli futuri.
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 ?
Hai problemi di performance con le tue query SQL? Non riesci a ottenere le prestazioni desiderate dal tuo database? Contattaci! Siamo esperti nell’ottimizzazione delle prestazioni dei database e possiamo aiutarti a risolvere i tuoi problemi di velocità. Con una profilatura accurata della tua attuale configurazione e una serie di ottimizzazioni mirate, possiamo aiutarti a ottenere le prestazioni che desideri dal tuo database. Non lasciare che i problemi di performance ti rallentino, contattaci oggi stesso per iniziare a risolvere i tuoi problemi!
