🥇Malalim na sumisid sa mga panloob na istatistika ng PostgreSQL. Alexey Lesovsky

Transkripsyon ng 2015 na ulat ni Alexey Lesovsky "Deep dive into PostgreSQL internal statistics"

Disclaimer mula sa may-akda ng ulat: Napansin ko na ang ulat na ito ay may petsang Nobyembre 2015 - mahigit 4 na taon na ang lumipas at maraming oras na ang lumipas. Ang bersyon 9.4 na tinalakay sa ulat ay hindi na suportado. Sa nakalipas na 4 na taon, 5 bagong release ang inilabas kung saan maraming inobasyon, pagpapahusay at pagbabago ang lumitaw tungkol sa mga istatistika, at ang ilan sa mga materyal ay luma na at hindi nauugnay. Habang nagrereview ako, sinubukan kong markahan ang mga lugar na ito para hindi ka mailigaw sa mambabasa. Hindi ko muling isinulat ang mga lugar na ito, marami sa kanila, at bilang isang resulta, isang ganap na naiibang ulat ang lalabas.

Ang PostgreSQL DBMS ay isang malaking mekanismo, at ang mekanismong ito ay binubuo ng maraming mga subsystem, ang pinag-ugnay na gawain na direktang nakakaapekto sa pagganap ng DBMS. Sa panahon ng operasyon, ang mga istatistika at impormasyon tungkol sa pagpapatakbo ng mga bahagi ay kinokolekta, na nagbibigay-daan sa iyong suriin ang pagiging epektibo ng PostgreSQL at gumawa ng mga hakbang upang mapabuti ang pagganap. Gayunpaman, mayroong maraming impormasyong ito at ipinakita ito sa isang medyo pinasimpleng anyo. Ang pagpoproseso ng impormasyong ito at pagbibigay-kahulugan dito ay kung minsan ay isang ganap na hindi mahalaga na gawain, at ang "zoo" ng mga tool at utility ay madaling malito kahit isang advanced na DBA.

Magandang hapon Ang pangalan ko ay Aleksey. Tulad ng sinabi ni Ilya, magsasalita ako tungkol sa mga istatistika ng PostgreSQL.

Mga istatistika ng aktibidad ng PostgreSQL. Ang PostgreSQL ay may dalawang istatistika. Mga istatistika ng aktibidad, na tatalakayin. At mga istatistika ng scheduler tungkol sa pamamahagi ng data. Partikular na magsasalita ako tungkol sa mga istatistika ng aktibidad ng PostgreSQL, na nagpapahintulot sa amin na hatulan ang pagganap at kahit papaano ay mapabuti ito.

Sasabihin ko sa iyo kung paano epektibong gumamit ng mga istatistika upang malutas ang iba't ibang mga problema na mayroon ka o maaaring mayroon ka.

Ano ang hindi makikita sa ulat? Sa ulat, hindi ko hawakan ang mga istatistika ng scheduler, dahil. ito ay isang hiwalay na paksa para sa isang hiwalay na ulat kung paano nakaimbak ang data sa database at kung paano nakakakuha ang tagaplano ng query ng ideya ng mga katangian ng husay at dami ng data na ito.

At hindi magkakaroon ng mga pagsusuri sa tool, hindi ko ihahambing ang isang produkto sa isa pa. Hindi magkakaroon ng advertising. I-drop natin ito.

Gusto kong ipakita sa iyo na ang paggamit ng mga istatistika ay kapaki-pakinabang. Kailangan iyon. Gamitin ito nang walang takot. Ang kailangan lang namin ay plain SQL at isang pangunahing kaalaman sa SQL.

At pag-uusapan natin kung aling mga istatistika ang pipiliin upang malutas ang mga problema.

Kung titingnan natin ang PostgreSQL at magpatakbo ng isang command sa operating system upang tingnan ang mga proseso, makikita natin ang isang "itim na kahon". Makakakita tayo ng ilang proseso na gumagawa ng isang bagay, at sa pangalan ay halos maiisip natin kung ano ang kanilang ginagawa doon, kung ano ang kanilang ginagawa. Ngunit, sa katunayan, ito ay isang itim na kahon, hindi namin maaaring tingnan ang loob.

Maaari nating tingnan ang pag-load ng CPU top, makikita natin ang paggamit ng memorya ng ilang mga utility ng system, ngunit hindi natin makikita ang loob ng PostgreSQL. Para dito kailangan namin ng iba pang mga tool.

At sa pagpapatuloy, sasabihin ko sa iyo kung saan ginugol ang oras. Kung kinakatawan namin ang PostgreSQL sa anyo ng naturang scheme, magiging posible na sagutin kung saan ginugol ang oras. Ang mga ito ay dalawang bagay: ito ay ang pagproseso ng mga kahilingan ng kliyente mula sa mga application at ang mga gawain sa background na ginagawa ng PostgreSQL upang mapanatili itong tumatakbo.

Kung magsisimula kaming tumingin sa kaliwang sulok sa itaas, makikita namin kung paano pinoproseso ang mga kahilingan ng kliyente. Ang kahilingan ay nagmula sa aplikasyon at isang sesyon ng kliyente ay binuksan para sa karagdagang trabaho. Ang kahilingan ay ipinasa sa scheduler. Ang tagaplano ay bumuo ng isang query plan. Ipinapadala pa ito para sa pagpapatupad. Mayroong ilang uri ng block I / O data na nauugnay sa mga talahanayan at index. Ang kinakailangang data ay binabasa mula sa mga disk patungo sa memorya sa isang espesyal na lugar na tinatawag na "shared buffers". Ang mga resulta ng query, kung ang mga ito ay mga pag-update, pagtanggal, ay naitala sa log ng transaksyon sa WAL. Ang ilang impormasyon sa istatistika ay napupunta sa log o kolektor ng istatistika. At ang resulta ng kahilingan ay ibinalik sa kliyente. Pagkatapos nito, maaaring ulitin ng kliyente ang lahat gamit ang isang bagong kahilingan.

Ano ang mayroon tayo sa mga gawain sa background at mga proseso sa background? Mayroon kaming ilang mga proseso na nagpapanatiling tumatakbo at tumatakbo nang normal ang database. Ang mga prosesong ito ay sasakupin din sa ulat: ito ay autovacuum, checkpointer, mga prosesong nauugnay sa pagtitiklop, background writer. Hahawakan ko ang bawat isa sa kanila habang nag-uulat ako.

Ano ang mga problema sa istatistika?

