10 suggerimenti essenziali sulle prestazioni per MySQL

Come con tutti i database relazionali, MySQL può dimostrarsi una bestia complicata, in grado di arrestarsi in un attimo, lasciando le tue applicazioni in balia e il tuo business in pericolo.

La verità è che gli errori comuni sono alla base della maggior parte dei problemi di prestazioni di MySQL. Per garantire che il tuo server MySQL funzioni alla massima velocità, fornendo prestazioni stabili e costanti, è importante eliminare questi errori, che sono spesso oscurati da qualche sottigliezza nel carico di lavoro o da una trappola di configurazione.

Fortunatamente, molti problemi di prestazioni di MySQL risultano avere soluzioni simili, rendendo la risoluzione dei problemi e l'ottimizzazione di MySQL un'attività gestibile.

Ecco 10 suggerimenti per ottenere grandi prestazioni da MySQL.

Suggerimento n. 1 sulle prestazioni di MySQL: profila il tuo carico di lavoro

Il modo migliore per capire come il tuo server impiega il suo tempo è profilare il carico di lavoro del server. Profilando il carico di lavoro, è possibile esporre le query più costose per ulteriori ottimizzazioni. In questo caso, il tempo è la metrica più importante perché quando invii una query al server, ti interessa molto poco di tutto tranne la velocità con cui viene completata.

Il modo migliore per profilare il tuo carico di lavoro è con uno strumento come l'analizzatore di query di MySQL Enterprise Monitor o il pt-query-digest dal Percona Toolkit. Questi strumenti catturano le query eseguite dal server e restituiscono una tabella di attività ordinate in ordine decrescente di tempo di risposta, portando immediatamente in alto le attività più costose e dispendiose in termini di tempo in modo da poter vedere dove concentrare i propri sforzi.

Gli strumenti di profilazione del carico di lavoro raggruppano query simili, consentendo di visualizzare le query lente, nonché quelle veloci ma eseguite molte volte.

Suggerimento n. 2 sulle prestazioni di MySQL: comprendi le quattro risorse fondamentali

Per funzionare, un server di database necessita di quattro risorse fondamentali: CPU, memoria, disco e rete. Se uno di questi è debole, irregolare o sovraccarico, è molto probabile che il server del database non funzioni correttamente.

Comprendere le risorse fondamentali è importante in due aree particolari: la scelta dell'hardware e la risoluzione dei problemi.

Quando scegli l'hardware per MySQL, assicurati che i componenti funzionino bene ovunque. Altrettanto importante, bilanciarli ragionevolmente bene l'uno contro l'altro. Spesso, le organizzazioni selezionano server con CPU e dischi veloci ma che hanno fame di memoria. In alcuni casi, l'aggiunta di memoria è un modo economico per aumentare le prestazioni di ordini di grandezza, in particolare sui carichi di lavoro associati al disco. Questo potrebbe sembrare controintuitivo, ma in molti casi i dischi sono sovrautilizzati perché non c'è abbastanza memoria per contenere il working set di dati del server.

Un altro buon esempio di questo equilibrio riguarda le CPU. Nella maggior parte dei casi, MySQL funzionerà bene con CPU veloci perché ogni query viene eseguita in un singolo thread e non può essere parallelizzata tra le CPU.

Quando si tratta di risoluzione dei problemi, controllare le prestazioni e l'utilizzo di tutte e quattro le risorse, con un occhio attento nel determinare se stanno funzionando male o semplicemente viene chiesto di fare troppo lavoro. Questa conoscenza può aiutare a risolvere rapidamente i problemi.

Suggerimento n. 3 sulle prestazioni di MySQL: non utilizzare MySQL come coda

Code e modelli di accesso simili a code possono intrufolarsi nella tua applicazione a tua insaputa. Ad esempio, se imposti lo stato di un elemento in modo che un particolare processo di lavoro possa richiederlo prima di agire su di esso, stai involontariamente creando una coda. Contrassegnare le e-mail come non inviate, inviarle e quindi contrassegnarle come inviate è un esempio comune.

Le code causano problemi per due motivi principali: serializzano il carico di lavoro, impedendo che le attività vengano eseguite in parallelo e spesso danno come risultato una tabella che contiene il lavoro in corso, nonché i dati storici dei lavori elaborati molto tempo fa. Entrambi aggiungono latenza all'applicazione e vengono caricati su MySQL.

Suggerimento n. 4 sulle prestazioni di MySQL: filtra i risultati in base ai meno costosi

Un ottimo modo per ottimizzare MySQL è fare prima un lavoro poco preciso e impreciso, poi il lavoro duro e preciso sul set di dati più piccolo e risultante.

