Introduzione filosofica
Come è noto, esistono solo due metodi per risolvere i problemi:
- Metodo analitico o metodo deduttivo, o dal generale al particolare.
- Metodo di sintesi o metodo induttivo, o dal particolare al generale.
Per affrontare il problema "migliorare le prestazioni del database", questo potrebbe apparire nel seguente modo.
Analisi — analizziamo il problema in parti singole e risolvendole tentiamo di migliorare in definitiva le prestazioni del database nel complesso.
In pratica, l'analisi appare più o meno così:
- Sorge un problema (incidente di prestazione)
- Raccogliamo informazioni statistiche sullo stato del database
- Cerchiamo i colli di bottiglia
- Affrontiamo i problemi dei colli di bottiglia
Collo di bottiglia del database — infrastruttura (CPU, Memoria, Dischi, Rete, OS), impostazioni(postgresql.conf), query:
Infrastruttura: possibilità di influenza e modifica per l'ingegnere — quasi nulle.
Impostazioni del database: le possibilità di modifiche sono un po' maggiori rispetto al caso precedente, ma in generale sono comunque piuttosto difficoltose, soprattutto nei cloud.
Richieste al database: l'unica area di manovra.
Sintesi — miglioriamo le prestazioni delle singole parti, aspettandoci che in tal modo le prestazioni del database migliorino.
Introduzione lirica o perché tutto ciò è necessario
Come avviene il processo di risoluzione degli incidenti di prestazioni, se le prestazioni del database non sono monitorate:
Cliente - “siamo messi male, tutto è lento, fateci star bene”
Ing. - “male in che senso?”
Cliente – “ecco come ora (un'ora fa, ieri, nel caso precedente), lentamente”
Ing. – “e quando andava bene?”
Cliente – “una settimana (due settimane) fa andava decentemente.” (Era una questione di fortuna)
Cliente – “non ricordo quando andava bene, ma adesso va male” (Risposta comune)
Di conseguenza, risulta un quadro classico:
Chi è responsabile e cosa fare?
Alla prima parte della domanda è più facile rispondere — è sempre colpa dell'ingegnere DBA.
Alla seconda parte, la risposta non è troppo complicata - è necessario implementare un sistema di monitoraggio delle prestazioni del database.
Sorge la prima domanda — cosa monitorare?
Opzione 1. Monitoriamo TUTTO
Carico CPU, numero di operazioni di lettura/scrittura su disco, dimensione della memoria allocata e un'enormità di altri contatori che qualsiasi sistema di monitoraggio che funzioni decentemente può fornire.
Il risultato è una marea di grafici, tabelle riassuntive e avvisi continui via email, con un ingegnere completamente occupato a risolvere una serie di ticket identici, solitamente con la formulazione standard – “Problema temporaneo. Nessuna azione necessaria”. Tuttavia, tutti sono occupati e c'è sempre qualcosa da mostrare al cliente – il lavoro è attivo.
Percorso 2. Monitorare solo ciò che serve e non monitorare ciò che non serve.
Si può monitorare, in modo un po' diverso, solo entità ed eventi:
- A cui l'ingegnere DBA può influire.
- Per i quali esiste un algoritmo di azione all'insorgere di un evento o di una modifica dell'entità.
Partendo da questa premessa e ricordando “Introduzione filosofica” al fine di evitare ripetizioni regolari di “Introduzione lirica o perché tutto ciò è necessario”, sarà opportuno monitorare le performance di singole query, per ottimizzare e analizzare, il che alla fine dovrebbe portare a un miglioramento delle prestazioni complessive dell'intero database.
Ma per migliorare una query pesante che influisce sulle prestazioni generali del database, è prima necessario trovarla.
Quindi, sorgono due domande interconnesse:
- quale query è considerata pesante?
- come cercare le query pesanti.
Ovviamente, una query pesante è una query che utilizza molte risorse del sistema operativo per restituire un risultato.
Passiamo alla seconda domanda – come cercare e poi monitorare le query pesanti?
Quali possibilità di monitoraggio delle query ci sono in PostgreSQL?
Rispetto a Oracle, le possibilità sono poche, ma qualcosa si può comunque fare.
PG_STAT_STATEMENTS
Per la ricerca e il monitoraggio delle query pesanti in PostgreSQL è stato creato un'estensione standard chiamata pg_stat_statements.
Dopo l'installazione dell'estensione, nella base di dati di destinazione appare una vista omonima, che deve essere utilizzata per scopi di monitoraggio.
Colonne target di pg_stat_statements per la costruzione di un sistema di monitoraggio:
- queryid Hash code interno calcolato sull'albero di analisi dell'operatore
- max_time Tempo massimo speso per l'operatore, in millisecondi
Accumulated and using statistics from these two columns, a monitoring system can be built.
Come viene utilizzato pg_stat_statements per monitorare le prestazioni di PostgreSQL.
Per il monitoraggio delle prestazioni delle query si utilizza:
Dalla parte del database di destinazione — la vista pg_stat_statements
Da parte di server e del database di monitoraggio — un insieme di script bash e tabelle di servizio.
Fase 1 — raccolta dei dati statistici
Sull'host di monitoraggio uno script viene eseguito regolarmente tramite cron che copia il contenuto della vista pg_stat_statements dal database di destinazione alla tabella pg_stat_history nel database di monitoraggio.
In questo modo si crea una cronologia delle esecuzioni delle singole query, che può essere utilizzata per generare report sulle prestazioni e impostare le metriche.
Fase 2 — impostazione delle metriche di prestazione
Basandoci sui dati raccolti, selezioniamo 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 — avvio del monitoraggio delle prestazioni
- Lo script di monitoraggio, all'avvio, verifica le metriche di prestazione configurate, confrontando il valore max_time della metrica con il valore dalla vista pg_stat_statements nel database di destinazione.
- Se il valore nel database di destinazione supera il valore della metrica, viene generato un avviso (incidente nel sistema di ticketing)
Opzione aggiuntiva 1
Cronologia dei piani di esecuzione delle query
Per la risoluzione di incidenti di prestazioni è molto utile avere la cronologia delle modifiche ai piani di esecuzione delle query.
Per memorizzare la cronologia si utilizza la tabella di servizio log_query. La tabella viene riempita durante l'analisi del file di log caricato di PostgreSQL. Poiché nel file di log, a differenza della vista pg_stat_statements, entra il testo completo con i valori dei parametri di esecuzione, e non il testo normalizzato, è possibile registrare non solo il tempo e la durata delle query, ma anche memorizzare i piani di esecuzione nel momento attuale.
Opzione aggiuntiva 2
Processo continuo di miglioramento delle prestazioni
Il monitoraggio delle singole query, in generale, non è destinato a risolvere il problema del miglioramento continuo delle prestazioni del database nel suo insieme poiché controlla e risolve i problemi di prestazioni solo per singole query. Tuttavia, è possibile estendere il metodo e configurare il monitoraggio delle query per tutti i database.
Per questo è necessario inserire metriche prestazionali aggiuntive:
- Negli ultimi giorni
- Nel periodo di riferimento
Lo script seleziona le query dalla vista pg_stat_statements nel database di destinazione e confronta il valore max_time con la media di max_time, nel primo caso negli ultimi giorni o nel periodo di tempo selezionato (baseline), nel secondo caso.
In questo modo, in caso di degrado delle prestazioni per qualsiasi query, l'avviso verrà generato automaticamente, senza analisi manuale dei rapporti.
E a cosa serve la sintesi?
Nel approccio descritto, come suggerito dal metodo di sintesi — migliorando le singole parti del sistema, miglioriamo nel complesso il sistema.
- La query eseguita dal database – tesi
- La query modificata – antitesi
- Modifica dello stato del sistema — sintesi
Sviluppo del sistema
- Espansione delle statistiche raccolte aggiungendo la cronologia per la vista sistemica pg_stat_activity
- Espansione delle statistiche raccolte aggiungendo la cronologia per le statistiche delle singole tabelle coinvolte nelle query
- Integrazione con il sistema di monitoraggio nel cloud AWS
- E inoltre, si può inventare qualcos'altro...
Fonte: habr.com
