Ottimizzazione delle query del database nell'esempio di un servizio B2B per costruttori

Come far crescere le richieste al database dieci volte senza passare a un server più potente e mantenendo 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 le spese per le risorse di calcolo.

Faccio un servizio per la gestione dei processi aziendali nelle aziende di costruzione. Collaboriamo con circa 3.000 aziende. Oltre 10.000 persone lavorano ogni giorno con il nostro sistema per 4-10 ore. Risolve vari compiti di pianificazione, avviso, allerta, convalida... Utilizziamo PostgreSQL 9.6. Nel nostro database ci sono circa 300 tabelle e ogni giorno riceve fino a 200 milioni di richieste (10.000 diverse). In media abbiamo 3-4.000 richieste al secondo, nei momenti più attivi oltre 10.000 richieste al secondo. La maggior parte delle richieste è OLAP. Aggiunte, modifiche e cancellazioni sono di gran lunga inferiori, cioè il carico OLTP è relativamente ridotto. Ho fornito tutti questi numeri per darvi un'idea della portata del nostro progetto e per capire quanto la nostra esperienza possa esservi utile.

Immagine prima. Lirica

Quando abbiamo iniziato lo sviluppo, non ci siamo particolarmente preoccupati del carico che avrebbe sopportato il database e di cosa avremmo fatto se il server non ce l'avesse fatta. Nella progettazione del database siamo stati guidati da raccomandazioni generali e abbiamo cercato di non spararci nelle piedi, ma oltre ai consigli generali come 'non usare il pattern Entity Attribute Values non siamo andati. Abbiamo progettato basandoci sui principi di normalizzazione evitando la ridondanza dei dati e non ci siamo preoccupati di accelerare determinate richieste. Non appena sono arrivati i primi utenti, ci siamo trovati di fronte a problemi di prestazioni. Come al solito, eravamo assolutamente impreparati. I primi problemi si sono rivelati semplici. In genere, tutto si risolveva aggiungendo un nuovo indice. Ma è arrivato un momento in cui le semplici soluzioni hanno smesso di funzionare. Rendendoci conto che ci mancava esperienza e che era sempre più difficile capire qual era la causa dei problemi, abbiamo assunto specialisti che ci hanno aiutato a configurare correttamente il server, a collegare il monitoraggio, ci hanno mostrato dove guardare per ottenere statistiche.

Immagine seconda. Statistica

Abbiamo quindi circa 10.000 diverse query che vengono eseguite sul nostro DB in un giorno. Di queste 10.000 ci sono dei mostri che vengono eseguiti 2-3 milioni di volte con un tempo medio di esecuzione di 0,1-0,3 ms e ci sono query con un tempo medio di esecuzione di 30 secondi, invocate 100 volte al giorno.

Ottimizzare tutte e 10.000 le query non si è rivelato possibile, quindi abbiamo deciso di capire dove concentrare gli sforzi per migliorare correttamente le prestazioni del DB. Dopo alcune 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 invocate molto frequentemente o query che richiedono molto tempo per essere completate (le query lunghe e frequenti sono state ottimizzate nelle prime iterazioni nella corsa per la velocità). Di conseguenza, il server spende più tempo sulla loro esecuzione. È importante separare le query top in base al tempo di esecuzione totale e separatamente in base al tempo di IO. I metodi di ottimizzazione per queste query sono leggermente diversi.

