ProHoster > Blogs > Administrācija > Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Alekseja Ä»esovska 2015. gada ziņojuma "Deep dive into PostgreSQL iekŔējā statistika" atÅ”ifrējums

Ziņojuma autora atruna: Es atzÄ«mēju, ka Å”is ziņojums ir datēts ar 2015. gada novembri - ir pagājuÅ”i vairāk nekā 4 gadi un pagājis daudz laika. Pārskatā aplÅ«kotā versija 9.4 vairs netiek atbalstÄ«ta. Pēdējo 4 gadu laikā ir izdoti 5 jauni izdevumi, kuros ir daudz jauninājumu, uzlabojumu un izmaiņu attiecÄ«bā uz statistiku, un daļa no materiāliem ir novecojuÅ”i un nav aktuāli. Pārskatot, es centos atzÄ«mēt Ŕīs vietas, lai nemaldinātu lasÄ«tāju. Es nepārrakstÄ«ju Å”os fragmentus, to ir daudz un rezultāts bÅ«s pavisam cits ziņojums.

PostgreSQL DBVS ir milzÄ«gs mehānisms, un Å”is mehānisms sastāv no daudzām apakÅ”sistēmām, kuru koordinēta darbÄ«ba tieÅ”i ietekmē DBVS veiktspēju. DarbÄ«bas laikā tiek apkopota statistika un informācija par komponentu darbÄ«bu, kas ļauj novērtēt PostgreSQL efektivitāti un veikt pasākumus veiktspējas uzlaboÅ”anai. Tomēr Ŕīs informācijas ir daudz, un tā ir sniegta diezgan vienkārÅ”otā veidā. Å Ä«s informācijas apstrāde un interpretācija dažreiz ir pilnÄ«gi nenozÄ«mÄ«gs uzdevums, un rÄ«ku un utilÄ«tu ā€œzoodārzsā€ var viegli sajaukt pat progresÄ«vu DBA.
Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis


Labdien Mani sauc Aleksejs. Kā teica Iļja, es runāŔu par PostgreSQL statistiku.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

PostgreSQL aktivitāŔu statistika. PostgreSQL ir divi statistikas dati. DarbÄ«bas statistika, kas tiks apspriesta. Un plānotāja statistika par datu sadali. Es runāŔu konkrēti par PostgreSQL aktivitāŔu statistiku, kas ļauj spriest par veiktspēju un kaut kā to uzlabot.

Es jums pastāstÄ«Å”u, kā efektÄ«vi izmantot statistiku, lai atrisinātu dažādas problēmas, kas jums ir vai varētu bÅ«t.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Kas nebÅ«s atskaitē? Pārskatā es neskarÅ”u plānotāja statistiku, jo... Å Ä« ir atseviŔķa tēma atseviŔķam ziņojumam par to, kā dati tiek glabāti datu bāzē un kā vaicājumu plānotājs gÅ«st priekÅ”statu par Å”o datu kvalitatÄ«vajām un kvantitatÄ«vajām Ä«paŔībām.

Un nebūs instrumentu apskatu, nesalīdzināŔu vienu produktu ar citu. Reklāmas nebūs. Liksim to malā.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Es vēlos jums parādÄ«t, ka statistikas izmantoÅ”ana ir noderÄ«ga. Tas ir nepiecieÅ”ams. Tas ir droÅ”i lietojams. Viss, kas mums nepiecieÅ”ams, ir regulāra SQL un pamatzināŔanas par SQL.

Un parunāsim par to, kādu statistiku izvēlēties problēmu risināŔanai.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Ja mēs skatāmies uz PostgreSQL un palaižam komandu operētājsistēmā, lai skatÄ«tu procesus, mēs redzēsim "melno kasti". Mēs redzēsim dažus procesus, kas kaut ko dara, un pēc nosaukuma mēs aptuveni varam iedomāties, ko viņi tur dara, ko viņi dara. Bet bÅ«tÄ«bā tā ir melnā kaste, mēs nevaram ieskatÄ«ties iekŔā.

Mēs varam redzēt CPU slodzi top, mēs varam aplÅ«kot dažu sistēmas utilÄ«tu atmiņas izmantoÅ”anu, taču mēs nevarēsim ieskatÄ«ties PostgreSQL. Å im nolÅ«kam mums ir nepiecieÅ”ami citi rÄ«ki.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Un, turpinot tālāk, pastāstÄ«Å”u, kur tiek pavadÄ«ts laiks. Ja iedomājamies PostgreSQL Ŕādas diagrammas veidā, tad varam atbildēt, kur tiek pavadÄ«ts laiks. Tās ir divas lietas: tā apstrādā klientu pieprasÄ«jumus no lietojumprogrammām un fona uzdevumi, ko PostgreSQL veic, lai turpinātu darboties.

Ja mēs sākam skatÄ«ties augŔējā kreisajā stÅ«rÄ«, mēs varam redzēt, kā tiek apstrādāti klientu pieprasÄ«jumi. PieprasÄ«jums nāk no lietojumprogrammas, un tiek atvērta klienta sesija turpmākam darbam. PieprasÄ«jums tiek nosÅ«tÄ«ts plānotājam. Plānotājs izveido vaicājumu plānu. NosÅ«ta to tālāk izpildei. Ir sava veida bloku datu ievade/izvade, kas saistÄ«ta ar tabulām un indeksiem. NepiecieÅ”amie dati tiek nolasÄ«ti no diskiem atmiņā Ä«paŔā apgabalā "koplietojamos buferos". PieprasÄ«juma rezultāti, ja tie ir atjauninājumi, dzēsumi, tiek ierakstÄ«ti darÄ«jumu žurnālā WAL. Daļa statistikas informācijas nonāk žurnālā vai statistikas apkopotājā. Un pieprasÄ«juma rezultāts tiek nosÅ«tÄ«ts atpakaļ klientam. Pēc tam klients var atkārtot visu vēlreiz ar jaunu pieprasÄ«jumu.