Maraming impormasyon. Nagbibigay ang PostgreSQL 9.4 ng 109 na sukatan para sa pagtingin sa data ng mga istatistika. Gayunpaman, kung ang database ay nag-iimbak ng maraming mga talahanayan, mga schema, mga database, ang lahat ng mga sukatan na ito ay kailangang i-multiply sa kaukulang bilang ng mga talahanayan, mga database. Ibig sabihin, marami pang impormasyon. At napakadaling malunod dito.
Ang susunod na problema ay ang mga istatistika ay kinakatawan ng mga counter. Kung titingnan natin ang mga istatistikang ito, makikita natin ang patuloy na pagtaas ng mga counter. At kung maraming oras na ang lumipas mula noong na-reset ang mga istatistika, makikita natin ang bilyun-bilyong halaga. At wala silang sinasabi sa amin.
Walang kasaysayan. Kung mayroon kang ilang uri ng pagkabigo, may nahulog 15-30 minuto ang nakalipas, hindi mo magagamit ang mga istatistika at makita kung ano ang nangyari 15-30 minuto ang nakalipas. Problema ito.
Ang kakulangan ng isang tool na binuo sa PostgreSQL ay isang problema. Ang mga developer ng kernel ay hindi nagbibigay ng anumang utility. Wala silang ganyan. Nagbibigay lang sila ng mga istatistika sa database. Gamitin ito, humiling dito, anuman ang gusto mo, pagkatapos ay gawin ito.
Dahil walang tool na binuo sa PostgreSQL, nagdudulot ito ng isa pang problema. Maraming mga tool ng third party. Ang bawat kumpanya na may higit o mas kaunting direktang mga kamay ay nagsisikap na magsulat ng sarili nitong programa. At bilang isang resulta, ang komunidad ay may maraming mga tool na maaari mong gamitin upang gumana sa mga istatistika. At sa ilang mga tool mayroong ilang mga tampok, sa ibang mga tool ay walang iba pang mga tampok, o mayroong ilang mga bagong tampok. At lumitaw ang isang sitwasyon na kailangan mong gumamit ng dalawa, tatlo, o apat na tool na magkakapatong sa isa't isa at may iba't ibang mga function. Ito ay lubhang nakakainis.

Ano ang kasunod nito? Mahalagang direktang kumuha ng mga istatistika upang hindi umasa sa mga programa, o kahit papaano ay pahusayin ang mga programang ito sa iyong sarili: magdagdag ng ilang mga function upang makuha ang iyong benepisyo.

At kailangan mo ng pangunahing kaalaman sa SQL. Upang makakuha ng ilang data mula sa mga istatistika, kailangan mong gumawa ng mga query sa SQL, ibig sabihin, kailangan mong malaman kung paano ginawa ang pagpili, pagsali.

Sinasabi sa amin ng mga istatistika ang ilang bagay. Maaari silang hatiin sa mga kategorya.

Ang unang kategorya ay ang mga kaganapang nagaganap sa database. Ito ay kapag nangyari ang ilang kaganapan sa database: isang query, isang pag-access sa talahanayan, autovacuum, commit, pagkatapos ito ay lahat ng mga kaganapan. Ang mga counter na tumutugma sa mga kaganapang ito ay dinadagdagan. At masusubaybayan natin ang mga kaganapang ito.
Ang pangalawang kategorya ay ang mga katangian ng mga bagay tulad ng mga talahanayan, mga database. May mga ari-arian sila. Ito ang laki ng mga mesa. Maaari naming subaybayan ang paglago ng mga talahanayan, ang paglago ng mga index. Nakikita natin ang mga pagbabago sa dynamics.
At ang ikatlong kategorya ay ang oras na ginugol sa kaganapan. Ang kahilingan ay isang kaganapan. Mayroon itong sariling tiyak na sukat ng tagal. Dito nagsimula, dito natapos. Masusubaybayan natin ito. Alinman sa oras ng pagbabasa ng isang bloke mula sa disk o pagsulat. Ang mga bagay na ito ay sinusubaybayan din.

Ang mga mapagkukunan ng mga istatistika ay ipinakita tulad ng sumusunod:

Sa nakabahaging memorya (shared buffers) mayroong isang segment para sa paglalagay ng static na data doon, mayroon ding mga counter na patuloy na nadaragdagan kapag nangyari ang ilang mga kaganapan, o ilang sandali ay lumitaw sa pagpapatakbo ng database.
Ang lahat ng mga counter na ito ay hindi available sa user at hindi rin available sa administrator. Ito ay mga bagay na mababa ang antas. Upang ma-access ang mga ito, ang PostgreSQL ay nagbibigay ng isang interface sa anyo ng mga SQL function. Maaari kaming gumawa ng mga piling pagpili gamit ang mga function na ito at makakuha ng ilang uri ng sukatan (o hanay ng mga sukatan).
Gayunpaman, hindi palaging maginhawang gamitin ang mga function na ito, kaya ang mga function ay ang batayan para sa mga view (VIEWs). Ito ay mga virtual na talahanayan na nagbibigay ng mga istatistika sa isang partikular na subsystem, o sa ilang hanay ng mga kaganapan sa database.
Ang mga built-in na view (VIEW) ay ang pangunahing user interface para sa pagtatrabaho sa mga istatistika. Available ang mga ito bilang default nang walang anumang karagdagang mga setting, maaari mong agad na gamitin ang mga ito, panoorin, kumuha ng impormasyon mula doon. At mayroon ding mga kontribusyon. Ang mga kontribusyon ay opisyal. Maaari mong i-install ang postgresql-contrib package (halimbawa, postgresql94-contrib), i-load ang kinakailangang module sa pagsasaayos, tukuyin ang mga parameter para dito, i-restart ang PostgreSQL at magagamit mo ito. (Tandaan. Depende sa pamamahagi, sa mga kamakailang bersyon ng contrib ang package ay bahagi ng pangunahing package).
At may mga hindi opisyal na kontribusyon. Hindi sila binibigyan ng karaniwang pamamahagi ng PostgreSQL. Dapat silang i-compile o i-install bilang isang library. Maaaring ibang-iba ang mga opsyon, depende sa kung ano ang ginawa ng developer ng hindi opisyal na kontribusyong ito.

Ipinapakita ng slide na ito ang lahat ng view (VIEW) at ilan sa mga function na available sa PostgreSQL 9.4. Sa nakikita natin, marami sila. At medyo madaling malito kung naranasan mo ito sa unang pagkakataon.

Gayunpaman, kung kukunin natin ang nakaraang larawan Как тратится время на PostgreSQL at tugma sa listahang ito, nakuha namin ang larawang ito. Ang bawat view (VIEWs), o bawat function, maaari naming gamitin para sa isang layunin o iba pa upang makuha ang naaangkop na istatistika kapag mayroon kaming PostgreSQL na tumatakbo. At maaari na tayong makakuha ng ilang impormasyon tungkol sa pagpapatakbo ng subsystem.

