Trascrizione del report del 2015 di Alexey Lesovsky "Deep dive into PostgreSQL internal statistics"
Dichiarazione di non responsabilità dell'autore del rapporto: Prendo atto che questo rapporto è datato novembre 2015: sono passati più di 4 anni ed è passato molto tempo. La versione 9.4 discussa nel report non è più supportata. Negli ultimi 4 anni sono state rilasciate 5 nuove versioni in cui sono apparse molte innovazioni, miglioramenti e modifiche relative alle statistiche e parte del materiale è obsoleto e non pertinente. Come ho recensito, ho cercato di contrassegnare questi luoghi per non fuorviare il lettore. Non ho riscritto questi luoghi, ce ne sono molti e, di conseguenza, verrà fuori un rapporto completamente diverso.
Il DBMS PostgreSQL è un meccanismo enorme e questo meccanismo è costituito da molti sottosistemi, il cui lavoro coordinato influisce direttamente sulle prestazioni del DBMS. Durante il funzionamento, vengono raccolte statistiche e informazioni sul funzionamento dei componenti, che consentono di valutare l'efficacia di PostgreSQL e adottare misure per migliorare le prestazioni. Tuttavia, ci sono molte di queste informazioni e sono presentate in una forma piuttosto semplificata. Elaborare queste informazioni e interpretarle a volte è un compito del tutto non banale e lo "zoo" di strumenti e utilità può facilmente confondere anche un DBA avanzato.
Buon pomeriggio Mi chiamo Alessio. Come ha detto Ilya, parlerò delle statistiche di PostgreSQL.
Statistiche sull'attività di PostgreSQL. PostgreSQL ha due statistiche. Statistiche di attività, che saranno discusse. E le statistiche dello scheduler sulla distribuzione dei dati. Parlerò in particolare delle statistiche di attività di PostgreSQL, che ci consentono di giudicare le prestazioni e in qualche modo migliorarle.
Ti dirò come utilizzare efficacemente le statistiche per risolvere una varietà di problemi che hai o potresti avere.
Cosa non sarà nel rapporto? Nel rapporto non toccherò le statistiche dello scheduler, perché. questo è un argomento separato per un rapporto separato su come i dati vengono archiviati nel database e su come il pianificatore di query ottiene un'idea delle caratteristiche qualitative e quantitative di questi dati.
E non ci saranno recensioni di strumenti, non confronterò un prodotto con un altro. Non ci sarà pubblicità. Lasciamo perdere.
Voglio mostrarti che usare le statistiche è utile. È necessario. Usalo senza paura. Tutto ciò di cui abbiamo bisogno è un semplice SQL e una conoscenza di base di SQL.
E parleremo di quali statistiche scegliere per risolvere i problemi.
Se guardiamo PostgreSQL ed eseguiamo un comando sul sistema operativo per visualizzare i processi, vedremo una "scatola nera". Vedremo alcuni processi che fanno qualcosa, e per nome possiamo immaginare approssimativamente cosa stanno facendo lì, cosa stanno facendo. Ma, in effetti, questa è una scatola nera, non possiamo guardarci dentro.
Possiamo esaminare il carico della CPU top, possiamo vedere l'utilizzo della memoria da parte di alcune utilità di sistema, ma non saremo in grado di guardare all'interno di PostgreSQL. Per questo abbiamo bisogno di altri strumenti.
E continuando oltre, ti dirò dove è trascorso il tempo. Se rappresentiamo PostgreSQL sotto forma di tale schema, allora sarà possibile rispondere dove viene speso il tempo. Queste sono due cose: è l'elaborazione delle richieste client dalle applicazioni e le attività in background che PostgreSQL esegue per mantenerlo in esecuzione.
Se iniziamo a guardare nell'angolo in alto a sinistra, possiamo vedere come vengono elaborate le richieste dei clienti. La richiesta proviene dall'applicazione e viene aperta una sessione client per ulteriori operazioni. La richiesta viene passata allo scheduler. Il pianificatore crea un piano di query. Lo invia ulteriormente per l'esecuzione. Esiste una sorta di dati di I/O a blocchi associati a tabelle e indici. I dati necessari vengono letti dai dischi in memoria in un'area speciale chiamata "buffer condivisi". I risultati della query, se sono aggiornamenti, eliminazioni, vengono registrati nel registro delle transazioni in WAL. Alcune informazioni statistiche vengono inserite nel registro o nel raccoglitore di statistiche. E il risultato della richiesta viene restituito al cliente. Successivamente, il cliente può ripetere tutto con una nuova richiesta.
Cosa abbiamo con le attività in background e i processi in background? Abbiamo diversi processi che mantengono il database attivo e funzionante in una normale modalità operativa. Anche questi processi saranno trattati nel report: si tratta di autovacuum, checkpointer, processi relativi alla replica, background writer. Toccherò ciascuno di essi mentre riferisco.
Quali sono i problemi con le statistiche?
- Molte informazioni. PostgreSQL 9.4 fornisce 109 metriche per la visualizzazione dei dati statistici. Tuttavia, se il database memorizza molte tabelle, schemi, database, tutte queste metriche dovranno essere moltiplicate per il numero corrispondente di tabelle, database. Cioè, ci sono ancora più informazioni. Ed è molto facile affogarci dentro.
- Il problema successivo è che le statistiche sono rappresentate da contatori. Se guardiamo a queste statistiche, vedremo contatori in costante aumento. E se è passato molto tempo dall'azzeramento delle statistiche, vedremo miliardi di valori. E non ci dicono niente.
- Non c'è storia. Se hai qualche tipo di errore, qualcosa è caduto 15-30 minuti fa, non sarai in grado di utilizzare le statistiche e vedere cosa è successo 15-30 minuti fa. Questo è un problema.
- La mancanza di uno strumento integrato in PostgreSQL è un problema. Gli sviluppatori del kernel non forniscono alcuna utilità. Non hanno niente del genere. Forniscono solo statistiche nel database. Usalo, fai una richiesta ad esso, qualunque cosa tu voglia, poi fallo.
- Poiché non esiste uno strumento integrato in PostgreSQL, ciò causa un altro problema. Molti strumenti di terze parti. Ogni azienda che ha mani più o meno dirette sta cercando di scrivere il proprio programma. Di conseguenza, la community ha molti strumenti che puoi utilizzare per lavorare con le statistiche. E in alcuni strumenti ci sono alcune funzionalità, in altri strumenti non ci sono altre funzionalità o ci sono alcune nuove funzionalità. E si verifica una situazione in cui è necessario utilizzare due, tre o quattro strumenti che si sovrappongono e hanno funzioni diverse. Questo è molto spiacevole.
Cosa ne consegue? È importante essere in grado di prendere direttamente le statistiche per non dipendere dai programmi, o in qualche modo migliorare tu stesso questi programmi: aggiungi alcune funzioni per ottenere il tuo vantaggio.
E hai bisogno di una conoscenza di base di SQL. Per ottenere alcuni dati dalle statistiche, è necessario eseguire query SQL, ovvero è necessario sapere come vengono effettuate le selezioni e le unioni.
Le statistiche ci dicono diverse cose. Possono essere suddivisi in categorie.
- La prima categoria sono gli eventi che si svolgono nel database. Questo è quando si verifica un evento nel database: una query, un accesso alla tabella, l'autovacuum, i commit, quindi questi sono tutti eventi. I contatori corrispondenti a questi eventi vengono incrementati. E possiamo tenere traccia di questi eventi.
- La seconda categoria sono le proprietà di oggetti come tabelle, database. Hanno proprietà. Questa è la dimensione dei tavoli. Possiamo monitorare la crescita delle tabelle, la crescita degli indici. Possiamo vedere i cambiamenti nelle dinamiche.
- E la terza categoria è il tempo dedicato all'evento. La richiesta è un evento. Ha una sua specifica misura di durata. Iniziato qui, finito qui. Possiamo rintracciarlo. O il tempo di lettura di un blocco dal disco o di scrittura. Anche queste cose sono tracciate.
Le fonti statistiche sono presentate come segue:
- Nella memoria condivisa (buffer condivisi) è presente un segmento per l'inserimento di dati statici, ci sono anche quei contatori che vengono costantemente incrementati quando si verificano determinati eventi, o sorgono alcuni momenti nel funzionamento del database.
- Tutti questi contatori non sono disponibili per l'utente e nemmeno per l'amministratore. Queste sono cose di basso livello. Per accedervi, PostgreSQL fornisce un'interfaccia sotto forma di funzioni SQL. Possiamo effettuare selezioni selezionate utilizzando queste funzioni e ottenere una sorta di metrica (o insieme di metriche).
- Tuttavia, non è sempre conveniente utilizzare queste funzioni, quindi le funzioni sono la base per le viste (VIEW). Si tratta di tabelle virtuali che forniscono statistiche su un sottosistema specifico o su un insieme di eventi nel database.
- Queste viste integrate (VIEW) sono l'interfaccia utente principale per lavorare con le statistiche. Sono disponibili per impostazione predefinita senza alcuna impostazione aggiuntiva, puoi usarli immediatamente, guardare, prendere informazioni da lì. E ci sono anche i contributi. I contributi sono ufficiali. Puoi installare il pacchetto postgresql-contrib (ad esempio, postgresql94-contrib), caricare il modulo necessario nella configurazione, specificarne i parametri, riavviare PostgreSQL e puoi usarlo. (Nota. A seconda della distribuzione, nelle versioni recenti di contrib il pacchetto fa parte del pacchetto principale).
- E ci sono contributi non ufficiali. Non sono forniti con la distribuzione PostgreSQL standard. Devono essere compilati o installati come libreria. Le opzioni possono essere molto diverse, a seconda di ciò che ha inventato lo sviluppatore di questo contributo non ufficiale.
Questa diapositiva mostra tutte quelle viste (VIEW) e alcune di quelle funzioni che sono disponibili in PostgreSQL 9.4. Come possiamo vedere, ce ne sono molti. Ed è abbastanza facile confondersi se lo stai sperimentando per la prima volta.
Tuttavia, se prendiamo la foto precedente Как тратится время на PostgreSQL e compatibile con questo elenco, otteniamo questa immagine. Ogni vista (VIEW), o ogni funzione, possiamo usarla per uno scopo o per un altro per ottenere le statistiche appropriate quando abbiamo PostgreSQL in esecuzione. E possiamo già ottenere alcune informazioni sul funzionamento del sottosistema.
La prima cosa che vedremo è pg_stat_database. Come possiamo vedere, questa è una rappresentazione. Contiene molte informazioni. Le informazioni più svariate. E fornisce una conoscenza molto utile di ciò che sta succedendo nel database.
Cosa possiamo prendere da lì? Partiamo dalle 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 guardare è la percentuale di accesso alla cache. La percentuale di accesso alla cache è una metrica utile. Ti consente di stimare quanti dati vengono prelevati dalla cache dei buffer condivisi e quanto viene letto dal disco.
È chiaro che più cache hit abbiamo, meglio è. Valutiamo questa metrica come percentuale. E, ad esempio, se abbiamo una percentuale di questi accessi alla cache superiore al 90%, allora va bene. Se scende al di sotto del 90%, non disponiamo di memoria sufficiente per mantenere in memoria la testina calda dei dati. E per utilizzare questi dati, PostgreSQL è costretto ad accedere al disco e questo è più lento che se i dati fossero letti dalla memoria. E devi pensare ad aumentare la memoria: aumentare i buffer condivisi o aumentare la memoria di ferro (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;Cos'altro si può prendere da questa performance? Puoi vedere le anomalie che si verificano nel database. Cosa viene mostrato qui? Ci sono commit, rollback, creazione di file temporanei, loro dimensione, deadlock e conflitti.
Possiamo usare questa richiesta. Questo SQL è piuttosto semplice. E possiamo vedere questi dati per noi stessi.
Ed ecco i valori soglia. Osserviamo il rapporto tra commit e rollback. Commit è una conferma di successo della transazione. Il rollback è un rollback, ad es. la transazione ha funzionato, ha messo a dura prova il database, ha considerato qualcosa, quindi si è verificato un errore e i risultati della transazione sono stati scartati. cioè. il numero di rollback in costante aumento è negativo. E dovresti in qualche modo evitarli e modificare il codice in modo che ciò non accada.
I conflitti sono correlati alla replica. E dovrebbero anche essere evitati. Se hai alcune query che vengono eseguite sulla replica e sorgono conflitti, allora devi analizzare questi conflitti e vedere cosa succede. I dettagli possono essere trovati nei registri. E risolvi i conflitti in modo che le richieste delle applicazioni funzionino senza errori.
Anche i deadlock sono una brutta situazione. Quando le richieste competono per le risorse, una richiesta ha avuto accesso a una risorsa e ha preso il blocco, la seconda richiesta ha avuto accesso alla seconda risorsa e ha anche preso il blocco, quindi entrambe le richieste hanno avuto accesso alle reciproche risorse e si sono bloccate in attesa che il vicino rilasci il blocco. Anche questa è una situazione problematica. Devono essere affrontati a livello di riscrittura delle applicazioni e serializzazione dell'accesso alle risorse. E se vedi che i tuoi deadlock sono in costante aumento, devi guardare i dettagli nei registri, analizzare le situazioni che si sono verificate e vedere qual è il problema.
Anche i file temporanei (temp_files) sono dannosi. Quando una richiesta dell'utente non dispone di memoria sufficiente per contenere i dati operativi e temporanei, crea un file su disco. E tutte le operazioni che potrebbe eseguire in un buffer temporaneo in memoria, inizia a eseguirle già sul disco. È lento. Ciò aumenta il tempo di esecuzione della query. E il client che ha inviato una richiesta a PostgreSQL riceverà una risposta poco dopo. Se tutte queste operazioni vengono eseguite in memoria, Postgres risponderà molto più velocemente e il client attenderà meno.
pg_stat_bgwriter - Questa vista descrive il funzionamento di due sottosistemi in background di PostgreSQL: checkpointer и background writer.
Per cominciare, analizziamo i punti di controllo, i cosiddetti. checkpoints. Cosa sono i checkpoint? Un checkpoint è una posizione nel log delle transazioni che indica che tutte le modifiche ai dati salvate nel log sono state sincronizzate correttamente con i dati su disco. Il processo, a seconda del carico di lavoro e delle impostazioni, può essere lungo e consiste principalmente nella sincronizzazione di pagine sporche in buffer condivisi con file di dati su disco. Cosa serve? Se PostgreSQL accedesse continuamente al disco e prelevasse dati da lì e scrivesse dati a ogni accesso, sarebbe lento. Pertanto, PostgreSQL ha un segmento di memoria, la cui dimensione dipende dai parametri nella configurazione. Postgres alloca i dati operativi in questa memoria per ulteriori elaborazioni o interrogazioni. Nel caso di richieste di modifica dei dati, questi vengono modificati. E otteniamo due versioni dei dati. Uno è in memoria, l'altro è su disco. E periodicamente è necessario sincronizzare questi dati. Abbiamo bisogno che ciò che è cambiato in memoria sia sincronizzato con il disco. Ciò richiede un checkpoint.
Il checkpoint passa attraverso i buffer condivisi, contrassegna le pagine sporche che sono necessarie per il checkpoint. Quindi avvia il secondo passaggio attraverso i buffer condivisi. E le pagine contrassegnate per checkpoint, le sincronizza già. Pertanto, i dati sono già sincronizzati con il disco.
Esistono due tipi di punti di controllo. Un checkpoint viene eseguito al timeout. Questo checkpoint è utile e buono - checkpoint_timed. E ci sono posti di blocco su richiesta - checkpoint required. Un tale checkpoint si verifica quando abbiamo un record di dati molto grande. Abbiamo registrato molti registri delle transazioni. E PostgreSQL crede di dover sincronizzare tutto questo il più rapidamente possibile, creare un checkpoint e andare avanti.
E se hai guardato le statistiche pg_stat_bgwriter e guarda cosa hai checkpoint_req è molto più grande di checkpoint_timed, quindi questo non va bene. Perchè male? Ciò significa che PostgreSQL è sottoposto a stress costante quando deve scrivere dati su disco. Il checkpoint per timeout è meno stressante e viene eseguito secondo il programma interno e, per così dire, allungato nel tempo. PostgreSQL ha la capacità di mettere in pausa il lavoro e non sovraccaricare il sottosistema del disco. Questo è utile per PostgreSQL. E le richieste eseguite durante il checkpoint non subiranno stress dal fatto che il sottosistema del disco è occupato.
E ci sono tre parametri per regolare il checkpoint:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Consentono di controllare il funzionamento dei punti di controllo. Ma non mi soffermerò su di loro. La loro influenza è una questione separata.
Nota: La versione 9.4 considerata nel report non è più rilevante. Nelle versioni moderne di PostgreSQL, il parametro checkpoint_segments sostituito da parametri min_wal_size и max_wal_size.
Il sottosistema successivo è il background writer - background writer. Cosa sta facendo? Funziona costantemente in un ciclo infinito. Esegue la scansione delle pagine nei buffer condivisi e scarica le pagine sporche che trova sul disco. In questo modo, aiuta il checkpointer a svolgere meno lavoro durante il checkpoint.
Per cos'altro è necessario? Prevede la necessità di pagine pulite nei buffer condivisi se vengono improvvisamente richieste (in grandi quantità e immediatamente) per contenere i dati. Supponiamo che si sia verificata una situazione in cui la richiesta ha richiesto pagine pulite e si trovano già nei buffer condivisi. Postgres backend li prende e li usa, non deve pulire niente da solo. Ma se all'improvviso non ci sono pagine di questo tipo, il back-end si ferma e inizia a cercare pagine per scaricarle su disco e prenderle per le proprie esigenze, il che influisce negativamente sul tempo della richiesta attualmente in esecuzione. Se vedi che hai un parametro maxwritten_clean grande, questo significa che il background writer non sta facendo il suo lavoro ed è necessario aumentare i parametri bgwriter_lru_maxpagesin modo che possa fare più lavoro in un ciclo, cancellare più pagine.
E un altro indicatore molto utile è buffers_backend_fsync. I backend non eseguono fsync perché è lento. Passano fsync al checkpointer dello stack IO. Il checkpointer ha una propria coda, elabora periodicamente 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 stesso e questo rallenta il backend, ovvero il client riceverà una risposta più tardi di quanto potrebbe. Se vedi che hai questo valore maggiore di zero, allora questo è già un problema e è necessario prestare attenzione alle impostazioni del background writer e valutare anche le prestazioni del sottosistema del disco.
Nota: _Il testo seguente descrive le visualizzazioni statistiche associate alla replica. La maggior parte dei nomi delle viste e delle funzioni sono stati rinominati in Postgres 10. L'essenza delle rinominazioni era sostituire xlog su wal и location su lsn nei nomi delle funzioni/visualizzazioni, ecc. Esempio particolare, funzione pg_xlog_location_diff() è stato rinominato pg_wal_lsn_diff()._
Abbiamo molto anche qui. Ma abbiamo bisogno solo di elementi relativi alla posizione.
Se vediamo che tutti i valori sono uguali, allora questo è l'ideale e la replica non è in ritardo rispetto al master.
Questa posizione esadecimale qui è la posizione nel log delle transazioni. Aumenta costantemente se c'è qualche attività nel database: inserimenti, cancellazioni, ecc.
сколько записано xlog в байтах
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
лаг репликации в байтах
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
лаг репликации в секундах
$ select
extract(epoch from now() - pg_last_xact_replay_timestamp());Se queste cose sono diverse, allora c'è una sorta di ritardo. Il ritardo è il ritardo della replica dal master, ovvero i dati differiscono tra i server.
I motivi del ritardo sono tre:
- È il sottosistema del disco che non è in grado di gestire le scritture di sincronizzazione dei file.
- Questi sono possibili errori di rete, o sovraccarico di rete, quando i dati non hanno il tempo di raggiungere la replica e non possono riprodurli.
- E il processore. Il processore è un caso molto raro. E l'ho visto due o tre volte, ma può succedere anche quello.
E qui ci sono tre query che ci consentono di utilizzare le statistiche. Possiamo stimare quanto viene registrato nel nostro registro delle transazioni. C'è una tale funzione pg_xlog_location_diff e possiamo stimare il ritardo di replica in byte e secondi. Usiamo anche il valore di questa vista (VISUALIZZAZIONI) per questo.
Nota: _Invece di pg_xlog_locationdiff(), è possibile utilizzare l'operatore di sottrazione e sottrarre una posizione da un'altra. Comodo.
Con un ritardo, che è in secondi, c'è un momento. Se non c'è attività sul master, la transazione era lì circa 15 minuti fa e non c'è attività, e se osserviamo questo ritardo sulla replica, vedremo un ritardo di 15 minuti. Vale la pena ricordarlo. E può portare a uno stupore quando hai visto questo ritardo.
pg_stat_all_tables è un'altra visualizzazione utile. Mostra le statistiche sulle tabelle. Quando abbiamo tabelle nel database, c'è qualche attività con esso, alcune azioni, 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 guardare sono le scansioni sequenziali della tabella. Il numero stesso dopo questi passaggi non è necessariamente negativo e non indica che dobbiamo già fare qualcosa.
Tuttavia, esiste una seconda metrica: seq_tup_read. Questo è il numero di righe restituite dalla scansione sequenziale. Se il numero medio supera 1, 000, 10, 000, allora questo è già un indicatore che potrebbe essere necessario creare un indice da qualche parte in modo che gli accessi siano per indice, oppure è possibile ottimizzare le query che utilizzano tali scansioni sequenziali in modo che questo non accade.
Un semplice esempio: diciamo che ne vale la pena una richiesta con un OFFSET e un LIMIT grandi. Ad esempio, vengono scansionate 100 righe in una tabella e successivamente vengono prese 000 righe richieste e le precedenti righe scansionate vengono eliminate. Anche questo è un brutto caso. E tali richieste devono essere ottimizzate. Ed ecco una query SQL così semplice su cui puoi vederla e valutare i numeri ricevuti.
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 della tabella possono anche essere ottenute utilizzando questa tabella e utilizzando funzioni aggiuntive pg_total_relation_size(), pg_relation_size().
In generale, ci sono metacomandi dt и di, che puoi usare in PSQL e vedere anche le dimensioni della tabella e dell'indice.
Tuttavia, l'uso delle funzioni ci aiuta a guardare le dimensioni delle tabelle, anche tenendo conto degli indici, o senza tener conto degli indici, e fare già delle stime basate sulla crescita del database, ovvero come cresce con noi, con quale intensità, e traggo già alcune conclusioni sull'ottimizzazione del dimensionamento.
Scrivere attività. Cos'è un record? Diamo un'occhiata all'operazione UPDATE – l'operazione di aggiornamento delle righe nella tabella. In effetti, l'aggiornamento è di due operazioni (o anche di più). Questo sta inserendo una nuova versione di riga e contrassegnando la vecchia versione di riga come obsoleta. Più tardi, l'autovacuum arriverà e ripulirà queste versioni obsolete delle linee, contrassegnando questo posto come disponibile per il riutilizzo.
Inoltre, l'aggiornamento non riguarda solo l'aggiornamento di una tabella. È ancora un aggiornamento dell'indice. Se nella tabella sono presenti molti indici, con l'aggiornamento dovranno essere aggiornati anche tutti gli indici a cui partecipano i campi aggiornati nella query. Questi indici avranno anche versioni di riga obsolete che dovranno essere ripulite.
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 grazie al suo design, UPDATE è un'operazione pesante. Ma possono essere resi più facili. Mangiare hot updates. Sono apparsi in PostgreSQL versione 8.3. E questo che cos'è? Si tratta di un aggiornamento leggero che non comporta la ricostruzione degli indici. Cioè, abbiamo aggiornato il record, ma è stato aggiornato solo il record nella pagina (che appartiene alla tabella) e gli indici puntano ancora allo stesso record nella pagina. C'è un po' di una logica di lavoro così interessante, quando arriva il vuoto, allora ha queste catene hot si ricostruisce e tutto continua a funzionare senza aggiornare gli indici, e tutto avviene con minor dispendio di risorse.
E quando hai n_tup_hot_upd grande, è molto buono. Ciò significa che prevalgono gli aggiornamenti leggeri e questo è più economico per noi in termini di risorse e va tutto bene.
ALTER TABLE table_name SET (fillfactor = 70);Come aumentare il volume hot updateov? Possiamo usare fillfactor. Determina la dimensione dello spazio libero riservato quando si riempie una pagina in una tabella utilizzando INSERT. Quando gli inserti vanno al tavolo, riempiono completamente la pagina, non lasciano spazio vuoto al suo interno. Quindi viene evidenziata una nuova pagina. I dati vengono nuovamente inseriti. E questo è il comportamento predefinito, fattore di riempimento = 100%.
Possiamo impostare il fattore di riempimento al 70%. Cioè, con gli inserti è stata assegnata una nuova pagina, ma è stato riempito solo il 70% della pagina. E abbiamo il 30% di riserva. Quando è necessario eseguire un aggiornamento, molto probabilmente avverrà sulla stessa pagina e la nuova versione della riga si adatterà alla stessa pagina. E hot_update sarà fatto. Questo rende più facile scrivere 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 autovuoto. Autovacuum è un tale sottosistema per il quale ci sono pochissime statistiche in PostgreSQL. Possiamo solo vedere nelle tabelle in pg_stat_activity quanti vuoti abbiamo al momento. Tuttavia, è molto difficile capire quanti tavoli in coda ha in movimento.
Nota: _Da Postgres 10, la situazione con il tracciamento del vuoto è migliorata molto: è apparsa la vista pg_stat_progressvacuum, che semplifica enormemente il problema del monitoraggio dell'autovacuum.
Possiamo usare questa query semplificata. E possiamo vedere quando dovrebbe essere fatto il vuoto. Ma come e quando dovrebbe iniziare il vuoto? Queste sono le vecchie versioni delle corde di cui ho parlato prima. Aggiornamento avvenuto, è stata inserita la nuova versione della riga. È apparsa una versione obsoleta della stringa. Tavolo pg_stat_user_tables esiste un tale parametro n_dead_tup. Mostra il numero di righe "morte". E non appena il numero di righe morte sarà superiore a una certa soglia, arriverà al tavolo un autovacuum.
E come viene calcolata questa soglia? Questa è una percentuale molto specifica del numero totale di righe nella tabella. C'è un parametro autovacuum_vacuum_scale_factor. Definisce la percentuale. Diciamo 10% + c'è una soglia base aggiuntiva di 50 linee. E cosa succede? Quando abbiamo più righe morte di "10% + 50" di tutte le righe nella tabella, mettiamo la tabella in 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 punto. Soglie di base per i parametri av_base_thresh и av_scale_factor possono essere assegnati individualmente. E, di conseguenza, la soglia non sarà globale, ma individuale per il tavolo. Pertanto, per calcolare, devi usare trucchi e trucchi. E se sei interessato, puoi guardare l'esperienza dei nostri colleghi di Avito (il link sulla slide non è valido ed è stato aggiornato nel testo).
Hanno scritto per che tiene conto di queste cose. C'è una coperta su due fogli. Ma pensa correttamente e in modo abbastanza efficace ci consente di valutare dove abbiamo bisogno di molto vuoto per tavoli dove c'è poco.
Cosa possiamo fare al riguardo? Se abbiamo una lunga coda e l'autovuoto non ce la fa, allora possiamo aumentare il numero di addetti al vuoto o semplicemente rendere il vuoto più aggressivoin modo che si attivi prima, elabora la tabella in piccoli pezzi. E così la coda diminuirà. - La cosa principale qui è monitorare il carico sui dischi, perché. La faccenda del vuoto non è gratuita, anche se con l'avvento dei dispositivi SSD/NVMe il problema è diventato meno evidente.
pg_stat_all_indexes sono le statistiche sugli indici. Lei non è grande. E possiamo ottenere informazioni sull'uso degli indici da esso. E ad esempio, possiamo determinare quali indici abbiamo in più.
Come ho già detto, update non aggiorna solo le tabelle, ma aggiorna anche gli indici. Di conseguenza, se nella tabella sono presenti molti indici, quando si aggiornano le righe nella tabella, è necessario aggiornare anche gli indici dei campi indicizzati e se disponiamo di indici inutilizzati per i quali non ci sono scansioni dell'indice, allora rimangono con noi come zavorra. E devi sbarazzartene. Per questo abbiamo bisogno di un campo idx_scan. Guardiamo solo il numero di scansioni dell'indice. Se gli indici hanno zero scansioni per un periodo relativamente lungo di archiviazione delle statistiche (almeno 2-3 settimane), molto probabilmente si tratta di indici errati, dobbiamo eliminarli.
Nota: Durante la ricerca di indici inutilizzati nel caso di cluster di replica in streaming, è necessario controllare tutti i nodi del cluster, perché le statistiche non sono globali e se l'indice non viene utilizzato sul master, può essere utilizzato sulle repliche (se è presente un carico).
Due link:
Questi sono esempi di query più avanzati su come cercare gli indici inutilizzati.
Il secondo collegamento è una query piuttosto interessante. C'è una logica molto non banale lì dentro. Lo consiglio per la revisione.
Cos'altro dovrebbe essere riassunto dagli indici?
-
Gli indici inutilizzati sono cattivi.
-
Occupano spazio.
-
Rallenta le operazioni di aggiornamento.
-
Lavoro extra per il vuoto.
Se rimuoviamo gli indici inutilizzati, miglioreremo solo il database.
La vista successiva è pg_stat_activity. Questo è un analogo dell'utilità ps, solo in PostgreSQL. Se ps'oh, guarda i processi nel sistema operativo, allora pg_stat_activity ti mostrerà l'attività all'interno di PostgreSQL.
Cosa possiamo prendere da lì?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Possiamo vedere l'attività complessiva che sta accadendo nel database. Possiamo fare un nuovo schieramento. Lì è esploso tutto, le nuove connessioni non vengono accettate, gli errori si riversano nell'applicazione.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Possiamo eseguire una query come questa e vedere la percentuale totale di connessioni rispetto al limite massimo di connessioni e vedere chi ha più connessioni. E in questo caso specifico, vediamo quell'utente cron_role aperto 508 connessioni. E gli è successo qualcosa. Devi affrontarlo e vedere. Ed è del tutto possibile che si tratti di una sorta di numero anomalo di connessioni.
Se abbiamo un carico OLTP, le query dovrebbero essere veloci, molto veloci e non dovrebbero esserci query lunghe. Tuttavia, se ci sono richieste lunghe, a breve termine non c'è nulla di cui preoccuparsi, ma a lungo termine, le query lunghe danneggiano il database, aumentano l'effetto di gonfiamento delle tabelle quando si verifica la frammentazione della tabella. Sia le query gonfie che quelle lunghe devono essere eliminate.
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 tale richiesta, possiamo definire richieste e transazioni lunghe. Usiamo la funzione clock_timestamp() per determinare l'orario di lavoro. Lunghe richieste che abbiamo trovato, possiamo ricordarle, eseguirle explain, guarda i piani e in qualche modo ottimizza. Riprendiamo le attuali richieste lunghe e continuiamo a vivere.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Le transazioni errate sono inattive nella transazione e inattive nelle transazioni (interrotte).
Cosa significa? Le transazioni hanno più stati. E uno di questi stati può richiedere in qualsiasi momento. C'è un campo per definire gli stati state in questa visione. E lo usiamo per determinare lo stato.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';E, come ho detto sopra, questi due stati inattivo nella transazione e inattivo nella transazione (interrotto) sono cattivi. Cos'è? Questo è quando l'applicazione ha aperto una transazione, ha eseguito alcune azioni e ha svolto la propria attività. La transazione rimane aperta. Si blocca, non accade nulla, richiede una connessione, si blocca sulle righe modificate e potenzialmente aumenta ancora il volume di altre tabelle, a causa dell'architettura del motore transazionale Postrges. E anche tali transazioni dovrebbero essere sparate, perché in generale sono dannose, in ogni caso.
Se vedi che ne hai più di 5-10-20 nel tuo database, allora devi preoccuparti e iniziare a fare qualcosa con loro.
Qui usiamo anche per il tempo di calcolo clock_timestamp(). Riprendiamo le transazioni, ottimizziamo l'applicazione.
Come ho detto sopra, i blocchi si verificano quando due o più transazioni competono per una o un gruppo di risorse. Per questo abbiamo un campo waiting con valore booleano true o false.
Vero: questo significa che il processo è in attesa, è necessario fare qualcosa. Quando un processo è in attesa, anche il client che ha avviato il processo è in attesa. Il client nel browser si siede e attende anche.
Nota: _A partire da Postgres 9.6, il campo waiting rimosso e sostituito da due ulteriori campi informativi wait_event_type и wait_event._
Cosa fare? Se vedi vero per molto tempo, dovresti sbarazzarti di tali richieste. Riprendiamo solo tali transazioni. Scriviamo agli sviluppatori ciò che deve essere ottimizzato in qualche modo in modo che non ci sia corsa per le risorse. E poi gli sviluppatori ottimizzano l'applicazione in modo che ciò non accada.
E un caso estremo, ma potenzialmente non fatale lo è verificarsi di situazioni di stallo. Due transazioni hanno aggiornato due risorse, quindi vi accedono nuovamente, già a risorse opposte. PostgreSQL in questo caso prende e spara la transazione stessa in modo che l'altra possa continuare a funzionare. Questa è una situazione senza uscita e lei non capisce se stessa. Pertanto, PostgreSQL è costretto a prendere misure estreme.
E qui ci sono due query che ti consentono di tenere traccia dei blocchi. Usiamo la vista pg_locks, che ti consente di tenere traccia delle serrature pesanti.
E il primo link è il testo della richiesta stessa. È piuttosto lungo.
E il secondo link è un articolo sui lucchetti. È utile leggere, è molto interessante.
Quindi cosa vediamo? Vediamo due richieste. Transazione con ALTER TABLE è una transazione di blocco. È iniziato, ma non è finito, e l'applicazione che ha pubblicato questa transazione sta facendo altre cose da qualche parte. E la seconda richiesta è l'aggiornamento. Attende che il tavolo dell'altare finisca prima di continuare il suo lavoro.
Questo è il modo in cui possiamo scoprire chi ha rinchiuso chi, chi detiene chi, e possiamo affrontare ulteriormente la questione.
Il modulo successivo è pg_stat_statements. Come ho detto, è un modulo. Per usarlo, devi caricare la sua libreria nella configurazione, riavviare PostgreSQL, installare il modulo (con un comando), e poi avremo una nuova vista.
Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;Cosa possiamo prendere da lì? Se parliamo di cose semplici, possiamo prendere il tempo medio di esecuzione della query. Il tempo sta crescendo, il che significa che PostgreSQL sta rispondendo lentamente e bisogna fare qualcosa.
Possiamo vedere le transazioni di scrittura più attive nel database che modificano i dati nei buffer condivisi. Scopri chi aggiorna o elimina i dati lì.
E possiamo semplicemente esaminare statistiche diverse per queste query.
Noi pg_stat_statements utilizzato per creare report. Reimpostiamo le statistiche una volta al giorno. Accumuliamolo. Prima di reimpostare le statistiche la prossima volta, creiamo un rapporto. Di seguito il link al rapporto. Puoi guardarlo.
Che cosa stiamo facendo? Calcoliamo le statistiche complessive per tutte le richieste. Quindi, per ogni query, contiamo il suo contributo individuale a questa statistica complessiva.
E cosa possiamo vedere? Possiamo vedere il tempo totale di esecuzione di tutte le richieste di un particolare tipo sullo sfondo di tutte le altre richieste. Possiamo esaminare l'utilizzo di CPU e I/O in relazione al quadro generale. E già per ottimizzare queste richieste. Stiamo costruendo le query principali sulla base di questo rapporto e stiamo già ricevendo spunti di riflessione su cosa ottimizzare.
Cosa abbiamo dietro le quinte? Ci sono ancora alcune proposte che non ho preso in considerazione, perché il tempo è limitato.
C'è pgstattuple è anche un modulo aggiuntivo del pacchetto contribs standard. Ti permette di valutare bloat tavoli, cosiddetti. frammentazione della tabella. E se la frammentazione è grande, devi rimuoverla, utilizzare strumenti diversi. E funzione pgstattuple funziona a lungo. E più tavoli ci sono, più a lungo funzionerà.
Il prossimo contributo è pg_buffercache. Ti consente di ispezionare i buffer condivisi: quanto intensamente e per quali tabelle vengono utilizzate le pagine del buffer. E ti consente solo di esaminare i buffer condivisi e valutare cosa sta succedendo lì.
Il modulo successivo è pgfincore. Ti consente di eseguire operazioni di tabella di basso livello tramite una chiamata di sistema mincore(), ovvero consente di caricare la tabella in buffer condivisi o di scaricarla. E consente, tra l'altro, di ispezionare la cache delle pagine del sistema operativo, ovvero quanto occupa la tabella nella cache delle pagine, nei buffer condivisi, e consente semplicemente di valutare il carico sulla tabella.
Il modulo successivo è pg_stat_kcache. Utilizza anche la chiamata di sistema getrusage(). E lo esegue prima e dopo l'esecuzione della richiesta. E nelle statistiche ottenute, ci consente di stimare quanto la nostra richiesta ha speso per I / O su disco, ovvero operazioni con il file system e guarda l'utilizzo del processore. Tuttavia, il modulo è giovane (khe-khe) e per il suo funzionamento richiede PostgreSQL 9.4 e pg_stat_statements, di cui ho parlato prima.
-
La possibilità di utilizzare le statistiche è utile. Non hai bisogno di software di terze parti. Puoi guardare, vedere, fare qualcosa, esibirti.
-
Usare le statistiche è facile, è semplice SQL. Hai raccolto una richiesta, compilata, inviata, guardata.
-
Le statistiche aiutano a rispondere alle domande. Se hai domande, ti rivolgi alle statistiche: guarda, trai conclusioni, analizza i risultati.
-
E sperimenta. Tante richieste, tanti dati. Puoi sempre ottimizzare alcune query esistenti. Puoi creare la tua versione della richiesta che ti si addice meglio dell'originale e usarla.
riferimenti
Collegamenti validi che sono stati trovati nell'articolo, in base al quale, erano nel rapporto.
L'autore scrive di più
(ita)
Il collezionista di statistiche
Funzioni di amministrazione del sistema
Moduli contributivi
Utilità SQL ed esempi di codice SQL
Grazie a tutti per la vostra attenzione!
Fonte: habr.com