Kā ar fona uzdevumiem un fona procesiem? Mums ir vairāki procesi, kas nodroÅ”ina datu bāzes darbÄ«bu normālā darbÄ«bas režīmā. Å ie procesi tiks skarti arÄ« pārskatā: autovakuums, kontrolpunkts, ar replikāciju saistÄ«ti procesi, fona rakstÄ«tājs. Es pieskarÅ”os katram no tiem, ziņojot.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Kādas problēmas ir ar statistiku?

  • Informācijas ir ļoti daudz. PostgreSQL 9.4 nodroÅ”ina 109 metriku statistikas datu skatÄ«Å”anai. Taču, ja datu bāzē glabājas daudzas tabulas, shēmas, datu bāzes, tad visi Å”ie rādÄ«tāji bÅ«s jāreizina ar atbilstoÅ”o tabulu, datu bāzu skaitu. Tas ir, ir vēl vairāk informācijas. Un tajā ir ļoti viegli noslÄ«kt.
  • Nākamā problēma ir tā, ka statistiku attēlo skaitÄ«tāji. Ja mēs skatāmies uz Å”o statistiku, mēs redzēsim pastāvÄ«gi pieaugoÅ”us skaitÄ«tājus. Un, ja kopÅ” statistikas atiestatÄ«Å”anas ir pagājis daudz laika, mēs redzēsim vērtÄ«bas miljardos. Un viņi mums neko nestāsta.
  • Nav stāsta. Ja jums bija kāda neveiksme, kaut kas nokrita pirms 15ā€“30 minÅ«tēm, jÅ«s nevarēsit izmantot statistiku un redzēt, kas notika pirms 15ā€“30 minÅ«tēm. Tā ir problēma.
  • PostgreSQL iebÅ«vēta rÄ«ka trÅ«kums ir problēma. Kodola izstrādātāji nesniedz nekādu utilÄ«tu. Viņiem nekā tāda nav. Viņi vienkārÅ”i sniedz statistiku datu bāzē. Izmantojiet to, pieprasiet to, dariet visu, ko vēlaties.
  • Tā kā PostgreSQL nav iebÅ«vēts rÄ«ks, tas rada vēl vienu problēmu. Daudz treÅ”o puÅ”u rÄ«ku. Katrs uzņēmums, kuram ir vairāk vai mazāk tieÅ”as rokas, cenÅ”as uzrakstÄ«t savu programmu. Tā rezultātā kopienai ir daudz rÄ«ku, ko var izmantot, lai strādātu ar statistiku. Un dažiem rÄ«kiem ir noteiktas iespējas, citiem rÄ«kiem nav citu iespēju, vai arÄ« ir dažas jaunas iespējas. Un rodas situācija, ka jums ir jāizmanto divi, trÄ«s vai četri rÄ«ki, kas pārklājas viens ar otru un kuriem ir dažādas funkcijas. Tas ir ļoti nepatÄ«kami.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Kas no tā izriet? Ir svarÄ«gi, lai bÅ«tu iespēja tieÅ”i ņemt statistiku, lai nebÅ«tu atkarÄ«gs no programmām vai kaut kā pats uzlabotu Ŕīs programmas: pievienojiet dažas funkcijas, lai gÅ«tu labumu.

Un jums ir nepiecieŔamas pamatzināŔanas par SQL. Lai iegūtu dažus datus no statistikas, jums ir jāizveido SQL vaicājumi, t.i., jums jāzina, kā tiek apkopoti atlase un pievienoŔanās.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Statistika mums stāsta vairākas lietas. Tos var iedalīt kategorijās.

  • Pirmā kategorija ir notikumi, kas notiek datu bāzē. Tas ir tad, kad datu bāzē notiek kāds notikums: pieprasÄ«jums, piekļuve tabulai, autovakuums, apņemÅ”anās, tad tie visi ir notikumi. SkaitÄ«tāji, kas atbilst Å”iem notikumiem, tiek palielināti. Un mēs varam izsekot Å”iem notikumiem.
  • Otrā kategorija ir objektu, piemēram, tabulu un datu bāzu, Ä«paŔības. Viņiem ir Ä«paŔības. Tas ir tabulu izmērs. Mēs varam izsekot tabulu pieaugumam un indeksu pieaugumam. Mēs varam redzēt izmaiņas dinamikā.
  • Un treŔā kategorija ir pasākumā pavadÄ«tais laiks. PieprasÄ«jums ir notikums. Tam ir savs Ä«paÅ”ais ilguma mērs. Sākās Å”eit, beidzās Å”eit. Mēs varam izsekot. Laiks, kas nepiecieÅ”ams bloka nolasÄ«Å”anai no diska vai rakstÄ«Å”anai. Tādas lietas arÄ« tiek izsekotas.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Statistikas avoti ir parādīti Ŕādi:

  • Koplietojamā atmiņā (koplietojamā buferÄ«) ir segments statisku datu glabāŔanai, ir arÄ« tie skaitÄ«tāji, kas pastāvÄ«gi tiek palielināti, kad notiek noteikti notikumi vai rodas kādi momenti datu bāzes darbÄ«bā.
  • Visi Å”ie skaitÄ«tāji nav pieejami lietotājam un pat nav pieejami administratoram. Tās ir zema lÄ«meņa lietas. Lai tiem piekļūtu, PostgreSQL nodroÅ”ina saskarni SQL funkciju veidā. Mēs varam veikt atlasÄ«tus metienus, izmantojot Ŕīs funkcijas, un iegÅ«t sava veida metriku (vai metrikas kopu).
  • Tomēr Å”o funkciju izmantoÅ”ana ne vienmēr ir ērta, tāpēc funkcijas ir skatu (SKATÄ«jumu) pamatā. Tās ir virtuālās tabulas, kas nodroÅ”ina statistiku par konkrētu apakÅ”sistēmu vai noteiktu notikumu kopu datubāzē.
  • Å ie iegultie skati (SKATI) ir galvenā lietotāja saskarne darbam ar statistiku. Tie ir pieejami pēc noklusējuma bez papildu iestatÄ«jumiem, jÅ«s varat tos nekavējoties izmantot, apskatÄ«t un ņemt no tiem informāciju. Un tad ir devumi. IeguldÄ«jumi ir oficiāli. Var instalēt postgresql-contrib pakotni (piemēram, postgresql94-contrib), ielādēt vajadzÄ«go moduli konfigurācijā, norādÄ«t tam parametrus, restartēt PostgreSQL un var to izmantot. (PiezÄ«me. AtkarÄ«bā no izplatÄ«Å”anas jaunākajās versijās contrib pakotne ir daļa no galvenās pakotnes).
  • Un ir arÄ« neoficiāli ieguldÄ«jumi. Tie nav iekļauti standarta PostgreSQL izplatÄ«Å”anā. Tie ir jāapkopo vai jāinstalē kā bibliotēka. Iespējas var bÅ«t ļoti dažādas, atkarÄ«bā no tā, ko izdomājis Ŕī neoficiālā ieguldÄ«juma izstrādātājs.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Šajā slaidā ir parādīti visi SKATI un dažas funkcijas, kas ir pieejamas programmā PostgreSQL 9.4. Kā redzam, to ir ļoti daudz. Un ir diezgan viegli apjukt, ja ar to saskaraties pirmo reizi.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Tomēr, ja ņemam iepriekŔējo attēlu ŠšŠ°Šŗ трŠ°Ń‚Šøтся Š²Ń€ŠµŠ¼Ń Š½Š° PostgreSQL un saderÄ«gs ar Å”o sarakstu, mēs iegÅ«stam Å”o attēlu. Mēs varam izmantot katru skatu (SKATUS) vai katru funkciju vienam vai otram mērÄ·im, lai iegÅ«tu atbilstoÅ”o statistiku, kad darbojas PostgreSQL. Un mēs jau varam iegÅ«t kādu informāciju par apakÅ”sistēmas darbÄ«bu.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Pirmā lieta, ko mēs apskatÄ«sim, ir pg_stat_database. Kā redzam, Ŕī ir izrāde. Tajā ir daudz informācijas. Visdažādākā informācija. Un tas sniedz ļoti noderÄ«gas zināŔanas par to, kas notiek mÅ«su datubāzē.

