Nozioni di base sul monitoraggio PostgreSQL. Alexey Lesovsky

Ti suggerisco di leggere la trascrizione del rapporto di Alexey Lesovsky da Data Egret “Fundamentals of PostgreSQL Monitoring”

In questo rapporto, Alexey Lesovsky parlerà dei punti chiave delle statistiche post-gress, cosa significano e perché dovrebbero essere presenti nel monitoraggio; su quali grafici dovrebbero essere presenti nel monitoraggio, come aggiungerli e come interpretarli. Il rapporto sarà utile agli amministratori di database, agli amministratori di sistema e agli sviluppatori interessati alla risoluzione dei problemi di Postgres.

Mi chiamo Alexey Lesovsky, rappresento la società Data Egret.

Qualche parola su di me. Ho iniziato molto tempo fa come amministratore di sistema.

Ho amministrato tutti i tipi di sistemi Linux diversi, ho lavorato su varie cose relative a Linux, ad esempio virtualizzazione, monitoraggio, ho lavorato con proxy, ecc. Ma ad un certo punto ho iniziato a lavorare di più con i database, PostgreSQL. Mi è davvero piaciuto. E ad un certo punto ho iniziato a lavorare su PostgreSQL per gran parte del mio tempo lavorativo. E così gradualmente sono diventato un DBA PostgreSQL.

E nel corso della mia carriera mi sono sempre interessato ai temi della statistica, del monitoraggio e della telemetria. E quando ero amministratore di sistema, ho lavorato a stretto contatto con Zabbix. E ho scritto una piccola serie di script come estensioni-zabbix. Era piuttosto popolare ai suoi tempi. E lì era possibile monitorare cose importanti molto diverse, non solo Linux, ma anche vari componenti.

Ora sto lavorando su PostgreSQL. Sto già scrivendo un'altra cosa che ti permette di lavorare con le statistiche PostgreSQL. È chiamato pgCenter (articolo su Habré - Statistiche post-gress senza nervi e tensioni).

Una piccola nota introduttiva. Quali situazioni hanno i nostri clienti, i nostri clienti? C'è qualche tipo di incidente legato al database. E quando il database è già stato ripristinato, arriva il capo del dipartimento o il responsabile dello sviluppo e dice: "Amici, dobbiamo monitorare il database, perché è successo qualcosa di brutto e dobbiamo evitare che ciò accada in futuro". E qui inizia l'interessante processo di scelta di un sistema di monitoraggio o di adattamento di un sistema di monitoraggio esistente in modo da poter monitorare il tuo database: PostgreSQL, MySQL o altri. E i colleghi iniziano a suggerire: “Ho sentito che esiste questo o quel database. Usiamolo." I colleghi iniziano a litigare tra loro. E alla fine si scopre che selezioniamo una sorta di database, ma il monitoraggio PostgreSQL è presentato piuttosto male e dobbiamo sempre aggiungere qualcosa. Prendi alcuni repository da GitHub, clonali, adatta gli script e personalizzali in qualche modo. E alla fine finisce per essere una sorta di lavoro manuale.

Pertanto, in questo discorso cercherò di darti alcune conoscenze su come scegliere il monitoraggio non solo per PostgreSQL, ma anche per il database. E darti le conoscenze che ti permetteranno di completare il tuo monitoraggio per trarne qualche beneficio, affinché tu possa monitorare con beneficio il tuo database, in modo da prevenire tempestivamente eventuali imminenti situazioni di emergenza che potrebbero verificarsi.

E le idee contenute in questo rapporto possono essere adattate direttamente a qualsiasi database, sia esso un DBMS o noSQL. Pertanto, non esiste solo PostgreSQL, ma ci saranno molte ricette su come farlo in PostgreSQL. Ci saranno esempi di query, esempi di entità che PostgreSQL ha per il monitoraggio. E se il tuo DBMS ha le stesse cose che ti permettono di metterle in monitoraggio, puoi anche adattarle, aggiungerle e andrà bene.

Non sarò nel rapporto
parlare di come fornire e archiviare le metriche. Non dirò nulla sulla post-elaborazione dei dati e sulla loro presentazione all'utente. E non dirò nulla sugli avvisi.
Ma man mano che la storia procede, mostrerò diversi screenshot del monitoraggio esistente e in qualche modo li criticherò. Cercherò comunque di non nominare i marchi per non creare pubblicità o anti-pubblicità per questi prodotti. Pertanto, tutte le coincidenze sono casuali e sono lasciate alla tua immaginazione.

Per prima cosa, scopriamo cos'è il monitoraggio. Il monitoraggio è una cosa molto importante da avere. Tutti lo capiscono. Ma allo stesso tempo, il monitoraggio non riguarda un prodotto aziendale e non influisce direttamente sul profitto dell’azienda, quindi il tempo viene sempre assegnato al monitoraggio su base residua. Se abbiamo tempo, allora facciamo il monitoraggio; se non abbiamo tempo, allora ok, lo metteremo in arretrato e un giorno torneremo a questi compiti.

Pertanto, dalla nostra pratica, quando arriviamo ai clienti, il monitoraggio è spesso incompleto e non presenta elementi interessanti che potrebbero aiutarci a fare un lavoro migliore con il database. E quindi il monitoraggio va sempre portato a termine.

I database sono cose così complesse che devono essere monitorate, perché sono un deposito di informazioni. E le informazioni sono molto importanti per l'azienda, non possono essere perse in nessun modo. Ma allo stesso tempo, i database sono software molto complessi. Sono costituiti da un gran numero di componenti. E molti di questi componenti devono essere monitorati.