La pratica comune di tutte le aziende è lavorare con le QUERY TOP. Ce ne sono poche, l'ottimizzazione anche di una sola query può liberare il 5-10% delle risorse. Tuttavia, man mano che il progetto “cresce”, l'ottimizzazione delle QUERY TOP diventa un compito sempre più non banale. Tutti i metodi semplici sono già stati sfruttati, e la query “più pesante” consuma “solo” il 3-5% delle risorse. Se le QUERY TOP occupano complessivamente meno del 30-40% del tempo, è probabile che abbiate già fatto uno sforzo per farle funzionare rapidamente e sia giunto il momento di passare all'ottimizzazione delle query del gruppo successivo.
Rimane da rispondere alla domanda su quante query superiori includere in questo gruppo. Di solito prendo non meno di 10, ma non più di 20. Cerco di fare in modo che il tempo della prima e dell'ultima query nel gruppo TOP non differisca più di 10 volte. Quindi, se il tempo di esecuzione delle query scende bruscamente dal 1° posto al 10°, prendo il TOP-10; se il calo è più graduale, aumento le dimensioni del gruppo a 15 o 20.

MEDIO

Queste sono tutte le query che seguono immediatamente le QUERY TOP, escluse le ultime 5-10%. Di solito, nell'ottimizzazione di queste query si trova la possibilità di aumentare notevolmente le prestazioni del server. Queste query possono rappresentare fino all'80%. Ma anche se la loro quota supera il 50%, è tempo di esaminarle con maggiore attenzione.

CODA

Come detto, queste richieste arrivano alla fine e richiedono il 5-10% del tempo. Si possono dimenticare, a meno che non si utilizzino strumenti automatici per l'analisi delle richieste; in tal caso, l'ottimizzazione potrebbe anche risultare poco costosa.

Come valutare ogni gruppo?

Uso una query SQL che aiuta a fare tale valutazione per PostgreSQL (sono sicuro che per molti altri DBMS si possa scrivere una query simile)

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) 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, ognuna delle quali contiene la percentuale di tempo spesa per trattare le richieste di questo gruppo. All'interno della query ci sono due numeri (nel mio caso 20 e 800), che separano le richieste di un gruppo da un altro.

Ecco come si rapportano le quote delle richieste all'inizio dei lavori di ottimizzazione e ora.

Dal diagramma è evidente che la quota delle richieste TOP è drasticamente diminuita, mentre sono aumentate quelle "medie".
All'inizio le richieste TOP includevano evidenti errori. Col tempo, i problemi iniziali sono scomparsi, la quota delle richieste TOP si è ridotta e abbiamo dovuto impegnarci sempre di più per velocizzare le richieste pesanti.

Per ottenere il testo delle richieste utilizziamo la seguente 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 un elenco delle tecniche più comunemente utilizzate che ci hanno aiutato a velocizzare le richieste TOP:

• Ridefinizione del sistema, ad esempio, rifacendo la logica delle notifiche su un message broker invece di effettuare richieste periodiche al DB
• Aggiunta o modifica degli indici
• Riscrittura delle query ORM in SQL puro
• Riscrittura della logica di lazy loading dei dati
• Caching tramite denormalizzazione dei dati. Ad esempio, abbiamo una relazione tra le tabelle Consegna -> Fattura -> Richiesta -> Domanda. Cioè, ogni consegna è collegata a una domanda attraverso altre tabelle. Per non dover collegare tutte le tabelle in ogni richiesta, abbiamo duplicato il riferimento alla domanda nella tabella Consegna.
• La memorizzazione delle tabelle statiche con le directory e delle tabelle che cambiano raramente nella memoria del programma.

A volte le modifiche richiedevano un ridisegno sostanziale, ma fornivano un alleggerimento del 5-10% del sistema, ed erano giustificate. Col passare del tempo, il rendimento diventava sempre minore e il ridisegno necessario era sempre più serio.

Allora abbiamo prestato attenzione al secondo gruppo di interrogazioni - il gruppo degli intermedi. In esso c'erano molte più interrogazioni e sembrava che l'analisi di tutto il gruppo richiedesse molto tempo. Tuttavia, la maggior parte delle interrogazioni si è rivelata molto semplice da ottimizzare e molti problemi si ripetevano decine di volte in varie variazioni. Ecco alcuni esempi di ottimizzazioni tipiche che abbiamo applicato a decine di interrogazioni simili, ognuna delle quali alleggeriva il database del 3-5%.

