Trascrizione della relazione del 2015 di Alexey Lesovsky "Deep dive into PostgreSQL internal statistics"
Disclaimer dall'autore della relazione: Nota che questa relazione è datata novembre 2015 — sono passati oltre 4 anni e molto tempo è passato. La versione trattata nella relazione, 9.4, non è più supportata. Negli ultimi 4 anni sono stati rilasciati 5 nuovi aggiornamenti che hanno introdotto molte novità, miglioramenti e cambiamenti relativi alle statistiche, e parte del materiale è obsoleto e non rilevante. Durante la revisione, ho cercato di segnalare questi punti per non confondere te, lettore. Tuttavia, non ho riscritto questi passaggi, ce ne sono troppi e alla fine risulterebbe in una relazione completamente diversa.
Il DBMS PostgreSQL è un potente meccanismo composto da molte sottosistemi, la cui interazione diretta influisce sulla performance del DBMS stesso. Durante il suo utilizzo, vengono raccolti dati e informazioni sulle performance dei componenti, permettendo di valutare l'efficacia di PostgreSQL e adottare misure per ottimizzarne le prestazioni. Tuttavia, l'ammontare di informazioni è vasto e presentato in modo piuttosto semplificato. L'elaborazione e l'interpretazione di queste informazioni può essere una sfida non banale, e il "parco giochi" di strumenti e utility potrebbe facilmente disorientare anche un DBA esperto.
Buongiorno! Mi chiamo Aleksej. Come ha detto Il'ja, parlerò della statistica di PostgreSQL.
Statistiche di attività di PostgreSQL. PostgreSQL ha due tipi di statistiche. La statistica di attività, di cui parleremo, e la statistica dello scheduler riguardante la distribuzione dei dati. Mi concentrerò sulla statistica di attività di PostgreSQL, che ci permette di valutare la performance e di come migliorarla.
Ti mostrerò come utilizzare efficacemente le statistiche per risolvere vari problemi che hai o che potresti avere.
Cosa non verrà trattato nella relazione? Non parlerò delle statistiche del pianificatore, poiché è un tema a sé stante per un'altra presentazione riguardo a come i dati sono memorizzati nel database e a come il pianificatore delle query ottiene una visione qualitativa e quantitativa di queste informazioni.
Non ci saranno recensioni di strumenti, non confronterò un prodotto con un altro. Nessuna pubblicità sarà presente. Lasciamo tutto ciò da parte.
Voglio mostrarvi che utilizzare le statistiche è utile. È necessario. Non fa paura utilizzarle. Avremo bisogno solo di SQL di base e di conoscenze fondamentali su SQL.
Parleremo anche di quali statistiche scegliere per risolvere i problemi.
Se guardiamo a PostgreSQL e nella sistema operativo eseguiamo un comando per visualizzare i processi, vediamo una "scatola nera". Vediamo alcuni processi che fanno qualcosa e possiamo fare un'idea di cosa stiano facendo dai nomi. Ma in sostanza, è una scatola nera, non possiamo guardare all'interno.
Possiamo monitorare il carico della CPU in top, possiamo analizzare l'utilizzo della memoria con alcune utilità di sistema, ma non possiamo addentrarci all'interno di PostgreSQL. Per questo ci servono altri strumenti.
Continuando, parlerò di dove viene speso il tempo. Se immaginiamo PostgreSQL come uno schema, sarà possibile rispondere a dove viene speso il tempo. Si tratta di due aspetti: il trattamento delle richieste client da parte delle applicazioni e i task in background che PostgreSQL svolge per mantenere la sua operatività.
Se iniziamo a considerare dall'angolo in alto a sinistra, possiamo tracciare come vengono gestite le richieste dei clienti. La richiesta arriva dall'applicazione e per ulteriori elaborazioni viene aperta una sessione cliente. La richiesta viene inviata al pianificatore. Il pianificatore costruisce un piano di richiesta. Lo invia poi per l'esecuzione. Si verifica qualche operazione di input/output sui dati legati alle tabelle e agli indici. I dati necessari vengono letti dai dischi in memoria in un'area speciale chiamata "shared buffers". I risultati della richiesta, se si tratta di aggiornamenti o cancellazioni, vengono registrati nel log delle transazioni nel WAL. Alcune informazioni statistiche vengono raccolte nel log o nel raccoglitore delle statistiche. Infine, il risultato della richiesta viene restituito al cliente. Dopodiché, il cliente può ripetere tutto da capo con una nuova richiesta.
Cosa succede con i compiti in background e i processi in background? Abbiamo diversi processi che garantiscono il funzionamento e mantengono il database in uno stato operativo normale. Questi processi saranno anch'essi trattati nella presentazione: si tratta di autovacuum, checkpointer, processi legati alla replicazione, e background writer. Ognuno di essi verrà menzionato durante la presentazione.
Quali problemi ci sono con la statistica?
- Ci sono molte informazioni. PostgreSQL 9.4 offre 109 metriche per visualizzare i dati statistici. Tuttavia, se il database contiene molte tabelle, schemi e database, tutte queste metriche dovranno essere moltiplicate per il numero corrispondente di tabelle e database. Cioè, le informazioni diventano ancora più numerose. E affogare in esse è molto facile.
- Il problema successivo è che la statistica è presentata sotto forma di contatori. Se guardiamo questa statistica, vediamo contatori in costante aumento. E se è passato molto tempo dall'azzeramento della statistica, vedremo valori nell'ordine dei miliardi. E questi non ci dicono nulla.
- Manca la cronologia. Se hai riscontrato un malfunzionamento, qualcosa è crollato 15-30 minuti fa, non puoi usare la statistica per vedere cosa è successo 15-30 minuti fa. Questo è un problema.
- L'assenza di uno strumento integrato in PostgreSQL è un problema. Gli sviluppatori del kernel non forniscono alcuna utilità. Non hanno niente del genere. Forniscono semplicemente la statistica nel database. Usala, fai la tua query, fai ciò che vuoi.
- Poiché non esiste uno strumento integrato in PostgreSQL, questo causa un altro problema. Numerosi strumenti di terze parti. Ogni azienda con un minimo di competenze cerca di scrivere il proprio programma. E alla fine ci sono molti strumenti nella community che possono essere utilizzati per lavorare con le statistiche. In alcuni strumenti ci sono certe funzionalità, in altri mancano, oppure ci sono nuove possibilità. Si crea quindi la situazione di dover usare due, tre o quattro strumenti che si sovrappongono e hanno funzioni diverse. Questo è molto sgradevole.
Cosa ne deriva? È importante essere in grado di raccogliere statistiche direttamente, per non dipendere dai programmi, oppure migliorare questi programmi: aggiungere alcune funzionalità per trarne beneficio.
E sono necessarie conoscenze di base di SQL. Per ottenere dati dalle statistiche, è necessario redigere query SQL, ovvero è necessario sapere come vengono formulate le select, le join.
Le statistiche ci offrono diverse informazioni. Queste possono essere suddivise in categorie.
- La prima categoria comprende gli eventi che si verificano nel database. Si tratta di eventi come richieste, accessi a tabelle, autovacuum, commit; tutti questi sono eventi. I contatori corrispondenti a questi eventi vengono incrementati. Possiamo quindi tracciare questi eventi.
- La seconda categoria riguarda le proprietà degli oggetti, come tabelle e database. Hanno delle proprietà. Ad esempio, la dimensione delle tabelle. Possiamo monitorare la crescita delle tabelle e degli indici. Possiamo anche osservare le variazioni nel tempo.
- La terza categoria è il tempo speso per un evento. Una richiesta è un evento e ha una sua misura di durata specifica. Qui inizia e qui termina. Possiamo monitorarlo, così come il tempo di lettura da disco o di scrittura. Anche queste cose vengono tracciate.
Le fonti statistiche sono presentate come segue:
- Nella memoria condivisa (shared buffers) c'è un segmento dedicato all'alloggio di dati statistici, dove si trovano anche i contatori che vengono costantemente incrementati quando si verificano vari eventi o si presentano determinati momenti nell'attività del database.
- Questi contatori non sono accessibili all'utente e nemmeno all'amministratore. Sono cose a basso livello. Per accedervi, PostgreSQL offre un'interfaccia sotto forma di funzioni SQL. Possiamo effettuare delle selezioni utilizzando queste funzioni e ottenere alcune metriche (o un insieme di metriche).
- Tuttavia, utilizzare queste funzioni non è sempre comodo, pertanto le funzioni servono come base per le viste (VIEW). Queste sono tabelle virtuali che forniscono statistiche su una specifica sotto-sistema o su un insieme di eventi nel database.
- Queste viste incorporate (VIEW) costituiscono l'interfaccia principale per l'utente per lavorare con le statistiche. Sono disponibili per default senza alcuna configurazione aggiuntiva, potete iniziare ad utilizzarle subito per visualizzare e prelevare informazioni. Ci sono anche i contrib. I contrib sono ufficiali. Potete installare il pacchetto postgresql-contrib (ad esempio, postgresql94-contrib), caricare il modulo necessario nella configurazione, specificare i parametri e riavviare PostgreSQL per utilizzarlo. (Nota. A seconda della distribuzione, nelle versioni più recenti il pacchetto contrib fa parte del pacchetto principale.).
- Ci sono contributi non ufficiali. Non sono inclusi nella fornitura standard di PostgreSQL. Devono essere compilati o installati come librerie. Le opzioni possono variare ampiamente, a seconda di ciò che ha ideato lo sviluppatore di questo contributo non ufficiale.
In questa diapositiva sono presentate tutte le viste (VIEW) e alcune delle funzioni disponibili in PostgreSQL 9.4. Come possiamo vedere, ce ne sono molte. Ed è piuttosto facile confondersi se ci si imbatte in questo per la prima volta.
Tuttavia, se prendiamo l'immagine precedente Come viene speso il tempo in PostgreSQL e lo confrontiamo con questo elenco, otteniamo quest'immagine. Ogni vista (VIEW) o funzione può essere utilizzata per vari scopi per ottenere le statistiche corrispondenti quando PostgreSQL è in funzione. E possiamo già ottenere alcune informazioni sul funzionamento del sottosistema.
La prima cosa che esamineremo è pg_stat_database. Come possiamo vedere, questa vista contiene molte informazioni. Informazioni molto varie. E fornisce una conoscenza molto utile su ciò che sta accadendo nel database.
Cosa possiamo trarre di utile da lì? Cominciamo dalle cose più semplici.
seleziona
somma(blks_hit)*100/somma(blks_hit+blks_read) come hit_ratio
from pg_stat_database;La prima cosa che possiamo controllare è la percentuale di hit nella cache. La percentuale di hit nella cache è una metrica utile. Permette di valutare quale volume di dati viene prelevato dalla cache shared buffers e quale volume viene letto dal disco.
È ovvio che maggiore è il nostro hit nella cache, meglio è. Valutiamo questa metrica come percentuale. Ad esempio, se la percentuale di questi hit nella cache è superiore al 90 %, è un buon segno. Se scende sotto il 90 %, significa che non abbiamo abbastanza memoria per mantenere i dati "caldi" in memoria. E per utilizzare questi dati, PostgreSQL deve accedere al disco, il che è più lento rispetto alla lettura dalla memoria. A questo punto dobbiamo pensare a come aumentare la memoria: o aumentando gli shared buffers o migliorando la memoria hardware (RAM).
seleziona
datname,
(xact_commit*100)/(xact_commit+xact_rollback) come c_ratio,
deadlocks, conflitti,
foglio_temp, pg_size_pretty(temp_bytes) come temp_size
from pg_stat_database;Cosa altro possiamo ottenere da questa vista? Possiamo controllare le anomalie che si verificano nel database. Cosa viene mostrato qui? Ci sono commit, rollback, creazione di file temporanei, il loro volume, deadlocks e conflitti.
Possiamo sfruttare questa richiesta. Questa SQL è piuttosto semplice. Possiamo anche controllare questi dati interni.
E qui ci sono subito i valori soglia. Osserviamo il rapporto tra commit e rollback. I commit sono conferme riuscite delle transazioni. I rollback sono lo storno, cioè una transazione ha eseguito un lavoro, ha sollecitato il database, ha eseguito dei calcoli, ma poi c'è stata un'interruzione e i risultati della transazione vengono scartati. Cioè, un numero crescente di rollback è negativo. Dobbiamo evitarli e correggere il codice per prevenire tali situazioni.
I conflitti (conflicts) sono legati alla replica e anche essi vanno evitati. Se hai delle richieste che vengono eseguite sulla replica e si verificano conflitti, è necessario esaminarli, vedere cosa succede. I dettagli possono essere trovati nei log. È fondamentale risolvere le situazioni conflittuali affinché le richieste dell'applicazione funzionino senza errori.
I deadlock sono una situazione problematica. Quando le richieste competono per le risorse, una richiesta si rivolge a una risorsa e ottiene un blocco, mentre un'altra richiesta si rivolge a una seconda risorsa e ottiene anch'essa un blocco, e poi entrambe le richieste si rivolgono alle risorse l'une dell'altra e si bloccano in attesa che l'altra rilasci il blocco. Anche questa è una situazione problematica. Devono essere risolti a livello di riscrittura delle applicazioni e serializzazione dell'accesso alle risorse. Se notate un aumento costante dei deadlock, è necessario esaminare i dettagli nei log, analizzare le situazioni che si verificano e identificare il problema.
I file temporanei (temp_files) sono anche un problema. Quando a una richiesta dell'utente manca memoria per allocare dati temporanei, crea un file su disco. Tutte le operazioni che potrebbe eseguire nel buffer temporaneo in memoria iniziano a essere eseguite sul disco. Questo è lento. Questo aumenta il tempo di esecuzione della richiesta. E il client che ha inviato la richiesta a PostgreSQL riceverà una risposta con un certo ritardo. Se tutte queste operazioni venissero eseguite in memoria, Postgres risponderebbe molto più rapidamente e il cliente attenderebbe meno.
Pg_stat_bgwriter – questa vista descrive il funzionamento di due sottosistemi di PostgreSQL: esso checkpointer e writer di fondo.
Per iniziare, analizziamo i punti di controllo, ovvero i checkpoints. Cos'è un checkpoint? Un checkpoint è una posizione nel registro delle transazioni che indica che tutte le modifiche ai dati registrate nel log sono state sincronizzate con successo con i dati su disco. A seconda del carico di lavoro e delle impostazioni, questo processo può richiedere del tempo e consiste principalmente nella sincronizzazione delle pagine sporche nei buffer condivisi con i file di dati su disco. Perché è necessario? Se PostgreSQL accedesse costantemente al disco per prelevare e registrare dati a ogni operazione, sarebbe molto lento. Per questo motivo, PostgreSQL dispone di una porzione di memoria il cui size dipende dai parametri di configurazione. PostgreSQL allocca in questa memoria i dati da elaborare o restituire in risposta a richieste. In caso di richieste di modifica dei dati, i dati stessi vengono modificati. Così otteniamo due versioni dei dati: una in memoria e l'altra su disco. Periodicamente, è necessario sincronizzare questi dati. Dobbiamo sincronizzare su disco ciò che è stato modificato in memoria. A questo servono i checkpoint.
Checkpoint esegue un passaggio attraverso i buffer condivisi, contrassegnando le pagine sporche come necessarie per il checkpoint. Poi avvia un secondo passaggio attraverso i buffer condivisi. Le pagine contrassegnate per il checkpoint vengono quindi sincronizzate. In questo modo avviene la sincronizzazione dei dati con il disco.
Esistono due tipi di checkpoint. Un checkpoint viene eseguito per timeout. Questo checkpoint è utile e buono – checkpoint_timed. E ci sono checkpoint richiesti – checkpoint required. Questo checkpoint avviene quando abbiamo una scrittura massiccia di dati. Abbiamo registrato un gran numero di log delle transazioni. E PostgreSQL ritiene che debba sincronizzare tutto il più rapidamente possibile, effettuare il checkpoint e continuare a funzionare.
E se hai controllato le statistiche pg_stat_bgwriter e hai visto che hai checkpoint_req molto più alto rispetto a checkpoint_timed, allora è un problema. Perché è un problema? Questo significa che PostgreSQL è in una situazione di stress costante, dove deve scrivere i dati su disco. Il checkpoint in modalità timeout è meno stressante e viene eseguito secondo un programma interno, come se fosse dilatato nel tempo. PostgreSQL ha la capacità di fare pause nel lavoro e di non sovraccaricare il sistema di storage. Questo è utile per PostgreSQL. Le query eseguite durante il checkpoint non subiranno stress poiché il sistema di storage è occupato.
E per la regolazione del checkpoint ci sono tre parametri:
checkpoint_segments.checkpoint_timeout.checkpoint_completion_target.
Questi permettono di regolare il funzionamento dei checkpoint. Ma non mi soffermerò su di essi. Il loro impatto è un argomento a parte.
Attenzione: La versione discussa nel rapporto, 9.4, non è più attuale. Nelle versioni moderne di PostgreSQL il parametro checkpoint_segments è stato sostituito dai parametri min_wal_size e max_wal_size.
Il prossimo sotto-sistema è lo scrittore in background — writer di fondo. Cosa fa? Lavora continuamente in un ciclo infinito. Scansione delle pagine nei shared buffers e scrive su disco le pagine sporche che ha trovato. In questo modo aiuta il checkpointer a svolgere meno lavoro durante l'esecuzione dei checkpoint.
A cosa serve ancora? Soddisfa la necessità di pagine pulite nei shared buffers se devono essere richieste (in gran quantità e contemporaneamente) per memorizzare i dati. Supponiamo che si verifichi una situazione in cui sono necessarie pagine pulite per eseguire una query e sono già presenti nei shared buffers. Il sistema PostgreSQL backend le usa direttamente senza doverle pulire. Ma se tali pagine non sono disponibili, il backend sospende il lavoro e inizia a cercare pagine da scrivere su disco per utilizzarle — il che influisce negativamente sui tempi di esecuzione della query attualmente in corso. Se noti che hai il parametro maxwritten_clean alto, significa che il background writer non sta svolgendo correttamente il proprio compito e devi aumentare i parametri bgwriter_lru_maxpages, affinché possa compiere più lavoro in un ciclo, pulendo più pagine.
Un altro indicatore molto utile è buffers_backend_fsync. I backend non eseguono fsync, perché è lento. Lo passano al checkpointer nel layer I/O superiore. Il checkpointer ha la sua coda e periodicamente gestisce l’fsync, sincronizzando le pagine in memoria con i file su disco. Se la coda è grande e piena al checkpointer, il backend è costretto a effettuare fsync da solo, il che rallenta il lavoro del backend., cioè il cliente riceverà una risposta più tardi di quanto potrebbe. Se notate che questo valore è maggiore di zero, è già un problema e è necessario prestare attenzione alle impostazioni del background writer e anche valutare le prestazioni del sottosistema di archiviazione.
Attenzione: _Il seguente testo descrive le rappresentazioni statistiche relative alla replica. La maggior parte dei nomi delle rappresentazioni e delle funzioni è stata rinominata in Postgres 10. Il succo dei rinominamenti consisteva nella sostituzione xlog con wal e location con lsn nei nomi delle funzioni/rappresentazioni, ecc. Un esempio particolare è la funzione pg_xlog_location_diff() che è stata rinominata in pg_wal_lsn_diff()._
Qui abbiamo anche molte cose. Ma ci serviranno solo i punti relativi alla location.
Se vediamo che tutti i valori sono uguali, allora questa è la situazione ideale e la replica non è in ritardo rispetto al master.
Questa posizione esadecimale è la posizione nel log delle transazioni. Aumenta costantemente se c'è qualche attività nel database: inserimenti, cancellazioni, ecc.
quanti byte di xlog sono registrati
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
ritardo di replicazione in byte
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
ritardo di replicazione in secondi
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Se questi valori sono diversi, significa che c'è un qualche ritardo. Il ritardo è la differenza tra il replica e il master, cioè i dati sono diversi tra i server.
Ci sono tre motivi per il ritardo:
- Il sottosistema di archiviazione non gestisce correttamente la registrazione della sincronizzazione dei file.
- Ci possono essere errori di rete, oppure un sovraccarico della rete, quando i dati non riescono a raggiungere la replica e non possono essere riprodotti.
- E infine il processore. Il processore è un caso molto raro. L'ho visto solo due o tre volte, ma può succedere anche questo.
Ecco tre query che ci permettono di utilizzare le statistiche. Possiamo valutare quanti dati sono registrati nel log delle transazioni. C'è una funzione chiamata pg_xlog_location_diff e possiamo valutare il ritardo di replicazione in byte e secondi. Utilizziamo anche un valore da questa vista (VIEWs) per farlo.
Nota: _Invece della funzione pg_xlog_locationdiff() possiamo utilizzare l'operatore di sottrazione e sottrarre una location dall'altra. È conveniente.
C'è una cosa da considerare riguardo al ritardo, espresso in secondi. Se non ci sono attività sul master, e la transazione è stata effettuata circa 15 minuti fa senza ulteriori attività, se controlliamo questo ritardo sulla replica, vedremo un ritardo di 15 minuti. È importante tenere a mente questo aspetto. Questo può risultare confuso quando si osserva il ritardo.
Pg_stat_all_tables è un'altra vista utile. Mostra le statistiche sulle tabelle. Quando abbiamo tabelle nel database e ci sono delle attività o delle operazioni, possiamo ottenere queste informazioni da questa vista.
select
relname,
pg_size_pretty(pg_relation_size(relname::regclass)) as size,
seq_scan, seq_tup_read,
seq_scan / seq_tup_read as seq_tup_avg
from pg_stat_user_tables
where seq_tup_read > 0 order by 3,4 desc limit 5;La prima cosa che possiamo esaminare sono le scansioni sequenziali sulla tabella. Il numero stesso dopo questi passaggi non suggerisce necessariamente che ci sia un problema e non indica che dobbiamo già intraprendere azioni.
Tuttavia, c'è un'altra metrica: seq_tup_read. Si tratta del numero di righe restituite da una scansione sequenziale. Se il numero medio supera 1.000, 10.000, 50.000 o 100.000, significa che potrebbe essere necessario costruire un indice per le interrogazioni, oppure ottimizzare le query che utilizzano tali scansioni sequenziali.
Un semplice esempio: supponiamo che ci sia una query con un grande OFFSET e LIMIT. Ad esempio, si scansionano 100.000 righe in una tabella e poi vengono prese 50.000 righe necessarie, mentre le righe precedentemente scansionate vengono scartate. Anche questo è un caso sfavorevole. È necessario ottimizzare tali query. Qui c'è una semplice query SQL che può essere utilizzata per analizzare e valutare i risultati ottenuti.
select
relname,
pg_size_pretty(pg_total_relation_size(relname::regclass)) as
full_size,
pg_size_pretty(pg_relation_size(relname::regclass)) as
table_size,
pg_size_pretty(pg_total_relation_size(relname::regclass) -
pg_relation_size(relname::regclass)) as index_size
from pg_stat_user_tables
order by pg_total_relation_size(relname::regclass) desc limit 10;Le dimensioni delle tabelle possono essere ottenute anche tramite questa tabella e con funzioni aggiuntive. pg_total_relation_size(), pg_relation_size().
In generale, ci sono metacomandi. dt e di, che possono essere utilizzati in PSQL e anche per visualizzare le dimensioni delle tabelle e degli indici.
Tuttavia, l'uso delle funzioni ci aiuta a vedere le dimensioni delle tabelle tenendo conto anche degli indici, oppure senza tenerne conto, e a fare delle valutazioni basate sulla crescita del database, ossia come esso cresce, con quale intensità, e a fare delle conclusioni sull'ottimizzazione delle dimensioni.
Attività di scrittura. Cos'è una scrittura? Vediamo l'operazione UPDATE – operazione di aggiornamento delle righe nella tabella. In sostanza, l'update è due operazioni (o anche di più). Si tratta di inserire una nuova versione della riga e contrassegnare la vecchia versione della riga come obsoleta. Successivamente, verrà il vacuum automatico che pulirà queste versioni obsolete delle righe e contrassegnerà quel posto come disponibile per un riutilizzo.
Inoltre, l'update non riguarda solo l'aggiornamento della tabella. Riguarda anche l'aggiornamento degli indici. Se hai molti indici sulla tabella, durante l'update tutti gli indici che coinvolgono i campi aggiornati nella query dovranno essere aggiornati anche essi. In questi indici ci saranno anche versioni obsolete delle righe che dovranno essere pulite.
seleziona
s.relname,
pg_size_pretty(pg_relation_size(relid)),
coalesce(n_tup_ins,0) + 2 * coalesce(n_tup_upd,0) -
coalesce(n_tup_hot_upd,0) + coalesce(n_tup_del,0) AS total_writes,
(coalesce(n_tup_hot_upd,0)::float * 100 / (case when n_tup_upd > 0
then n_tup_upd else 1 end)::float)::numeric(10,2) AS hot_rate,
(select v[1] FROM regexp_matches(reloptions::text,E'fillfactor=(\d+)') as
r(v) limit 1) AS fillfactor
from pg_stat_all_tables s
join pg_class c ON c.oid=relid
order by total_writes desc limit 50;E grazie al suo design, l'UPDATE è un'operazione pesante. Ma può essere semplificata. Ci sono hot updates. Sono stati introdotti in PostgreSQL versione 8.3. E cosa sono? Si tratta di un aggiornamento leggero che non provoca la ricostruzione degli indici. Vale a dire, abbiamo aggiornato un record, ma solo il record nella pagina (appartenente alla tabella) è stato aggiornato, mentre gli indici continuano a puntare allo stesso record nella pagina. C'è una logica interessante quando arriva il vacuum, che ricostruisce queste catene hot e tutto continua a funzionare senza aggiornamenti degli indici, avvenendo con un minore consumo di risorse.
E quando hai n_tup_hot_upd elevato, è molto positivo. Questo significa che gli aggiornamenti leggeri prevalgono e il costo in termini di risorse è inferiore, quindi tutto va bene.
ALTER TABLE table_name SET (fillfactor = 70);Come aumentare il numero di hot update?. Possiamo usare fillfactor. Definisce la dimensione dello spazio libero riservato durante il riempimento della pagina in una tabella tramite INSERT. Quando nella tabella vengono eseguiti degli insert, riempiono completamente la pagina, senza lasciare spazio vuoto. Poi viene riservata una nuova pagina. Di nuovo i dati vengono riempiti. Questo è il comportamento predefinito, con un fillfactor = 100%.
Possiamo impostare il fillfactor al 70%. Cioè, durante gli insert si è riservata una nuova pagina, ma è stato riempito solo il 70% della pagina. E il 30% rimane per riserva. Quando sarà necessario effettuare un aggiornamento, è molto probabile che avvenga nella stessa pagina, e la nuova versione della riga verrà posizionata nella stessa pagina. E verrà eseguito un hot_update. In questo modo si semplifica la scrittura nelle tabelle.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Coda dell'autovacuum. L'autovacuum è un sottosistema per il quale ci sono poche statistiche in PostgreSQL. Possiamo vedere nelle tabelle di pg_stat_activity quanto durano i nostri vacuum attualmente. Tuttavia, è molto difficile capire quante tabelle sono in coda in questo momento.
Nota: A partire dalla versione Postgres 10, la situazione per il monitoraggio dell'autovacuum è notevolmente migliorata — è stata introdotta la vista pg_stat_progressvacuum, che semplifica notevolmente la questione del monitoraggio dell'autovacuum.
Possiamo utilizzare una query semplificata come questa. Possiamo anche controllare quando sarà eseguito il vacuum. Ma come e quando deve partire l'autovacuum? Queste sono le versioni obsolete delle righe di cui parlavo prima. È avvenuto un aggiornamento, è stata inserita una nuova versione della riga. È stata generata una versione obsoleta della riga. Nella tabella pg_stat_user_tables c'è un parametro n_dead_tup. Indica il numero di righe "morte". E non appena il numero delle righe morte supera una soglia determinata, l'autovacuum verrà attivato per la tabella.
E come viene calcolata questa soglia? È una percentuale specifica del numero totale di righe nella tabella. C'è un parametro autovacuum_vacuum_scale_factor. Questo determina la percentuale. Supponiamo un 10% + una soglia base aggiuntiva di 50 righe. Qual è il risultato? Quando il numero di righe morte supera il "10% + 50" rispetto a tutte le righe della tabella, allora impostiamo la tabella su autovacuum.
select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));Tuttavia, c'è un aspetto da considerare. Le soglie di base per i parametri av_base_thresh e av_scale_factor possono essere assegnate individualmente. Di conseguenza, la soglia sarà specifica per la tabella e non globale. Pertanto, per calcolarla, è necessario utilizzare tecniche e trucchi. E se sei interessato, puoi dare un'occhiata all'esperienza dei nostri colleghi di Avito (il link nella diapositiva è non valido ed è stato aggiornato nel testo).
Hanno sviluppato un , che tiene conto di queste cose. C'è un documento di due pagine. Tuttavia, calcola in modo corretto e permette di valutare in modo abbastanza efficace dove abbiamo bisogno di più vacuum per le tabelle e dove ne serve meno.
Cosa possiamo fare al riguardo? Se abbiamo una lunga coda e l'autovacuum non riesce a gestirla, possiamo aumentare il numero di lavoratori del vacuum, o semplicemente rendere il vacuum più aggressivo, in modo che si attivi prima e gestisca la tabella in piccoli pezzi. Così facendo, la coda si ridurrà. — La cosa principale qui è monitorare il carico sui dischi, poiché il vuoto non è gratuito, anche se con l'arrivo dei dispositivi SSD/NVMe il problema è diventato meno evidente.
Pg_stat_all_indexes fornisce statistiche sugli indici. È un'informazione limitata, e possiamo utilizzarla per ottenere dettagli sull'uso degli indici. Ad esempio, possiamo determinare quali indici sono superflui.
Come ho già detto, l'update non riguarda solo l'aggiornamento delle tabelle, ma anche quello degli indici. Di conseguenza, se abbiamo molti indici sulla tabella, durante l'aggiornamento delle righe nella tabella, anche gli indici dei campi indicizzati devono essere aggiornati, e se abbiamo indici non utilizzati, sui quali non ci sono scansioni, essi rimangono come un peso morto. È necessario liberarsene. Per questo, abbiamo bisogno del campo idx_scan. Basta guardare il numero di scansioni degli indici. Se un indice non ha scansioni per un periodo relativamente lungo di conservazione delle statistiche (non meno di 2-3 settimane), probabilmente si tratta di indici non validi, e dobbiamo eliminarli.
Nota: Quando si cercano indici non utilizzati in cluster di replica streaming, è necessario controllare tutti i nodi del cluster, poiché le statistiche non sono globali e se un indice non è utilizzato sul master, potrebbe essere utilizzato sulle repliche (se c'è un carico lì).
Due link:
Questi sono esempi di query più avanzate su come cercare indici non utilizzati.
Il secondo link è una query piuttosto interessante. Contiene una logica molto non banale. Lo consiglio per una lettura.
Cosa altro vale la pena riassumere sugli indici?
Gli indici non utilizzati sono dannosi.
Occupano spazio.
Rallentano le operazioni di aggiornamento.
Creano lavoro extra per il vacuum.
Se rimuoviamo gli indici non utilizzati, otterremo solo vantaggi per il database.
La seguente vista è pg_stat_activity. È l'analogo dello strumento ps, ma in PostgreSQL. Se pscon 'top' si osservano i processi nel sistema operativo, pg_stat_activity qui si mostra l'attività all'interno di PostgreSQL.
Cosa possiamo estrarre di utile da lì?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Possiamo osservare l'attività generale e cosa sta succedendo nel database. Possiamo eseguire un nuovo deployment. Lì tutto è esploso, non vengono accettate nuove connessioni e ci sono errori nel'applicazione.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Possiamo eseguire questa query e vedere la percentuale totale di connessioni rispetto al limite massimo di connessioni e controllare chi occupa la maggior parte delle connessioni. In questo caso, vediamo che l'utente cron_role ha aperto 508 connessioni. E qualcosa è successo con lui. Dobbiamo indagare e vedere. È molto possibile che si tratti di un numero anomalo di connessioni.
Se abbiamo un carico OLTP, le query devono essere eseguite rapidamente, molto rapidamente e non ci devono essere richieste lunghe. Tuttavia, se ci sono richieste lunghe, a breve termine non è un problema, ma a lungo termine le richieste lunghe danneggiano il database, aumentando l'effetto di bloat delle tabelle, quando si verifica la frammentazione delle tabelle. È necessario eliminare sia il bloat che le richieste lunghe.
seleziona
client_addr, username, datname,
clock_timestamp() - xact_start as xact_age,
clock_timestamp() - query_start as query_age,
query
from pg_stat_activity order by xact_start, query_start;Nota: con questa query possiamo identificare le transazioni e le query lunghe. Utilizziamo la funzione clock_timestamp() per determinare il tempo di esecuzione. Le query lunghe che abbiamo trovato possiamo memorizzarle, eseguire explain, esaminare i piani e ottimizzare in qualche modo. Le attuali query lunghe le chiudiamo e continuiamo a vivere.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Le transazioni problematiche sono quelle in stato di idle in transaction e idle in transaction (aborted).
Cosa significa? Le transazioni possono avere diversi stati. E uno di questi stati può essere assunto in qualsiasi momento. Per determinare gli stati, c'è il campo state in questa vista. E lo utilizziamo per determinare lo stato.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';E, come ho già detto sopra, questi due stati idle in transaction e idle in transaction (aborted) – è un problema. Cos'è? Quando un'applicazione apre una transazione, esegue alcune operazioni e poi se ne va. La transazione rimane aperta. È sospesa, non accade nulla, occupa la connessione, blocca le righe modificate e potenzialmente aumenta il bloat di altre tabelle, a causa dell'architettura del motore di transazione di Postgres. È opportuno terminare queste transazioni, poiché sono dannose in qualsiasi caso.
Se notate che ce ne sono più di 5-10-20 nel vostro database, è il momento di preoccuparsi e iniziare ad agire.
Qui usiamo anche per il calcolo del tempo clock_timestamp(). Terminando le transazioni, ottimizziamo l'applicazione.
Come già accennato, i blocchi avvengono quando due o più transazioni contendono una o più risorse. Per questo abbiamo il campo waiting con un valore booleano. true o false.
True – significa che il processo è in attesa, è necessario agire. Quando il processo è in attesa, anche il cliente che ha avviato questo processo sta aspettando. Il cliente nel browser è lì e sta aspettando.
Attenzione: _A partire dalla versione Postgres 9.6, il campo waiting è stato rimosso e sostituito con due campi più informativi wait_event_type e wait_event._
Cosa fare? Se vedi true per lungo tempo, significa che è necessario liberarsi di tali richieste. Semplicemente interrompiamo queste transazioni. Scriviamo agli sviluppatori che è necessario ottimizzare il sistema per evitare competizioni per le risorse. Successivamente, gli sviluppatori ottimizzano l'applicazione per prevenire tali situazioni.
E un caso limite, ma potenzialmente non fatale, è l'insorgere di deadlock. Due transazioni aggiornano due risorse e poi cercano di accedervi di nuovo, ma a risorse opposte. In questo caso, PostgreSQL termina una delle transazioni per consentire all'altra di continuare. Si tratta di una situazione di stallo che non si risolve da sola. Pertanto, PostgreSQL è costretto a prendere misure drastiche.
Ecco due query che permettono di monitorare i blocchi. Utilizziamo la vista pg_locks, che consente di monitorare blocchi pesanti.
La prima link è il testo della query. È piuttosto lunga.
Il secondo link è un articolo sui locks. È utile leggerlo, è molto interessante.
Quindi, cosa vediamo? Vediamo due query. La transazione con ALTER TABLE – è una transazione bloccante. È stata avviata ma non completata, e l'applicazione che ha avviato questa transazione sta facendo altro da qualche parte. E la seconda richiesta – update. Sta aspettando che alter table termini per poter continuare il suo lavoro.
Così possiamo scoprire chi ha bloccato chi, tenere il controllo e possiamo approfondire la questione.
Il modulo successivo è pg_stat_statements. Come ho già detto, si tratta di un modulo. Per utilizzarlo, è necessario caricare la sua libreria nella configurazione, riavviare PostgreSQL, installare il modulo (con un solo comando) e poi avremo una nuova vista.
Tempo medio di query in millisecondi
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Le query più attive (in shared_buffers)
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;Cosa possiamo ricavarne? Se parliamo di cose semplici, possiamo ottenere il tempo medio di esecuzione della query. Se il tempo aumenta, significa che PostgreSQL sta rispondendo lentamente e dobbiamo prendere delle misure.
Possiamo verificare le transazioni più attive in scrittura nel database, quelle che modificano i dati negli shared buffers. Possiamo vedere chi sta aggiornando o eliminando dati.
Possiamo semplicemente visualizzare diverse statistiche per queste richieste.
Noi pg_stat_statements utilizziamo per la creazione di report. Azzeriamo le statistiche una volta al giorno. Le accumuliamo. Prima di ripristinare le statistiche la prossima volta, creiamo un report. Ecco il link al report. Puoi visualizzarlo.
Cosa facciamo? Contiamo le statistiche totali per tutte le richieste. Successivamente, calcoliamo il contributo individuale di ciascuna richiesta a queste statistiche totali.
Cosa possiamo visualizzare? Possiamo vedere il tempo totale di esecuzione di tutte le richieste di un determinato tipo rispetto a tutte le altre richieste. Possiamo osservare l'uso delle risorse della CPU e le operazioni di input/output rispetto all'immagine complessiva. E quindi ottimizzare queste richieste. Creiamo una classifica delle richieste in base a questo report, e otteniamo così spunti per cosa ottimizzare.
Cosa è rimasto fuori? Ci sono ancora alcune visualizzazioni che non ho considerato, perché il tempo è limitato.
Sì pgstattuple – è anche un modulo aggiuntivo del pacchetto standard contribs. Permette di valutare bloat tabella, cioè la frammentazione della tabella. E se la frammentazione è elevata, è necessario rimuoverla, utilizzando strumenti diversi. E la funzione pgstattuple richiede molto tempo. E più tabelle ci sono, più a lungo funzionerà.
Il prossimo contributore è pg_buffercache. Permette di esaminare i shared buffers: quanto intensamente e per quali tabelle vengono utilizzate le pagine del buffer. E consente semplicemente di dare un'occhiata ai shared buffers e valutare cosa sta succedendo.
Il modulo successivo è pgfincore. Permette di eseguire operazioni a basso livello sulle tabelle tramite la chiamata di sistema mincore(), cioè permette di caricare una tabella nei shared buffers oppure di scaricarla. Consente oltre a ciò di ispezionare la cache delle pagine del sistema operativo, ossia in quale misura la nostra tabella è occupata nella page cache, nei shared buffers e consente semplicemente di valutare il carico della tabella.
Il prossimo modulo è pg_stat_kcache. Utilizza anch'esso la chiamata di sistema getrusage(). E lo esegue prima e dopo l'esecuzione della query. Nelle statistiche ottenute, permette di valutare quanto il nostro query abbia speso per l'input/output su disco, ovvero operazioni con il file system, e monitora l'uso della CPU. Tuttavia, il modulo è giovane (ehm-ehm) e richiede PostgreSQL 9.4 e pg_stat_statements, di cui ho parlato in precedenza.
Essere in grado di utilizzare le statistiche è utile. Non hai bisogno di programmi di terze parti. Puoi dare un'occhiata tu stesso, vedere, fare qualcosa, eseguire.
Utilizzare le statistiche non è difficile, è un normale SQL. Hai raccolto la query, l'hai composta, inviata, hai guardato.
Le statistiche aiutano a rispondere alle domande. Se hai domande, ti rivolgi alle statistiche - guardi, trai conclusioni, analizzi i risultati.
E sperimenta. Ci sono molte query, molti dati. C'è sempre la possibilità di ottimizzare una query esistente. Puoi creare una tua versione della query, che si adatta meglio a te rispetto all'originale, e usarla.
Link
Collegamenti utili, che sono stati menzionati nell'articolo, sono stati inclusi nella relazione.
L'autore scrive ancora
(eng)
Il Collezionista di Statistiche
Funzioni di Amministrazione di Sistema
Moduli Contrib
Utilità SQL e esempi di codice SQL
Grazie a tutti per l'attenzione!
Fonte: habr.com