Se parliamo specificamente di PostgreSQL, allora può essere rappresentato sotto forma di uno schema composto da un gran numero di componenti. Questi componenti interagiscono tra loro. E allo stesso tempo, PostgreSQL ha il cosiddetto sottosistema Stats Collector, che consente di raccogliere statistiche sul funzionamento di questi sottosistemi e fornire una sorta di interfaccia all'amministratore o all'utente in modo che possa visualizzare queste statistiche.

Queste statistiche sono presentate sotto forma di un determinato insieme di funzioni e visualizzazioni. Possono anche essere chiamati tabelle. Cioè, utilizzando un normale client psql, puoi connetterti al database, effettuare una selezione su queste funzioni e visualizzazioni e ottenere alcuni numeri specifici sul funzionamento dei sottosistemi PostgreSQL.

Puoi aggiungere questi numeri al tuo sistema di monitoraggio preferito, disegnare grafici, aggiungere funzioni e ottenere analisi a lungo termine.

Ma in questo resoconto non tratterò tutte queste funzioni in modo completo, perché potrebbe richiedere l’intera giornata. Affronterò letteralmente due, tre o quattro cose e vi dirò come aiutano a migliorare il monitoraggio.

E se parliamo di monitoraggio dei database, cosa è necessario monitorare? Innanzitutto dobbiamo monitorare la disponibilità, perché il database è un servizio che fornisce l'accesso ai dati ai clienti e dobbiamo monitorare la disponibilità, oltre a fornire alcune delle sue caratteristiche qualitative e quantitative.

Dobbiamo anche monitorare i client che si connettono al nostro database, perché possono essere sia client normali che client dannosi che possono danneggiare il database. Hanno anche bisogno di essere monitorati e le loro attività tracciate.

Quando i client si connettono al database, è ovvio che iniziano a lavorare con i nostri dati, quindi dobbiamo monitorare come i client lavorano con i dati: con quali tabelle e, in misura minore, con quali indici. Dobbiamo cioè valutare il carico di lavoro creato dai nostri clienti.

Ma il carico di lavoro è, ovviamente, costituito anche da richieste. Le applicazioni si connettono al database, accedono ai dati utilizzando query, quindi è importante valutare quali query abbiamo nel database, monitorare la loro adeguatezza, che non siano scritte in modo storto, che alcune opzioni debbano essere riscritte e fatte in modo che funzionino più velocemente e con prestazioni migliori.

E poiché stiamo parlando di un database, il database è sempre un processo in background. I processi in background aiutano a mantenere le prestazioni del database a un buon livello, quindi richiedono una certa quantità di risorse per funzionare. Allo stesso tempo, possono sovrapporsi alle risorse delle richieste del client, quindi i processi in background avidi possono influenzare direttamente le prestazioni delle richieste del client. Pertanto, devono essere monitorati e monitorati in modo che non vi siano distorsioni in termini di processi in background.

E tutto questo in termini di monitoraggio del database rimane nella metrica del sistema. Ma considerando che la maggior parte della nostra infrastruttura si sta spostando sul cloud, i parametri di sistema di un singolo host passano sempre in secondo piano. Ma nei database sono ancora rilevanti e, ovviamente, è necessario anche monitorare le metriche del sistema.

Con le metriche di sistema va più o meno tutto bene, tutti i moderni sistemi di monitoraggio supportano già queste metriche, ma in generale alcuni componenti non sono ancora sufficienti e alcune cose devono essere aggiunte. Li toccherò anche, ci saranno diverse diapositive a riguardo.

Il primo punto del piano è l’accessibilità. Cos'è l'accessibilità? La disponibilità, a mio avviso, è la capacità della base di servire le connessioni, ad es. la base viene sollevata e, come servizio, accetta connessioni dai client. E questa accessibilità può essere valutata in base a determinate caratteristiche. È molto comodo visualizzare queste caratteristiche sui dashboard.

Tutti sanno cosa sono i dashboard. Questo è quando hai dato un'occhiata alla schermata in cui sono riepilogate le informazioni necessarie. E puoi determinare immediatamente se c'è un problema nel database o meno.
Di conseguenza, la disponibilità del database e altre caratteristiche chiave dovrebbero essere sempre visualizzate sui dashboard in modo che queste informazioni siano a portata di mano e sempre disponibili. Alcuni dettagli aggiuntivi che già aiutano nell'indagine degli incidenti, quando si indaga su alcune situazioni di emergenza, devono già essere inseriti in dashboard secondarie o nascosti in collegamenti di drilldown che portano a sistemi di monitoraggio di terze parti.

Un esempio di un noto sistema di monitoraggio. Questo è un sistema di monitoraggio molto interessante. Raccoglie molti dati, ma dal mio punto di vista ha uno strano concetto di dashboard. C'è un collegamento per "creare una dashboard". Ma quando crei una dashboard, crei un elenco di due colonne, un elenco di grafici. E quando hai bisogno di guardare qualcosa, inizi a cliccare con il mouse, scorrere, cercare il grafico desiderato. E questo richiede tempo, ovvero non esistono dashboard veri e propri. Ci sono solo elenchi di grafici.

Cosa dovresti aggiungere a queste dashboard? Puoi iniziare con una caratteristica come il tempo di risposta. PostgreSQL ha la vista pg_stat_statements. È disabilitato per impostazione predefinita, ma è una delle visualizzazioni di sistema importanti che dovrebbero essere sempre abilitate e utilizzate. Memorizza informazioni su tutte le query in esecuzione che sono state eseguite nel database.