Kādas noderÄ«gas lietas mēs varam paņemt no turienes? Sāksim ar visvienkārŔākajām lietām.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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

Pirmā lieta, ko mēs varam apskatÄ«t, ir keÅ”atmiņas trāpÄ«jumu procentuālais daudzums. KeÅ”atmiņas trāpÄ«jumu lÄ«menis ir noderÄ«gs rādÄ«tājs. Tas ļauj novērtēt, cik daudz datu tiek ņemts no koplietoto buferu keÅ”atmiņas un cik daudz tiek nolasÄ«ts no diska.

Tas ir skaidrs jo vairāk keÅ”atmiņas trāpÄ«jumu mums ir, jo labāk. Mēs mēra Å”o metriku procentos. Un, piemēram, ja mÅ«su Å”o keÅ”atmiņas trāpÄ«jumu procentuālā daļa ir lielāka par 90%, tad tas ir labi. Ja tas nokrÄ«tas zem 90%, tas nozÄ«mē, ka mums nav pietiekami daudz atmiņas, lai saglabātu atmiņā karsto datu galvu. Un, lai izmantotu Å”os datus, PostgreSQL ir spiests piekļūt diskam, un tas notiek lēnāk nekā tad, ja dati tiktu nolasÄ«ti no atmiņas. Un jums ir jādomā par atmiņas palielināŔanu: vai nu palielināt koplietojamo buferu skaitu, vai palielināt aparatÅ«ras atmiņu (RAM).

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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;

Ko vēl var paņemt no Ŕīs izrādes? JÅ«s varat redzēt datubāzē raduŔās anomālijas. Kas Å”eit ir parādÄ«ts? Pastāv apņemÅ”anās, atcelÅ”ana, pagaidu failu izveide, to lielums, strupceļi un konflikti.

Mēs varam izmantot Å”o pieprasÄ«jumu. Å Ä« SQL ir diezgan vienkārÅ”a. Un mēs varam apskatÄ«t Å”os datus Å”eit.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Un Å”eit ir sliekŔņa vērtÄ«bas. Mēs aplÅ«kojam saistÄ«bu un atcelÅ”anas attiecÄ«bu. SaistÄ«bas ir veiksmÄ«gs darÄ«juma apstiprinājums. AtcelÅ”ana ir atcelÅ”ana, t.i., darÄ«jums veica kādu darbu, noslogoja datu bāzi, kaut ko aprēķināja, un tad notika kļūme un darÄ«juma rezultāti tiek atmesti. Tas ir atcelÅ”anu skaits pastāvÄ«gi pieaug, ir slikti. Un jums vajadzētu kaut kā izvairÄ«ties no tiem un rediģēt kodu, lai tas nenotiktu.

Konflikti ir saistÄ«ti ar replikāciju. Un arÄ« no tiem vajadzētu izvairÄ«ties. Ja jums ir daži vaicājumi, kas tiek izpildÄ«ti replikā un rodas konflikti, jums ir jāatrisina Å”ie konflikti un jāredz, kas notiek. SÄ«kāka informācija atrodama žurnālos. Un novērsiet konfliktsituācijas, lai lietojumprogrammu pieprasÄ«jumi darbotos bez kļūdām.

ArÄ« strupceļi ir slikta situācija. Kad pieprasÄ«jumi cÄ«nās par resursiem, viens pieprasÄ«jums piekļuva vienam resursam un paņēma bloÄ·Ä“Å”anu, otrs pieprasÄ«jums piekļuva otrajam resursam un arÄ« bloķēja, un pēc tam abi pieprasÄ«jumi piekļuva viens otra resursiem un tika bloķēti, gaidot, kamēr kaimiņŔ atbrÄ«vos slēdzeni. ArÄ« Ŕī ir problemātiska situācija. Tie ir jārisina lietojumprogrammu pārrakstÄ«Å”anas un piekļuves resursiem seriālizācijas lÄ«menÄ«. Un, ja redzat, ka jÅ«su strupceļi nepārtraukti palielinās, jums ir jāaplÅ«ko žurnālos esoŔās detaļas, jāanalizē raduŔās situācijas un jānoskaidro, kāda ir problēma.

ArÄ« pagaidu faili (temp_files) ir slikti. Ja lietotāja pieprasÄ«jumam nav pietiekami daudz atmiņas, lai ievietotu operatÄ«vos, pagaidu datus, tas diskā izveido failu. Un visas darbÄ«bas, ko tas varētu veikt pagaidu buferÄ« atmiņā, sāk veikt diskā. Tas ir lēns. Tas palielina vaicājuma izpildes laiku. Un klients, kurÅ” nosÅ«tÄ«ja pieprasÄ«jumu PostgreSQL, saņems atbildi nedaudz vēlāk. Ja visas Ŕīs darbÄ«bas tiek veiktas atmiņā, Postgres reaģēs daudz ātrāk un klients gaidÄ«s mazāk.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Pg_stat_bgwriter ā€” Å”is skats apraksta divu PostgreSQL fona apakÅ”sistēmu darbÄ«bu: Å”is checkpointer Šø background writer.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Vispirms apskatÄ«sim kontrolpunktus, tā sauktos. checkpoints. Kas ir kontroles punkti? Kontrolpunkts ir pozÄ«cija darÄ«jumu žurnālā, kas norāda, ka visas žurnālā ierakstÄ«tās datu izmaiņas ir veiksmÄ«gi sinhronizētas ar diskā esoÅ”ajiem datiem. Process, atkarÄ«bā no darba slodzes un iestatÄ«jumiem, var bÅ«t ilgstoÅ”s un galvenokārt sastāv no netÄ«ro lapu sinhronizÄ“Å”anas koplietotajos buferos ar datu failiem diskā. Kam tas paredzēts? Ja PostgreSQL nepārtraukti piekļūtu diskam un ienestu no turienes datus un rakstÄ«tu datus par katru piekļuvi, tas bÅ«tu lēns. Tāpēc PostgreSQL ir atmiņas segments, kura lielums ir atkarÄ«gs no konfigurācijas iestatÄ«jumiem. Postgres saglabā reāllaika datus Å”ajā atmiņā vēlākai apstrādei vai vaicājumam. Datu maiņas pieprasÄ«jumu gadÄ«jumā tie tiek mainÄ«ti. Un mēs iegÅ«stam divas datu versijas. Viens ir mÅ«su atmiņā, otrs ir diskā. Un periodiski jums Å”ie dati ir jāsinhronizē. Tas, kas ir mainÄ«ts atmiņā, ir jāsinhronizē ar disku. Å im nolÅ«kam ir nepiecieÅ”ami kontrolpunkti.

