Introduzione filosofica
Come è noto, esistono solo due metodi per risolvere i problemi:
- Il metodo dell'analisi o il metodo deduttivo, ovvero dal generale al particolare.
- Il metodo della sintesi o il metodo induttivo, ovvero dal particolare al generale.
Per affrontare il problema "migliorare le prestazioni del database", questo potrebbe apparire nel modo seguente.
Analisi — analizziamo il problema in parti separate e cercando di risolverle miglioriamo le prestazioni del database nel suo complesso.
Nella pratica, l'analisi appare all'incirca così:
- Sorge un problema (incidente di prestazioni)
- Raccogliamo informazioni statistiche sullo stato del database
- Cerchiamo i colli di bottiglia
- Affrontiamo i problemi legati ai colli di bottiglia
Collo di bottiglia del database — infrastruttura (CPU, Memoria, Dischi, Rete, OS), configurazioni (postgresql.conf), query:
Infrastruttura: le possibilità di impatto e modifica per l'ingegnere sono quasi nulle.
Configurazioni del database: le possibilità di modifica sono lievemente più elevate rispetto al caso precedente, ma di solito rimangono comunque piuttosto difficili, specialmente nei cloud.
Richieste al database: l'unica area per manovre.
Sintesi — miglioriamo le prestazioni di singoli componenti, aspettandoci che in risultato le prestazioni del database migliorino.
Introduzione lirica o perché è tutto ciò necessario
Come avviene il processo di risoluzione degli incidenti di prestazione se le prestazioni del database non vengono monitorate:
Cliente - “abbiamo problemi, è tutto lento, fateci bene”
Ingegnere - “cosa significa lento?”
Cliente – “ecco come è ora (un'ora fa, ieri, nell'ultima occasione), è lento”
Ingegnere – “quando andava bene?”
Cliente – “una settimana (due settimane) fa andava bene.” (È stato fortunato)
Cliente – “non ricordo quando andava bene, ma ora è male” (Risposta comune)
Ne risulta un quadro classico:
Chi è colpevole e cosa fare?
Alla prima parte della domanda è più facile rispondere — è sempre colpa dell'ingegnere DBA.
Alla seconda parte rispondere è anche piuttosto semplice — è necessario implementare un sistema di monitoraggio delle prestazioni del database.
Sorge la prima domanda — cosa monitorare?
Via 1. Monitoreremo TUTTO
Il carico della CPU, il numero di operazioni di lettura/scrittura su disco, la dimensione della memoria allocata e una miriade di altri contatori che qualsiasi sistema di monitoraggio minimamente funzionale può fornire.
Il risultato è una montagna di grafici, tabelle riassuntive e avvisi continui via email, con un ingegnere sempre impegnato a risolvere un gran numero di ticket identici, che di norma hanno formulazioni standard come "Problema temporaneo. Nessuna azione necessaria." Ma alla fine, tutti sono occupati e c'è sempre qualcosa da mostrare al cliente — il lavoro non si ferma.
Percorso 2. Monitorare solo ciò che è necessario e non monitorare ciò che non serve.
È possibile monitorare in modo diverso: solo entità ed eventi:
- Sui quali l'ingegnere DBA può influire.
- Per i quali esiste un algoritmo d'azione in caso di evento o modifica dell'entità.
Basandosi su questa assunzione e ricordando «Introduzione filosofica» per evitare ripetizioni regolari di «Introduzione lirica o perché è tutto ciò necessario» sarà opportuno monitorare le prestazioni di query specifiche, per ottimizzare e analizzare, ciò che alla fine dovrebbe portare a un miglioramento delle prestazioni dell'intero database.
Ma per migliorare una query pesante che influisce sulle prestazioni complessive del database, è prima necessario trovarla.
Quindi, sorgono due questioni correlate:
- quale query è considerata pesante
- come cercare le query pesanti.
Evidentemente, una query pesante è una query che utilizza molte risorse del sistema operativo per restituire un risultato.
Passiamo alla seconda questione: come cercare e monitorare le query pesanti?
Quali possibilità di monitoraggio delle query ci sono in PostgreSQL?
Rispetto a Oracle, le possibilità sono limitate, ma è comunque possibile fare qualcosa.
PG_STAT_STATEMENTS
Per cercare e monitorare le query pesanti in PostgreSQL, è progettata l'estensione standard pg_stat_statements.
Dopo aver installato l'estensione, appare una vista omonima nel database target, che deve essere utilizzata a scopo di monitoraggio.
Colonne target di pg_stat_statements per costruire un sistema di monitoraggio:
- queryid Codice hash interno calcolato dall'albero di analisi dell'operatore
- max_time Il tempo massimo speso per l'operatore, in millisecondi
Accumula utilizzando le statistiche su queste due colonne, è possibile costruire un sistema di monitoraggio.
Come utilizzare pg_stat_statements per monitorare le prestazioni di PostgreSQL
Per monitorare le prestazioni delle query si utilizza:
Dalla parte del database di destinazione — la vista pg_stat_statements
Dalla parte server e dal database di monitoraggio — un insieme di script bash e tabelle di servizio.
Fase 1 — raccolta dei dati statistici
Sul host di monitoraggio, viene eseguito regolarmente uno script tramite cron che copia il contenuto della vista pg_stat_statements dal database di destinazione nella tabella pg_stat_history nel database di monitoraggio.
In questo modo si crea una cronologia dell'esecuzione delle singole query, che può essere utilizzata per generare report sulle prestazioni e configurare le metriche.
Fase 2 — configurazione delle metriche di prestazione
Basandosi sui dati raccolti, scegliamo le query la cui esecuzione è più critica/importante per il cliente (applicazione). In accordo con il cliente, impostiamo i valori delle metriche di prestazione utilizzando i campi queryid e max_time.
Risultato — inizio del monitoraggio delle prestazioni
- Lo script di monitoraggio controlla le metriche di performance configurate durante l'avvio, confrontando il valore di max_time della metrica con quello della vista pg_stat_statements nel database di destinazione.
- Se il valore nel database di destinazione supera quello della metrica, viene generato un avviso (un incidente nel sistema di ticketing).
Funzionalità aggiuntiva 1
Storia dei piani di esecuzione delle query
È molto utile avere la storia delle modifiche ai piani di esecuzione delle query per risolvere incidenti di performance.
Per memorizzare la storia viene utilizzata una tabella di servizio log_query. La tabella viene popolata durante l'analisi del file di log PostgreSQL caricato. Poiché il file di log, a differenza della vista pg_stat_statements, contiene il testo completo con i valori dei parametri di esecuzione, non solo il testo normalizzato, è possibile registrare non solo il tempo e la durata delle query, ma anche conservare i piani di esecuzione al momento attuale.
Funzionalità aggiuntiva 2
Processo continuo di miglioramento delle performance
Il monitoraggio delle singole query non è generalmente destinato a risolvere il problema del miglioramento continuo delle prestazioni del database nel suo complesso, poiché controlla e affronta le problematiche di prestazioni solo per singole query. Tuttavia, è possibile estendere il metodo e configurare il monitoraggio delle query per tutti i database.
Per questo è necessario introdurre metriche di prestazione aggiuntive:
- Negli ultimi giorni
- Per il periodo di riferimento
Lo script seleziona le query dalla vista pg_stat_statements nel database di destinazione e confronta il valore max_time con il valore medio di max_time, nel primo caso per gli ultimi giorni o per il periodo di tempo selezionato (baseline), nel secondo caso.
In questo modo, in caso di degrado delle prestazioni per qualsiasi query, verrà automaticamente generata un'avvertenza, senza analisi manuale dei rapporti.
E cosa c'entra la sintesi?
Nell'approccio descritto, come suggerisce il metodo di sintesi, migliorando le singole parti del sistema, miglioriamo il sistema nel suo complesso.
- La query eseguita dal database – tesi
- La query modificata – antitesi
- Cambiamento dello stato del sistema – sintesi
Sviluppo del sistema
- Estensioni per la raccolta di statistiche aggiungendo la cronologia per la vista di sistema pg_stat_activity
- Estensione per la raccolta di statistiche aggiungendo la cronologia per le statistiche delle singole tabelle coinvolte nelle query
- Integrazione con il sistema di monitoraggio su AWS
- E ancora, si può pensare a qualcos'altro...
Fonte: habr.com