Ad esempio, supponi di cercare qualcosa entro un dato raggio di un punto geografico. Il primo strumento nella cassetta degli attrezzi di molti programmatori è la formula del cerchio grande (Haversine) per calcolare la distanza lungo la superficie di una sfera. Il problema con questa tecnica è che la formula richiede molte operazioni trigonometriche, che richiedono molta CPU. I calcoli del cerchio grande tendono a essere lenti e fanno salire alle stelle l'utilizzo della CPU della macchina.

Prima di applicare la formula del cerchio grande, riduci i tuoi record a un piccolo sottoinsieme del totale e ritaglia il set risultante in un cerchio preciso. Un quadrato che contiene il cerchio (in modo preciso o impreciso) è un modo semplice per farlo. In questo modo, il mondo fuori dalla piazza non viene mai colpito da tutte quelle costose funzioni trigonometriche.

Suggerimento n. 5 sulle prestazioni di MySQL: conoscere le due trappole mortali della scalabilità

La scalabilità non è così vaga come potresti credere. In effetti, esistono precise definizioni matematiche di scalabilità espresse come equazioni. Queste equazioni evidenziano il motivo per cui i sistemi non si scalano come dovrebbero.

Prendi la legge sulla scalabilità universale, una definizione utile per esprimere e quantificare le caratteristiche di scalabilità di un sistema. Spiega i problemi di ridimensionamento in termini di due costi fondamentali: serializzazione e crosstalk.

I processi paralleli che devono interrompersi affinché avvenga qualcosa di serializzato sono intrinsecamente limitati nella loro scalabilità. Allo stesso modo, se i processi paralleli hanno bisogno di chattare tra loro tutto il tempo per coordinare il loro lavoro, si limitano a vicenda.

Evita la serializzazione e il crosstalk e la tua applicazione scalerà molto meglio. In cosa si traduce questo all'interno di MySQL? Varia, ma alcuni esempi potrebbero evitare blocchi esclusivi sulle righe. Le code, punto n. 3 sopra, tendono a scalare male per questo motivo.

Suggerimento per le prestazioni di MySQL n. 6: non concentrarti troppo sulla configurazione

Gli amministratori di database tendono a dedicare un'enorme quantità di tempo alla modifica delle configurazioni. Il risultato di solito non è un grande miglioramento e talvolta può anche essere molto dannoso. Ho visto molti server "ottimizzati" che si bloccavano costantemente, esaurivano la memoria e funzionavano male quando il carico di lavoro diventava un po 'più intenso.

Le impostazioni predefinite fornite con MySQL sono di taglia unica e sono decisamente obsolete, ma non è necessario configurare tutto. È meglio impostare correttamente i fondamentali e modificare altre impostazioni solo se necessario. Nella maggior parte dei casi, è possibile ottenere il 95% delle prestazioni massime del server impostando correttamente circa 10 opzioni. Le poche situazioni in cui ciò non si applica saranno casi limite unici per le tue circostanze.

Nella maggior parte dei casi, gli strumenti di "ottimizzazione" del server non sono consigliati perché tendono a fornire linee guida che non hanno senso per casi specifici. Alcuni contengono persino consigli pericolosi e imprecisi, come i rapporti di riscontro della cache e le formule di consumo della memoria. Queste non erano mai giuste e sono diventate ancora meno corrette col passare del tempo.

Suggerimento n. 7 sulle prestazioni di MySQL: attenzione alle query di impaginazione

Le applicazioni che si impaginano tendono a mettere in ginocchio il server. Nel mostrare una pagina di risultati, con un collegamento per andare alla pagina successiva, queste applicazioni in genere si raggruppano e ordinano in modi che non possono utilizzare gli indici, e utilizzano un LIMITe offsetche fanno sì che il server faccia molto lavoro generando, quindi scartando le righe.

Le ottimizzazioni possono spesso essere trovate nell'interfaccia utente stessa. Invece di mostrare il numero esatto di pagine nei risultati e i link a ciascuna pagina singolarmente, puoi semplicemente mostrare un link alla pagina successiva. Puoi anche impedire alle persone di accedere a pagine troppo lontane dalla prima pagina.

Sul lato query, invece di utilizzare LIMITcon offset, è possibile selezionare una riga in più del necessario e quando l'utente fa clic sul collegamento "pagina successiva", è possibile designare quella riga finale come punto di partenza per la serie di risultati successiva. Ad esempio, se l'utente ha visualizzato una pagina con le righe da 101 a 120, selezionare anche la riga 121; per eseguire il rendering della pagina successiva, interrogare il server per righe maggiori o uguali a 121, limite 21.