• Invece di controllare la presenza di record utilizzando COUNT e una scansione completa della tabella, abbiamo iniziato a utilizzare EXISTS.
• Abbiamo eliminato DISTINCT (non c'è una ricetta generale, ma a volte si può facilmente eliminarlo aumentando la velocità della query di 10-100 volte).

  Ad esempio, invece di fare una query per ottenere tutti i conducenti da una grande tabella delle consegne (DELIVERY)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  abbiamo 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 avessimo utilizzato una sottoselezione correlata, ma essa offre un'accelerazione di oltre 10 volte.

• In molti casi abbiamo persino rinunciato a COUNT e
  sostituito con il calcolo di un valore approssimato.
• anziché

  UPPER(s) LIKE JOHN% 
  

  usiamo

  s ILIKE “John%”
  

Ogni singola query è stata talvolta velocizzata da 3 a 1000 volte. Nonostante i risultati impressionanti, inizialmente ci sembrava che non ci fosse senso nell'ottimizzare una query che viene eseguita in 10 ms, rientra nella terza centinaia delle query più pesanti e occupa nel tempo di carico sul database frazioni di percentuale. Ma applicando lo stesso rimedio a un gruppo di query analoghe, recuperavamo diversi punti percentuali. Per non perdere tempo a esaminare manualmente tutte le centinaia di query, abbiamo scritto alcuni semplici script che trovavano query simili utilizzando espressioni regolari. Alla fine, la ricerca automatica di gruppi di query ci ha permesso di migliorare ulteriormente le nostre prestazioni investendo sforzi modestissimi.

Alla fine, abbiamo già lavorato per tre anni con lo stesso hardware. Il carico medio giornaliero è di circa il 30%, con punte che arrivano fino al 70%. Il numero di richieste e il numero di utenti sono aumentati di circa 10 volte. E tutto ciò grazie al monitoraggio costante di questi gruppi di richieste TOP-MEDIUM. Non appena appare una nuova richiesta nel gruppo TOP, la analizziamo immediatamente e cerchiamo di velocizzarla. Il gruppo MEDIUM lo esaminiamo settimanalmente tramite script di analisi delle richieste. Se troviamo nuove richieste che già sappiamo ottimizzare, le modifichiamo rapidamente. A volte troviamo nuovi modi di ottimizzazione che possono essere applicati subito a più richieste.

Secondo le nostre previsioni, il server attuale reggerà un aumento del numero di utenti di ulteriori 3-5 volte. Tuttavia, abbiamo un ulteriore asso nella manica: non abbiamo ancora trasferito le richieste SELECT sul mirror, come è consigliato fare. Ma non lo facciamo intenzionalmente, poiché vogliamo prima sfruttare fino in fondo le possibilità di ottimizzazione "intelligente" prima di attivare l' "artiglieria pesante".
Uno sguardo critico al lavoro svolto potrebbe suggerire di utilizzare la scalabilità verticale. Comprare un server più potente, invece di far perdere tempo agli specialisti. Un server potrebbe non costare tanto, soprattutto considerando che i limiti della scalabilità verticale non sono ancora stati esauriti. Tuttavia, il numero di richieste è aumentato solo di 10 volte. Negli ultimi anni, le funzionalità del sistema sono aumentate e ora ci sono più varianti di richieste. Le funzionalità precedenti, grazie alla memorizzazione nella cache, vengono eseguite con un minor numero di richieste, inoltre, richieste più efficaci. Quindi si può moltiplicare tranquillamente per 5, per ottenere il vero coefficiente di accelerazione. Pertanto, dai calcoli più modesti, si può dire che l'accelerazione è stata di 50 volte o più. Scalare verticalmente il server di 50 volte costerebbe di più. Soprattutto considerando che un'ottimizzazione una volta eseguita funziona continuamente, mentre la bolletta del server noleggiato arriva ogni mese.

Fonte: habr.com