Kontrolpunkts iet cauri koplietotajiem buferiem, atzīmē netīrās lapas, ka tās ir vajadzīgas kontrolpunktam. Pēc tam tas palaiž otru pāreju caur koplietotajiem buferiem. Un lapas, kas ir atzīmētas kontrolpunktam, tās jau sinhronizē. Tādā veidā dati tiek sinhronizēti ar disku.

Ir divu veidu kontrolpunkti. Viens kontrolpunkts tiek izpildÄ«ts ar taimautu. Å is kontrolpunkts ir noderÄ«gs un labs ā€“ checkpoint_timed. Un pēc pieprasÄ«juma ir kontrolpunkti - checkpoint required. Å is kontrolpunkts rodas, ja mums ir ļoti liels datu ieraksts. Mēs ierakstÄ«jām daudz darÄ«jumu žurnālu. Un PostgreSQL uzskata, ka tas viss ir jāsinhronizē pēc iespējas ātrāk, jāveic kontrolpunkts un jāturpina.

Un ja paskatÄ«tos statistiku pg_stat_bgwriter un ieraudzÄ«ju, kas tev ir checkpoint_req ir daudz lielāks par checkpoint_timed, tad tas ir slikti. Kāpēc slikti? Tas nozÄ«mē, ka PostgreSQL ir pakļauts pastāvÄ«gam stresam, kad tai ir nepiecieÅ”ams ierakstÄ«t datus diskā. Taimauta kontrolpunkts rada mazāku stresu un tiek veikts saskaņā ar iekŔējo grafiku un it kā tiek sadalÄ«ts laika gaitā. PostgreSQL ir iespēja apturēt darbu un nenoslogot diska apakÅ”sistēmu. Tas ir noderÄ«gi PostgreSQL. Un vaicājumi, kas tiek izpildÄ«ti kontrolpunkta laikā, neradÄ«s stresu, jo diska apakÅ”sistēma ir aizņemta.

Un, lai pielāgotu kontrolpunktu, ir trīs parametri:

  • сheckpoint_segments.

  • сheckpoint_timeout.

  • сheckpoint_competion_target.

Tie ļauj regulēt kontroles punktu darbÄ«bu. Bet es pie tiem nekavÄ“Å”os. To ietekme ir atseviŔķa tēma.

BrÄ«dinājums: Ziņojumā aplÅ«kotā versija 9.4 vairs nav aktuāla. MÅ«sdienu PostgreSQL versijās parametrs checkpoint_segments aizstāts ar parametriem min_wal_size Šø max_wal_size.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Nākamā apakÅ”sistēma ir fona rakstÄ«tājs āˆ’ background writer. Ko viņŔ dara? Tas pastāvÄ«gi darbojas bezgalÄ«gā cilpā. Skenē lapas koplietotajos buferos un netÄ«rās lapas, kuras tiek atrastas, izmet diskā. Tādējādi tas palÄ«dz kontrolpunktam veikt mazāk darba kontrolpunkta izpildes laikā.

PriekÅ” kam vēl tas vajadzÄ«gs? Tas nodroÅ”ina vajadzÄ«bu pēc tukŔām lapām koplietotajos buferos, ja tās pēkŔņi ir nepiecieÅ”amas (lielos daudzumos un nekavējoties), lai ievietotu datus. Pieņemsim, ka radās situācija, kad pieprasÄ«juma pabeigÅ”anai bija vajadzÄ«gas tukÅ”as lapas un tās jau atradās koplietotajos buferos. PostgresÄ«vs backend viņŔ tos vienkārÅ”i paņem un lieto, paÅ”am nekas nav jātÄ«ra. Bet, ja pēkŔņi Ŕādu lapu nav, aizmugursistēma aptur darbu un sāk meklēt lapas, lai tās izmestu diskā un ņemtu tās savām vajadzÄ«bām, kas negatÄ«vi ietekmē paÅ”laik izpildāmā pieprasÄ«juma laiku. Ja redzat, ka jums ir parametrs maxwritten_clean liels, tas nozÄ«mē, ka fona rakstÄ«tājs nedara savu darbu un jums ir jāpalielina parametri bgwriter_lru_maxpages, lai viņŔ vienā ciklā varētu paveikt vairāk darbu, notÄ«rÄ«t vairāk lapu.

Un vēl viens ļoti noderÄ«gs rādÄ«tājs ir buffers_backend_fsync. Aizmugursistēmas neveic fsync, jo tas ir lēns. Viņi iztur fsync augÅ”up IO steka kontrolpunktā. Kontrolpunktam ir sava rinda, tas periodiski apstrādā fsync un sinhronizē atmiņas lapas ar failiem diskā. Ja rinda pie kontrolpunkta ir liela un pilna, tad aizmugursistēma ir spiesta pati veikt fsync un tas palēnina aizmugursistēmas darbu, t.i., klients saņems atbildi vēlāk, nekā varētu. Ja redzat, ka jÅ«su vērtÄ«ba ir lielāka par nulli, tad tā jau ir problēma un jums jāpievērÅ” uzmanÄ«ba fona rakstÄ«Å”anas iestatÄ«jumiem un arÄ« jānovērtē diska apakÅ”sistēmas veiktspēja.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

BrÄ«dinājums: _Å ajā tekstā ir aprakstÄ«ti ar replikāciju saistÄ«tie statistikas skati. Lielākā daļa skatu un funkciju nosaukumu tika pārdēvēti programmā Postgres 10. PārdēvÄ“Å”anas bÅ«tÄ«ba bija aizstāt xlog par wal Šø location par lsn funkciju/skatu nosaukumos utt. Konkrēts piemērs, funkcija pg_xlog_location_diff() tika pārdēvēta par pg_wal_lsn_diff()._

Mums arī Ŕeit ir daudz lietu. Bet mums ir vajadzīgas tikai preces, kas saistītas ar atraŔanās vietu.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Ja mēs redzam, ka visas vērtības ir vienādas, tad tas ir ideāls variants, un kopija neatpaliek no meistara.

Å Ä« heksadecimālā pozÄ«cija Å”eit ir pozÄ«cija darÄ«jumu žurnālā. Tas pastāvÄ«gi palielinās, ja datu bāzē ir kādas darbÄ«bas: ievieto, dzÄ“Å” utt.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