Ang una nating titingnan ay pg_stat_database. Tulad ng nakikita natin, ito ay isang representasyon. Naglalaman ito ng maraming impormasyon. Ang pinaka-iba't ibang impormasyon. At nagbibigay ito ng napakakapaki-pakinabang na kaalaman sa kung ano ang nangyayari sa database.

Ano ang maaari nating kunin mula doon? Magsimula tayo sa mga pinakasimpleng bagay.

select
sum(blks_hit)*100/sum(blks_hit+blks_read) as hit_ratio
from pg_stat_database;

Ang unang bagay na maaari nating tingnan ay ang porsyento ng hit ng cache. Ang porsyento ng cache ng hit ay isang kapaki-pakinabang na sukatan. Binibigyang-daan ka nitong tantiyahin kung gaano karaming data ang kinukuha mula sa nakabahaging cache ng mga buffer, at kung gaano karami ang binabasa mula sa disk.

Ito ay malinaw na mas marami tayong cache hit, mas mabuti. Sinusuri namin ang sukatang ito bilang isang porsyento. At, halimbawa, kung mayroon kaming isang porsyento ng mga hit ng cache na ito na higit sa 90%, kung gayon ito ay mabuti. Kung bumaba ito sa ibaba 90%, kung gayon wala kaming sapat na memorya upang mapanatili ang mainit na ulo ng data sa memorya. At upang magamit ang data na ito, pinipilit ng PostgreSQL na i-access ang disk at ito ay mas mabagal kaysa kung ang data ay nabasa mula sa memorya. At kailangan mong mag-isip tungkol sa pagpapataas ng memorya: maaaring dagdagan ang mga nakabahaging buffer, o dagdagan ang iron memory (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;

Ano pa ang maaaring makuha mula sa pagtatanghal na ito? Maaari mong makita ang mga anomalya na nagaganap sa database. Ano ang ipinapakita dito? May mga commit, rollback, paglikha ng mga pansamantalang file, ang kanilang laki, deadlock at mga salungatan.

Magagamit natin ang kahilingang ito. Ang SQL na ito ay medyo simple. At makikita natin ang data na ito para sa ating sarili.

At narito ang mga halaga ng threshold. Tinitingnan namin ang ratio ng mga commit at rollback. Ang commit ay isang matagumpay na pagkumpirma ng transaksyon. Ang mga rollback ay isang rollback, ibig sabihin, ang transaksyon ay gumawa ng ilang trabaho, pinilit ang database, isinasaalang-alang ang isang bagay, at pagkatapos ay isang pagkabigo, at ang mga resulta ng transaksyon ay itinapon. i.e. ang bilang ng mga rollback na patuloy na tumataas ay masama. At dapat mong iwasan ang mga ito, at i-edit ang code upang hindi ito mangyari.

Ang mga salungatan ay nauugnay sa pagtitiklop. At dapat din silang iwasan. Kung mayroon kang ilang mga query na naisakatuparan sa replica at lumitaw ang mga salungatan, kailangan mong suriin ang mga salungatan na ito at tingnan kung ano ang mangyayari. Ang mga detalye ay matatagpuan sa mga tala. At lutasin ang mga salungatan upang ang mga kahilingan sa application ay gumana nang walang mga error.

Ang deadlock ay isa ring masamang sitwasyon. Kapag ang mga kahilingan ay nakikipagkumpitensya para sa mga mapagkukunan, ang isang kahilingan ay nag-access ng isang mapagkukunan at kinuha ang lock, ang pangalawang kahilingan ay nag-access sa pangalawang mapagkukunan at kinuha din ang lock, at pagkatapos ay ang parehong mga kahilingan ay nag-access sa mga mapagkukunan ng isa't isa at na-block ang paghihintay sa kapitbahay na ilabas ang lock. Problemadong sitwasyon din ito. Kailangang matugunan ang mga ito sa antas ng muling pagsusulat ng mga application at pag-serialize ng access sa mga mapagkukunan. At kung nakikita mo na ang iyong mga deadlock ay patuloy na tumataas, kailangan mong tingnan ang mga detalye sa mga log, pag-aralan ang mga sitwasyon na lumitaw at tingnan kung ano ang problema.

Ang mga pansamantalang file (temp_files) ay masama rin. Kapag ang kahilingan ng user ay walang sapat na memorya upang mapaunlakan ang pagpapatakbo, pansamantalang data, lumilikha ito ng file sa disk. At lahat ng mga operasyon na maaari niyang gawin sa isang pansamantalang buffer sa memorya, nagsisimula siyang gumanap na sa disk. Ito ay mabagal. Pinapataas nito ang oras ng pagpapatupad ng query. At ang kliyente na nagpadala ng kahilingan sa PostgreSQL ay makakatanggap ng tugon sa ibang pagkakataon. Kung ang lahat ng mga operasyong ito ay ginawa sa memorya, ang mga Postgres ay tutugon nang mas mabilis at ang kliyente ay maghihintay nang mas kaunti.

pg_stat_bgwriter - Inilalarawan ng view na ito ang pagpapatakbo ng dalawang PostgreSQL background subsystem: checkpointer и background writer.

Upang magsimula, pag-aralan natin ang mga control point, ang tinatawag na. checkpoints. Ano ang mga checkpoint? Ang checkpoint ay isang posisyon sa log ng transaksyon na nagsasaad na ang lahat ng pagbabago ng data na ginawa sa log ay matagumpay na na-synchronize sa data sa disk. Ang proseso, depende sa workload at mga setting, ay maaaring mahaba at kadalasang binubuo ng pag-synchronize ng mga maruruming page sa mga nakabahaging buffer sa mga file ng data sa disk. Para saan ito? Kung ang PostgreSQL ay ina-access ang disk sa lahat ng oras at kumukuha ng data mula doon, at nagsusulat ng data sa bawat pag-access, ito ay magiging mabagal. Samakatuwid, ang PostgreSQL ay may isang memory segment, ang laki nito ay nakasalalay sa mga parameter sa pagsasaayos. Ang mga postgres ay naglalaan ng data ng pagpapatakbo sa memorya na ito para sa karagdagang pagproseso o pagtatanong. Sa kaso ng mga kahilingan sa pagbabago ng data, binago ang mga ito. At nakakakuha kami ng dalawang bersyon ng data. Ang isa ay nasa memorya, ang isa ay nasa disk. At pana-panahon kailangan mong i-synchronize ang data na ito. Kailangan namin kung ano ang nabago sa memorya upang mai-synchronize sa disk. Nangangailangan ito ng checkpoint.

Dumadaan ang checkpoint sa mga nakabahaging buffer, minarkahan ang mga maruruming pahina na kailangan nila para sa checkpoint. Pagkatapos ay sisimulan nito ang pangalawang pagpasa sa mga nakabahaging buffer. At yung mga page na nakamarka para sa checkpoint, pinagsasabay na niya. Kaya, ang data ay naka-synchronize na sa disk.

Mayroong dalawang uri ng mga control point. Isinasagawa ang isang checkpoint sa timeout. Ang checkpoint na ito ay kapaki-pakinabang at mabuti - checkpoint_timed. At may mga checkpoint on demand - checkpoint required. Ang ganitong checkpoint ay nangyayari kapag mayroon tayong napakalaking rekord ng data. Nag-record kami ng maraming mga log ng transaksyon. At naniniwala ang PostgreSQL na kailangan nitong i-synchronize ang lahat ng ito sa lalong madaling panahon, gumawa ng checkpoint at magpatuloy.

At kung titingnan mo ang mga istatistika pg_stat_bgwriter at tingnan kung ano ang mayroon ka Ang checkpoint_req ay mas malaki kaysa sa checkpoint_timed, kung gayon ito ay masama. Bakit masama? Nangangahulugan ito na ang PostgreSQL ay nasa ilalim ng patuloy na stress kapag kailangan nitong magsulat ng data sa disk. Ang checkpoint sa pamamagitan ng timeout ay hindi gaanong nakaka-stress at isinasagawa ayon sa panloob na iskedyul at, kumbaga, nababanat sa paglipas ng panahon. Ang PostgreSQL ay may kakayahang mag-pause sa trabaho at hindi pilitin ang disk subsystem. Ito ay kapaki-pakinabang para sa PostgreSQL. At ang mga kahilingan na isinasagawa sa checkpoint ay hindi makakaranas ng stress mula sa katotohanan na ang disk subsystem ay abala.

At mayroong tatlong mga parameter upang ayusin ang checkpoint:

сheckpoint_segments.
сheckpoint_timeout.
сheckpoint_competion_target.

Pinapayagan ka nitong kontrolin ang pagpapatakbo ng mga control point. Pero hindi ko na sila papansinin. Ang kanilang impluwensya ay isang hiwalay na isyu.

Babala: Ang bersyon 9.4 na isinasaalang-alang sa ulat ay hindi na nauugnay. Sa mga modernong bersyon ng PostgreSQL, ang parameter checkpoint_segments pinalitan ng mga parameter min_wal_size и max_wal_size.

Ang susunod na subsystem ay ang background writer − background writer. Ano ang ginagawa niya? Ito ay patuloy na tumatakbo sa isang walang katapusang loop. Ini-scan nito ang mga pahina sa mga nakabahaging buffer at ini-flush sa disk ang mga maruruming pahinang nahanap nito. Sa ganitong paraan, nakakatulong ito sa checkpointer na gumawa ng mas kaunting trabaho sa panahon ng checkpointing.

Ano pa ba ang kailangan niya? Nagbibigay ito ng pangangailangan para sa malinis na mga pahina sa mga nakabahaging buffer kung bigla silang kinakailangan (sa malalaking dami at kaagad) upang mapaunlakan ang data. Ipagpalagay na ang isang sitwasyon ay lumitaw kapag ang kahilingan ay nangangailangan ng malinis na mga pahina at ang mga ito ay nasa mga nakabahaging buffer. Mga postgres backend kinukuha lang niya at ginagamit, hindi niya kailangang linisin ang sarili niya. Ngunit kung biglang walang ganoong mga pahina, ang backend ay huminto at magsisimulang maghanap ng mga pahina upang i-flush ang mga ito sa disk at dalhin ang mga ito para sa sarili nitong mga pangangailangan - na negatibong nakakaapekto sa oras ng kasalukuyang pagpapatupad ng kahilingan. Kung nakita mo na mayroon kang isang parameter maxwritten_clean malaki, nangangahulugan ito na hindi ginagawa ng background writer ang trabaho nito at kailangan mong dagdagan ang mga parameter bgwriter_lru_maxpagespara makagawa siya ng mas maraming trabaho sa isang cycle, mag-clear ng mas maraming page.

At isa pang napaka-kapaki-pakinabang na tagapagpahiwatig ay buffers_backend_fsync. Ang mga backend ay hindi gumagawa ng fsync dahil ito ay mabagal. Ipapasa nila ang fsync sa IO stack checkpointer. Ang checkpointer ay may sariling pila, pana-panahon itong nagpoproseso ng fsync at nagsi-synchronize ng mga pahina sa memorya sa mga file sa disk. Kung ang checkpointer queue ay malaki at puno, ang backend ay mapipilitang gawin ang fsync mismo at ito ay nagpapabagal sa backend, ibig sabihin, makakatanggap ang kliyente ng tugon sa ibang pagkakataon kaysa sa maaari. Kung nakikita mo na mayroon kang halagang ito na mas malaki kaysa sa zero, ito ay isa nang problema at kailangan mong bigyang-pansin ang mga setting ng background writer at suriin din ang pagganap ng disk subsystem.

Babala: _Ang sumusunod na teksto ay naglalarawan ng istatistikal na pananaw na nauugnay sa pagtitiklop. Karamihan sa mga pangalan ng view at function ay pinalitan ng pangalan sa Postgres 10. Ang kakanyahan ng mga pagpapalit ng pangalan ay palitan xlog sa wal и location sa lsn sa mga pangalan ng function/view, atbp. Partikular na halimbawa, pag-andar pg_xlog_location_diff() ay pinalitan ng pangalan sa pg_wal_lsn_diff()._

Marami din tayo dito. Ngunit kailangan lang namin ng mga item na may kaugnayan sa lokasyon.

Kung nakikita natin na ang lahat ng mga halaga ay pantay, kung gayon ito ay perpekto at ang replika ay hindi nahuhuli sa master.

Ang hexadecimal na posisyon dito ay ang posisyon sa log ng transaksyon. Patuloy itong tumataas kung mayroong ilang aktibidad sa database: pagsingit, pagtanggal, atbp.

сколько записано 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());