Suggerimento n. 8 sulle prestazioni di MySQL: salva le statistiche con entusiasmo, avvisa con riluttanza

Il monitoraggio e gli avvisi sono essenziali, ma cosa succede al tipico sistema di monitoraggio? Inizia a inviare falsi positivi e gli amministratori di sistema impostano regole di filtraggio della posta per fermare il rumore. Presto il tuo sistema di monitoraggio è completamente inutile.

Mi piace pensare al monitoraggio in due modi: acquisire metriche e allertare. È molto importante acquisire e salvare tutte le metriche possibili perché sarai felice di averle quando proverai a capire cosa è cambiato nel sistema. Un giorno si presenterà uno strano problema e adorerai la possibilità di puntare a un grafico e mostrare un cambiamento nel carico di lavoro del server.

Al contrario, c'è la tendenza ad allertare troppo. Le persone spesso avvisano su cose come il rapporto di buffer hit o il numero di tabelle temporanee create al secondo. Il problema è che non esiste una buona soglia per un tale rapporto. La soglia giusta non è solo diversa da server a server, ma di ora in ora man mano che il carico di lavoro cambia.

Di conseguenza, prestare attenzione con parsimonia e solo alle condizioni che indicano un problema definito e perseguibile. Un basso rapporto di hit del buffer non è utilizzabile, né indica un problema reale, ma un server che non risponde a un tentativo di connessione è un problema reale che deve essere risolto.

Suggerimento n. 9 sulle prestazioni di MySQL: impara le tre regole di indicizzazione

L'indicizzazione è probabilmente l'argomento più frainteso nei database perché ci sono tanti modi per confondersi su come funzionano gli indici e su come il server li utilizza. Ci vuole un grande sforzo per capire veramente cosa sta succedendo.

Gli indici, se progettati correttamente, servono a tre scopi importanti in un server di database:

  1. Gli indici consentono al server di trovare gruppi di righe adiacenti invece di righe singole. Molte persone pensano che lo scopo di un indice sia quello di trovare singole righe, ma trovare singole righe porta a operazioni del disco casuali, il che è lento. È molto meglio trovare gruppi di righe, tutte o la maggior parte delle quali sono interessanti, piuttosto che trovare righe una alla volta.
  2. Gli indici consentono al server di evitare l'ordinamento leggendo le righe nell'ordine desiderato. Lo smistamento è costoso. La lettura delle righe nell'ordine desiderato è molto più veloce.
  3. Gli indici consentono al server di soddisfare intere query dal solo indice, evitando la necessità di accedere alla tabella. Questo è variamente noto come indice di copertura o query di solo indice.

Se puoi progettare i tuoi indici e le tue query per sfruttare queste tre opportunità, puoi rendere le tue query più veloci di diversi ordini di grandezza.

Suggerimento n. 10 sulle prestazioni di MySQL: sfruttare l'esperienza dei tuoi colleghi

Non provare a farlo da solo. Se ti stai interrogando su un problema e stai facendo ciò che ti sembra logico e sensato, è fantastico. Questo funzionerà circa 19 volte su 20. L'altra volta, andrai in una tana di coniglio che sarà molto costoso e richiederà tempo, proprio perché la soluzione che stai cercando sembra avere molto senso.

Crea una rete di risorse relative a MySQL e questo va oltre i set di strumenti e le guide per la risoluzione dei problemi. Ci sono alcune persone estremamente competenti in agguato su mailing list, forum, siti web di domande e risposte e così via. Conferenze, fiere ed eventi di gruppi di utenti locali offrono preziose opportunità per acquisire informazioni e costruire relazioni con colleghi che possono aiutarti in un attimo.

Per coloro che cercano strumenti per integrare questi suggerimenti, è possibile controllare Percona Configuration Wizard per MySQL, Percona Query Advisor per MySQL e Percona Monitoring Plugins. (Nota: dovrai creare un account Percona per accedere a questi primi due link. È gratuito.) La configurazione guidata può aiutarti a generare un file my.cnf di base per un nuovo server che è superiore ai file di esempio forniti con il server. Il consulente query analizzerà l'SQL per rilevare modelli potenzialmente dannosi come le query di impaginazione (n. 7). I plug-in di monitoraggio di Percona sono un insieme di plug-in di monitoraggio e grafici che consentono di salvare le statistiche con entusiasmo e avvisare con riluttanza (n. 8). Tutti questi strumenti sono disponibili gratuitamente.