Shqyrtimi i raportit të vitit 2015 të Alexey Lesovskyy "Deep dive into PostgreSQL internal statistics"
Kufizimi nga autori i raportit: Dua tĂ« theksoj se ky raport daton nga nĂ«ntori 2015 â kanĂ« kaluar mĂ« shumĂ« se 4 vjet dhe ka kaluar shumĂ« kohĂ«. Versioni i diskutuar nĂ« raportin 9.4 tashmĂ« nuk mbĂ«shtetet. GjatĂ« kĂ«tyre 4 viteve kanĂ« dalĂ« 5 lĂ«shime tĂ« reja nĂ« tĂ« cilat janĂ« shfaqur shumĂ« novita, pĂ«rmirĂ«sime dhe ndryshime nĂ« lidhje me statistikĂ«n dhe njĂ« pjesĂ« e materialit Ă«shtĂ« bĂ«rĂ« e tejkaluar dhe e papĂ«rshtatshme. GjatĂ« rishikimit kam pĂ«rpiqur tĂ« shĂ«noj kĂ«to pika pĂ«r tĂ« mos tĂ« futur ty, lexues i dashur, nĂ« keqkuptim. Nuk kam bĂ«rĂ« rishkrim tĂ« kĂ«tyre vendeve sepse janĂ« shumĂ« dhe do tĂ« rezultonte nĂ« njĂ« raport krejt tjetĂ«r.
Sistemi i Menaxhimit të Novacioneve PostgreSQL është një mekanizëm i madh, i cili përbëhet nga shumë njesi të sistemit, nga puna e mirë e të cilave varet direkt performanca e sistemit. Gjatë operimit, mbledhja e statistikave dhe informacionit mbi funksionimin e komponentëve sigurohet, e cila lejon të vlerësohet efektiviteti i PostgreSQL dhe të merren masa për rritjen e performancës. Megjithatë, ka shumë informacion dhe ai është i paraqitur në një formë mjaft të thjeshtë. Trajtimi i këtij informacioni dhe interpretimi i tij shpesh është një detyrë e paparashikueshme, dhe "zoo" e mjeteve dhe utilitarëve lehtësisht mund të konfuzojë edhe një DBA të avancuar.
Mirë dita! Më quaj Alexey. Siç tha Ilya, unë do të flas për statistikat e PostgreSQL.
Statistikat e aktivitetit të PostgreSQL. PostgreSQL ka dy statistika. Statistika e aktivitetit, për të cilën do të flas. Dhe statistika e planifikuesit për shpërndarjen e të dhënave. Unë do të flas pikërisht për statistikat e aktivitetit të PostgreSQL, të cilat na lejojnë të gjykojmë mbi performancën dhe si ta përmirësojmë atë.
Do të flas se si të përdorim me efikasitet statistikat për të zgjidhur probleme të ndryshme që keni ose mund të keni.
ĂfarĂ« nuk do tĂ« jetĂ« nĂ« raport? NĂ« raport nuk do tĂ« flas pĂ«r statistikat e planifikuesit, pasi kjo Ă«shtĂ« njĂ« temĂ« e veçantĂ« pĂ«r njĂ« raport tjetĂ«r mbi atĂ« se si tĂ« dhĂ«nat ruhen nĂ« bazĂ« dhe si planifikuesi i kĂ«rkesave merr njĂ« ide mbi karakteristikat cilĂ«sore dhe sasiore tĂ« kĂ«tyre tĂ« dhĂ«nave.
Dhe nuk do të ketë pasqyrash të mjeteve, nuk do të krahasoj një produkt me tjetrin. Nuk do të ketë as reklama. Ta lëmë këtë pas.
Dua t'ju tregoj se pĂ«rdorimi i statistikave Ă«shtĂ« i dobishĂ«m. ĂshtĂ« e nevojshme. PĂ«rdorimi i saj nuk Ă«shtĂ« frikshĂ«m. Na nevojiten vetĂ«m SQL standard dhe njohuri themelore mbi SQL.
Dhe do të flasim se çfarë statistikash të zgjidhni për probleme.
Nëse e shohim 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 nga emri mund të imagjinojmë se çfarë po bëjnë. Por, në thelb, kjo është një kutisë e zezë, nuk mund të shohim brenda.
Mund të shohim ngarkesën e procesorit në top, mund të shohim përdorimin e memories me disa utilitete sistemike, por nuk do të arrijmë të shohim brenda PostgreSQL. Për këtë na nevojiten mjete të tjera.
Dhe duke vazhduar, do të flas për ku shpenzohet koha. Nëse e imagjinojmë PostgreSQL si një të tillë skemë, do të mund të përgjigjemi ku shpenzohet koha. Këto janë dy gjëra: përpunimi i kërkesave të klientëve nga aplikacionet dhe detyrat pas skenës që PostgreSQL kryen për të ruajtur funksionalitetin e tij.
Nëse fillojmë të shqyrtojmë nga gjëja e majtë e sipërme, mund të ndjekim se si përpunohen kërkesat e klientëve. Kërkesa vjen nga aplikacioni dhe për të vazhduar hapet një sesion klienti. Kërkesa i kalon planifikuesit. Planifikuesi ndan një plan për kërkesën. E dërgon më pas për përmbushje. Ndodhin disa hyrje-dalje bllokesh të dhënash të lidhura me tabelat dhe indekset. Të dhënat e nevojshme lexohen nga disku në memorie në një zonë të veçantë "shared buffers". Rezultatet e kërkesës, nëse janë për azhurnime, fshirje, regjistrohen në logfile-n e transaksioneve në WAL. Informacioni statistik qëndron në log ose te kolektori i statistikave. Dhe rezultati i kërkesës i kthehet tashmë klientit. Pas kësaj, klienti mund ta përsërisë të gjithë procesin nga fillimi me një kërkesë të re.
ĂfarĂ« ndodh me detyrat dhe proceset pas skenĂ«s? Ne kemi disa procese qĂ« sigurojnĂ« funksionalitetin dhe mbajnĂ« bazĂ«n e tĂ« dhĂ«nave nĂ« njĂ« gjendje normale tĂ« punĂ«s. KĂ«to procese gjithashtu do tĂ« preken nĂ« raport: autovacuum, checkpointer, proceset e lidhura me replikimin, background writer. Secilin nga ata do ta diskutoj gjatĂ« raportit.
Cilat janë problemet me statistikat?
- Ka shumë informacione. PostgreSQL 9.4 ofron 109 metrika për të parë të dhënat statistikore. Sidoqoftë, nëse në bazën e të dhënave ruhet shumë tabela, skema, baza, të gjitha këto metrika do të duhet të shumëzohen me numrin përkatës të tabelave, bazave. Kjo do të thotë që informacioni bëhet edhe më i madh. Dhe është shumë e lehtë të humbasësh në të.
- Problemi tjetër është se statistikat janë të paraqitura si numërues. Nëse shohim këto statistika, do të shohim numërues që rriten vazhdimisht. Dhe nëse ka kaluar shumë kohë që nga rivarrosja e statistikave, do të shohim vlera në miliarda. Dhe ato nuk na thonë asgjë.
- Nuk ka histori. Nëse keni pasur ndonjë gabim, diçka ka rënë 15-30 minuta më parë, nuk do të jeni në gjendje të përdorni statistikat dhe të shihni se çfarë ndodhi 15-30 minuta më parë. Kjo është një problem.
- Mungesa e një mjeti të integruar në PostgreSQL është një problem. Zhvilluesit e thelbit nuk ofrojnë asnjë utilitar. Nuk kanë asgjë të tillë. Ata thjesht japin statistikën në bazë. Përdorni, bëni kërkesa, bëni çfarë të doni.
- Duke qenĂ« se nuk ka njĂ« mjet tĂ« integruar nĂ« PostgreSQL, kjo Ă«shtĂ« njĂ« arsye pĂ«r njĂ« problem tjetĂ«r. ShumĂ« mjete tĂ« jashtme. Ădo kompani qĂ« ka pak pĂ«rvojĂ« pĂ«rpiqet tĂ« shkruajĂ« programin e saj. Dhe nĂ« fund nĂ« komunitet ka shumĂ« mjete qĂ« mund tĂ« pĂ«rdoren pĂ«r tĂ« punuar me statistikat. Disa mjete kanĂ« disa mundĂ«si, tĂ« tjera nuk kanĂ« disa mundĂ«si apo kanĂ« disa mundĂ«si tĂ« reja. Dhe ndodh qĂ« duhet tĂ« pĂ«rdorni dy-tre-katĂ«r mjete qĂ« mbivendosen me njĂ«ra-tjetrĂ«n dhe kanĂ« funksione tĂ« ndryshme. Kjo Ă«shtĂ« shumĂ« e pakĂ«ndshme.
ĂfarĂ« duhet tĂ« nxjerrim nga kjo? ĂshtĂ« e rĂ«ndĂ«sishme tĂ« dish tĂ« marrĂ«sh statistikat drejtpĂ«rdrejt, pĂ«r tĂ« mos vare nga programet, ose ndryshe tĂ« pĂ«rmirĂ«sosh kĂ«to programe: tĂ« shtosh disa funksione, pĂ«r tĂ« fituar pĂ«rfitime.
Dhe nevojiten njohuri bazike SQL. Për të marrë disa të dhëna nga statistikat, është e nevojshme të formulohet pyetje SQL, pra duhet të dinë si të përgatiten SELECT, JOIN.
Statistika na ofron disa gjëra. Ato mund të ndahen në kategori.
- Kategoria e parë është ngjarjet që ndodhin në bazë. Kjo është kur ndodhin disa ngjarje në bazë: kërkesa, qasje në tabelë, autovakuum, komitët, ato janë të gjitha ngjarje. Numrat përkatës për këto ngjarje rriten. Dhe ne mund të ndjekim këto ngjarje.
- Kategoria e dytë është pronat e objekteve siç janë tabelat, bazat. Ato kanë pronat e tyre. Kjo është madhësia e tabelave. Mund të ndjekim rritjen e tabelave, rritjen e indekseve. Mund të shohim ndryshimet në dinamikë.
- Dhe kategoria e tretë është koha e shpenzuar për një ngjarje. Kërkesa është një ngjarje. Ajo ka një masë të veçantë të kohëzgjatjes. Këtu ka filluar, këtu ka përfunduar. Ne mund ta ndjekim këtë. Ose koha e leximit të bllokut nga disku ose e shkruarjes. Të tilla gjëra gjithashtu ndoqen.
Burimet e statistikave paraqiten si më poshtë:
- Në memorien e ndarë (shared buffers) ka një segment për vendosjen e të dhënave statistikore, aty janë edhe ato numra që vazhdimisht rriten kur ndodhin disa ngjarje, ose ndodhin disa momente në punën e bazës.
- Të gjitha këto numërues janë të papërshtatshëm për përdoruesit dhe madje edhe për administratorët. Këto janë gjëra në nivel të ulët. Për t'u adresuar, PostgreSQL ofron një ndërfaqe në formën e funksioneve SQL. Ne mund të bëjmë zgjedhje përmes këtyre funksioneve dhe të marrim ndonjë metrikë (ose një grup metrikash).
- Megjithatë, përdorimi i këtyre funksioneve nuk është gjithmonë i përshtatshëm, prandaj funksionet janë baza për pamjet (VIEWs). Këto janë tabela virtuale që ofrojnë statistika për një nën-sistem të caktuar ose për një grup ngjarjesh në bazën e të dhënave.
- Këto pamje të ndërtuara (VIEWs) janë ndërfaqja kryesore e përdoruesit për të punuar me statistikën. Ato janë të disponueshme siç janë, pa ndonjë konfigurim shtesë, mund t'i përdorni menjëherë, të shihni, të merrni informacion nga ato. Po ashtu ka dhe contrib. Contrib janë zyrtare. Ju mund të instaloni pakon postgresql-contrib (p.sh., postgresql94-contrib), të ngarkoni modulin e nevojshëm në konfigurim, të përcaktoni parametrat për të, të ribotoni PostgreSQL dhe mund ta përdorni. (Shënim. Në varësi të distribucionit, në versionet më të reja paketa contrib është pjesë e paketës kryesore.).
- Dhe ka contrib jozyrtare. Ato nuk ofrohen në instalimin standard të PostgreSQL. Duhet të kompilohen ose të instalohen si bibliotekë. Mund të ketë variacione të ndryshme, në varësi të asaj që ka shpikur zhvilluesi i kësaj contrib-je jozyrtare.
Në këtë slide paraqiten të gjitha ato pamje (VIEWs) dhe disa nga ato funksione që janë të disponueshme në PostgreSQL 9.4. Siç shohim, ka shumë prej tyre. Dhe është mjaft e lehtë të ngatërrohemi, nëse përballeni për herë të parë me këtë.
MegjithatĂ«, nĂ«se marrim imazhin e mĂ«parshĂ«m Si harxhohet koha nĂ« PostgreSQL dhe e krahasojmĂ« me kĂ«tĂ« listĂ«, do tĂ« marrim njĂ« pamje tĂ« tillĂ«. Ădo pamje (VIEWs) ose çdo funksion mund ta pĂ«rdorim pĂ«r qĂ«llime tĂ« ndryshme pĂ«r tĂ« marrĂ« statistikĂ« pĂ«rkatĂ«se, kur PostgreSQL Ă«shtĂ« duke punuar. Dhe mund tĂ« marrim tashmĂ« disa informacione mbi funksionimin e nĂ«n-sistemit.
E para që do të shqyrtojmë është pg_stat_database. Siç shohim, kjo është një pamje. Ka shumë informacion. Një informacion shumë i larmishëm. Dhe ai ofron njohuri shumë të dobishme për atë që po ndodh në bazën e të dhënave.
ĂfarĂ« mund tĂ« pĂ«rdorim 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Ă« memorie. PĂ«rqindja e goditjeve nĂ« memorie Ă«shtĂ« njĂ« metrikĂ« e dobishme. Ajo lejon tĂ« vlerĂ«sojmĂ« se sa pĂ«rqind e tĂ« dhĂ«nave merret nga memoria e ndarĂ« dhe sa shkarkohet nga disku.
Natyrisht, sa më shumë goditje që kemi në memorie, aq më mirë. Ne e vlerësojmë këtë metrikë si përqindje. Dhe, për shembull, nëse kemi një përqindje të këtyre goditjeve në memorie më të madhe se 90%, atëherë kjo është mirë. Nëse ai bie nën 90%, atëherë kemi mungesë memorjeje për të mbajtur "kokën" e nxehtë të të dhënave në memorje. Dhe për të përdorur këto të dhëna, PostgreSQL është i detyruar të bëjë thirrje në disk dhe kjo është më e ngadalshme se sa të dhënat që lexohen nga memoria. Duhet tashmë të mendojmë për rritjen e memorjes: ose të rrisim buffer-at e ndarjes, ose të zgjerim kujtesën (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Ă« mund tĂ« marrim gjithashtu nga kjo paraqitje? Mund tĂ« shohim anomali qĂ« ndodhin nĂ« bazĂ«. ĂfarĂ« shikohet kĂ«tu? KĂ«tu ka commits, rollbacks, krijimin e skedarĂ«ve temporarĂ«, volumin e tyre, deadlocks dhe konflikte.
Mund të përfitojmë nga ky kërkesë. Ky SQL është mjaft i thjeshtë. Dhe mund të shohim këto të dhëna për veten tonë.
Dhe kĂ«tu menjĂ«herĂ« janĂ« vlerat pĂ«rcaktuese. Ne shohim raportin midis commits dhe rollbacks. Commits â Ă«shtĂ« miratimi i suksesshĂ«m tĂ« transaksionit. Rollbacks â Ă«shtĂ« kthimi prapa, dmth. transaksioni bĂ«nte ndonjĂ« punĂ«, ngarkonte bazĂ«n, ndonjĂ«herĂ« llogariste, dhe pastaj ndodhi njĂ« dĂ«shtim, dhe rezultatet e transaksionit hidhen poshtĂ«. Dmth. numri i rollbacks, qĂ« rritet vazhdimisht, Ă«shtĂ« e keqe. Dhe duhet ndonjĂ«herĂ« tĂ« shmangen dhe tĂ« rregullohen kodet qĂ« tĂ« mos ndodhin kĂ«shtu.
Konfliktet (conflicts) janë të lidhura me replikimin. Dhe ato gjithashtu duhet të shmangen. Nëse keni ndonjë kërkesë që ekzekutohet në replikë dhe ndodhin konflikte, atëherë duhet të shqyrtoni këto konflikte, të shihni çfarë ndodh. Detajet mund të gjenden në log-et. Dhe të eliminohet situatat konfliktuale, që kërkesat e aplikacionit të punojnë pa gabime.
Deadlocks â kjo Ă«shtĂ« gjithashtu njĂ« situatĂ« e keqe. Kur kĂ«rkesat luftojnĂ« pĂ«r burimet, njĂ« kĂ«rkesĂ« i Ă«shtĂ« drejtuar njĂ« burimi dhe ka marrĂ« bllokim, kĂ«rkesa e dytĂ« i Ă«shtĂ« drejtuar njĂ« burimi tĂ« dytĂ« dhe gjithashtu ka marrĂ« bllokim, dhe pastaj tĂ« dyja kĂ«rkesat i janĂ« drejtuar burimeve tĂ« njĂ«ra-tjetrĂ«s dhe janĂ« bllokuar nĂ« pritje qĂ« fqinja tĂ« lirojĂ« bllokimin. Kjo Ă«shtĂ« gjithashtu njĂ« situatĂ« problematike. Ato duhet tĂ« zgjidhen nĂ« nivelin e rishkrimit tĂ« aplikacioneve dhe serializimit tĂ« aksesit nĂ« burime. Dhe nĂ«se shihni qĂ« deadlocks rriten vazhdimisht, duhet tĂ« shihni detajet nĂ« log-e, tĂ« shqyrtoni situatat nĂ« ngritje dhe tĂ« shihni nĂ« çfarĂ« problemi Ă«shtĂ«.
SkedarĂ«t temporarĂ« (temp_files) â kjo Ă«shtĂ« gjithashtu e keqe. Kur kĂ«rkesĂ«s sĂ« pĂ«rdoruesit i mungon memoria pĂ«r tĂ« ruajtur tĂ« dhĂ«nat operativĂ«, ajo krijon njĂ« skedar nĂ« disk. Dhe tĂ« gjitha operacionet qĂ« do tĂ« kishte mundur tĂ« kryente nĂ« buffer-in temporar nĂ« memoria, fillon t'i kryejĂ« tashmĂ« nĂ« disk. Kjo Ă«shtĂ« e ngadaltĂ«. Kjo rrit kohĂ«n e ekzekutimit tĂ« kĂ«rkesĂ«s. Dhe klienti, qĂ« ka dĂ«rguar kĂ«rkesĂ«n nĂ« PostgreSQL do tĂ« marrĂ« pĂ«rgjigje pak mĂ« vonĂ«. NĂ«se kĂ«to operacione tĂ« gjitha do tĂ« kryheshin nĂ« memoria, Postgres do tĂ« pĂ«rgjigjej shumĂ« mĂ« shpejt dhe klienti do tĂ« priste mĂ« pak.
Pg_stat_bgwriter â kjo paraqitje pĂ«rshkruan punĂ«n e dy sistemeve tĂ« prapambetjes nĂ« PostgreSQL: kĂ«to janĂ« checkpointer dhe background writer.
Le tĂ« fillojmĂ« me pikĂ«t e kontrollit, tĂ« njohura si checkpoint. ĂfarĂ« janĂ« pikĂ«t e kontrollit? NjĂ« pikĂ« kontrolli Ă«shtĂ« njĂ« pozicion nĂ« ditarin e transaksioneve qĂ« informon se tĂ« gjitha ndryshimet e dhĂ«nave qĂ« janĂ« regjistruar nĂ« ditar janĂ« sinkronizuar me sukses me tĂ« dhĂ«nat nĂ« disk. Procesi, nĂ« varĂ«si tĂ« ngarkesĂ«s dhe konfigurimeve, mund tĂ« jetĂ« i gjatĂ« dhe pĂ«rfshin kryesisht sinkronizimin e faqeve tĂ« pista nĂ« memorien e ndarĂ« me datat e skedarĂ«ve nĂ« disk. Pse Ă«shtĂ« e nevojshme? NĂ«se PostgreSQL do tĂ« ishte gjithmonĂ« duke iu drejtuar diskut pĂ«r tĂ« marrĂ« tĂ« dhĂ«na dhe pĂ«r tĂ« regjistruar tĂ« dhĂ«na me çdo kĂ«rkesĂ«, do tĂ« ishte ngadalĂ«. Prandaj, PostgreSQL ka njĂ« segment memorije, madhĂ«sia e tĂ« cilit varet nga parametrat nĂ« konfigurim. Postgres vendos nĂ« kĂ«tĂ« memorie tĂ« dhĂ«nat operative pĂ«r pĂ«rpunim tĂ« mĂ«vonshĂ«m ose pĂ«r t'u dhĂ«nĂ« pĂ«rgjigje kĂ«rkesave. NĂ« rast tĂ« kĂ«rkesave pĂ«r ndryshimin e tĂ« dhĂ«nave, ndodhin ndryshime. Dhe ne marrim dy versione tĂ« tĂ« dhĂ«nave. NjĂ«ra Ă«shtĂ« nĂ« memorie, tjetra nĂ« disk. Dhe herĂ« pas here na nevojitet tĂ« sinkronizojmĂ« kĂ«to tĂ« dhĂ«na. Na duhet ajo qĂ« Ă«shtĂ« ndryshuar nĂ« memorie, tĂ« sinkronizohet nĂ« disk. PĂ«r kĂ«tĂ« janĂ« tĂ« nevojshme pikĂ«t e kontrollit.
Pika e kontrollit kalon nëpër memorien e ndarë, shënon faqet e pista se ato janë të nevojshme për pikën e kontrollit. Më pas fillon një kalim të dytë nëpër memorien e ndarë. Dhe faqet, që janë shënuar për pikën e kontrollit, ai tashmë i sinkronizon. Kështu realizohet sinkronizimi i të dhënave me diskun.
EkzistojnĂ« dy tipa pikash kontrolli. NjĂ« pikĂ« kontrolli ekzekutohet sipas njĂ« kohĂ«zgjatjeje. Kjo Ă«shtĂ« njĂ« pikĂ« kontrolli e dobishme dhe e mirĂ« â checkpoint_timed. Dhe ka pika kontrolli sipas kĂ«rkesĂ«s â checkpoint required. NjĂ« pikĂ« kontrolli e tillĂ« ndodh kur kemi njĂ« shkarkim tĂ« madh tĂ« tĂ« dhĂ«nave. Ne kemi regjistruar shumĂ« ditarĂ« transaksionesh. Dhe PostgreSQL mendon se duhet t'i sinkronizojĂ« sa mĂ« shpejt tĂ« jetĂ« e mundur, tĂ« bĂ«jĂ« njĂ« pikĂ« kontrolli dhe tĂ« vazhdojĂ« pĂ«rpara.
Dhe nëse shikoni statistikat pg_stat_bgwriter dhe shihni se keni checkpoint_req është shumë më i lartë se checkpoint_timed, atëherë kjo është e keqe. Pse është e keqe? Kjo do të thotë se PostgreSQL është në një situatë të vazhdueshme stresi, kur duhet të regjistrojë të dhëna në disk. Pika e kontrollit sipas kohës është më pak stresuese dhe kryhet sipas një orari të brendshëm dhe siç duket është e shtrirë në kohë. PostgreSQL ka mundësinë të bëjë pushime në punë dhe të mos ngarkojë sistemin e diskut. Kjo është e dobishme për PostgreSQL. Dhe kërkesat që ekzekutohen gjatë pikës së kontrollit nuk do të përjetojnë stresin nga ngarkesa e sistemit të diskut.
Dhe për të rregulluar pikën e kontrollit ka tre parametra:
checkpoint_segments.checkpoint_timeout.checkpoint_competion_target.
KĂ«ta lejojnĂ« rregullimin e funksionimit tĂ« pikave tĂ« kontrollit. Por nuk do tĂ« ndalem shumĂ« te kĂ«ta. Ndikimi i tyre â Ă«shtĂ« njĂ« temĂ« e veçantĂ«.
Kujdes: Versioni i shqyrtuar në raport, 9.4, tashmë është i papërdorur. Në versionet moderne të PostgreSQL, parametri checkpoint_segments është zëvendësuar me parametrat min_wal_size dhe max_wal_size.
NĂ«n-sistemi i ardhshĂ«m Ă«shtĂ« shkruesi i sfondit â background writer. ĂfarĂ« bĂ«n ai? Ai punon vazhdimisht nĂ« njĂ« cikĂ«l tĂ« pafund. Skanon faqet nĂ« buffers tĂ« pĂ«rbashkĂ«t dhe faqet e ndotura qĂ« ka gjetur i shkarkon nĂ« disk. NĂ« kĂ«tĂ« mĂ«nyrĂ« ai ndihmon checkpointer-in tĂ« bĂ«jĂ« mĂ« pak punĂ« gjatĂ« procesit tĂ« 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ë përbashkët nëse ato papritur nevojiten (në numër të madh dhe menjëherë) për vendosjen e të dhënave. Supozoni se ndodh një situatë kur për të ekzekutuar një kërkesë janë të nevojshme faqe të pastra dhe ato janë tashmë në buffers të përbashkët. PostgreSQL backend thjesht i merr ato dhe i përdor, nuk ka nevojë të pastrojë asgjë vetë. Por nëse ndodhin që të mos ketë faqe të tilla, backend ndalon punën dhe fillon kërkimin e faqeve për t'i shkarkuar në disk dhe t'i marrë për nevojat e tij - kjo ndikon negativisht në kohën e ekzekutimit aktual të kërkesës. Nëse shihni se parametri juaj maxwritten_clean është i madh, kjo do të thotë se background writer nuk po përballon punën e tij dhe duhet të rriten parametrat bgwriter_lru_maxpages, që ai të mund të bëjë më shumë punë në një cikël dhe të pastrojë më shumë faqe.
Dhe një tregues tjetër shumë të dobishëm është buffers_backend_fsync. Backend-et nuk bëjnë fsync, sepse kjo është e ngadalshme. Ata e transferojnë fsync lart në stack-un IO tek checkpointer-i. Checkpointer-i ka radhën e tij, ai përherë fsync e përpunon dhe sinkronizon faqet në memorie me skedarët në disk. Nëse radhë e checkpointer-it është e madhe 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ë tashmë një problem dhe duhet të kushtoni vëmendje parametrave të background writer-it dhe gjithashtu të vlerësoni performancën e sistemit disk.
Kujdes: _Teksti vijues përshkruan pamjet statistikore të lidhura me replikimin. Pjesa e madhe e emrave të pamjeve dhe funksioneve u rinovua në Postgres 10. Thelbi i rinovimeve ishte zëvendësimi xlog në wal dhe lokacionin në lsn në emrat e funksioneve/pamjeve etj. Një shembull specifik, funksioni pg_xlog_location_diff() u rinovua në pg_wal_lsn_diff()._
Këtu kemi gjithashtu shumë gjëra. Por do na nevojiten vetëm pikat që lidhen me pozitat.
Nëse shohim se të gjitha vlerat janë të barabarta, atëherë kjo është varianti ideal dhe replikimi nuk po nëndërprehet nga poçka.
Kjo pozitë hexadecimale është pozita në regjistrin e transaksioneve. Ajo rritet vazhdimisht, nëse në bazë ka ndonjë aktivitet: shtesa, fshirje etj.
sa është regjistruar xlog në byte
$ select
pg_xlog_location_diff(pg_current_xlog_location(),'0/00000000');
vonesa e replikimit në byte
$ select
client_addr,
pg_xlog_location_diff(pg_current_xlog_location(), replay_location)
from pg_stat_replication;
vonesa e 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ë diferenca mes replikës dhe serverit kryesor, që do të thotë se të dhënat ndryshojnë midis serverëve.
Ka tre arsye për vonesën:
- Sistemi disk po e ketë të vështirë të regjistrojë sinkronizimin e skedarëve.
- Këto mund të jenë gabime në rrjet, ose ngarkesë e tepërt në rrjet, kur të dhënat nuk arrijnë në replikë dhe ai nuk mund t'i riprodhojë ato.
- 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 pyetje, që na lejojnë të përdorim statistikat. Mund të vlerësojmë se sa është regjistruar në regjistrin tonë të transaksioneve. Ka një funksion të tillë pg_xlog_location_diff dhe mund të vlerësojmë vonesën e replikimit në byte dhe sekonda. Po ashtu e përdorim vlerën nga kjo pamje (VIEWs).
ShĂ«nim: _PĂ«rveç pg_xlog_locationfunksionit diff() mund tĂ« pĂ«rdorim operatorin e zbritjes dhe tĂ« zbritim njĂ« lokacion nga tjetri. ĂshtĂ« e pĂ«rshtatshme.
Me vonesën, që shprehet në sekonda, ka një moment. Nëse në serverin kryesor nuk ndodh asnjë aktivitet, transaksioni ka ndodhur rreth 15 minuta më parë dhe nuk ka aktivitet, dhe nëse ne e shohim këtë vonesë në replikë, do të shohim një vonesë prej 15 minutash. Kjo duhet mbajtur mend. Dhe kjo mund të japi konfuzion, kur e shikoni këtë vonesë.
Pg_stat_all_tables â Ă«shtĂ« njĂ« pamje tjetĂ«r e dobishme. Ajo tregon statistikat pĂ«r tabelat. Kur kemi tabela nĂ« bazĂ«n tonĂ« dhe ato kanĂ« ndonjĂ« aktivitet, ndonjĂ« veprim, 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 i vazhdueshëm i tabelës. Numri vetë pas këtyre kalimeve nuk është domosdoshmërisht i keq dhe nuk është tregues se duhet të veprojmë.
MegjithatĂ«, ekziston njĂ« metrikĂ« tjetĂ«r â seq_tup_read. Kjo Ă«shtĂ« numri i rreshtave qĂ« kthehen si rezultat i skanimit tĂ« vazhdueshĂ«m. NĂ«se numri mesatar tejkalon 1,000, 10,000, 50,000, 100,000, atĂ«herĂ« kjo Ă«shtĂ« njĂ« tregues se ndoshta duhet tĂ« ndĂ«rtojmĂ« ndonjĂ« indeks, pĂ«r tĂ« realizuar kĂ«rkesat pĂ«rmes indekseve, ose ndoshta tĂ« optimizojmĂ« kĂ«rkesat qĂ« pĂ«rdorin kĂ«to skanimet e vazhdueshme, pĂ«r tĂ« mos pasur tĂ« tilla.
NjĂ« shembull i thjeshtĂ« â le tĂ« themi se njĂ« kĂ«rkesĂ« me OFFSET dhe LIMIT tĂ« madh Ă«shtĂ« nĂ« kĂ«mbim. PĂ«r shembull, skanohet 100,000 rreshta nĂ« tabelĂ« dhe pas kĂ«saj merren 50,000 rreshta tĂ« nevojshĂ«m, ndĂ«rsa rreshtat e skanuar mĂ« parĂ« hidhen poshtĂ«. Kjo Ă«shtĂ« gjithashtu njĂ« rast i keq. Dhe kĂ«to kĂ«rkesa duhet tĂ« optimizohen. Dhe kĂ«tu Ă«shtĂ« njĂ« pyetje SQL e thjeshtĂ«, nĂ« tĂ« cilĂ«n mund ta shikoni dhe tĂ« vlerĂ«soni numrat e marrĂ«.
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;Mënyrat për të marrë dimensionet e tabelave mund të merrni gjithashtu me këtë tabelë dhe me funksione shtesë. pg_total_relation_size(), pg_relation_size().
Në të vërtetë, ekzistojnë komandat meta dt dhe di, të cilat mund të përdoren në PSQL dhe gjithashtu të shohin dimensionet e tabelave dhe indekseve.
Megjithatë, përdorimi i funksioneve na ndihmon të shohim dimensionet e tabelave me llogaritjen e indekseve, ose pa llogaritjen e indekseve dhe tashmë të bëjmë ndonjë vlerësim mbi rritjen e bazës së të dhënave, dmth. si po rritet, me sa intensitet dhe të bëjmë disa përfundime mbi optimizimin e dimensioneve.
Aktiviteti i shkrimit. ĂfarĂ« Ă«shtĂ« shkrimi? Le tĂ« shqyrtojmĂ« operacionin UPDATE â operacionin e pĂ«rditĂ«simit tĂ« rreshtave nĂ« tabelĂ«. NĂ« thelb, update Ă«shtĂ« dy operacione (ose ndoshta edhe mĂ« shumĂ«). Kjo Ă«shtĂ« njĂ« pĂ«rfshirje e versionit tĂ« ri tĂ« rreshtit dhe shenimi i versionit tĂ« vjetĂ«r tĂ« rreshtit si tĂ« vjetruar. MĂ« vonĂ« do tĂ« vijĂ« avakuumi dhe kĂ«to versione tĂ« vjetra do tĂ« spastrohen, 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. ĂshtĂ« gjithashtu pĂ«rditĂ«simi i indekseve. NĂ«se keni shumĂ« indekse nĂ« tabelĂ«, atĂ«herĂ« gjatĂ« update tĂ« gjitha indekset qĂ« pĂ«rfshijnĂ« fushat e pĂ«rditĂ«suara nĂ« kĂ«rkesĂ« do tĂ« duhet gjithashtu tĂ« pĂ«rditĂ«sohen. NĂ« kĂ«to indekse do tĂ« ketĂ« gjithashtu versione tĂ« vjetra tĂ« rreshtave qĂ« duhet tĂ« spastrohen.
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. Ekzistojnë update të nxehta. Ato u shfaqën në versionin 8.3 të PostgreSQL. Dhe çfarë janë ato? Këto janë përditësime të lehta, që nuk shkaktojnë rindërtimin e indekseve. Kjo do të thotë se ne e përditësuam një shkrim, por gjithsesi u përditësua vetëm shkrimi në faqe (e cila i përket tabelës), ndërsa indekset vazhdojnë të tregojnë për të njëjtin shkrim në faqe. Aty ka një logjikë interesante të funksionimit, kur vjen avakuumi, ai rinovon këto lidhje të nxehta dhe gjithçka vazhdon të funksionojë pa përditësimin e indekseve, dhe gjithçka ndodh me një shpenzim më të vogël të burimeve.
Dhe kur keni n_tup_hot_upd të mëdha, kjo është shumë mirë. Kjo do të thotë se përditësimet e lehta dominojnë dhe kjo na del më lirë në burime dhe gjithçka është në rregull.
ALTER TABLE table_name SET (fillfactor = 70);Si të rrisim sasinë e përditësimeve të nxehta?Mund të përdorim fillfactor. Ai definon madhësinë e hapësirës rezervuese kur plotësohet faqja në tabelë me insert-e. Kur në tabelë shkojnë insert-et, ato mbushin plotësisht faqen, pa lënë hapësirë të zbrazët. Pastaj një faqe e re caktohet. Të dhënat mbushen përsëri. Dhe ky është sjellja e paracaktuar, fill faktor = 100 %.
Ne mund të bëjmë fill faktor në 70 %. Pra, kur bëhen insert-e, caktohet një faqe e re, por mbushet vetëm 70 % e faqes. Dhe 30 % na mbetet për rezervë. Kur nevojitet të bëhet një përditësim, ka shumë gjasa që ai të ndodhi në të njëjtën faqe, dhe versioni i ri i rreshtit do të vendoset në të njëjtën faqe. Dhe do të bëhet një hot_update. Kështu lehtësohet shkrimi 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));Radhit e avtokombinimit. Avtokombinimi â Ă«shtĂ« njĂ« nĂ«nsiatĂ«, mbi tĂ« cilĂ«n statistika nĂ« PostgreSQL Ă«shtĂ« shumĂ« e dobĂ«t. Ne mund tĂ« shohim nĂ« tabela vetĂ«m nĂ« pg_stat_activity sa vakume po ndodhin nĂ« momentin aktual. MegjithatĂ«, tĂ« kuptojmĂ« se sa tabela janĂ« nĂ« radhĂ« Ă«shtĂ« shumĂ« e vĂ«shtirĂ«.
ShĂ«nim: _QĂ« nga versioni Postgres 10, situata me ndjekjen e avtokombinimit Ă«shtĂ« pĂ«rmirĂ«suar ndjeshĂ«m â Ă«shtĂ« shfaqur pamja pg_stat_progressvakum, e cila e thjeshton ndjeshĂ«m çështjen e monitorimit tĂ« avtokombinimit.
Ne mund të përdorim një kërkesë të tillë të thjeshtuar. Dhe mund të shohim kur do të bëhet vakumi. Por, si dhe kur duhet të fillojë vakumi? Këto versione të tejkaluara të rreshtave, për të cilat flas më parë. Ndodhi një përditësim, një version i ri i rreshtit u ngjit. 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 sapo numri i rreshtave të vdekur bëhet më i madh se një prag i caktuar, avtokombinimi do të vijë te tabela.
Dhe si llogaritet ky prag? Ky është një raport konkret përqindjeje nga numri total i rreshtave në tabelë. Ka një parametër autovacuum_vacuum_scale_factor. Ai përcakton raportin përqindor. Le të themi, 10 % + një prag të bazës me 50 rreshta. Dhe çfarë rezulton? Kur kemi rreshta të vdekur më shumë se "10 % + 50" nga të gjitha rreshtat në tabelë, ne e vendosim tabelën në avtokombinimin.
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ë për parametrat av_base_thresh dhe av_scale_factor mund të caktohen individualisht. Dhe, për rrjedhojë, pragjet do të jenë jo globale, por individuale për tabelën. Pra, për të llogaritur, duhet të përdoren mashtrime dhe truqe. Dhe nëse ju intereson, ju mund të shihni përvojën e kolegëve tanë nga Avito (linku në sliden është i pavlefshëm dhe është përditësuar në tekst).
Ata shkruan për , i cili merr parasysh këto gjëra. Atje ka një dokument në dy faqe. Por ai llogarit saktë dhe në mënyrë të efektshme na lejon të vlerësojmë ku na nevojitet më shumë vakuum për tabelat, ku më pak.
ĂfarĂ« mund tĂ« bĂ«jmĂ« me kĂ«tĂ«? NĂ«se kemi njĂ« radhĂ« tĂ« madhe dhe avakovaku nuk po pĂ«rballon, atĂ«herĂ« mund tĂ« rrisim numrin e punĂ«torĂ«ve tĂ« vakuumit, ose thjesht ta bĂ«jmĂ« vakuumin mĂ« agresiv, qĂ« ai tĂ« aktivizohet mĂ« herĂ«t dhe tĂ« pĂ«rpunojĂ« tabelĂ«n nĂ« copĂ«za tĂ« vogla. Dhe kĂ«shtu, radha do tĂ« zvogĂ«lohet. â E rĂ«ndĂ«sishme kĂ«tu Ă«shtĂ« tĂ« monitorojmĂ« ngarkesĂ«n nĂ« diske, sepse vakuumi Ă«shtĂ« diçka qĂ« nuk Ă«shtĂ« falas, megjithatĂ« me shfaqjen e pajisjeve SSD/NVMe problemi Ă«shtĂ« bĂ«rĂ« mĂ« pak i dukshĂ«m.
Pg_stat_all_indexes â Ă«shtĂ« statistika pĂ«r indekset. Ajo Ă«shtĂ« e vogĂ«l. Dhe ne mund tĂ« marrim informacion pĂ«r pĂ«rdorimin e indekseve nga ajo. Dhe pĂ«r shembull, mund tĂ« pĂ«rcaktojmĂ« se cilat indekse janĂ« tĂ« tepĂ«rta.
Siç e thashĂ« mĂ« parĂ«, update â nuk Ă«shtĂ« vetĂ«m pĂ«rditĂ«simi i tabelave, Ă«shtĂ« gjithashtu pĂ«rditĂ«simi i indekseve. Prandaj, nĂ«se kemi shumĂ« indekse mbi njĂ« tabelĂ«, atĂ«herĂ« gjatĂ« pĂ«rditĂ«simit tĂ« rreshtave nĂ« tabelĂ«, indekset e fushave tĂ« indeksuara gjithashtu duhet tĂ« pĂ«rditĂ«sohen, dhe nĂ«se kemi indekse tĂ« papĂ«rdorura, pĂ«r tĂ« cilat nuk ka skanime indeksesh, ato janĂ« si njĂ« barrĂ« e panevojshme. Dhe duhet tĂ« heqim dorĂ« nga to. PĂ«r kĂ«tĂ« na nevojitet fusha idx_scan. Ne thjesht shikojmĂ« numrin e skanimeve indeskĂ«. NĂ«se indekset nuk kanĂ« skanime pĂ«r njĂ« periudhĂ« mjaft tĂ« gjatĂ« ruajtjeje tĂ« statistikave (jo mĂ« pak se 2-3 javĂ«), atĂ«herĂ« nĂ« shumicĂ«n e rasteve janĂ« indekse tĂ« kĂ«qija, duhet t'i heqim ato.
Shënim: Kur kërkojmë indekse të papërdorura në rastin e klasterëve të replikimit në rrjedhë, duhet të kontrollojmë të gjitha nyjat e klasterit, sepse statistikat nuk janë globale, dhe nëse një indeks nuk përdoret në master, ai mund të përdoret në replika (nëse atje ka ngarkesë).
Dy lidhje:
Këto janë shembuj më të avancuar të pyetjeve për të kërkuar indekse të papërdorura.
Lidhja e dytĂ« â Ă«shtĂ« njĂ« pyetje mjaft interesante. Aty ka logjikĂ« shumĂ« jo-triviale. E rekomandoj pĂ«r njohje.
ĂfarĂ« tjetĂ«r duhet tĂ« pĂ«rmbledhim pĂ«r indekset?
Indekset e papĂ«rdorura â janĂ« tĂ« kĂ«qija.
Zgjatin hapësirën.
Ngadalësojnë operacionet e përditësimit.
Punë e tepërt për vakuumin.
Nëse heqim indekset e papërdorura, do ta bëjmë bazën më të mirë.
Prezantimi tjetĂ«r Ă«shtĂ« pg_stat_activity. ĂshtĂ« analogu i utilitarit ps, vetĂ«m nĂ« PostgreSQL. NĂ«se ps'e shihni proceset nĂ« sistemin operativ, atĂ«herĂ« pg_stat_activity do t'ju tregojĂ« aktivitetin brenda PostgreSQL.
ĂfarĂ« mund tĂ« marrim nga aty si tĂ« dobishĂ«m?
select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;Ne mund të shohim aktivitetin e përgjithshëm, çfarë po ndodh në bazë. Mund të bëjmë një publikim të ri. Kemi një shpërthim, lidhjet e reja nuk pranohen, ka gabime të shumta në aplikacion.
select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;Ne mund të realizojmë një kërkesë të tillë dhe të shohim përqindjen totale të lidhjeve në raport me limitin maksimal të lidhjeve dhe të shohim se kush ka më shumë lidhje. Në këtë rast të paraqitur, ne shohim që përdoruesi cron_role ka hapur 508 lidhje. Diku ka ndodhur diçka. Duhet të merremi me të dhe të shohim. Dhe është plotësisht e mundur që ky të jetë një numër anormal lidhjesh.
Nëse kemi ngarkesë OLTP, kërkesat duhet të kryhen shpejt, shumë shpejt dhe nuk duhet të kenë kërkesa të gjata. Megjithatë, nëse ndodhin kërkesa të gjata, atëherë në perspektivën afatshkurtër nuk është asgjë e keqe, por në perspektivën afatgjatë, kërkesat e gjata dëmtojnë bazën, ato rrisin efektin bloat të tabelave, kur ndodh fragmentimi i tabelave. Duhet të heqim dorë nga bloati dhe 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ë përcaktojmë kërkesat dhe transaksionet e gjata. Ne përdorim funksionin clock_timestamp() për të përcaktuar kohën e funksionimit. Kërkesat e gjata që kemi gjetur, mund t'i mbajmë në mend, t'i kryejmë explain, të shohim planet dhe ndonjëherë të optimizojmë. Kërkesat e gjata aktuale i mbyllim 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. Dhe njĂ« prej kĂ«tyre gjendjeve mund tĂ« marrin nĂ« çdo moment. PĂ«r tĂ« pĂ«rcaktuar gjendjet, ka njĂ« fushĂ« gjendja nĂ« kĂ«tĂ« pamje. Dhe ne e pĂ«rdorim 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Ă« kĂ«qija. ĂfarĂ« janĂ«? Kjo ndodh kur aplikacioni ka hapur njĂ« transaksion, ka bĂ«rĂ« disa veprime dhe Ă«shtĂ« larguar. Transaksioni mbetet i hapur. Ai var, nuk ndodh asgjĂ« nĂ« tĂ«, zĂ« njĂ« lidhje, ndalon rreshtat e ndryshuar dhe potencialisht rrit bloat-in e tabelave tĂ« tjera pĂ«r shkak tĂ« arkitekturĂ«s sĂ« motorit tĂ« transaksioneve PostgreSQL. Dhe kĂ«to transaksione gjithashtu duhen mbyllur, sepse janĂ« tĂ« dĂ«mshme nĂ« çdo rast.
Nëse shihni se ka më shumë se 5-10-20 të tillë në bazën tuaj, duhet të shqetësoheni dhe të filloni të bëni diçka me to.
Këtu ne gjithashtu përdorim për kohën llogaritëse clock_timestamp(). Transaksionet i mbyllim, aplikacionin e optimizojmë.
Siç e thashë më sipër, bllokimet janë kur dy ose më shumë transaksione garojnë për një ose grup burimesh. Për këtë, kemi një fushë waiting me vlerë boolean e vërtetë ose false.
True â kjo do tĂ« thotĂ« se procesi Ă«shtĂ« nĂ« pritje, dhe duhet bĂ«rĂ« diçka. Kur procesi Ă«shtĂ« nĂ« pritje, klienti qĂ« e iniciativ njĂ« proces tĂ« tillĂ« po ashtu Ă«shtĂ« nĂ« pritje. Klienti nĂ« shfletues Ă«shtĂ« ulur dhe gjithashtu po pret.
Kujdes: _Duke filluar nga versioni Postgres 9.6, fusha waiting është fshirë dhe në vend të saj janë shtuar dy fushat më informuese wait_event_type dhe wait_event._
ĂfarĂ« tĂ« bĂ«jmĂ«? NĂ«se shihni true pĂ«r njĂ« kohĂ« tĂ« gjatĂ«, atĂ«herĂ« ky Ă«shtĂ« njĂ« tregues qĂ« duhet tĂ« shmangen kĂ«rkesa tĂ« tilla. Ne thjesht i eliminojmĂ« kĂ«to transaksione. U shkruajmĂ« zhvilluesve se duhet optimizuar ndryshe pĂ«r tĂ« evituar garĂ«n pĂ«r burime. Pastaj zhvilluesit optimizojnĂ« aplikacionin nĂ« mĂ«nyrĂ« qĂ« mos tĂ« ndodhin tĂ« tilla situata.
Dhe rasti i fundit, por potencialisht jo fatal â Ă«shtĂ« shfaqja e deadlocks. Dy transaksione pĂ«rditĂ«sojnĂ« dy burime, pastaj kĂ«rkojnĂ« sĂ«rish, tashmĂ« pĂ«r burimet e kundĂ«rta. NĂ« kĂ«tĂ« rast, PostgreSQL e eliminon vetĂ« transaksionin, pĂ«r t'i dhĂ«nĂ« mundĂ«sinĂ« tjetrit tĂ« vazhdojĂ« punĂ«n. Kjo Ă«shtĂ« njĂ« situatĂ« e bllokuar dhe nuk zgjidhet vetĂ«. Prandaj PostgreSQL detyrohet tĂ« marrĂ« masa ekstreme.
Dhe këtu janë dy kërkesa që lejojnë ndjekjen e bllokimeve. Ne përdorim pamjen pg_locks, e cila lejon të ndjekim bllokimet e rënda.
Dhe lidhja e parĂ« â Ă«shtĂ« vetĂ« teksti i kĂ«rkesĂ«s. Ai Ă«shtĂ« mjaft i gjatĂ«.
Dhe lidhja e dytĂ« â Ă«shtĂ« njĂ« artikull mbi locks. ĂshtĂ« e dobishme ta lexoni, Ă«shtĂ« shumĂ« interesante.
Pra, çfarĂ« shohim? Ne shohim dy kĂ«rkesa. Transaksioni me ALTER TABLE â Ă«shtĂ« njĂ« transaksion qĂ« bllokon. Ai Ă«shtĂ« nisur, por nuk Ă«shtĂ« pĂ«rfunduar dhe aplikacioni qĂ« e ka nisur kĂ«tĂ« transaksion Ă«shtĂ« duke u marrĂ« me çështje tĂ« tjera. Dhe kĂ«rkesa e dytĂ« â update. Ai po pret qĂ« alter table tĂ« pĂ«rfundojĂ«, qĂ« tĂ« vazhdojĂ« punĂ«n e tij.
Kështu mund të zbulojmë se kush ka bllokuar, e mban dhe mund të merremi me këtë më tutje.
Moduli tjetĂ«r â Ă«shtĂ« pg_stat_statements. Ashtu siç e thashĂ«, Ă«shtĂ« njĂ« modul. PĂ«r ta shfrytĂ«zuar, duhet ngarkuar bibliotekĂ«n e tij nĂ« konfigurim, ri-nisur PostgreSQL, instaluar modulin (me njĂ« komandĂ«) dhe pastaj do tĂ« na shfaqet njĂ« pamje e re.
Koha mesatare e kërkesës në milisekonda
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;
Kërkesat më aktive të shkruhura (në shared_buffers)
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;ĂfarĂ« mund tĂ« nxjerrim nga aty? NĂ«se flasim pĂ«r gjĂ«ra tĂ« thjeshta, mund tĂ« marrim kohĂ«n mesatare tĂ« ekzekutimit tĂ« kĂ«rkesĂ«s. Koha po rritet, do tĂ« thotĂ« se PostgreSQL po pĂ«rgjigjet ngadalĂ« dhe duhet bĂ«rĂ« diçka.
Mund të shohim transaksionet më aktive të shkruara në bazën e të dhënave, të cilat ndryshojnë të dhënat në shared buffers. Të shohim kush po përditëson ose fshin të dhëna.
Dhe mund të shohim thjesht statistikë të ndryshme për këto kërkesa.
Ne pg_stat_statements përdorim për ndërtimin e raporteve. Cdo ditë ne zbresim statistikën. E akumulojmë atë. Para se të zbresim statistikën herën tjetër, ndërtuam raportin. Këtu është lidhja për raportin. Mund ta shikoni atë.
ĂfarĂ« bĂ«jmĂ« ne? Ne llogarisim statistikĂ«n totale pĂ«r tĂ« gjitha kĂ«rkesat. MĂ« pas pĂ«r çdo kĂ«rkesĂ« llogarisim kontributin e saj individual nĂ« kĂ«tĂ« statistikĂ« totale.
Dhe çfarë mund të shohim? Mund të shohim kohën e përgjithshme të ekzekutimit të të gjitha kërkesave të një lloji të caktuar në sfondin e të gjitha kërkesave të tjera. Mund të shohim përdorimin e burimeve të procesorit dhe hyrje-daljeve në lidhje me pamjen e përgjithshme. Dhe tashmë mund të optimizojmë këto kërkesa. Ne ndërtuam një top të kërkesave sipas këtij raporti dhe tashmë kemi bazë për të menduar se çfarë të optimizojmë.
ĂfarĂ« na ka mbetur jashtĂ«? Ka disa prezantime qĂ« nuk i kam shqyrtuar, pasi koha Ă«shtĂ« e kufizuar.
Ka pgstattuple â Ă«shtĂ« gjithashtu njĂ« modul shtesĂ« nga paketa standarde contribs. Ai lejon tĂ« vlerĂ«sojmĂ« bloat tabelat, pra fragmentimin e tabelĂ«s. Dhe nĂ«se fragmentimi Ă«shtĂ« i madh, duhet ta heqim, tĂ« pĂ«rdorim mjete tĂ« ndryshme. Dhe funksioni pgstattuple punon ngadalĂ«. Dhe sa mĂ« shumĂ« tabela tĂ« ketĂ«, aq mĂ« gjatĂ« do tĂ« punojĂ«.
Moduli tjetër contrib është pg_buffercache. Ai lejon të inspektojë bufferat e ndarë: sa intensivisht dhe për cilat tabela janë shfrytëzuar faqet e buffers. Dhe thjesht lejon të shikoni në bufferat e ndarë dhe të vlerësoni atë që ndodh atje.
Moduli tjetĂ«r â Ă«shtĂ« pgfincore. Ai lejon tĂ« kryeni operacione tĂ« nivelit tĂ« ulĂ«t me tabelat pĂ«rmes thirrjeve sistemore mincore(), dmth, ai lejon ngarkimin e tabelĂ«s nĂ« bufferat e ndarĂ«, ose nxjerrjen e saj. Dhe, pĂ«rveç tĂ« tjerash, lejon tĂ« inspektojĂ« cache-n e faqeve tĂ« sistemit operativ, dmth, nĂ« çfarĂ« sasi tabela jonĂ« merret nĂ« cache-n e faqeve, nĂ« bufferat e ndarĂ« dhe thjesht lejon tĂ« vlerĂ«sojĂ« ngarkesĂ«n e tabelĂ«s.
Moduli tjetër është pg_stat_kcache. Ai gjithashtu përdor thirrjen sistemore getrusage(). Dhe e kryen para dhe pas ekzekutimit të kërkesës. Dhe në statistikën e marrë lejon të vlerësosh, sa ka shpenzuar kërkesa për ekzekutimin e hyrjeve dhe daljeve të diskut, dmth, operacioneve me sistemin e skedarëve dhe shikon përdorimin e procesorit. Megjithatë, moduli është i ri (ehm-ehm) dhe për funksionimin e tij kërkon PostgreSQL 9.4 dhe pg_stat_statements, për të cilin kam folur më parë.
Dija për të përdorur statistikat është e dobishme. Nuk ju nevojiten programe të jashtme. Mund të shikoni vetë, të shihni, të bëni diçka, të ekzekutoni.
Përdorimi i statistikave nuk është i vështirë, është SQL normal. Keni mbledhur kërkesën, e keni formuar, e keni dërguar, e keni parë.
Statistika ndihmon pĂ«r tĂ« pĂ«rgjigjur pyetjeve. NĂ«se keni pyetje, ju drejtoheni statistikave â shikoni, bĂ«ni pĂ«rfundime, analizoni rezultatet.
Dhe eksperimento. Ka shumë kërkesa, shumë të dhëna. Gjithmonë mund të optimizohet ndonjë kërkesë ekzistuese. Mund të krijoni versionin tuaj të kërkesës, që ju përshtatet më mirë se origjinali dhe ta përdorni atë.
Linke
Lidhjet e vlefshme, të cilat u përmendën në artikullin, mbi të cilin u paraqit raporti.
Autori, shkruaj akoma
(eng)
Koleksionisti i Statistikave
Funksionet e Administratës së Sistemëve
Modulet kontribuuese
Mjetet SQL dhe shembujt e kodit SQL
Faleminderit të gjithëve për vëmendjen!
Burimi: habr.com