Di conseguenza, possiamo partire dal fatto che possiamo prendere il tempo di esecuzione totale di tutte le richieste e dividerlo per il numero di richieste utilizzando i campi sopra. Ma questa è la temperatura media in ospedale. Possiamo iniziare da altri campi: tempo minimo di esecuzione della query, massimo e mediano. E possiamo anche costruire percentili; PostgreSQL ha funzioni corrispondenti per questo. E possiamo ottenere dei numeri che caratterizzano il tempo di risposta del nostro database per le richieste già completate, ovvero non eseguiamo la falsa richiesta 'seleziona 1' e guardiamo il tempo di risposta, ma analizziamo il tempo di risposta per le richieste già completate e disegniamo o una figura separata oppure costruiamo un grafico basato su di essa.

È anche importante monitorare il numero di errori attualmente generati dal sistema. E per questo puoi usare la vista pg_stat_database. Ci concentriamo sul campo xact_rollback. Questo campo mostra non solo il numero di rollback che si verificano nel database, ma tiene conto anche del numero di errori. Relativamente parlando, possiamo visualizzare questa cifra nella nostra dashboard e vedere quanti errori abbiamo attualmente. Se sono presenti molti errori, questo è un buon motivo per esaminare i registri e vedere che tipo di errori sono e perché si verificano, quindi investire e risolverli.

Puoi aggiungere qualcosa come un tachimetro. Questi sono il numero di transazioni al secondo e il numero di richieste al secondo. Relativamente parlando, puoi utilizzare questi numeri come prestazioni attuali del tuo database e osservare se ci sono picchi nelle richieste, picchi nelle transazioni o, al contrario, se il database è sottocarico perché qualche backend ha fallito. È importante guardare sempre questa cifra e ricordare che per il nostro progetto questo tipo di prestazione è normale, ma i valori sopra e sotto sono già in qualche modo problematici e incomprensibili, il che significa che dobbiamo capire perché questi numeri sono così alto.

Per stimare il numero di transazioni, possiamo fare nuovamente riferimento alla vista pg_stat_database. Possiamo aggiungere il numero di commit e il numero di rollback e ottenere il numero di transazioni al secondo.

Tutti capiscono che più richieste possono rientrare in un'unica transazione? Pertanto TPS e QPS sono leggermente diversi.

Il numero di richieste al secondo può essere ottenuto da pg_stat_statements e calcolare semplicemente la somma di tutte le richieste completate. È chiaro che confrontiamo il valore corrente con quello precedente, lo sottraiamo, otteniamo il delta e otteniamo la quantità.

Se lo desideri, puoi aggiungere ulteriori parametri che aiutano anche a valutare la disponibilità del nostro database e a monitorare se si sono verificati tempi di inattività.

Uno di questi parametri è il tempo di attività. Ma i tempi di attività in PostgreSQL sono un po' complicati. Ti dirò perché. Una volta avviato PostgreSQL, il tempo di attività inizia a segnalare. Ma se a un certo punto, ad esempio, qualche attività era in esecuzione di notte, arriva un OOM-killer e termina forzatamente il processo figlio PostgreSQL, in questo caso PostgreSQL termina la connessione di tutti i client, reimposta l'area di memoria frammentata e inizia il ripristino da l'ultimo posto di blocco. E mentre dura questo ripristino dal checkpoint, il database non accetta connessioni, ovvero questa situazione può essere valutata come tempo di inattività. Ma il contatore del tempo di attività non verrà azzerato, perché tiene conto del tempo di avvio di Postmaster fin dal primo momento. Pertanto, tali situazioni possono essere saltate.

Dovresti anche monitorare il numero di addetti al vuoto. Tutti sanno cos'è l'autovacuum in PostgreSQL? Questo è un sottosistema interessante in PostgreSQL. Sono stati scritti molti articoli su di lei, sono state fatte molte segnalazioni. Si discute molto sul vuoto e su come dovrebbe funzionare. Molti lo considerano un male necessario. Ma è così. Questa è una sorta di analogo di un garbage collector che pulisce le versioni obsolete di righe che non sono necessarie per alcuna transazione e libera spazio nelle tabelle e negli indici per nuove righe.

Perchè hai bisogno di monitorarlo? Perché il vuoto a volte fa molto male. Consuma una grande quantità di risorse e di conseguenza le richieste dei client iniziano a risentirne.

E dovrebbe essere monitorato attraverso la vista pg_stat_activity, di cui parlerò nella prossima sezione. Questa vista mostra l'attività corrente nel database. E attraverso questa attività possiamo monitorare il numero di aspirapolvere che stanno funzionando in questo momento. Possiamo tenere traccia dei vuoti e vedere che se abbiamo superato il limite, allora questo è un motivo per esaminare le impostazioni di PostgreSQL e ottimizzare in qualche modo il funzionamento del vuoto.