Kung ang mga bagay na ito ay naiiba, pagkatapos ay mayroong ilang uri ng lag. Ang Lag ay ang lag ng replica mula sa master, ibig sabihin, ang data ay naiiba sa pagitan ng mga server.

May tatlong dahilan para sa pagkaantala:

Ito ang disk subsystem na hindi makayanan ang pagsusulat ng pag-sync ng file.
Ito ay mga posibleng network error, o network overload, kapag ang data ay walang oras upang maabot ang replica at hindi ito maaaring kopyahin.
At ang processor. Ang processor ay isang napakabihirang kaso. At dalawa o tatlong beses ko nang nakita iyon, ngunit maaaring mangyari din iyon.

At narito ang tatlong query na nagpapahintulot sa amin na gumamit ng mga istatistika. Maaari naming tantyahin kung magkano ang naitala sa aming log ng transaksyon. May ganoong function pg_xlog_location_diff at maaari nating tantyahin ang lag ng pagtitiklop sa mga byte at segundo. Ginagamit din namin ang halaga mula sa view na ito (mga VIEW) para dito.

Tandaan: _Sa halip na pg_xlog_locationdiff() function, maaari mong gamitin ang subtract operator at ibawas ang isang lokasyon mula sa isa pa. Komportable.

Sa isang lag, na nasa segundo, mayroong isang sandali. Kung walang aktibidad sa master, ang transaksyon ay naroon mga 15 minuto ang nakalipas at walang aktibidad, at kung titingnan natin ang lag na ito sa replica, makikita natin ang isang lag ng 15 minuto. Ito ay nagkakahalaga ng pag-alala. At maaari itong humantong sa pagkahilo kapag napanood mo ang lag na ito.

