Trascrizione di u rapportu 2015 da Alexey Lesovsky "Immersione profonda in e statistiche interne PostgreSQL"
Disclaimer da l'autore di u rapportu: Aghju nutatu chì stu rapportu hè di nuvembre 2015 - più di 4 anni sò passati è assai tempu hè passatu. A versione 9.4 discutata in u rapportu ùn hè più supportata. In l'ultimi anni 4, 5 novi versioni sò stati liberati in quale sò apparsu assai innuvazioni, migliure è cambiamenti riguardanti statistiche, è una parte di u materiale hè obsoleta è micca pertinente. Cume aghju rivista, aghju pruvatu à marcà sti lochi per ùn ingannà u lettore. Ùn aghju micca riscrivite sti lochi, ci sò assai di elli, è in u risultatu, un rapportu completamente diversu serà da esse.
U DBMS PostgreSQL hè un mecanismu enormu, è questu mecanismu hè custituitu da parechji sottosistemi, u travagliu coordinatu di quale affetta direttamente u rendiment di u DBMS. Durante l'operazione, statistiche è informazioni nantu à u funziunamentu di i cumpunenti sò recullati, chì vi permette di valutà l'efficacità di PostgreSQL è piglià misure per migliurà u rendiment. Tuttavia, ci hè assai di sta infurmazione è hè presentata in una forma piuttostu simplificata. Trattamentu di sta infurmazione è l'interpretazione hè qualchì volta un compitu cumplettamente micca trivial, è u "zoo" di arnesi è utilità ponu facilmente cunfundà ancu un DBA avanzatu.
Bonghjornu Mi chjamu Aleksey. Cum'è Ilya hà dettu, parleraghju di statistiche PostgreSQL.
Statistiche di attività PostgreSQL. PostgreSQL hà duie statistiche. Statistiche di l'attività, chì seranu discututi. E statistiche di pianificazione nantu à a distribuzione di dati. Parlaraghju specificamente di e statistiche di l'attività PostgreSQL, chì ci permettenu di ghjudicà u rendiment è in qualchì manera di migliurà.
Vi dicu cumu utilizà in modu efficace e statistiche per risolve una varietà di prublemi chì avete o pudete avè.
Chì ùn serà micca in u rapportu? In u rapportu, ùn aghju micca toccu nantu à e statistiche di u pianificatore, perchè. Questu hè un tema separatu per un rapportu separatu nantu à cumu i dati sò almacenati in a basa di dati è cumu u pianificatore di query si face una idea di e caratteristiche qualitative è quantitative di sti dati.
È ùn ci sarà micca rivista di strumentu, ùn aghju micca paragunà un pruduttu cù un altru. Ùn ci sarà micca publicità. Lasciamu caccià.
Vogliu dimustrà chì l'usu di statistiche hè utile. Hè necessariu. Aduprate senza paura. Tuttu ciò chì avemu bisognu hè SQL chjaru è una cunniscenza basica di SQL.
E parlemu di quale statistiche sceglite per risolve i prublemi.
Se guardemu à PostgreSQL è eseguite un cumandamentu nantu à u sistema operatore per vede i prucessi, vedemu una "scatola negra". Videremu qualchi prucessi chì facenu qualcosa, è per nome pudemu quasi imagine ciò chì facenu quì, ciò chì facenu. Ma, in fattu, questu hè una scatula negra, ùn pudemu micca guardà dentru.
Pudemu guardà a carica di CPU in top, pudemu vede l'utilizazione di a memoria da certi utilità di u sistema, ma ùn puderemu micca guardà in PostgreSQL. Per questu avemu bisognu di altri strumenti.
E cuntinuendu più in là, vi dicu induve u tempu hè passatu. Se rapprisentamu PostgreSQL in a forma di un tali schema, allora serà pussibule di risponde induve u tempu hè passatu. Quessi sò dui cose: hè u trattamentu di e dumande di i clienti da l'applicazioni è e funzioni di fondo chì PostgreSQL eseguisce per mantene in esecuzione.
Se cumminciamu à guardà à l'angulu superiore manca, pudemu vede cumu e richieste di i clienti sò trattate. A dumanda vene da l'applicazione è una sessione di cliente hè aperta per u travagliu più. A dumanda hè passata à u pianificatore. U pianificatore custruisce un pianu di dumanda. Invia più in più per l'esecuzione. Ci hè un certu tipu di dati di bloccu I / O assuciatu cù e tabelle è indici. I dati necessarii sò letti da i dischi in memoria in una zona speciale chjamata "buffers spartuti". I risultati di a dumanda, se sò aghjurnamenti, sguassate, sò registrati in u logu di transazzione in WAL. Certi infurmazioni statistiche finiscinu in un logu o cullizzioni di statistiche. È u risultatu di a dumanda hè restituita à u cliente. Dopu questu, u cliente pò ripetiri tuttu cù una nova dumanda.
Chì avemu cù i travaglii di fondo è i prucessi di fondo? Avemu parechji prucessi chì mantenenu a basa di dati in funziunamentu in un modu operativu normale. Sti prucessi seranu ancu coperti in u rapportu: questi sò autovacuum, checkpointer, prucessi ligati à a replicazione, scrittore di fondo. Tuccaraghju à ognuna di elli mentre raportu.
Chì sò i prublemi cù e statistiche?
- Assai infurmazione. PostgreSQL 9.4 furnisce 109 metriche per vede dati di statistiche. In ogni casu, se a basa di dati guarda parechje tavule, schemi, basa di dati, allora tutte queste metriche anu da esse multiplicate da u numeru currispundente di tavule, basa di dati. Questu hè, ci hè ancu più infurmazione. È hè assai faciule d'affucà in questu.
- U prublema dopu hè chì e statistiche sò rapprisentate da cuntatori. Se guardemu queste statistiche, videremu cuntatori in constantemente crescente. È s'ellu hè passatu assai tempu da quandu e statistiche sò state resettate, vedemu miliardi di valori. È ùn ci dicenu nunda.
- Ùn ci hè micca storia. Sì avete qualchì tipu di fallimentu, qualcosa hè cascatu 15-30 minuti fà, ùn puderete micca aduprà e statistiche è vede ciò chì hè accadutu 15-30 minuti fà. Questu hè u prublema.
- A mancanza di un strumentu integratu in PostgreSQL hè un prublema. I sviluppatori di u kernel ùn furnisce micca alcuna utilità. Ùn anu micca nunda cusì. Solu dà statistiche in a basa di dati. Aduprate, fate una dumanda à ellu, ciò chì vulete, poi fate.
- Siccomu ùn ci hè micca strumentu integratu in PostgreSQL, questu causa un altru prublema. Un saccu di strumenti di terzu partitu. Ogni cumpagnia chì hà più o menu mani dirette prova di scrive u so propiu prugramma. È in u risultatu, a cumunità hà assai strumenti chì pudete aduprà per travaglià cù statistiche. È in certi arnesi ci sò qualchi funziunalità, in altri arnesi ùn sò micca altre funziunalità, o ci sò qualchi funziunalità novi. È una situazione hè chì avete bisognu di utilizà dui o trè o quattru arnesi chì si sovrapponenu è anu diverse funzioni. Questu hè assai dispiacevule.
Chì seguita da questu? Hè impurtante per esse capace di piglià statistiche direttamente per ùn dipende micca di i prugrammi, o in qualchì manera migliurà sti prugrammi stessu: aghjunghje alcune funzioni per uttene u vostru benefiziu.
È avete bisognu di cunniscenze basi di SQL. Per piglià qualchi dati da statistiche, avete bisognu di fà e dumande SQL, vale à dì chì avete bisognu di sapè cumu selezziunate, unisce sò fatti.
L'statistiche ci dicenu parechje cose. Puderanu esse divisu in categurie.
- A prima categuria hè l'avvenimenti chì si facenu in a basa di dati. Questu hè quandu qualchì avvenimentu accade in a basa di dati: una quistione, un accessu à a tavula, autovacuum, commits, allora sò tutti l'avvenimenti. I cuntatori chì currispondenu à questi avvenimenti sò incrementati. È pudemu seguità questi avvenimenti.
- A seconda categuria hè a pruprietà di l'uggetti cum'è e tavule, basa di dati. Hanu pruprietà. Questa hè a dimensione di e tavule. Pudemu seguità a crescita di e tavule, a crescita di l'indici. Pudemu vede cambiamenti in a dinamica.
- È a terza categuria hè u tempu passatu nantu à l'avvenimentu. A dumanda hè un avvenimentu. Hà a so propria misura specifica di durata. Cuminciò quì, finisci quì. Pudemu seguità. Sia u tempu di leghje un bloccu da u discu o di scrittura. Queste cose sò ancu tracciate.
I fonti di statistiche sò presentati cum'è seguente:
- In a memoria spartuta (buffers spartuti) ci hè un segmentu per mette dati statici quì, ci sò ancu quelli cuntatori chì sò constantemente aumentati quandu certi avvenimenti accadenu, o certi mumenti nascenu in u funziunamentu di a basa di dati.
- Tutti questi cuntatori ùn sò micca dispunibili per l'utilizatore è ùn sò ancu dispunibili per l'amministratore. Quessi sò cose di livellu bassu. Per accede à elli, PostgreSQL furnisce una interfaccia in forma di funzioni SQL. Pudemu selezziunà selezzione cù queste funzioni è uttene qualchì tipu di metrica (o set di metriche).
- In ogni casu, ùn hè micca sempre cunvenutu per utilizà sti funzioni, cusì e funzioni sò a basa per vede (VIEWs). Quessi sò tavule virtuale chì furnisce statistiche nantu à un subsistema specificu, o in qualchì settore di avvenimenti in a basa di dati.
- Queste viste integrate (VIEWs) sò l'interfaccia d'utilizatore principale per travaglià cù statistiche. Sò dispunibuli per automaticamente senza paràmetri supplementari, pudete immediatamente aduprà, fighjate, piglià infurmazioni da quì. È ci sò ancu cuntribuzioni. I cuntributi sò ufficiali. Pudete installà u pacchettu postgresql-contrib (per esempiu, postgresql94-contrib), carricà u modulu necessariu in a cunfigurazione, specificà i paràmetri per questu, riavvia PostgreSQL è pudete aduprà. (Nota. Sicondu a distribuzione, in e versioni recenti di contrib u pacchettu face parte di u pacchettu principale).
- È ci sò cuntributi micca ufficiali. Ùn sò micca furniti cù a distribuzione standard PostgreSQL. Deve esse compilati o installati cum'è una biblioteca. L'opzioni ponu esse assai diffirenti, secondu ciò chì u sviluppatore di sta cuntribuzione non ufficiale hè ghjuntu.
Questa diapositiva mostra tutte e viste (VIEWs) è alcune di e funzioni chì sò dispunibili in PostgreSQL 9.4. Comu pudemu vede, ci sò assai di elli. È hè abbastanza faciule d'avè cunfusu s'è vo avete sperimentatu per a prima volta.
Tuttavia, s'è no pigghiamu u ritrattu nanzu Как тратится время на PostgreSQL è cumpatibili cù sta lista, avemu sta stampa. Ogni vista (VIEWs), o ogni funzione, pudemu usà per un scopu o un altru per ottene e statistiche appropritate quandu avemu PostgreSQL in esecuzione. È pudemu digià avè qualchì infurmazione nantu à u funziunamentu di u subsistema.
A prima cosa chì guardemu hè pg_stat_database. Comu pudemu vede, questu hè una rappresentanza. Contene assai infurmazione. L'infurmazione più variata. È dà una cunniscenza assai utile di ciò chì avemu in a basa di dati.
Chì pudemu piglià da quì ? Cuminciamu cù e cose più simplici.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;A prima cosa chì pudemu vede hè u percentualità di cache hit. U percentualità di cache hit hè una metrica utile. Permette di stimà quantu dati hè pigliatu da a cache di buffers spartuti, è quantu hè lettu da u discu.
Hè chjaru chì u più cache hit avemu, u megliu. Evaluemu sta metrica in percentuale. E, per esempiu, se avemu un percentinu di sti cache hits più grande di 90%, allora questu hè bonu. S'ellu scende sottu à 90%, allora ùn avemu micca abbastanza memoria per mantene a testa calda di dati in memoria. È per aduprà sta dati, PostgreSQL hè furzatu à accede à u discu è questu hè più lento chè se i dati sò stati letti da a memoria. È avete bisognu di pensà à l'aumentu di a memoria: o aumentà i buffers spartuti, o cresce a memoria di ferru (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;Chì altru pò esse pigliatu da questa prestazione? Pudete vede l'anomali chì si trovanu in a basa di dati. Chì hè mostratu quì? Ci sò commits, rollbacks, creazione di fugliali tempuranee, a so dimensione, deadlocks è cunflitti.
Pudemu aduprà sta dumanda. Questu SQL hè abbastanza simplice. È pudemu vede sti dati per noi stessi.
È quì sò i valori limite. Fighjemu u rapportu di commits è rollbacks. Commits hè una cunferma successu di a transazzione. Rollbacks hè un rollback, vale à dì chì a transazzione hà fattu qualchì travagliu, strained the database, cunzidiratu qualcosa, è dopu un fallimentu hè accadutu, è i risultati di a transazzione sò scartati. i.e. u numaru di rollbacks in constantemente crescente hè male. È duvete in qualchì manera evitari, è edità u codice per chì questu ùn succede micca.
I cunflitti sò ligati à a replicazione. È anu da esse evitati ancu. Sì avete qualchì quistione chì sò eseguite nantu à a replica è i cunflitti sorgi, allora avete bisognu di analizà questi cunflitti è vede ciò chì succede. I dettagli ponu esse truvati in i logs. È risolve i cunflitti per chì e richieste di l'applicazione funzionanu senza errori.
Deadlocks hè ancu una mala situazione. Quandu e dumande cumpetenu per i risorsi, una dumanda accede à una risorsa è pigliò a serratura, a seconda dumanda accede à a seconda risorsa è pigliò ancu a serratura, è dopu e duie dumande accede à e risorse di l'altru è bluccatu aspittendu chì u vicinu per liberà a serratura. Questa hè ancu una situazione problematica. Hanu bisognu à esse indirizzati à u livellu di l'applicazioni di riscrittura è di serializà l'accessu à e risorse. E se vi vede chì i vostri blocchi sò in constantemente crescente, avete bisognu di guardà i dettagli in i logs, analizà e situazioni chì sò ghjunti è vede quale hè u prublema.
I schedarii temporali (temp_files) sò ancu cattivi. Quandu una dumanda di l'utilizatori ùn hà micca abbastanza memoria per allughjà i dati operativi, tempuranee, crea un schedariu nantu à u discu. È tutte l'operazioni ch'ellu puderia esse realizatu in un buffer temporale in memoria cumincianu à fà digià in u discu. Hè lentu. Questu aumenta u tempu di esecuzione di a dumanda. È u cliente chì hà mandatu una dumanda à PostgreSQL riceverà una risposta un pocu dopu. Se tutte queste operazioni sò realizate in memoria, Postgres risponderà assai più veloce è u cliente aspittà menu.
pg_stat_bgwriter - Questa vista descrive l'operazione di dui sottosistemi di fondo PostgreSQL: checkpointer и background writer.
Per principià, analizzemu i punti di cuntrollu, u cusì chjamatu. checkpoints. Chì sò i punti di cuntrollu? Un puntu di cuntrollu hè una pusizioni in u logu di transazzione chì indica chì tutti i cambiamenti di dati impegnati in u logu sò sincronizzati bè cù e dati nantu à u discu. U prucessu, secondu a carica di travagliu è i paràmetri, pò esse longu è cunsiste principalmente di sincronizà e pagine brutte in buffers spartuti cù i schedarii di dati nantu à u discu. Chì ghjè per ? Se PostgreSQL accede à u discu tuttu u tempu è pigliate dati da quì, è scrive dati nantu à ogni accessu, saria lentu. Per quessa, PostgreSQL hà un segmentu di memoria, a dimensione di quale dipende di i paràmetri in a cunfigurazione. Postgres attribuisce dati operativi in questa memoria per ulteriore trasfurmazioni o dumande. In u casu di richieste di cambiamentu di dati, sò cambiati. È avemu duie versioni di e dati. Unu hè in memoria, l'altru hè in discu. È periodicamente avete bisognu di sincronizà sti dati. Avemu bisognu di ciò chì hè cambiatu in memoria per esse sincronizatu à u discu. Questu hè bisognu di un puntu di cuntrollu.
Checkpoint passa per i buffer condivisi, marca e pagine brutte chì sò necessarii per u checkpoint. Allora principia a seconda passata per i buffers spartuti. È e pagine chì sò marcate per u checkpoint, hà digià sincronizza. Cusì, i dati sò digià sincronizati cù u discu.
Ci hè dui tipi di punti di cuntrollu. Un puntu di cuntrollu hè eseguitu nantu à u timeout. Stu puntu di cuntrollu hè utile è bonu - checkpoint_timed. È ci sò punti di cuntrollu nantu à a dumanda - checkpoint required. Un tali checkpoint si verifica quandu avemu un record di dati assai grande. Avemu arregistratu assai logs di transazzione. È PostgreSQL crede chì deve sincronizà tuttu questu u più prestu pussibule, fate un puntu di cuntrollu è andate avanti.
È s'è vo guardate à e statistiche pg_stat_bgwriter è vede ciò chì avete checkpoint_req hè assai più grande di checkpoint_timed, allora questu hè male. Perchè male? Questu significa chì PostgreSQL hè sottumessu à un stress constante quandu hà bisognu di scrive dati à u discu. Checkpoint by timeout hè menu stressante è hè eseguitu secondu u calendariu internu è, per esse, allungatu cù u tempu. PostgreSQL hà a capacità di mette in pausa in u travagliu è micca strincà u sottosistema di discu. Questu hè utile per PostgreSQL. E dumande chì sò eseguite durante u puntu di cuntrollu ùn anu micca stress da u fattu chì u sottosistema di discu hè occupatu.
È ci sò trè paràmetri per aghjustà u puntu di cuntrollu:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Permettenu di cuntrullà u funziunamentu di i punti di cuntrollu. Ma ùn mi stendu micca nantu à elli. A so influenza hè un tema separatu.
Attenti: A versione 9.4 cunsiderata in u rapportu ùn hè più pertinente. In e versioni muderni di PostgreSQL, u paràmetru checkpoint_segments rimpiazzatu da paràmetri min_wal_size и max_wal_size.
U prossimu subsistema hè u scrittore di fondo - background writer. Chì faci ? Corre constantemente in un ciclu senza fine. Scanseghja e pagine in buffer condivisi è sguassate e pagine sporche chì trova à u discu. In questu modu, aiuta à u checkpointer per fà menu travagliu durante u checkpointing.
Chì altru hè necessariu per? Prevede a necessità di pagine pulite in buffers spartuti si sò subitu richiesti (in grande quantità è immediatamente) per allughjà dati. Eppo supponi chì una situazione hè ghjunta quandu e pagine pulite sò state richieste per compie una dumanda è sò digià in buffer spartuti. Postgres backend solu li piglia è li usa, ùn hà micca bisognu di pulisce nunda ellu stessu. Ma s'ellu di colpu ùn ci sò micca tali pagine, u backend si mette in pausa è principia à circà e pagine per scacciallu à u discu è piglialli per i so bisogni - chì affetta negativamente u tempu di a dumanda attualmente in esecuzione. Se vi vede chì avete un paràmetru maxwritten_clean grande, questu significa chì u scrittore di fondo ùn hè micca fattu u so travagliu è avete bisognu di aumentà i paràmetri bgwriter_lru_maxpagescusì ch'ellu pò fà più travagliu in un ciclu, sguassate più pagine.
È un altru indicatore assai utile hè buffers_backend_fsync. I backend ùn facenu micca fsync perchè hè lento. Passanu fsync in u checkpointer di stack IO. U checkpointer hà a so propria fila, prucessa periodicamente fsync è sincronizza e pagine in memoria cù i schedari nantu à u discu. Se a fila di checkpointer hè grande è piena, allora u backend hè obligatu à fà fsync stessu è questu rallenta u backend., vale à dì chì u cliente riceverà una risposta più tardi ch'ellu puderia. Se vi vede chì avete stu valore più grande di cero, allora questu hè digià un prublema è avete bisognu di attentu à i paràmetri di u scrittore di fondo è ancu evaluà u rendiment di u sottusistema di discu.
Attenti: _U testu seguente descrive i punti di vista statistichi assuciati à a replicazione. A maiò parte di i nomi di vista è di funzioni sò stati rinominati in Postgres 10. L'essenza di i rinominazioni era di rimpiazzà. xlog nantu wal и location nantu lsn in nomi di funzioni / vede, etc. Esempiu particulare, funzione pg_xlog_location_diff() fù rinominatu à pg_wal_lsn_diff()._
Avemu ancu assai quì. Ma avemu solu bisognu di l'articuli ligati à u locu.
Se vedemu chì tutti i valori sò uguali, allora questu hè l'ideale è a rèplica ùn resta micca daretu à u maestru.
Questa pusizione esadecimale quì hè a pusizione in u logu di transazzione. Aumenta constantemente s'ellu ci hè una certa attività in a basa di dati: inserisce, sguassate, etc.
сколько записано 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());Sì sti cosi sò diffirenti, allora ci hè un tipu di lag. Lag hè u lag di a replica da u maestru, vale à dì chì e dati sò diffirenti trà i servitori.
Ci sò trè motivi per u ritardu:
- Hè u sottosistema di discu chì ùn pò micca trattà di scrittura di sincronia di file.
- Quessi sò pussibuli errori di rete, o overload di rete, quandu i dati ùn anu micca tempu per ghjunghje à a replica è ùn pò micca ripruduce.
- È u processatore. U processatore hè un casu assai raru. È l'aghju vistu duie o trè volte, ma pò ancu succede.
E quì sò trè dumande chì ci permettenu di utilizà statistiche. Pudemu stimà quantu hè registratu in u nostru logu di transazzione. Ci hè una tale funzione pg_xlog_location_diff è pudemu stimà u lag di replicazione in byte è seconde. Avemu ancu aduprà u valore da questa vista (VIEWs) per questu.
Nutate bè: _Invece di pg_xlog_locationdiff() funzione, pudete aduprà l'operatore di sottrazione è resta un locu da un altru. Cunfortu.
Cù un lag, chì hè in seconde, ci hè un mumentu. Se ùn ci hè micca attività nantu à u maestru, a transazzione era quì circa 15 minuti fà è ùn ci hè micca attività, è se guardemu stu lag nantu à a replica, vedemu un lag di 15 minuti. Questu vale a pena ricurdà. È pò purtà à un stupore quandu avete vistu stu lag.
pg_stat_all_tables hè una altra vista utile. Mostra statistiche nantu à e tavule. Quandu avemu tàvule in a basa di dati, ci hè una certa attività cun ellu, alcune azzioni, pudemu avè sta infurmazione 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;A prima cosa chì pudemu guardà hè scans sequential table. U numeru stessu dopu à sti passaghji ùn hè micca necessariamente male è ùn indica micca chì avemu bisognu di fà qualcosa digià.
Tuttavia, ci hè una seconda metrica - seq_tup_read. Questu hè u nùmeru di fila restituita da a scansione sequenziale. Se u numeru mediu supera 1, 000, 10, 000, allora questu hè digià un indicatore chì pudete avè bisognu di custruisce un indexu in qualchì locu per chì l'accessi sò per indice, o hè pussibule ottimisà e dumande chì utilizanu tali scans sequenziali in modu chì questu ùn succede micca.
Un esempiu simplice - dicemu chì una dumanda cù un grande OFFSET è LIMIT vale a pena. Per esempiu, 100 000 fila in una tavula sò scanned è dopu chì 50 000 fila necessaria sò pigliate, è e fila scanned precedente sò scartate. Questu hè ancu un male casu. E tali richieste deve esse ottimisate. È quì hè una dumanda SQL cusì simplice nantu à quale pudete vede è evaluà 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;E dimensioni di a tavula ponu ancu esse ottenute usendu sta tabella è utilizendu funzioni supplementari pg_total_relation_size(), pg_relation_size().
In generale, ci sò metacumandamenti dt и di, chì pudete aduprà in PSQL è vede ancu e dimensioni di tavulinu è indici.
In ogni casu, l'usu di e funzioni ci aiuta à guardà e dimensioni di i tavulini, ancu piglià in contu l'indici, o senza piglià in contu l'indici, è digià fà qualchì stima basatu annantu à a crescita di a basa di dati, vale à dì cumu si cresce cun noi, cù chì intensità, è digià tirate qualchi cunclusioni nantu à l'optimizazione di dimensionamentu.
Scrivite attività. Cosa hè un record? Fighjemu l'operazione UPDATE - l'operazione di aghjurnà e fila in a tavula. In fatti, l'aghjurnamentu hè duie operazioni (o ancu più). Questu hè inseritu una nova versione di fila è marcà a vechja versione di fila cum'è obsoleta. In seguitu, l'autovacuum vinarà è pulisce queste versioni obsolete di e linee, marcate stu locu cum'è dispunibule per a reutilizazione.
Inoltre, l'aghjurnamentu ùn hè micca solu per aghjurnà una tavola. Hè sempre una aghjurnazione di l'indici. Se tenete assai indici nantu à a tavula, allora cù l'aghjurnamentu, tutti l'indici in quale participà i campi aghjurnati in a query anu da esse ancu aghjurnati. Questi indici anu ancu avè versioni di fila obsoleti chì anu da esse puliti.
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;È per via di u so disignu, UPDATE hè una operazione di pesu pesu. Ma ponu esse facilitate. Manghja hot updates. Sò apparsu in a versione PostgreSQL 8.3. È chì hè questu? Questa hè una aghjurnazione ligera chì ùn provoca micca a ricustruzzione di l'indici. Hè, avemu aghjurnatu u recordu, ma solu u record in a pagina (chì appartene à a tavula) hè statu aghjurnatu, è l'indici sempre puntanu à u listessu record in a pagina. Ci hè un pocu di una logica cusì interessante di u travagliu, quandu vene un vacuum, allora hà sti catene hot ricustruisce è tuttu cuntinueghja à travaglià senza aghjurnà l'indici, è tuttu succede cù menu perdi di risorse.
È quandu avete n_tup_hot_upd grande, hè assai bonu. Questu significa chì l'aghjurnamenti ligeri prevalenu è questu hè più prezzu per noi in termini di risorse è tuttu hè bè.
ALTER TABLE table_name SET (fillfactor = 70);Cumu aumentà u voluminu hot updateov? Pudemu aduprà fillfactor. Determina a dimensione di u spaziu liberu riservatu quandu si riempie una pagina in una tavula cù INSERT. Quandu l'inserzioni vanu à a tavula, riempianu cumplettamente a pagina, ùn lasciate micca spaziu vacanti in questu. Allora una nova pagina hè evidenziata. I dati sò riempiti di novu. È questu hè u cumpurtamentu predeterminatu, fillfactor = 100%.
Pudemu stabilisce u fillfactor à 70%. Questu hè, cù inserti, una nova pagina hè stata attribuita, ma solu u 70% di a pagina hè stata piena. È avemu 30% in riserva. Quandu avete bisognu di fà un aghjurnamentu, u più prubabilmente succede in a listessa pagina, è a nova versione di a fila si mette in a stessa pagina. È hot_update serà fattu. Questu facenu più faciule per scrive nantu à e tavule.
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));Fila d'autovacuum. Autovacuum hè un tali subsistema per quale ci sò assai pochi statistiche in PostgreSQL. Pudemu vede solu in e tavule in pg_stat_activity quantu vacuums avemu in u mumentu. Tuttavia, hè assai difficiuli di capisce quante tavule in a fila hà in muvimentu.
Nutate bè: _Da Postgres 10, a situazione cù u seguimentu di u vacuum vacuum hà migliuratu assai - a vista pg_stat_progress hè apparsavacuum, chì simplificà assai u prublema di u surviglianza autovacuum.
Pudemu aduprà sta dumanda simplificata. È pudemu vede quandu u vacuum deve esse fattu. Ma, cumu è quandu deve principià u vacuum? Quessi sò i vechji versioni di e corde chì aghju parlatu prima. L'aghjurnamentu hè accadutu, a nova versione di a fila hè stata inserita. Una versione obsoleta di a stringa hè apparsa. Table pg_stat_user_tables ci hè un tali paràmetru n_dead_tup. Mostra u numeru di fila "morti". È quandu u numeru di fila morta hè diventatu più di un certu limitu, un autovacuum vinarà à a tavula.
È cumu hè calculatu stu limitu? Questu hè un percentinu assai specificu di u numeru tutale di fila in a tavula. Ci hè un paràmetru autovacuum_vacuum_scale_factor. Definisce u percentuale. Diciamu 10% + ci hè un limitu di basa supplementu di 50 linee. È chì passa ? Quandu avemu più fila morta di "10% + 50" di tutte e fila in a tavula, mettemu a tavula 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, ci hè un puntu. Soglie basi per i paràmetri av_base_thresh и av_scale_factor pò esse attribuitu individualmente. È, per quessa, u limitu ùn serà micca globale, ma individuale per a tavula. Dunque, per calculà, ci hè bisognu di utilizà scherzi è trucchi. È s'è vo site interessatu, pudete guardà l'esperienza di i nostri culleghi di Avito (u ligame nantu à a slide hè invalidu è hè statu aghjurnatu in u testu).
Hanu scrittu per chì piglia in contu sti cosi. Ci hè una tovaglia nantu à dui fogli. Ma cunsidereghja currettamente è abbastanza efficace ci permette di valutà induve avemu bisognu di assai vacuum per i tavulini, induve ci hè pocu.
Chì pudemu fà per questu? Se avemu una longa fila è l'autovacuum ùn pò micca affruntà, allora pudemu aumentà u numeru di travagliadori di vacuum, o simpricimenti fà u vacuum più aggressivu.in modu chì attiva prima, processa a tavola in pezzi chjuchi. È cusì a fila diminuirà. - A cosa principale quì hè di monitorà a carica nantu à i dischi, perchè. U vacuum ùn hè micca liberu, ancu s'è cù l'avventu di i dispositi SSD / NVMe, u prublema hè diventatu menu notu.
pg_stat_all_indexes hè statistiche nantu à l'indici. Ella ùn hè micca grande. È pudemu avè infurmazione nantu à l'usu di l'indici da ellu. È per esempiu, pudemu stabilisce quale indici avemu extra.
Cume aghju digià dettu, L'aghjurnamentu ùn hè micca solu aghjurnà e tavule, ma ancu aghjurnà l'indici. In cunsiquenza, se avemu assai indici nantu à a tavula, allora quandu aghjurnà e fila in a tavula, l'indici di i campi indiziati anu ancu esse aghjurnati, è s'ellu avemu indici inutilizati per i quali ùn ci sò micca scans d'indici, allora pendenu cun noi cum'è ballast. È avete bisognu di caccià elli. Per questu avemu bisognu di un campu idx_scan. Fighjemu solu u numeru di scans d'indici. Se l'indici anu scans cero nantu à un periudu relativamente longu di almacenamiento di statistiche (almenu 2-3 simane), allura u più prubabilmente questi sò indici cattivi, avemu bisognu di sguassà.
Nutate bè: Quandu cercate indici inutilizati in u casu di clusters di replicazione in streaming, avete bisognu di verificà tutti i nodi di u cluster, perchè statistiche ùn sò micca glubale, è se l'indici ùn hè micca utilizatu nantu à u maestru, allora pò esse usatu nantu à repliche (se ci hè una carica).
Dui ligami:
Quessi sò esempi di quistione più avanzati per cumu cercà l'indici inutilizati.
U sicondu ligame hè una dumanda piuttostu interessante. Ci hè una logica assai micca triviale in questu. U cunsigliu per rivisione.
Chì altru deve esse riassuntu da indici ?
-
L'indici inutilizati sò cattivi.
-
Ocupanu u spaziu.
-
Rallenta l'operazione di aghjurnamentu.
-
Travagliu extra per u vacuum.
Se sguassemu l'indici inutilizati, allora faremu solu a basa di dati megliu.
A prossima vista hè pg_stat_activity. Questu hè un analogu di l'utilità ps, solu in PostgreSQL. Se ps'Ohm, guardate i prucessi in u sistema operatore, allora pg_stat_activity vi mostrarà l'attività in PostgreSQL.
Chì pudemu piglià da quì ?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Pudemu vede l'attività generale chì succede in a basa di dati. Pudemu fà una nova implementazione. Tuttu hà splutatu quì, novi cunnessione ùn sò micca accettati, l'errori si versanu in l'applicazione.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Pudemu eseguisce una dumanda cum'è questu è vede u percentuale tutale di cunnessione relative à u limitu massimu di cunnessione è vede quale avemu a più cunnessione. È in questu casu datu, vedemu quellu utilizatore cron_role hà apertu 508 cunnessione. È qualcosa hè accadutu à ellu. Avete bisognu di trattà cun ellu è vede. È hè abbastanza pussibule chì questu hè un certu numaru di cunnessione anomalu.
Se avemu una carica OLTP, e dumande duveranu esse veloci, assai veloci, è ùn deve esse dumande longu. Tuttavia, s'ellu ci sò dumande longu, allora in u cortu termini ùn ci hè nunda di preoccupari, ma à longu andà, e dumande longu dannu a basa di dati, aumentanu l'effettu bloat di e tavule quandu si trova a frammentazione di a tavola. Sia e dumande gonfiate è longu devenu esse 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;Per piacè nutate: cù una tale dumanda, pudemu definisce dumande longu è transazzione. Avemu aduprà a funzione clock_timestamp() per determinà u tempu di travagliu. Longe dumande chì avemu trovu, pudemu ricurdà, eseguisce explain, fighjate i piani è in qualchì manera ottimisimu. Tiriamu l'attuale dumande longu è campemu.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';E transazzioni cattivi sò inattivi in transazzione è inattivi in transazzione (abortate).
Cosa significa? E transacciones anu parechje stati. È unu di sti stati pò piglià in ogni mumentu. Ci hè un campu per definisce i stati state in questa vista. È avemu aduprà per determinà u statu.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';E, cum'è aghju dettu sopra, sti dui stati idle in transazzione è idle in transazzione (aborted) sò cattivi. Chì ghjè ? Questu hè quandu l'applicazione hà apertu una transazzione, hà fattu alcune azzioni è andò in a so attività. A transazzione ferma aperta. Impicca, ùn succede nunda in questu, pigghia una cunnessione, chjude nantu à fila cambiata è potenzalmentu ancu aumenta u bloat di altre tavule, per via di l'architettura di u mutore transazionale Postrges. E tali transazzioni anu ancu esse sparati, perchè sò preghjudiziu in generale, in ogni casu.
Se vi vede chì avete più di 5-10-20 di elli in a vostra basa di dati, allora avete bisognu di preoccupari è cumincià à fà qualcosa cun elli.
Quì avemu ancu aduprà per u tempu di calculu clock_timestamp(). Tiremu transazzione, ottimisimu l'applicazione.
Cumu l'aghju dettu sopra, i chjusi sò quandu duie o più transazzioni cumpetenu per una o un gruppu di risorse. Per questu avemu un campu waiting cun valore booleanu true o false.
True - questu significa chì u prucessu aspetta, qualcosa deve esse fattu. Quandu un prucessu aspetta, allora u cliente chì hà iniziatu u prucessu hè ancu aspittendu. U cliente in u navigatore si mette è aspetta ancu.
Attenti: _Partendu da Postgres 9.6, u campu waiting eliminatu è rimpiazzatu da dui campi più informativi wait_event_type и wait_event._
Chì deve fà? Sè vo vede veru per un bellu pezzu, allura tu avissi a caccià tali dumandesi. Avemu ghjustu sparà tali transazzioni. Scrivemu à i sviluppatori ciò chì deve esse ottimisatu in qualchì manera per chì ùn ci hè micca una corsa per i risorse. E poi i sviluppatori ottimisanu l'applicazione per chì questu ùn succede micca.
È un casu estremu, ma potenzialmente micca fatale hè l'ocurrenza di blocchi. Dui transazzione anu aghjurnatu duie risorse, dopu accede à elli di novu, digià à risorse opposte. PostgreSQL in questu casu piglia è spara a transazzione stessu per chì l'altru pò cuntinuà à travaglià. Questa hè una situazione senza fine è ùn si capisce micca. Dunque, PostgreSQL hè obligatu à piglià misure estreme.
E quì sò duie dumande chì permettenu di seguità i serrature. Avemu aduprà a vista pg_locks, chì vi permette di seguità serrature pesanti.
È u primu ligame hè u testu di a dumanda stessu. Hè abbastanza longa.
È u sicondu ligame hè un articulu nantu à i chjusi. Hè utile à leghje, hè assai interessante.
Allora chì vedemu? Avemu vede duie dumande. Transazzione cù ALTER TABLE hè una transazzione di bloccu. Hè cuminciatu, ma ùn hè micca finitu, è l'applicazione chì hà publicatu sta transazzione face altre cose in qualchì locu. È a seconda dumanda hè l'aghjurnamentu. Aspittà chì a tavola di l'alter finisci prima di cuntinuà u so travagliu.
Questu hè cumu pudemu scopre quale hà chjusu quale, quale tene quale, è pudemu trattà cun questu in più.
U prossimu modulu hè pg_stat_statements. Comu dissi, hè un modulu. Per aduprà, avete bisognu di carricà a so libreria in a cunfigurazione, riavvia PostgreSQL, installate u modulu (cù un cumandimu), è dopu avè una nova 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;Chì pudemu piglià da quì ? Se parlemu di cose simplici, pudemu piglià u tempu mediu di esecuzione di a dumanda. U tempu cresce, chì significa chì PostgreSQL risponde lentamente è qualcosa deve esse fattu.
Pudemu vede e transazzione di scrittura più attiva in a basa di dati chì cambianu dati in buffers spartuti. Vede quale aghjurnà o sguassate dati quì.
È pudemu solu fighjà diverse statistiche per queste richieste.
Мы pg_stat_statements utilizatu per custruisce rapporti. Resetemu e statistiche una volta à ghjornu. Accumulemu. Prima di resettate e statistiche a prossima volta, custruemu un rapportu. Eccu un ligame à u rapportu. Pudete fighjulà.
Chì facemu ? Calculemu e statistiche generale per tutte e dumande. Allora, per ogni dumanda, cuntamu a so cuntribuzione individuale à sta statistica generale.
È chì pudemu vede ? Pudemu vede u tempu d'esekzione tutale di tutte e dumande di un tipu particulari in u sfondate di tutte l'altri dumande. Pudemu guardà l'usu di CPU è I / O in relazione à a stampa generale. E digià per ottimisà sti richieste. Custruemu e dumande principali basate nantu à stu rapportu è avemu digià ottinutu à pensà à ciò chì ottimisimu.
Chì avemu daretu à e scene ? Ci sò ancu uni pochi di sottumissioni chì ùn aghju micca cunsideratu, perchè u tempu hè limitatu.
Ci sò pgstattuple hè ancu un modulu supplementu da u pacchettu standard di cuntribuzioni. Permette di valutà bloat tavulini, cusì chjamati. frammentazione di a tavola. E se a frammentazione hè grande, avete bisognu di sguassà, utilizate diverse arnesi. È funzione pgstattuple travaglia per un bellu pezzu. E più tavule, più longu u travagliu.
U prossimu cuntribuitu hè pg_buffercache. Permette di inspeccionà i buffers spartuti: quantu intensivamente è per quale tabelle e pagine di buffer sò utilizzate. È vi permette solu di guardà in i buffers spartuti è valutà ciò chì succede quì.
U prossimu modulu hè pgfincore. Permette di eseguisce operazioni di tavulinu à livellu bassu attraversu una chjama di sistema mincore(), vale à dì vi permette di carricà a tavola in buffers spartuti, o scaricate. È permette, frà altri cose, di inspeccionà a cache di a pagina di u sistema operatore, vale à dì quantu a tavula occupa in a cache di a pagina, in buffers spartuti, è solu permette di evaluà a carica nantu à a tavula.
U prossimu modulu hè pg_stat_kcache. Utilizeghja ancu a chjama di u sistema getrusage(). È eseguisce prima è dopu chì a dumanda hè eseguita. È in e statistiche ottenute, ci permette di stimà quantu a nostra dumanda spesa nantu à u discu I / O, vale à dì, l'operazioni cù u sistema di fugliale è guarda l'usu di u processatore. Tuttavia, u modulu hè ghjovanu (khe-khe) è per u so travagliu hè bisognu di PostgreSQL 9.4 è pg_stat_statements, chì aghju citatu prima.
-
A capacità di utilizà statistiche hè utile. Ùn avete bisognu di software di terzu partitu. Pudete vede, vede, fà qualcosa, fà.
-
L'usu di statistiche hè faciule, hè simplice SQL. Avete cullatu una dumanda, compilatu, mandatu, guardatu.
-
Statistiche aiutanu à risponde à e dumande. Sì avete dumande, turnate à statistiche - fighjate, fate cunclusioni, analizà i risultati.
-
È sperimentà. Un saccu di dumande, assai dati. Pudete sempre ottimisà qualchì dumanda esistente. Pudete fà a vostra propria versione di a dumanda chì vi cunvene megliu cà l'uriginale è aduprà.
referenze
Ligami validi chì sò stati truvati in l'articulu, basatu nantu à quale, eranu in u rapportu.
L'autore scrive più
(eng)
U cullettore di statistiche
Funzioni di Amministrazione di u Sistema
Moduli Contrib
Utilità SQL è esempi di codice SQL
Grazie à tutti per a vostra attenzione!
Source: www.habr.com