сŠŗŠ¾Š»ŃŒŠŗŠ¾ Š·Š°ŠæŠøсŠ°Š½Š¾ 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());

Ja Ŕīs lietas atŔķiras, tad ir kaut kāda nobīde. Lag ir nobīde starp repliku un galveno, t.i., dati atŔķiras starp serveriem.

Ir trÄ«s kavÄ“Å”anās iemesli:

  • Å Ä« diska apakÅ”sistēma nevar tikt galā ar ieraksta failu sinhronizāciju.
  • Tās ir iespējamas tÄ«kla kļūdas vai tÄ«kla pārslodze, kad datiem nav laika sasniegt repliku un tā nevar to reproducēt.
  • Un procesors. Procesors ir ļoti rets gadÄ«jums. Un es to redzēju divas vai trÄ«s reizes, bet tas var arÄ« notikt.

Un Å”eit ir trÄ«s vaicājumi, kas ļauj mums izmantot statistiku. Mēs varam novērtēt, cik daudz esam ierakstÄ«juÅ”i darÄ«jumu žurnālā. Ir tāda funkcija pg_xlog_location_diff un mēs varam novērtēt replikācijas nobÄ«di baitos un sekundēs. Å im nolÅ«kam mēs izmantojam arÄ« vērtÄ«bu no Ŕī skata (SKATÄŖJUMI).

PiezÄ«me: _Pg_xlog_location vietāFunkcija diff() var izmantot atņemÅ”anas operatoru un atņemt vienu vietu no citas. Ērti.

Ir viens punkts ar nobÄ«di, kas ir sekundēs. Ja galvenajā datorā nav aktivitāŔu, darÄ«jums notika apmēram pirms 15 minÅ«tēm un darbÄ«bas nav, un, ja mēs skatāmies uz Å”o replikas aizkavi, mēs redzēsim 15 minÅ«Å”u aizkavi. To ir vērts atcerēties. Un tas var bÅ«t mulsinoÅ”i, kad skatāties Å”o nobÄ«di.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Pg_stat_all_tables ir vēl viens noderÄ«gs skats. Tas parāda statistiku tabulās. Kad mums datu bāzē ir tabulas, ar to notiek kāda darbÄ«ba, kādas darbÄ«bas, mēs varam iegÅ«t Å”o informāciju no Ŕī skata.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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;

Pirmā lieta, ko mēs varam apskatÄ«t, ir secÄ«gi skenējumi visā tabulā. Pats skaitlis pēc Ŕīm piespēlēm ne vienmēr ir slikts un nav rādÄ«tājs, ka mums kaut kas jādara.

Tomēr ir otrs rādÄ«tājs - seq_tup_read. Å is ir no secÄ«gās skenÄ“Å”anas atgriezto rindu skaits. Ja vidējais skaitlis pārsniedz 1, 000 10, 000 50, 000 100, tad tas jau ir rādÄ«tājs, ka varbÅ«t kaut kur jāveido indekss, lai vaicājumi bÅ«tu balstÄ«ti uz indeksu, vai arÄ« ir iespējams optimizēt vaicājumus, kas izmanto Ŕādu secÄ«gu skenÄ“Å”anu, lai ka tas nenotiek, bija.

VienkārÅ”s piemērs ā€“ teiksim pieprasÄ«jums ar lielu OFFSET un LIMIT izmaksām. Piemēram, tiek skenētas 100 000 tabulas rindas un pēc tam tiek ņemtas 50 000 vajadzÄ«gās rindas, un iepriekŔējās skenētās rindas tiek izmestas. ArÄ« Å”is ir slikts gadÄ«jums. Un Ŕādi vaicājumi ir jāoptimizē. Un Å”eit ir vienkārÅ”s SQL vaicājums, kurā varat to apskatÄ«t un novērtēt iegÅ«tos skaitļus.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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;

Izmantojot Å”o tabulu un izmantojot papildu funkcijas, var iegÅ«t arÄ« tabulu izmērus pg_total_relation_size(), pg_relation_size().

Kopumā ir metakomandas dt Šø di, ko var izmantot PSQL, kā arÄ« skatÄ«t tabulu un indeksu izmērus.

Tomēr funkciju izmantoÅ”ana palÄ«dz mums apskatÄ«t tabulu izmērus, pat ņemot vērā indeksus vai neņemot vērā indeksus, un jau veikt dažus aprēķinus, pamatojoties uz datu bāzes pieaugumu, t.i., kā tā aug, ar kādu intensitāti un izdarÄ«t dažus secinājumus par izmēru optimizāciju.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

DarbÄ«bas ierakstÄ«Å”ana. Kas ir ieraksts? ApskatÄ«sim operāciju UPDATE ā€“ tabulas rindu atjaunināŔanas darbÄ«ba. Faktiski atjaunināŔana ir divas darbÄ«bas (vai pat vairāk). Tas nozÄ«mē jaunas rindas versijas ievietoÅ”anu un rindas vecās versijas atzÄ«mÄ“Å”anu kā novecojuÅ”u. Pēc tam autovakuums ieradÄ«sies un iztÄ«rÄ«s Ŕīs novecojuŔās lÄ«niju versijas, atzÄ«mējot Å”o vietu kā pieejamu atkārtotai izmantoÅ”anai.

Turklāt atjaunināŔana nav tikai tabulas atjaunināŔana. Šis ir arī indeksa atjauninājums. Ja tabulā ir daudz indeksu, tad atjaunināŔanas laikā būs jāatjaunina arī visi indeksi, kas ietver vaicājumā atjauninātos laukus. Šiem indeksiem būs arī novecojuŔas rindu versijas, kuras būs jāiztīra.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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;

Un tā jaunā dizaina dēļ UPDATE ir smagsvara operācija. Bet tos var padarÄ«t vienkārŔākus. Ēst hot updates. Tie parādÄ«jās PostgreSQL versijā 8.3. Un kas tas ir? Å is ir viegls atjauninājums, kas neizraisa indeksu pārbÅ«vi. Tas ir, mēs atjauninājām ierakstu, bet tika atjaunināts tikai ieraksts lapā (kas pieder tabulai), un indeksi joprojām norāda uz to paÅ”u ierakstu lapā. Ir mazliet interesanta darbÄ«bas loÄ£ika: kad rodas vakuums, tas rada Ŕīs ķēdes hot pārbÅ«vē un viss turpina darboties bez indeksu atjaunināŔanas, un viss notiek ar mazāku resursu izŔķērdÄ“Å”anu.

Un kad tu n_tup_hot_upd liels, tad tas ir ļoti labi. Tas nozīmē, ka dominē vieglie atjauninājumi, un tas mums ir lētāks resursu ziņā, un viss ir kārtībā.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