Ang pg_stat_all_tables ay isa pang kapaki-pakinabang na view. Nagpapakita ito ng mga istatistika sa mga talahanayan. Kapag mayroon kaming mga talahanayan sa database, mayroong ilang aktibidad kasama nito, ilang mga aksyon, maaari naming makuha ang impormasyong ito mula sa view na ito.

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;

Ang unang bagay na maaari nating tingnan ay ang mga sequential table scan. Ang bilang mismo pagkatapos ng mga sipi na ito ay hindi naman masama at hindi nagpapahiwatig na kailangan na nating gawin ang isang bagay.

Gayunpaman, mayroong pangalawang sukatan - seq_tup_read. Ito ang bilang ng mga row na ibinalik mula sa sequential scan. Kung ang average na numero ay lumampas sa 1, 000, 10, 000, ito ay isa nang tagapagpahiwatig na maaaring kailanganin mong bumuo ng isang index sa isang lugar upang ang mga pag-access ay ayon sa index, o posible na i-optimize ang mga query na gumagamit ng naturang mga sunud-sunod na pag-scan upang hindi ito nangyayari. was.

Isang simpleng halimbawa - sabihin nating isang kahilingan na may malaking OFFSET at LIMIT ay sulit. Halimbawa, 100 row sa isang table ang na-scan at pagkatapos noon ay 000 row ang kinukuha, at ang mga naunang na-scan na row ay itatapon. Ito rin ay isang masamang kaso. At ang mga naturang kahilingan ay kailangang ma-optimize. At narito ang isang simpleng query sa SQL kung saan makikita mo ito at suriin ang mga natanggap na numero.

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;

Ang mga sukat ng talahanayan ay maaari ding makuha gamit ang talahanayang ito at gamit ang mga karagdagang function pg_total_relation_size(), pg_relation_size().

Sa pangkalahatan, may mga metacommand dt и di, na magagamit mo sa PSQL at tingnan din ang mga laki ng talahanayan at index.

Gayunpaman, ang paggamit ng mga function ay tumutulong sa amin na tingnan ang mga sukat ng mga talahanayan, kahit na isinasaalang-alang ang mga index, o nang hindi isinasaalang-alang ang mga index, at gumawa na ng ilang mga pagtatantya batay sa paglago ng database, ibig sabihin, kung paano ito lumalaki kasama namin, na may anong intensity, at gumawa na ng ilang konklusyon tungkol sa pag-optimize ng sizing.

Sumulat ng aktibidad. Ano ang isang talaan? Tingnan natin ang operasyon UPDATE – ang pagpapatakbo ng pag-update ng mga hilera sa talahanayan. Sa katunayan, ang pag-update ay dalawang operasyon (o higit pa). Ito ay naglalagay ng bagong bersyon ng row at minamarkahan ang lumang bersyon ng row bilang hindi na ginagamit. Sa ibang pagkakataon, darating ang autovacuum at linisin ang mga hindi na ginagamit na bersyon ng mga linya, markahan ang lugar na ito bilang magagamit muli.

Gayundin, ang pag-update ay hindi lamang tungkol sa pag-update ng isang talahanayan. Ito ay isang index update pa rin. Kung mayroon kang maraming mga index sa talahanayan, pagkatapos ay sa pag-update, ang lahat ng mga index kung saan ang mga patlang na na-update sa query ay lumahok ay kailangan ding i-update. Ang mga index na ito ay magkakaroon din ng mga hindi na ginagamit na bersyon ng row na kailangang linisin.

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;

At dahil sa disenyo nito, ang UPDATE ay isang heavyweight na operasyon. Ngunit maaari silang gawing mas madali. Kumain hot updates. Sila ay lumitaw sa PostgreSQL na bersyon 8.3. At ano ito? Ito ay isang magaan na pag-update na hindi nagiging sanhi ng muling pagtatayo ng mga index. Iyon ay, na-update namin ang tala, ngunit ang tala lamang sa pahina (na kabilang sa talahanayan) ang na-update, at ang mga index ay tumuturo pa rin sa parehong talaan sa pahina. Mayroong kaunting tulad ng isang kawili-wiling lohika ng trabaho, kapag ang isang vacuum ay dumating, pagkatapos ay mayroon itong mga kadena hot muling itinayo at ang lahat ay patuloy na gumagana nang hindi ina-update ang mga index, at lahat ng bagay ay nangyayari nang mas kaunting pag-aaksaya ng mga mapagkukunan.

At kapag mayroon ka n_tup_hot_upd malaki, napakabuti. Nangangahulugan ito na nananaig ang magaan na mga update at ito ay mas mura para sa amin sa mga tuntunin ng mga mapagkukunan at lahat ay maayos.

ALTER TABLE table_name SET (fillfactor = 70);

Paano dagdagan ang volume hot updateov? Pwede natin gamitin fillfactor. Tinutukoy nito ang laki ng nakareserbang libreng espasyo kapag pinupunan ang isang pahina sa isang talahanayan gamit ang mga INSERT. Kapag ang mga pagsingit ay pumunta sa talahanayan, ganap nilang pinupuno ang pahina, huwag mag-iwan ng walang laman na espasyo dito. Pagkatapos ay naka-highlight ang isang bagong pahina. Ang data ay napunan muli. At ito ang default na pag-uugali, fillfactor = 100%.