Un'altra caratteristica di PostgreSQL è che PostgreSQL è stufo delle transazioni lunghe. Soprattutto da transazioni che restano in sospeso per molto tempo e non fanno nulla. Questo è il cosiddetto stat idle-in-transaction. Una tale transazione blocca i blocchi e impedisce al vuoto di funzionare. E di conseguenza i tavoli si gonfiano e aumentano di dimensioni. E le query che funzionano con queste tabelle iniziano a funzionare più lentamente, perché è necessario spostare tutte le vecchie versioni delle righe dalla memoria al disco e viceversa. Occorre quindi monitorare anche il tempo, la durata delle transazioni più lunghe, le richieste di vuoto più lunghe. E se vediamo alcuni processi che sono in esecuzione da molto tempo, già più di 10-20-30 minuti per un caricamento OLTP, allora dobbiamo prestare loro attenzione e terminarli forzatamente, o ottimizzare l'applicazione in modo che non vengono chiamati e non si bloccano così a lungo. Per un carico di lavoro analitico 10-20-30 minuti sono normali; ce ne sono anche di più lunghi.

Successivamente abbiamo l'opzione con i client connessi. Dopo aver già creato una dashboard e pubblicato su di essa le principali metriche di disponibilità, possiamo anche aggiungere lì ulteriori informazioni sui client connessi.

Le informazioni sui client connessi sono importanti perché, dal punto di vista di PostgreSQL, i client sono diversi. Ci sono buoni clienti e ci sono cattivi clienti.

Un semplice esempio. Per cliente intendo l'applicazione. L'applicazione si è connessa al database e inizia immediatamente a inviare lì le sue richieste, il database le elabora, le esegue e restituisce i risultati al client. Questi sono clienti buoni e corretti.

Ci sono situazioni in cui il client si è connesso, mantiene la connessione ma non fa nulla. È in stato di inattività.

Ma ci sono clienti cattivi. Ad esempio, lo stesso client si è connesso, ha aperto una transazione, ha eseguito qualcosa nel database e poi è entrato nel codice, ad esempio per accedere a una fonte esterna o per elaborare lì i dati ricevuti. Ma non ha chiuso la transazione. E la transazione si blocca nel database e viene tenuta bloccata sulla linea. Questa è una brutta condizione. E se improvvisamente un'applicazione da qualche parte al suo interno fallisce con un'eccezione, la transazione può rimanere aperta per un tempo molto lungo. E questo influisce direttamente sulle prestazioni di PostgreSQL. PostgreSQL sarà più lento. Pertanto, è importante monitorare tali clienti in modo tempestivo e interrompere forzatamente il loro lavoro. E devi ottimizzare la tua applicazione in modo che tali situazioni non si verifichino.

Altri clienti cattivi stanno aspettando clienti. Ma diventano cattivi a causa delle circostanze. Ad esempio, una semplice transazione inattiva: può aprire una transazione, bloccare alcune righe, quindi da qualche parte nel codice fallirà, lasciando una transazione sospesa. Un altro client verrà e richiederà gli stessi dati, ma incontrerà un blocco, perché la transazione sospesa contiene già dei blocchi su alcune righe richieste. E la seconda transazione rimarrà in attesa del completamento della prima transazione o della chiusura forzata da parte dell'amministratore. Pertanto, le transazioni in sospeso possono accumularsi e riempire il limite di connessione al database. E quando il limite è pieno, l'applicazione non può più funzionare con il database. Questa è già una situazione di emergenza per il progetto. Pertanto, i clienti dannosi devono essere monitorati e trattati in modo tempestivo.

Un altro esempio di monitoraggio. E qui c'è già una dashboard decente. Ci sono informazioni sulle connessioni sopra. Connessione DB – 8 pezzi. E questo è tutto. Non abbiamo informazioni su quali client siano attivi, quali client siano semplicemente inattivi, senza fare nulla. Non ci sono informazioni sulle transazioni in sospeso e sulle connessioni in sospeso, ovvero questa è una cifra che mostra il numero di connessioni e basta. E poi indovina tu stesso.

Di conseguenza, per aggiungere queste informazioni al monitoraggio, è necessario accedere alla vista di sistema pg_stat_activity. Se trascorri molto tempo su PostgreSQL, questa è un'ottima vista che dovrebbe diventare tua amica, perché mostra l'attività corrente in PostgreSQL, cioè cosa sta succedendo al suo interno. Per ogni processo c'è una riga separata che mostra le informazioni su questo processo: da quale host è stata effettuata la connessione, con quale utente, con quale nome, quando è stata avviata la transazione, quale richiesta è attualmente in esecuzione, quale richiesta è stata eseguita l'ultima volta. E, di conseguenza, possiamo valutare lo stato del cliente utilizzando il campo stat. Relativamente parlando, possiamo raggruppare in base a questo campo e ottenere le statistiche attualmente presenti nel database e il numero di connessioni che hanno questa statistica nel database. E possiamo inviare i numeri già ricevuti al nostro monitoraggio e tracciare grafici basati su di essi.
È importante valutare anche la durata dell’operazione. Ho già detto che è importante valutare la durata dei vuoti, ma le transazioni si valutano allo stesso modo. Sono presenti i campi xact_start e query_start. Essi, relativamente parlando, mostrano l'ora di inizio della transazione e l'ora di inizio della richiesta. Prendiamo la funzione now(), che mostra il timestamp corrente, sottraiamo la transazione e richiediamo il timestamp. E otteniamo la durata della transazione, la durata della richiesta.

Se vediamo transazioni lunghe, dovremmo già completarle. Per un caricamento OLTP, le transazioni lunghe durano già più di 1-2-3 minuti. Per un carico di lavoro OLAP, le transazioni lunghe sono normali, ma se richiedono più di due ore per essere completate, anche questo è un segno che c'è una distorsione da qualche parte.

