Come crescere dieci volte il numero di richieste al database senza passare a un server più potente, mantenendo al contempo la funzionalità del sistema? Vi racconterò come abbiamo affrontato il calo delle prestazioni del nostro database, come abbiamo ottimizzato le query SQL per servire il maggior numero possibile di utenti senza aumentare i costi delle risorse di calcolo.
Sto sviluppando un servizio per la gestione dei processi aziendali nelle imprese di costruzione. Operiamo con circa 3.000 aziende. Oltre 10.000 persone utilizzano il nostro sistema ogni giorno, per 4-10 ore. Esso risolve varie attività di pianificazione, notifiche, avvisi, convalide... Utilizziamo PostgreSQL 9.6. Nel nostro database abbiamo circa 300 tabelle e ogni giorno riceve fino a 200 milioni di richieste (10.000 diverse). In media, abbiamo 3.000-4.000 richieste al secondo, nei momenti di maggior attività oltre 10.000 richieste al secondo. La maggior parte delle richieste sono OLAP. Aggiunte, modifiche e cancellazioni sono molto meno comuni, quindi il carico OLTP è relativamente ridotto. Ho fornito tutte queste cifre affinché possiate valutare la portata del nostro progetto e capire quanto la nostra esperienza possa essere utile per voi.
Primo quadro. Lirico
Quando abbiamo iniziato lo sviluppo, non ci siamo particolarmente preoccupati del carico che la base dati avrebbe dovuto sostenere e di cosa avremmo fatto se il server non avesse più retto. Durante la progettazione della base dati, abbiamo seguito raccomandazioni generali e cercato di non spararci sui piedi, ma oltre ai consigli generali come "non utilizzare il pattern , non siamo andati. Abbiamo progettato sulla base dei principi di normalizzazione, evitando la ridondanza dei dati e non ci siamo preoccupati di velocizzare determinate query. Non appena sono arrivati i primi utenti, ci siamo trovati di fronte a problemi di prestazioni. Come al solito, ci siamo trovati completamente impreparati. I primi problemi sono stati semplici. Di norma, tutto è stato risolto con l'aggiunta di un nuovo indice. Ma è arrivato un momento in cui le semplici soluzioni hanno smesso di funzionare. Rendendoci conto che non avevamo esperienza e che era sempre più difficile capire quale fosse la causa dei problemi, abbiamo assunto specialisti che ci hanno aiutato a configurare correttamente il server, collegare il monitoraggio, e ci hanno mostrato dove guardare per ottenere .
Secondo quadro. Statistico
Abbiamo circa 10.000 diverse query che vengono eseguite nel nostro database ogni giorno. Di queste 10.000, ci sono mostri che vengono eseguiti 2-3 milioni di volte con tempi di esecuzione medi di 0,1-0,3 ms, e ci sono query con tempi di esecuzione medi di 30 secondi, che vengono chiamate 100 volte al giorno.
Ottimizzare tutte le 10.000 query non era possibile, quindi abbiamo deciso di capire dove concentrare gli sforzi per migliorare correttamente le performance del database. Dopo diverse iterazioni, abbiamo iniziato a suddividere le query in categorie.
QUERY TOP
Queste sono le query più pesanti, che richiedono più tempo (tempo totale). Sono query che vengono chiamate molto frequentemente o query che impiegano molto tempo per essere eseguite (quelle lunghe e frequenti erano state ottimizzate già nelle prime iterazioni per migliorare la velocità). Alla fine, il server spende complessivamente più tempo per eseguirle. È importante distinguere le query top per il tempo di esecuzione totale e separatamente per il tempo IO. I metodi di ottimizzazione per queste query sono leggermente diversi.
È pratica comune per tutte le aziende lavorare con le parole chiave TOP. Ce ne sono poche, e l'ottimizzazione anche di una sola parola chiave può liberare il 5-10% delle risorse. Tuttavia, man mano che il progetto 'cresce', l'ottimizzazione delle parole chiave TOP diventa sempre più una sfida non banale. Tutti i metodi semplici sono già stati esauriti, e persino la parola chiave più 'pesante' consuma 'solo' il 3-5% delle risorse. Se le parole chiave TOP occupano complessivamente meno del 30-40% del tempo, è probabile che tu abbia già fatto uno sforzo affinché funzionassero rapidamente, e sia giunto il momento di passare all'ottimizzazione delle parole chiave del gruppo successivo.
Rimane da rispondere alla domanda su quante parole chiave nella parte alta includere in questo gruppo. Di solito ne prendo almeno 10, ma non più di 20. Cerco di fare in modo che il tempo di esecuzione del primo e dell'ultimo nella TOP group non differisca di più di dieci volte. Cioè, se il tempo di esecuzione delle query diminuisce nettamente dal primo al decimo posto, prendo il TOP-10; se la diminuzione è più graduale, aumento il numero del gruppo a 15 o 20.
Parole chiave medie (medium)
Queste sono tutte le query che seguono immediatamente TOP, escluse le ultime 5-10%. Di solito, l'ottimizzazione di queste query offre l'opportunità di migliorare significativamente le prestazioni del server. Queste query possono costituire fino all'80%. Ma anche se la loro percentuale supera il 50%, è il momento di osservarle più da vicino.
Coda (tail)
Come detto, queste query si trovano alla fine e richiedono il 5-10% del tempo. Possono essere ignorate, a meno che non si utilizzino strumenti analitici automatici; in tal caso, la loro ottimizzazione può essere comunque a basso costo.
Come valutare ogni gruppo?
Utilizzo una query SQL che aiuta a fare tale valutazione per PostgreSQL (sono sicuro che si possa scrivere una query simile per molti altri DBMS)
Query SQL per valutare 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 AND 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 consiste in tre colonne, ciascuna delle quali contiene la percentuale di tempo impiegato per elaborare le richieste di questo gruppo. All'interno della query ci sono due numeri (nel mio caso sono 20 e 800) che separano le richieste di un gruppo dall'altra.
Ecco come si rapportano le quote delle richieste al momento dell'inizio dei lavori di ottimizzazione e adesso.
Dal grafico si evince che la quota delle richieste TOP è diminuita drasticamente, mentre sono aumentate le "richieste medie".
Inizialmente, le richieste TOP includevano evidenti errori. Col tempo, i difetti iniziali sono scomparsi, la quota delle richieste TOP si è ridotta, e abbiamo dovuto fare sempre più sforzi per velocizzare le richieste pesanti.
Per ottenere il testo delle richieste, utilizziamo questa query
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 AND rn 800 -- TAIL
Ecco l'elenco delle tecniche più comunemente utilizzate che ci hanno aiutato a velocizzare le richieste TOP:
- Ridefinire il sistema, ad esempio ripensare la logica delle notifiche su un message broker invece di fare richieste periodiche al database.
- Aggiunta o modifica degli indici
- Riscrivere le query ORM in SQL puro
- Riscrittura della logica di caricamento lazy dei dati
- Caching tramite denormalizzazione dei dati. Ad esempio, abbiamo una relazione tra le tabelle Spedizione -> Fattura -> Richiesta -> Domanda. Ogni spedizione è collegata alla domanda attraverso altre tabelle. Per evitare di collegare tutte le tabelle in ogni richiesta, abbiamo duplicato il riferimento alla domanda nella tabella Spedizione.
- Caching delle tabelle statiche con riferimenti e di quelle che cambiano raramente nella memoria del programma.
A volte le modifiche iniziavano a richiedere un notevole redesign, ma offrivano solo un aumento del 5-10% nel carico del sistema e risultavano giustificate. Col tempo, i ritorni divenivano sempre minori e si richiedeva un redesign sempre più serio.
In quel momento abbiamo prestato attenzione al secondo gruppo di richieste, il gruppo degli intermedi. Ci sono molte più richieste in questo gruppo e sembrava che l'analisi di tutto il gruppo richiedesse molto tempo. Tuttavia, la maggior parte delle richieste si è rivelata molto semplice da ottimizzare e molti problemi si ripetevano decine di volte in diverse variazioni. Ecco alcuni esempi di ottimizzazioni tipiche che abbiamo applicato a decine di richieste simili e ogni gruppo di richieste ottimizzate ha ridotto il carico del DB del 3-5%.
- Invece di controllare la presenza di record con COUNT e scansionare completamente la tabella, abbiamo iniziato a utilizzare EXISTS
- Ci siamo liberati di DISTINCT (non esiste una soluzione generale, ma a volte è possibile eliminarlo facilmente accelerando la query di 10-100 volte).
Ad esempio, invece di una query per selezionare tutti i conducenti 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.IDabbiamo effettuato la query su una tabella PERSON relativamente piccola
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 avessimo utilizzato una sottoselezione correlata, ma offre un'accelerazione di oltre 10 volte.
- In molti casi abbiamo completamente abbandonato COUNT e
- invece di
UPPER(s) LIKE JOHN%utilizziamo
s ILIKE "John%"
Siamo riusciti ad accelerare ogni singola query da 3 a 1000 volte. Nonostante le performance impressionanti, inizialmente pensavamo che non avesse senso ottimizzare una query che impiega 10 ms, che rientra tra le 300 query più complesse e che occupa solo una frazione percentuale del tempo di carico del database. Applicando la stessa strategia a un gruppo di query simili, siamo riusciti a migliorare il rendimento di alcuni punti percentuali. Per evitare di perdere tempo a controllare manualmente centinaia di query, abbiamo creato alcuni semplici script che, utilizzando espressioni regolari, trovano query simili. Alla fine, la ricerca automatica di gruppi di query ci ha permesso di migliorare ulteriormente le nostre performance con sforzi modesti.
Di fatto, siamo già tre anni che utilizziamo la stessa infrastruttura. Il carico medio giornaliero è di circa il 30%, con punte che arrivano fino al 70%. Il numero di richieste e utenti è aumentato di circa 10 volte. E tutto questo grazie al monitoraggio costante dei gruppi di richieste TOP-MEDIUM. Non appena compare una nuova richiesta nel gruppo TOP, la analizziamo immediatamente e cerchiamo di migliorarla. Ogni settimana esaminiamo il gruppo MEDIUM tramite script di analisi delle richieste. Se troviamo nuove richieste che conosciamo già come ottimizzare, le cambiamo rapidamente. A volte scopriamo nuovi modi di ottimizzazione che possono essere applicati subito a più richieste.
Secondo le nostre previsioni, il server attuale può gestire un ulteriore incremento di utenti da 3 a 5 volte. Tuttavia, abbiamo ancora un asso nella manica: non abbiamo ancora trasferito le query SELECT sul mirror, come si raccomanda di fare. Ma lo facciamo intenzionalmente, poiché vogliamo prima sfruttare al massimo le opportunità di ottimizzazione 'intelligente' prima di attivare la 'pesante artiglieria'.
Una valutazione critica del lavoro svolto può suggerire di adottare la scalabilità verticale. Acquistare un server più potente, anziché far spendere tempo agli specialisti. Un server può non costare molto, soprattutto considerando che i limiti della scalabilità verticale non sono stati ancora esauriti. Tuttavia, il numero di richieste è aumentato di dieci volte. Nel corso degli anni, la funzionalità del sistema è cresciuta e ora ci sono più varianti di richieste. La funzionalità precedente viene ora eseguita con un numero inferiore di richieste grazie alla memorizzazione nella cache, ed è anche più efficiente. Questo significa che possiamo moltiplicare per 5 per ottenere un reale coefficiente di accelerazione. Pertanto, con le stime più conservative, si può affermare che l'accelerazione è stata di 50 volte o più. Scendere a scalare verticalmente il server di 50 volte costerebbe di più. Soprattutto considerando che un'ottimizzazione una volta effettuata continua a funzionare nel tempo, mentre la bolletta per il server affittato arriva ogni mese.
Fonte: habr.com
