{"id":56075,"date":"2020-02-04T00:00:00","date_gmt":"2020-02-03T21:00:00","guid":{"rendered":"https:\/\/prohoster.info\/blog\/blog_prohoster\/osnovy-monitoringa-postgresql-aleksej-lesovskij"},"modified":"2020-02-18T14:04:16","modified_gmt":"2020-02-18T11:04:16","slug":"osnovy-monitoringa-postgresql-aleksej-lesovskij","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/osnovy-monitoringa-postgresql-aleksej-lesovskij","title":{"rendered":"Bazat e monitorimit PostgreSQL. Alexey Lesovski","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Ju ofroj t\u00eb njihni interpretimin e referatit t\u00eb Alexey Lesovski nga Data Egret \"T\u00eb Dh\u00ebnat Baz\u00eb p\u00ebr Monitorimin e PostgreSQL\"<\/strong><\/p>\n

<\/p>\n

N\u00eb k\u00ebt\u00eb referat, Alexey Lesovski do t\u00eb flas\u00eb p\u00ebr pikat ky\u00e7e t\u00eb statistik\u00ebs s\u00eb PostgreSQL, \u00e7far\u00eb do t\u00eb thon\u00eb ato dhe pse duhet t\u00eb jen\u00eb t\u00eb pranishme n\u00eb monitorim; p\u00ebr grafik\u00ebt q\u00eb duhet t\u00eb jen\u00eb n\u00eb monitorim, si t'i shtojm\u00eb ata dhe si t'i interpretojm\u00eb. Referati do t\u00eb jet\u00eb i dobish\u00ebm p\u00ebr administrator\u00ebt e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave, administrator\u00ebt e sistemeve dhe zhvilluesit q\u00eb jan\u00eb t\u00eb interesuar n\u00eb zgjidhjen e problemeve t\u00eb PostgreSQL.<\/p>\n

\n

\r\n \"Luaj\r\n
<\/div>\r\n <\/div><\/center><\/a><\/noindex><\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Un\u00eb quhem Alexei Lesovski, p\u00ebrfaq\u00ebsoj kompanin\u00eb Data Egret. <\/p>\n

<\/p>\n

Disa fjal\u00eb p\u00ebr veten. Kam filluar nj\u00eb her\u00eb e nj\u00eb koh\u00eb si administrator sistemi. <\/p>\n

<\/p>\n

Kam administruar t\u00eb ndryshme Linux, kam b\u00ebr\u00eb gj\u00ebra t\u00eb ndryshme q\u00eb lidhen me Linux, pra virtualizim, monitorim, kam punuar me proxy etj. Por n\u00eb nj\u00eb moment, fillova t\u00eb merrem m\u00eb shum\u00eb me bazat e t\u00eb dh\u00ebnave, PostgreSQL. M\u00eb p\u00eblqeu shum\u00eb. Dhe n\u00eb nj\u00eb moment, fillova t\u00eb merrem me PostgreSQL pjes\u00ebn m\u00eb t\u00eb madhe t\u00eb koh\u00ebs sime t\u00eb pun\u00ebs. K\u00ebshtu gradualisht u b\u00ebra PostgreSQL DBA.<\/p>\n

<\/p>\n

Dhe gjat\u00eb gjith\u00eb karrier\u00ebs time kam qen\u00eb gjithmon\u00eb i interesuar n\u00eb temat e statistikave, monitorimit, marr\u00ebjes s\u00eb telemetris\u00eb. Dhe kur isha administrator sistemi, meresha shum\u00eb me Zabbix. Kam shkruar nj\u00eb grup t\u00eb vog\u00ebl skriptesh si zabbix-extensions<\/a><\/noindex>. Ai ishte mjaft popullor n\u00eb koh\u00ebn e tij. Dhe atje mund t\u00eb monitoroheshin shum\u00eb gj\u00ebra t\u00eb r\u00ebnd\u00ebsishme, jo vet\u00ebm Linuxi, por gjithashtu komponente t\u00eb ndryshme.<\/p>\n

<\/p>\n

Tani merem me PostgreSQL. Po shkruaj nj\u00eb gj\u00eb tjet\u00ebr q\u00eb lejon pun\u00ebn me statistik\u00ebn e PostgreSQL. Ajo quhet pgCenter<\/a><\/noindex> (artikulli n\u00eb habr \u2014 Statistika e PostgreSQL pa nerva dhe stres<\/a><\/noindex>). <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Nj\u00eb hyrje e vog\u00ebl. Cilat jan\u00eb situatat e ndryshme me t\u00eb cilat p\u00ebrballen klient\u00ebt tan\u00eb? Ka ndodhur ndonj\u00eb fatkeq\u00ebsi q\u00eb lidhet me baz\u00ebn e t\u00eb dh\u00ebnave. Dhe kur e rikthejm\u00eb baz\u00ebn e t\u00eb dh\u00ebnave, shefi i departamentit ose shefi i zhvillimit thot\u00eb: \"Shok\u00eb, duhet ta monitorojm\u00eb baz\u00ebn e t\u00eb dh\u00ebnave, sepse ndodhi di\u00e7ka e keqe dhe duhet q\u00eb n\u00eb t\u00eb ardhmen t\u00eb mos ndodhi p\u00ebrs\u00ebri\". K\u00ebtu fillon nj\u00eb proces interesant i zgjedhjes s\u00eb sistemit t\u00eb monitorimit ose p\u00ebrshtatjes s\u00eb nj\u00eb sistemi ekzistues t\u00eb monitorimit q\u00eb t\u00eb mund t\u00eb monitoronim baz\u00ebn ton\u00eb t\u00eb t\u00eb dh\u00ebnave \u2013 PostgreSQL, MySQL ose ndonj\u00eb tjet\u00ebr. Koleg\u00ebt fillojn\u00eb t\u00eb rekomandojn\u00eb: \"Kam d\u00ebgjuar se ka nj\u00eb baz\u00eb t\u00eb till\u00eb t\u00eb t\u00eb dh\u00ebnave. Le t\u00eb p\u00ebrdorim at\u00eb\". Koleg\u00ebt fillojn\u00eb t\u00eb diskutojn\u00eb mes tyre. Dhe p\u00ebrfundimisht p\u00ebrfundojm\u00eb duke zgjedhur nj\u00eb baz\u00eb t\u00eb dh\u00ebnash, por monitorimi i PostgreSQL n\u00eb t\u00eb \u00ebsht\u00eb mjaft i zbeht\u00eb dhe gjithmon\u00eb duhet t\u00eb b\u00ebjm\u00eb disa rregullime. T\u00eb merremi me disa repozita nga GitHub, t'i klonojm\u00eb, t\u00eb p\u00ebrshtatim skriptet, t\u00eb rregullojm\u00eb disi. Dhe p\u00ebrfundimisht kjo rezulton n\u00eb pun\u00eb manuale. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Prandaj n\u00eb k\u00ebt\u00eb raport do t\u00eb p\u00ebrpiqem t\u00eb ofroj disa njohuri mbi se si t\u00eb zgjidhni monitorim jo vet\u00ebm p\u00ebr PostgreSQL, por edhe p\u00ebr bazat e t\u00eb dh\u00ebnave. Dhe t'u jap ato njohuri q\u00eb do t'ju lejojn\u00eb t\u00eb p\u00ebrmir\u00ebsoni monitorimin tuaj, q\u00eb t\u00eb merrni ndihm\u00eb prej tij, q\u00eb t\u00eb mund t\u00eb monitoroni baz\u00ebn tuaj t\u00eb t\u00eb dh\u00ebnave me dobi, p\u00ebr t\u00eb parashikuar n\u00eb koh\u00eb situatat emergjente q\u00eb mund t\u00eb ndodhin. <\/p>\n

<\/p>\n

Dhe idet\u00eb q\u00eb do t\u00eb jen\u00eb n\u00eb k\u00ebt\u00eb raport, mund t\u00eb adaptohen drejtp\u00ebrdrejt p\u00ebr \u00e7do baz\u00eb t\u00eb dh\u00ebnash, qoft\u00eb kjo nj\u00eb DBMS apo noSQL. Prandaj, k\u00ebtu nuk \u00ebsht\u00eb vet\u00ebm PostgreSQL, por ka shum\u00eb receta se si ta b\u00ebni k\u00ebt\u00eb n\u00eb PostgreSQL. Do t\u00eb ket\u00eb shembuj pyetjesh, shembuj entitetesh q\u00eb jan\u00eb n\u00eb PostgreSQL p\u00ebr monitorim. Dhe n\u00ebse DBMS juaj ka gj\u00ebra t\u00eb ngjashme q\u00eb lejojn\u00eb t\u00eb futen n\u00eb monitorim, ju gjithashtu mund t'i adaptoni, t\u00eb shtoni dhe do t\u00eb jet\u00eb mir\u00eb.<\/p>\n

<\/p>\n

\"BazatN\u00eb raportin nuk do t\u00eb
\nflas p\u00ebr se si t\u00eb dor\u00ebzojm\u00eb dhe ruajm\u00eb metrikat. Nuk do t\u00eb flas asgj\u00eb p\u00ebr pas-p\u00ebrpunimin e t\u00eb dh\u00ebnave dhe ofrimin e tyre p\u00ebr p\u00ebrdoruesin. Dhe nuk do t\u00eb flas asgj\u00eb p\u00ebr alarmin.
\nPor gjat\u00eb rr\u00ebfimit do t\u00eb tregoj disa screenshot-e t\u00eb monitorimeve ekzistuese, do t'i kritikoj disi. Por megjithat\u00eb do p\u00ebrpiqem t\u00eb mos p\u00ebrmend markat, q\u00eb t\u00eb mos krijoj reklam\u00eb ose anti-reklam\u00eb p\u00ebr k\u00ebto produkte. Prandaj, t\u00eb gjitha p\u00ebrputhjet jan\u00eb rast\u00ebsi dhe mbeten n\u00eb imagjinat\u00ebn tuaj.
\n\"Bazat
\nN\u00eb fillim, le t\u00eb kuptojm\u00eb se \u00e7far\u00eb \u00ebsht\u00eb monitorimi. Monitorimi \u00ebsht\u00eb nj\u00eb gj\u00eb shum\u00eb e r\u00ebnd\u00ebsishme q\u00eb duhet t\u00eb kemi. T\u00eb gjith\u00eb e kuptojn\u00eb k\u00ebt\u00eb. Por n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb monitorimi nuk i p\u00ebrket produktit t\u00eb biznesit dhe nuk ndikon drejtp\u00ebrdrejt n\u00eb fitimin e kompanis\u00eb, prandaj monitorimit gjithmon\u00eb i kushtohet koh\u00eb si nj\u00eb pun\u00eb e mbetur. N\u00ebse kemi koh\u00eb, at\u00ebher\u00eb merremi me monitorimin, n\u00ebse nuk kemi koh\u00eb, at\u00ebher\u00eb OK, e vendosim n\u00eb backlog dhe ndonj\u00ebher\u00eb do t\u00eb kthehemi n\u00eb k\u00ebto detyra. <\/p>\n

<\/p>\n

Pra situat\u00ebn ton\u00eb, kur shkojm\u00eb te klient\u00ebt, monitorimi shpesh \u00ebsht\u00eb i pap\u00ebrfunduar dhe nuk ka asgj\u00eb interesante q\u00eb ndihmon n\u00eb p\u00ebrmir\u00ebsimin e pun\u00ebs me baz\u00ebn e t\u00eb dh\u00ebnave. Prandaj, monitorimi gjithmon\u00eb ka nevoj\u00eb p\u00ebr p\u00ebrmir\u00ebsim. <\/p>\n

<\/p>\n

Baza e t\u00eb dh\u00ebnave \u00ebsht\u00eb nj\u00eb sistem kompleks q\u00eb gjithashtu ka nevoj\u00eb p\u00ebr monitorim, pasi ato jan\u00eb magazina informacioni. Informacioni \u00ebsht\u00eb shum\u00eb i r\u00ebnd\u00ebsish\u00ebm p\u00ebr kompanin\u00eb, ai nuk duhet humbur aspak. Megjithat\u00eb, bazat e t\u00eb dh\u00ebnave jan\u00eb pjes\u00eb shum\u00eb komplekse software-i, q\u00eb p\u00ebrb\u00ebhen nga shum\u00eb komponent\u00eb. Shumica e k\u00ebtyre komponent\u00ebve duhet monitoruar. <\/p>\n

<\/p>\n

\"BazatKur flasim p\u00ebr PostgreSQL, mund ta imagjinojm\u00eb si nj\u00eb skem\u00eb p\u00ebrb\u00ebr\u00eb nga shum\u00eb komponent\u00eb. K\u00ebta komponent\u00eb nd\u00ebrveprojn\u00eb me nj\u00ebri-tjetrin. Po ashtu, PostgreSQL ka nj\u00eb n\u00ebn-sistem t\u00eb quajtur Stats Collector, q\u00eb mund\u00ebson mbledhjen e statistikave mbi k\u00ebto sisteme dhe ofron nj\u00eb nd\u00ebrfaqe p\u00ebr administratorin ose p\u00ebrdoruesin p\u00ebr t\u00eb par\u00eb k\u00ebto statistika. <\/p>\n

<\/p>\n

K\u00ebto statistik\u00eb paraqiten si nj\u00eb grup funksionesh dhe pamjesh (view). Mund t'i referohen edhe si tabela. K\u00ebshtu, me klientin e zakonsh\u00ebm psql, mund t\u00eb lidhesh me baz\u00ebn e t\u00eb dh\u00ebnave, t\u00eb blesh k\u00ebto funksione dhe pamje dhe t\u00eb marr\u00ebsh disa numra konkret\u00eb p\u00ebr funksionimin e n\u00ebn-sistemeve t\u00eb PostgreSQL. <\/p>\n

<\/p>\n

Mund t'i shtosh k\u00ebta numra n\u00eb sistemin t\u00ebnd t\u00eb preferuar t\u00eb monitorimit, t\u00eb vizatosh grafika, t\u00eb shtosh funksione dhe t\u00eb marr\u00ebsh analiza afat-gjate. <\/p>\n

<\/p>\n

Por n\u00eb k\u00ebt\u00eb paraqitje, nuk do t\u00eb shqyrtoj t\u00eb gjitha k\u00ebto funksione, sepse do t\u00eb merrte nj\u00eb dit\u00eb t\u00eb t\u00ebr\u00eb. Do t\u00eb p\u00ebrqendrohem vet\u00ebm n\u00eb dy-tre-kat\u00ebr gj\u00ebra dhe do t\u00eb tregoj si ato ndihmojn\u00eb p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar monitorimin.
\n\"Bazat
\nDhe n\u00ebse flasim p\u00ebr monitorimin e baz\u00ebs, \u00e7far\u00eb duhet t\u00eb monitorojm\u00eb? S\u00eb pari dhe m\u00eb kryesorja \u00ebsht\u00eb t\u00eb monitorojm\u00eb disponueshm\u00ebrin\u00eb, sepse baza \u00ebsht\u00eb nj\u00eb sh\u00ebrbim q\u00eb ofron akses n\u00eb t\u00eb dh\u00ebna p\u00ebr klient\u00ebt dhe ne duhet t\u00eb monitorojm\u00eb disponueshm\u00ebrin\u00eb, si dhe disa karakteristika t\u00eb saj cil\u00ebsore dhe sasiore. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Gjithashtu, duhet t\u00eb monitorojm\u00eb klient\u00ebt q\u00eb lidhen me baz\u00ebn ton\u00eb, sepse ata mund t\u00eb jen\u00eb klient\u00eb normal\u00eb, por ndonj\u00ebher\u00eb edhe klient\u00eb keqdash\u00ebs q\u00eb mund t\u00eb d\u00ebmtojn\u00eb baz\u00ebn e t\u00eb dh\u00ebnave. Po ashtu, duhet t'i monitorojm\u00eb dhe t\u00eb ndjekim aktivitetin e tyre.<\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Kur klient\u00ebt lidhen me baz\u00ebn e t\u00eb dh\u00ebnave, \u00ebsht\u00eb e qart\u00eb se ata fillojn\u00eb t\u00eb punojn\u00eb me t\u00eb dh\u00ebnat tona, k\u00ebshtu q\u00eb duhet t\u00eb monitorojm\u00eb si punojn\u00eb klient\u00ebt me t\u00eb dh\u00ebnat: me cilat tabela, dhe m\u00eb pak me cilat indekse. Pra, duhet t\u00eb vler\u00ebsojm\u00eb ngarkes\u00ebn (workload) q\u00eb krijohet nga klient\u00ebt tan\u00eb.<\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Por ngarkesa p\u00ebrb\u00ebhet sigurisht nga k\u00ebrkesat. Aplikacionet lidhen me baz\u00ebn, i qasen t\u00eb dh\u00ebnave p\u00ebrmes k\u00ebrkesave, prandaj \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsojm\u00eb se cilat k\u00ebrkesa kemi n\u00eb baz\u00ebn ton\u00eb, t\u00eb ndjekim adekuat\u00ebsin\u00eb e tyre, q\u00eb ato t\u00eb mos jen\u00eb t\u00eb shkruara keq, dhe q\u00eb disa opsione duhet t\u00eb p\u00ebrmir\u00ebsohen p\u00ebr t\u00eb punuar m\u00eb shpejt dhe me m\u00eb shum\u00eb efikasitet. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Dhe kur flasim p\u00ebr bazat e t\u00eb dh\u00ebnave, ato gjithmon\u00eb kan\u00eb procese t\u00eb prapme. Proceset e prapme ndihmojn\u00eb n\u00eb mbajtjen e performanc\u00ebs s\u00eb baz\u00ebs n\u00eb nj\u00eb nivel t\u00eb mir\u00eb, k\u00ebshtu q\u00eb p\u00ebr funksionimin e tyre k\u00ebrkojn\u00eb nj\u00eb sasi t\u00eb caktuar burimesh p\u00ebr vete. N\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb, ato mund t\u00eb konkurrojn\u00eb me burimet e k\u00ebrkesave t\u00eb klient\u00ebve, k\u00ebshtu q\u00eb punimi k\u00ebrkues i proceseve t\u00eb prapme mund t\u00eb ndikoj\u00eb drejtp\u00ebrdrejt n\u00eb performanc\u00ebn e k\u00ebrkesave t\u00eb klient\u00ebve. Prandaj, ato gjithashtu duhet t\u00eb monitorohen dhe t\u00eb sigurohemi se nuk ka asnj\u00eb anomali n\u00eb proceset e prapme. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Dhe gjith\u00e7ka q\u00eb lidhet me monitorimin e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave mbetet n\u00eb metrikat sistemike. Nd\u00ebrsa marrim parasysh se infrastruktura jon\u00eb po migron n\u00eb cloud, metrikat sistemike t\u00eb nj\u00eb hosti t\u00eb ve\u00e7ant\u00eb shpesh kalojn\u00eb n\u00eb plan t\u00eb dyt\u00eb. Megjithat\u00eb, n\u00eb bazat e t\u00eb dh\u00ebnave ato ende jan\u00eb relevante dhe monitorimi i metrikeve sistemike sigurisht q\u00eb \u00ebsht\u00eb i domosdosh\u00ebm. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Me metrikat sistemike \u00ebsht\u00eb m\u00eb shum\u00eb-m\u00eb pak e gjith\u00eb situata \u00ebsht\u00eb n\u00eb rregull, t\u00eb gjitha sistemet moderne t\u00eb monitorimit tashm\u00eb mb\u00ebshtesin k\u00ebto metrika, por gjithsesi disa komponente akoma mungojn\u00eb dhe disa gj\u00ebra duhet t\u00eb shtohen. P\u00ebr k\u00ebto do t\u00eb flas\u00eb gjithashtu, disa diapozitiva do t\u00eb p\u00ebrfshijn\u00eb themra. <\/p>\n

<\/p>\n

\"Bazat
\nPika e par\u00eb e planit \u00ebsht\u00eb disponueshm\u00ebria. \u00c7far\u00eb \u00ebsht\u00eb disponueshm\u00ebria? N\u00eb mendimin tim, disponueshm\u00ebria \u00ebsht\u00eb aft\u00ebsia e baz\u00ebs p\u00ebr t\u00eb mb\u00ebshtetur lidhjet, dmth baza \u00ebsht\u00eb ngritur, ajo, si nj\u00eb sh\u00ebrbim, pranon lidhje nga klient\u00ebt. Dhe kjo disponueshm\u00ebri mund t\u00eb vler\u00ebsohet me disa karakteristika. K\u00ebto karakteristika jan\u00eb mjaft t\u00eb p\u00ebrshtatshme p\u00ebr t'u paraqitur n\u00eb dashboard. <\/p>\n

<\/p>\n

\"Bazat
\nT\u00eb gjith\u00eb e din\u00eb se \u00e7far\u00eb jan\u00eb tabelat e kontrollit. Ato jan\u00eb kur ju hidheni nj\u00eb her\u00eb n\u00eb ekran, ku \u00ebsht\u00eb e grumbulluar informacioni i nevojsh\u00ebm. Dhe ju mund ta p\u00ebrcaktoni menj\u00ebher\u00eb \u2013 n\u00ebse ka nj\u00eb problem n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave apo jo.
\nPrandaj, disponueshm\u00ebria e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave dhe karakteristikat e tjera ky\u00e7e gjithmon\u00eb duhet t\u00eb jen\u00eb t\u00eb pranishme n\u00eb tabelat e kontrollit, q\u00eb kjo informacion t\u00eb jet\u00eb n\u00eb dor\u00eb, gjithmon\u00eb pran\u00eb jush. Detajet e tjera, t\u00eb cilat ndihmojn\u00eb n\u00eb hetimin e incidenteve, n\u00eb hetimin e situatave emergjente, duhet t\u00eb jen\u00eb t\u00eb ndara n\u00eb tabelat e kontrollit t\u00eb dyta, ose t\u00eb fshihen n\u00eb lidhje t\u00eb drilldown, t\u00eb cilat \u00e7ojn\u00eb n\u00eb sisteme t\u00eb monitorimit t\u00eb jashtme. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Nj\u00eb shembull i nj\u00eb sistemi t\u00eb njohur t\u00eb monitorimit. Ky \u00ebsht\u00eb nj\u00eb sistem shum\u00eb i bukur monitorimi. Ai grumbullon shum\u00eb t\u00eb dh\u00ebna, por sipas mendimit tim, ai ka nj\u00eb koncept t\u00eb \u00e7uditsh\u00ebm t\u00eb tabelave t\u00eb kontrollit. Atje ka nj\u00eb lidhje 'krijo tabel\u00eb kontrolli'. Por kur krijoni nj\u00eb tabel\u00eb kontrolli, ju krijoni nj\u00eb list\u00eb t\u00eb caktuar, e cila p\u00ebrb\u00ebhet nga dy kolona, nj\u00eb list\u00eb grafik\u00ebsh. Dhe kur ju nevojitet t\u00eb shihni di\u00e7ka, filloni t\u00eb klikoni me miun, shtyni, k\u00ebrkoni grafik\u00ebn q\u00eb ju nevojitet. Dhe kjo merr koh\u00eb, pra tabelat e kontrollit, si\u00e7 jan\u00eb, nuk ekzistojn\u00eb. Ka vet\u00ebm lista grafik\u00ebsh.<\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

\u00c7far\u00eb duhet t\u00eb shtohet n\u00eb k\u00ebto tabela kontrolli? Mund t\u00eb filloni me nj\u00eb karakteristik\u00eb si koha e p\u00ebrgjigjes. N\u00eb PostgreSQL ka nj\u00eb pamje pg_stat_statements. N\u00eb m\u00ebnyr\u00eb default, ajo \u00ebsht\u00eb e \u00e7ky\u00e7ur, por kjo \u00ebsht\u00eb nj\u00eb nga pamjet sistematike t\u00eb r\u00ebnd\u00ebsishme q\u00eb gjithmon\u00eb duhet t\u00eb \u00e7ky\u00e7et dhe p\u00ebrdoret. Ajo ruan informacionin mbi t\u00eb gjitha k\u00ebrkesat q\u00eb jan\u00eb ekzekutuar n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave. <\/p>\n

<\/p>\n

Sipas k\u00ebsaj, ne mund t\u00eb fillojm\u00eb nga ajo q\u00eb mund t\u00eb marrim koh\u00ebn totale t\u00eb ekzekutimit t\u00eb t\u00eb gjitha pyetjeve dhe ta ndajm\u00eb me numrin e pyetjeve duke p\u00ebrdorur fushat e m\u00ebsip\u00ebrme. Por kjo \u00ebsht\u00eb si nj\u00eb temperatur\u00eb mesatare n\u00eb spital. Ne mund t\u00eb fillojm\u00eb nga fusha t\u00eb tjera \u2013 koha minimale e ekzekutimit t\u00eb pyetjeve, maksimumi dhe mediana. Madje mund t\u00eb nd\u00ebrtojm\u00eb percentilet, n\u00eb PostgreSQL ka funksione p\u00ebr k\u00ebt\u00eb. Dhe mund t\u00eb marrim disa shifra q\u00eb karakterizojn\u00eb koh\u00ebn e p\u00ebrgjigjes s\u00eb baz\u00ebs son\u00eb n\u00eb lidhje me pyetjet e ekzekutuara, dometh\u00ebn\u00eb, ne nuk ekzekutojm\u00eb nj\u00eb pyetje fasade 'select 1' dhe shohim koh\u00ebn e p\u00ebrgjigjes, por analizojm\u00eb koh\u00ebn e p\u00ebrgjigjeve p\u00ebr pyetjet e ekzekutuara dhe e paraqesim at\u00eb ose si nj\u00eb shif\u00ebr t\u00eb ve\u00e7ant\u00eb, ose e nd\u00ebrtom\u00eb grafikisht. <\/p>\n

<\/p>\n

Gjithashtu \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb ndjekim numrin e gabimeve, t\u00eb cilat gjenerohen nga sistemi n\u00eb k\u00ebt\u00eb moment. Dhe p\u00ebr k\u00ebt\u00eb mund t\u00eb p\u00ebrdorim pamjen pg_stat_database. Ne orientohemi n\u00eb fush\u00ebn xact_rollback. Kjo fush\u00eb tregon jo vet\u00ebm numrin e rrotullimeve q\u00eb ndodhin n\u00eb baz\u00eb, por gjithashtu merr parasysh numrin e gabimeve. N\u00eb nj\u00ebfar\u00eb m\u00ebnyre, mund ta nxjerrim k\u00ebt\u00eb shif\u00ebr n\u00eb tabel\u00ebn ton\u00eb t\u00eb kontrollit dhe t\u00eb shohim sa gabime kemi n\u00eb at\u00eb moment. N\u00ebse gabimet jan\u00eb t\u00eb shumta, kjo \u00ebsht\u00eb nj\u00eb mund\u00ebsi e mir\u00eb p\u00ebr t\u00eb hedhur nj\u00eb sy n\u00eb log dhe t\u00eb shikojm\u00eb se \u00e7far\u00eb lloji gabimesh jan\u00eb ato dhe pse ndodhin, dhe m\u00eb pas t\u00eb investigojm\u00eb dhe t'i zgjidhim ato.<\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Mund t\u00eb shtoni di\u00e7ka si Tachometrin. Ky \u00ebsht\u00eb numri i transaksioneve n\u00eb sekond\u00eb dhe numri i k\u00ebrkesave n\u00eb sekond\u00eb. N\u00eb nj\u00ebfar\u00eb m\u00ebnyre, ju mund t\u00eb p\u00ebrdorni k\u00ebto shifra si performanc\u00ebn aktuale t\u00eb baz\u00ebs suaj t\u00eb t\u00eb dh\u00ebnave dhe t\u00eb v\u00ebzhgoni n\u00ebse ka kulme t\u00eb k\u00ebrkesave, kulme t\u00eb transaksioneve ose, p\u00ebrkundrazi, baza nuk \u00ebsht\u00eb e ngarkuar, sepse ndonj\u00eb backend \u00ebsht\u00eb rr\u00ebzuar. Kjo shif\u00ebr \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb shikohet gjithmon\u00eb dhe t\u00eb mbani mend se p\u00ebr projektin ton\u00eb nj\u00eb performanc\u00eb e till\u00eb \u00ebsht\u00eb normale, nd\u00ebrsa vlerat m\u00eb t\u00eb larta dhe m\u00eb t\u00eb ul\u00ebta jan\u00eb t\u00eb problemet dhe t\u00eb paqart\u00eb, dhe prandaj duhet t\u00eb shikoni pse k\u00ebto shifra.<\/p>\n

<\/p>\n

P\u00ebr t\u00eb vler\u00ebsuar numrin e transaksioneve, ne mund t\u00eb kthehemi p\u00ebrs\u00ebri n\u00eb pamjen pg_stat_database. Ne mund t\u00eb p\u00ebrmbledhim numrin e commit dhe nuk mbytsh\u00ebm dhe t\u00eb marrim numrin e transaksioneve n\u00eb sekond\u00eb. <\/p>\n

<\/p>\n

T\u00eb gjith\u00eb e kuptojn\u00eb se n\u00eb nj\u00eb transaksion mund t\u00eb p\u00ebrfshihen disa k\u00ebrkesa? Pra, TPS dhe QPS jan\u00eb paksa t\u00eb ndryshme. <\/p>\n

<\/p>\n

Numri i k\u00ebrkesave n\u00eb sekond\u00eb mund t\u00eb merret nga pg_stat_statements dhe thjesht t\u00eb llogaritet shuma e t\u00eb gjitha k\u00ebrkesave t\u00eb ekzekutuara. \u00cbsht\u00eb e qart\u00eb se ne krahasojm\u00eb vler\u00ebn aktuale me at\u00eb t\u00eb m\u00ebparshme, e heqim, marrim diferenc\u00ebn dhe marrim numrin.<\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Mund t\u00eb shtoni metrika t\u00eb tjera n\u00eb var\u00ebsi nga d\u00ebshira, t\u00eb cilat gjithashtu ndihmojn\u00eb n\u00eb vler\u00ebsimin e disponueshm\u00ebris\u00eb s\u00eb baz\u00ebs son\u00eb dhe v\u00ebzhgojn\u00eb \u2013 a kishte ndonj\u00eb downtime. <\/p>\n

<\/p>\n

Nj\u00eb nga k\u00ebto metrika \u00ebsht\u00eb uptime. Por uptime n\u00eb PostgreSQL \u00ebsht\u00eb di\u00e7ka paksa e komplikuar. Do t\u00eb shpjegoj pse. Kur PostgreSQL fillon, fillon t\u00eb llogaris\u00eb uptime. Por n\u00ebse n\u00eb nj\u00eb moment, p\u00ebr shembull, gjat\u00eb nat\u00ebs ekzekutohet ndonj\u00eb detyr\u00eb dhe OOM-killer do t\u00eb p\u00ebrcaktoj\u00eb me forc\u00eb procesin f\u00ebmij\u00eb t\u00eb PostgreSQL, at\u00ebher\u00eb n\u00eb k\u00ebt\u00eb rast PostgreSQL ndalon lidhjen e t\u00eb gjith\u00eb klient\u00ebve, e ribashkon hap\u00ebsir\u00ebn e memories sharduar dhe fillon rikuperimin nga pika e fundit t\u00eb kontrollit. Dhe sa koh\u00eb zgjat kjo rikuperim nga pika e kontrollit, databaza nuk pranon lidhje, pra kjo situat\u00eb mund t\u00eb konsiderohet si downtime. Por edhe pse numri i uptime nuk do t\u00eb reshtet, sepse ai llogarit koh\u00ebn e nisjes s\u00eb postmaster q\u00eb nga momenti i par\u00eb. Prandaj, situatat e tilla mund t\u00eb kalohen pa u marr\u00eb parasysh.<\/p>\n

<\/p>\n

Gjithashtu duhet t\u00eb monitorohet numri i pun\u00ebtor\u00ebve t\u00eb vakumit. T\u00eb gjith\u00eb e din\u00eb \u00e7far\u00eb \u00ebsht\u00eb autovacuum n\u00eb PostgreSQL? Kjo \u00ebsht\u00eb nj\u00eb n\u00ebnstruktur\u00eb interesante n\u00eb PostgreSQL. P\u00ebr t\u00eb \u00ebsht\u00eb shkruar shum\u00eb artikuj, jan\u00eb b\u00ebr\u00eb shum\u00eb prezantime. Ka shum\u00eb diskutime rreth vakumit, se si duhet t\u00eb funksionoj\u00eb. Shum\u00eb e konsiderojn\u00eb at\u00eb nj\u00eb t\u00eb keqe t\u00eb pashmangshme. Por t\u00eb tilla \u00ebsht\u00eb. Ajo \u00ebsht\u00eb nj\u00eb p\u00ebrfaq\u00ebsuese e mbledh\u00ebsit t\u00eb plehrave, q\u00eb pastron versionet e vjetra t\u00eb rreshtave q\u00eb nuk i nevojiten asnj\u00eb nga transaksionet dhe \u00e7liron hap\u00ebsir\u00eb n\u00eb tabela, indekse p\u00ebr rreshta t\u00eb rinj. <\/p>\n

<\/p>\n

Pse duhet ta monitorojm\u00eb? Sepse vakumi ndonj\u00ebher\u00eb b\u00ebn shum\u00eb d\u00ebme. Ai konsumon nj\u00eb sasi t\u00eb madhe burimesh dhe k\u00ebrkesat e klient\u00ebve fillojn\u00eb t\u00eb vuajn\u00eb p\u00ebr k\u00ebt\u00eb. <\/p>\n

<\/p>\n

Dhe duhet ta monitorojm\u00eb at\u00eb p\u00ebrmes pamjes pg_stat_activity, p\u00ebr t\u00eb cil\u00ebn do t\u00eb flas n\u00eb seksionin tjet\u00ebr. Kjo pamje tregon aktivitetin aktual n\u00eb databaz\u00eb. Dhe p\u00ebrmes k\u00ebtij aktiviteti ne mund t\u00eb gjurmojm\u00eb numrin e vakumeve q\u00eb po punojn\u00eb aktualisht. Ne mund t\u00eb ndjekim vakumet dhe t\u00eb shohim se n\u00ebse kemi kaluar limitin, at\u00ebher\u00eb kjo \u00ebsht\u00eb nj\u00eb arsye p\u00ebr t\u00eb shqyrtuar parametrat e PostgreSQL dhe p\u00ebr t\u00eb optimizuar pun\u00ebn e vakumit. <\/p>\n

<\/p>\n

Nj\u00eb ve\u00e7ori tjet\u00ebr e PostgreSQL \u00ebsht\u00eb se PostgreSQL p\u00ebson shum\u00eb nga transaksionet e gjata. Sidomos, nga transaksionet q\u00eb varen p\u00ebr nj\u00eb koh\u00eb t\u00eb gjat\u00eb dhe nuk b\u00ebjn\u00eb asgj\u00eb. K\u00ebto jan\u00eb, t\u00eb ashtuquajturat, stat idle-in-transaction. Nj\u00eb transaksion i till\u00eb mban bllokimin, ai pengon pun\u00ebn e vakumit. Si rezultat, tabelat fryhen, ato rriten n\u00eb madh\u00ebsi. Dhe k\u00ebrkesat q\u00eb punojn\u00eb me k\u00ebto tabela fillojn\u00eb t\u00eb punojn\u00eb m\u00eb ngadal\u00eb, sepse duhet t\u00eb pastrojn\u00eb t\u00eb gjitha versionet e vjetra t\u00eb rreshtave nga memoria n\u00eb disk dhe p\u00ebr t'i kthyer prap\u00eb.<\/strong> Prandaj, koha e zgjatjes s\u00eb transaksioneve m\u00eb t\u00eb gjata, k\u00ebrkesat m\u00eb t\u00eb gjata t\u00eb vakumit gjithashtu duhet t\u00eb monitorohen. Dhe n\u00ebse shohim ndonj\u00eb proces q\u00eb punon p\u00ebr nj\u00eb koh\u00eb shum\u00eb t\u00eb gjat\u00eb, m\u00eb shum\u00eb se 10-20-30 minuta p\u00ebr ngarkes\u00ebn OLTP, at\u00ebher\u00eb duhet t\u2019i kushtohet v\u00ebmendje dhe duhet t\u00eb p\u00ebrfundohet me forc\u00eb, ose t\u00eb optimizohet aplikacioni n\u00eb m\u00ebnyr\u00eb q\u00eb ato t\u00eb mos thirren dhe t\u00eb mos mbesin kaq gjat\u00eb.<\/strong> P\u00ebr ngarkesat analitike 10-20-30 minuta \u00ebsht\u00eb normale, ndonj\u00ebher\u00eb mund t\u00eb zgjas\u00eb edhe m\u00eb shum\u00eb. <\/p>\n

<\/p>\n

\"Bazat
\nM\u00eb pas kemi variantin me klient\u00ebt e lidhur. Kur tashm\u00eb e kemi formuar dashboard-in, e kemi vendosur aty metrikat kryesore t\u00eb disponueshm\u00ebris\u00eb, mund t\u00eb shtojm\u00eb gjithashtu informacione shtes\u00eb p\u00ebr klient\u00ebt e lidhur. <\/p>\n

<\/p>\n

Informacioni rreth klient\u00ebve t\u00eb lidhur \u00ebsht\u00eb i r\u00ebnd\u00ebsish\u00ebm, sepse, nga k\u00ebndv\u00ebshtrimi i PostgreSQL, klient\u00ebt jan\u00eb t\u00eb ndrysh\u00ebm. Ka klient\u00eb t\u00eb mir\u00eb dhe ka klient\u00eb t\u00eb keq. <\/p>\n

<\/p>\n

Nj\u00eb shembull i thjesht\u00eb. Nga klienti kuptoj nj\u00eb aplikacion. Aplikacioni lidhet me databaz\u00ebn dhe fillon menj\u00ebher\u00eb t\u00eb d\u00ebrgoj\u00eb atje k\u00ebrkesat e tij, databaza i p\u00ebrpunon dhe ekzekuton, rezultatet i kthen klientit. K\u00ebta jan\u00eb klient\u00eb t\u00eb mir\u00eb dhe t\u00eb sakt\u00eb. <\/p>\n

<\/p>\n

Ka situata q\u00eb klienti \u00ebsht\u00eb lidhur, mban lidhjen, por nuk b\u00ebn asgj\u00eb. Ai \u00ebsht\u00eb n\u00eb gjendjen idle. <\/p>\n

<\/p>\n

Por ka klient\u00eb t\u00eb k\u00ebqij. P\u00ebr shembull, i nj\u00ebjti klient \u00ebsht\u00eb lidhur, ka hapur nj\u00eb transaksion, ka b\u00ebr\u00eb di\u00e7ka n\u00eb databaz\u00eb dhe pastaj ka kaluar n\u00eb kod, le t\u00eb themi, p\u00ebr t\u2019u drejtuar n\u00eb nj\u00eb burim t\u00eb jasht\u00ebm ose p\u00ebr t\u00eb b\u00ebr\u00eb atje p\u00ebrpunimin e t\u00eb dh\u00ebnave t\u00eb marra. Por ai nuk e ka mbyllur transaksionin. Dhe transaksioni mbetet hapur n\u00eb databaz\u00eb dhe mban bllokimin mbi rreshta. Ky \u00ebsht\u00eb nj\u00eb gjendje e keqe. Dhe n\u00ebse ndodhi q\u00eb aplikacioni t\u00eb bie ndonj\u00ebher\u00eb me nj\u00eb exception, at\u00ebher\u00eb transaksioni mund t\u00eb mbetet i hapur p\u00ebr nj\u00eb koh\u00eb shum\u00eb t\u00eb gjat\u00eb. Dhe kjo ndikon drejtp\u00ebrdrejt n\u00eb performanc\u00ebn e PostgreSQL. PostgreSQL do t\u00eb punoj\u00eb m\u00eb ngadal\u00eb. Prandaj, \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb monitorohet n\u00eb koh\u00eb k\u00ebta klient\u00eb dhe t\u00eb p\u00ebrfundohet puna e tyre me forc\u00eb. Dhe duhet t\u00eb optimizohet aplikacioni p\u00ebr t\u00eb parandaluar k\u00ebt\u00eb situat\u00eb. <\/p>\n

<\/p>\n

Klient\u00ebt e tjer\u00eb t\u00eb k\u00ebqij jan\u00eb ata q\u00eb presin. Por ata b\u00ebhen t\u00eb k\u00ebqij p\u00ebr shkak t\u00eb rrethanave. P\u00ebr shembull, nj\u00eb transaksion i thjesht\u00eb q\u00eb mbetet pezull: mund t\u00eb hap\u00eb nj\u00eb transaksion, t\u00eb marr\u00eb bllokime mbi disa rreshta, pastaj diku n\u00eb kod do t\u00eb bie, duke l\u00ebn\u00eb nj\u00eb transaksion t\u00eb mbetur pezull. Nj\u00eb klient tjet\u00ebr do t\u00eb vij\u00eb, do t\u00eb k\u00ebrkoj\u00eb t\u00eb nj\u00ebjtat t\u00eb dh\u00ebna, por do t\u00eb p\u00ebrballet me bllokimin sepse ai transaksion i mbetur pezull mban bllokime mbi disa rreshta t\u00eb nevojsh\u00ebm. Dhe transaksioni i dyt\u00eb do t\u00eb mbetet n\u00eb pritje derisa transaksioni i par\u00eb t\u00eb p\u00ebrfundoj\u00eb ose administratori i tij ta mbyll\u00eb me forc\u00eb. K\u00ebshtu, transaksionet q\u00eb presin mund t\u00eb grumbullohen dhe t\u00eb mbushin limitin e lidhjeve me baz\u00ebn e t\u00eb dh\u00ebnave. Dhe kur limiti \u00ebsht\u00eb tejkaluar, aplikacioni nuk mund t\u00eb punoj\u00eb m\u00eb me baz\u00ebn. Kjo \u00ebsht\u00eb nj\u00eb situat\u00eb emergjente p\u00ebr projektin. Prandaj, klient\u00ebt e k\u00ebqij duhet t\u00eb monitorohen dhe t\u00eb reagohet n\u00eb koh\u00eb ndaj tyre. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Nj\u00eb shembull tjet\u00ebr monitorimi. Dhe k\u00ebtu kemi nj\u00eb dashboard t\u00eb plot\u00eb. Ka informacion mbi lidhjet sip\u00ebr. Lidhja DB \u2013 8 \u00e7\u00ebshte. Dhe kjo \u00ebsht\u00eb gjith\u00e7ka. Ne nuk kemi informacion se cil\u00ebt klient\u00eb jan\u00eb aktiv\u00eb, cil\u00ebt klient\u00eb jan\u00eb thjesht t\u00eb papun\u00eb, nuk b\u00ebjn\u00eb asgj\u00eb. Nuk ka informacion mbi transaksionet e mbetura pezull dhe lidhjet q\u00eb presin, dmth. kjo \u00ebsht\u00eb nj\u00eb num\u00ebr q\u00eb tregon sasin\u00eb e lidhjeve dhe kaq. Tani, mundohuni t\u00eb kuptoni vet\u00eb.
\n\"Bazat
\nP\u00ebr ta shtuar k\u00ebt\u00eb informacion n\u00eb monitorim, duhet t\u00eb referoheni te vtk_stat_activity. N\u00ebse kaloni shum\u00eb koh\u00eb n\u00eb PostgreSQL, kjo \u00ebsht\u00eb nj\u00eb vew shum\u00eb e mir\u00eb q\u00eb duhet t\u00eb b\u00ebhet shoku juaj, sepse tregon aktivitetin aktual n\u00eb PostgreSQL, dmth. \u00e7far\u00eb po ndodh atje. P\u00ebr \u00e7do proces ka nj\u00eb rresht t\u00eb ve\u00e7ant\u00eb q\u00eb tregon informacionin mbi k\u00ebt\u00eb proces: nga cili host \u00ebsht\u00eb b\u00ebr\u00eb lidhja, nga cili p\u00ebrdorues, me cilin em\u00ebr, kur \u00ebsht\u00eb nisur transaksioni, cili k\u00ebrkes\u00eb \u00ebsht\u00eb duke iu p\u00ebrmbajtur aktualisht, cili k\u00ebrkes\u00eb \u00ebsht\u00eb ekzekutuar s\u00eb fundmi. Dhe, p\u00ebr rrjedhoj\u00eb, ne mund t\u00eb vler\u00ebsojm\u00eb gjendjen e klientit sipas fush\u00ebs stat. N\u00eb terma t\u00eb thjesht\u00eb, mund t\u00eb b\u00ebjm\u00eb grupimin sipas k\u00ebsaj fushe dhe t\u00eb marrim ato stats q\u00eb ekzistojn\u00eb aktualisht n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave dhe numrin e lidhjeve q\u00eb kan\u00eb k\u00ebto stats n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave. Dhe ndonj\u00ebher\u00eb n\u00eb xhiroshtat m\u00eb dalin numrat.
\nE r\u00ebnd\u00ebsishme \u00ebsht\u00eb t\u00eb vler\u00ebsojm\u00eb gjithashtu koh\u00ebzgjatjen e transaksionit. Kam folur m\u00eb par\u00eb se \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsojm\u00eb koh\u00ebzgjatjen e vakuumeve, por edhe transaksionet vler\u00ebsohen po ashtu. Ekzistojn\u00eb fushat xact_start dhe query_start. Ato, n\u00eb nj\u00eb far\u00eb m\u00ebnyre, tregojn\u00eb koh\u00ebn e nisjes s\u00eb transaksionit dhe koh\u00ebn e nisjes s\u00eb k\u00ebrkes\u00ebs. Ne marrim funksionin now(), i cili tregon pik\u00ebn e sakt\u00eb t\u00eb koh\u00ebs aktuale dhe e zbritim timestamp e transaksionit dhe k\u00ebrkes\u00ebs. Dhe marrim koh\u00ebzgjatjen e transaksionit, koh\u00ebzgjatjen e k\u00ebrkes\u00ebs. <\/p>\n

<\/p>\n

N\u00ebse shohim transaksione t\u00eb gjata, duhet t'i mbyllim ato. P\u00ebr ngarkesat OLTP, transaksionet e gjata jan\u00eb m\u00eb shum\u00eb se 1-2-3 minuta.<\/strong>. P\u00ebr ngarkesat OLAP, transaksionet e gjata jan\u00eb normale, por n\u00ebse ato zgjaten m\u00eb shum\u00eb se dy or\u00eb, kjo \u00ebsht\u00eb gjithashtu nj\u00eb shenj\u00eb se ndodhin probleme diku.<\/strong> <\/p>\n

<\/p>\n

\"Bazat
\nKur klient\u00ebt lidhen me baz\u00ebn e t\u00eb dh\u00ebnave, ata fillojn\u00eb t\u00eb punojn\u00eb me t\u00eb dh\u00ebnat tona. Ata iu drejtohen tabelave, iu drejtohen indekseve, p\u00ebr t\u00eb marr\u00eb t\u00eb dh\u00ebna nga tabela. Dhe \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsojm\u00eb se si klient\u00ebt punojn\u00eb me k\u00ebto t\u00eb dh\u00ebna.<\/p>\n

<\/p>\n

Kjo \u00ebsht\u00eb e nevojshme p\u00ebr t\u00eb vler\u00ebsuar ngarkes\u00ebn ton\u00eb dhe p\u00ebr t\u00eb kuptuar p\u00ebraf\u00ebrsisht se cilat jan\u00eb tabelat m\u00eb 't\u00eb nxehta'. P\u00ebr shembull, kjo \u00ebsht\u00eb e r\u00ebnd\u00ebsishme n\u00eb situatat kur duam t\u00eb vendosim tabelat 't\u00eb nxehta' n\u00eb ndonj\u00eb ruajtje t\u00eb shpejt\u00eb SSD. P\u00ebr shembull, disa tabela arkivore, q\u00eb nuk i kemi p\u00ebrdorur prej koh\u00ebsh, mund t'i kalojm\u00eb n\u00eb ndonj\u00eb 'ruajtje t\u00eb ftoht\u00eb', n\u00eb disqe SATA dhe le t\u00eb jetojn\u00eb atje, duke pasur qasje n\u00eb to sipas nevoj\u00ebs. <\/p>\n

<\/p>\n

Kjo \u00ebsht\u00eb e dobishme gjithashtu p\u00ebr t\u00eb zbuluar anomali pas disa l\u00ebshimeve dhe deplojeve. Supozoni se projekti ka nxjerr\u00eb nj\u00eb tipar t\u00eb ri. P\u00ebr shembull, kemi shtuar nj\u00eb funksionalitet t\u00eb ri p\u00ebr t\u00eb punuar me baz\u00ebn. Dhe n\u00ebse nd\u00ebrtuam grafikat e p\u00ebrdorimit t\u00eb tabelave, n\u00eb k\u00ebto grafika do t\u00eb jemi n\u00eb gjendje t\u00eb zbulojm\u00eb leht\u00ebsisht k\u00ebto anomali. P\u00ebr shembull, shp\u00ebrthime n\u00eb update ose shp\u00ebrthime n\u00eb delete. Kjo do t\u00eb jet\u00eb shum\u00eb e dukshme.<\/p>\n

<\/p>\n

Gjithashtu \u00ebsht\u00eb e mundur t\u00eb zbulohet anomali e statistikave q\u00eb kan\u00eb 'rr\u00ebshqitur'. \u00c7far\u00eb do t\u00eb thot\u00eb kjo? N\u00eb PostgreSQL ka nj\u00eb planifikues k\u00ebrkesash shum\u00eb t\u00eb fort\u00eb dhe shum\u00eb t\u00eb mir\u00eb. Dhe zhvilluesit i kushtojn\u00eb shum\u00eb koh\u00eb zhvillimit t\u00eb tij. Si funksionon? P\u00ebr t\u00eb nd\u00ebrtuar plane t\u00eb mira, PostgreSQL mbledh statistik\u00ebn p\u00ebr shp\u00ebrndarjen e t\u00eb dh\u00ebnave n\u00eb tabela me nj\u00eb interval t\u00eb caktuar, me nj\u00eb periodicitet t\u00eb caktuar. K\u00ebto jan\u00eb vlerat m\u00eb t\u00eb shpeshta: numri i vlerave unike, informacioni mbi NULL-in n\u00eb tabel\u00eb, shum\u00eb informacione. <\/p>\n

<\/p>\n

Bazuar n\u00eb k\u00ebt\u00eb statistik\u00eb, planifikuesi ndalon disa k\u00ebrkesa, zgjedh m\u00eb t\u00eb optimizuar\u00ebn dhe p\u00ebrdor k\u00ebt\u00eb plan k\u00ebrkes\u00eb p\u00ebr t\u00eb ekzekutuar k\u00ebrkes\u00ebn dhe kthyer t\u00eb dh\u00ebnat. <\/p>\n

<\/p>\n

Dhe ndodh q\u00eb statistika \"l\u00ebviz\". Cil\u00ebsia e t\u00eb dh\u00ebnave, sasia ka ndryshuar n\u00eb ndonj\u00eb m\u00ebnyr\u00eb n\u00eb tabel\u00eb, por statistika nuk \u00ebsht\u00eb mbledhur. Dhe planet e formuara mund t\u00eb mos jen\u00eb optimale. N\u00ebse planet tona rezultojn\u00eb t\u00eb jen\u00eb t\u00eb paoptimalizuara sipas monitorimit t\u00eb mbledhur, ne do t\u00eb mund t\u00eb shohim k\u00ebto anomalit\u00eb. P\u00ebr shembull, ndonj\u00ebher\u00eb, t\u00eb dh\u00ebnat kan\u00eb ndryshuar cil\u00ebsisht dhe indeksi fillon t\u00eb p\u00ebrdor\u00eb nj\u00eb kalim t\u00eb nj\u00ebpasnj\u00ebsh\u00ebm p\u00ebrmes tabel\u00ebs. Kjo do t\u00eb thot\u00eb q\u00eb n\u00ebse k\u00ebrkesa duhet t\u00eb kthej\u00eb vet\u00ebm 100 rreshta (ka nj\u00eb kufi limit 100), at\u00ebher\u00eb p\u00ebr k\u00ebt\u00eb k\u00ebrkes\u00eb do t\u00eb kryhet nj\u00eb kontroll i plot\u00eb. Dhe kjo gjithmon\u00eb ka nj\u00eb ndikim shum\u00eb negativ n\u00eb performanc\u00eb. <\/p>\n

<\/p>\n

Dhe ne do t\u00eb mund ta shohim k\u00ebt\u00eb n\u00eb monitorim. Dhe p\u00ebr t\u00eb par\u00eb k\u00ebt\u00eb k\u00ebrkes\u00eb, t\u00eb ekzekutojm\u00eb nj\u00eb explain p\u00ebr t\u00eb, t\u00eb mbledhim statistikat, t\u00eb nd\u00ebrtojm\u00eb nj\u00eb indeks t\u00eb ri shtes\u00eb. Dhe pastaj t\u00eb reagojm\u00eb ndaj k\u00ebsaj problemi. Prandaj, \u00ebsht\u00eb e r\u00ebnd\u00ebsishme. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Nj\u00eb shembull tjet\u00ebr monitorimi. Mendoj se shum\u00eb e njohin, sepse \u00ebsht\u00eb shum\u00eb popullor. Kush e p\u00ebrdor at\u00eb n\u00eb projektet e tyre Prometheus<\/a><\/noindex>? \u0410 \u043a\u0442\u043e \u0438\u0441\u043f\u043e\u043b\u044c\u0437\u0443\u0435\u0442 \u044d\u0442\u043e\u0442 \u043f\u0440\u043e\u0434\u0443\u043a\u0442 \u0441\u043e\u0432\u043c\u0435\u0441\u0442\u043d\u043e \u0441 Prometheus? \u0414\u0435\u043b\u043e \u0432 \u0442\u043e\u043c, \u0447\u0442\u043e \u0432 \u0441\u0442\u0430\u043d\u0434\u0430\u0440\u0442\u043d\u043e\u043c \u0440\u0435\u043f\u043e\u0437\u0438\u0442\u043e\u0440\u0438\u0438 \u044d\u0442\u043e\u0433\u043e \u043c\u043e\u043d\u0438\u0442\u043e\u0440\u0438\u043d\u0433\u0430 \u0435\u0441\u0442\u044c \u0434\u0430\u0448\u0431\u043e\u0440\u0434 \u0434\u043b\u044f \u0440\u0430\u0431\u043e\u0442\u044b \u0441 PostgreSQL \u2013 postgres_exporter<\/a><\/noindex> Prometheus. Por k\u00ebtu ka nj\u00eb detaj t\u00eb keq. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Ka disa grafika. Dhe si nj\u00ebsi jan\u00eb t\u00eb specifikuara bajt\u00ebt, pra ka 5 grafika. K\u00ebto jan\u00eb Insert data, Update data, Delete data, Fetch data dhe Return data. Si nj\u00ebsi matjeje jan\u00eb t\u00eb specifikuara bajt\u00ebt. Por \u00e7\u00ebshtja \u00ebsht\u00eb se statistika n\u00eb PostgreSQL kthen t\u00eb dh\u00ebnat n\u00eb tuple (rreshta). Dhe, p\u00ebr pasoj\u00eb, k\u00ebto grafika jan\u00eb nj\u00eb m\u00ebnyr\u00eb shum\u00eb e mir\u00eb p\u00ebr t\u00eb ulur ngarkes\u00ebn tuaj disa her\u00eb, madje dhjet\u00ebra her\u00eb, sepse tuple nuk \u00ebsht\u00eb bajt, tuple \u00ebsht\u00eb rresht, kjo \u00ebsht\u00eb shum\u00eb bajt\u00eb dhe gjithmon\u00eb ka gjat\u00ebsi variabile. Pra, llogaritja e ngarkes\u00ebs n\u00eb bajt\u00eb p\u00ebrmes tuples \u00ebsht\u00eb nj\u00eb detyr\u00eb e pamundur ose shum\u00eb e komplikuar. Prandaj, kur p\u00ebrdorni nj\u00eb dashboard ose monitorim t\u00eb integruar, gjithmon\u00eb \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb kuptoni se ai funksionon sakt\u00eb dhe ju kthen t\u00eb dh\u00ebna t\u00eb vler\u00ebsuara n\u00eb m\u00ebnyr\u00eb korrekte. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Si t\u00eb marrim statistika p\u00ebr k\u00ebto tabela? P\u00ebr k\u00ebt\u00eb PostgreSQL ka nj\u00eb familje t\u00eb caktuar t\u00eb view-\u00ebve. Dhe view kryesore \u00ebsht\u00eb pg_stat_user_tables<\/a><\/noindex>. User_tables do t\u00eb thot\u00eb se tabelat jan\u00eb krijuar nga p\u00ebrdoruesi. N\u00eb kontrast, ka view t\u00eb sistemit q\u00eb p\u00ebrdor vet\u00eb PostgreSQL. Dhe ka nj\u00eb tabel\u00eb p\u00ebrmbledh\u00ebse Alltables, e cila p\u00ebrfshin si tabelat sistemike, ashtu edhe ato t\u00eb p\u00ebrdoruesve. Ju mund t\u00eb mb\u00ebshteteni n\u00eb ndonj\u00ebra prej tyre, cilat do ju p\u00eblqejn\u00eb m\u00eb shum\u00eb.<\/p>\n

<\/p>\n

P\u00ebrmes fushave t\u00eb p\u00ebrmendura m\u00eb lart mund t\u00eb vler\u00ebsojm\u00eb numrin e insert, update dhe delete. Shembulli i dashboard-it q\u00eb kam p\u00ebrdorur, sakt\u00ebsisht p\u00ebrdor k\u00ebto fusha p\u00ebr t\u00eb vler\u00ebsuar karakteristikat e ngarkes\u00ebs. Prandaj, ne gjithashtu mund t\u00eb mb\u00ebshtetemi n\u00eb to. Por duhet t\u00eb mbahet mend se k\u00ebto jan\u00eb tuples, jo bajt\u00eb, prandaj nuk mund t\u00eb kthehemi dhe t\u00eb b\u00ebjm\u00eb at\u00eb n\u00eb bajt\u00eb.<\/p>\n

<\/p>\n

Bazuar n\u00eb k\u00ebto t\u00eb dh\u00ebna, ne mund t\u00eb nd\u00ebrtojm\u00eb, k\u00ebshtu quhen, tabelat TopN. P\u00ebr shembull, Top-5, Top-10. Dhe mund t\u00eb ndjekim ato tabela \"t\u00eb nxehta\" q\u00eb p\u00ebrdoren m\u00eb shum\u00eb se t\u00eb tjerat. P\u00ebr shembull, 5 tabelat \"e nxehta\" me insertim. Dhe bazuar n\u00eb k\u00ebto tabela TopN, ne vler\u00ebsojm\u00eb ngarkes\u00ebn ton\u00eb dhe mund t\u00eb vler\u00ebsojm\u00eb shp\u00ebrthimet e ngarkes\u00ebs pas \u00e7do l\u00ebshimi, p\u00ebrdit\u00ebsimi dhe deploji. <\/p>\n

<\/p>\n

Gjithashtu \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsojm\u00eb p\u00ebrmasat e tabelave, sepse ndonj\u00ebher\u00eb zhvilluesit l\u00ebshojn\u00eb nj\u00eb ve\u00e7ori t\u00eb re dhe tabelat tona fillojn\u00eb t\u00eb fryhen n\u00eb p\u00ebrmasat e tyre t\u00eb m\u00ebdha, sepse vendosin t\u00eb shkruajn\u00eb nj\u00eb volum t\u00eb shtuar t\u00eb t\u00eb dh\u00ebnave, por nuk parashikojn\u00eb si do t\u00eb ndikoj\u00eb kjo n\u00eb madh\u00ebsin\u00eb e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. K\u00ebto raste gjithashtu ndodhin dhe jan\u00eb surpriza p\u00ebr ne. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Dhe tani nj\u00eb pyetje e vog\u00ebl p\u00ebr ju. Cila \u00ebsht\u00eb pyetja q\u00eb lind kur v\u00ebreni ngarkes\u00ebn n\u00eb serverin me baz\u00ebn e t\u00eb dh\u00ebnave? Cila \u00ebsht\u00eb pyetja tjet\u00ebr q\u00eb ju lind? <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Por n\u00eb t\u00eb v\u00ebrtet\u00eb pyetja q\u00eb lind \u00ebsht\u00eb kjo. Cil\u00ebt jan\u00eb k\u00ebrkesat q\u00eb shkaktojn\u00eb ngarkes\u00eb? Pra, nuk \u00ebsht\u00eb interesante t\u00eb shikosh proceset q\u00eb shkaktojn\u00eb ngarkes\u00ebn. E kuptueshme, n\u00ebse hosti ka baz\u00ebn e t\u00eb dh\u00ebnave, at\u00ebher\u00eb aty \u00ebsht\u00eb e drejtuar baza e t\u00eb dh\u00ebnave dhe e qart\u00eb se vet\u00ebm baza e t\u00eb dh\u00ebnave do t\u00eb p\u00ebrdor\u00eb aty. N\u00ebse hapim Top, do t\u00eb shohim nj\u00eb list\u00eb procesesh n\u00eb PostgreSQL q\u00eb b\u00ebjn\u00eb di\u00e7ka. Nga Top do t\u00eb jet\u00eb e paqart\u00eb se \u00e7far\u00eb b\u00ebjn\u00eb ata. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Pra, \u00ebsht\u00eb e nevojshme t\u00eb zbulojm\u00eb ato k\u00ebrkesa q\u00eb shkaktojn\u00eb ngarkes\u00ebn m\u00eb t\u00eb madhe, sepse tuning i k\u00ebrkesave, zakonisht, sjell m\u00eb shum\u00eb p\u00ebrfitim sesa tuning i konfigurimit t\u00eb PostgreSQL ose sistemit operativ, ose madje tuning i harduerit. Sipas vler\u00ebsimit tim \u2013 kjo \u00ebsht\u00eb rreth 80-85-90%. Dhe kjo b\u00ebhet shum\u00eb m\u00eb shpejt. \u00cbsht\u00eb m\u00eb e leht\u00eb t\u00eb korrigjosh nj\u00eb k\u00ebrkes\u00eb, sesa t\u00eb korrigjosh konfigurimin, t\u00eb planifikosh nj\u00eb rinisje, ve\u00e7an\u00ebrisht n\u00ebse nuk lejohet rinisja e baz\u00ebs, ose t\u00eb shtosh harduer. M\u00eb e thjesht\u00eb \u00ebsht\u00eb t\u00eb riformatosh ndonj\u00eb k\u00ebrkes\u00eb apo t\u00eb shtosh nj\u00eb indeks p\u00ebr t\u00eb marr\u00eb nj\u00eb rezultat m\u00eb t\u00eb mir\u00eb nga kjo k\u00ebrkes\u00eb. <\/p>\n

<\/p>\n

\"Bazat
\nPrandaj, duhet t\u00eb monitorojm\u00eb k\u00ebrkesat dhe adekvat\u00ebsin\u00eb e tyre. Le t\u00eb marrim nj\u00eb shembull tjet\u00ebr t\u00eb monitorimit. Dhe k\u00ebtu gjithashtu duket nj\u00eb monitorim i shk\u00eblqyer. Ka informacione mbi replikimin, ka informacione mbi kapacitetin, bllokimet, shfryt\u00ebzimin e resurseve. E gjith\u00eb kjo \u00ebsht\u00eb e shk\u00eblqyer, por nuk ka informacione mbi k\u00ebrkesat. Nuk \u00ebsht\u00eb e qart\u00eb se cilat k\u00ebrkesa po ekzekutohen n\u00eb baz\u00ebn ton\u00eb t\u00eb t\u00eb dh\u00ebnave, sa gjat\u00eb po ekzekutohen, sa jan\u00eb k\u00ebto k\u00ebrkesa. Ne duhet t\u00eb kemi gjithmon\u00eb k\u00ebt\u00eb informacion n\u00eb monitorim. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Dhe p\u00ebr t\u00eb marr\u00eb k\u00ebt\u00eb informacion ne mund t\u00eb p\u00ebrdorim modulin pg_stat_statements. N\u00eb baz\u00ebn e tij mund t\u00eb nd\u00ebrtojm\u00eb grafika t\u00eb ndryshme. P\u00ebr shembull, mund t\u00eb marrim informacione p\u00ebr k\u00ebrkesat m\u00eb t\u00eb zakonshme, dometh\u00ebn\u00eb p\u00ebr ato k\u00ebrkesa q\u00eb ekzekutohen m\u00eb shpesh. Po, pas deployeve gjithashtu \u00ebsht\u00eb shum\u00eb e dobishme ta shohim dhe t\u00eb kuptojm\u00eb n\u00ebse ka ndonj\u00eb shp\u00ebrthim t\u00eb k\u00ebrkesave. <\/p>\n

<\/p>\n

Mund t\u00eb monitorojm\u00eb k\u00ebrkesat m\u00eb t\u00eb gjata, dometh\u00ebn\u00eb ato k\u00ebrkesa q\u00eb ekzekutohen m\u00eb gjat\u00eb. Ato funksionojn\u00eb n\u00eb procesor, konsumojn\u00eb input-output. Ne gjithashtu mund ta vler\u00ebsojm\u00eb k\u00ebt\u00eb sipas fushave total_time, mean_time, blk_write_time dhe blk_read_time. <\/p>\n

<\/p>\n

Ne mund t\u00eb vler\u00ebsojm\u00eb dhe t\u00eb monitorojm\u00eb k\u00ebrkesat m\u00eb t\u00eb r\u00ebnda n\u00eb aspektin e shfryt\u00ebzimit t\u00eb resurseve, ato q\u00eb lexojn\u00eb nga disku, ato q\u00eb punojn\u00eb me memorie apo, p\u00ebrkundrazi, krijojn\u00eb nj\u00eb ngarkes\u00eb shkruese.<\/p>\n

<\/p>\n

Mund t\u00eb vler\u00ebsojm\u00eb edhe k\u00ebrkesat m\u00eb t\u00eb bujshme. K\u00ebto jan\u00eb ato k\u00ebrkesa q\u00eb kthejn\u00eb nj\u00eb num\u00ebr t\u00eb madh rreshtash. P\u00ebr shembull, mund t\u00eb jet\u00eb ndonj\u00eb k\u00ebrkes\u00eb ku \u00ebsht\u00eb harruar t\u00eb vendoset nj\u00eb limit. Dhe ajo thjesht kthen gjith\u00eb p\u00ebrmbajtjen e tabel\u00ebs ose k\u00ebrkuesve t\u00eb tabelave t\u00eb k\u00ebrkuara.<\/p>\n

<\/p>\n

Dhe mund t\u00eb monitorojm\u00eb gjithashtu k\u00ebrkesat q\u00eb p\u00ebrdorin skedar\u00eb temporar\u00eb ose tabela temporar\u00eb. <\/p>\n

<\/p>\n

\"Bazat
\nDhe na kan\u00eb mbetur proceset e prapme. Proceset e prapme jan\u00eb para s\u00eb gjithash kontrollet, ose si\u00e7 quhen ndryshe, pik\u00ebt e kontrollit, autovacuum dhe replikimi. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Nj\u00eb tjet\u00ebr shembull monitorimi. Ka nj\u00eb sked\u00eb Maintenance n\u00eb maj\u00eb, kalojm\u00eb n\u00eb t\u00eb dhe shpresojm\u00eb t\u00eb shohim di\u00e7ka t\u00eb dobishme. Por k\u00ebtu ka vet\u00ebm koh\u00ebn e pun\u00ebs s\u00eb vakuumit dhe mbledhjen e statistikave, asgj\u00eb m\u00eb shum\u00eb. Kjo \u00ebsht\u00eb shum\u00eb informacion i varf\u00ebr, prandaj gjithmon\u00eb \u00ebsht\u00eb e nevojshme t\u00eb kem informacion se si funksionojn\u00eb proceset e prapme n\u00eb baz\u00ebn ton\u00eb t\u00eb t\u00eb dh\u00ebnave dhe n\u00ebse ka ndonj\u00eb problem nga puna e tyre. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Kur shqyrtojm\u00eb pik\u00ebt e kontrollit, duhet t\u00eb mbajm\u00eb parasysh se pik\u00ebt e kontrollit e shkarkojn\u00eb \"faqet e ndotura\" nga zona e memories s\u00eb sharduar n\u00eb disk, pastaj krijojn\u00eb nj\u00eb pik\u00eb kontrolli. Dhe kjo pik\u00eb kontrolli mund t\u00eb p\u00ebrdoret si nj\u00eb vend p\u00ebr rikuperim, n\u00ebse ndonj\u00ebher\u00eb PostgreSQL \u00ebsht\u00eb mbyllur n\u00eb m\u00ebnyr\u00eb t\u00eb papritur. <\/p>\n

<\/p>\n

Prandaj, p\u00ebr t\u00eb shkarkuar t\u00eb gjitha \"faqet e ndotura\" n\u00eb disk, duhet t\u00eb realizohet nj\u00eb sasi e caktuar shkruese. Dhe, si rregull, n\u00eb sistemet me nj\u00eb sasi t\u00eb madhe memories \u2013 kjo \u00ebsht\u00eb shum\u00eb e madhe. Dhe n\u00ebse pik\u00ebt e kontrollit b\u00ebhen shum\u00eb shpesh n\u00eb nj\u00eb interval t\u00eb shkurt\u00ebr, at\u00ebher\u00eb performanca e diskut do t\u00eb ulet shum\u00eb. K\u00ebrkesat e klient\u00ebve do t\u00eb vuajn\u00eb nga mungesa e resurseve. Ato do t\u00eb luftojn\u00eb p\u00ebr resurset dhe do t'u mungoj\u00eb kapaciteti. <\/p>\n

<\/p>\n

Prandaj, p\u00ebrmes pg_stat_bgwriter ne mund t\u00eb monitorojm\u00eb numrin e pik\u00ebve t\u00eb kontrollit q\u00eb ndodhin. Dhe n\u00ebse p\u00ebr nj\u00eb periudh\u00eb t\u00eb caktuar kohe (p\u00ebr 10-15-20 minuta, p\u00ebr gjysm\u00eb ore) ka shum\u00eb pika kontrolli, p\u00ebr shembuj, 3-4-5, at\u00ebher\u00eb kjo mund t\u00eb jet\u00eb nj\u00eb problem. Dhe pastaj duhet t\u00eb shohim n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave, t\u00eb shohim n\u00eb konfigurim, \u00e7far\u00eb po shkakton k\u00ebt\u00eb p\u00ebrmbajtje t\u00eb madhe t\u00eb pik\u00ebve kontrolleve. Ndoshta, ka ndonj\u00eb shkarkim t\u00eb madh. N\u00eb baz\u00eb t\u00eb ngarkes\u00ebs mund t\u00eb vler\u00ebsojm\u00eb, sepse grafikat e ngarkes\u00ebs jan\u00eb tashm\u00eb t\u00eb shtuar. Ne mund t\u00eb rregullojm\u00eb parametrat e pik\u00ebve t\u00eb kontrollit dhe t\u00eb b\u00ebjm\u00eb q\u00eb ato t\u00eb mos ndikojn\u00eb shum\u00eb n\u00eb performanc\u00ebn e k\u00ebrkesave.<\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Po rikthehem s\u00ebrish te autovacuum, sepse kjo \u00ebsht\u00eb nj\u00eb gj\u00eb, si\u00e7 kam th\u00ebn\u00eb, q\u00eb leht\u00ebsisht mund t\u00eb d\u00ebmtoj\u00eb performanc\u00ebn si t\u00eb disqeve ashtu edhe t\u00eb k\u00ebrkesave, prandaj gjithmon\u00eb \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsojm\u00eb numrin e autovacuum. <\/p>\n

<\/p>\n

Numri i punonj\u00ebsve autovacuum n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave \u00ebsht\u00eb i kufizuar. Si rregull, jan\u00eb tre, prandaj n\u00ebse gjithmon\u00eb kan\u00eb funksionuar tre punonj\u00ebs n\u00eb baz\u00eb, at\u00ebher\u00eb kjo do t\u00eb thot\u00eb se autovacuum \u00ebsht\u00eb i paqart\u00eb, duhet t\u00eb rriten kufijt\u00eb, t\u00eb rishikohen cil\u00ebsimet e autovacuum dhe t\u00eb hyhet n\u00eb konfigurim.
\n\u00cbsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsojm\u00eb cil\u00ebt punonj\u00ebs po funksionojn\u00eb. Ose \u00ebsht\u00eb nj\u00eb vakuum i nisur nga p\u00ebrdoruesi, DBA ka ardhur dhe ka nisur me duar ndonj\u00eb vakuum, dhe kjo krijoi nj\u00eb ngarkes\u00eb. Llogarit\u00eb ndonj\u00eb problem. Ose \u00ebsht\u00eb numri i vakuumeve q\u00eb rrisin numrin e transaksioneve. P\u00ebr disa versione t\u00eb PostgreSQL \u2013 k\u00ebto jan\u00eb vakuume shum\u00eb t\u00eb r\u00ebnda. Dhe ato mund t\u00eb d\u00ebmtojn\u00eb leht\u00ebsisht performanc\u00ebn, sepse lexojn\u00eb t\u00eb gjith\u00eb tabel\u00ebn n\u00eb t\u00ebr\u00ebsi, skanojn\u00eb t\u00eb gjitha blloqet n\u00eb k\u00ebt\u00eb tabel\u00eb. <\/p>\n

<\/p>\n

Dhe, sigurisht, \u00ebsht\u00eb edhe koh\u00ebzgjatja e vakuumeve. N\u00ebse kemi vakuume t\u00eb gjata q\u00eb funksionojn\u00eb p\u00ebr nj\u00eb koh\u00eb t\u00eb gjat\u00eb, at\u00ebher\u00eb duhet t\u00eb shqyrtojm\u00eb p\u00ebrs\u00ebri konfigurimin e vakuumit dhe ndoshta t\u00eb rishohim cil\u00ebsimet e tij. Sepse mund t\u00eb ndodh\u00eb q\u00eb vakuumi t\u00eb punoj\u00eb p\u00ebr nj\u00eb koh\u00eb t\u00eb gjat\u00eb mbi nj\u00eb tabel\u00eb (3-4 or\u00eb), por gjat\u00eb koh\u00ebs s\u00eb pun\u00ebs s\u00eb vakuumit n\u00eb tabel\u00eb jan\u00eb akumuluar p\u00ebrs\u00ebri nj\u00eb sasi e madhe rreshtash t\u00eb vdekur. Dhe sapo t\u00eb p\u00ebrfundoj\u00eb vakuumi, ai ka nevoj\u00eb s\u00ebrish t\u00eb b\u00ebj\u00eb vakuum mbi k\u00ebt\u00eb tabel\u00eb. Dhe arrijm\u00eb n\u00eb situat\u00ebn e vakuumit t\u00eb pafund. N\u00eb k\u00ebt\u00eb rast, vakuumi nuk p\u00ebrmbush detyrat e tij, dhe tabelat fillojn\u00eb t\u00eb fryhen gradualisht n\u00eb p\u00ebrmasa, ndon\u00ebse sasia e t\u00eb dh\u00ebnave t\u00eb dobishme n\u00eb to mbetet e nj\u00ebjt\u00eb. Prandaj, gjat\u00eb vakuumeve t\u00eb gjata, gjithmon\u00eb shqyrtojm\u00eb konfigurimin dhe p\u00ebrpiqemi ta optimizojm\u00eb at\u00eb, por pa e d\u00ebmtuar performanc\u00ebn e k\u00ebrkesave t\u00eb klient\u00ebve. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Tani praktikisht nuk ka instalimesh t\u00eb PostgreSQL pa replikimin n\u00eb rrjedh\u00eb. Replikimi \u00ebsht\u00eb procesi i transferimit t\u00eb t\u00eb dh\u00ebnave nga masteri n\u00eb replik.<\/p>\n

<\/p>\n

Replikimi n\u00eb PostgreSQL funksionon n\u00ebp\u00ebrmjet journalit t\u00eb transaksioneve. Masteri gjeneron journalin e transaksioneve. Ky journal niset p\u00ebrmes lidhjes rrjet es\u00eb p\u00ebr n\u00eb replik\u00eb, dhe m\u00eb pas ai riprodhohet n\u00eb replik\u00eb. E thjesht\u00eb. <\/p>\n

<\/p>\n

Prandaj, p\u00ebr monitorimin e vones\u00ebs s\u00eb replikimit p\u00ebrdoret pamja pg_stat_replication. Por me t\u00eb, nuk \u00ebsht\u00eb gjith\u00e7ka kaq e thjesht\u00eb. N\u00eb versionin 10, pamja ka kaluar disa ndryshime. S\u00eb pari, disa fusha jan\u00eb rinovuar. Disa fusha jan\u00eb shtuar gjithashtu. N\u00eb versionin 10 jan\u00eb shtuar fusha q\u00eb lejojn\u00eb t\u00eb vler\u00ebsohet vonesa e replikimit n\u00eb sekonda. Kjo \u00ebsht\u00eb shum\u00eb e dobishme. Para versionit 10, ishte e mundur t\u00eb vler\u00ebsohej vonesa e replikimit n\u00eb byte. Kjo mund\u00ebsi ka mbetur edhe n\u00eb versionin 10, pra, mund t\u00eb zgjidhni se \u00e7far\u00eb ju p\u00ebrshtatet m\u00eb mir\u00eb \u2013 t\u00eb vler\u00ebsoni vones\u00ebn n\u00eb byte ose n\u00eb sekonda. Shum\u00eb p\u00ebrdorin t\u00eb dyja.<\/p>\n

<\/p>\n

Megjithat\u00eb, p\u00ebr t\u00eb vler\u00ebsuar vones\u00ebn e replikimit, \u00ebsht\u00eb e nevojshme t\u00eb dihet pozita e journalit n\u00eb transaksion. Dhe k\u00ebto pozita t\u00eb journalit t\u00eb transaksioneve ndodhen pik\u00ebrisht n\u00eb pamjen pg_stat_replication. Th\u00ebn\u00eb ndryshe, me ndihm\u00ebn e funksionit pg_xlog_location_diff() mund t\u00eb marrim dy pika n\u00eb journalin e transaksioneve. T\u00eb llogarisim diferenc\u00ebn mes tyre dhe t\u00eb marrim vones\u00ebn e replikimit n\u00eb byte. Kjo \u00ebsht\u00eb shum\u00eb e dobishme dhe e thjesht\u00eb. <\/p>\n

<\/p>\n

N\u00eb versionin 10, ky funksion \u00ebsht\u00eb rinovuar n\u00eb pg_wal_lsn_diff(). N\u00eb p\u00ebrgjith\u00ebsi, n\u00eb t\u00eb gjitha funksionet, pamjet, utilitetet, ku \u00ebsht\u00eb hasur fjal\u00ebn \"xlog\", \u00ebsht\u00eb z\u00ebvend\u00ebsuar me termin \"wal\". Kjo \u00ebsht\u00eb nj\u00eb risit\u00eb e till\u00eb. <\/p>\n

<\/p>\n

Plus n\u00eb versionin 10 jan\u00eb shtuar linjat q\u00eb tregojn\u00eb sakt\u00ebsisht vones\u00ebn. Kjo \u00ebsht\u00eb vonesa e shkruar, vonesa e flush, vonesa e riprodhimit. Pra, k\u00ebto jan\u00eb gj\u00ebra t\u00eb r\u00ebnd\u00ebsishme p\u00ebr tu monitoruar. N\u00ebse shohim q\u00eb kemi vones\u00eb replikimi, duhet t\u00eb hetojm\u00eb pse \u00ebsht\u00eb shfaqur, nga ka ardhur dhe t\u00eb zgjidhim problemin. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Me metrikat sistemore, pothuajse gjith\u00e7ka \u00ebsht\u00eb n\u00eb rregull. Kur fillon \u00e7do monitorim, ai fillon me metrikat sistemore. K\u00ebto jan\u00eb p\u00ebrdorimi i procesor\u00ebve, memorie, swap, rrjeti dhe disku. Megjithat\u00eb, shum\u00eb parametra nuk jan\u00eb aty nga default. <\/p>\n

<\/p>\n

N\u00ebse p\u00ebrdorimi i procesit \u00ebsht\u00eb n\u00eb rregull, at\u00ebher\u00eb problemet jan\u00eb me p\u00ebrdorimin e disku. Si rregull, zhvilluesit e monitorimeve shtojn\u00eb informacion rreth kapacitetit t\u00eb kalimit. Ky informacion mund t\u00eb jet\u00eb n\u00eb iops ose byte. Por harrojn\u00eb mbi latency dhe p\u00ebrdorimin e pajisjeve disk. K\u00ebto jan\u00eb parametra m\u00eb t\u00eb r\u00ebnd\u00ebsish\u00ebm q\u00eb lejojn\u00eb t\u00eb vler\u00ebsohet sa jan\u00eb t\u00eb ngarkuar disqet dhe sa ngadal\u00ebsojn\u00eb. N\u00ebse kemi latency t\u00eb lart\u00eb, kjo tregon se ka disa probleme me disqet. N\u00ebse kemi p\u00ebrdorim t\u00eb lart\u00eb, kjo do t\u00eb thot\u00eb se disqet nuk po mundohen.<\/strong> K\u00ebto jan\u00eb karakteristika m\u00eb cil\u00ebsore se kapaciteti i kalimit.<\/p>\n

<\/p>\n

Duke pasur parasysh q\u00eb kjo statistik\u00eb gjithashtu mund t\u00eb merret nga sistemi i skedar\u00ebve \/proc, si\u00e7 b\u00ebhet p\u00ebr p\u00ebrdorimin e procesor\u00ebve. Pse kjo informacion nuk shtohet n\u00eb monitorime, nuk e di. Megjithat\u00eb, \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb keni k\u00ebt\u00eb n\u00eb monitorimin tuaj.<\/strong> <\/p>\n

<\/p>\n

Po ashtu p\u00ebr nd\u00ebrfaqet rrjet. Ka informacion mbi kapacitetin e kalimit t\u00eb rrjetit n\u00eb paketa, n\u00eb byte, por megjithat\u00eb nuk ka informacion mbi latency dhe nuk ka informacion mbi p\u00ebrdorimin, ndon\u00ebse kjo gjithashtu \u00ebsht\u00eb informacion i dobish\u00ebm.<\/strong> <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

\u00c7do monitorim ka disavantazhe. Dhe \u00e7far\u00ebdo monitorimi q\u00eb t\u00eb zgjidhni, gjithmon\u00eb do t\u00eb ket\u00eb disa kritere q\u00eb nuk p\u00ebrmbushen. Megjithat\u00eb, ata po zhvillohen, shtohen funksionalitete t\u00eb reja, gj\u00ebra t\u00eb reja, prandaj zgjidhni di\u00e7ka dhe rregulloni. <\/p>\n

<\/p>\n

Dhe p\u00ebr t\u00eb rregulluar, \u00ebsht\u00eb gjithmon\u00eb e nevojshme t\u00eb keni nj\u00eb ide se \u00e7far\u00eb do t\u00eb thot\u00eb statistika q\u00eb jepet dhe si me ndihm\u00ebn e saj mund t\u00eb zgjidhni probleme. <\/p>\n

<\/p>\n

Dhe disa pika kryesore:<\/p>\n

<\/p>\n