Una volta che i clienti si sono connessi al database, iniziano a lavorare con i nostri dati. Accedono alle tabelle, accedono agli indici per ottenere dati dalla tabella. Ed è importante valutare il modo in cui i clienti interagiscono con questi dati.

Questo è necessario per valutare il nostro carico di lavoro e capire approssimativamente quali sono i tavoli più “caldi” per noi. Ad esempio, ciò è necessario nelle situazioni in cui vogliamo posizionare tabelle “calde” su una sorta di storage SSD veloce. Ad esempio, alcune tabelle di archivio che non utilizziamo da molto tempo possono essere spostate in una sorta di archivio "freddo", su unità SATA e lasciarle vivere lì, sarà possibile accedervi secondo necessità.

Ciò è utile anche per rilevare anomalie dopo eventuali rilasci e distribuzioni. Diciamo che il progetto ha rilasciato alcune nuove funzionalità. Ad esempio, abbiamo aggiunto nuove funzionalità per lavorare con il database. E se tracciamo i grafici sull'utilizzo delle tabelle, possiamo facilmente rilevare queste anomalie su questi grafici. Ad esempio, aggiorna i burst o elimina i burst. Sarà molto visibile.

Puoi anche rilevare anomalie nelle statistiche “fluttuanti”. Cosa significa? PostgreSQL ha un pianificatore di query molto potente e molto buono. E gli sviluppatori dedicano molto tempo al suo sviluppo. Come lavora? Per poter fare buoni piani, PostgreSQL raccoglie statistiche sulla distribuzione dei dati nelle tabelle in un certo intervallo di tempo e con una certa frequenza. Questi sono i valori più comuni: il numero di valori univoci, informazioni su NULL nella tabella, molte informazioni.

Sulla base di queste statistiche, il pianificatore costruisce diverse query, seleziona quella più ottimale e utilizza questo piano di query per eseguire la query stessa e restituire i dati.

E succede che le statistiche “fluttuano”. I dati qualitativi e quantitativi nella tabella sono in qualche modo cambiati, ma le statistiche non sono state raccolte. E i piani formulati potrebbero non essere ottimali. E se i nostri piani risultassero non ottimali in base ai monitoraggi raccolti, in base alle tabelle, potremo vedere queste anomalie. Ad esempio, da qualche parte i dati sono cambiati qualitativamente e al posto dell'indice è stato utilizzato un passaggio sequenziale attraverso la tabella, ad es. se una query deve restituire solo 100 righe (esiste un limite di 100), verrà eseguita una ricerca completa per questa query. E questo ha sempre un effetto molto negativo sulle prestazioni.

E questo lo possiamo vedere nel monitoraggio. E guarda già questa query, esegui una spiegazione, raccogli statistiche, crea un nuovo indice aggiuntivo. E già rispondi a questo problema. Ecco perché è importante.

Un altro esempio di monitoraggio. Penso che molte persone lo abbiano riconosciuto perché è molto popolare. Chi lo utilizza nei propri progetti Prometeo? Chi usa questo prodotto insieme a Prometheus? Il fatto è che nel repository standard di questo monitoraggio è presente una dashboard per lavorare con PostgreSQL: postgres_exporter Prometeo. Ma c'è un dettaglio negativo.

Ci sono diversi grafici. E i byte sono indicati come unità, ad es. ci sono 5 grafici. Questi sono Inserisci dati, Aggiorna dati, Elimina dati, Recupera dati e Restituisci dati. L'unità di misura è il byte. Ma il fatto è che le statistiche in PostgreSQL restituiscono i dati in tuple (righe). E, di conseguenza, questi grafici sono un ottimo modo per sottovalutare il carico di lavoro più volte, decine di volte, perché una tupla non è un byte, una tupla è una stringa, è di molti byte ed è sempre di lunghezza variabile. Cioè, calcolare il carico di lavoro in byte utilizzando le tuple è un compito irrealistico o molto difficile. Pertanto, quando si utilizza una dashboard o un monitoraggio integrato, è sempre importante capire che funzioni correttamente e restituisca dati valutati correttamente.

Come ottenere statistiche su queste tabelle? A questo scopo PostgreSQL dispone di una determinata famiglia di visualizzazioni. E la vista principale è pg_stat_user_tables. User_tables: significa tabelle create per conto dell'utente. Al contrario, ci sono viste di sistema utilizzate dallo stesso PostgreSQL. E c'è una tabella riepilogativa Alltables, che include sia quelle di sistema che quelle dell'utente. Puoi iniziare da quello che ti piace di più.

Utilizzando i campi sopra puoi stimare il numero di inserimenti, aggiornamenti ed eliminazioni. L'esempio di dashboard che ho utilizzato utilizza questi campi per valutare le caratteristiche di un carico di lavoro. Pertanto possiamo anche basarci su di essi. Ma vale la pena ricordare che queste sono tuple, non byte, quindi non possiamo farlo solo in byte.

Sulla base di questi dati possiamo costruire le cosiddette tabelle TopN. Ad esempio, Top-5, Top-10. E puoi tenere traccia di quelle tavole calde che vengono riciclate più di altre. Ad esempio 5 tabelle “calde” da inserire. E utilizzando queste tabelle TopN valutiamo il nostro carico di lavoro e possiamo valutare i picchi di carico di lavoro dopo eventuali rilasci, aggiornamenti e distribuzioni.

È anche importante valutare la dimensione della tabella, perché a volte gli sviluppatori lanciano una nuova funzionalità e le nostre tabelle iniziano a gonfiarsi di grandi dimensioni, perché hanno deciso di aggiungere una quantità aggiuntiva di dati, ma non hanno previsto come ciò sarebbe avvenuto. influiscono sulla dimensione del database. Anche questi casi ci sorprendono.

