Decodifica della relazione del 2015 di Alexey Lesovsky "Deep dive into PostgreSQL internal statistics"
Avviso dell'autore della relazione: Voglio osservare che questa relazione è datata novembre 2015 — sono passati più di 4 anni e molto tempo. La versione considerata nella relazione, 9.4, non è più supportata. Negli ultimi 4 anni sono stati rilasciati 5 nuove versioni in cui sono state introdotte molte novità, miglioramenti e modifiche relative alle statistiche, e parte del materiale è obsoleta e non più attuale. Durante la revisione ho cercato di annotare questi punti per non fuorviarti, lettore. Non ho riscritto questi passaggi, ce ne sono davvero troppi e alla fine risulterebbe una relazione completamente diversa.
Il DBMS PostgreSQL è un meccanismo enorme, composto da numerosi sottosistemi, il cui funzionamento coordinato influisce direttamente sulle prestazioni del DBMS. Durante l'uso, viene raccolta statistica e informazione sul funzionamento dei componenti, il che consente di valutare l'efficacia di PostgreSQL e di adottare misure per migliorarne le prestazioni. Tuttavia, ci sono molte informazioni e sono presentate in una forma piuttosto semplificata. L'elaborazione e l'interpretazione di queste informazioni non è un compito banale, e il "zoo" di strumenti e utility può facilmente confondere anche un DBA esperto.
Buongiorno! Mi chiamo Alexey. Come ha detto Ilya, parlerò delle statistiche di PostgreSQL.
Statistiche di attività di PostgreSQL. PostgreSQL ha due tipi di statistiche. La statistica di attività, di cui parleremo. E la statistica del pianificatore sulla distribuzione dei dati. Parlerò specificamente della statistica di attività di PostgreSQL, che ci consente di giudicare le prestazioni e di cercare di migliorarle.
Spiegherò come utilizzare efficacemente le statistiche per risolvere i diversi problemi che si possono presentare o potrebbero sorgere.
Cosa non verrà trattato nella relazione? Non parlerò delle statistiche del pianificatore, poiché questo è un argomento separato per un'altra relazione su come i dati vengono memorizzati nel database e su come il pianificatore delle query acquisisce una comprensione delle caratteristiche qualitative e quantitative di questi dati.
E non ci saranno recensioni sugli strumenti, non confronterò un prodotto con un altro. Non ci sarà pubblicità. Mettiamo da parte questo.
Voglio mostrarvi che utilizzare le statistiche è utile. È necessario. Usarle non è spaventoso. Avremo solo bisogno di SQL di base e conoscenze fondamentali su SQL.
E parleremo di quale statistica scegliere per risolvere i problemi.
Se guardiamo a PostgreSQL e lanciamo un comando nel sistema operativo per visualizzare i processi, vedremo una "scatola nera". Vedremo alcuni processi che fanno qualcosa e, in base ai nomi, possiamo avere un'idea di cosa stiano facendo. Ma, in sostanza, è una scatola nera, non possiamo guardare all'interno.
Possiamo controllare il carico della CPU in top, possiamo osservare l'utilizzo della memoria tramite alcune utilità di sistema, ma non possiamo sbirciare all'interno di PostgreSQL. Per questo abbiamo bisogno di altri strumenti.
E continuando, vi racconterò dove viene speso il tempo. Se immaginiamo PostgreSQL come uno schema, sarà possibile rispondere a dove viene speso il tempo. Ci sono due aspetti: il trattamento delle richieste dei clienti dalle applicazioni e i compiti in background che PostgreSQL esegue per mantenere la sua operatività.
Se iniziamo a considerare l'angolo in alto a sinistra, possiamo seguire come vengono gestite le richieste dei clienti. La richiesta arriva dall'applicazione e, per il funzionamento successivo, viene aperta una sessione cliente. La richiesta viene inviata allo scheduler. Lo scheduler costruisce un piano di richiesta. Lo invia ulteriormente per l'esecuzione. Si verifica un qualche input-output in blocco dei dati legato a tabelle e indici. I dati necessari vengono letti dai dischi nella memoria in una zona speciale chiamata "shared buffers". I risultati della richiesta, se si tratta di aggiornamenti o eliminazioni, vengono registrati nel registro delle transazioni nel WAL. Alcune informazioni statistiche vengono inviate al log o al raccoglitore di statistiche. E il risultato della richiesta viene restituito al cliente. Dopo di che, il cliente può ripetere tutto da capo con una nuova richiesta.
Cosa abbiamo 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 verranno trattati anche nella relazione: autovacuum, checkpointer, processi legati alla replica, background writer. Ognuno di essi verrà discusso man mano che procede la relazione.
Quali problemi ci sono con le statistiche?
- Ci sono molte informazioni. PostgreSQL 9.4 fornisce 109 metriche per visualizzare i dati statistici. Tuttavia, se nel database sono memorizzate molte tabelle, schemi e database, tutte queste metriche devono essere moltiplicate per il numero corrispondente di tabelle e database. Cioè, le informazioni diventano ancora di più. E è molto facile annegare in esse.
- Un altro problema è che la statistica è presentata tramite contatori. Se guardiamo a queste statistiche, vediamo contatori che aumentano costantemente. Se è passato molto tempo da quando sono state azzerate le statistiche, vedremo valori miliardari. E non ci dicono nulla.
- C'è assenza di storia. Se hai avuto un guasto, qualcosa è andato storto 15-30 minuti fa, non potrai utilizzare 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 nulla del genere. Forniscono semplicemente statistiche nel database. Usale, fai richieste, fai quello che vuoi.
- Poiché non c'è uno strumento integrato in PostgreSQL, questo è alla base di un altro problema. Ci sono molti strumenti di terze parti. Ogni azienda con più o meno capacità tenta di scrivere il proprio programma. E alla fine, nella community ci sono molti strumenti da usare per lavorare con le statistiche. E in alcuni strumenti ci sono certe funzioni, in altri strumenti possono mancare, oppure ci sono nuove funzionalità. E si verifica la situazione in cui è necessario utilizzare due, tre, quattro strumenti, che si sovrappongono e hanno funzioni diverse. Questo è molto sgradito.
Cosa ne deriva? È importante saper prendere la statistica direttamente, per non dipendere da programmi, oppure migliorare in qualche modo questi programmi: aggiungere funzioni per ottenere i propri vantaggi.
Servono conoscenze di base di SQL. Per ottenere dei dati dalle statistiche, è necessario comporre delle query SQL, cioè è necessario sapere come si formano i select e i join.
La statistica ci offre diverse cose. Possono essere suddivise in categorie.
- La prima categoria comprende eventi che si verificano nel database. È quando nel database si verifica un evento: una richiesta, un accesso a una tabella, un autovacuum, commit, e tutti questi sono eventi. I contatori corrispondenti a questi eventi vengono incrementati. Possiamo tracciare questi eventi.
- La seconda categoria riguarda le proprietà degli oggetti, come le tabelle e i database. Questi hanno delle proprietà. È la dimensione delle tabelle. Possiamo tracciare la crescita delle tabelle e degli indici. Possiamo osservare le variazioni nel tempo.
- E la terza categoria consiste nel tempo impiegato per un evento. Una richiesta è un evento. Ha una sua misura di durata. Qui inizia, qui finisce. Possiamo tracciare questo. Oppure il tempo di lettura di un blocco dal disco o di scrittura. Anche queste cose vengono monitorate.
Le fonti delle statistiche sono rappresentate come segue:
- Nella memoria condivisa (shared buffers) c'è un segmento per memorizzare i dati statistici, ci sono anche quei contatori che vengono continuamente incrementati quando si verificano determinati eventi, o sorgono delle situazioni nel funzionamento del database.
- Tutti questi contatori non sono accessibili all'utente e nemmeno all'amministratore. Si tratta di elementi a basso livello. Per accedervi, PostgreSQL fornisce un'interfaccia sotto forma di funzioni SQL. Possiamo effettuare selezioni utilizzando queste funzioni e ottenere delle metriche (o un insieme di metriche).
- Tuttavia, utilizzare queste funzioni non è sempre comodo, perciò le funzioni sono alla base delle viste (VIEWs). Queste sono tabelle virtuali che forniscono statistiche su un determinato sotto-sistema o su un certo insieme di eventi nel database.
- Queste viste incorporate (VIEWs) sono l'interfaccia principale per l'utente per lavorare con le statistiche. Sono disponibili di default senza alcuna configurazione aggiuntiva, puoi iniziare a utilizzarle subito, consultare e prelevare informazioni da esse. Ci sono anche contrib. I contrib sono ufficiali. Puoi installare il pacchetto postgresql-contrib (ad esempio, postgresql94-contrib), caricare il modulo necessario nella configurazione, specificare i parametri, riavviare PostgreSQL e cominciare a usarlo. (Nota. A seconda della distribuzione, nelle ultime versioni il pacchetto contrib è parte del pacchetto principale.).
- Esistono anche contrib non ufficiali. Non sono inclusi nella distribuzione standard di PostgreSQL. Devono essere compilati o installati come librerie. Le opzioni possono variare ampiamente, a seconda di cosa ha inventato lo sviluppatore di quel contrib non ufficiale.
In questa slide sono presentate tutte le visualizzazioni (VIEWs) e parte delle funzioni disponibili in PostgreSQL 9.4. Come possiamo vedere, ce ne sono davvero molte. È abbastanza facile confondersi se ci si imbatte in questo per la prima volta.
Tuttavia, se prendiamo l'immagine precedente Come viene speso il tempo su PostgreSQL e la confrontiamo con questo elenco, otteniamo un'immagine simile. Ogni visualizzazione (VIEWs) o ogni funzione può essere utilizzata per vari scopi per ottenere statistiche pertinenti mentre PostgreSQL è in esecuzione. Possiamo già ottenere alcune informazioni sul funzionamento del sottosistema.
La prima cosa che esamineremo è pg_stat_database. Come possiamo vedere, è una visualizzazione. Contiene molte informazioni. Informazioni di vario genere. E fornisce conoscenze molto utili su cosa sta accadendo nel database.
Cosa possiamo trarne di utile? Iniziamo con le cose più semplici.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;La prima cosa che possiamo controllare è la percentuale di hit nel cache. La percentuale di hit nel cache è un metrica utile. Permette di valutare quanto dato viene prelevato dalla cache dei buffer condivisi e quanto invece viene letto dal disco.
Chiaramente, più elevata è la percentuale di hit nel cache, meglio è. Valutiamo questa metrica come percentuale. E, ad esempio, se il rapporto percentuale di questi hit nel cache è superiore al 90%, va bene. Se scende sotto il 90%, significa che non abbiamo abbastanza memoria per mantenere la "testa" dei dati caldi in memoria. E per utilizzare questi dati, PostgreSQL è costretto a fare riferimento al disco, e ciò è più lento rispetto alla lettura dalla memoria. Bisogna già pensare ad aumentare la memoria: o aumentare i buffer condivisi o potenziare la memoria RAM.
select
datname,
(xact_commit*100)/(xact_commit+xact_rollback) as c_ratio,
deadlocks, conflicts,
temp_file, pg_size_pretty(temp_bytes) as temp_size
from pg_stat_database;Cosa possiamo ulteriormente ottenere da questa visualizzazione? Possiamo osservare 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 utilizzare questa query. Questo SQL è abbastanza semplice. E possiamo vedere questi dati da noi.
E qui subito ci sono i valori soglia. Stiamo osservando il rapporto tra commit e rollback. I commit sono conferme di transazione riuscite. I rollback sono il ripristino, cioè la transazione ha fatto un certo lavoro, ha sollecitato il database, ha calcolato qualcosa, ma poi si è verificato un errore e i risultati della transazione vengono annullati. Cioè, un numero crescente di rollback è una cosa negativa. E bisognerebbe in qualche modo evitarli e correggere il codice per prevenire tale situazione.
I conflitti (conflicts) sono legati alla replica. E anche questi vanno evitati. Se hai delle query che vengono eseguite sulla replica e si verificano conflitti, è necessario analizzare questi conflitti, vedere cosa succede. I dettagli possono essere trovati nei log. E risolvere le situazioni conflittuali affinché le query dell'applicazione funzionino senza errori.
I deadlock sono anch'essi una situazione negativa. Quando le query competono per le risorse, una query accede a una risorsa e la blocca, la seconda query accede a una seconda risorsa e blocca anche quella, e poi entrambe le query cercano di accedere alle risorse l'una dell'altra e restano bloccate in attesa che l'altra rilasci la blocking. Questa è anche una situazione problematica. Devono essere risolti a livello di riscrittura delle applicazioni e serializzazione dell'accesso alle risorse. E se vedi che i tuoi deadlock aumentano costantemente, devi osservare i dettagli nei log, analizzare le situazioni emerse e capire qual è il problema.
I file temporanei (temp_files) sono anche un problema. Quando a una richiesta dell'utente manca memoria per allocare dati temporanei, viene creato un file su disco. E tutte le operazioni che potrebbe eseguire nel buffer temporaneo in memoria iniziano a essere eseguite su disco. Questo è lento. Questo aumenta il tempo di esecuzione della query. E il client che ha inviato una richiesta a PostgreSQL riceverà una risposta un po' più tardi. Se tutte queste operazioni vengono eseguite in memoria, Postgres risponderà molto più velocemente e il cliente dovrà aspettare meno.
Pg_stat_bgwriter è una vista che descrive il lavoro di due sottosistemi in background di PostgreSQL: sono checkpointer e background writer.
Iniziamo con l'analizzare i checkpoint, ovvero checkpoints. Cosa sono i checkpoint? Un checkpoint è una posizione nel log delle transazioni che comunica che tutte le modifiche ai dati registrati nel log sono state sincronizzate con successo con i dati sul disco. Il processo, a seconda del carico di lavoro e delle impostazioni, può essere lungo e consiste principalmente nella sincronizzazione delle pagine sporche nei buffer condivisi con i file di dati sul disco. A cosa serve? Se PostgreSQL accedesse continuamente al disco per recuperare e registrare dati a ogni richiesta, sarebbe lento. Pertanto, PostgreSQL ha un segmento di memoria, la cui dimensione dipende dalle impostazioni di configurazione. Postgres colloca in questa memoria i dati operativi per un successivo elaborazione o per rispondere alle richieste. In caso di richieste di modifica dei dati, questi vengono modificati. E otteniamo due versioni dei dati. Una è in memoria, l'altra è sul disco. Periodicamente, questi dati devono essere sincronizzati. Dobbiamo sincronizzare ciò che è stato modificato in memoria sul disco. A questo servono i checkpoint.
Il checkpoint attraversa i buffer condivisi, contrassegna le pagine sporche che sono necessarie per il checkpoint. Poi esegue un secondo passaggio attraverso i buffer condivisi. E le pagine contrassegnate per il checkpoint vengono già sincronizzate. In questo modo avviene la sincronizzazione dei dati con il disco.
Ci sono due tipi di checkpoint. Uno è il checkpoint eseguito con timeout. Questo checkpoint è utile e buono – checkpoint_timed. E ci sono checkpoint su richiesta – checkpoint required. Questo tipo di checkpoint si verifica quando abbiamo una grande quantità di registrazioni di dati. Abbiamo scritto molti log di transazione. E PostgreSQL ritiene di doverli sincronizzare il prima possibile, effettuando un checkpoint e continuando.
E se hai guardato le statistiche pg_stat_bgwriter e hai visto che hai checkpoint_req di gran lunga superiori a checkpoint_timed, allora è un problema. Perché è un problema? Significa che PostgreSQL si trova in una situazione di stress costante, dove deve scrivere dati sul disco. Il checkpoint con timeout è meno stressante e viene eseguito secondo un programma interno e si estende nel tempo. PostgreSQL ha la possibilità di fare pause nel lavoro e non sovraccaricare il sistema di archiviazione del disco. Questo è vantaggioso per PostgreSQL. E le richieste eseguite durante il checkpoint non subiranno stress a causa dell'occupazione del sistema di archiviazione.
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 9.4 trattata nella relazione non è più attuale. Nelle versioni moderne di PostgreSQL il parametro checkpoint_segments è sostituito dai parametri min_wal_size e max_wal_size.
Il prossimo sottosistema è il writer di fondo — background writer. Cosa fa? Lavora continuamente in un ciclo infinito. Scorre le pagine nei shared buffers e scrive su disco quelle sporche che trova. In questo modo aiuta il checkpointer a svolgere meno lavoro durante l'esecuzione dei checkpoint.
A cosa serve anche? Garantisce la necessità di pagine pulite negli shared buffers se dovessero essere necessarie (in grande quantità e tutte insieme) per allocare dati. Supponiamo che si verifichi una situazione in cui per eseguire una query siano necessarie pagine pulite e queste sono già negli shared buffers. PostgreSQL backend le prende e le utilizza, non deve pulire nulla da solo. Ma se tali pagine non ci sono, il backend interrompe il lavoro e inizia a cercare pagine da scrivere su disco e da prendere per le proprie esigenze — il che influisce negativamente sul tempo della query in esecuzione. Se notate che il vostro parametro maxwritten_clean è alto, significa che il background writer non sta gestendo il proprio lavoro e è necessario aumentare i parametri bgwriter_lru_maxpages, in modo che possa svolgere più lavoro e pulire più pagine in un ciclo.
E un altro indicatore molto utile è buffers_backend_fsync. I backend non eseguono fsync, perché è lento. Passano l’fsync più in alto nello stack IO al checkpointer. Il checkpointer ha la sua coda, gestisce periodicamente l’fsync e sincronizza le pagine in memoria con i file su disco. Se la coda del checkpointer è grande e piena, il backend è costretto a eseguire fsync da solo e questo rallenta il lavoro del backend, ovvero 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 sistema di archiviazione.
Attenzione: _Il testo seguente descrive le rappresentazioni statistiche relative alla replica. La maggior parte dei nomi delle rappresentazioni e delle funzioni è stata rinominata in Postgres 10. L'essenza dei rinominamenti consisteva nella sostituzione xlog in wal e location in lsn nei nomi delle funzioni/rappresentazioni e simili. Un esempio specifico, la funzione pg_xlog_location_diff() è stata rinominata in pg_wal_lsn_diff()._
Anche qui abbiamo molte cose. Ma ci serviranno solo i punti relativi alla location.
Se vediamo che tutti i valori sono uguali, allora questa è l'opzione ideale e la replica non sta passando indietro rispetto al master.
Questa posizione esadecimale è la posizione nel registro delle transazioni. Essa aumenta costantemente se c'è qualche attività nel database: inserimenti, cancellazioni, ecc.
quanti byte di xlog sono stati registrati
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
larghezza della replica in byte
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
larghezza della replica in secondi
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Se queste cose sono diverse, significa che c'è un qualche ritardo. Il ritardo è il gap tra la replica e il master, ovvero i dati differiscono tra i server.
Ci sono tre motivi per il ritardo:
- Il sistema di dischi non riesce a gestire la scrittura della sincronizzazione dei file.
- Potrebbero esserci errori di rete o sovraccarico di rete, quando i dati non riescono a raggiungere la replica in tempo e quindi non possono essere riprodotti.
- E il processore. Il processore è un caso molto raro. E l'ho visto due o tre volte, ma può succedere.
Ecco tre query che ci permettono di utilizzare le statistiche. Possiamo valutare quanto è stato registrato nel registro delle transazioni. C'è una funzione pg_xlog_location_diff e possiamo valutare il ritardo della replica in byte e secondi. Anche per questo utilizziamo il valore da questa rappresentazione (VIEWs).
Nota: _Invece della funzione pg_xlog_locationdiff() possiamo usare l'operatore di sottrazione e sottrarre una location dall'altra. È comodo.
Con il ritardo, che è in secondi, c'è un aspetto. Se non c'è alcuna attività sul master, e la transazione è avvenuta circa 15 minuti fa senza attività, e se guardiamo questo ritardo sulla replica, vedremo un ritardo di 15 minuti. È importante ricordarlo. Questo può confondere quando guardi questo ritardo.
Pg_stat_all_tables è un'altra vista utile. Mostra le statistiche delle tabelle. Quando abbiamo tabelle nel database, con qualche attività o azione, 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 queste scansioni non è necessariamente un cattivo segnale e non indica che dobbiamo già prendere provvedimenti.
Tuttavia, c'è una seconda metrica: seq_tup_read. Questo rappresenta il numero di righe restituite come risultato di una scansione sequenziale. Se il numero medio supera 1.000, 10.000, 50.000, 100.000, allora è un segnale che forse è necessario creare un indice, affinché le operazioni siano indicizzate, oppure potrebbe essere necessario ottimizzare le query che utilizzano tali scansioni sequenziali, per ridurne la frequenza.
Un semplice esempio: supponiamo che una query con un grande OFFSET e LIMIT sia costosa. Per esempio, scan di 100.000 righe nella tabella e poi si prendono 50.000 righe rilevanti, mentre le righe precedentemente scansionate vengono scartate. Anche questo è un caso negativo. E tali query devono essere ottimizzate. Qui c'è una semplice query SQL, su cui possiamo osservare e valutare i numeri 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 utilizzando funzioni aggiuntive. pg_total_relation_size(), pg_relation_size().
In generale, ci sono metacomandi dt e di, che possono essere utilizzati in PSQL per visualizzare anche le dimensioni di tabelle e indici.
Tuttavia, utilizzare funzioni aiuta a vedere le dimensioni delle tabelle anche tenendo conto degli indici, o senza considerarli, e fare già delle valutazioni basate sulla crescita del database, ovvero come cresce, con quale intensità, e trarre conclusioni sull'ottimizzazione delle dimensioni.
Attività di scrittura. Cos'è la scrittura? Esaminiamo l'operazione UPDATE – operazione di aggiornamento delle righe nella tabella. In sostanza, un update è composto da due operazioni (se non di più). Si tratta di inserire una nuova versione della riga e di contrassegnare la vecchia versione della riga come obsoleta. In seguito, arriverà l'autovacuum e queste versioni obsolete delle righe saranno rimosse, contrassegnando questo spazio come disponibile per il riutilizzo.
Inoltre, un update non riguarda solo l'aggiornamento della tabella. Si tratta anche di aggiornare gli indici. Se hai molti indici sulla tabella, durante l'update tutti gli indici che coinvolgono i campi aggiornati nella richiesta dovranno essere aggiornati. In questi indici ci saranno anche versioni obsolete delle righe che dovranno essere pulite.
select
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 per via del suo design, UPDATE è un'operazione pesante. Ma può essere alleggerita. Ci sono hot updates. Sono apparse nella versione 8.3 di PostgreSQL. E che cos'è? È un update leggero, che non porta alla ricostruzione degli indici. Cioè, abbiamo aggiornato una voce, ma è stata aggiornata solo la voce nella pagina (che appartiene alla tabella), mentre gli indici puntano ancora alla stessa voce nella pagina. C'è una logica di funzionamento interessante: quando arriva il vacuum, ristruttura queste catene hot e tutto continua a funzionare senza aggiornamenti degli indici, e si svolge tutto con un minore dispendio di risorse.
E quando hai n_tup_hot_upd elevato, è molto positivo. Significa che gli updates leggeri predominano e questo risulta essere più economico in termini di risorse e tutto va per il meglio.
ALTER TABLE table_name SET (fillfactor = 70);Come aumentare il volume di hot updates? Possiamo usare fillfactor. Definisce la dimensione dello spazio libero riservato durante il riempimento della pagina nella tabella tramite insert. Quando ci sono INSERT nella tabella, riempiono totalmente la pagina, senza lasciare spazio vuoto. Poi viene allocata una nuova pagina. I dati vengono nuovamente riempiti. Questo comportamento è predefinito, con fillfactor = 100%.
Possiamo impostare il fillfactor al 70%. Cioè, durante le operazioni di inserimento si riserva una nuova pagina, ma si riempie solo il 70% della pagina. E il 30% rimane in riserva. Quando sarà necessario eseguire un aggiornamento, con un'alta probabilità si verificherà nella stessa pagina e la nuova versione della riga si inserirà nella stessa pagina. Sarà effettuato un hot_update. In questo modo, si facilita la scrittura sulle 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 di avvio del vacuum. L'autovacuum è un sotto-sistema di cui sono disponibili pochissime informazioni nelle statistiche di PostgreSQL. Possiamo vedere in pg_stat_activity solo quanto durano attualmente le operazioni di vacuum. Tuttavia, comprendere quante tabelle sono in coda è piuttosto difficile.
Nota: _A partire dalla versione Postgres 10, la situazione per il monitoraggio del vacuum è notevolmente migliorata: è stata aggiunta la vista pg_stat_progressvacuum, che semplifica notevolmente il monitoraggio dell'autovacuum.
Possiamo utilizzare una query semplificata come questa. Possiamo vedere quando sarà effettuato il vacuum. Ma come e quando deve avviarsi l'autovacuum? Queste sono le versioni obsolete delle righe di cui ho parlato prima. Si è verificato un aggiornamento, è stata inserita una nuova versione della riga. È apparsa una versione obsoleta della riga. Nella tabella pg_stat_user_tables esiste un parametro chiamato n_dead_tup. Indica il numero di righe "morte". E non appena il numero di righe morte supera una certa soglia, l'autovacuum verrà attivato sulla tabella.
E come viene calcolata questa soglia? È una percentuale specifica del numero totale di righe nella tabella. Esiste un parametro autovacuum_vacuum_scale_factor. Questo parametro determina la percentuale. Supponiamo 10% + una soglia base aggiuntiva di 50 righe. E cosa succede? Quando il numero di righe morte supera il "10% + 50" rispetto al totale delle righe nella tabella, si attiva l'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 assegnati individualmente. E, di conseguenza, la soglia non sarà globale, ma individuale per la tabella. Pertanto, per calcolare, è necessario utilizzare astuzie e trucchi. E se ti interessa, puoi dare un'occhiata all'esperienza dei nostri colleghi di Avito (il link nella diapositiva è non valido ed è stato aggiornato nel testo).
Hanno scritto per , che tiene conto di queste cose. C'è una lista lunga due fogli. Ma calcola correttamente e consente di valutare abbastanza efficacemente dove abbiamo bisogno di molto vuoto per le tabelle e dove ce n'è poco.
Cosa possiamo fare con questo? Se abbiamo una grande coda e l'auto-vuoto non riesce a far fronte, possiamo aumentare il numero di lavoratori per il vuoto, oppure semplicemente rendere il vuoto più aggressivo, in modo che venga attivato prima e elabori la tabella in piccoli pezzi. Così facendo, la coda si ridurrà. — È importante monitorare il carico sui dischi, poiché il vuoto non è gratuito, anche se con l'arrivo di dispositivi SSD/NVMe il problema è diventato meno evidente.
Pg_stat_all_indexes è la statistica sugli indici. È piccola. Possiamo usarla per ottenere informazioni sull'utilizzo degli indici. Possiamo ad esempio determinare quali indici sono superflui.
Come ho già detto, l'update non è solo un aggiornamento delle tabelle, ma è anche un aggiornamento degli indici. Di conseguenza, se abbiamo molti indici nella tabella, durante l'aggiornamento delle righe nella tabella, è necessario aggiornare anche gli indici dei campi indicizzati, e se abbiamo indici non utilizzati, per i quali non ci sono scansioni indicizzate, rimangono come un peso morto. Dobbiamo liberarci di essi. Per questo abbiamo bisogno dell'campo idx_scan. Controlliamo semplicemente il numero di scansioni degli indici. Se gli indici hanno zero scansioni per un periodo relativamente lungo di archiviazione delle statistiche (di almeno 2-3 settimane), probabilmente si tratta di indici scadenti, dobbiamo liberarci di essi.
Nota: Quando cerchiamo indici non utilizzati nel caso di cluster di replica streaming, dobbiamo controllare tutti i nodi del cluster, poiché la statistica non è globale, e se un indice non è utilizzato nel master, potrebbe essere utilizzato nelle repliche (se lì c'è carico).
Due link:
Questi sono esempi di query più avanzate su come cercare indici non utilizzati.
Il secondo link è una richiesta piuttosto interessante. C'è una logica non banale incorporata. Lo consiglio per la lettura.
Cosa altro vale la pena riassumere sugli indici?
Gli indici non utilizzati sono problematici.
Occupano spazio.
Rallentano le operazioni di aggiornamento.
Lavoro extra per il vacuum.
Se eliminiamo gli indici non utilizzati, miglioreremo solo il database.
La prossima rappresentazione è pg_stat_activity. È analoga all'utilità ps, solo in PostgreSQL. Se psstai guardando i processi nel sistema operativo, allora pg_stat_activity ti mostrerà l'attività all'interno di PostgreSQL.
Cosa possiamo prendere di utile da lì?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Possiamo vedere l'attività complessiva, cosa sta succedendo nel database. Possiamo fare un nuovo deploy. Tutto è andato in crash, nuove connessioni non vengono accettate, gli errori si accumulano nell'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 vedere chi occupa la maggior parte delle connessioni. In questo caso, vediamo che l'user cron_role ha aperto 508 connessioni. E c'è qualcosa che non va. Dobbiamo occuparcene e vedere. È molto probabile 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 query lunghe. Tuttavia, se si verificano query lunghe, nel breve termine non è nulla di grave, ma nel lungo termine le query lunghe danneggiano il database, aumentano l'effetto bloat delle tabelle, quando si verifica la frammentazione delle tabelle. E bisogna liberarsi sia dal bloat che dalle query lunghe.
select
client_addr, usename, 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 query lunghe e le transazioni. Utilizziamo la funzione clock_timestamp() per determinare il tempo di esecuzione. Le query lunghe che abbiamo trovato possiamo memorizzarle e eseguire explain, vedere i piani e ottimizzare in qualche modo. Le query lunghe attuali le gestiamo 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 hanno diversi stati. E uno di questi stati può essere assunto in qualsiasi momento. Per definire gli stati c'è un 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) – sono problematici. Cosa significa? È quando un'applicazione ha aperto una transazione, ha svolto alcune operazioni e poi si è allontanata. La transazione è rimasta aperta. Essa rimane in sospeso, non accade nulla, occupa una connessione, blocchi sulle righe modificate e potenzialmente aumenta il bloat di altre tabelle, a causa dell'architettura del motore delle transazioni di Postgres. È opportuno terminare anche queste transazioni, perché sono dannose in ogni caso.
Se si vede che ce ne sono più di 5-10-20 nel database, è il momento di preoccuparsi e iniziare a fare qualcosa.
Qui utilizziamo anche per il calcolo del tempo clock_timestamp(). Terminiamo le transazioni, ottimizziamo l'applicazione.
Come ho già detto sopra, i blocchi – sono quando due o più transazioni si contendono una o più risorse. Per questo abbiamo un campo waiting con un valore booleano true o false.
True – significa che il processo è in attesa, è necessario fare qualcosa. Quando il processo è in attesa, significa che anche il cliente che ha avviato il processo sta aspettando. Il cliente nel browser è seduto e aspetta.
Attenzione: _A partire dalla versione Postgres 9.6 il campo waiting è stato rimosso e al suo posto sono stati aggiunti due campi più informativi wait_event_type e wait_event._
Cosa fare? Se si vede true per un lungo periodo, significa che è necessario liberarsi di tali richieste. Semplicemente terminiamo tali transazioni. Comunichiamo agli sviluppatori che è necessario ottimizzare in modo da non avere conflitti per le risorse. E poi gli sviluppatori ottimizzano l'applicazione per evitare tali situazioni.
E l'ultima, ma comunque potenzialmente non fatale, situazione è il verificarsi di deadlock. Due transazioni hanno aggiornato due risorse, poi si riconnettono a loro, già a risorse opposte. PostgreSQL, in questo caso, interviene e termina una transazione, affinché l'altra possa continuare a lavorare. È una situazione di stallo e non si risolve da sola. Pertanto, PostgreSQL è costretto a prendere misure estreme.
Ecco due query che consentono di monitorare i blocchi. Utilizziamo la vista pg_locks, che consente di monitorare i blocchi pesanti.
E il primo collegamento è il testo della richiesta. È piuttosto lungo.
E il secondo collegamento è un articolo sui lock. È utile leggerlo, è molto interessante.
Quindi, cosa vediamo? Vediamo due richieste. La transazione con ALTER TABLE è una transazione bloccante. È stata avviata, ma non è stata completata e l'applicazione che ha avviato questa transazione è occupata a fare altro. E la seconda richiesta è l'update. Sta aspettando che l'alter table finisca per continuare il proprio lavoro.
In questo modo possiamo scoprire chi ha bloccato chi, gestirlo e approfondire ulteriormente.
Il prossimo modulo è pg_stat_statements. Come ho già detto, è un modulo. Per utilizzarlo, è necessario caricare la sua libreria nella configurazione, riavviare PostgreSQL, installare il modulo (con un comando) e avremo una nuova vista.
Il tempo medio di richiesta in millisecondi
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Le richieste 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 ricavare da lì? Se parliamo di cose semplici, possiamo prendere il tempo medio di esecuzione della richiesta. Se il tempo cresce, significa che PostgreSQL risponde lentamente e dobbiamo prendere provvedimenti.
Possiamo osservare le transazioni di scrittura più attive nel database che modificano i dati in shared buffers. Possiamo vedere chi sta aggiornando o eliminando dati.
E possiamo semplicemente visualizzare varie statistiche su queste richieste.
Noi pg_stat_statements utilizziamo per generare report. Resettiamo la statistica una volta al giorno. La accumuliamo. Prima del reset della statistica la prossima volta, generiamo un report. Ecco il collegamento al report. Puoi consultarlo.
Cosa facciamo? Calcoliamo la statistica totale su tutte le richieste. Poi, per ogni richiesta, calcoliamo il suo contributo individuale a questa statistica totale.
E cosa possiamo osservare? Possiamo vedere il tempo totale di esecuzione di tutte le richieste di un certo tipo rispetto a tutte le altre richieste. Possiamo esaminare l'uso delle risorse della CPU e dell'I/O rispetto all'immagine generale. E già ottimizzare queste richieste. Costruiamo un elenco delle richieste in base a questo report e otteniamo materiale per riflettere su cosa ottimizzare.
Cosa ci è rimasto fuori dai riflettori? Rimangono ancora alcuni argomenti che non ho considerato, perché il tempo è limitato.
C'è pgstattuple – è anche un modulo aggiuntivo del pacchetto standard dei contribs. Permette di valutare bloat le tabelle, la cosiddetta frammentazione delle tabelle. E se la frammentazione è alta, è necessario rimuoverla, utilizzando diversi strumenti. E la funzione pgstattuple richiede tempo. E più tabelle ci sono, più a lungo impiegherà a funzionare.
Il prossimo contrib è pg_buffercache. Permette di ispezionare i buffer condivisi: quanto intensamente e per quali tabelle vengono utilizzate le pagine del buffer. E semplicemente consente di dare un'occhiata nei buffer condivisi e valutare cosa sta accadendo lì.
Il prossimo modulo è pgfincore. Permette di eseguire operazioni a basso livello con le tabelle tramite la chiamata di sistema mincore(), cioè permette di caricare una tabella nei buffer condivisi, o di scaricarla. E consente, tra l'altro, di ispezionare la cache delle pagine del sistema operativo, cioè in che misura la nostra tabella occupa spazio nella page cache, nei buffer condivisi e semplicemente permette di valutare il carico della tabella.
Il prossimo modulo è pg_stat_kcache. Utilizza anche la chiamata di sistema getrusage(). E la esegue prima e dopo l'esecuzione della query. E nelle statistiche ottenute permette di valutare quanto tempo la nostra query ha impiegato per eseguire le operazioni di input/output su disco, cioè operazioni con il file system e osserva l'utilizzo della CPU. Tuttavia, il modulo è giovane (cough-cough) e per funzionare richiede PostgreSQL 9.4 e pg_stat_statements, di cui ho parlato in precedenza.
Sapere come utilizzare le statistiche è utile. Non hai bisogno di programmi di terze parti. Puoi semplicemente dare un'occhiata, vedere, fare qualcosa, eseguire.
Utilizzare le statistiche è semplice, è un SQL normale. Hai composto la query, l'hai formulata, inviata, esaminata.
Le statistiche aiutano a rispondere alle domande. Se hai domande, puoi rivolgerti alle statistiche - osservi, trai conclusioni, analizzi i risultati.
E sperimenta. Ci sono molte query, molti dati. È sempre possibile ottimizzare una query esistente. Puoi creare la tua versione della query che ti si addice di più rispetto all'originale e utilizzarla.
Link
I link validi che ho incontrato nell'articolo, che sono stati utilizzati nella presentazione.
L'autore scrive ancora
(eng)
Il Collezionista di Statistiche
Funzioni di Amministrazione del Sistema
Moduli Contrib
Utilità SQL ed esempi di codice sql
Grazie a tutti per l'attenzione!
Fonte: habr.com