ALTER TABLE table_name SET (fillfactor = 70);

Kā palielināt skaļumu hot updateov? Varam izmantot fillfactor. Tas nosaka rezervētās brÄ«vās vietas lielumu, aizpildot lapu tabulā, izmantojot INSERT. Kad tabulai pievieno ieliktņus, tie pilnÄ«bā aizpilda lapu un neatstāj tukÅ”u vietu. Pēc tam tiek iezÄ«mēta jauna lapa. Dati tiek aizpildÄ«ti vēlreiz. Un Ŕī ir noklusējuma darbÄ«ba, aizpildÄ«Å”anas koeficients = 100%.

Mēs varam padarÄ«t aizpildÄ«Å”anas koeficientu 70%. Tas ir, ievietoÅ”anas laikā tika izcelta jauna lapa, bet tikai 70% lapas tika aizpildÄ«tas. Un mums ir palikuÅ”i 30% rezervē. Kad jums ir nepiecieÅ”ams veikt atjauninājumu, tas, visticamāk, notiks tajā paŔā lapā, un jaunā lÄ«nijas versija ietilps tajā paŔā lapā. Un hot_update tiks darÄ«ts. Tas atvieglo rakstÄ«Å”anu uz tabulām.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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));

Autovakuuma rinda. Autovacuum ir apakÅ”sistēma, par kuru PostgreSQL ir ļoti maz statistikas datu. Tikai pg_stat_activity tabulās varam redzēt, cik vakuumu mums Å”obrÄ«d ir. Taču ir ļoti grÅ«ti saprast, cik tabulu uzreiz ir rindā.

PiezÄ«me: _Sākot ar Postgres 10, situācija ar Vatovac izsekoÅ”anu ir ievērojami uzlabojusies - ir parādÄ«jies pg_stat_progress skatsvakuums, kas ievērojami vienkārÅ”o jautājumu par automaŔīnas vakuuma uzraudzÄ«bu.

Mēs varam izmantot Å”o vienkārÅ”oto vaicājumu. Un mēs varam redzēt, kad vakuums bÅ«s jāveido. Bet kā un kad jāsāk vakuums? Å Ä«s ir to lÄ«niju mantotās versijas, par kurām es runāju iepriekÅ”. Notika atjauninājums, tika ievietota jauna lÄ«nijas versija. Ir parādÄ«jusies novecojusi virknes versija. Tabulā pg_stat_user_tables ir tāds parametrs n_dead_tup. Tas parāda "miruÅ”o" lÄ«niju skaitu. Un, tiklÄ«dz miruÅ”o rindu skaits kļūst lielāks par noteiktu slieksni, pie galda nonāks autovakuums.

Un kā Å”is slieksnis tiek aprēķināts? Tā ir ļoti konkrēta procentuālā daļa no kopējā tabulas rindu skaita. Ir parametrs autovacuum_vacuum_scale_factor. Tas nosaka procentuālo daudzumu. Teiksim, 10% + ir papildu pamata slieksnis 50 rindiņas. Un kas notiek? Kad mums ir vairāk nedzÄ«vu rindu nekā ā€œ10% + 50ā€ no visām tabulas rindām, tad tabulu liekam uz autovakuumu.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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));

Tomēr ir viens punkts. Parametru pamata sliekŔņi av_base_thresh Šø av_scale_factor var pieŔķirt individuāli. Un attiecÄ«gi tabulai slieksnis nebÅ«s globāls, bet gan individuāls. Tāpēc, lai aprēķinātu, jums ir jāizmanto triki un triki. Un, ja jÅ«s interesē, tad varat apskatÄ«t mÅ«su kolēģu pieredzi no Avito (slaidā esoŔā saite nav derÄ«ga un tekstā ir atjaunināta).

Viņi rakstÄ«ja priekÅ” munin spraudnis, kas ņem vērā Ŕīs lietas. Tur ir divu lapu kāju lupata. Bet tas aprēķina pareizi un diezgan efektÄ«vi ļauj mums novērtēt, kur mums ir nepiecieÅ”ams daudz vakuuma galdiem, kur ir maz.

Ko mēs varam darÄ«t lietas labā? Ja mums ir liela rinda un autovakuums netiek galā, mēs varam palielināt vakuuma darbinieku skaitu vai vienkārÅ”i padarÄ«t vakuumu agresÄ«vāku, lai tas iedarbinātu agrāk, apstrādā tabulu mazos gabaliņos. Un lÄ«dz ar to rinda samazināsies. ā€” Å eit galvenais ir uzraudzÄ«t disku slodzi, jo... vakuums nav bezmaksas lieta, lai gan lÄ«dz ar SSD/NVMe ierīču parādÄ«Å”anos problēma ir kļuvusi mazāk pamanāma.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Pg_stat_all_indexes ir indeksu statistika. Viņa nav liela. Un mēs to varam izmantot, lai iegÅ«tu informāciju par indeksu izmantoÅ”anu. Un, piemēram, mēs varam noteikt, kuri indeksi mums ir papildus.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Kā jau teicu, atjauninājums ir ne tikai tabulu, bet arÄ« indeksu atjauninājums. AttiecÄ«gi, ja tabulā ir daudz indeksu, tad, atjauninot tabulas rindas, ir jāatjaunina arÄ« indeksēto lauku indeksi, un ja mums ir neizmantoti indeksi, kuriem nav indeksu skenÄ“Å”anas, tad tie karājas kā balasts. Un mums ir jāatbrÄ«vojas no tiem. Å im nolÅ«kam mums ir nepiecieÅ”ams lauks idx_scan. Mēs vienkārÅ”i skatāmies uz indeksu skenÄ“Å”anas skaitu. Ja indeksiem ir nulle skenÄ“Å”ana salÄ«dzinoÅ”i ilgā statistikas glabāŔanas periodā (vismaz 2-3 nedēļas), tad visticamāk tie ir slikti indeksi, mums no tiem jāatbrÄ«vojas.

PiezÄ«me: Meklējot neizmantotos indeksus straumÄ“Å”anas replikācijas klasteru gadÄ«jumā, ir jāpārbauda visi klasteru mezgli, jo statistika nav globāla, un, ja indekss netiek izmantots uz galvenā, tad to var izmantot replikās (ja tur ir slodze).

Divas saites:

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

Šie ir sarežģītāki vaicājumu piemēri, kā meklēt neizmantotos indeksus.

Otrā saite ir diezgan interesants pieprasījums. Tur ir ļoti netriviāla loģika. Es iesaku to atsaucei.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Ko vēl ir vērts summēt, izmantojot indeksus?

  • Neizmantotie indeksi ir slikti.

  • Tie aizņem vietu.

  • Palēnināt atjaunināŔanas darbÄ«bas.

  • Papildus darbs vakuumam.

