Përkthimi i raportit të vitit 2015 nga Aleksej Lesovski "Deep dive into PostgreSQL internal statistics"
Kujdes nga autori i raportit: Dua tĂ« theksoj se ky raport Ă«shtĂ« datuar nĂ« nĂ«ntor 2015 â kanĂ« kaluar mĂ« shumĂ« se 4 vjet dhe ka kaluar shumĂ« kohĂ«. Versioni i shqyrtuar nĂ« raportin 9.4 nuk mbĂ«shtetet mĂ«. GjatĂ« kĂ«tyre 4 viteve janĂ« lĂ«shuar 5 versione tĂ« reja ku janĂ« sjellĂ« shumĂ« risitĂ«, pĂ«rmirĂ«sime dhe ndryshime nĂ« lidhje me statistikĂ«n dhe shumĂ« nga materialet janĂ« bĂ«rĂ« tĂ« papĂ«rshtatshme dhe tĂ« pavlefshme. GjatĂ« rishikimit, pĂ«rpiqesha tĂ« shĂ«noja kĂ«to vende pĂ«r tĂ« mos tĂ« çuar nĂ« ngatĂ«rresĂ«, lexues i dashur. Nuk e kam shkruar pĂ«rsĂ«ri, Ă«shtĂ« shumicĂ« e madhe dhe do tĂ« rezultonte njĂ« raport krejtĂ«sisht ndryshe.
Sistemi i Menaxhimit të Shkathës PostgreSQL është një mekanizëm i madh, përveç që ky mekanizëm përbëhet nga shumë nën-sisteme, nga funksionimi i mirë i të cilave varet drejtpërdrejt performanca e sistemit. Gjatë procesit të përdorimit, mbledhja e statistikave dhe informacionit rreth funksionimit të komponenteve sigurohet, duke lejuar vlerësimin e eficiencës së PostgreSQL dhe marrjen e masave për të përmirësuar performancën. Megjithatë, kjo informacion është shumë e madhe dhe paraqitet në një formë mjaft të thjeshtë. Procesi i përpunimit të këtij informacioni dhe interpretimi i tij ndonjëherë është një detyrë e papritur, dhe "zooparku" i mjeteve dhe utilitareve lehtë mund të trazojë edhe një DBA të avancuar.
Përshëndetje! Unë quhem Aleksej. Siç tha Ilia, do të flas për statistikën e PostgreSQL.
Statistika e aktivitetit të PostgreSQL. PostgreSQL ka dy statistika. Statistika e aktivitetit, për të cilën do të flasim. Dhe statistika e planifikuesit për shpërndarjen e të dhënave. Do të flas pikërisht për statistikën e aktivitetit të PostgreSQL, e cila na lejon të gjykojmë për performancën dhe si ta përmirësojmë atë.
Do të tregoj se si ta përdorim efektivisht statistikën për zgjidhjen e problemeve të ndryshme që ju dalin ose mund të dalin.
ĂfarĂ« nuk do tĂ« ketĂ« nĂ« raport? NĂ« raport nuk do tĂ« diskutoj pĂ«r statistikĂ«n e planifikuesit, pasi kjo Ă«shtĂ« njĂ« temĂ« e veçantĂ« nĂ« njĂ« raport tĂ« veçantĂ« pĂ«r mĂ«nyrĂ«n se si tĂ« dhĂ«nat ruhen nĂ« bazĂ« dhe se si planifikuesi i kĂ«rkesave merr njĂ« pamje mbi karakteristikat cilĂ«sore dhe sasiore tĂ« kĂ«tyre tĂ« dhĂ«nave.
Dhe nuk do të ketë shqyrtime të mjeteve, nuk do të krahasoj një produkt me tjetrin. Nuk do të ketë asnjë reklame. Të heqim këto.
Dua t'ju tregoj se përdorimi i statistikave është i dobishëm. Kjo është e nevojshme. Të përdorësh atë nuk është frikësuese. Na nevojitet vetëm SQL-i i zakonshëm dhe njohuri bazike mbi SQL-in.
Dhe do të flasim se cila statistikë duhet zgjedhur për zgjidhjen e problemeve.
Nëse shikojmë PostgreSQL dhe në sistemin operativ ekzekutojmë një komandë për të parë proceset, do të shohim një "kutinë e zezë". Do të shohim disa procese që bëjnë diçka dhe nëpërmjet emrit mund të përfytyrojmë se çfarë ndodhin aty, çfarë po bëjnë. Por, në thelb, kjo është një kuti e zezë, ne nuk mund të shohim brenda.
Ne mund të shohim ngarkesën në procesor në top, mund të shohim shfrytëzimin e memories me disa utilitetet sistemike, por nuk do të mund t'i hedhim një sy brenda PostgreSQL. Për këtë, na duhen mjete të tjera.
Dhe duke vazhduar përpara, do t'ju tregoj ku shpenzohet koha. Nëse e paraqesim PostgreSQL si një skemë të tillë, do të mund të përgjigjemi se ku shpenzohet koha. Këto janë dy gjëra: trajtimi i kërkesave të klientëve nga aplikacionet dhe detyrat e prapambetura që kryen PostgreSQL për të ruajtur funksionalitetin e tij.
Nëse fillojmë të shqyrtojmë nga këndi i sipërm majtas, mund të ndjekim se si trajtohen kërkesat e klientëve. Kërkesa vjen nga aplikacioni dhe për punën e mëtejshme hapet një sesion klienti. Kërkesa kalon në planifikues. Planifikuesi ndërtuon një plan kërkese. E dërgon për realizim. Realizohet ndonjë hyrje-dalje e dhënave nga blloqet të lidhura me tabelat dhe indeksat. Të dhënat e nevojshme lexohen nga diskët në memorie në një zonë të veçantë "shared buffers". Rezultatet e kërkesës, nëse janë për përditësime, fshirje, regjistrohen në ditarin e transaksioneve në WAL. Disa informacione statistikore kalojnë në log ose në kolektorin e statistikave. Dhe rezultati i kërkesës i jepet klientit përsëri. Pas kësaj, klienti mund të përsërisë gjithçka nga fillimi me një kërkesë të re.
ĂfarĂ« ndodh me detyrat dhe proceset e prapambetura? Ne kemi disa procese qĂ« sigurojnĂ« funksionimin dhe mbajnĂ« bazĂ«n e tĂ« dhĂ«nave nĂ« njĂ« gjendje normale pune. KĂ«to procese do tĂ« trajtohen gjithashtu nĂ« raport: autovacuum, checkpointer, proceset lidhur me replikimin, background writer. Secilin prej tyre do ta prek me gjatĂ« raportit.
Cilat janë problemet me statistikat?
- Ka shumĂ« informacion. PostgreSQL 9.4 ofron 109 metrika pĂ«r shikimin e tĂ« dhĂ«nave statistikore. MegjithatĂ«, nĂ«se nĂ« bazĂ«n e tĂ« dhĂ«nave ruhet shumĂ« tabela, skema, ose baza, tĂ« gjitha kĂ«to metrika do tĂ« duhet tĂ« shumĂ«zohen me numrin pĂ«rkatĂ«s tĂ« tabelave dhe bazave. Kjo do tĂ« thotĂ« se informata bĂ«het akoma mĂ« e madhe. ĂshtĂ« shumĂ« e lehtĂ« tĂ« humbasĂ«sh nĂ« tĂ«.
- Problemi tjetër është se statistika paraqitet nga numërues. Nëse ne e shohim këtë statistikë, do të shohim numërues që rriten vazhdimisht. Nëse ka kaluar shumë kohë që nga resetimi i statistikës, do të shohim vlera miliardesh. Këto nuk na thonë asgjë.
- Nuk ka histori. Nëse keni ndodhur ndonjë dështim, diçka ka rënë para 15-30 minutash, nuk do të mund të shfrytëzoni statistikën për të parë se çfarë ka ndodhur 15-30 minuta më parë. Ky është një problem.
- Mungesa e një instrumenti të integruar në PostgreSQL është një problem. Zhvilluesit e bërthamës nuk ofrojnë asnjë utilitar. Nuk kanë asgjë të tillë. Ata thjesht ofrojnë statistikën në bazë. Përdorni, bëni kërkesa, bëni çfarë dëshironi.
- Duke qenĂ« se nuk ka njĂ« instrument tĂ« integruar nĂ« PostgreSQL, kjo Ă«shtĂ« njĂ« shkak tjetĂ«r i problemit. ShumĂ« instrumente tĂ« jashtme. Ădo kompani qĂ« ka disa aftĂ«si pĂ«r tĂ« zhvilluar pĂ«rpiqet tĂ« shkruajĂ« programin e vet. Dhe nĂ« fund, nĂ« komunitet ka shumĂ« instrumente qĂ« mund tĂ« pĂ«rdoren pĂ«r tĂ« punuar me statistikĂ«n. Disa instrumente kanĂ« disa mundĂ«si, tĂ« tjerat nuk kanĂ« disa mundĂ«si, ose kanĂ« disa mundĂ«si tĂ« reja. KĂ«shtu qĂ« krijohet njĂ« situatĂ« ku duhet tĂ« pĂ«rdoren dy, tri, katĂ«r instrumente qĂ« pĂ«rputhen me njĂ«ri-tjetrin dhe kanĂ« funksione tĂ« ndryshme. Kjo Ă«shtĂ« shumĂ« e pakĂ«ndshme.
ĂfarĂ« ndodh nga kjo? ĂshtĂ« e rĂ«ndĂ«sishme tĂ« jeni nĂ« gjendje tĂ« merrni statistikĂ«n direkt, pĂ«r tĂ« mos u varur nga programet, ose ndonjĂ«herĂ« tĂ« pĂ«rmirĂ«soni vetĂ« kĂ«to programe: tĂ« shtoni disa funksione pĂ«r tĂ« pĂ«rfituar.
Dhe nevojiten njohuri bazike SQL. Për të marrë disa të dhëna nga statistika, duhet të formoni kërkesa SQL, domethënë, ju nevojitet të dini si të formoni select, join.
Statistika na ofron disa gjëra. Ato mund të ndahen në kategori.
- Kategoria e parë janë ngjarjet që ndodhin brenda bazës. Kjo është kur ndodh një ngjarje në bazë: një kërkesë, një referencë në tabelë, autovakuumi, komitë, këto janë të gjitha ngjarje. Numëruesit përkatës për këto ngjarje inkrementohen. Kështu që mund të ndjekim këto ngjarje.
- Kategoria e dytë janë vetitë e objekteve siç janë tabelat, bazat. Ato kanë disa veti. Kjo është madhësia e tabelave. Mund të ndjekim rritjen e tabelave, rritjen e indekseve. Mund të shohim ndryshimet në dinamikë.
- Kategoria e tretë është koha e shpenzuar për një ngjarje. Një kërkesë është një ngjarje. Ajo ka një masë specifike të kohëzgjatjes. Këtu filloi, këtu përfundoi. Mund ta ndjekim. Po ashtu edhe koha e leximit të bllokut nga disku ose e shkruarjes. Këto gjëra gjithashtu ndjekin.
Burimet e statistikës janë paraqitur si më poshtë:
- Në memorjen e ndarë (shared buffers) ka një segment për vendosjen e të dhënave statistikore, ku ndodhen edhe ato numërues që inkrementohen vazhdimisht kur ndodhin disa ngjarje ose shfaqen disa çaste në punën e bazës.
- Të gjithë këta numërues nuk janë të aksesueshëm për përdoruesit dhe as për administratorin. Këto janë gjëra të nivelit të ulët. Për të pasur qasje në to, PostgreSQL ofron një ndërfaqe në formën e funksioneve SQL. Mund të bëjmë seleksione me ndihmën e këtyre funksioneve dhe të marrim disa metrika (ose një grup metrike).
- Megjithatë, perdorimi i këtyre funksioneve nuk është gjithmonë i përshtatshëm, kështu që funksionet janë baza për pamjet (VIEWs). Këto janë tabela virtuale që ofrojnë statistikë për një nënsistem të caktuar, ose për një grup ngjarjesh në bazën e të dhënave.
- Këto pamje të ndërtuara brenda (VIEWs) janë ndërfaqja kryesore e përdoruesit për të punuar me statistikën. Ato janë të aksesueshme siç janë pa pasur nevojë për ndonjë konfigurim të mëtejshëm, mund të filloni t'i përdorni menjëherë, të shihni, të merrni informacion prej aty. Dhe ka gjithashtu kontribuime. Kontribuimet janë zyrtare. Mund të instaloni paketën postgresql-contrib (për shembull, postgresql94-contrib), të ngarkoni modul të nevojshëm në konfigurim, të tregoni parametrat për të, të ri-nderprisni PostgreSQL dhe të filloni ta përdorni. (Shënim. Në varësi të distribuimit, në versionet e fundit, paketa e kontribuimit është pjesë e paketës kryesore.).
- Dhe ka kontribucione jo zyrtare. Ata nuk vijnë me shpërndarjen standard të PostgreSQL. Duhet t'i kompiloni ose t'i instaloni si bibliotekë. Opcionesh mund të jenë të ndryshme, në varësi të asaj çfarë ka menduar zhvilluesi i këtij kontribuimi jo zyrtar.
Në këtë slajd janë paraqitur të gjitha këto pamje (VIEWs) dhe disa prej atyre funksioneve që janë të disponueshme në PostgreSQL 9.4. Siç shohim, ka shumë prej tyre. Dhe është mjaft e lehtë të ngatërrohesh nëse hasesh për herë të parë me këtë.
MegjithatĂ«, nĂ«se e marrim figurĂ«n e mĂ«parshme Si shpĂ«rndahet koha nĂ« PostgreSQL dhe e pĂ«rputhim me kĂ«tĂ« listĂ«, atĂ«herĂ« do tĂ« marrim njĂ« figurĂ« tĂ« tillĂ«. Ădo pamje (VIEWs) ose çdo funksion mund ta pĂ«rdorim pĂ«r qĂ«llime tĂ« ndryshme pĂ«r tĂ« marrĂ« statistikat pĂ«rkatĂ«se, kur PostgreSQL funksionon. Dhe mund tĂ« marrim tashmĂ« ndonjĂ« informacion pĂ«r funksionimin e nĂ«n-sistemit.
E para që do të shikojmë është pg_stat_database. Siç e shohim, kjo është pamja. Ajo ka shumë informacion. Informacioni është shumë i larmishëm. Dhe ajo ofron njohuri shumë të dobishme mbi atë që ndodh në bazën e të dhënave.
ĂfarĂ« mund tĂ« marrim tĂ« dobishme nga aty? Le tĂ« fillojmĂ« me gjĂ«rat mĂ« tĂ« thjeshta.
select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;E para qĂ« mund tĂ« shohim â Ă«shtĂ« pĂ«rqindja e goditjeve nĂ« cache. PĂ«rdorimi nĂ« cache â Ă«shtĂ« njĂ« metrikĂ« e dobishme. Ajo lejon tĂ« vlerĂ«sojmĂ« se sa tĂ« dhĂ«na merret nga bufferat e ndarĂ« shared, dhe sa tĂ« dhĂ«na lexohen nga disku.
Natyrisht sa më shumë që kemi goditje në cache, aq më mirë. Ne e vlerësojmë këtë metrikë si përqindje. Dhe, për shembull, po që nëse përqindja e goditjeve në cache është mbi 90%, atëherë është mirë. Nëse bie poshtë 90%, atëherë do të thotë se kemi mungesë memorie për të mbajtur "kokën" e nxehtë të të dhënave në memorie. Dhe për të përdorur këto të dhëna, PostgreSQL është e detyruar të vijë tek disku dhe kjo është më e ngadalshme se sa të dhënat të lexohen nga memoria. Kështu që duhet të mendojmë për rritjen e memories: ose të rregullojmë bufferat e ndarë, ose të rritim memorien fizike (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;ĂfarĂ« tjetĂ«r mund tĂ« marrim nga kjo pamje? Mund tĂ« shikojmĂ« anomali qĂ« ndodhin nĂ« bazĂ«. ĂfarĂ« tregon kĂ«tu? Ka commits, rollbacks, krijimin e fileve pĂ«rkohĂ«she, volumin e tyre, deadlocks dhe konflikte.
Mund të shfrytëzojmë këtë kërkesë. Ky SQL është mjaft i thjeshtë. Dhe mund të shohim këto të dhëna në sistemin tonë.
Dhe këtu janë menjëherë vlerat kufi. Ne shikojmë raportin midis commits dhe rollbacks. Commits janë konfirmime të suksesshme të transaksionit. Rollbacks janë ristrukturim, pra, transaksioni bënte ndonjë punë, ngarkonte databazën, llogariste diçka, e pastaj ndodhi një dështim dhe rezultatet e transaksionit hidhen poshtë. Kështu, numri i rollbacks që rritet vazhdimisht është i pafavorshëm. Duhet të shmangim ndodhi të tilla dhe të rregullojmë kodin për të parandaluar këtë.
Konfliktet janë të lidhura me replikimin. Dhe ato gjithashtu duhet të shmangen. Nëse keni disa kërkesa që ekzekutohen në replikë dhe ndodhin konflikte, duhet t'i shqyrtoni këto konflikte, të shikoni çfarë po ndodh. Detajet mund të gjenden në log. Dhe të zgjidhni situatat konfliktuale që kërkesat e aplikacionit të funksionojnë pa gabime.
Deadlocks është gjithashtu një situatë e keqe. Kur kërkesat garojnë për burime, një kërkesë i drejtohet një burimi dhe merr një bllokim, kërkesa tjetër i drejtohet një burimi tjetër dhe gjithashtu merr një bllokim, pastaj të dyja kërkesat i drejtohen burimeve të njëra-tjetrës dhe bllokohen duke pritur që njeriu fqinj të lirojë bllokimin. Kjo gjithashtu është një situatë problematike. Ato duhet të zgjidhen në nivelin e rishkrimit të aplikacioneve dhe serializimit të aksesit në burime. Dhe nëse shihni se deadlocks tuaj po rriten vazhdimisht, duhet të shikoni detajet në log, të shqyrtoni situatat e shfaqura dhe të shikoni ku është problemi.
Skedarët temporal janë gjithashtu të këqij. Kur një kërkesë nga përdoruesi nuk ka mjaftueshëm memorie për të ruajtur të dhëna temporale, ajo krijon një skedar në disk. Dhe të gjitha operacionet që mund të kryejë në buffer-in temporar në memorie, këto fillon ti kryejë tashmë në disk. Kjo është ngadalë. Kjo rrit kohën e ekzekutimit të kërkesës. Dhe klienti, që dërgoi kërkesën në PostgreSQL, do të marrë një përgjigje pak më vonë. Nëse të gjitha këto operacione do të kryhen në memorie, Postgres do të përgjigjet shumë më shpejt dhe klienti do të presë më pak.
Pg_stat_bgwriter është një paraqitje që përshkruan punën e dy nënsistemeve të prapme të PostgreSQL: kjo është checkpointer dhe background writer.
Fillimisht do tĂ« shqyrtojmĂ« pikĂ«t kontrolle, tĂ« njohura si checkpoints. ĂfarĂ« janĂ« piketat e kontrollit? NjĂ« pikĂ« kontrolli Ă«shtĂ« njĂ« pozicion nĂ« regjistrin e transaksioneve qĂ« njofton se tĂ« gjitha ndryshimet e tĂ« dhĂ«nave tĂ« regjistruara nĂ« regjistĂ«r janĂ« sinkronizuar me sukses me tĂ« dhĂ«nat nĂ« disk. Procesi, nĂ« varĂ«si tĂ« ngarkesĂ«s sĂ« punĂ«s dhe konfigurimeve, mund tĂ« jetĂ« i gjatĂ« dhe nĂ« tĂ« shumtĂ«n e rasteve pĂ«rfshin sinkronizimin e faqeve tĂ« ndotura nĂ« buffers tĂ« pĂ«rbashkĂ«t me skedarĂ«t e tĂ« dhĂ«nave nĂ« disk. PĂ«r çfarĂ« Ă«shtĂ« kjo e nevojshme? NĂ«se PostgreSQL do tĂ« kishim qasje gjithmonĂ« nĂ« disk pĂ«r tĂ« marrĂ« tĂ« dhĂ«na dhe pĂ«r t'i shkruar ato me çdo qasje, do tĂ« ishte e ngadalĂ«t. Prandaj, PostgreSQL ka njĂ« segment tĂ« memories, madhĂ«sia e tĂ« cilit varet nga parametrat nĂ« konfigurim. PostgreSQL vendos tĂ« dhĂ«nat operuese nĂ« kĂ«tĂ« memorie pĂ«r pĂ«rpunim tĂ« mĂ«tejshĂ«m ose pĂ«r t'i ofruar ato sipas kĂ«rkesave. NĂ« rastin e kĂ«rkesave pĂ«r ndryshimin e tĂ« dhĂ«nave, ato pĂ«sojnĂ« ndryshime. KĂ«shtu, ne marrim dy versione tĂ« tĂ« dhĂ«nave. NjĂ«ra Ă«shtĂ« nĂ« memorie, tjetra Ă«shtĂ« nĂ« disk. Dhe herĂ« pas here Ă«shtĂ« e nevojshme t'i sinkronizojmĂ« kĂ«to tĂ« dhĂ«na. Na nevojitet qĂ« ajo qĂ« Ă«shtĂ« ndryshuar nĂ« memorie, tĂ« sinkronizohet nĂ« disk. KĂ«tu nevojitet piketa e kontrollit.
Piketat e kontrollit kalojnë nëpër buffers të përbashkët, etiketojnë faqe të ndotura, që ato janë të nevojshme për piketat e kontrollit. Pastaj fillon një kalim i dytë nëpër buffers të përbashkët. Dhe faqet që janë etiketuar për piketat e kontrollit, ato tani sinkronizohen. Kështu, përfundon sinkronizimi i të dhënave me disku.
Ka dy lloje piketash tĂ« kontrollit. NjĂ« pikĂ« kontrolli zbatohet sipas skadimit. Kjo pikĂ« kontrolli Ă«shtĂ« e dobishme dhe e mirĂ« â checkpoint_timed. Dhe ka piketat e kontrollit sipas kĂ«rkesĂ«s â checkpoint required. NjĂ« pikĂ« kontrolli e tillĂ« ndodh kur kemi njĂ« shkarkim shumĂ« tĂ« madh tĂ« tĂ« dhĂ«nave. Ne kemi regjistruar shumĂ« regjistra transaksionesh. Dhe PostgreSQL mendon se i nevojitet ta sinkronizojĂ« gjithçka sa mĂ« shpejt tĂ« jetĂ« e mundur, tĂ« krijojĂ« njĂ« pikĂ« kontrolli dhe tĂ« vazhdojĂ« mĂ« tej.
Dhe nëse e keni parë statistikën pg_stat_bgwriter dhe keni parë se ju keni checkpoint_req shumë më tepër se checkpoint_timed, atëherë kjo është e keqe. Pse është e keqe? Kjo do të thotë që PostgreSQL ndodhet në një situatë të vazhdueshme stresi, kur i nevojitet të shkruajë të dhëna në disk. Piketa e kontrollit sipas skadimit është më pak stresuese dhe zbatohet sipas një orari të brendshëm dhe është siç thonë, e shpërndarë në kohë. PostgreSQL ka mundësinë të bëjë pause në punë dhe të mos e ngarkojë nënstrukturën e diskut. Kjo është e dobishme për PostgreSQL. Dhe kërkesat që ekzekutohen gjatë piketës së kontrollit nuk do të ndiejnë stres nga fakti se nënstruktura e diskut është e angazhuar.
Dhe për rregullimin e checkpoint ka tre parametra:
checkpoint_segments.checkpoint_timeout.checkpoint_competion_target.
Këta lejojnë rregullimin e punës së pikave të kontrollit. Por nuk do të ndalem shumë te ato. Të ndikimit të tyre - kjo është një temë e veçantë.
Kujdes: Versioni 9.4 i diskutuar në raport tashmë nuk është aktual. Në versionet moderne të PostgreSQL, parametri checkpoint_segments është zëvendësuar me parametrat min_wal_size dhe max_wal_size.
NĂ«nĂ«sistemi tjetĂ«r Ă«shtĂ« shkruesi i sfondit - background writer. ĂfarĂ« bĂ«n ai? Ai punon vazhdimisht nĂ« njĂ« cikĂ«l tĂ« pafund. Skanon faqet nĂ« buffers tĂ« ndarĂ« dhe faqet e ndotura qĂ« gjen, i shkarkon nĂ« disk. KĂ«shtu ndihmon checkpointer-in tĂ« bĂ«jĂ« punĂ« mĂ« pak gjatĂ« ekzekutimit tĂ« pikave tĂ« kontrollit.
Për çfarë tjetër është i nevojshëm? Ai siguron nevojën për faqe të pastra në buffers të ndarë nëse ato papritmas kërkohen (në sasi të mëdha dhe papritmas) për ruajtjen e të dhënave. Supozoni se ndodhi një situatë kur për ekzekutimin e një kërkese u nevojiten faqe të pastra dhe ato janë tashmë në buffers të ndarë. PostgreSQL-i backend thjesht i merr dhe i përdor, nuk ka nevojë të pastrojë asgjë vetë. Por nëse papritmas nuk ka faqesh të tilla, backend ndalon punën dhe fillon të kërkojë faqe për t'i shkarkuar në disk dhe për t'i përdorur për nevojat e tij - që ndikon negativisht në kohën e kërkesës që po ekzekutohet në atë moment. Nëse shihni që parametri juaj maxwritten_clean është i madh, kjo do të thotë se shkruesi i sfondit nuk po përmbush detyrat e tij dhe duhet të rriten parametrat bgwriter_lru_maxpages, që ai të mund të bëjë më shumë punë në një cikël, të pastrojë më shumë faqe.
Dhe një tregues tjetër shumë i dobishëm - është buffers_backend_fsync. Backend-et nuk bëjnë fsync, sepse është i ngadaltë. Ata e kalojnë fsync më lart në IO stack te checkpointer-i. Checkpointer-i ka radhën e tij, ai përpunon fsync nga koha në kohë dhe sinkronizon faqet në kujtesë me skedarët në disk. Nëse radha është e madhe te checkpointer-i dhe e mbushur, atëherë backend-i është i detyruar të bëjë vetë fsync dhe kjo ngadalëson punën e backend-it,, dmth. klienti do të marrë përgjigje më vonë se sa mund të kishte. Nëse shihni se kjo vlerë është më e madhe se zero, atëherë kjo është një problem dhe duhet të kushtoni vëmendje parametrave të shkruesit të sfondit dhe gjithashtu të vlerësoni performancën e nënësistemit të disqeve.
Kujdes: _Teksti në vazhdim përshkruan përfaqësime statistikore të lidhura me replikimin. Pjesa më e madhe e emrave të përfaqësimeve dhe funksioneve është riemëruar në Postgres 10. Esenca e riemërimeve qëndron në zëvendësimin xlog në wal dhe location në në emrat e funksioneve/përfaqësimeve etj. Një shembull konkret, funksioni pg_xlog_location_diff() u riemërua në pg_wal_lsn_diff() Kemi shumë informacione këtu. Por do na nevojiten vetëm pikat që lidhen me lokacionin.._
Nëse shohim se të gjitha vlerat janë të barabarta, atëherë ky është varianti ideal dhe replika nuk vonohet nga masteri.
Kjo pozita në sistemin hexadecimal është pozita në regjistrin e transaksioneve. Ajo vazhdimisht rritet nëse në bazë ka ndonjë aktivitet: shtesa, fshirje etj.
sa është shkruar xlog në bytes $ select pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000'); shkalla e vonesës së replikimit në byte $ select client_addr, pg_xlog_location_diff(pg_current_xlog_location(), replay_location) from pg_stat_replication; shkalla e vonesës së replikimit në sekonda $ select extract(epoch from now() - pg_last_xact_replay_timestamp());
Nëse këto gjëra ndryshojnë, atëherë ka një vonesë. Vonesa është prapambetje e replikës nga masteri, pra të dhënat ndryshojnë mes serverëve.Ka tre arsye për prapambetje:
Kjo është sepse sub-sistemi i diskut nuk arrin të përballojë regjistrimin e sinkronizimit të skedareve.
- Kjo mund të jetë për shkak të gabimeve në rrjet, ose ngarkesës në rrjet, kur të dhënat nuk arrijnë në replikë dhe ai nuk mund t'i riprodhojë.
- Dhe procesori. Procesori është një rast shumë i rrallë. Kam parë diçka të tillë dy ose tre herë, por gjithashtu mund të ndodhë.
- Dhe këtu janë tre kërkesa që na lejojnë të përdorim statistikat. Mund të vlerësojmë sa është shkruar në regjistrin e transaksioneve. Ka një funksion
pg_xlog_location_diff dhe mund të vlerësojmë vonesën e replikimit në byte dhe sekonda. Ne gjithashtu e përdorim atë vlerë nga këtë përfaqësim (VIEWs). _Në vend të pg_xlog_location
VĂ«rejtje: diff() funksioni mund tĂ« pĂ«rdorim operatorin e zbritjes dhe tĂ« zbrisnim njĂ« lokacion nga tjetri. ĂshtĂ« e pĂ«rshtatshme.Me vonesĂ«n qĂ« Ă«shtĂ« nĂ« sekonda, ka njĂ« moment. NĂ«se nĂ« master nuk ka asnjĂ« aktivitet, transaksioni atje ishte diku 15 minuta mĂ« parĂ« dhe nuk ka aktivitet, dhe nĂ«se shohim kĂ«tĂ« vonesĂ« nĂ« replikĂ«, do tĂ« shohim njĂ« vonesĂ« prej 15 minutash. Kjo Ă«shtĂ« diçka pĂ«r tĂ« cilĂ«n duhet tĂ« kujdesemi. Dhe kjo mund tĂ« shkaktojĂ« konfuzion kur shihni kĂ«tĂ« vonesĂ«.
Kjo mund të shkaktojë konfuzion kur shihni këtë vonesë.
Pg_stat_all_tables â njĂ« tjetĂ«r pamje e dobishme. Ajo tregon statistikĂ«n pĂ«r tabelat. Kur kemi tabela nĂ« bazĂ«n tonĂ«, me ndonjĂ« aktivitet, ndonjĂ« veprim, ne mund ta marrim kĂ«tĂ« informacion nga kjo pamje.
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;E para që mund të shohim është skanimi sekondar për tabelën. Numri vete pas këtyre kalimeve nuk është domosdoshmërisht i keq dhe nuk është tregues se duhet të ndërmarrim diçka.
MegjithatĂ«, ka njĂ« metrikĂ« tjetĂ«r â seq_tup_read. Kjo Ă«shtĂ« numri i rreshtave tĂ« kthyer si rezultat i skanimit sekondar. NĂ«se numri mesatar kalon 1,000, 10,000, 50,000, 100,000, atĂ«herĂ« kjo Ă«shtĂ« njĂ« tregues se ndoshta duhet tĂ« ndĂ«rtohet ndonjĂ« indeks, qĂ« tĂ« kemi qasje pĂ«rmes indeksit, ose ndoshta tĂ« optimizohen kĂ«rkesat qĂ« pĂ«rdorin skanime tĂ« tilla, qĂ« tĂ« mos ndodhin.
NjĂ« shembull i thjeshtĂ« â le tĂ« themi se njĂ« kĂ«rkesĂ« me OFFSET dhe LIMIT tĂ« madh Ă«shtĂ« e nevojshme. PĂ«r shembull, skanohen 100,000 rreshta nĂ« tabelĂ« dhe pas kĂ«saj merren 50,000 rreshta tĂ« nevojshĂ«m, ndĂ«rsa rreshtat e skanuar mĂ« parĂ« pĂ«rjashtohen. Ky Ă«shtĂ« gjithashtu njĂ« rast i keq. KĂ«to kĂ«rkesa duhet tĂ« optimizohen. Dhe kĂ«tu ka njĂ« SQL tĂ« thjeshtĂ«, mbi tĂ« cilin mund tĂ« shohim dhe vlerĂ«sojmĂ« shifrat e marra.
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;Madhësitë e tabelave gjithashtu mund të merren përmes kësaj tabele dhe me funksione shtesë pg_total_relation_size(), pg_relation_size().
Në thelb, ka komanda metaverse dt dhe di, të cilat mund të përdoren në PSQL dhe gjithashtu të shikoni madhësitë e tabelave dhe indekseve.
Megjithatë, përdorimi i funksioneve na ndihmon të shohim madhësitë e tabelave gjithashtu duke marrë parasysh indekset, ose pa marrë parasysh indekset dhe tashmë të bëjmë disa vlerësime mbi rritjen e bazës së të dhënave, dmth si po rritet, me çfarë intensiteti dhe të nxjerrim disa përfundime mbi optimizimin e madhësive.
Aktiviteti nĂ« shkrim. ĂfarĂ« Ă«shtĂ« shkrimi? Le tĂ« shqyrtojmĂ« operacionin UPDATE â operacionin e pĂ«rditĂ«simit tĂ« rreshtave nĂ« tabelĂ«. NĂ« thelb, update Ă«shtĂ« dy operacione (nĂ« fakt mĂ« shumĂ«). Kjo Ă«shtĂ« njĂ« futje e njĂ« versioni tĂ« ri tĂ« rreshtit dhe shĂ«nimi i versionit tĂ« vjetĂ«r tĂ« rreshtit si tĂ« vjetruar. MĂ« vonĂ« do tĂ« vijĂ« avokati automatik dhe do tĂ« pastrojĂ« kĂ«to versione tĂ« vjetra tĂ« rreshtave, duke e shĂ«nuar kĂ«tĂ« vend si tĂ« disponueshĂ«m pĂ«r ripĂ«rdorim.
Për më tepër, update nuk është vetëm përditësimi i tabelës. Kjo është gjithashtu përditësimi i indekseve. Nëse keni shumë indekse në tabelë, atëherë gjatë update të gjitha indekset, në të cilat merrni pjesë fusha të përditësuara në kërkesë, gjithashtu duhet të përditësohen. Në këto indekse do të ketë gjithashtu versione të vjetra të rreshtave, të cilat duhen pastruar.
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;Dhe pĂ«r shkak tĂ« dizajnit tĂ« saj, UPDATE Ă«shtĂ« operacione tĂ« rĂ«nda. Por ato mund tĂ« lehtĂ«sohen. Ka pĂ«rditĂ«sime tĂ« nxehta. Ato u shfaqĂ«n nĂ« versionin 8.3 tĂ« PostgreSQL. ĂfarĂ« janĂ« ato? Kjo Ă«shtĂ« njĂ« pĂ«rditĂ«sim i lehtĂ«, i cili nuk shkakton rindarjen e indekseve. KĂ«shtu, ne pĂ«rditĂ«suam regjistrimin, por gjatĂ« kĂ«saj pĂ«rditĂ«sua vetĂ«m regjistrimi nĂ« faqen (e cila i pĂ«rket tabelĂ«s), ndĂ«rsa indekset vazhdojnĂ« tĂ« tregojnĂ« pĂ«r tĂ« njĂ«jtin regjistrim nĂ« faqe. Atje ka njĂ« logjikĂ« interesante tĂ« funksionimit, kur vjen vakuumi, ai rindĂ«rton kĂ«to lidhje tĂ« nxehta dhe gjithçka vazhdon tĂ« funksionojĂ« pa pĂ«rditĂ«simin e indekseve, dhe ndodh gjithçka me njĂ« konsum mĂ« tĂ« vogĂ«l tĂ« burimeve.
Dhe kur keni n_tup_hot_upd të lartë, kjo është shumë mirë. Kjo do të thotë se përditësimet e lehta mbizotërojnë dhe kjo na dalin më lirë në burime dhe gjithçka është në rregull.
ALTER TABLE tabela_emri SET (fillfactor = 70);Si të rrisim volumet e përditësimeve të nxehtamunde të përdorim fillfactor. Ai përcakton madhësinë e hapësirës së lirë të rezervuar kur mbushni faqen në tabelë me anë të INSERT-ve. Kur në tabelë hyjnë futje, ato plotësojnë plotësisht faqen, pa lënë hapësirë të lirë. Më pas krijohet një faqe e re. Sërish të dhënat plotësohen. Dhe ky është sjellja e paracaktuar, fillfactor = 100%.
Ne mund të bëjmë fillfactor në 70%. Kjo do të thotë se gjatë inserts, një faqe e re u ndan, por është mbushur vetëm 70% e saj. Dhe 30% mbetet si rezervë. Kur do të jetë e nevojshme të bëhet përditësimi, ka një mundësi të lartë që ai do të ndodhë në të njëjtën faqe, dhe versione e re e rreshtit do të vendoset në të njëjtën faqe. Dhe do të bëhet hot_update. Kështu lehtësohet regjistrimi në tabela.
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));Rradha e avtokakuumit. Avtokakuumi është një nën-sistem për të cilin statistika në PostgreSQL është shumë e pakët. Ne mund të shohim në tabela vetëm në pg_stat_activity për të parë sa avokume janë duke zgjatur në moment. Megjithatë, të kuptojmë se sa tabela janë në radhë është shumë e vështirë.
VĂ«rejtje: _QĂ« nga versioni Postgres 10, situata me monitorimin e avtokakuumit Ă«shtĂ« pĂ«rmirĂ«suar ndjeshĂ«m â Ă«shtĂ« shfaqur pamja pg_stat_progressvacuum, e cila e thjeshton ndjeshĂ«m çështjen e monitorimit tĂ« avtokakuumit.
Ne mund të përdorim një kërkesë të tillë të thjeshtuar. Dhe mund të shohim se kur duhet të bëhet vakumi. Por, si dhe kur duhet të fillojë vakumi? Këto janë versionet e tejkalura të rreshtave, për të cilat kam folur më parë. Ndodhi një përditësim, një version i ri rreshti u vendos. U shfaq një version i tejkaluar i rreshtit. Në tabelë pg_stat_user_tables ka një parametër të tillë n_dead_tup. Ai tregon numrin e rreshtave "të vdekur". Dhe sa herë që numri i rreshtave të vdekur kalon një prag të caktuar, avtokakuumi do të vijë në tabelë.
Dhe si llogaritet ky prag? Ky është një raport specifik përqindje nga numri i përgjithshëm i rreshtave në tabelë. Ka një parametër autovacuum_vacuum_scale_factor. Ai përcakton raportin përqindje. Supozoni, 10% + një prag bazë shtesë prej 50 rreshtash. Dhe çfarë ndodh? Kur numri i rreshtave të vdekur është më i madh se "10% + 50" nga të gjithë rreshtat në tabelë, atëherë e vendosim tabelën në avtokakum.
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));Megjithatë, këtu ka një moment. Pragjet bazë të parametrave av_base_thresh dhe av_scale_factor mund të caktohen individualisht. Dhe, përkatësisht, pragu nuk do të jetë global, por individual për tryezën. Prandaj, për ta llogaritur, është e nevojshme të përdoren truke dhe hile. Dhe nëse jeni të interesuar, mund të shihni përvojën e kolegëve tanë nga Avito (linku në slajd është i pavlefshëm dhe është përditësuar në tekst).
Ata shkruan për , i cili merr parasysh këto gjëra. Aty ka një dokument që zgjat dy faqe. Por ai llogarit saktë dhe ndihmon për të vlerësuar mjaft eficent ku kemi shumë vakuum të nevojshëm për tabelat, ku pak.
ĂfarĂ« mund tĂ« bĂ«jmĂ« me kĂ«tĂ«? NĂ«se kemi njĂ« radhĂ« tĂ« madhe dhe avotokum nuk pĂ«rballon, atĂ«herĂ« mund tĂ« rrisim numrin e punĂ«torĂ«ve tĂ« vakuumit, ose thjesht ta bĂ«jmĂ« vakuumin mĂ« agresiv, nĂ« mĂ«nyrĂ« qĂ« ai tĂ« aktivizohet mĂ« herĂ«t, tĂ« pĂ«rpunojĂ« tabelĂ«n nĂ« copa tĂ« vogla. Dhe kĂ«shtu radhĂ«t do tĂ« pakĂ«sohen. â E rĂ«ndĂ«sishme kĂ«tu Ă«shtĂ« tĂ« ndjekim ngarkesĂ«n nĂ« disqe, pasi vakuumi nuk Ă«shtĂ« i lirĂ«, megjithatĂ« me ardhjen e pajisjeve SSD/NVMe, problemi Ă«shtĂ« bĂ«rĂ« mĂ« pak i dukshĂ«m.
Pg_stat_all_indexes â kjo Ă«shtĂ« statistika pĂ«r indekset. Ajo Ă«shtĂ« e vogĂ«l. Dhe ne mund tĂ« marrim informacion pĂ«r pĂ«rdorimin e indekseve. PĂ«r shembull, mund tĂ« pĂ«rcaktojmĂ« cilat indekse janĂ« tĂ« tepĂ«rta.
Siç e thashĂ« mĂ« parĂ«, update â nuk Ă«shtĂ« vetĂ«m pĂ«rditĂ«simi i tabelave, por gjithashtu pĂ«rditĂ«simi i indekseve. Pra, nĂ«se kemi shumĂ« indekse nĂ« tabelĂ«, gjatĂ« pĂ«rditĂ«simit tĂ« rreshtave nĂ« tabelĂ«, indekset e fushave tĂ« indeksuara gjithashtu duhet tĂ« pĂ«rditĂ«sohen, dhe nĂ«se kemi indekse qĂ« nuk pĂ«rdoren, pĂ«r tĂ« cilat nuk ka skanime indekse, ato na mbajnĂ« peshĂ«. Dhe duhet tĂ« heqim dorĂ« nga ato. PĂ«r kĂ«tĂ« na nevojitet fusha idx_scan. Ne thjesht shohim numrin e skanimeve tĂ« indekseve. NĂ«se indekset kanĂ« zero skanime pĂ«r njĂ« periudhĂ« tĂ« gjatĂ« tĂ« ruajtjes sĂ« statistikave (jo mĂ« pak se 2-3 javĂ«), atĂ«herĂ« shumĂ« gjasĂ« ato janĂ« indekse tĂ« dobĂ«ta, duhet tĂ« heqim dorĂ« nga to.
Vërejtje: Gjatë kërkimit të indekseve që nuk përdoren në rastin e klastereve të replikimit të vazhdueshëm, duhet të kontrollojmë të gjithë nyjet e klasterit, pasi statistika nuk është globale, dhe nëse indeksi nuk përdoret në master, mund të përdoret në replika (nëse ka ngarkesë atje).
Dy linke:
Këto janë shembuj më të avancuar të pyetjeve se si të kërkoni indekse që nuk përdoren.
Linku i dytë është një kërkesë mjaft interesante. Aty është e vendosur një logjikë shumë e veçantë. E rekomandoj për t'u njohur.
ĂfarĂ« tjetĂ«r duhet tĂ« pĂ«rmbledhĂ«sh pĂ«r indeksat?
Indeksat e pa përdorur janë të këqija.
Zgjatin hapësirën.
Ngadalësojnë operacionet e përditësimit.
Puna e tepërt për vakuumin.
Nëse ne i hiqnim indeksat e pa përdorur, do t'i bënim bazës vetëm më të mirë.
Prezantimi tjetĂ«r Ă«shtĂ« pg_stat_activity. Kjo Ă«shtĂ« njĂ« analogji e utilitarit ps, vetĂ«m nĂ« PostgreSQL. NĂ«se psnĂ« âju shikoni proceset nĂ« sistemin operativ, atĂ«herĂ« pg_stat_activity do t'ju tregojĂ« aktiviteten brenda PostgreSQL.
ĂfarĂ« mund tĂ« marrim atje tĂ« dobishme?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Mund të shohim aktivitetin e përgjithshëm, çfarë po ndodh në bazë. Mund të bëjmë një shpërndarje të re. Atje të gjitha u shpërthejnë, nuk pranohet lidhje të reja, gabimet vërshojnë në aplikacion.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Mund të kryejmë një kërkesë të tillë dhe të shohim përqindjen e përgjithshme të lidhjeve në lidhje me limitin maksimal të lidhjeve dhe të shohim se kush ka më shumë lidhje. Dhe në rastin e dhënë, ne shohim që përdoruesi cron_role ka hapur 508 lidhje. Dhe diçka ka ndodhur me të. Duhet të merremi me të dhe është e mundur që kjo është një numër anormal lidhjesh.
Nëse kemi ngarkesë OLTP, kërkesat duhet të kryhen shpejt, shumë shpejt dhe nuk duhet të ketë kërkesa të gjata. Megjithatë, nëse ndodhin kërkesa të gjata, atëherë në një perspektivë afatshkurtër nuk ka asgjë të tmerrshme, por në një perspektivë afatgjatë, kërkesat e gjata dëmtojnë bazën, ato rrisin efektin e bloat në tabela, kur ndodhin fragmentime të tabelave. Dhe duhet të heqim dorë nga bloat dhe nga kërkesat e gjata.
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;Kujdes: me një kërkesë të tillë ne mund të identifikojmë kërkesat e gjata dhe transaksionet. Ne përdorim funksionin clock_timestamp() për të përcaktuar kohëzgjatjen e punës. Kërkesat e gjata që gjetëm, mund t'i ruajmë, t'i kryejmë explain, të shohim planet dhe të optimizojmë disi. Kërkesat aktuale të gjata i eliminojmë dhe vazhdojmë të jetojmë.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Transaksionet e këqija janë transaksionet në gjendjen idle in transaction dhe idle in transaction (aborted).
ĂfarĂ« do tĂ« thotĂ« kjo? Transaksionet kanĂ« disa gjendje. NjĂ« nga kĂ«to gjendje mund tĂ« jetĂ« nĂ« çdo moment. PĂ«r tĂ« pĂ«rcaktuar gjendjet, ka njĂ« fushĂ« state nĂ« kĂ«tĂ« pamje. Dhe ne e pĂ«rdorim atĂ« pĂ«r tĂ« pĂ«rcaktuar gjendjen.
select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';Dhe, siç e thashĂ« mĂ« sipĂ«r, kĂ«to dy gjendje idle in transaction dhe idle in transaction (aborted) â janĂ« tĂ« keqija. ĂfarĂ« janĂ«? Kjo ndodh kur aplikacioni hap njĂ« transaksion, bĂ«n disa veprime dhe largohet pĂ«r punĂ«t e tij. Transaksioni mbetet i hapur. Ai mbetet pezull, nuk ndodh asgjĂ«, zĂ« njĂ« lidhje, bllokime mbi rreshtat e ndryshuar dhe potencialisht rrit shfrytĂ«zimin e tabeleve tĂ« tjera, pĂ«r shkak tĂ« arkitekturĂ«s sĂ« motorit transaksional Postgres. Dhe kĂ«to transaksione gjithashtu duhet tĂ« eleminohen, sepse janĂ« tĂ« dĂ«mshme nĂ« çdo rast.
Nëse shihni se keni më shumë se 5-10-20 të tilla në bazën tuaj, atëherë duhet të shqetësoheni dhe të filloni të bëni diçka për to.
Këtu ne gjithashtu përdorim clock_timestamp()për kohën e llogaritjes. Ne eleminojmë transaksionet, optimizojmë aplikacionin.
Ashtu siç kam thënë më parë, bllokimet janë kur dy ose më shumë transaksione luftojnë për një ose një grup burimesh. Për këtë kemi një fushë waiting me një vlerë booleane true ose false.
True â do tĂ« thotĂ« se procesi Ă«shtĂ« nĂ« pritje, duhet tĂ« bĂ«het diçka. Kur procesi Ă«shtĂ« nĂ« pritje, do tĂ« thotĂ« se klienti qĂ« iniciuoi kĂ«tĂ« proces po pret gjithashtu. Klienti nĂ« shfletues po pret gjithashtu.
Kujdes: _Që nga versioni Postgres 9.6, fusha waiting është hequr dhe në vend të saj janë shtuar dy fusha më informuese wait_event_type dhe wait_event._
ĂfarĂ« tĂ« bĂ«jmĂ«? NĂ«se shihni true pĂ«r njĂ« kohĂ« tĂ« gjatĂ«, do tĂ« thotĂ« se duhet tĂ« heqim dorĂ« nga kĂ«rkesat e tilla. Thjesht eleminojmĂ« kĂ«to transaksione. U shkruajmĂ« zhvilluesve qĂ« duhet tĂ« optimizojnĂ« pĂ«r tĂ« shmangur garat pĂ«r burime. Dhe mĂ« pas zhvilluesit optimizojnĂ« aplikacionin qĂ« tĂ« mos ndodhin tĂ« tilla.
Dhe rasti ekstrem, por potencialisht jo fatal â Ă«shtĂ« kĂ«rcimi i deadlocks. Dy transaksione kanĂ« pĂ«rditĂ«suar dy burime, pastaj i referohen atyre pĂ«rsĂ«ri, tashmĂ« pĂ«r burimet e kundĂ«rta. PostgreSQL nĂ« kĂ«tĂ« rast merr dhe eleminon vetĂ« transaksionin, nĂ« mĂ«nyrĂ« qĂ« tjetri tĂ« mund tĂ« vazhdojĂ« punĂ«n. Kjo Ă«shtĂ« njĂ« situatĂ« e bllokuar dhe ajo nuk zgjidhet mĂ« vete. Prandaj PostgreSQL Ă«shtĂ« i detyruar tĂ« marrĂ« masa ekstreme.
Dhe këtu janë dy kërkesa që lejojnë të ndjekim bllokimet. Ne përdorim pamjen pg_locks, e cila ndihmon në ndjekjen e bllokimeve të rënda.
Dhe lidhja e parë është vetë teksti i pyetjes. Ai është mjaft i gjatë.
Dhe lidhja e dytĂ« Ă«shtĂ« artikulli mbi locks. ĂshtĂ« e dobishme tĂ« lexohet, shumĂ« interesante.
Pra, çfarĂ« shohim? Shohim dy pyetje. Transaksion me ALTER TABLE â Ă«shtĂ« njĂ« transaksion bllokues. Ai Ă«shtĂ« aktivizuar, por nuk Ă«shtĂ« pĂ«rfunduar dhe aplikacioni qĂ« ka nisur kĂ«tĂ« transaksion Ă«shtĂ« duke u marrĂ« me gjĂ«ra tĂ« tjera. Dhe pyetja e dytĂ« â update. Ai pret qĂ« tĂ« pĂ«rfundojĂ« alter table-in qĂ« tĂ« vazhdojĂ« punĂ«n e tij.
Kështu mund të zbulojmë se kush ka bllokuar kë dhe mund të merremi më tej me këtë.
Moduli tjetër është pg_stat_statements. Siç e përmenda, ky është një modul. Për ta përdorur, duhet të ngarkoni bibliotekën e tij në configuracion, të rinisni PostgreSQL, të instaloni modulin (me një komandë) dhe pastaj do të kemi një pamje të re.
Koha mesatare e pyetjes në milisekonda
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Pyetjet më aktive të shkruara (në shared_buffers)
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;ĂfarĂ« mund tĂ« marrim nga atje? NĂ«se flasim pĂ«r gjĂ«ra tĂ« thjeshta, mund tĂ« marrim kohĂ«n mesatare tĂ« ekzekutimit tĂ« pyetjes. NĂ«se koha rritet, atĂ«herĂ« PostgreSQL po pĂ«rgjigjet ngadalĂ« dhe duhet tĂ« marrim masa.
Mund të shohim transaksionet më aktive të shkruara në bazën e të dhënave, që ndryshojnë të dhënat në shared buffers. Të shikojmë se kush po përditëson ose fshin të dhëna.
Dhe mund të shohim thjesht statistika të ndryshme mbi këto pyetje.
Ne pg_stat_statements e përdorim për ndërtimin e raporteve. Një herë në ditë e resetem statistikën. E akumulojmë atë. Para rindërritjes së statistikave herën tjetër, ndërtojmë raportin. Këtu është lidhja për raportin. Mund ta shihni atë.
ĂfarĂ« bĂ«jmĂ«? Llogarisim statistikat totale pĂ«r tĂ« gjitha pyetjet. MĂ« pas, pĂ«r secilĂ«n pyetje llogarisim kontributin e saj individual nĂ« kĂ«tĂ« statistikĂ« tĂ« pĂ«rgjithshme.
Dhe çfarë mund të shohim? Mund të shohim kohën totale të ekzekutimit të të gjitha pyetjeve të një lloji specifik në sfondin e të gjitha pyetjeve të tjera. Mund të shohim përdorimin e burimeve të procesorit dhe input-output në lidhje me pamjen e përgjithshme. Dhe pastaj të optimizojmë këto pyetje. Ndërtojmë topin e pyetjeve mbi këtë raport dhe më pas marrim informacione për t'u menduar, se çfarë të optimizojmë.
ĂfarĂ« na ka mbetur pas skenĂ«s? JanĂ« ende disa prezantime qĂ« nuk i shqyrtova, sepse koha Ă«shtĂ« e kufizuar.
Ka pgstattuple â Ă«shtĂ« gjithashtu njĂ« modul shtesĂ« nga paketa standarde contribs. Ai lejon tĂ« vlerĂ«sosh bloat tabelat, e ashtuquajtur fragmentimi i tabelĂ«s. Dhe nĂ«se fragmentimi Ă«shtĂ« i madh, duhet tĂ« hiqet, tĂ« pĂ«rdoren mjete tĂ« ndryshme. Dhe funksioni pgstattuple punon pĂ«r njĂ« kohĂ« tĂ« gjatĂ«. Dhe sa mĂ« shumĂ« tabela tĂ« jenĂ«, aq mĂ« gjatĂ« do tĂ« punojĂ«.
Contrib-i tjetër është pg_buffercache. Ai lejon inspektimin e shfletuesve të ndarë: sa intensa dhe për cilat tabela shfrytëzohen faqet e shfletuesit. Dhe thjesht lejon të hedhësh një vështrim në shfletuesit e ndarë dhe të vlerësosh se çfarë ndodh atje.
Moduli tjetër është pgfincore. Ai lejon kryerjen e operacioneve me nivel të ulët me tabelat përmes thirrjeve sistemore mincore(), dmth ai lejon ngarkimin e një tabele në shfletuesit e ndarë ose shkarkimin e saj. Dhe, përveç kësaj, lejon inspektimin e caches së faqeve të sistemit operativ, dmth në çfarë sasi tabela jonë zë në cache të faqes, në shfletuesit e ndarë dhe thjesht lejon të vlerësohet ngarkesa e tabelës.
Moduli tjetër është pg_stat_kcache. Ai gjithashtu përdor thirrjen sistemore getrusage(). Dhe e ekzekuton atë para dhe pas ekzekutimit të pyetjes. Dhe në statistikën e marrë lejon të vlerësojmë se sa e ka shpenzuar pyetja jonë për ekzekutimin e hyrjeve dhe daljeve në disk, dmth operacionet me sistemin e skedarëve dhe sheh përdorimin e procesorit. Megjithatë, moduli është i ri (ahem-ahem) dhe për funksionimin e tij kërkon PostgreSQL 9.4 dhe pg_stat_statements, për të cilin kam folur më parë.
Të diturit si të përdorësh statistikat është e dobishme. Nuk keni nevojë për programe të jashtme. Mund të hedhësh vetë një vështrim, të shohësh, të bësh diçka, të ekzekutosh.
Përdorimi i statistikave nuk është i vështirë, është SQL i zakonshëm. Keni grumbulluar një pyetje, e keni përgatitur, e keni dërguar, e keni parë.
Statistikat ndihmojnĂ« pĂ«r tĂ« pĂ«rgjigjur pyetjeve. NĂ«se keni pyetje, ju drejtoheni statistikave â shihni, nxjerrni pĂ«rfundime, analizoni rezultatet.
Dhe eksperimentoni. Ka shumë pyetje, shumë të dhëna. Gjithmonë mund të optimizoni një pyetje ekzistuese. Mund të bëni versionin tuaj të pyetjes, i cili ju përshtatet më shumë se origjinali dhe ta përdorni.
Linket
Lidhje të vlefshme që u përmendën në artikullin, mbi materialet e të cilit, ishin në raport.
Autori shkruan akoma
(eng)
Koleksionisti i Statistikave
Funksionet e Administratës së Sistemit
Modulet Contrib
Mjetet SQL dhe shembujt e kodit sql
Faleminderit të gjithëve për vëmendjen!
Burimi: habr.com
