Transkripsyon rapò 2015 pa Alexey Lesovsky "Plonje byen fon nan estatistik entèn PostgreSQL"
Limit responsabilite nou nan men otè rapò a: Mon konstate ki sa rapor in date Novanm 2015 – plis ki 4an in pase e bokou letan in pase. Vèsyon 9.4 diskite nan rapò a pa sipòte ankò. Pandan 4 ane ki sot pase yo, 5 nouvo lage yo te lage nan ki yon anpil nan inovasyon, amelyorasyon ak chanjman konsènan estatistik yo te parèt, ak kèk nan materyèl la se demode epi yo pa enpòtan. Kòm mwen te revize, mwen te eseye make kote sa yo pou pa twonpe ou lektè a. Mwen pa t 'reekri kote sa yo, gen yon anpil nan yo, e kòm yon rezilta, yon rapò konplètman diferan pral vire soti.
DBMS PostgreSQL a se yon mekanis gwo, ak mekanis sa a konsiste de anpil sous-sistèm, travay kowòdone ki afekte dirèkteman pèfòmans DBMS la. Pandan operasyon an, estatistik ak enfòmasyon sou operasyon an nan eleman yo kolekte, ki pèmèt ou evalye efikasite nan PostgreSQL ak pran mezi pou amelyore pèfòmans. Sepandan, gen anpil enfòmasyon sa yo epi li prezante nan yon fòm olye senplifye. Pwosesis enfòmasyon sa a ak entèprete li se pafwa yon travay konplètman ki pa trivial, ak "zou" nan zouti ak sèvis piblik ka byen fasil konfonn menm yon DBA avanse.
Bon apremidi Non mwen se Aleksey. Kòm Ilya te di, mwen pral pale sou estatistik PostgreSQL.
Estatistik aktivite PostgreSQL. PostgreSQL gen de estatistik. Estatistik aktivite, ki pral diskite. Ak estatistik orè sou distribisyon done. Mwen pral pale espesyalman sou estatistik aktivite PostgreSQL, ki pèmèt nou jije pèfòmans ak yon jan kanmenm amelyore li.
M ap di w kijan pou w itilize estatistik efektivman pou rezoud yon varyete pwoblèm ou genyen oswa ou ka genyen.
Ki sa ki pa pral nan rapò a? Nan rapò a, mwen pa pral manyen sou estatistik yo nan orè a, paske. sa a se yon sijè separe pou yon rapò separe sou fason done yo estoke nan baz done a ak ki jan planifikatè rechèch la vin yon lide sou karakteristik yo kalitatif ak quantitative nan done sa yo.
Epi pa pral gen okenn revizyon zouti, mwen pa pral konpare yon pwodwi ak yon lòt. Pa pral gen piblisite. Ann lage sa.
Mwen vle montre w ke itilize estatistik itil. Li nesesè. Sèvi ak li san pè. Tout sa nou bezwen se SQL plenn ak yon konesans debaz nan SQL.
Epi nou pral pale sou ki estatistik yo chwazi pou rezoud pwoblèm.
Si nou gade nan PostgreSQL epi kouri yon lòd sou sistèm operasyon an pou wè pwosesis yo, nou pral wè yon "bwat nwa". Nou pral wè kèk pwosesis ki fè yon bagay, ak non nou ka apeprè imajine sa yo ap fè la, sa yo ap fè. Men, an reyalite, sa a se yon bwat nwa, nou pa ka gade andedan.
Nou ka gade chaj CPU a nan top, nou ka wè itilizasyon memwa pa kèk sèvis piblik sistèm, men nou p ap kapab gade andedan PostgreSQL. Pou sa nou bezwen lòt zouti.
Epi kontinye pi lwen, mwen pral di w kote tan an pase. Si nou reprezante PostgreSQL nan fòm yon konplo konsa, lè sa a li pral posib pou reponn kote tan an pase. Sa yo se de bagay: li se pwosesis la nan demann kliyan ki soti nan aplikasyon yo ak travay yo background ke PostgreSQL fè pou kenbe li kouri.
Si nou kòmanse gade nan kwen an tèt gòch, nou ka wè ki jan demann kliyan yo trete. Demann lan soti nan aplikasyon an epi yo louvri yon sesyon kliyan pou plis travay. Se demann lan pase bay orè a. Planifikatè a bati yon plan rechèch. Voye li pi lwen pou ekzekisyon. Gen kèk kalite blòk I / O done ki asosye ak tab ak endèks. Done ki nesesè yo li soti nan disk nan memwa nan yon zòn espesyal ki rele "tanpon pataje". Rezilta rechèch yo, si yo se mizajou, efase, yo anrejistre nan boutèy tranzaksyon an nan WAL. Gen kèk enfòmasyon estatistik ale nan boutèy la oswa pèseptè estatistik. Ak rezilta demann lan bay kliyan an tounen. Apre sa, kliyan an ka repete tout bagay ak yon nouvo demann.
Ki sa nou genyen ak travay background ak pwosesis background? Nou gen plizyè pwosesis ki kenbe baz done a kouri ak kouri nòmalman. Pwosesis sa yo pral kouvri tou nan rapò a: sa yo se autovacuum, checkpointer, pwosesis ki gen rapò ak replikasyon, ekriven background. M ap touche chak nan yo pandan m ap rapòte.
Ki pwoblèm ki genyen ak estatistik?
- Anpil enfòmasyon. PostgreSQL 9.4 bay 109 mezi pou gade done estatistik yo. Sepandan, si baz done a estoke anpil tab, chema, baz done, Lè sa a, tout mezi sa yo pral gen yo dwe miltipliye pa kantite korespondan tab, baz done. Sa vle di, gen menm plis enfòmasyon. Epi li trè fasil pou nwaye ladan l.
- Pwoblèm nan pwochen se ke estatistik yo reprezante pa kontè. Si nou gade nan estatistik sa yo, nou pral wè kontè toujou ap ogmante. Men, si anpil tan te pase depi estatistik yo te reset, nou pral wè dè milya de valè. Epi yo pa di nou anyen.
- Pa gen istwa. Si ou gen yon kalite echèk, yon bagay tonbe 15-30 minit de sa, ou pa yo pral kapab sèvi ak estatistik yo ak wè sa ki te pase 15-30 minit de sa. Sa a se pwoblèm.
- Mank yon zouti ki bati nan PostgreSQL se yon pwoblèm. Devlopè nwayo yo pa bay okenn sèvis piblik. Yo pa gen anyen konsa. Yo jis bay estatistik nan baz done a. Sèvi ak li, fè yon demann pou li, tou sa ou vle, Lè sa a, fè li.
- Depi pa gen okenn zouti ki bati nan PostgreSQL, sa lakòz yon lòt pwoblèm. Anpil zouti twazyèm pati. Chak konpayi ki gen plis oswa mwens dirèk men ap eseye ekri pwòp pwogram li yo. Epi kòm yon rezilta, kominote a gen anpil zouti ke ou ka itilize pou travay ak estatistik. Ak nan kèk zouti gen kèk karakteristik, nan lòt zouti pa gen okenn lòt karakteristik, oswa gen kèk karakteristik nouvo. Ak yon sitiyasyon rive ke ou bezwen sèvi ak de, twa, oswa kat zouti ki sipèpoze youn ak lòt epi ki gen fonksyon diferan. Sa a trè anmèdan.
Ki sa ki soti nan sa a? Li enpòtan pou kapab pran estatistik dirèkteman pou pa depann de pwogram yo, oswa yon jan kanmenm amelyore pwogram sa yo tèt ou: ajoute kèk fonksyon pou jwenn benefis ou.
Epi ou bezwen konesans debaz nan SQL. Pou jwenn kèk done nan estatistik, ou bezwen fè demann SQL, sa vle di ou bezwen konnen ki jan chwazi, rantre nan yo te fè.
Estatistik di nou plizyè bagay. Yo ka divize an kategori.
- Premye kategori a se evènman k ap fèt nan baz done a. Sa a se lè kèk evènman rive nan baz done a: yon rechèch, yon aksè tab, autovacuum, komèt, Lè sa a, sa yo se tout evènman. Kontwa ki koresponn ak evènman sa yo ap ogmante. Epi nou ka swiv evènman sa yo.
- Dezyèm kategori a se pwopriyete yo nan objè tankou tab, baz done. Yo gen pwopriyete. Sa a se gwosè tab yo. Nou ka swiv kwasans lan nan tab, kwasans lan nan endèks. Nou ka wè chanjman nan dinamik.
- Ak twazyèm kategori a se tan ki pase sou evènman an. Demann se yon evènman. Li gen pwòp mezi espesifik li yo nan dire. Kòmanse isit la, fini isit la. Nou ka swiv li. Swa tan an nan lekti yon blòk soti nan disk oswa ekri. Bagay sa yo tou swiv.
Sous estatistik yo prezante jan sa a:
- Nan memwa pataje (tanpon pataje) gen yon segman pou mete done estatik la, gen tou kontè sa yo ki toujou ap ogmante lè sèten evènman rive, oswa kèk moman rive nan operasyon an nan baz done a.
- Tout kontè sa yo pa disponib pou itilizatè a epi yo pa menm disponib pou administratè a. Sa yo se bagay ki ba nivo. Pou jwenn aksè nan yo, PostgreSQL bay yon koòdone nan fòm fonksyon SQL. Nou ka fè seleksyon chwazi lè l sèvi avèk fonksyon sa yo epi jwenn kèk kalite metrik (oswa seri metrik).
- Sepandan, li pa toujou pratik pou itilize fonksyon sa yo, kidonk fonksyon yo se baz pou opinyon (VIEWs). Sa yo se tab vityèl ki bay estatistik sou yon subsistèm espesifik, oswa sou kèk seri evènman nan baz done a.
- Sa yo entegre opinyon (VIEWs) se koòdone itilizatè prensipal la pou travay ak estatistik. Yo disponib pa default san yo pa nenpòt lòt anviwònman, ou ka imedyatman itilize yo, gade, pran enfòmasyon ki soti nan la. E gen kontribisyon tou. Kontribisyon yo ofisyèl yo. Ou ka enstale pake postgresql-contrib (pa egzanp, postgresql94-contrib), chaje modil ki nesesè nan konfigirasyon an, presize paramèt pou li, rekòmanse PostgreSQL epi ou ka sèvi ak li. (Remak. Tou depan de distribisyon an, nan dènye vèsyon contrib pake a se yon pati nan pake prensipal la).
- Epi gen kontribisyon ofisyèl. Yo pa apwovizyone ak distribisyon estanda PostgreSQL. Yo dwe swa konpile oswa enstale kòm yon bibliyotèk. Opsyon yo ka trè diferan, tou depann de sa pwomotè a nan kontribisyon ofisyèl sa a te vini ak.
Slide sa a montre tout opinyon sa yo (VIEWS) ak kèk nan fonksyon sa yo ki disponib nan PostgreSQL 9.4. Kòm nou ka wè, gen anpil nan yo. Epi li trè fasil jwenn konfonn si w ap fè eksperyans li pou premye fwa.
Sepandan, si nou pran foto anvan an Как тратится время на PostgreSQL ak konpatib ak lis sa a, nou jwenn foto sa a. Chak View (VIEWs), oswa chak fonksyon, nou ka itilize pou yon objektif oswa yon lòt jwenn estatistik ki apwopriye yo lè nou gen PostgreSQL kouri. Epi nou ka deja jwenn kèk enfòmasyon sou operasyon an nan subsistèm nan.
Premye bagay nou pral gade se pg_stat_database. Kòm nou ka wè, sa a se yon reprezantasyon. Li genyen anpil enfòmasyon. Enfòmasyon ki pi varye. Epi li bay konesans trè itil sou sa nou genyen nan baz done a.
Ki sa nou ka pran de la? Ann kòmanse ak bagay ki pi senp yo.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Premye bagay nou ka gade se pousantaj frape kachèt la. Pousantaj frape Cache se yon metrik itil. Li pèmèt ou estime konbyen done yo pran nan kachèt tanpon pataje yo, ak konbyen yo li nan disk.
Li klè ke plis frape kachèt nou genyen, pi bon an. Nou evalye metrik sa a kòm yon pousantaj. Epi, pou egzanp, si nou gen yon pousantaj nan frape sa yo kachèt ki pi gran pase 90%, Lè sa a, sa a se yon bon bagay. Si li desann pi ba pase 90%, lè sa a nou pa gen ase memwa pou kenbe tèt cho done nan chonje. Ak nan lòd yo sèvi ak done sa yo, PostgreSQL fòse yo jwenn aksè nan disk la ak sa a se pi dousman pase si done yo te li nan memwa. Epi ou bezwen reflechi sou ogmante memwa: swa ogmante tanpon pataje, oswa ogmante memwa fè (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;Ki lòt bagay yo ka pran nan pèfòmans sa a? Ou ka wè anomali ki fèt nan baz done a. Ki sa ki montre la a? Gen komèt, rollbacks, kreyasyon dosye tanporè, gwosè yo, enpas ak konfli.
Nou ka sèvi ak demann sa a. SQL sa a trè senp. Epi nou ka wè done sa yo pou tèt nou.
Ak isit la yo se valè yo papòt. Nou gade nan rapò a nan komèt ak rollbacks. Commits se yon konfimasyon siksè nan tranzaksyon an. Rollbacks se yon rollback, sa vle di tranzaksyon an te fè kèk travay, fòse baz done a, konsidere kòm yon bagay, ak Lè sa a, yon echèk te fèt, ak rezilta yo nan tranzaksyon an yo jete. i.e. kantite Rollbacks toujou ap ogmante se move. Epi ou ta dwe yon jan kanmenm evite yo, epi modifye kòd la pou sa pa rive.
Konfli yo gen rapò ak replikasyon. Epi yo ta dwe evite tou. Si ou gen kèk demann ki egzekite sou kopi a ak konfli leve, Lè sa a, ou bezwen analize konfli sa yo ak wè sa k ap pase. Ou ka jwenn detay yo nan mòso bwa yo. Epi rezoud konfli pou demann aplikasyon an travay san erè.
Deadlocks se tou yon move sitiyasyon. Lè demann yo fè konpetisyon pou resous, yon sèl demann te jwenn aksè nan yon resous epi li te pran kadna a, dezyèm demann lan te jwenn aksè nan dezyèm resous la epi tou li te pran kadna a, ak Lè sa a, tou de demann yo te jwenn aksè nan resous youn ak lòt epi yo te bloke ap tann pou vwazen an lage kadna a. Sa a se tou yon sitiyasyon pwoblèm. Yo bezwen adrese nan nivo reekri aplikasyon yo ak seri aksè nan resous yo. Men, si ou wè ke deadlocks ou yo toujou ap ogmante, ou bezwen gade nan detay yo nan mòso bwa yo, analize sitiyasyon yo ki te parèt ak wè ki pwoblèm nan.
Fichye tanporè (temp_files) yo tou move. Lè yon demann itilizatè pa gen ase memwa pou akomode done operasyonèl, tanporè, li kreye yon dosye sou disk. Ak tout operasyon yo ke li te kapab fè nan yon tanpon tanporè nan memwa, li kòmanse fè deja sou disk la. Li dousman. Sa a ogmante tan an ekzekisyon demann. Ak kliyan an ki voye yon demann nan PostgreSQL pral resevwa yon repons yon ti kras pita. Si tout operasyon sa yo fèt an memwa, Postgres pral reponn pi vit epi kliyan an ap tann mwens.
pg_stat_bgwriter - View sa a dekri operasyon de sistèm background PostgreSQL: checkpointer и background writer.
Pou kòmanse, se pou yo analize pwen kontwòl yo, sa yo rele yo. checkpoints. Ki sa ki pòs kontwòl? Yon pòs se yon pozisyon nan jounal tranzaksyon an ki endike ke tout chanjman done yo komèt nan boutèy la yo avèk siksè senkronize ak done yo sou disk. Pwosesis la, tou depann de kantite travay la ak anviwònman, ka long epi sitou konsiste de senkronize paj sal nan tanpon pataje ak dosye done sou disk. Pou kisa li ye? Si PostgreSQL te jwenn aksè nan disk la tout tan an epi pran done ki soti nan la, epi ekri done sou chak aksè, li ta ralanti. Se poutèt sa, PostgreSQL gen yon segman memwa, gwosè a ki depann sou paramèt yo nan konfigirasyon an. Postgres asiyen done operasyonèl nan memwa sa a pou plis pwosesis oswa demann. Nan ka demann chanjman done yo, yo chanje. Epi nou jwenn de vèsyon done yo. Youn nan memwa, lòt la sou disk. Epi detanzantan ou bezwen senkronize done sa yo. Nou bezwen sa ki chanje nan memwa yo dwe senkronize nan disk. Sa a mande pou yon pòs.
Checkpoint ale nan tanpon pataje, make paj sal ke yo bezwen pou pòs. Lè sa a, li kòmanse dezyèm pas nan tanpon pataje. Ak paj sa yo ki make pou pòs, li deja senkronize yo. Kidonk, done yo senkronize deja ak disk la.
Gen de kalite pwen kontwòl. Yon pwen kontwòl egzekite sou timeout. Pwen kontwòl sa a itil ak bon - checkpoint_timed. Epi gen baraj sou demann - checkpoint required. Yon pòs konsa rive lè nou gen yon dosye done trè gwo. Nou anrejistre anpil mòso bwa tranzaksyon yo. Ak PostgreSQL kwè ke li bezwen senkronize tout bagay sa yo pi vit ke posib, fè yon pòs ak deplase sou.
Men, si ou gade nan estatistik yo pg_stat_bgwriter epi gade sa ou genyen checkpoint_req se pi gwo pase checkpoint_timed, Lè sa a, sa a se move. Poukisa move? Sa vle di ke PostgreSQL se anba estrès konstan lè li bezwen ekri done sou disk. Checkpoint pa timeout se mwens estrès epi li egzekite dapre orè entèn la epi, kòm li te, lonje sou tan. PostgreSQL gen kapasite pou fè yon poz nan travay epi yo pa souch sou sistèm nan disk. Sa itil pou PostgreSQL. Ak demann ke yo egzekite pandan pòs pa pral fè eksperyans estrès nan lefèt ke subsistèm nan disk se okipe.
Epi gen twa paramèt pou ajiste pòs la:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Yo pèmèt ou kontwole operasyon an nan pwen kontwòl. Men, mwen p'ap rete sou yo. Enfliyans yo se yon pwoblèm separe.
Avètisman: Vèsyon 9.4 konsidere nan rapò a pa enpòtan ankò. Nan vèsyon modèn nan PostgreSQL, paramèt la checkpoint_segments ranplase pa paramèt min_wal_size и max_wal_size.
Sou-sistèm kap vini an se ekriven background - background writer. Ki sa l ap fè? Li kouri toujou nan yon bouk kontinuèl. Li analize paj nan tanpon pataje epi li vide paj sal li jwenn sou disk. Nan fason sa a, li ede pòs la fè mwens travay pandan pòs.
Pou ki lòt bagay li bezwen? Li bay bezwen pou paj pwòp nan tanpon pataje si yo toudenkou oblije (nan gwo kantite ak imedyatman) pou akomode done yo. Sipoze yon sitiyasyon leve lè demann lan mande paj pwòp epi yo deja nan tanpon pataje. Postgres backend li jis pran yo epi sèvi ak yo, li pa oblije netwaye anyen tèt li. Men, si toudenkou pa gen okenn paj sa yo, backend la pran poz epi kòmanse chèche paj yo kole yo nan disk epi pran yo pou pwòp bezwen li yo - ki afekte negatif tan an nan demann lan kounye a egzekite. Si ou wè ke ou gen yon paramèt maxwritten_clean gwo, sa vle di ke ekriven an background pa fè travay li epi ou bezwen ogmante paramèt yo bgwriter_lru_maxpagespou li ka fè plis travay nan yon sèl sik, klè plis paj.
Ak yon lòt endikatè trè itil se buffers_backend_fsync. Backends pa fè fsync paske li dousman. Yo pase fsync moute IO stack pointer la. Pointer la gen pwòp keu li yo, li detanzantan trete fsync ak senkronize paj nan memwa ak dosye sou disk. Si keu checkpointer la gwo ak plen, Lè sa a, backend la oblije fè fsync tèt li epi sa ralanti backend la., sa vle di kliyan an pral resevwa yon repons pita pase li te kapab. Si ou wè ke ou gen valè sa a pi gran pase zewo, Lè sa a, sa a se deja yon pwoblèm ak ou bezwen peye atansyon sou anviwònman yo nan ekriven an background epi tou evalye pèfòmans nan subsistèm nan ki gen kapasite.
Avètisman: _Tèks sa a dekri opinyon estatistik ki asosye ak replikasyon. Pifò nan vi ak non fonksyon yo te chanje non nan Postgres 10. Sans nan chanje non yo te ranplase xlog sou wal и location sou lsn nan non fonksyon/wè, elatriye. Egzanp patikilye, fonksyon pg_xlog_location_diff() te chanje non an pg_wal_lsn_diff()._
Nou gen anpil isit la tou. Men, nou sèlman bezwen atik ki gen rapò ak kote.
Si nou wè ke tout valè yo egal, Lè sa a, sa a se ideyal ak kopi a pa lag dèyè mèt la.
Pozisyon egzadesimal sa a se pozisyon nan tranzaksyon an. Li toujou ap ogmante si gen kèk aktivite nan baz done a: foure, efase, elatriye.
сколько записано 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());Si bagay sa yo diferan, lè sa a gen kèk kalite lag. Lag se lag nan kopi a soti nan mèt la, sa vle di done yo diferan ant serveurs.
Gen twa rezon pou reta a:
- Se sous-sistèm disk la ki pa ka okipe dosye senkronizasyon ekri.
- Sa yo se erè rezo posib, oswa surcharge rezo, lè done yo pa gen tan rive jwenn kopi a epi li pa ka repwodui li.
- Ak processeur a. Processeur a se yon ka ki ra anpil. Apre sa, mwen te wè sa de oswa twa fwa, men sa ka rive tou.
Ak isit la gen twa demann ki pèmèt nou sèvi ak estatistik. Nou ka estime konbyen yo anrejistre nan jounal tranzaksyon nou an. Gen yon fonksyon konsa pg_xlog_location_diff epi nou ka estime dekalaj nan replikasyon an byte ak segonn. Nou menm tou nou itilize valè ki soti nan vi sa a (VIEWs) pou sa.
Remak: _Olye pg_xlog_locationdiff() fonksyon, ou ka itilize soustraksyon operatè a epi soustraksyon yon kote nan yon lòt. Konfòtab.
Avèk yon lag, ki se nan segonn, gen yon moman. Si pa gen okenn aktivite sou mèt la, tranzaksyon an te la apeprè 15 minit de sa epi pa gen okenn aktivite, epi si nou gade nan lag sa a sou kopi a, nou pral wè yon dekalaj nan 15 minit. Sa a se vo sonje. Epi li ka mennen nan yon stupèr lè ou gade lag sa a.
pg_stat_all_tables se yon lòt vi ki itil. Li montre estatistik sou tab yo. Lè nou gen tab nan baz done a, gen kèk aktivite ak li, kèk aksyon, nou ka jwenn enfòmasyon sa a nan View sa a.
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;Premye bagay nou ka gade se analiz tab sekans. Nimewo nan tèt li apre pasaj sa yo pa nesesèman move epi li pa endike ke nou bezwen fè yon bagay deja.
Sepandan, gen yon dezyèm metrik - seq_tup_read. Sa a se kantite ranje ki retounen nan eskanè sekans lan. Si nimewo an mwayèn depase 1, 000, 10, 000, Lè sa a, sa a se deja yon endikatè ke ou ka bezwen bati yon endèks yon kote pou ke aksè yo se pa endèks, oswa li posib pou optimize demann ki itilize analiz sekans sa yo pou ke sa pa rive.
Yon egzanp senp - an n di yon demann ak yon gwo OFFSET ak LIMIT vo li. Pa egzanp, 100 ranje nan yon tablo yo analize epi apre sa 000 ranje obligatwa yo pran, epi ranje anvan yo analize yo jete. Sa a se tou yon move ka. Ak demann sa yo bezwen optimize. Ak isit la se tankou yon rechèch SQL senp ki te sou ki ou ka wè li epi evalye nimewo yo resevwa.
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;Gwosè tab yo ka jwenn tou lè l sèvi avèk tab sa a ak lè l sèvi avèk fonksyon adisyonèl pg_total_relation_size(), pg_relation_size().
An jeneral, gen metakòmand dt и di, ki ou ka itilize nan PSQL epi tou wè gwosè tab ak endèks.
Sepandan, itilizasyon fonksyon yo ede nou gade gwosè tab yo, menm pran an kont endèks, oswa san yo pa pran an kont endèks, e deja fè kèk estimasyon ki baze sou kwasans baz done a, sa vle di kijan li grandi avèk nou, ak ki sa entansite, e deja tire kèk konklizyon sou optimize dimensionnement.
Ekri aktivite. Ki sa ki se yon dosye? Ann gade nan operasyon an UPDATE – operasyon an nan mete ajou ranje nan tablo a. An reyalite, aktyalizasyon se de operasyon (oswa menm plis). Sa a se mete yon nouvo vèsyon ranje epi make ansyen vèsyon an ranje kòm demode. Apre sa, otovakyòm pral vini ak netwaye vèsyon sa yo demode nan liy yo, make kote sa a kòm ki disponib pou itilize ankò.
Epitou, aktyalizasyon se pa sèlman sou mete ajou yon tab. Li toujou yon aktyalizasyon endèks. Si ou gen anpil endèks sou tab la, Lè sa a, ak aktyalizasyon, tout endèks kote jaden yo mete ajou nan rechèch la patisipe ap bezwen tou mete ajou. Endis sa yo pral gen tou vèsyon ranje demode ki pral bezwen netwaye.
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;Ak akòz konsepsyon li yo, UPDATE se yon operasyon pwa lou. Men, yo ka vin pi fasil. Manje hot updates. Yo te parèt nan PostgreSQL vèsyon 8.3. E kisa sa ye? Sa a se yon aktyalizasyon ki lejè ki pa lakòz endèks yo dwe rebati. Sa vle di, nou mete ajou dosye a, men se sèlman dosye ki nan paj la (ki fè pati tablo a) mete ajou, epi endis yo toujou montre menm dosye a nan paj la. Gen yon ti jan nan yon lojik enteresan nan travay, lè yon vakyòm vini, Lè sa a, li gen chenn sa yo. hot rekonstwi ak tout bagay ap kontinye travay san yo pa mete ajou endis yo, ak tout bagay rive ak mwens fatra nan resous.
E lè ou genyen n_tup_hot_upd gwo, li trè bon. Sa vle di ke mizajou ki lejè domine e sa a pi bon mache pou nou an tèm de resous ak tout bagay anfòm.
ALTER TABLE table_name SET (fillfactor = 70);Ki jan yo ogmante volim hot updateov? Nou ka itilize fillfactor. Li detèmine gwosè espas lib rezève lè w ap ranpli yon paj nan yon tablo lè l sèvi avèk INSERT. Lè foure yo ale sou tab la, yo konplètman ranpli paj la, pa kite espas vid ladan l. Lè sa a, yon nouvo paj make. Done yo ranpli ankò. Epi sa a se konpòtman an default, fillfactor = 100%.
Nou ka mete fillfactor a 70%. Sa vle di, ak foure, yo te resevwa yon nouvo paj, men sèlman 70% nan paj la te ranpli. E nou reste 30% an rezèv. Lè ou bezwen fè yon aktyalizasyon, li pral gen plis chans rive sou menm paj la, ak nouvo vèsyon an nan ranje a ap anfòm sou menm paj la. Epi hot_update pral fèt. Sa fè li pi fasil pou ekri sou tab.
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));Queu Autovacuum. Autovacuum se tankou yon sous-sistèm pou ki gen anpil estatistik nan PostgreSQL. Nou ka sèlman wè nan tablo ki nan pg_stat_activity konbyen aspiratè nou genyen nan moman sa a. Sepandan, li trè difisil pou konprann konbyen tab nan keu la li genyen sou ale.
Remak: _Depi Postgres 10, sitiyasyon an ak swiv vakyòm vakyòm la amelyore anpil - gade pg_stat_progress la parèt.vakyòm, ki anpil senplifye pwoblèm nan nan siveyans otovakyòm.
Nou ka itilize rechèch senplifye sa a. Epi nou ka wè ki lè vakyòm lan ta dwe fèt. Men, ki jan ak ki lè vakyòm lan ta dwe kòmanse? Sa yo se vèsyon yo fin vye granmoun nan fisèl yo mwen te pale sou pi bonè. Mizajou te fèt, nouvo vèsyon ranje a te mete. Yon vèsyon demode nan fisèl la te parèt. Tablo pg_stat_user_tables gen yon paramèt konsa n_dead_tup. Li montre kantite ranje "mouri". Epi le pli vit ke kantite ranje ki mouri yo vin pi plis pase yon sèten papòt, yon autovacuum ap vin sou tab la.
Ak ki jan papòt sa a kalkile? Sa a se yon pousantaj trè espesifik nan kantite total ranje nan tablo a. Gen yon paramèt autovacuum_vacuum_scale_factor. Li defini pousantaj la. Ann di 10% + gen yon papòt baz adisyonèl nan 50 liy. Ak sa k ap pase? Lè nou gen plis ranje mouri pase "10% + 50" nan tout ranje nan tablo a, nou mete tab la sou otovakyòm.
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));Sepandan, gen yon pwen. Papòt debaz pou paramèt av_base_thresh и av_scale_factor ka plase endividyèlman. Epi, kòmsadwa, papòt la pa pral mondyal, men endividyèl pou tab la. Se poutèt sa, yo kalkile, gen ou bezwen sèvi ak ke trik nouvèl ak ke trik nouvèl. Men, si w enterese, ou ka gade eksperyans kòlèg nou yo nan Avito (lyen ki sou glisad la pa valab epi li te mete ajou nan tèks la).
Yo te ekri pou ki pran an kont bagay sa yo. Gen yon twal pou pye sou de dra. Men, li konsidere kòrèkteman ak byen efektivman pèmèt nou evalye kote nou bezwen yon anpil nan vakyòm pou tab, kote ki gen ti kras.
Kisa nou ka fè sou li? Si nou gen yon keu long ak otovakyòm nan pa ka fè fas, Lè sa a, nou ka ogmante kantite travayè vakyòm, oswa tou senpleman fè vakyòm lan pi agresif.konsa ke li deklanche pi bonè, trete tab la an ti moso. Se konsa, keu la ap diminye. - Bagay pwensipal lan isit la se kontwole chaj la sou disk yo, paske. Bagay vakyòm lan pa gratis, byenke ak avènement de aparèy SSD / NVMe, pwoblèm nan te vin mwens aparan.
pg_stat_all_indexes se estatistik sou endèks yo. Li pa gwo. Epi nou ka jwenn enfòmasyon sou itilizasyon endèks nan li. Ak pou egzanp, nou ka detèmine ki endèks nou genyen siplemantè.
Kòm mwen te deja di, aktyalizasyon se pa sèlman mete ajou tab yo, li se tou mete ajou endèks yo. An konsekans, si nou gen yon anpil nan endèks sou tab la, Lè sa a, lè mete ajou ranje yo nan tablo a, endèks yo nan jaden yo endis yo bezwen tou mete ajou, ak si nou gen endèks ki pa itilize pou ki pa gen okenn analiz endèks, Lè sa a, yo kwoke avèk nou kòm ballast. Epi ou bezwen debarase m de yo. Pou sa nou bezwen yon jaden idx_scan. Nou jis gade kantite analiz endèks yo. Si endis yo gen zewo analiz sou yon peryòd relativman long nan depo estatistik (omwen 2-3 semèn), Lè sa a, gen plis chans sa yo se endis move, nou bezwen debarase m de yo.
Remak: Lè w ap chèche endis ki pa itilize nan ka grap replikasyon difizyon, ou bezwen tcheke tout nœuds gwoup la, paske estatistik yo pa mondyal, epi si endèks la pa itilize sou mèt la, Lè sa a, li ka itilize sou kopi (si gen yon chaj).
De lyen:
Sa yo se egzanp rechèch ki pi avanse pou kijan pou chèche endèks ki pa itilize yo.
Dezyèm lyen an se yon rechèch olye enteresan. Gen yon lojik trè ki pa trivial nan la. Mwen rekòmande li pou revizyon.
Ki lòt bagay yo ta dwe adisyone pa endèks?
-
Endis ki pa itilize yo se move.
-
Yo pran espas.
-
Ralanti operasyon aktyalizasyon yo.
-
Travay siplemantè pou vakyòm lan.
Si nou retire endis ki pa itilize yo, lè sa a nou pral sèlman fè baz done a pi byen.
View kap vini an se pg_stat_activity. Sa a se yon analogue nan sèvis piblik la ps, sèlman nan PostgreSQL. Si ps'Ohm ou gade pwosesis yo nan sistèm operasyon an, lè sa a pg_stat_activity pral montre w aktivite andedan PostgreSQL.
Kisa nou ka pran de la?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Nou ka wè aktivite an jeneral k ap pase nan baz done a. Nou ka fè yon nouvo deplwaman. Tout bagay te eksploze la, nouvo koneksyon yo pa aksepte, erè ap vide nan aplikasyon an.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Nou ka kouri yon rechèch tankou sa a epi wè pousantaj total koneksyon an parapò ak limit maksimòm koneksyon an epi wè ki moun nou gen plis koneksyon. Ak nan ka sa a bay, nou wè itilizatè sa a cron_role louvri 508 koneksyon. Epi yon bagay te rive l. Ou bezwen fè fas ak li epi wè. Epi li se byen posib ke sa a se kèk kalite anomali kantite koneksyon.
Si nou gen yon chaj OLTP, demann yo ta dwe rapid, trè vit, epi pa ta dwe gen demann long. Sepandan, si gen demann long, Lè sa a, nan kout tèm pa gen anyen enkyete sou, men nan kouri nan longè, demann long mal baz done a, yo ogmante efè a gonfleman nan tab lè fwagmantasyon tab la. Tou de gonfle ak demann long bezwen jete.
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;Tanpri sonje: ak yon demann konsa, nou ka defini demann long ak tranzaksyon. Nou itilize fonksyon an clock_timestamp() pou detèmine tan travay la. Long demann ke nou jwenn, nou ka sonje yo, egzekite yo explain, gade nan plan ak yon jan kanmenm optimize. Nou tire demann aktyèl yo long ak viv sou.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Move tranzaksyon yo san fè anyen konsa nan tranzaksyon ak san fè anyen konsa nan tranzaksyon (avòte).
Sa sa vle di? Tranzaksyon yo gen plizyè eta. Ak youn nan eta sa yo ka pran nenpòt ki lè. Gen yon jaden pou defini eta yo state nan vi sa a. Epi nou sèvi ak li pou detèmine eta a.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Epi, jan mwen te di pi wo a, de eta sa yo idle nan tranzaksyon ak idle nan tranzaksyon (avòte) yo move. Ki sa li ye? Sa a se lè aplikasyon an louvri yon tranzaksyon, te fè kèk aksyon ak te ale nan biznis li yo. Tranzaksyon an rete ouvè. Li pandye, pa gen anyen k ap pase nan li, li pran yon koneksyon, kadna sou ranje chanje ak potansyèlman toujou ogmante gonfleman nan lòt tab, akòz achitekti a nan motè a tranzaksyon Postrges. Ak tranzaksyon sa yo ta dwe tou tire, paske yo danjere an jeneral, nan nenpòt ka.
Si ou wè ke ou gen plis pase 5-10-20 nan baz done ou a, Lè sa a, ou bezwen jwenn enkyete epi kòmanse fè yon bagay ak yo.
Isit la nou itilize tou pou tan an kalkil clock_timestamp(). Nou tire tranzaksyon, nou optimize aplikasyon an.
Kòm mwen te di pi wo a, kadna se lè de oswa plis tranzaksyon konpetisyon pou youn oswa yon gwoup resous. Pou sa nou gen yon jaden waiting ak valè booleyen true oswa false.
Vre - sa vle di ke pwosesis la ap tann, yon bagay bezwen fè. Lè yon pwosesis ap tann, Lè sa a, kliyan an ki inisye pwosesis la ap tann tou. Kliyan an nan navigatè a chita epi tann tou.
Avètisman: _Apati de Postgres 9.6, jaden an waiting retire ak ranplase pa de plis jaden enfòmatif wait_event_type и wait_event._
Ki sa ki fè? Si ou wè vre pou yon tan long, Lè sa a, ou ta dwe debarase m de demann sa yo. Nou jis tire tranzaksyon sa yo. Nou ekri devlopè yo sa ki bezwen yo dwe optimize yon jan kanmenm pou pa gen okenn kous pou resous yo. Lè sa a, devlopè yo optimize aplikasyon an pou sa pa rive.
Ak yon ka ekstrèm, men potansyèlman pa fatal se ensidan enpas. De tranzaksyon yo te mete ajou de resous, Lè sa a, yo jwenn aksè yo ankò, deja nan resous opoze. PostgreSQL nan ka sa a pran ak tire sou tranzaksyon an tèt li pou ke lòt la ka kontinye travay. Sa a se yon sitiyasyon ki pa mouri epi li pa konprann tèt li. Se poutèt sa, PostgreSQL oblije pran mezi ekstrèm.
Ak isit la yo se de demann ki pèmèt ou swiv kadna. Nou itilize vi a pg_locks, ki pèmèt ou swiv kadna lou.
Ak lyen an premye se tèks la demann tèt li. Li trè long.
Ak dezyèm lyen an se yon atik sou kadna. Li itil pou li, li trè enteresan.
Se konsa, sa nou wè? Nou wè de demann. Tranzaksyon ak ALTER TABLE se yon tranzaksyon bloke. Li te kòmanse, men li pa t fini, ak aplikasyon ki afiche tranzaksyon sa a ap fè lòt bagay yon kote. Ak dezyèm demann lan se aktyalizasyon. Li tann pou tab alter la fini anvan li kontinye travay li.
Men ki jan nou ka konnen ki moun ki fèmen ki moun, ki moun ki kenbe ki moun, epi nou ka fè fas ak sa a pi lwen.
Modil kap vini an se pg_stat_statements. Kòm mwen te di, li se yon modil. Pou itilize li, ou bezwen chaje bibliyotèk li yo nan konfigirasyon an, rekòmanse PostgreSQL, enstale modil la (ak yon sèl kòmand), ak Lè sa a, nou pral gen yon nouvo View.
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;Kisa nou ka pran de la? Si nou pale sou bagay ki senp, nou ka pran tan an mwayèn ekzekisyon demann. Tan ap grandi, ki vle di ke PostgreSQL ap reponn tou dousman e yon bagay bezwen fè.
Nou ka wè tranzaksyon ekri ki pi aktif nan baz done a ki chanje done nan tanpon pataje. Gade ki moun ki mete ajou oswa efase done la.
Epi nou ka jis gade estatistik diferan pou demann sa yo.
Nou pg_stat_statements itilize pou konstwi rapò. Nou reset estatistik yo yon fwa pa jou. Ann akimile li. Anvan reset estatistik pwochen fwa, nou bati yon rapò. Men yon lyen ki mennen nan rapò a. Ou ka gade li.
Kisa nap fè? Nou kalkile estatistik an jeneral pou tout demann yo. Lè sa a, pou chak demann, nou konte kontribisyon endividyèl li nan estatistik jeneral sa a.
E kisa nou ka wè? Nou ka wè tan total ekzekisyon tout demann nan yon kalite patikilye kont background nan nan tout lòt demann. Nou ka gade nan CPU ak I / O itilizasyon an relasyon ak foto an jeneral. E deja pou optimize demann sa yo. Nou ap bati pi gwo demann ki baze sou rapò sa a epi yo deja jwenn manje pou reflechi sou sa yo optimize.
Ki sa nou genyen dèyè sèn nan? Genyen toujou kèk soumisyon ke mwen pa t 'konsidere, paske tan yo limite.
Gen pgstattuple se tou yon modil adisyonèl nan pake kontribisyon estanda yo. Li pèmèt ou evalye bloat tab, sa yo rele. fwagmantasyon tab la. Men, si fwagmantasyon an gwo, ou bezwen retire li, sèvi ak zouti diferan. Ak fonksyon pgstattuple travay pou yon tan long. Ak plis tab yo, plis li pral travay.
Pwochen kontribisyon an se pg_buffercache. Li pèmèt ou enspekte tanpon pataje: ki jan entansif ak pou ki tab paj tanpon yo itilize. Epi li jis pèmèt ou gade nan tanpon pataje ak evalye sa k ap pase la.
Modil kap vini an se pgfincore. Li pèmèt ou fè operasyon tab ba nivo atravè yon apèl sistèm mincore(), sa vle di li pèmèt ou chaje tab la nan tanpon pataje, oswa dechaje li. Epi li pèmèt, pami lòt bagay, enspekte kachèt paj nan sistèm operasyon an, se sa ki, konbyen tab la okipe nan kachèt paj la, nan tanpon pataje, epi tou senpleman pèmèt ou evalye chaj la sou tab la.
Modil kap vini an se pg_stat_kcache. Li itilize tou apèl sistèm lan getrusage(). Epi li egzekite li anvan ak apre demann lan egzekite. Ak nan estatistik yo jwenn, li pèmèt nou estime konbyen demann nou an te depanse sou disk I / O, sa vle di, operasyon ak sistèm nan dosye ak gade nan itilizasyon processeur a. Sepandan, modil la se jèn (khe-khe) epi pou travay li mande pou PostgreSQL 9.4 ak pg_stat_statements, ke mwen mansyone pi bonè.
-
Kapasite pou itilize estatistik itil. Ou pa bezwen lojisyèl twazyèm pati. Ou ka gade, wè, fè yon bagay, fè.
-
Sèvi ak estatistik se fasil, li nan SQL plenn. Ou kolekte yon demann, konpile li, voye li, gade li.
-
Estatistik ede reponn kesyon yo. Si ou gen kesyon, ou ale nan estatistik - gade, tire konklizyon, analize rezilta yo.
-
Ak eksperyans. Anpil demann, anpil done. Ou ka toujou optimize kèk rechèch ki egziste deja. Ou ka fè pwòp vèsyon ou nan demann lan ki kostim ou pi bon pase orijinal la epi sèvi ak li.
Referans
Lyen ki valab ki te jwenn nan atik la, ki baze sou ki, yo te nan rapò a.
Otè ekri plis
(eng)
Pèseptè Estatistik la
Fonksyon Administrasyon Sistèm
Kontrib modil
SQL sèvis piblik ak egzanp kòd sql
Mèsi tout pou atansyon ou!
Sous: www.habr.com