Ja mēs noņemsim neizmantotos indeksus, mēs tikai uzlabosim datubāzi.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Nākamā prezentācija ir pg_stat_activity. Šis ir lietderības analogs ps, tikai PostgreSQL. Ja psPēc tam apskatiet procesus operētājsistēmā pg_stat_activity Tas parādīs darbību PostgreSQL.

Kādas noderīgas lietas mēs varam paņemt no turienes?

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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

Mēs varam redzēt kopējo aktivitāti, notiekoÅ”o datubāzē. Mēs varam veikt jaunu izvietoÅ”anu. Å eit viss ir uzsprādzis, jaunus savienojumus nepieņem, aplikācijā birst kļūdas.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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

Mēs varam izpildÄ«t Ŕādu vaicājumu un redzēt savienojumu kopējo procentuālo daudzumu attiecÄ«bā pret maksimālo savienojuma ierobežojumu un redzēt, kam ir visvairāk savienojumu. Un Å”ajā gadÄ«jumā mēs redzam Å”o lietotāju cron_role atvērti 508 savienojumi. Un tur ar viņu kaut kas notika. Mums ar to jātiek galā un jāskatās. Un tas ir pilnÄ«gi iespējams, ka tas ir kaut kāds anomāls savienojumu skaits.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Ja mums ir OLTP darba slodze, vaicājumiem jābÅ«t ātriem, ļoti ātriem un nevajadzētu bÅ«t gariem vaicājumiem. Taču, ja rodas gari vaicājumi, tad Ä«stermiņā nav par ko uztraukties, bet Ilgtermiņā gari vaicājumi kaitē datu bāzei; tie palielina tabulu uzpÅ«Å”anos, kad notiek tabulas sadrumstalotÄ«ba. JāatbrÄ«vojas gan no uzpÅ«Å”anās, gan gariem vaicājumiem.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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;

LÅ«dzu, ņemiet vērā: ar Å”o pieprasÄ«jumu mēs varam identificēt garus vaicājumus un darÄ«jumus. Mēs izmantojam funkciju clock_timestamp() lai noteiktu darbÄ«bas laiku. Garus vaicājumus, kurus atradām, varam atcerēties, izpildÄ«t explain, paskaties plānus un kaut kā optimizē. Mēs atvairojam paÅ”reizējos garos pieprasÄ«jumus un turpinām savu dzÄ«vi.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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

Slikti darījumi ir darījumi, kas atrodas dīkstāvē transakcijas laikā un dīkstāvē darījuma (pārtrauktā) stāvoklī.

Ko tas nozÄ«mē? DarÄ«jumiem ir vairāki stāvokļi. Un kādu no Å”iem stāvokļiem var pieņemt jebkurā laikā. Ir lauks stāvokļu definÄ“Å”anai state Å”ajā prezentācijā. Un mēs to izmantojam, lai noteiktu stāvokli.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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

Un, kā jau teicu iepriekÅ”, Å”ie divi stāvokļi dÄ«kstāve darÄ«jumā un dÄ«kstāve darÄ«jumā (pārtraukts) ir slikti. Kas tas ir? Å ajā laikā lietojumprogramma atvēra darÄ«jumu, veica dažas darbÄ«bas un sāka darboties. DarÄ«jums paliek atvērts. Tas uzkaras, tajā nekas nenotiek, tas aizņem savienojumu, bloķējas uz mainÄ«tajām rindām un, iespējams, palielina citu tabulu uzpÅ«Å”anos Postrges transakciju dzinēja arhitektÅ«ras dēļ. Un tādus darÄ«jumus arÄ« vajadzētu noÅ”aut, jo tie vispār ir kaitÄ«gi, jebkurā gadÄ«jumā.

Ja redzat, ka jūsu datubāzē ir vairāk nekā 5-10-20 no tiem, tad jums ir jāuztraucas un jāsāk ar tiem kaut ko darīt.

Å eit mēs izmantojam arÄ« laika aprēķināŔanai clock_timestamp(). Mēs uzņemam darÄ«jumus un optimizējam lietojumprogrammu.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Kā jau teicu iepriekÅ”, bloÄ·Ä“Å”ana ir tad, kad divi vai vairāki darÄ«jumi cÄ«nās par vienu vai resursu grupu. Å im nolÅ«kam mums ir lauks waiting ar BÅ«la vērtÄ«bu true vai false.

Tiesa ā€“ tas nozÄ«mē, ka process gaida, kaut kas ir jādara. Kad process gaida, tas nozÄ«mē, ka gaida arÄ« klients, kurÅ” uzsāka Å”o procesu. Klients sēž pārlÅ«kprogrammā un arÄ« gaida.

BrÄ«dinājums: _Sākot no Postgres versijas 9.6 lauka waiting noņemts un tā vietā pievienoti vēl divi informatÄ«vie lauki wait_event_type Šø wait_event._

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Ko darÄ«t? Ja jÅ«s ilgu laiku redzat patiesÄ«bu, tas nozÄ«mē, ka jums ir jāatbrÄ«vojas no Ŕādiem pieprasÄ«jumiem. Tādus darÄ«jumus mēs vienkārÅ”i noÅ”aujam. Mēs rakstām izstrādātājiem, ka viņiem kaut kā jāoptimizē, lai nebÅ«tu sacensÄ«bu par resursiem. Un tad izstrādātāji optimizē lietojumprogrammu, lai tas nenotiktu.

Un galējais, bet potenciāli neletāls gadÄ«jums ir strupceļu raÅ”anās. Divas transakcijas atjaunināja divus resursus, pēc tam tiem atkal piekļuva, Å”oreiz pretējiem resursiem. Å ajā gadÄ«jumā PostgreSQL nogalina paÅ”u darÄ«jumu, lai cits varētu turpināt darbu. Å Ä« ir strupceļa situācija, un viņa pati to nevar izdomāt. Tāpēc PostgreSQL ir spiests veikt ārkārtējus pasākumus.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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/

Un Å”eit ir divi vaicājumi, kas ļauj izsekot bloÄ·Ä“Å”anai. Mēs izmantojam skatu pg_locks, kas ļauj izsekot smagām slēdzenēm.

Un pirmā saite ir pats pieprasījuma teksts. Tas ir diezgan garŔ.

Un otrā saite ir raksts par slēdzenēm. Ir noderīgi lasīt, tas ir ļoti interesanti.