Maaari naming itakda ang fillfactor sa 70%. Iyon ay, may mga pagsingit, isang bagong pahina ang inilaan, ngunit 70% lamang ng pahina ang napunan. At mayroon kaming 30% na natitira sa reserba. Kapag kailangan mong gumawa ng update, malamang na mangyayari ito sa parehong page, at ang bagong bersyon ng row ay magkakasya sa parehong page. At gagawin ang hot_update. Ginagawa nitong mas madaling magsulat sa mga talahanayan.

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 na pila. Ang Autovacuum ay isang subsystem kung saan kakaunti ang mga istatistika sa PostgreSQL. Makikita lang natin sa mga talahanayan sa pg_stat_activity kung gaano karaming mga vacuum ang mayroon tayo sa ngayon. Gayunpaman, napakahirap maunawaan kung gaano karaming mga talahanayan sa pila ang mayroon siya habang naglalakbay.

Tandaan: _Simula sa Postgres 10, ang sitwasyon sa pagsubaybay sa vacuum vacuum ay bumuti nang husto - lumitaw ang pg_stat_progress viewvacuum, na lubos na nagpapadali sa isyu ng autovacuum monitoring.

Magagamit natin itong pinasimpleng query. At makikita natin kung kailan dapat gawin ang vacuum. Ngunit, paano at kailan dapat magsimula ang vacuum? Ito ang mga lumang bersyon ng mga string na napag-usapan ko kanina. Naganap ang pag-update, naipasok na ang bagong bersyon ng row. Isang hindi na ginagamit na bersyon ng string ang lumitaw. mesa pg_stat_user_tables may ganyang parameter n_dead_tup. Ipinapakita nito ang bilang ng mga "patay" na hanay. At sa sandaling ang bilang ng mga patay na hanay ay naging higit sa isang tiyak na threshold, isang autovacuum ang darating sa talahanayan.

At paano kinakalkula ang threshold na ito? Ito ay isang napaka-tukoy na porsyento ng kabuuang bilang ng mga hilera sa talahanayan. Mayroong isang parameter autovacuum_vacuum_scale_factor. Tinutukoy nito ang porsyento. Sabihin nating 10% + mayroong karagdagang base threshold na 50 linya. At ano ang mangyayari? Kapag mayroon kaming mas maraming patay na row kaysa sa "10% + 50" ng lahat ng row sa talahanayan, inilalagay namin ang talahanayan sa autovacuum.

select c.relname,
current_setting('autovacuum_vacuum_threshold') as av_base_thresh,
current_setting('autovacuum_vacuum_scale_factor') as av_scale_factor,
(current_setting('autovacuum_vacuum_threshold')::int +
(current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples))
as av_thresh,
s.n_dead_tup
from pg_stat_user_tables s join pg_class c ON s.relname = c.relname
where s.n_dead_tup > (current_setting('autovacuum_vacuum_threshold')::int
+ (current_setting('autovacuum_vacuum_scale_factor')::float * c.reltuples));

Gayunpaman, mayroong isang punto. Mga pangunahing threshold para sa mga parameter av_base_thresh и av_scale_factor maaaring italaga nang paisa-isa. At, nang naaayon, ang threshold ay hindi magiging pandaigdigan, ngunit indibidwal para sa talahanayan. Samakatuwid, upang makalkula, doon kailangan mong gumamit ng mga trick at trick. At kung interesado ka, maaari mong tingnan ang karanasan ng aming mga kasamahan mula sa Avito (ang link sa slide ay hindi wasto at na-update sa teksto).

Sumulat sila para sa munin pluginna isinasaalang-alang ang mga bagay na ito. May footcloth sa dalawang sheet. Ngunit siya ay isinasaalang-alang nang tama at medyo epektibong nagbibigay-daan sa amin upang masuri kung saan kailangan namin ng maraming vacuum para sa mga talahanayan, kung saan mayroong kaunti.

Ano ang maaari nating gawin tungkol dito? Kung mayroon tayong mahabang pila at hindi makayanan ng autovacuum, maaari nating dagdagan ang bilang ng mga vacuum worker, o gawing mas agresibo ang vacuum.upang ito ay nag-trigger nang mas maaga, pinoproseso ang talahanayan sa maliliit na piraso. At sa gayon ay bababa ang pila. - Ang pangunahing bagay dito ay upang subaybayan ang pagkarga sa mga disk, dahil. Ang vacuum na bagay ay hindi libre, bagaman sa pagdating ng mga SSD / NVMe device, ang problema ay naging hindi gaanong kapansin-pansin.

Ang pg_stat_all_indexes ay mga istatistika sa mga index. Hindi siya malaki. At makakakuha tayo ng impormasyon sa paggamit ng mga index mula dito. At halimbawa, matutukoy namin kung aling mga index ang mayroon kami ng dagdag.

Gaya ng sinabi ko na, Ang pag-update ay hindi lamang pag-update ng mga talahanayan, pag-update din ng mga index. Alinsunod dito, kung mayroon kaming maraming mga index sa talahanayan, pagkatapos kapag ina-update ang mga hilera sa talahanayan, ang mga index ng mga na-index na patlang ay kailangan ding i-update, at kung mayroon kaming hindi nagamit na mga index kung saan walang mga pag-scan ng index, kung gayon ang mga ito ay kasama namin bilang ballast. At kailangan mong alisin ang mga ito. Para dito kailangan namin ng isang patlang idx_scan. Tinitingnan lang namin ang bilang ng mga index scan. Kung ang mga index ay walang mga pag-scan sa loob ng medyo mahabang panahon ng pag-iimbak ng mga istatistika (hindi bababa sa 2-3 linggo), malamang na ang mga ito ay hindi magandang mga index, kailangan nating alisin ang mga ito.

Tandaan: Kapag naghahanap ng hindi nagamit na mga index sa kaso ng streaming na mga cluster ng pagtitiklop, kailangan mong suriin ang lahat ng mga node ng cluster, dahil ang mga istatistika ay hindi pandaigdigan, at kung ang index ay hindi ginagamit sa master, maaari itong magamit sa mga replika (kung mayroong pag-load).

Dalawang link:

https://github.com/dataegret/pg-utils/blob/master/sql/low_used_indexes.sql

http://www.databasesoup.com/2014/05/new-finding-unused-indexes-query.html

Ito ay mga mas advanced na halimbawa ng query para sa kung paano maghanap ng mga hindi nagamit na index.

Ang pangalawang link ay isang medyo kawili-wiling query. Mayroong isang napaka non-trivial na lohika doon. Inirerekomenda ko ito para sa pagsusuri.

