Afrit af skýrslu Alexey Lesovsky 2015 „Djúp kafa í innri tölfræði PostgreSQL“
Fyrirvari frá höfundi skýrslunnar: Ég tek fram að þessi skýrsla er dagsett í nóvember 2015 - meira en 4 ár eru liðin og langur tími liðinn. Útgáfa 9.4 sem fjallað er um í skýrslunni er ekki lengur studd. Undanfarin 4 ár hafa komið út 5 nýjar útgáfur þar sem mikið er um nýjungar, endurbætur og breytingar varðandi tölfræði og sumt af efninu er úrelt og ekki viðeigandi. Þegar ég rifja upp, reyndi ég að merkja þessa staði til að villa um fyrir lesandanum. Ég endurskrifaði ekki þessa kafla, þeir eru margir og útkoman verður allt önnur skýrsla.
PostgreSQL DBMS er risastórt vélbúnaður og þetta vélbúnaður samanstendur af mörgum undirkerfum, samræmd rekstur sem hefur bein áhrif á frammistöðu DBMS. Við notkun er tölfræði og upplýsingum um rekstur íhluta safnað, sem gerir þér kleift að meta virkni PostgreSQL og gera ráðstafanir til að bæta árangur. Hins vegar er mikið af þessum upplýsingum og þær eru settar fram í frekar einföldu formi. Að vinna úr þessum upplýsingum og túlka þær er stundum algjörlega ekki léttvægt verkefni og „dýragarðurinn“ af tækjum og tólum getur auðveldlega ruglað jafnvel háþróaðan DBA.
Góðan daginn Ég heiti Aleksey. Eins og Ilya sagði mun ég tala um PostgreSQL tölfræði.
PostgreSQL virkni tölfræði. PostgreSQL hefur tvær tölfræði. Tölfræði um starfsemi sem verður rædd. Og tímaáætlunartölfræði um dreifingu gagna. Ég mun tala sérstaklega um PostgreSQL virkni tölfræði, sem gerir okkur kleift að dæma árangur og einhvern veginn bæta hana.
Ég skal segja þér hvernig á að nota tölfræði á áhrifaríkan hátt til að leysa margvísleg vandamál sem þú hefur eða gætir átt í.
Hvað verður ekki í skýrslunni? Í skýrslunni mun ég ekki snerta tölfræði tímaáætlunar, vegna þess að... Þetta er sérstakt efni fyrir sérstaka skýrslu um hvernig gögn eru geymd í gagnagrunninum og hvernig fyrirspurnaskipuleggjandinn fær hugmynd um eigindleg og megindleg einkenni þessara gagna.
Og það verða engar umsagnir um verkfæri, ég mun ekki bera eina vöru saman við aðra. Það verða engar auglýsingar. Við skulum leggja það til hliðar.
Ég vil sýna þér að það er gagnlegt að nota tölfræði. Það er nauðsynlegt. Það er óhætt að nota. Allt sem við þurfum er venjulegur SQL og grunnþekking á SQL.
Og við skulum tala um hvaða tölfræði á að velja til að leysa vandamál.
Ef við skoðum PostgreSQL og keyrum skipunina á stýrikerfinu til að skoða ferla þá sjáum við "svartan kassa". Við munum sjá nokkur ferli sem eru að gera eitthvað og út frá nafninu getum við ímyndað okkur í grófum dráttum hvað þeir eru að gera þarna, hvað þeir eru að gera. En í rauninni er þetta svartur kassi; við getum ekki litið inn.
Við getum séð CPU hlaðið inn top, við getum skoðað minnisnotkun sumra kerfistækja, en við munum ekki geta skoðað PostgreSQL. Til þess þurfum við önnur tæki.
Og áfram mun ég segja þér hvar tímanum er varið. Ef við ímyndum okkur PostgreSQL í formi slíkrar skýringarmyndar, þá getum við svarað því hvar tímanum er varið. Þetta eru tveir hlutir: það er að vinna úr beiðnum viðskiptavina frá forritum og bakgrunnsverkefnin sem PostgreSQL framkvæmir til að halda sér í gangi.
Ef við byrjum að skoða efst í vinstra horninu getum við séð hvernig beiðnir viðskiptavina eru unnar. Beiðnin kemur frá umsókninni og opnað er fyrir viðskiptafund fyrir frekari vinnu. Beiðnin er send til tímaritara. Tímaáætlunin býr til fyrirspurnaáætlun. Sendir það frekar til framkvæmdar. Það er einhvers konar inntak/úttak blokkgagna tengd töflum og vísitölum. Nauðsynleg gögn eru lesin af diskunum í minni í sérstakt svæði "samnýtt biðminni". Niðurstöður beiðninnar, ef þær eru uppfærslur, eyðingar, eru skráðar í viðskiptaskránni í WAL. Sumar tölulegar upplýsingar lenda í skránni eða tölfræðisafnanum. Og niðurstaða beiðninnar er send aftur til viðskiptavinarins. Eftir það getur viðskiptavinurinn endurtekið allt aftur með nýrri beiðni.
Hvað með bakgrunnsverkefni og bakgrunnsferli? Við höfum nokkra ferla sem halda gagnagrunninum í gangi í venjulegum rekstrarham. Þessi ferla verður einnig snert í skýrslunni: sjálfvirkt tómarúm, eftirlitsmælir, afritunartengd ferli, bakgrunnsritari. Ég mun koma inn á hvert þeirra þegar ég skýri frá.
Hvaða vandamál eru með tölfræði?
- Það er mikið af upplýsingum. PostgreSQL 9.4 veitir 109 mælikvarða til að skoða tölfræðigögn. Hins vegar, ef gagnagrunnurinn geymir margar töflur, skema, gagnagrunna, þá verður að margfalda allar þessar mælingar með samsvarandi fjölda taflna, gagnagrunna. Það er, það eru enn meiri upplýsingar. Og það er mjög auðvelt að drukkna í því.
- Næsta vandamál er að tölfræði er táknuð með teljara. Ef við skoðum þessa tölfræði munum við sjá stöðugt hækkandi teljara. Og ef langur tími er liðinn frá því að tölfræðin var endurstillt, munum við sjá verðmæti í milljörðum. Og þeir segja okkur ekki neitt.
- Engin saga. Ef þú lentir í einhvers konar bilun, eitthvað datt fyrir 15-30 mínútum síðan, þú munt ekki geta notað tölfræði og séð hvað gerðist fyrir 15-30 mínútum síðan. Þetta er vandamál.
- Skortur á tóli sem er innbyggt í PostgreSQL er vandamál. Kjarnahönnuðirnir bjóða ekki upp á nein tól. Þeir eru ekki með neitt svoleiðis. Þeir gefa einfaldlega tölfræði í gagnagrunninum. Notaðu það, gerðu beiðni um það, gerðu hvað sem þú vilt.
- Þar sem ekkert tól er innbyggt í PostgreSQL veldur þetta öðru vandamáli. Fullt af verkfærum þriðja aðila. Hvert fyrirtæki sem hefur meira og minna beinar hendur er að reyna að skrifa sitt eigið forrit. Og þar af leiðandi hefur samfélagið fullt af verkfærum sem hægt er að nota til að vinna með tölfræði. Og sum verkfæri hafa ákveðna getu, önnur verkfæri hafa ekki aðra getu, eða það eru einhverjir nýir möguleikar. Og sú staða kemur upp að þú þarft að nota tvö, þrjú eða fjögur verkfæri sem skarast hvert annað og hafa mismunandi aðgerðir. Þetta er mjög óþægilegt.
Hvað leiðir af þessu? Það er mikilvægt að geta tekið tölfræði beint, svo að vera ekki háður forritum, eða einhvern veginn bæta þessi forrit sjálfur: bæta við nokkrum aðgerðum til að fá eigin ávinning.
Og þú þarft grunnþekkingu á SQL. Til að fá einhver gögn úr tölfræði þarftu að búa til SQL fyrirspurnir, þ.e.a.s. þú þarft að vita hvernig select og join eru sett saman.
Tölfræði segir okkur ýmislegt. Þeim má skipta í flokka.
- Fyrsti flokkurinn eru atburðir sem eiga sér stað í gagnagrunninum. Þetta er þegar einhver atburður á sér stað í gagnagrunninum: beiðni, aðgangur að töflu, autovacuum, skuldbindingar, þá eru þetta allt atburðir. Teljararnir sem samsvara þessum atburðum eru hækkaðir. Og við getum fylgst með þessum atburðum.
- Annar flokkurinn er eiginleikar hluta eins og töflur og gagnagrunna. Þeir hafa eignir. Þetta er stærðin á borðunum. Við getum fylgst með vexti taflna og vöxt vísitölu. Við getum séð breytingar á gangverki.
- Og þriðji flokkurinn er tíminn sem fer í viðburðinn. Beiðni er viðburður. Það hefur sinn sérstaka mælikvarða á lengd. Byrjaði hér, endaði hér. Við getum fylgst með því. Annað hvort tíminn sem það tekur að lesa blokk af diski eða skrifa hann. Svona hlutir eru líka raktir.
Heimildir tölfræði eru settar fram sem hér segir:
- Í sameiginlegu minni (shared buffers) er hluti til að geyma kyrrstæð gögn, það eru líka þeir teljarar sem eru stöðugt auknir þegar ákveðnir atburðir eiga sér stað, eða einhver augnablik koma upp í rekstri gagnagrunnsins.
- Allir þessir teljarar eru ekki aðgengilegir notandanum og ekki einu sinni aðgengilegir stjórnandanum. Þetta eru hlutir á lágu plani. Til að fá aðgang að þeim veitir PostgreSQL viðmót í formi SQL aðgerða. Við getum gert valin kast með því að nota þessar aðgerðir og fá einhvers konar mæligildi (eða sett af mæligildum).
- Hins vegar er ekki alltaf þægilegt að nota þessar aðgerðir, þannig að aðgerðir eru grunnurinn fyrir skoðanir (VIEWs). Þetta eru sýndartöflur sem veita tölfræði um tiltekið undirkerfi eða ákveðna atburði í gagnagrunninum.
- Þessar innbyggðu skoðanir (VIEWs) eru aðal notendaviðmótið til að vinna með tölfræði. Þær eru sjálfgefnar tiltækar án frekari stillinga, þú getur strax notað þær, skoðað þær og tekið upplýsingar úr þeim. Og svo eru það framlög. Framlög eru opinber. Þú getur sett upp postgresql-contrib pakkann (til dæmis postgresql94-contrib), hlaðið nauðsynlegri einingu í uppsetninguna, tilgreint færibreytur fyrir hana, endurræst PostgreSQL og þú getur notað hana. (Athugið. Það fer eftir dreifingu, í nýlegum útgáfum er framlagspakkinn hluti af aðalpakkanum).
- Og það eru óopinber framlög. Þau eru ekki innifalin í stöðluðu PostgreSQL dreifingunni. Þeir verða annað hvort að vera settir saman eða settir upp sem bókasafn. Valmöguleikarnir geta verið mjög mismunandi, allt eftir því hvað verktaki þessa óopinbera framlags fann upp.
Þessi glæra sýnir allar þessar skoðanir og sumar aðgerðir sem eru tiltækar í PostgreSQL 9.4. Eins og við sjáum eru þeir margir. Og það er frekar auðvelt að ruglast ef þú lendir í því í fyrsta skipti.
Hins vegar, ef við tökum fyrri myndina Как тратится время на PostgreSQL og samhæft við þennan lista fáum við þessa mynd. Við getum notað hvert útsýni (VIEWs) eða hverja aðgerð í einum eða öðrum tilgangi til að fá samsvarandi tölfræði þegar PostgreSQL er í gangi. Og við getum nú þegar fengið nokkrar upplýsingar um rekstur undirkerfisins.
Það fyrsta sem við munum skoða er pg_stat_database. Eins og við sjáum er þetta frammistaða. Það er mikið af upplýsingum í því. Fjölbreyttustu upplýsingarnar. Og það gefur mjög gagnlega þekkingu á því sem er að gerast í gagnagrunninum okkar.
Hvaða gagnlega hluti getum við tekið þaðan? Byrjum á einföldustu hlutunum.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;Það fyrsta sem við getum skoðað er skyndiminni högghlutfallið. Slaghraði skyndiminni er gagnlegur mælikvarði. Það gerir þér kleift að áætla hversu mikið af gögnum er tekið úr sameiginlegu biðminni skyndiminni og hversu mikið er lesið af diski.
Það er ljóst að því fleiri skyndiminni sem við höfum, því betra. Við mælum þennan mælikvarða sem prósentu. Og, til dæmis, ef hlutfall okkar af þessum skyndiminni er meira en 90%, þá er þetta gott. Ef það fer niður fyrir 90% þýðir það að við höfum ekki nóg minni til að halda heitum gagnahausnum í minni. Og til þess að nota þessi gögn neyðist PostgreSQL til að komast inn á diskinn og þetta er hægara en ef gögnin væru lesin úr minni. Og þú þarft að hugsa um að auka minni: annað hvort auka sameiginlega biðminni eða auka vélbúnaðarminni (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;Hvað annað geturðu tekið úr þessari frammistöðu? Þú getur séð frávik sem eiga sér stað í gagnagrunninum. Hvað er sýnt hér? Það eru skuldbindingar, afturköllun, gerð tímabundinna skráa, stærð þeirra, deadlocks og átök.
Við getum notað þessa beiðni. Þetta SQL er frekar einfalt. Og við getum skoðað þessi gögn hér.
Og hér eru þröskuldsgildin. Við skoðum hlutfall skuldbindinga og afturköllunar. Skuldbinding er árangursrík staðfesting á viðskiptum. Afturkallanir eru afturköllun, þ.e.a.s. færsla virkaði eitthvað, þvingaði gagnagrunninn, reiknaði eitthvað út og síðan kom upp bilun og niðurstöðum viðskiptanna er hent. Það er fjöldi afturkalla sem sífellt fjölgar er slæmt. Og þú ættir einhvern veginn að forðast þá og breyta kóðanum þannig að þetta gerist ekki.
Átök eru tengd afritun. Og þeir ættu líka að forðast. Ef þú ert með einhverjar fyrirspurnir sem eru keyrðar á eftirmynd og átök koma upp, þá þarftu að raða þessum átökum og sjá hvað er að gerast. Nánari upplýsingar er að finna í logs. Og útrýma árekstra þannig að umsóknarbeiðnir virka án villna.
Kyrrstaða er líka slæm staða. Þegar beiðnir eru að berjast um auðlindir fékk ein beiðni aðgang að einni auðlind og tók lásinn, önnur beiðni fékk aðgang að annarri auðlindinni og tók einnig lásinn, og þá komust báðar beiðnir í auðlindir hvors annars og lokuðust á meðan beðið var eftir að nágranninn losaði lásinn. Þetta er líka vandamál. Það þarf að taka á þeim á því stigi að endurskrifa forrit og raðgreina aðgang að auðlindum. Og ef þú sérð að stöðvun þín er stöðugt að aukast þarftu að skoða smáatriðin í annálunum, greina aðstæðurnar sem koma upp og sjá hvert vandamálið er.
Tímabundnar skrár (temp_files) eru líka slæmar. Þegar notendabeiðni hefur ekki nóg minni til að rúma tímabundin gögn í rekstri, býr hún til skrá á diski. Og allar aðgerðir sem það gæti framkvæmt í tímabundnum biðminni í minni byrjar að framkvæma á disknum. Það er hægt. Þetta eykur framkvæmdartíma fyrirspurnar. Og viðskiptavinurinn sem sendi beiðni til PostgreSQL mun fá svar aðeins síðar. Ef allar þessar aðgerðir eru framkvæmdar í minni mun Postgres bregðast mun hraðar og viðskiptavinurinn mun bíða minna.
Pg_stat_bgwriter - Þetta útsýni lýsir starfsemi tveggja PostgreSQL bakgrunns undirkerfa: þetta checkpointer и background writer.
Fyrst skulum við skoða eftirlitsstaði, svokallaða. checkpoints. Hvað eru eftirlitsstaðir? Athugunarpunktur er staðsetning í viðskiptaskránni sem gefur til kynna að allar gagnabreytingar sem skráðar eru í skránni hafi verið samstilltar við gögnin á disknum. Ferlið, allt eftir vinnuálagi og stillingum, getur verið langt og felst að mestu í því að samstilla óhreinar síður í sameiginlegum biðmunum við gagnaskrár á diski. Til hvers er það? Ef PostgreSQL væri stöðugt að nálgast diskinn og sækja gögn þaðan, og skrifa gögn á hvern aðgang, væri það hægt. Þess vegna hefur PostgreSQL minnishluta þar sem stærð fer eftir stillingum í uppsetningunni. Postgres geymir lifandi gögn í þessu minni til síðari vinnslu eða fyrirspurna. Ef beðið er um að breyta gögnunum er þeim breytt. Og við fáum tvær útgáfur af gögnunum. Annar er í minni okkar, hinn er á diski. Og reglulega þarftu að samstilla þessi gögn. Við þurfum að samstilla það sem er breytt í minni yfir á disk. Til þess þarftu eftirlitsstöðvar.
Checkpoint fer í gegnum sameiginlega biðminni, merkir óhreinar síður sem þær eru nauðsynlegar fyrir checkpoint. Síðan setur það aðra leið í gegnum sameiginlegu biðminni. Og síðurnar sem eru merktar fyrir eftirlitsstöð, það samstillir þær nú þegar. Þannig eru gögnin samstillt við diskinn.
Það eru tvenns konar eftirlitsstöðvar. Eitt eftirlitsstöð er framkvæmt með tímamörkum. Þessi eftirlitsstöð er gagnleg og góð - checkpoint_timed. Og það eru eftirlitsstöðvar á eftirspurn - checkpoint required. Þetta eftirlit á sér stað þegar við erum með mjög stóra gagnaskrá. Við skráðum mikið af viðskiptaskrám. Og PostgreSQL telur að það þurfi að samstilla þetta allt eins fljótt og auðið er, gera eftirlitsstöð og halda áfram.
Og ef þú skoðar tölfræðina pg_stat_bgwriter og sá hvað þú átt checkpoint_req er miklu stærra en checkpoint_timed, þá er þetta slæmt. Hvers vegna slæmt? Þetta þýðir að PostgreSQL er undir stöðugu álagi þegar það þarf að skrifa gögn á diskinn. Tímamörk er minna streituvaldandi og er framkvæmt í samræmi við innri áætlun og dreifist nokkurn veginn yfir tíma. PostgreSQL hefur getu til að gera hlé á vinnu og ekki álag á disk undirkerfið. Þetta er gagnlegt fyrir PostgreSQL. Og fyrirspurnir sem eru framkvæmdar á eftirlitsstað munu ekki upplifa streitu vegna þess að undirkerfi disksins er upptekið.
Og til að stilla eftirlitsstöðina eru þrjár breytur:
-
сheckpoint_segments. -
сheckpoint_timeout. -
сheckpoint_competion_target.
Þeir leyfa þér að stjórna virkni stjórnstöðva. En ég mun ekki dvelja við þá. Áhrif þeirra eru sérstakt umræðuefni.
Viðvörun: Útgáfa 9.4 sem fjallað er um í skýrslunni á ekki lengur við. Í nútíma útgáfum af PostgreSQL er færibreytan checkpoint_segments skipt út fyrir breytur min_wal_size и max_wal_size.
Næsta undirkerfi er bakgrunnsritari - background writer. Hvað er hann að gera? Það keyrir stöðugt í endalausri lykkju. Skannar síður í samnýttum biðmunum og setur óhreinum síðum sem það finnur á diskinn. Þannig hjálpar það eftirlitsstöðinni að vinna minni vinnu við framkvæmd eftirlitsstaða.
Til hvers þarf það annars? Það veitir þörf á auðum síðum í sameiginlegum biðmunum ef skyndilega er þörf á þeim (í miklu magni og strax) til að koma til móts við gögn. Segjum sem svo að aðstæður hafi komið upp þegar tómar síður voru nauðsynlegar til að klára beiðni og þær væru þegar í sameiginlegu biðminni. Postgresive backend hann tekur þá bara upp og notar þá þarf hann ekki að þrífa neitt sjálfur. En ef það eru skyndilega engar slíkar síður, gerir bakendinn hlé á vinnunni og byrjar að leita að síðum til að henda þeim inn á diskinn og taka þær eftir eigin þörfum - sem hefur neikvæð áhrif á tíma beiðninnar sem er í gangi. Ef þú sérð að þú ert með breytu maxwritten_clean stór, þetta þýðir að bakgrunnsritarinn er ekki að vinna vinnuna sína og þú þarft að auka færibreyturnar bgwriter_lru_maxpages, svo að hann geti unnið meira í einni lotu, hreinsa fleiri síður.
Og annar mjög gagnlegur vísir er buffers_backend_fsync. Bakendarnir fsync ekki vegna þess að þeir eru hægir. Þeir fara fsync upp IO stafla eftirlitsmælirinn. Gátvísirinn hefur sína eigin biðröð, hann vinnur reglulega úr fsync og samstillir síður í minni við skrár á diski. Ef röðin við eftirlitsstöðina er stór og full, þá neyðist bakendinn til að gera fsync sjálfan og það hægir á vinnu bakendans, þ.e.a.s. viðskiptavinurinn mun fá svar seinna en hann gat. Ef þú sérð að gildi þitt er meira en núll, þá er þetta nú þegar vandamál og þú þarft að fylgjast með stillingum bakgrunnsritarans og einnig meta frammistöðu diska undirkerfisins.
Viðvörun: _Eftirfarandi texti lýsir tölfræðilegum skoðunum sem tengjast afritun. Flest nöfn útsýnis og aðgerða voru endurnefnd í Postgres 10. Kjarninn í endurnöfnuninni var að skipta út xlog á wal и location á lsn í fall-/skoðanöfnum o.s.frv. Sérstakt dæmi, virka pg_xlog_location_diff() var breytt í pg_wal_lsn_diff()._
Við höfum líka ýmislegt hérna. En við þurfum aðeins hluti sem tengjast staðsetningu.
Ef við sjáum að öll gildi eru jöfn, þá er þetta kjörinn kostur og eftirmyndin er ekki á eftir meistaranum.
Þessi sextándastaða hér er staðan í færsluskránni. Það eykst stöðugt ef það er einhver virkni í gagnagrunninum: setur inn, eyðir osfrv.
сколько записано 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());Ef þessir hlutir eru öðruvísi, þá er einhvers konar töf. Töf er töfin milli eftirmyndarinnar og meistarans, þ.e.a.s. gögnin eru mismunandi á milli netþjóna.
Það eru þrjár ástæður fyrir seinkuninni:
- Þetta undirkerfi á disknum getur ekki ráðið við samstillingu skráa.
- Þetta eru hugsanlegar netvillur, eða ofhleðsla nets, þegar gögnin hafa ekki tíma til að ná til eftirmyndarinnar og þau geta ekki endurskapað þau.
- Og örgjörvinn. Örgjörvinn er mjög sjaldgæft tilfelli. Og ég sá þetta tvisvar eða þrisvar sinnum, en þetta getur líka gerst.
Og hér eru þrjár fyrirspurnir sem gera okkur kleift að nota tölfræði. Við getum metið hversu mikið við höfum skráð í viðskiptaskránni. Það er svona aðgerð pg_xlog_location_diff og við getum áætlað afritunartöfina í bætum og sekúndum. Við notum einnig gildið frá þessu útsýni (VIEWs) fyrir þetta.
Ath: _Í stað pg_xlog_locationAðgerðin diff() getur notað frádráttarvirkjann og dregið eina staðsetningu frá annarri. Þægilegt.
Það er eitt stig með seinkuninni, sem er í sekúndum. Ef það er engin virkni á masternum þá var færslan þar fyrir um 15 mínútum og það er engin virkni og ef við skoðum þessa töf á eftirmyndinni munum við sjá töf upp á 15 mínútur. Þessu er vert að muna. Og þetta getur verið ruglingslegt þegar þú horfir á þessa töf.
Pg_stat_all_tables er annað gagnlegt útsýni. Það sýnir tölfræði á töflum. Þegar við erum með töflur í gagnagrunninum er einhver virkni með hann, einhverjar aðgerðir, við getum fengið þessar upplýsingar frá þessu útsýni.
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ð fyrsta sem við getum skoðað eru raðskannanir yfir borðið. Fjöldinn sjálfur eftir þessar sendingar er ekki endilega slæmur og er ekki vísbending um að við þurfum að gera eitthvað.
Hins vegar er önnur mælikvarði - seq_tup_read. Þetta er fjöldi lína sem skilað er frá raðskönnuninni. Ef meðaltalan fer yfir 1, 000, 10, 000, þá er þetta nú þegar vísbending um að kannski þurfið þið að búa til vísitölu einhvers staðar svo að fyrirspurnir séu byggðar á vísitölunni, eða það er hægt að fínstilla fyrirspurnir sem nota slíkar raðskannanir svo að þetta gerist ekki var.
Einfalt dæmi - við skulum segja beiðni með miklum OFFSET og LIMIT kostnaði. Til dæmis eru 100 línur í töflu skannaðar og eftir það eru 000 nauðsynlegar línur teknar og fyrri skannaðar línur er hent. Þetta er líka slæmt mál. Og slíkar fyrirspurnir þarf að hagræða. Og hér er einföld SQL fyrirspurn þar sem þú getur skoðað þetta og metið tölurnar sem myndast.
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;Einnig er hægt að fá töflustærðir með því að nota þessa töflu og með því að nota viðbótaraðgerðir pg_total_relation_size(), pg_relation_size().
Almennt séð eru til metaskipanir dt и di, sem hægt er að nota í PSQL og einnig skoða stærðir á töflum og vísitölum.
Hins vegar, að nota aðgerðir hjálpar okkur að skoða stærðir taflna, jafnvel með hliðsjón af vísitölum, eða án þess að taka tillit til vísitölu, og gera nú þegar nokkrar áætlanir byggðar á vexti gagnagrunnsins, þ.e. hvernig hann er að stækka, með hvaða styrkleika og draga nokkrar ályktanir um hagræðingu stærðar.
Upptaka starfsemi. Hvað er upptaka? Lítum á reksturinn UPDATE – aðgerðin við að uppfæra línur í töflu. Reyndar eru uppfærslur tvær aðgerðir (eða jafnvel fleiri). Þetta er að setja inn nýja útgáfu af röðinni og merkja gamla útgáfuna af röðinni sem úrelt. Í kjölfarið mun sjálfvirka tómarúmið koma og hreinsa út þessar úreltu útgáfur af línunum og merkja þennan stað sem lausan til endurnotkunar.
Að auki snýst uppfærsla ekki aðeins um að uppfæra töflu. Þetta er líka vísitöluuppfærsla. Ef þú ert með margar vísitölur á borðinu, þá þarf einnig að uppfæra allar vísitölur sem innihalda reiti sem eru uppfærðir í fyrirspurninni meðan á uppfærslu stendur. Þessar vísitölur munu einnig hafa gamlar útgáfur af línum sem þarf að hreinsa upp.
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;Og vegna nýrrar hönnunar er UPDATE þungavigtaraðgerð. En það er hægt að gera þær auðveldari. Borða hot updates. Þeir birtust í PostgreSQL útgáfu 8.3. Og hvað er þetta? Þetta er létt uppfærsla sem veldur ekki því að vísitölur eru endurbyggðar. Það er að segja, við uppfærðum færsluna, en aðeins færslan á síðunni (sem tilheyrir töflunni) var uppfærð og vísitölurnar benda enn á sömu færsluna á síðunni. Það er svolítið áhugaverð rekstrarrökfræði: þegar tómarúm kemur, skapar það þessar keðjur hot endurbyggir og allt heldur áfram að virka án þess að uppfæra vísitölur og allt gerist með minni sóun á fjármagni.
Og hvenær gerir þú n_tup_hot_upd stór, þá er það mjög gott. Þetta þýðir að léttar uppfærslur eru allsráðandi og þetta er ódýrara fyrir okkur hvað varðar auðlindir og allt er í lagi.
ALTER TABLE table_name SET (fillfactor = 70);Hvernig á að auka hljóðstyrk hot updateov? Við getum notað fillfactor. Það ákvarðar stærð frátekins lausa pláss þegar fyllt er út síðu í töflu með INSERTs. Þegar innskotum er bætt við töflu fylla þau síðuna alveg og skilja ekkert eftir tómt pláss. Þá er ný síða auðkennd. Gögnin eru fyllt út aftur. Og þetta er sjálfgefin hegðun, fillfactor = 100%.
Við getum gert fyllingarstuðulinn 70%. Það er að segja að við innskot var ný síða auðkennd, en aðeins 70% af síðunni fylltist. Og við eigum 30% eftir sem varasjóð. Þegar þú þarft að gera uppfærslu mun það líklega gerast á sömu síðu og nýja útgáfan af línunni passar á sömu síðu. Og hot_update verður gert. Þetta gerir það auðveldara að skrifa á töflur.
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));Autovacuum biðröð. Autovacuum er undirkerfi sem það eru mjög fáar tölfræði í PostgreSQL. Við getum aðeins séð í töflunum í pg_stat_activity hversu mörg tómarúm við erum með í augnablikinu. Hins vegar er mjög erfitt að átta sig á því hversu mörg borð eru í biðröðinni strax.
Ath: _Frá og með Postgres 10 hefur ástandið með Vatovac rakningu batnað mikið - pg_stat_progress yfirlitið hefur birsttómarúm, sem einfaldar verulega eftirlit með bíltæmi.
Við getum notað þessa einfölduðu fyrirspurn. Og við getum séð hvenær tómarúmið verður að gera. En hvernig og hvenær ætti tómarúmið að byrja? Þetta eru eldri útgáfur af línunum sem ég var að tala um áðan. Uppfærsla átti sér stað, ný útgáfa af línunni var sett inn. Gamaldags útgáfa af strengnum hefur birst. Í töflunni pg_stat_user_tables það er svona breytu n_dead_tup. Það sýnir fjölda „dauðra“ lína. Og um leið og fjöldi dauðra raða verður meiri en ákveðinn þröskuldur kemur sjálfvirkt tómarúm á borðið.
Og hvernig er þessi þröskuldur reiknaður út? Þetta er mjög ákveðið hlutfall af heildarfjölda lína í töflunni. Það er breytu autovacuum_vacuum_scale_factor. Það ræður hlutfallinu. Segjum að 10% + það er viðbótar grunnþröskuldur 50 línur. Og hvað gerist? Þegar við erum með fleiri dauðar línur en "10% + 50" af öllum línum í töflunni, þá setjum við töfluna á sjálfvirkt tómarú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));Hins vegar er eitt atriði. Grunnþröskuldar fyrir færibreytur av_base_thresh и av_scale_factor hægt að úthluta fyrir sig. Og í samræmi við það verður þröskuldurinn ekki alþjóðlegur, heldur einstaklingsbundinn fyrir borðið. Þess vegna þarftu að nota brellur og brellur til að reikna út. Og ef þú hefur áhuga, þá geturðu skoðað reynslu samstarfsmanna okkar frá Avito (tengillinn á glærunni er ógildur og hefur verið uppfærður í textanum).
Þeir skrifuðu fyrir , sem tekur mið af þessum hlutum. Það er tveggja blaða fótdúkur þarna. En það reiknar rétt og gerir okkur kleift að meta hvar við þurfum mikið tómarúm fyrir borð þar sem lítið er.
Hvað getum við gert í því? Ef við erum með mikla biðröð og sjálfvirka tómarúmið getur ekki ráðið við, þá getum við fjölgað tómarúmsstarfsmönnum, eða einfaldlega gert tómarúmið árásargjarnara, þannig að það kvikni fyrr, vinnur töfluna í litlum bitum. Og þar með mun röðin minnka. — Aðalatriðið hér er að fylgjast með álaginu á diskunum, því... tómarúm er ekki ókeypis hlutur, þó að með tilkomu SSD/NVMe tækja hafi vandamálið orðið minna áberandi.
Pg_stat_all_indexes er tölfræði um vísitölur. Hún er ekki stór. Og við getum notað það til að fá upplýsingar um notkun vísitölu. Og til dæmis getum við ákvarðað hvaða vísitölur við höfum aukalega.
Eins og ég sagði þegar, uppfærsla er ekki aðeins uppfærsla á töflum, það er líka uppfærsla á vísitölum. Í samræmi við það, ef við erum með margar vísitölur á töflunni, þá þarf einnig að uppfæra vísitölur verðtryggðu reitanna þegar línurnar í töflunni eru uppfærðar, og ef við erum með ónotaðar vísitölur sem engar vísitöluskannanir eru fyrir, þá hanga þær sem kjölfesta. Og við þurfum að losna við þá. Til þess þurfum við akur idx_scan. Við lítum einfaldlega á fjölda vísitöluskannana. Ef vísitölur hafa núll skannanir yfir tiltölulega langan geymslutíma tölfræði (að minnsta kosti 2-3 vikur), þá eru þetta líklegast slæmar vísitölur, við þurfum að losna við þær.
Ath: Þegar leitað er að ónotuðum vísitölum þegar um er að ræða straumafritunarþyrpingar þarftu að athuga alla klasahnúta, vegna þess að tölfræði er ekki alþjóðleg, og ef vísitalan er ekki notuð á master, þá er hægt að nota hana á eftirmyndir (ef það er álag þar).
Tveir tenglar:
Þetta eru ítarlegri fyrirspurnadæmi um hvernig á að fletta upp ónotuðum vísitölum.
Seinni hlekkurinn er frekar áhugaverð beiðni. Það er mjög óléttvæg rökfræði þarna. Ég mæli með því til viðmiðunar.
Hvað annað er þess virði að draga saman með því að nota vísitölur?
-
Ónotaðar vísitölur eru slæmar.
-
Þeir taka pláss.
-
Hægja á uppfærsluaðgerðum.
-
Aukavinna fyrir tómarúmið.
Ef við fjarlægjum ónotaðar vísitölur munum við aðeins gera gagnagrunninn betri.
Næsta kynning er pg_stat_activity. Þetta er hliðstæða gagnseminnar ps, aðeins í PostgreSQL. Ef ps„Og þú horfir á ferlana í stýrikerfinu, þá pg_stat_activity Það mun sýna þér virknina í PostgreSQL.
Hvaða gagnlega hluti getum við tekið þaðan?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Við getum séð almenna starfsemi, hvað er að gerast í gagnagrunninum. Við getum gert nýja dreifingu. Hér er allt sprungið, nýjar tengingar eru ekki samþykktar, villur streyma inn í forritið.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Við getum keyrt fyrirspurn eins og þessa og séð heildarhlutfall tenginga miðað við hámarkstengingarmörk og séð hver er með flestar tengingar. Og í þessu tilviki sjáum við þann notanda cron_role opnaði 508 tengingar. Og eitthvað kom fyrir hann þar. Við þurfum að takast á við það og skoða það. Og það er alveg mögulegt að þetta sé einhvers konar afbrigðilegur fjöldi tenginga.
Ef við erum með OLTP vinnuálag ættu fyrirspurnirnar að vera hraðar, mjög hraðar og það ættu ekki að vera langar fyrirspurnir. Hins vegar, ef langar fyrirspurnir koma upp, þá er ekkert til að hafa áhyggjur af til skamms tíma, en Til lengri tíma litið skaða langar fyrirspurnir gagnagrunninn, þær auka uppblástursáhrif taflna þegar töflu sundrun á sér stað. Þú þarft að losna við bæði uppþembu og langar fyrirspurnir.
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;Vinsamlegast athugið: með þessari beiðni getum við greint langar fyrirspurnir og viðskipti. Við notum aðgerðina clock_timestamp() til að ákvarða rekstrartíma. Langar fyrirspurnir sem við fundum, við getum munað þær, uppfyllt þær explain, skoða áætlanirnar og hagræða einhvern veginn. Við skjótum niður núverandi langar beiðnir og höldum áfram með líf okkar.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Slæm færslur eru færslur í aðgerðalausu í færslu og aðgerðalausri í færslu (hætt við).
Hvað þýðir það? Viðskipti hafa mörg ríki. Og gera má ráð fyrir einu af þessum ríkjum hvenær sem er. Það er reitur til að skilgreina ríki state í þessari kynningu. Og við notum það til að ákvarða ástandið.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Og eins og ég sagði hér að ofan, þessi tvö ríki aðgerðalaus í viðskiptum og aðgerðalaus í viðskiptum (hætt við) eru slæm. Hvað það er? Þetta er þegar forritið opnaði viðskipti, gerði nokkrar aðgerðir og fór í viðskiptum sínum. Viðskiptin eru enn opin. Það hangir, ekkert gerist í því, það tekur upp tenginguna, læsist á breyttum röðum og eykur hugsanlega uppblástur annarra borða, vegna arkitektúrs Postrges viðskiptavélarinnar. Og slík viðskipti ættu líka að vera skotin niður, því þau eru almennt skaðleg, hvort sem er.
Ef þú sérð að þú ert með meira en 5-10-20 af þeim í gagnagrunninum þínum, þá þarftu að hafa áhyggjur og byrja að gera eitthvað með þeim.
Hér notum við einnig fyrir útreikningstímann clock_timestamp(). Við tökum viðskipti og fínstillum forritið.
Eins og ég sagði hér að ofan er blokkun þegar tvö eða fleiri viðskipti berjast um eina eða hóp auðlinda. Fyrir þetta höfum við akur waiting með Boolean gildi true eða false.
Satt - þetta þýðir að ferlið er í bið, eitthvað þarf að gera. Þegar ferli bíður þýðir það að viðskiptavinurinn sem hóf þetta ferli bíður líka. Viðskiptavinurinn situr í vafranum og bíður líka.
Viðvörun: _Byrjað á Postgres útgáfu 9.6 sviði waiting fjarlægð og tveimur upplýsandi reitum bætt við í staðinn wait_event_type и wait_event._
Hvað á að gera? Ef þú sérð satt í langan tíma þýðir það að þú þarft að losna við slíkar beiðnir. Við einfaldlega skjótum niður svona viðskipti. Við skrifum til hönnuða að þeir þurfi einhvern veginn að hagræða þannig að ekkert kapphlaup verði um fjármagn. Og svo hagræða forritararnir forritið þannig að þetta gerist ekki.
Og öfgafulla, en hugsanlega ekki banvæna málið er uppkoma stöðvunar. Tvær færslur uppfærðu tvær tilföng og fengu síðan aðgang að þeim aftur, að þessu sinni í gagnstæðar tilföng. Í þessu tilviki drepur PostgreSQL viðskiptin sjálf svo að önnur geti haldið áfram að vinna. Þetta er dauðastaða og hún getur ekki fundið út úr því sjálf. Þess vegna neyðist PostgreSQL til að grípa til öfgafullra ráðstafana.
Og hér eru tvær fyrirspurnir sem gera þér kleift að fylgjast með lokun. Við notum útsýni pg_locks, sem gerir þér kleift að fylgjast með þungum læsingum.
Og fyrsti hlekkurinn er beiðnitextinn sjálfur. Það er frekar langt.
Og seinni hlekkurinn er grein um lása. Það er gagnlegt að lesa, það er mjög áhugavert.
Svo hvað sjáum við? Við sjáum tvær beiðnir. Viðskipti við ALTER TABLE er hindrunarviðskipti. Það byrjaði en kláraðist ekki og forritið sem skráði þessa færslu er að gera aðra hluti einhvers staðar. Og önnur beiðnin er uppfærsla. Hann bíður eftir að breytingaborðinu ljúki áður en hann getur haldið áfram vinnu sinni.
Þannig getum við komist að því hver læsti hverjum, heldur hverjum og við getum tekist á við það frekar.
Næsta eining er pg_stat_statements. Eins og ég sagði, þetta er eining. Til að nota það þarftu að hlaða bókasafni þess í uppsetninguna, endurræsa PostgreSQL, setja upp eininguna (með einni skipun) og þá munum við fá nýtt útsýni.
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;Hvað getum við tekið þaðan? Ef við tölum um einfalda hluti getum við tekið meðalframkvæmdartíma fyrirspurna. Tíminn vex, sem þýðir að PostgreSQL bregst hægt og við þurfum að gera eitthvað.
Við getum skoðað virkustu skriffærslurnar í gagnagrunninum sem breyta gögnum í sameiginlegum biðmunum. Sjáðu hver uppfærir eða eyðir gögnum þar.
Og við getum einfaldlega skoðað mismunandi tölfræði fyrir þessar beiðnir.
Við pg_stat_statements Við notum það til að búa til skýrslur. Við endurstillum tölfræðina einu sinni á dag. Við skulum safna því. Áður en þú endurstillir tölfræðina næst skulum við búa til skýrslu. Hér er hlekkur á skýrsluna. Þú getur horft á það.
Hvað erum við að gera? Við reiknum út almenna tölfræði fyrir allar beiðnir. Síðan, fyrir hverja beiðni, teljum við einstaklingsframlag hennar til þessarar heildartölfræði.
Og hvað getum við horft á? Við getum skoðað heildarframkvæmdartíma allra beiðna af tiltekinni gerð á bakgrunni allra annarra beiðna. Við getum skoðað CPU og I/O auðlindanotkun miðað við heildarmyndina. Og fínstilltu nú þegar þessar fyrirspurnir. Við erum að byggja upp helstu fyrirspurnirnar byggðar á þessari skýrslu og erum nú þegar að fá umhugsunarefni um hvað eigi að hagræða.
Hvað eigum við eftir á bak við tjöldin? Það eru enn nokkrar sendingar eftir sem ég tók ekki tillit til vegna þess að tíminn er takmarkaður.
Það er pgstattuple er einnig viðbótareining úr staðlaða framlagapakkanum. Það gerir þér kleift að meta bloat borðum, svokölluðum töflu sundrun. Og ef það er mikið af sundrungu þarftu að fjarlægja það og nota mismunandi verkfæri. Og virka pgstattuple virkar í langan tíma. Og því fleiri borð sem eru, því lengur mun það virka.
Næsta framlag er pg_buffercache. Það gerir þér kleift að skoða sameiginlega biðminni: hversu mikið og fyrir hvaða töflur biðminni eru notaðar. Og það gerir þér einfaldlega kleift að skoða sameiginlega biðminni og meta hvað er að gerast þar.
Næsta eining er pgfincore. Það gerir töfluaðgerðir á lágu stigi með kerfiskalli mincore(), þ.e. það gerir þér kleift að hlaða töflu í sameiginlega biðminni, eða losa hana. Og það gerir meðal annars kleift að skoða síðuskyndiminni stýrikerfisins, þ.
Næsta eining - pg_stat_kcache. Það notar einnig kerfiskall getrusage(). Og það framkvæmir það fyrir og eftir að beiðnin er framkvæmd. Og í tölfræðinni sem myndast gerir það okkur kleift að áætla hversu miklu beiðni okkar eyddi í I/O disks, þ.e.a.s. aðgerðir með skráarkerfinu og lítur á örgjörvanotkunina. Hins vegar er einingin ung (hósti hósti) og fyrir rekstur hennar þarf hún PostgreSQL 9.4 og pg_stat_statements, sem ég nefndi áðan.
-
Það er gagnlegt að vita hvernig á að nota tölfræði. Þú þarft ekki forrit frá þriðja aðila. Þú getur komið inn, séð, gert eitthvað, áorkað einhverju.
-
Að nota tölfræði er ekki erfitt, það er bara venjulegur SQL. Þú safnaðir beiðninni, settir hana saman, sendir hana, skoðaðir hana.
-
Tölfræði hjálpar til við að svara spurningum. Ef þú hefur spurningar snýrðu þér að tölfræði - skoðaðu, dragðu ályktanir, greindu niðurstöðurnar.
-
Og tilraunir. Það eru margar beiðnir, mikið af gögnum. Þú getur alltaf fínstillt fyrirliggjandi fyrirspurn. Þú getur búið til þína eigin útgáfu af beiðninni sem hentar þér betur en upprunalega og notað hana.
tilvísanir
Viðeigandi tenglar sem fundust í greininni, byggðir á efni, voru í skýrslunni.
Höfundur skrifa meira
(enska)
Tölfræðisafnari
Kerfisstjórnunaraðgerðir
Framlagseiningar
Dæmi um SQL tól og sql kóða
Þakka ykkur öllum fyrir athygli ykkar!
Heimild: www.habr.com