E ora una piccola domanda per te. Quale domanda sorge quando noti il ​​carico sul tuo server database? Qual è la prossima domanda che hai?

Ma in realtà la questione si pone come segue. Quali richieste provoca il carico? Cioè, non è interessante osservare i processi causati dal carico. È chiaro che se l'host dispone di un database, il database è in esecuzione lì ed è chiaro che lì verranno eliminati solo i database. Se apriamo Top, vedremo un elenco di processi in PostgreSQL che stanno facendo qualcosa. Non sarà chiaro a Top cosa stanno facendo.

Di conseguenza, è necessario trovare quelle query che causano il carico maggiore, poiché l'ottimizzazione delle query, di norma, offre maggiori profitti rispetto all'ottimizzazione di PostgreSQL o della configurazione del sistema operativo o persino all'ottimizzazione dell'hardware. Secondo la mia stima, questo è circa l'80-85-90%. E questo viene fatto molto più velocemente. È più veloce correggere una richiesta che correggere la configurazione, pianificare un riavvio, soprattutto se non è possibile riavviare il database, o aggiungere hardware. È più semplice riscrivere la query da qualche parte o aggiungere un indice per ottenere un risultato migliore da questa query.

Occorre pertanto monitorare le richieste e la loro adeguatezza. Prendiamo un altro esempio di monitoraggio. E anche qui il monitoraggio sembra essere ottimo. Sono disponibili informazioni sulla replica, informazioni su velocità effettiva, blocco e utilizzo delle risorse. Va tutto bene, ma non ci sono informazioni sulle richieste. Non è chiaro quali query siano in esecuzione nel nostro database, per quanto tempo vengono eseguite, quante di queste query siano. Abbiamo sempre bisogno di avere queste informazioni nel nostro monitoraggio.

E per ottenere queste informazioni possiamo usare il modulo pg_stat_statements. Sulla base di esso, puoi costruire una varietà di grafici. Ad esempio è possibile ottenere informazioni sulle query più frequenti, ovvero su quelle query che vengono eseguite più spesso. Sì, dopo le distribuzioni è anche molto utile guardarlo e capire se c'è qualche impennata di richieste.

È possibile monitorare le query più lunghe, ovvero quelle query che richiedono più tempo per essere completate. Funzionano sul processore e consumano I/O. Possiamo anche valutarlo utilizzando i campi total_time, mean_time, blk_write_time e blk_read_time.

Possiamo valutare e monitorare le richieste più pesanti in termini di utilizzo delle risorse, quelle che leggono dal disco, che lavorano con la memoria o, al contrario, creano una sorta di carico di scrittura.

Possiamo valutare le richieste più generose. Queste sono le query che restituiscono un numero elevato di righe. Ad esempio, potrebbe trattarsi di una richiesta in cui si sono dimenticati di impostare un limite. E restituisce semplicemente l'intero contenuto della tabella o della query tra le tabelle interrogate.

Puoi anche monitorare le query che utilizzano file o tabelle temporanee.

E abbiamo ancora processi in background. I processi in background sono principalmente checkpoint o sono anche chiamati checkpoint, si tratta di autovacuum e replica.

Un altro esempio di monitoraggio. C'è una scheda Manutenzione sulla sinistra, vai su di essa e spera di vedere qualcosa di utile. Ma qui c'è solo il momento dell'operazione di vuoto e della raccolta delle statistiche, niente di più. Si tratta di informazioni molto scarse, quindi abbiamo sempre bisogno di informazioni su come funzionano i processi in background nel nostro database e se ci sono problemi derivanti dal loro lavoro.

Quando guardiamo i checkpoint, dovremmo ricordare che i checkpoint scaricano le pagine sporche dall'area di memoria frammentata sul disco, quindi creano un checkpoint. E questo checkpoint può quindi essere utilizzato come luogo di ripristino nel caso in cui PostgreSQL venga improvvisamente terminato in caso di emergenza.

Di conseguenza, per scaricare tutte le pagine "sporche" sul disco, è necessario eseguire una certa quantità di scrittura. E, di norma, sui sistemi con grandi quantità di memoria, questo è molto. E se eseguiamo i checkpoint molto spesso in un breve intervallo, le prestazioni del disco diminuiranno in modo molto significativo. E le richieste dei clienti risentiranno della mancanza di risorse. Competeranno per le risorse e mancheranno di produttività.