Ano pa ang dapat ibuod ng mga index?

Ang mga hindi nagamit na index ay masama.
Kumuha sila ng espasyo.
Pabagalin ang mga pagpapatakbo ng pag-update.
Karagdagang trabaho para sa vacuum.

Kung aalisin namin ang mga hindi nagamit na index, gagawin lang namin ang database na mas mahusay.

Ang susunod na view ay pg_stat_activity. Ito ay isang analogue ng utility ps, sa PostgreSQL lamang. Kung ps'ohm pinapanood mo ang mga proseso sa operating system, kung gayon pg_stat_activity ipapakita sa iyo ang aktibidad sa loob ng PostgreSQL.

Ano ang maaari nating kunin mula doon?

select
count(*)*100/(select current_setting('max_connections')::int)
from pg_stat_activity;

Makikita natin ang kabuuang aktibidad na nangyayari sa database. Maaari tayong gumawa ng bagong deployment. Ang lahat ay sumabog doon, ang mga bagong koneksyon ay hindi tinatanggap, ang mga error ay bumubuhos sa aplikasyon.

select
client_addr, usename, datname, count(*)
from pg_stat_activity group by 1,2,3 order by 4 desc;

Maaari kaming magpatakbo ng isang query na tulad nito at makita ang kabuuang porsyento ng mga koneksyon na nauugnay sa maximum na limitasyon ng koneksyon at makita kung sino ang mayroon kaming pinakamaraming koneksyon. At sa ibinigay na kaso na ito, nakikita namin ang user na iyon cron_role nagbukas ng 508 na koneksyon. At may nangyari sa kanya. Kailangan mong harapin ito at tingnan. At ito ay lubos na posible na ito ay isang uri ng maanomalyang bilang ng mga koneksyon.

Kung mayroon tayong OLTP load, ang mga query ay dapat na mabilis, napakabilis, at hindi dapat magkaroon ng mahabang query. Gayunpaman, kung may mga mahabang kahilingan, pagkatapos ay sa maikling panahon ay walang dapat ipag-alala, ngunit sa katagalan, ang mga mahabang query ay nakakasira sa database, pinapataas nila ang bloat effect ng mga talahanayan kapag nangyari ang table fragmentation. Parehong bloat at mahabang query ay kailangang itapon.

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;

Pakitandaan: sa ganoong kahilingan, maaari naming tukuyin ang mahahabang kahilingan at transaksyon. Ginagamit namin ang function clock_timestamp() upang matukoy ang oras ng pagtatrabaho. Mahabang mga kahilingan na aming natagpuan, maaari naming tandaan ang mga ito, isagawa ang mga ito explain, tumingin sa mga plano at kahit papaano ay mag-optimize. Kinukuha namin ang kasalukuyang mahahabang kahilingan at nabubuhay.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

Ang mga masasamang transaksyon ay idle sa transaksyon at idle sa transaksyon (na-abort) na mga transaksyon.

Ano ang ibig sabihin nito? Ang mga transaksyon ay may maraming estado. At maaaring tumagal ang isa sa mga estadong ito anumang oras. Mayroong isang patlang upang tukuyin ang mga estado state sa ganitong pananaw. At ginagamit namin ito upang matukoy ang estado.