Tātad, ko mēs redzam? Mēs redzam divus pieprasÄ«jumus. DarÄ«jums ar ALTER TABLE ir bloķējoÅ”s darÄ«jums. Tas sākās, bet nepabeidza, un lietojumprogramma, kas ierakstÄ«ja Å”o darÄ«jumu, kaut kur veic citas darbÄ«bas. Un otrais pieprasÄ«jums ir atjaunināt. ViņŔ gaida, lÄ«dz beigsies alter table, pirms viņŔ var turpināt darbu.

Tādā veidā mēs varam noskaidrot, kurÅ” kuru aizslēdza, kuru tur, un varam tikt galā ar to tālāk.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Nākamais modulis ir pg_stat_statements. Kā jau teicu, Å”is ir modulis. Lai to izmantotu, jāielādē tā bibliotēka konfigurācijā, jārestartē PostgreSQL, jāinstalē modulis (ar vienu komandu) un tad mums bÅ«s jauns skats.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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;

Ko mēs no turienes varam paņemt? Ja mēs runājam par vienkārŔām lietām, mēs varam ņemt vidējo vaicājuma izpildes laiku. Laiks aug, kas nozÄ«mē, ka PostgreSQL reaģē lēni, un mums ir kaut kas jādara.

Mēs varam apskatÄ«t aktÄ«vākos ierakstÄ«Å”anas darÄ«jumus datu bāzē, kas maina datus koplietotajos buferos. Skatiet, kas tur atjaunina vai dzÄ“Å” datus.

Un mēs varam vienkārÅ”i apskatÄ«t dažādu statistiku Å”iem pieprasÄ«jumiem.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

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

Mēs pg_stat_statements Mēs to izmantojam, lai izveidotu pārskatus. Mēs atiestatām statistiku reizi dienā. Uzkrāsim to. Pirms statistikas atiestatÄ«Å”anas nākamreiz, izveidosim pārskatu. Å eit ir saite uz ziņojumu. JÅ«s varat to noskatÄ«ties.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Ko mēs darām? Mēs aprēķinām vispārÄ«gu statistiku visiem pieprasÄ«jumiem. Pēc tam katram pieprasÄ«jumam mēs uzskaitām tā individuālo ieguldÄ«jumu Å”ajā kopējā statistikā.

Un ko mēs varam skatÄ«ties? Mēs varam aplÅ«kot visu noteikta veida pieprasÄ«jumu kopējo izpildes laiku, ņemot vērā visus citus pieprasÄ«jumus. Mēs varam aplÅ«kot CPU un I/O resursu izmantoÅ”anu attiecÄ«bā pret kopējo attēlu. Un jau optimizējiet Å”os vaicājumus. Mēs veidojam populārākos vaicājumus, pamatojoties uz Å”o pārskatu, un jau tagad ir viela pārdomām par to, ko optimizēt.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Ko mēs esam atstājuÅ”i aizkulisēs? Vēl ir palikuÅ”i daži iesniegumi, kurus neizskatÄ«ju, jo laiks ir ierobežots.

Ir pgstattuple ir arī papildu modulis no standarta ieguldījumu paketes. Tas ļauj novērtēt bloat galdi, ts tabulas sadrumstalotība. Un, ja ir daudz sadrumstalotības, jums tas ir jānoņem un jāizmanto dažādi rīki. Un funkcija pgstattuple darbojas ilgu laiku. Un jo vairāk tabulu būs, jo ilgāk tas darbosies.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

Nākamais ieguldÄ«jums ir pg_buffercache. Tas ļauj pārbaudÄ«t koplietotos buferus: cik intensÄ«vi un kādām tabulām tiek izmantotas bufera lapas. Un tas vienkārÅ”i ļauj ieskatÄ«ties koplietotajos buferos un novērtēt tur notiekoÅ”o.

Nākamais modulis ir pgfincore. Tas ļauj veikt zema lÄ«meņa tabulas darbÄ«bas, izmantojot sistēmas zvanu mincore(), t.i., tas ļauj ielādēt tabulu koplietotajos buferos vai izlādēt to. Un tas cita starpā ļauj pārbaudÄ«t operētājsistēmas lapas keÅ”atmiņu, t.i., cik daudz vietas tabula aizņem lapas keÅ”atmiņā, koplietotajos buferos, un vienkārÅ”i ļauj novērtēt tabulas darba slodzi.

Nākamais modulis - pg_stat_kcache. Tas izmanto arÄ« sistēmas zvanu getrusage(). Un tas to izpilda pirms un pēc pieprasÄ«juma izpildes. Un iegÅ«tajā statistikā tas ļauj mums novērtēt, cik daudz mÅ«su pieprasÄ«jums iztērēja diska I/O, t.i., operācijām ar failu sistēmu, un aplÅ«ko procesora lietojumu. Tomēr modulis ir jauns (klepus klepus) un tā darbÄ«bai ir nepiecieÅ”ama PostgreSQL 9.4 un pg_stat_statements, par kuriem es minēju iepriekÅ”.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

  • Zināt, kā izmantot statistiku, ir noderÄ«gi. Jums nav nepiecieÅ”amas treÅ”o puÅ”u programmas. JÅ«s varat ienākt, redzēt, kaut ko darÄ«t, kaut ko paveikt.

  • Statistikas izmantoÅ”ana nav grÅ«ta, tā ir tikai parasta SQL. JÅ«s savācāt pieprasÄ«jumu, apkopojāt to, nosÅ«tÄ«jāt, apskatÄ«jāt.

  • Statistika palÄ«dz atbildēt uz jautājumiem. Ja jums ir jautājumi, pievērsieties statistikai - skatieties, izdariet secinājumus, analizējiet rezultātus.

  • Un eksperimentēt. Ir daudz pieprasÄ«jumu, daudz datu. JÅ«s vienmēr varat optimizēt esoÅ”u vaicājumu. Varat izveidot savu pieprasÄ«juma versiju, kas jums ir vairāk piemērota nekā oriÄ£ināls, un izmantot to.

Iedziļinieties PostgreSQL iekŔējā statistikā. Aleksejs Ä»esovskis

atsauces

Piemērotas saites, kas tika atrastas rakstā, pamatojoties uz materiāliem, bija ziņojumā.

Autors raksti vairāk
https://dataegret.com/news-blog (eng)

Statistikas apkopotājs
https://www.postgresql.org/docs/current/monitoring-stats.html

Sistēmas administrÄ“Å”anas funkcijas
https://www.postgresql.org/docs/current/functions-admin.html

Ieguldījumu moduļi
https://www.postgresql.org/docs/current/pgstatstatements.html
https://www.postgresql.org/docs/current/pgstattuple.html
https://www.postgresql.org/docs/current/pgbuffercache.html
https://github.com/klando/pgfincore
https://github.com/dalibo/pg_stat_kcache

SQL utilīti un sql kodu piemēri
https://github.com/dataegret/pg-utils

Paldies visiem par uzmanību!

Avots: www.habr.com

Pievieno komentāru