Di conseguenza, tramite pg_stat_bgwriter utilizzando i campi specificati possiamo monitorare il numero di checkpoint che si verificano. E se abbiamo molti posti di blocco per un certo periodo di tempo (in 10-15-20 minuti, in mezz'ora), ad esempio 3-4-5, allora questo può già essere un problema. E devi già cercare nel database, guardare nella configurazione, cosa causa una tale abbondanza di checkpoint. Forse c'è qualche tipo di grande registrazione in corso. Possiamo già valutare il carico di lavoro, perché abbiamo già aggiunto i grafici del carico di lavoro. Possiamo già modificare i parametri del checkpoint e assicurarci che non influenzino notevolmente le prestazioni della query.

Torno di nuovo all'autovacuum perché, come ho detto, può facilmente sommare le prestazioni del disco e delle query, quindi è sempre importante stimare la quantità di autovacuum.

Il numero di addetti all'autoaspirapolvere nel database è limitato. Per impostazione predefinita, ce ne sono tre, quindi se abbiamo sempre tre lavoratori che lavorano nel database, ciò significa che il nostro autovacuum non è configurato, dobbiamo aumentare i limiti, rivedere le impostazioni di autovacuum ed entrare nella configurazione.
È importante valutare quali aspiratori abbiamo. O è stato lanciato dall'utente, il DBA è arrivato e ha lanciato manualmente una sorta di vuoto, e questo ha creato un carico. Abbiamo qualche tipo di problema. Oppure questo è il numero di aspirapolvere che svitano il contatore delle transazioni. Per alcune versioni di PostgreSQL si tratta di vuoti molto pesanti. E possono facilmente aumentare le prestazioni perché leggono l'intera tabella, scansionano tutti i blocchi in quella tabella.

E, naturalmente, la durata del vuoto. Se disponiamo di aspirapolvere di lunga durata che funzionano per molto tempo, significa che dobbiamo prestare nuovamente attenzione alla configurazione del vuoto e magari riconsiderarne le impostazioni. Perché può verificarsi una situazione in cui l'aspirapolvere funziona a lungo sul tavolo (3-4 ore), ma durante il tempo in cui l'aspirapolvere ha funzionato, una grande quantità di file morte è riuscita ad accumularsi nuovamente nella tabella. E non appena l'aspirazione sarà completata, dovrà aspirare nuovamente questo tavolo. E arriviamo a una situazione: un vuoto infinito. E in questo caso, il vuoto non riesce a far fronte al suo lavoro e le tabelle iniziano gradualmente ad aumentare di dimensioni, sebbene il volume dei dati utili in esso contenuti rimanga lo stesso. Pertanto, durante i lunghi vuoti, guardiamo sempre la configurazione e cerchiamo di ottimizzarla, ma allo stesso tempo in modo che le prestazioni delle richieste del cliente non ne risentano.

Al giorno d'oggi non esiste praticamente alcuna installazione PostgreSQL che non disponga di replica in streaming. La replica è il processo di spostamento dei dati da un master a una replica.

La replica in PostgreSQL viene eseguita tramite un registro delle transazioni. La procedura guidata genera un registro delle transazioni. Il registro delle transazioni viaggia attraverso la connessione di rete alla replica e quindi viene riprodotto sulla replica. È semplice.

Di conseguenza, la vista pg_stat_replication viene utilizzata per monitorare il ritardo di replica. Ma non tutto è semplice con lei. Nella versione 10 la vista ha subito diverse modifiche. Innanzitutto, alcuni campi sono stati rinominati. E alcuni campi sono stati aggiunti. Nella versione 10 sono comparsi dei campi che consentono di stimare il ritardo di replica in secondi. È molto comodo Prima della versione 10 era possibile stimare il ritardo di replica in byte. Questa opzione rimane nella versione 10, ovvero puoi scegliere cosa è più conveniente per te: stimare il ritardo in byte o stimare il ritardo in secondi. Molte persone fanno entrambe le cose.

Tuttavia, per valutare il ritardo di replica, è necessario conoscere la posizione del log nella transazione. E queste posizioni del registro delle transazioni sono esattamente nella vista pg_stat_replication. Relativamente parlando, possiamo prendere due punti nel log delle transazioni utilizzando la funzione pg_xlog_location_diff(). Calcola il delta tra loro e ottieni il ritardo di replica in byte. È molto comodo e semplice.

Nella versione 10, questa funzione è stata rinominata pg_wal_lsn_diff(). In generale, in tutte le funzioni, viste e utilità in cui appariva la parola “xlog”, questa veniva sostituita con il valore “wal”. Questo vale sia per le visualizzazioni che per le funzioni. Questa è una tale innovazione.

Inoltre, nella versione 10, sono state aggiunte delle linee che mostrano specificamente il ritardo. Questi sono il ritardo di scrittura, il ritardo di flush, il ritardo di riproduzione. Cioè, è importante monitorare queste cose. Se vediamo che abbiamo un ritardo nella replicazione, dobbiamo indagare sul motivo per cui è apparso, da dove proviene e risolvere il problema.

Quasi tutto è in ordine con le metriche di sistema. Quando inizia qualsiasi monitoraggio, inizia con le metriche di sistema. Questa è la disposizione di processori, memoria, swap, rete e disco. Tuttavia, molti parametri non sono presenti per impostazione predefinita.

Se tutto è in ordine con il processo di riciclaggio, allora ci sono problemi con il riciclaggio del disco. Di norma, il monitoraggio degli sviluppatori aggiunge informazioni sulla velocità effettiva. Può essere in iops o byte. Ma si dimenticano della latenza e dell'utilizzo dei dispositivi disco. Questi sono parametri più importanti che ci permettono di valutare quanto sono caricati i nostri dischi e quanto sono lenti. Se abbiamo una latenza elevata, significa che ci sono dei problemi con i dischi. Se abbiamo un utilizzo elevato, significa che i dischi non reggono. Queste sono caratteristiche migliori della produttività.

Inoltre, queste statistiche possono essere ottenute anche dal file system /proc, come avviene per i processori di riciclo. Non so perché queste informazioni non vengano aggiunte al monitoraggio. Tuttavia, è importante tenerlo presente nel monitoraggio.

Lo stesso vale per le interfacce di rete. Ci sono informazioni sul throughput della rete in pacchetti, in byte, ma tuttavia non ci sono informazioni sulla latenza e nessuna informazione sull'utilizzo, sebbene anche queste siano informazioni utili.

Qualsiasi monitoraggio presenta degli svantaggi. E indipendentemente dal tipo di monitoraggio adottato, non soddisferà sempre alcuni criteri. Tuttavia, si stanno sviluppando, vengono aggiunte nuove funzionalità e nuove cose, quindi scegli qualcosa e finiscilo.

E per finire devi sempre avere un’idea di cosa significano le statistiche fornite e di come puoi utilizzarle per risolvere i problemi.

E alcuni punti chiave:

• Dovresti sempre monitorare la disponibilità e disporre di dashboard in modo da poter valutare rapidamente che tutto sia in ordine con il database.
• Devi sempre avere un'idea di quali clienti stanno lavorando con il tuo database per eliminare i clienti cattivi e abbatterli.
• È importante valutare il modo in cui questi client utilizzano i dati. Devi avere un'idea del tuo carico di lavoro.
• È importante valutare come si forma questo carico di lavoro, con l'aiuto di quali query. Puoi valutare le query, ottimizzarle, rifattorizzarle, creare indici per esse. È molto importante.
• I processi in background possono avere un impatto negativo sulle richieste dei client, quindi è importante monitorare che non utilizzino troppe risorse.
• Le metriche di sistema ti consentono di pianificare il ridimensionamento e l'aumento della capacità dei tuoi server, quindi è importante monitorare e valutare anche questi.

Se sei interessato a questo argomento puoi seguire questi link.
http://bit.do/stats_collector - questa è la documentazione ufficiale del raccoglitore di statistiche. È presente una descrizione di tutte le visualizzazioni statistiche e una descrizione di tutti i campi. Puoi leggerli, comprenderli e analizzarli. E sulla base di essi, costruisci i tuoi grafici e aggiungili al tuo monitoraggio.

Richieste di esempio:
http://bit.do/dataegret_sql
http://bit.do/lesovsky_sql

Questo è il nostro repository aziendale e il mio. Contengono query di esempio. Non ci sono query dalla serie select* from lì. Esistono già query già pronte con join, che utilizzano funzioni interessanti che consentono di trasformare i numeri grezzi in valori leggibili e convenienti, ad es. questi sono byte, tempo. Puoi raccoglierli, guardarli, analizzarli, aggiungerli al tuo monitoraggio, costruire il tuo monitoraggio basato su di essi.

domande

Domanda: hai detto che non pubblicizzerai i marchi, ma sono comunque curioso: che tipo di dashboard usi nei tuoi progetti?
Risposta: varia. Succede che arriviamo a un cliente e lui ha già il proprio monitoraggio. E consigliamo il cliente su ciò che deve essere aggiunto al suo monitoraggio. La situazione peggiore è con Zabbix. Perché non ha la capacità di costruire grafici TopN. Usiamo noi stessi Okmetro, perché ci stavamo consultando con questi ragazzi sul monitoraggio. Hanno monitorato PostgreSQL in base alle nostre specifiche tecniche. Sto scrivendo il mio progetto personale, che raccoglie dati tramite Prometheus e li visualizza graminacee. Il mio compito è creare il mio esportatore in Prometheus e quindi eseguire il rendering di tutto in Grafana.

Domanda: Esistono analoghi ai report AWR o... aggregazione? Conosci qualcosa del genere?
Risposta: Sì, so cos'è AWR, è una cosa interessante. Al momento ci sono una varietà di biciclette che implementano approssimativamente il seguente modello. Ad un certo intervallo di tempo, alcune linee di base vengono scritte nello stesso PostgreSQL o in un archivio separato. Puoi cercarli su Google su Internet, sono lì. Uno degli sviluppatori di una cosa del genere è seduto sul forum sql.ru nel thread PostgreSQL. Puoi prenderlo lì. Sì, ci sono cose del genere, possono essere usate. Inoltre nel suo pgCenter Sto anche scrivendo una cosa che ti permette di fare la stessa cosa.

PS1 Se utilizzi postgres_exporter, quale dashboard stai utilizzando? Ce ne sono molti. Sono già obsoleti. Forse la comunità creerà un modello aggiornato?

PS2 Rimosso pganalyze perché è un'offerta SaaS proprietaria che si concentra sul monitoraggio delle prestazioni e sui suggerimenti di ottimizzazione automatizzata.

Solo gli utenti registrati possono partecipare al sondaggio. AccediPer favore.

Quale monitoraggio postgresql self-hosted (con dashboard) consideri il migliore?

• 30,0%Zabbix + aggiunte da Alexey Lesovsky o zabbix 4.4 o libzbxpgsql + zabbix libzbxpgsql + zabbix3

• 0,0%https://github.com/lesovsky/pgcenter0

• 0,0%https://github.com/pg-monz/pg_monz0

• 20,0%https://github.com/cybertec-postgresql/pgwatch22

• 20,0%https://github.com/postgrespro/mamonsu2

• 0,0%https://www.percona.com/doc/percona-monitoring-and-management/conf-postgres.html0

• 10,0%pganalyze è un SaaS proprietario: non posso eliminarlo1

• 10,0%https://github.com/powa-team/powa1

• 0,0%https://github.com/darold/pgbadger0

• 0,0%https://github.com/darold/pgcluu0

• 0,0%https://github.com/zalando/PGObserver0

• 10,0%https://github.com/spotify/postgresql-metrics1

10 utenti hanno votato. 26 utenti si sono astenuti.

Fonte: habr.com