select * from pg_stat_activity where state in
('idle in transaction', 'idle in transaction (aborted)';

At, gaya ng sinabi ko sa itaas, ang dalawang estadong ito Ang idle sa transaksyon at ang idle sa transaksyon (na-abort) ay masama. Ano ito? Ito ay kapag ang application ay nagbukas ng isang transaksyon, gumawa ng ilang mga aksyon at ginawa ang negosyo nito. Ang transaksyon ay nananatiling bukas. Ito ay nakabitin, walang nangyayari dito, nangangailangan ito ng koneksyon, nakakandado sa mga nabagong hanay at potensyal na nagpapataas pa rin ng bloat ng iba pang mga talahanayan, dahil sa arkitektura ng Postrges transactional engine. At ang mga naturang transaksyon ay dapat ding pagbaril, dahil nakakapinsala sila sa pangkalahatan, sa anumang kaso.

Kung nakikita mong mayroon kang higit sa 5-10-20 sa kanila sa iyong database, kailangan mong mag-alala at magsimulang gumawa ng isang bagay sa kanila.

Dito rin namin ginagamit para sa oras ng pagkalkula clock_timestamp(). Kinukuha namin ang mga transaksyon, ino-optimize namin ang application.

Tulad ng sinabi ko sa itaas, ang mga kandado ay kapag dalawa o higit pang mga transaksyon ang nakikipagkumpitensya para sa isa o isang grupo ng mga mapagkukunan. Para dito mayroon kaming isang larangan waiting na may boolean na halaga true o false.

Totoo - nangangahulugan ito na ang proseso ay naghihintay, may kailangang gawin. Kapag naghihintay ang isang proseso, naghihintay din ang kliyente na nagpasimula ng proseso. Ang kliyente sa browser ay nakaupo at naghihintay din.

Babala: _Simula sa Postgres 9.6, ang field waiting inalis at pinalitan ng dalawa pang field na nagbibigay-kaalaman wait_event_type и wait_event._

Ano ang dapat gawin? Kung nakikita mong totoo sa loob ng mahabang panahon, dapat mong alisin ang mga naturang kahilingan. Kinukuha lang namin ang mga ganoong transaksyon. Sumulat kami sa mga developer kung ano ang kailangang i-optimize kahit papaano para walang karera para sa mga mapagkukunan. At pagkatapos ay i-optimize ng mga developer ang application upang hindi ito mangyari.

At ang sukdulan, ngunit sa parehong oras potensyal na hindi nakamamatay na kaso ay paglitaw ng mga deadlock. Dalawang transaksyon ang nag-update ng dalawang mapagkukunan, pagkatapos ay ina-access nila muli ang mga ito, na sa tapat ng mga mapagkukunan. Ang PostgreSQL sa kasong ito ay tumatagal at nag-shoot sa mismong transaksyon upang ang isa ay maaaring magpatuloy sa pagtatrabaho. Ito ay isang dead end na sitwasyon at hindi niya maintindihan ang kanyang sarili. Samakatuwid, ang PostgreSQL ay napipilitang gumawa ng matinding mga hakbang.

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/c4_06_show_locked_queries.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_95.sql

https://github.com/lesovsky/uber-scripts/blob/master/postgresql/sql/show_locked_queries_96.sql

http://big-elephants.com/2013-09/exploring-query-locks-in-postgres/

At narito ang dalawang query na nagbibigay-daan sa iyong subaybayan ang mga lock. Ginagamit namin ang view pg_locks, na nagbibigay-daan sa iyong subaybayan ang mabibigat na lock.

At ang unang link ay ang request text mismo. Medyo mahaba.

At ang pangalawang link ay isang artikulo sa mga kandado. Ito ay kapaki-pakinabang na basahin, ito ay lubhang kawili-wili.

Kaya ano ang nakikita natin? Nakikita namin ang dalawang kahilingan. Transaksyon sa ALTER TABLE ay isang transaksyon sa pagharang. Nagsimula ito, ngunit hindi natapos, at ang application na nag-post ng transaksyong ito ay gumagawa ng iba pang mga bagay sa isang lugar. At ang pangalawang kahilingan ay ang pag-update. Hinihintay nitong matapos ang alter table bago ipagpatuloy ang trabaho nito.

Ito ay kung paano natin malalaman kung sino ang nagkulong kung sino, sino ang humawak kung sino, at maaari pa nating harapin ito.

Ang susunod na modyul ay pg_stat_statements. Tulad ng sinabi ko, ito ay isang module. Para magamit ito, kailangan mong i-load ang library nito sa configuration, i-restart ang PostgreSQL, i-install ang module (na may isang command), at pagkatapos ay magkakaroon tayo ng bagong view.

Cреднее время запроса в милисекундах
$ select (sum(total_time) / sum(calls))::numeric(6,3)
from pg_stat_statements;

Самые активно пишущие (в shared_buffers) запросы
$ select query, shared_blks_dirtied
from pg_stat_statements
where shared_blks_dirtied > 0 order by 2 desc;

Ano ang maaari nating kunin mula doon? Kung pag-uusapan natin ang mga simpleng bagay, maaari nating kunin ang average na oras ng pagpapatupad ng query. Ang oras ay lumalaki, na nangangahulugan na ang PostgreSQL ay tumutugon nang mabagal at may kailangang gawin.

Makikita natin ang pinakaaktibong mga transaksyon sa pagsulat sa database na nagbabago ng data sa mga nakabahaging buffer. Tingnan kung sino ang nag-a-update o nagde-delete ng data doon.

At maaari lang nating tingnan ang iba't ibang istatistika para sa mga kahilingang ito.

https://github.com/dataegret/pg-utils/blob/master/sql/global_reports/query_stat_total.sql

Kami pg_stat_statements ginamit upang bumuo ng mga ulat. Nire-reset namin ang mga istatistika isang beses sa isang araw. Ipunin natin ito. Bago i-reset ang mga istatistika sa susunod, bubuo kami ng ulat. Narito ang isang link sa ulat. Maaari mong panoorin ito.

Anong gagawin natin? Kinakalkula namin ang pangkalahatang istatistika para sa lahat ng kahilingan. Pagkatapos, para sa bawat query, binibilang namin ang indibidwal na kontribusyon nito sa pangkalahatang istatistikang ito.

At ano ang makikita natin? Makikita natin ang kabuuang oras ng pagpapatupad ng lahat ng kahilingan ng isang partikular na uri sa background ng lahat ng iba pang kahilingan. Maaari nating tingnan ang paggamit ng CPU at I/O na may kaugnayan sa pangkalahatang larawan. At upang i-optimize ang mga kahilingang ito. Gumagawa kami ng mga nangungunang query batay sa ulat na ito at nakakakuha na kami ng pag-iisip kung ano ang i-optimize.

Ano ang mayroon tayo sa likod ng mga eksena? Mayroon pa ring ilang mga pagsusumite na hindi ko isinasaalang-alang, dahil limitado ang oras.

Mayroon pgstattuple ay isa ring karagdagang module mula sa karaniwang contribus package. Pinapayagan ka nitong suriin bloat mga talahanayan, tinatawag na. pagkakapira-piraso ng mesa. At kung malaki ang fragmentation, kailangan mong alisin ito, gumamit ng iba't ibang mga tool. At pag-andar pgstattuple gumagana nang mahabang panahon. At mas maraming mga talahanayan, mas mahaba ito gagana.

Ang susunod na kontribusyon ay pg_buffercache. Binibigyang-daan ka nitong suriin ang mga nakabahaging buffer: kung gaano kalakas at kung aling mga talahanayan ang ginagamit ang mga pahina ng buffer. At pinapayagan ka lamang nitong tumingin sa mga nakabahaging buffer at suriin kung ano ang nangyayari doon.

Ang susunod na modyul ay pgfincore. Binibigyang-daan ka nitong magsagawa ng mababang antas ng mga pagpapatakbo ng talahanayan sa pamamagitan ng isang tawag sa system mincore(), ibig sabihin, pinapayagan ka nitong i-load ang talahanayan sa mga nakabahaging buffer, o i-unload ito. At pinapayagan nito, bukod sa iba pang mga bagay, upang siyasatin ang cache ng pahina ng operating system, iyon ay, kung magkano ang nasasakop ng talahanayan sa cache ng pahina, sa mga nakabahaging buffer, at pinapayagan kang suriin ang pag-load sa talahanayan.

Ang susunod na modyul ay pg_stat_kcache. Ginagamit din nito ang system call getrusage(). At ipapatupad ito bago at pagkatapos isagawa ang kahilingan. At sa mga istatistika na nakuha, ito ay nagbibigay-daan sa amin upang tantiyahin kung magkano ang aming kahilingan na ginastos sa disk I / O, ibig sabihin, mga operasyon sa file system at tumitingin sa paggamit ng processor. Gayunpaman, ang module ay bata pa (khe-khe) at para sa trabaho nito ay nangangailangan ito ng PostgreSQL 9.4 at pg_stat_statements, na nabanggit ko kanina.

Ang kakayahang gumamit ng mga istatistika ay kapaki-pakinabang. Hindi mo kailangan ng software ng third party. Maaari kang tumingin, makakita, gumawa ng isang bagay, gumanap.
Ang paggamit ng mga istatistika ay madali, ito ay simpleng SQL. Nakolekta mo ang isang kahilingan, pinagsama-sama ito, ipinadala ito, tiningnan ito.
Nakakatulong ang mga istatistika sa pagsagot sa mga tanong. Kung mayroon kang mga katanungan, bumaling ka sa mga istatistika - tumingin, gumawa ng mga konklusyon, pag-aralan ang mga resulta.
At eksperimento. Maraming mga kahilingan, maraming data. Maaari mong palaging i-optimize ang ilang umiiral nang query. Maaari kang gumawa ng sarili mong bersyon ng kahilingan na mas nababagay sa iyo kaysa sa orihinal at gamitin ito.