Come aumentare di 10 volte il numero di query al database senza passare a un server più produttivo e mantenere la funzionalità del sistema? Ti dirò come abbiamo affrontato il calo delle prestazioni del nostro database, come abbiamo ottimizzato le query SQL per servire il maggior numero possibile di utenti e non aumentare il costo delle risorse di calcolo.
Realizzo un servizio per la gestione dei processi aziendali nelle imprese di costruzione. Con noi lavorano circa 3mila aziende. Più di 10mila persone lavorano ogni giorno con il nostro sistema per 4-10 ore. Risolve vari problemi di pianificazione, notifica, avviso, convalida... Usiamo PostgreSQL 9.6. Abbiamo circa 300 tabelle nel database e ogni giorno riceviamo fino a 200 milioni di query (10mila diverse). In media abbiamo 3-4mila richieste al secondo, nei momenti di maggior attività più di 10mila richieste al secondo. La maggior parte delle query sono OLAP. Sono presenti molte meno aggiunte, modifiche ed eliminazioni, il che significa che il carico OLTP è relativamente leggero. Ho fornito tutti questi numeri affinché tu possa valutare la portata del nostro progetto e capire quanto possa esserti utile la nostra esperienza.
Immagine uno. Lirico
Quando abbiamo iniziato lo sviluppo, non abbiamo davvero pensato al tipo di carico che sarebbe caduto sul database e a cosa avremmo fatto se il server avesse smesso di funzionare. Durante la progettazione del database, abbiamo seguito le raccomandazioni generali e abbiamo cercato di non darci la zappa sui piedi, ma siamo andati oltre i consigli generali come “non utilizzare lo schema non siamo entrati. Abbiamo progettato basandoci sui principi di normalizzazione, evitando la ridondanza dei dati e non ci siamo preoccupati di velocizzare determinate query. Non appena sono arrivati i primi utenti, abbiamo riscontrato un problema di prestazioni. Come al solito, eravamo completamente impreparati a questo. I primi problemi si sono rivelati semplici. Di norma, tutto veniva risolto aggiungendo un nuovo indice. Ma arrivò il momento in cui le semplici patch smisero di funzionare. Rendendoci conto che ci manca esperienza e che sta diventando sempre più difficile per noi capire cosa sta causando i problemi, abbiamo assunto degli specialisti che ci hanno aiutato a configurare correttamente il server, a connettere il monitoraggio e ci hanno mostrato dove cercare per ottenere .
Immagine due. Statistico
Quindi abbiamo circa 10mila query diverse che vengono eseguite sul nostro database ogni giorno. Di questi 10mila, ci sono mostri che vengono eseguiti 2-3 milioni di volte con un tempo di esecuzione medio di 0.1-0.3 ms, e ci sono query con un tempo di esecuzione medio di 30 secondi che vengono chiamate 100 volte al giorno.
Non è stato possibile ottimizzare tutte le 10mila query, quindi abbiamo deciso di capire dove indirizzare i nostri sforzi per migliorare correttamente le prestazioni del database. Dopo diverse iterazioni, abbiamo iniziato a dividere le richieste in tipologie.
Richieste TOP
Queste sono le query più pesanti che richiedono più tempo (tempo totale). Si tratta di query che vengono chiamate molto spesso o di query che richiedono molto tempo per essere eseguite (le query lunghe e frequenti sono state ottimizzate nelle prime iterazioni della lotta per la velocità). Di conseguenza, il server dedica la maggior parte del tempo alla loro esecuzione. Inoltre, è importante separare le richieste principali in base al tempo di esecuzione totale e separatamente in base al tempo di IO. I metodi per ottimizzare tali query sono leggermente diversi.
La pratica abituale di tutte le aziende è lavorare con richieste TOP. Ce ne sono pochi; l’ottimizzazione anche di una sola query può liberare il 5-10% delle risorse. Tuttavia, man mano che il progetto matura, l'ottimizzazione delle query TOP diventa un compito sempre più non banale. Tutti i metodi semplici sono già stati elaborati e la richiesta più “pesante” richiede “solo” il 3-5% delle risorse. Se le query TOP in totale richiedono meno del 30-40% delle volte, molto probabilmente hai già fatto degli sforzi per farle funzionare rapidamente ed è ora di passare all'ottimizzazione delle query del gruppo successivo.
Resta da rispondere alla domanda su quante query principali dovrebbero essere incluse in questo gruppo. Di solito ne prendo almeno 10, ma non più di 20. Cerco di assicurarmi che il tempo del primo e dell'ultimo nel gruppo TOP differisca di non più di 10 volte. Cioè, se il tempo di esecuzione della query scende bruscamente dal 1° al 10°, prendo TOP-10, se il calo è più graduale, aumento la dimensione del gruppo a 15 o 20.
Contadini medi
Queste sono tutte le richieste che vengono immediatamente dopo TOP, ad eccezione dell'ultimo 5-10%. Di solito, nell'ottimizzazione di queste query si nasconde l'opportunità di aumentare notevolmente le prestazioni del server. Tali richieste possono pesare fino all'80%. Ma anche se la loro quota supera il 50%, allora è tempo di esaminarli con più attenzione.
Coda
Come accennato, queste query arrivano alla fine e richiedono il 5-10% delle volte. Puoi dimenticartene solo se non utilizzi strumenti di analisi automatica delle query, quindi ottimizzarli può anche essere economico.
Come valutare ciascun gruppo?
Utilizzo una query SQL che aiuta a effettuare tale valutazione per PostgreSQL (sono sicuro che una query simile può essere scritta per molti altri DBMS)
Query SQL per stimare la dimensione dei gruppi TOP-MEDIUM-TAIL
SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top,
CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
CASE WHEN rn > 800 THEN tt_percent ELSE 0 END AS time_tail
FROM (
SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
FROM pg_stat_statements
ORDER BY total_time DESC
) AS t
)
AS ts
Il risultato della query è costituito da tre colonne, ciascuna delle quali contiene la percentuale di tempo necessaria per elaborare le query di questo gruppo. All'interno della richiesta ci sono due numeri (nel mio caso è 20 e 800) che separano le richieste di un gruppo da un altro.
Ecco come si confrontano approssimativamente le quote di richieste al momento dell'inizio del lavoro di ottimizzazione e adesso.
Il diagramma mostra che la quota delle richieste TOP è nettamente diminuita, ma i “contadini medi” sono aumentati.
Inizialmente, le richieste TOP contenevano errori palesi. Nel corso del tempo, le malattie infantili sono scomparse, la quota di richieste TOP è diminuita e si sono dovuti compiere sforzi sempre maggiori per accelerare le richieste difficili.
Per ottenere il testo delle richieste utilizziamo la seguente request
SELECT * FROM (
SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
FROM pg_stat_statements
ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800 -- TAIL
Ecco un elenco delle tecniche più comunemente utilizzate che ci hanno aiutato a velocizzare le query TOP:
- Riprogettazione del sistema, ad esempio, rielaborando la logica di notifica utilizzando un broker di messaggi invece di query periodiche al database
- Aggiunta o modifica degli indici
- Riscrittura delle query ORM in SQL puro
- Riscrittura della logica di caricamento lento dei dati
- Caching tramite denormalizzazione dei dati. Ad esempio, abbiamo una connessione tabella Consegna -> Fattura -> Richiesta -> Applicazione. Cioè ogni consegna è associata ad un'applicazione tramite altre tabelle. Per non collegare tutte le tabelle in ciascuna richiesta, abbiamo duplicato il collegamento alla richiesta nella tabella Consegna.
- Memorizzazione nella cache di tabelle statiche con libri di consultazione e tabelle che cambiano raramente nella memoria del programma.
A volte le modifiche equivalevano a una riprogettazione impressionante, ma fornivano il 5-10% del carico del sistema ed erano giustificate. Nel corso del tempo, lo scarico è diventato sempre più piccolo ed è stata necessaria una riprogettazione sempre più seria.
Quindi abbiamo rivolto la nostra attenzione al secondo gruppo di richieste: il gruppo dei contadini medi. Contiene molte altre domande e sembrava che ci sarebbe voluto molto tempo per analizzare l'intero gruppo. Tuttavia, la maggior parte delle query si è rivelata molto semplice da ottimizzare e molti problemi sono stati ripetuti decine di volte in diverse varianti. Ecco alcuni esempi di alcune ottimizzazioni tipiche che abbiamo applicato a dozzine di query simili e ciascun gruppo di query ottimizzate ha scaricato il database del 3-5%.
- Invece di verificare la presenza di record utilizzando COUNT e una scansione completa della tabella, si è iniziato a utilizzare EXISTS
- Eliminato DISTINCT (non esiste una ricetta generale, ma a volte puoi liberartene facilmente accelerando la richiesta di 10-100 volte).
Ad esempio, invece di una query per selezionare tutti gli autisti da una grande tabella di consegne (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDha effettuato una query su una tabella relativamente piccola PERSON
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Sembrerebbe che abbiamo utilizzato una sottoquery correlata, ma fornisce un aumento di velocità di oltre 10 volte.
- In molti casi, COUNT è stato abbandonato del tutto e
- invece di
UPPER(s) LIKE JOHN%’utilizzare
s ILIKE “John%”
Ogni richiesta specifica veniva talvolta accelerata di 3-1000 volte. Nonostante le prestazioni impressionanti, all'inizio ci è sembrato che non avesse senso ottimizzare una query che richiede 10 ms per essere completata, è una delle 3 query più pesanti e occupa un centesimo di percentuale del tempo di caricamento complessivo del database. Ma applicando la stessa ricetta a un gruppo di query dello stesso tipo, abbiamo recuperato una piccola percentuale. Per non perdere tempo rivedendo manualmente tutte le centinaia di query, abbiamo scritto diversi semplici script che utilizzavano espressioni regolari per trovare query dello stesso tipo. Di conseguenza, la ricerca automatica di gruppi di query ci ha permesso di migliorare ulteriormente le nostre prestazioni con uno sforzo modesto.
Di conseguenza, lavoriamo sullo stesso hardware ormai da tre anni. Il carico medio giornaliero è di circa il 30%, nei picchi arriva al 70%. Il numero di richieste, così come il numero di utenti, è aumentato di circa 10 volte. E tutto questo grazie al monitoraggio costante di questi stessi gruppi di richieste TOP-MEDIUM. Non appena appare una nuova richiesta nel gruppo TOP, la analizziamo immediatamente e proviamo a velocizzarla. Esaminiamo il gruppo MEDIUM una volta alla settimana utilizzando script di analisi delle query. Se incontriamo nuove query che sappiamo già come ottimizzare, le modifichiamo rapidamente. A volte troviamo nuovi metodi di ottimizzazione che possono essere applicati a più query contemporaneamente.
Secondo le nostre previsioni, il server attuale resisterà ad un aumento del numero di utenti di altre 3-5 volte. È vero, abbiamo un altro asso nella manica: non abbiamo ancora trasferito le query SELECT al mirror, come raccomandato. Ma non lo facciamo consapevolmente, perché vogliamo prima esaurire completamente le possibilità di ottimizzazione “intelligente” prima di accendere “l’artiglieria pesante”.
Uno sguardo critico al lavoro svolto può suggerire l'utilizzo del ridimensionamento verticale. Acquista un server più potente invece di perdere tempo con gli specialisti. Il server potrebbe non costare molto, soprattutto perché non abbiamo ancora esaurito i limiti dello scaling verticale. Tuttavia, solo il numero delle richieste è aumentato di 10 volte. Nel corso degli anni la funzionalità del sistema è aumentata e ora ci sono più tipologie di richieste. Grazie alla memorizzazione nella cache, la funzionalità esistente viene eseguita con meno richieste e richieste più efficienti. Ciò significa che puoi tranquillamente moltiplicare per altri 5 per ottenere il coefficiente di accelerazione reale. Quindi, secondo le stime più prudenti, possiamo dire che l'accelerazione è stata di 50 volte o più. Oscillare verticalmente un server costerebbe 50 volte di più. Soprattutto considerando che una volta effettuata l'ottimizzazione funziona sempre e la fattura per il server affittato arriva ogni mese.
Fonte: habr.com