{"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 t\u00eb PostgreSQL. Aleksei Lesovski","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Ju lutem, shqyrtoni interpretime t\u00eb raportit nga Alexey Lesovski nga Data Egret \"Bazat e monitorimit t\u00eb PostgreSQL\"<\/strong><\/p>\n

<\/p>\n

N\u00eb k\u00ebt\u00eb raport, Alexey Lesovski do t\u00eb flas\u00eb p\u00ebr piketat ky\u00e7e t\u00eb statistik\u00ebs PostgreSQL, \u00e7far\u00eb p\u00ebrfaq\u00ebsojn\u00eb ato dhe pse duhet t\u00eb jen\u00eb pjes\u00eb e monitorimit; p\u00ebr grafikat q\u00eb duhet t\u00eb p\u00ebrmbahen n\u00eb monitorim, si t'i shtojm\u00eb ato dhe si t'i interpretojm\u00eb. Raporti do t\u00eb jet\u00eb i dobish\u00ebm p\u00ebr administruesit e bazave t\u00eb t\u00eb dh\u00ebnave, administruesit e sistemit dhe zhvilluesit q\u00eb jan\u00eb t\u00eb interesuar p\u00ebr zgjidhjen e problemeve me Postgres.<\/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 Alexey Lesovski, p\u00ebrfaq\u00ebsoj kompanin\u00eb Data Egret. <\/p>\n

<\/p>\n

Pak fjal\u00eb p\u00ebr veten. Dikur, dikur isha administrator sistemi. <\/p>\n

<\/p>\n

Kam administruar lloje t\u00eb ndryshme Linux, kam b\u00ebr\u00eb gj\u00ebra t\u00eb ndryshme q\u00eb lidhen me Linux, pra, virtualizim, monitorim, kam punuar me proksi 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 kaloj pjes\u00ebn m\u00eb t\u00eb madhe t\u00eb koh\u00ebs time t\u00eb pun\u00ebs n\u00eb PostgreSQL. K\u00ebshtu gradualisht u b\u00ebra DBA i PostgreSQL.<\/p>\n

<\/p>\n

Dhe gjat\u00eb gjith\u00eb karrier\u00ebs sime, kam qen\u00eb gjithmon\u00eb i interesuar p\u00ebr temat e statistikave, monitorimit, marrjes s\u00eb telemetris\u00eb. Kur isha administrator sistemi, merresha ngusht\u00eb me Zabbix. Dhe shkrova nj\u00eb grumbull t\u00eb vog\u00ebl skriptesh si zabbix-extensions<\/a><\/noindex>. Ai ishte mjaft popullor n\u00eb koh\u00ebn e tij. Aty ishte e mundur t\u00eb monitoroheshin shum\u00eb gj\u00ebra t\u00eb r\u00ebnd\u00ebsishme, jo vet\u00ebm Linux, por edhe komponent\u00eb t\u00eb ndrysh\u00ebm.<\/p>\n

<\/p>\n

Tani merrem me PostgreSQL. Po shkruaj nj\u00eb gj\u00eb tjet\u00ebr q\u00eb lejon pun\u00ebn me statistikat e PostgreSQL. E quajtur pgCenter<\/a><\/noindex> (artikulli n\u00eb habr\u00eb \u2014 Statistika e Postgres pa nerva dhe stres<\/a><\/noindex>). <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Nj\u00eb hyrje e shkurt\u00ebr. \u00c7far\u00eb situatash hasin klient\u00ebt tan\u00eb? Duke ndodhur nj\u00eb aksident q\u00eb lidhet me baz\u00ebn e t\u00eb dh\u00ebnave. Dhe kur \u00ebsht\u00eb rikuperuar baza e t\u00eb dh\u00ebnave, shefi i departamentit ose shefi i zhvillimit thot\u00eb: \"Miq, 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 ndodhin gj\u00ebra t\u00eb tilla\". Dhe k\u00ebtu fillon nj\u00eb proces interesant i zgjedhjes s\u00eb sistemit t\u00eb monitorimit ose adaptimit t\u00eb nj\u00eb sistemi monitorimi ekzistues, n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb mund t\u00eb monitorojm\u00eb baz\u00ebn ton\u00eb t\u00eb t\u00eb dh\u00ebnave - PostgreSQL, MySQL ose ndonj\u00eb tjet\u00ebr. Dhe koleg\u00ebt fillojn\u00eb t\u00eb propozojn\u00eb: \"Kam d\u00ebgjuar se ekziston nj\u00eb baz\u00eb e till\u00eb t\u00eb dh\u00ebnash. Le t\u00eb p\u00ebrdorim at\u00eb\". Koleg\u00ebt fillojn\u00eb t\u00eb diskutojn\u00eb mes tyre. N\u00eb fund, p\u00ebrfundojm\u00eb duke zgjedhur nj\u00eb ndonj\u00eb baz\u00eb t\u00eb dh\u00ebnash, por monitorimi i PostgreSQL n\u00eb t\u00eb \u00ebsht\u00eb mjaft i dob\u00ebt dhe gjithmon\u00eb detyrohemi t\u00eb b\u00ebjm\u00eb disa rregullime. T\u00eb marrim disa depo nga GitHub, t'i klonojm\u00eb, t\u00eb adaptojm\u00eb skriptet, t'i konfigurojm\u00eb s\u00ebrish. Dhe n\u00eb fund, kjo p\u00ebrfundon n\u00eb nj\u00eb pun\u00eb manuale. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Prandaj, n\u00eb k\u00ebt\u00eb referat do t\u00eb p\u00ebrpiqem t'ju jap disa dije se si t\u00eb zgjidhni monitorimin jo vet\u00ebm p\u00ebr PostgreSQL, por edhe p\u00ebr bazat e t\u00eb dh\u00ebnave. Dhe t\u00eb jap ato dije q\u00eb do t'ju lejojn\u00eb t\u00eb p\u00ebrmir\u00ebsoni monitorimin tuaj, n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb merrni ndonj\u00eb p\u00ebrfitim prej tij, q\u00eb t\u00eb mund t\u00eb monitoroni baz\u00ebn tuaj t\u00eb t\u00eb dh\u00ebnave n\u00eb m\u00ebnyr\u00eb t\u00eb dobishme, p\u00ebr t\u00eb paralajm\u00ebruar n\u00eb koh\u00eb p\u00ebr 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 referat, mund t'i adaptoni drejtp\u00ebrdrejt p\u00ebr \u00e7do baz\u00eb t\u00eb dh\u00ebnash, qoft\u00eb kjo DBMS ose noSQL. Pra, k\u00ebtu nuk \u00ebsht\u00eb vet\u00ebm PostgreSQL, por do t\u00eb ket\u00eb shum\u00eb receta se si ta b\u00ebni k\u00ebt\u00eb n\u00eb PostgreSQL. Do t\u00eb ket\u00eb shembuj t\u00eb pyetjeve, shembuj t\u00eb entiteteve q\u00eb ekzistojn\u00eb n\u00eb PostgreSQL p\u00ebr monitorim. Dhe n\u00ebse DBMS juaj ka gj\u00ebra t\u00eb tilla q\u00eb lejojn\u00eb t'i futni ato n\u00eb monitorim, ju gjithashtu mund t'i adaptoni, t'i shtoni dhe do t\u00eb ishte mir\u00eb.<\/p>\n

<\/p>\n

\"BazatN\u00eb referat nuk do t\u00eb
\nflas p\u00ebr m\u00ebnyr\u00ebn se si t\u00eb dor\u00ebzoni dhe ruani metrikat. Nuk do t\u00eb flas p\u00ebr p\u00ebrpunimin post-dat\u00eb dhe ofrimin e tyre p\u00ebr p\u00ebrdoruesin. Dhe nuk do t\u00eb flas p\u00ebr alarmin.
\nGjat\u00eb rrug\u00ebtimit, do t\u00eb tregoj foto t\u00eb ndryshme t\u00eb monitorimeve ekzistuese dhe do t'i kritikoj ato. Sidoqoft\u00eb, do t\u00eb p\u00ebrpiqem t\u00eb mos p\u00ebrmend markat, p\u00ebr t\u00eb mos krijuar reklam\u00eb ose anti-reklam\u00eb p\u00ebr k\u00ebto produkte. Prandaj, t\u00eb gjitha p\u00ebrputhjet jan\u00eb t\u00eb rastit dhe mbeten n\u00eb imagjinat\u00ebn tuaj.
\n\"Bazat
\nFillimisht, le t\u00eb sqarojm\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 \u00ebsht\u00eb nj\u00eb produkt biznesi dhe nuk ndikon drejtp\u00ebrdrejt n\u00eb fitimin e kompanis\u00eb, prandaj zakonisht i jepet koh\u00eb monitoringut n\u00eb m\u00ebnyr\u00eb t\u00eb mbetur. N\u00ebse kemi koh\u00eb, merremi me monitorimin, n\u00ebse nuk kemi koh\u00eb, OK, do ta vendosim n\u00eb backlog dhe ndonj\u00ebher\u00eb do t\u00eb kthehemi te k\u00ebto detyra. <\/p>\n

<\/p>\n

Prandaj, nga praktika jon\u00eb, kur shkojm\u00eb te klient\u00ebt, monitorimi shpesh \u00ebsht\u00eb i pab\u00ebr\u00eb mir\u00eb dhe nuk ka disa gj\u00ebra interesante q\u00eb do t\u00eb na ndihmonin ta b\u00ebnim pun\u00ebn m\u00eb t\u00eb mir\u00eb me baz\u00ebn e t\u00eb dh\u00ebnave. Prandaj, monitorimi duhet gjithmon\u00eb t\u00eb p\u00ebrmir\u00ebsohet. <\/p>\n

<\/p>\n

Baza e t\u00eb dh\u00ebnave jan\u00eb gj\u00ebra kaq t\u00eb komplikuara q\u00eb gjithashtu duhet t\u00eb monitorohen, sepse bazat e t\u00eb dh\u00ebnave jan\u00eb magazina informacioni. Dhe informata \u00ebsht\u00eb shum\u00eb e r\u00ebnd\u00ebsishme p\u00ebr kompanin\u00eb, nuk duhet t\u00eb humbas\u00eb kurr\u00eb. Por, n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb, bazat e t\u00eb dh\u00ebnave jan\u00eb pjes\u00eb shum\u00eb komplekse t\u00eb softuerit. Ato p\u00ebrb\u00ebhen nga nj\u00eb num\u00ebr t\u00eb madh komponent\u00ebsh. Dhe shum\u00eb nga k\u00ebta komponent\u00eb duhet t\u00eb monitorohen. <\/p>\n

<\/p>\n

\"BazatN\u00ebse flasim konkretisht p\u00ebr PostgreSQL, mund ta paraqesim at\u00eb si nj\u00eb skem\u00eb q\u00eb p\u00ebrb\u00ebhet nga nj\u00eb num\u00ebr t\u00eb madh komponent\u00ebsh. K\u00ebta komponent\u00eb bashk\u00ebpunojn\u00eb me nj\u00ebri-tjetrin. Dhe n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb, n\u00eb PostgreSQL ka, ashtu t\u00eb quajtur\u00ebn, n\u00ebn-sistemi i Stats Collector, i cili lejon grumbullimin e statistikave mbi pun\u00ebn e k\u00ebtyre n\u00ebn-sistemeve 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 statistika paraqiten n\u00eb form\u00ebn e nj\u00eb grupi funksionesh dhe pamjesh (views). Ato mund t\u00eb quhen gjithashtu tabelat. Pra, me ndihm\u00ebn e nj\u00eb klienti t\u00eb zakonsh\u00ebm psql, mund t\u00eb lidheni me baz\u00ebn e t\u00eb dh\u00ebnave, t\u00eb b\u00ebni nj\u00eb select n\u00eb k\u00ebto funksione dhe pamje, dhe t\u00eb merrni tashm\u00eb disa numra konkret\u00eb mbi pun\u00ebn e n\u00ebn-sistemeve t\u00eb PostgreSQL. <\/p>\n

<\/p>\n

Mund t'i shtoni k\u00ebta numra n\u00eb sistemin tuaj t\u00eb preferuar t\u00eb monitorimit, t\u00eb vizatoni grafika, t\u00eb shtoni funksione dhe t\u00eb merrni analitik\u00eb n\u00eb perspektiv\u00eb afatgjat\u00eb. <\/p>\n

<\/p>\n

Por k\u00ebt\u00eb raport nuk do t\u00eb shqyrtoj t\u00eb gjitha k\u00ebto funksione n\u00eb m\u00ebnyr\u00eb t\u00eb p\u00ebrgjithshme, sepse do t\u00eb ishte nj\u00eb dit\u00eb e t\u00ebr\u00eb. Do t\u00eb p\u00ebrmend vet\u00ebm dy, tre ose kat\u00ebr aspekte dhe do t\u00eb flas se si ato ndihmojn\u00eb n\u00eb p\u00ebrmir\u00ebsimin e monitorimit.
\n\"Bazat
\nDhe kur flasim p\u00ebr monitorimin e baz\u00ebs, \u00e7far\u00eb duhet t\u00eb monitorsh? S\u00eb pari, duhet t\u00eb monitorohet disponueshm\u00ebria, 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 cil\u00ebsis\u00eb dhe sasis\u00eb s\u00eb saj. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Po ashtu, duhet t\u00eb monitorojm\u00eb klient\u00ebt q\u00eb lidhen me baz\u00ebn ton\u00eb, sepse ata mund t\u00eb jen\u00eb si klient\u00eb normal\u00eb, ashtu edhe klient\u00eb t\u00eb d\u00ebmsh\u00ebm q\u00eb mund t\u2019i shkaktojn\u00eb d\u00ebm t\u00eb dh\u00ebnave. Edhe ata duhet t\u00eb monitorohen dhe t\u00eb ndiqen aktivitetet 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, prandaj na duhet t\u00eb monitorojm\u00eb gjithashtu se si klient\u00ebt punojn\u00eb me t\u00eb dh\u00ebnat: me cilat tabela, n\u00eb mas\u00eb t\u00eb vog\u00ebl me cilat indekse. K\u00ebshtu, na nevojitet 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, natyrisht, nga k\u00ebrkesat. Aplikacionet lidhen me baz\u00ebn, i drejtohen t\u00eb dh\u00ebnave p\u00ebrmes k\u00ebrkesave, prandaj \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsohet se cilat k\u00ebrkesa kemi n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave, t\u00eb monitorohet adekuat\u00ebsia e tyre, q\u00eb t\u00eb mos jen\u00eb t\u00eb shkruara keq, q\u00eb disa opsione duhet t\u00eb rishkruhen p\u00ebr t'u realizuar m\u00eb shpejt dhe me m\u00eb shum\u00eb performanc\u00eb. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Dhe pasi flasim p\u00ebr baz\u00ebn e t\u00eb dh\u00ebnave, baza e t\u00eb dh\u00ebnave gjithmon\u00eb ka procese n\u00eb sfond. Proceset n\u00eb sfond ndihmojn\u00eb n\u00eb mbajtjen e performanc\u00ebs s\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave n\u00eb nj\u00eb nivel t\u00eb mir\u00eb, prandaj 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 p\u00ebrputhen me burimet e k\u00ebrkesave t\u00eb klient\u00ebve, prandaj nj\u00eb pun\u00eb e etur p\u00ebr burimet e proceseve n\u00eb sfond 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 ndiqen, p\u00ebr t\u00eb siguruar q\u00eb nuk ka asnj\u00eb p\u00ebr\u00e7arje n\u00eb proceset n\u00eb sfond. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Dhe gjith\u00eb kjo lidhur me monitorimin e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave mbetet n\u00eb metrikat sistematike. Por duke pasur parasysh se m\u00eb shum\u00eb se gjith\u00e7ka infrastruktura jon\u00eb po kalon n\u00eb cloud, metrikat sistematike t\u00eb nj\u00eb host-i t\u00eb ve\u00e7ant\u00eb gjithmon\u00eb kalojn\u00eb n\u00eb plan t\u00eb dyt\u00eb. Megjithat\u00eb, n\u00eb bazat e t\u00eb dh\u00ebnave ato ende jan\u00eb t\u00eb r\u00ebnd\u00ebsishme dhe monitorimi i metrikave sistematike, sigurisht, \u00ebsht\u00eb gjithashtu i nevojsh\u00ebm. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Me metrikat sistematike \u00ebsht\u00eb m\u00eb shum\u00eb se m\u00ebse mir\u00eb, t\u00eb gjitha sistemet moderne t\u00eb monitorimit tashm\u00eb mb\u00ebshtesin k\u00ebto metrika, por n\u00eb p\u00ebrgjith\u00ebsi, disa komponente megjithat\u00eb na mungojn\u00eb dhe disa gj\u00ebra duhen shtuar. P\u00ebr to un\u00eb do t\u00eb flas gjithashtu, disa slajde do t\u00eb jen\u00eb p\u00ebr to. <\/p>\n

<\/p>\n

\"Bazat
\nPika e par\u00eb e planit \u00ebsht\u00eb disponibiliteti. \u00c7far\u00eb \u00ebsht\u00eb disponibiliteti? Disponibiliteti n\u00eb kupim t\u00eb qytetar\u00ebve \u00ebsht\u00eb aft\u00ebsia e baz\u00ebs p\u00ebr t\u00eb sh\u00ebrbyer lidhjet, dmth. baza \u00ebsht\u00eb aktive, si nj\u00eb sh\u00ebrbim, pranon lidhje nga klient\u00ebt. Dhe ky disponibilitet mund t\u00eb vler\u00ebsohet me disa karakteristika. K\u00ebto karakteristika \u00ebsht\u00eb shum\u00eb e p\u00ebrshtatshme t'i vendosim n\u00eb dashboard. <\/p>\n

<\/p>\n

\"Bazat
\nT\u00eb gjith\u00eb e din\u00eb se \u00e7far\u00eb jan\u00eb dashboardet. \u00cbsht\u00eb kur shikon nj\u00eb her\u00eb ekranin, n\u00eb t\u00eb cilin \u00ebsht\u00eb p\u00ebrmbledhur informacioni i nevojsh\u00ebm. Dhe ju mund ta p\u00ebrcaktoni menj\u00ebher\u00eb \u2013 a ka nj\u00eb problem n\u00eb baz\u00eb apo jo.
\nP\u00ebr pasoj\u00eb, disponibiliteti i baz\u00ebs s\u00eb t\u00eb dh\u00ebnave dhe karakteristikat e tjera ky\u00e7e gjithmon\u00eb duhet t\u00eb vendosen n\u00eb dashboard, n\u00eb m\u00ebnyr\u00eb q\u00eb kjo informacion t\u00eb jet\u00eb p\u00ebr dor\u00eb, t\u00eb jet\u00eb gjithmon\u00eb af\u00ebr jush. Disa detaje t\u00eb tjera, t\u00eb cilat ndihmojn\u00eb gjat\u00eb hetimeve t\u00eb incidenteve, gjat\u00eb hetimeve t\u00eb situatave emergjente, ato tashm\u00eb duhet t'i vendosen n\u00eb dashboardet e dyta, ose t\u00eb fshihen n\u00eb linke drilldown, t\u00eb cilat \u00e7ojn\u00eb n\u00eb sisteme monitorimi 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 mrekulluesh\u00ebm monitorimi. Ai mbledh shum\u00eb t\u00eb dh\u00ebna, por sipas mendimit tim, ka nj\u00eb koncept t\u00eb \u00e7uditsh\u00ebm t\u00eb dashboardeve. Aty ka nj\u00eb lidhje 'krijo dashboard'. Por kur krijoni nj\u00eb dashboard, krijoni nj\u00eb list\u00eb, e cila p\u00ebrb\u00ebhet nga dy kolona, nj\u00eb list\u00eb grafike. Dhe kur ju nevojitet t\u00eb shihni di\u00e7ka, filloni t\u00eb klikoni me mouse, t\u00eb rrotulloni, t\u00eb k\u00ebrkoni grafikun e duhur. Dhe kjo k\u00ebrkon koh\u00eb, dometh\u00ebn\u00eb, p\u00ebr dashboarde si t\u00eb tilla, nuk ka. Ka vet\u00ebm lista grafik\u00ebsh.<\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

\u00c7far\u00eb duhet t\u00eb shtojm\u00eb n\u00eb k\u00ebto panel? Mund t\u00eb fillojm\u00eb me nj\u00eb karakteristik\u00eb si koha e p\u00ebrgjigjes. N\u00eb PostgreSQL ekziston nj\u00eb pamje pg_stat_statements. Sipas parazgjedhjes ajo \u00ebsht\u00eb e \u00e7aktivizuar, por kjo \u00ebsht\u00eb nj\u00eb nga pamjet sistemore t\u00eb r\u00ebnd\u00ebsishme q\u00eb gjithmon\u00eb duhet aktivizuar dhe p\u00ebrdorur. Ajo ruan informacion mbi t\u00eb gjitha pyetjet e ekzekutuara q\u00eb jan\u00eb kryer n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave. <\/p>\n

<\/p>\n

P\u00ebr rrjedhoj\u00eb, mund t\u00eb fillojm\u00eb nga ai q\u00eb mund t\u00eb marrim koh\u00ebn e p\u00ebrgjithshme t\u00eb ekzekutimit t\u00eb t\u00eb gjitha k\u00ebrkesave dhe ta ndajm\u00eb me numrin e k\u00ebrkesave duke p\u00ebrdorur fushat e sip\u00ebrp\u00ebrmendura. Mir\u00ebpo, kjo \u00ebsht\u00eb nj\u00eb temperatur\u00eb mesatare p\u00ebr spitalin. Mund t\u00eb fillojm\u00eb nga fusha t\u00eb tjera \u2013 koha minimale e ekzekutimit t\u00eb k\u00ebrkesave, maksimumi dhe median. Madje mund t\u00eb nd\u00ebrtosh percentilet, n\u00eb PostgreSQL ka funksione p\u00ebr k\u00ebt\u00eb. Dhe mund t\u00eb marrim disa numra q\u00eb karakterizojn\u00eb koh\u00ebn e p\u00ebrgjigjes s\u00eb baz\u00ebs son\u00eb p\u00ebr k\u00ebrkesat e kryera, dmth. nuk ekzekutojm\u00eb nj\u00eb k\u00ebrkes\u00eb false 'select 1' dhe shohim koh\u00ebn e p\u00ebrgjigjes, por analizojm\u00eb koh\u00ebn e p\u00ebrgjigjeve p\u00ebr k\u00ebrkesat e kryera dhe e vizatojm\u00eb ose si nj\u00eb num\u00ebr t\u00eb ve\u00e7ant\u00eb, ose e p\u00ebrpjekim n\u00eb nj\u00eb grafik. <\/p>\n

<\/p>\n

Po ashtu \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb ndjekim numrin e gabimeve q\u00eb 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 orientoheni n\u00eb fush\u00ebn xact_rollback. Kjo fush\u00eb tregon jo vet\u00ebm numrin e rollbakeve q\u00eb ndodhin n\u00eb baz\u00eb, por merr parasysh edhe numrin e gabimeve. N\u00eb m\u00ebnyr\u00eb konvencionale, ne mund ta paraqesim k\u00ebt\u00eb num\u00ebr n\u00eb panelin ton\u00eb dhe t\u00eb shohim sa gabime kemi n\u00eb k\u00ebt\u00eb moment. N\u00ebse ka shum\u00eb gabime, k\u00ebto jan\u00eb nj\u00eb shenj\u00eb e mir\u00eb p\u00ebr t\u00eb shqyrtuar skedar\u00ebt e log\u00ebve dhe t\u00eb shohim se \u00e7far\u00eb jan\u00eb k\u00ebto gabime dhe pse ndodhin, dhe pastaj t\u00eb investigojm\u00eb dhe t\u2019i zgjidhim ato.<\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Mund t\u00eb shtojm\u00eb nj\u00eb gj\u00eb si Takhomet\u00ebr. Kjo \u00ebsht\u00eb numri i transaksioneve p\u00ebr sekond\u00eb dhe numri i pyetjeve p\u00ebr sekond\u00eb. N\u00eb m\u00ebnyr\u00eb konvencionale, ju mund t\u00eb p\u00ebrdorni k\u00ebto numra si performanc\u00ebn aktuale t\u00eb baz\u00ebs suaj t\u00eb t\u00eb dh\u00ebnave dhe t\u00eb vini re n\u00ebse ka pika maksimale t\u00eb pyetjeve, pika maksimale t\u00eb transaksioneve ose, p\u00ebrkundrazi, n\u00ebse baza \u00ebsht\u00eb n\u00ebn-ngarkuar, sepse ndonj\u00eb backend ka d\u00ebshtuar. Ky num\u00ebr \u00ebsht\u00eb i r\u00ebnd\u00ebsish\u00ebm p\u00ebr t'u par\u00eb gjithmon\u00eb dhe p\u00ebr t\u00eb mbajtur mend se p\u00ebr projektin ton\u00eb kjo performanc\u00eb \u00ebsht\u00eb normale, nd\u00ebrsa vlerat m\u00eb larta ose m\u00eb t\u00eb ul\u00ebta jan\u00eb disa shqet\u00ebsime dhe t\u00eb paqarta, dhe do t\u00eb thot\u00eb se duhet t\u00eb shohim pse k\u00ebto numra jan\u00eb k\u00ebshtu.<\/p>\n

<\/p>\n

P\u00ebr t\u00eb vler\u00ebsuar numrin e transaksioneve, mund t\u00eb referohemi p\u00ebrs\u00ebri te pamja pg_stat_database. Mund t\u00eb mbledhim numrin e commit dhe numrin e rollback dhe t\u00eb marrim numrin e transaksioneve p\u00ebr sekond\u00eb. <\/p>\n

<\/p>\n

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

<\/p>\n

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

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Mund t\u00eb shtoni metrika t\u00eb tjera sipas d\u00ebshires, t\u00eb cilat ndihmojn\u00eb gjithashtu n\u00eb vler\u00ebsimin e disponueshm\u00ebris\u00eb s\u00eb baz\u00ebs son\u00eb dhe ndjekjen \u2013 n\u00ebse nuk kishte ndonj\u00eb downtime. <\/p>\n

<\/p>\n

Nj\u00eb nga k\u00ebto metrika \u00ebsht\u00eb uptime. Por uptime n\u00eb PostgreSQL \u00ebsht\u00eb nj\u00eb gj\u00eb paksa e komplikuar. Do t\u00eb tregoj pse. Kur PostgreSQL fillon, fillon t\u00eb num\u00ebroj\u00eb uptime. Por n\u00ebse n\u00eb ndonj\u00eb moment, p\u00ebr shembull, gjat\u00eb nat\u00ebs, ekzekutohej ndonj\u00eb detyr\u00eb, vjen OOM-killer dhe detyron p\u00ebrfundimin e procesit bashk\u00ebpunues t\u00eb PostgreSQL, n\u00eb k\u00ebt\u00eb rast PostgreSQL p\u00ebrfundon lidhjet e t\u00eb gjith\u00eb klient\u00ebve, heq zon\u00ebn e memories shp\u00ebrndar\u00ebse dhe fillon rikuperimin nga pika e kontrollit m\u00eb t\u00eb fundit. Dhe nd\u00ebrsa zgjat ky rikuperim nga pika e kontrollit, baza nuk pranon lidhje, dmth. kjo situat\u00eb mund t\u00eb vler\u00ebsohet si downtime. Por megjithat\u00eb, num\u00ebruesi i uptime nuk do t\u00eb zer\u00eb, sepse llogarit koh\u00ebn e nisjes s\u00eb postmaster q\u00eb nga momenti i par\u00eb. Prandaj situata t\u00eb tilla mund t\u00eb kalohen.<\/p>\n

<\/p>\n

Gjithashtu duhet t\u00eb monitoroni numrin e pun\u00ebtor\u00ebve t\u00eb vakumit. T\u00eb gjith\u00eb e din\u00eb se \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, jan\u00eb b\u00ebr\u00eb shum\u00eb prezantime. Ka shum\u00eb diskutime rreth vakumit, se si duhet t\u00eb funksionoj\u00eb. Shum\u00eb e shohin si nj\u00eb t\u00eb keqe t\u00eb pashmangshme. Por ashtu \u00ebsht\u00eb. \u00cbsht\u00eb nj\u00eb analog i mbledh\u00ebsit t\u00eb plehrave, i cili pastron versionet e vjetra t\u00eb rreshtave, t\u00eb cilat nuk i nevojiten asnj\u00eb nga transaksionet dhe lirojn\u00eb 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\u00ebm. Ai merr shum\u00eb burime dhe k\u00ebrkesat e klient\u00ebve fillojn\u00eb t\u00eb vuajn\u00eb nga kjo. <\/p>\n

<\/p>\n

Dhe monitorimi i tij duhet b\u00ebr\u00eb p\u00ebrmes pamjes pg_stat_activity, p\u00ebr t\u00eb cil\u00ebn do t\u00eb flas n\u00eb seksionin e ardhsh\u00ebm. Kjo pamje tregon aktivitetin aktual n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave. N\u00ebp\u00ebrmjet k\u00ebtij aktiviteti, ne mund t\u00eb ndjekim numrin e vakumeve q\u00eb punojn\u00eb aktualisht. Mund t\u00eb ndjekim vakumet dhe t\u00eb shohim se n\u00ebse kemi tejkaluar kufirin, at\u00ebher\u00eb kjo \u00ebsht\u00eb nj\u00eb arsye p\u00ebr t\u00eb kontrolluar cil\u00ebsimet e PostgreSQL dhe si t\u00eb optimizojm\u00eb pun\u00ebn e vakumit. <\/p>\n

<\/p>\n

Nj\u00eb tjet\u00ebr ve\u00e7ori e PostgreSQL \u00ebsht\u00eb se ai ndikohet shum\u00eb keq nga transaksionet e gjata. Sidomos nga transaksionet q\u00eb q\u00ebndrojn\u00eb gjat\u00eb dhe nuk b\u00ebjn\u00eb asgj\u00eb. K\u00ebto quhen stat idle-in-transaction. Nj\u00eb transaksion i till\u00eb mban bllokime dhe pengon pun\u00ebn e vakumit. Si rezultat, tabelat fryhen dhe rriten n\u00eb madh\u00ebsi. K\u00ebrkesat q\u00eb punojn\u00eb me k\u00ebto tabela fillojn\u00eb t\u00eb punojn\u00eb m\u00eb ngadal\u00eb, sepse duhet t\u00eb nxjerrin t\u00eb gjitha versionet e vjetra t\u00eb rreshtave nga memoria n\u00eb disk dhe p\u00ebrmbys.<\/strong> Prandaj, koha dhe gjat\u00ebsi e transaksioneve m\u00eb t\u00eb gjata, si dhe e k\u00ebrkesave m\u00eb t\u00eb gjata t\u00eb vakumit gjithashtu duhet t\u00eb monitorohet. Dhe n\u00ebse shohim disa procese q\u00eb kan\u00eb punuar shum\u00eb gjat\u00eb, m\u00eb shum\u00eb se 10-20-30 minuta p\u00ebr ngarkesat OLTP, at\u00ebher\u00eb duhet t\u00eb v\u00ebm\u00eb re dhe t'i mbyllim ato me forc\u00eb, ose t\u00eb optimizojm\u00eb aplikacionin q\u00eb ato t\u00eb mos thirren dhe t\u00eb mos ngelen kaq gjat\u00eb.<\/strong> P\u00ebr ngarkesat analitike, 10-20-30 minuta \u00ebsht\u00eb normale, ndodhin edhe m\u00eb t\u00eb gjata. <\/p>\n

<\/p>\n

\"Bazat
\nM\u00eb pas kemi mund\u00ebsin\u00eb me klient\u00ebt e lidhur. Kur tani kemi formuar nj\u00eb panel, e kemi publikuar at\u00eb me metrikat ky\u00e7e t\u00eb disponueshm\u00ebris\u00eb, mund t\u00eb shtojm\u00eb gjithashtu informacion shtes\u00eb mbi klient\u00ebt e lidhur. <\/p>\n

<\/p>\n

Informacioni mbi klient\u00ebt e lidhur \u00ebsht\u00eb i r\u00ebnd\u00ebsish\u00ebm, sepse, nga pik\u00ebpamja e PostgreSQL, klient\u00ebt jan\u00eb t\u00eb ndrysh\u00ebm. Ka klient\u00eb t\u00eb mir\u00eb dhe klient\u00eb t\u00eb k\u00ebqij. <\/p>\n

<\/p>\n

Nj\u00eb shembull i thjesht\u00eb. N\u00ebn klientin kuptoj aplikacionin. Aplikacioni \u00ebsht\u00eb lidhur me baz\u00ebn e t\u00eb dh\u00ebnave dhe fillon menj\u00ebher\u00eb t\u00eb d\u00ebrgoj\u00eb k\u00ebrkesat e tij, baza e t\u00eb dh\u00ebnave i p\u00ebrpunon ato dhe i ekzekuton, rezultatet kthehen te klienti. K\u00ebta jan\u00eb klient\u00eb t\u00eb mir\u00eb dhe t\u00eb duhur. <\/p>\n

<\/p>\n

Ka situata kur nj\u00eb klient lidhet, mban lidhjen, por nuk b\u00ebn asgj\u00eb. Ai ndodhet n\u00eb gjendjen idle. <\/p>\n

<\/p>\n

Porosite ke jan\u00eb edhe klient\u00eb t\u00eb k\u00ebqij. P\u00ebr shembull, nj\u00eb klient i till\u00eb u lidh, hapi nj\u00eb transaksion, b\u00ebri di\u00e7ka n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave dhe m\u00eb pas kaloi n\u00eb kod, le t\u00eb themi, p\u00ebr t'u lidhur me 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 mbylli transaksionin. Dhe transaksioni q\u00ebndron hapur n\u00eb baz\u00eb dhe mbajti bllokimin n\u00eb rresht. Kjo \u00ebsht\u00eb nj\u00eb gjendje e keqe. Dhe n\u00ebse ndonj\u00ebher\u00eb aplikacioni brenda ndonj\u00eb vendi bie p\u00ebr shkak t\u00eb nj\u00eb p\u00ebrjashtimi (Exception), at\u00ebher\u00eb transaksioni mund t\u00eb mbetet i hapur p\u00ebr nj\u00eb koh\u00eb t\u00eb gjat\u00eb. Kjo ndikon drejtp\u00ebrs\u00ebdrejti 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 monitoroni k\u00ebta klient\u00eb dhe t\u00eb mbyllni pun\u00ebn e tyre me forc\u00eb. Dhe duhet t\u00eb optimizoni aplikacionin tuaj p\u00ebr t\u00eb mos pasur situata t\u00eb tilla. <\/p>\n

<\/p>\n

Klient\u00ebt e tjer\u00eb t\u00eb k\u00ebqij jan\u00eb klient\u00ebt 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 n\u00eb pritje: mund t\u00eb hap\u00eb nj\u00eb transaksion, t\u00eb marr\u00eb bllokime n\u00eb disa rreshta, pastaj diku n\u00eb kod ai bie, duke l\u00ebn\u00eb pas nj\u00eb transaksion q\u00eb mbetet n\u00eb pritje. Nj\u00eb klient tjet\u00ebr vjen, k\u00ebrkon t\u00eb nj\u00ebjtat t\u00eb dh\u00ebna, por ai p\u00ebrballet me bllokimin, p\u00ebr shkak se ai transaksion q\u00eb pret tashm\u00eb mban bllokime n\u00eb disa rreshta t\u00eb nevojsh\u00ebm. Dhe transaksioni i dyt\u00eb do t\u00eb mbetet n\u00eb pritje p\u00ebr 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 akumulohen dhe t\u00eb tejkalojn\u00eb limitin e lidhjeve me baz\u00ebn e t\u00eb dh\u00ebnave. Dhe kur limiti tejkalohet, aplikacioni nuk mund t\u00eb punoj\u00eb m\u00eb me baz\u00ebn. Kjo tashm\u00eb \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 i monitorimit. Dhe k\u00ebtu kemi nj\u00eb panel t\u00eb k\u00ebndsh\u00ebm. Ka informacion mbi lidhjet sip\u00ebr. DB connection \u2013 8 cop\u00eb. Dhe kjo \u00ebsht\u00eb gjith\u00e7ka. Nuk kemi informacion se cil\u00ebt klient\u00eb jan\u00eb aktiv\u00eb, cil\u00ebt klient\u00eb thjesht jan\u00eb n\u00eb pritje, nuk b\u00ebjn\u00eb asgj\u00eb. Nuk ka informacion mbi transaksionet q\u00eb presin dhe lidhjet q\u00eb presin, dometh\u00ebn\u00eb, kjo \u00ebsht\u00eb nj\u00eb shif\u00ebr q\u00eb tregon numrin e lidhjeve dhe gjith\u00e7ka tjet\u00ebr. Tani gjejeni vet\u00eb.
\n\"Bazat
\nPrandaj, p\u00ebr ta shtuar k\u00ebt\u00eb informacion n\u00eb monitorim, duhet t\u00eb referoheni n\u00eb pamjen sistemore pg_stat_activity. N\u00ebse kaloni shum\u00eb koh\u00eb n\u00eb PostgreSQL, kjo \u00ebsht\u00eb nj\u00eb pamje shum\u00eb e mir\u00eb q\u00eb duhet t\u00eb b\u00ebhet miku juaj, sepse tregon aktivitetin aktual n\u00eb PostgreSQL, dmth \u00e7far\u00eb po ndodh aty. P\u00ebr \u00e7do proces ka nj\u00eb rresht t\u00eb ve\u00e7ant\u00eb, i cili tregon informacionin p\u00ebr at\u00eb proces: nga cili host \u00ebsht\u00eb b\u00ebr\u00eb lidhja, n\u00ebn cilin p\u00ebrdorues, n\u00ebn cilin em\u00ebr, kur \u00ebsht\u00eb nisur transaksioni, cili \u00ebsht\u00eb k\u00ebrkesa aktuale q\u00eb po ekzekutohet, cili ishte k\u00ebrkesa e fundit. Dhe, p\u00ebr rrjedhoj\u00eb, gjendjen e klientit mund ta vler\u00ebsojm\u00eb nga fusha 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 jan\u00eb aktualisht n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave dhe numrin e lidhjeve q\u00eb kan\u00eb k\u00ebt\u00eb stat n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave. Dhe numrat e marr\u00eb mund t'i d\u00ebrgojm\u00eb n\u00eb monitorimin ton\u00eb dhe t\u00eb vizatojm\u00eb grafa p\u00ebr ta.
\nPo ashtu, \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsojm\u00eb koh\u00ebzgjatjen e transaksionit. Kam folur tashm\u00eb se \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsojm\u00eb koh\u00ebzgjatjen e vakumeve, por edhe transaksionet jan\u00eb t\u00eb vler\u00ebsuara sakt\u00ebsisht nj\u00ebsoj. Ka fusha xact_start dhe query_start. Ato, n\u00eb terma t\u00eb thjesht\u00eb, tregojn\u00eb koh\u00ebn e fillimit t\u00eb transaksionit dhe koh\u00ebn e fillimit t\u00eb k\u00ebrkes\u00ebs. Ne marrim funksionin now(), i cili tregon mark\u00ebn aktuale t\u00eb koh\u00ebs dhe e zbritim timestamp-in e transaksionit dhe t\u00eb 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 p\u00ebrfundojm\u00eb ato. P\u00ebr ngarkesat OLTP, transaksionet e gjata jan\u00eb ato q\u00eb kalojn\u00eb 1-2-3 minuta.<\/strong>. P\u00ebr ngarkesat OLAP, transaksionet e gjata jan\u00eb normale, por n\u00ebse ato zgjasin m\u00eb shum\u00eb se dy or\u00eb, at\u00ebher\u00eb kjo \u00ebsht\u00eb gjithashtu nj\u00eb shenj\u00eb se diku kemi nj\u00eb problem.<\/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 referohen n\u00eb tabela, ata referohen n\u00eb indekse 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 t\u00eb pun\u00ebs dhe p\u00ebr t\u00eb kuptuar p\u00ebraf\u00ebrsisht se cilat tabela jan\u00eb m\u00eb \"t\u00eb nxehta\". P\u00ebr shembull, kjo \u00ebsht\u00eb e nevojshme n\u00eb situatat kur d\u00ebshirojm\u00eb t\u00eb vendosim \"tabelat e nxehta\" n\u00eb ndonj\u00eb ruajtje SSD t\u00eb shpejt\u00eb. P\u00ebr shembull, disa tabela arkivale q\u00eb nuk i p\u00ebrdorim m\u00eb prej koh\u00ebsh mund t\u00eb zhvendosen n\u00eb ndonj\u00eb \"arkiv t\u00eb ftoht\u00eb\", n\u00eb disqe SATA dhe le t\u00eb q\u00ebndrojn\u00eb atje, aksesimi do t\u00eb jet\u00eb vet\u00ebm kur \u00ebsht\u00eb e nevojshme. <\/p>\n

<\/p>\n

Gjithashtu, kjo \u00ebsht\u00eb e dobishme p\u00ebr zbulimin e anomali pas \u00e7do l\u00ebshimi dhe vendosjeje. Le t\u00eb themi, projekti ka l\u00ebshuar nj\u00eb funksion t\u00eb ri. P\u00ebr shembull, \u00ebsht\u00eb shtuar nj\u00eb funksionalitet i ri p\u00ebr pun\u00ebn me databaz\u00ebn. Dhe n\u00ebse nd\u00ebrtuojm\u00eb grafike t\u00eb p\u00ebrdorimit t\u00eb tabelave, ne do t\u00eb jemi n\u00eb gjendje t\u00eb zbulojm\u00eb leht\u00ebsisht k\u00ebto anomali n\u00eb k\u00ebto grafike. P\u00ebr shembull, shp\u00ebrthime t\u00eb update ose shp\u00ebrthime t\u00eb delete. Kjo do t\u00eb jet\u00eb shum\u00eb e dukshme.<\/p>\n

<\/p>\n

Gjithashtu, mund t\u00eb zbulojm\u00eb anomali t\u00eb \"statistikave t\u00eb l\u00ebshuara\". \u00c7far\u00eb do t\u00eb thot\u00eb kjo? N\u00eb PostgreSQL, planifikuesi i k\u00ebrkesave \u00ebsht\u00eb shum\u00eb i fort\u00eb dhe shum\u00eb i mir\u00eb. Dhe zhvilluesit i kushtojn\u00eb shum\u00eb koh\u00eb p\u00ebr ta zhvilluar at\u00eb. Si funksionon? P\u00ebr t\u00eb nd\u00ebrtuar plane t\u00eb mira, PostgreSQL, n\u00eb nj\u00eb interval t\u00eb caktuar kohor, mbledh statistik\u00eb mbi shp\u00ebrndarjen e t\u00eb dh\u00ebnave n\u00eb tabela. Kjo p\u00ebrfshin vlerat m\u00eb t\u00eb shpeshta: numri i vlerave unike, informacion rreth NULL n\u00eb tabel\u00eb, shum\u00eb informacion. <\/p>\n

<\/p>\n

N\u00eb baz\u00eb t\u00eb k\u00ebsaj statistike, planifikuesi nd\u00ebrtan disa k\u00ebrkesa, p\u00ebrzgjedh m\u00eb t\u00eb optimizuarin dhe p\u00ebrdor k\u00ebt\u00eb plan k\u00ebrkese p\u00ebr t\u00eb ekzekutuar vet\u00eb k\u00ebrkes\u00ebn dhe p\u00ebr t\u00eb kthyer t\u00eb dh\u00ebnat. <\/p>\n

<\/p>\n

Dhe ndodhin raste kur statistika \"l\u00ebviz\". Cil\u00ebsia dhe sasia e t\u00eb dh\u00ebnave jan\u00eb 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 rezultojn\u00eb jo optimale. N\u00ebse planet tona rezultojn\u00eb jo optimale p\u00ebr monitorimin e mbledhur, n\u00eb lidhje me tabelat, ne do t\u00eb jemi n\u00eb gjendje t\u00eb shohim k\u00ebto anomali. P\u00ebr shembull, ndoshta ndodhi ndonj\u00eb ndryshim cil\u00ebsor n\u00eb t\u00eb dh\u00ebna ku indeksi nisi t\u00eb p\u00ebrdor\u00eb kalimin e radh\u00ebs p\u00ebr t\u00eb kaluar n\u00eb tabel\u00eb, pra, n\u00ebse k\u00ebrkesa duhet t\u00eb kthej\u00eb vet\u00ebm 100 rreshta (ka kufizim limit 100), at\u00ebher\u00eb p\u00ebr k\u00ebt\u00eb k\u00ebrkes\u00eb do t\u00eb realizohet nj\u00eb p\u00ebrmbledhje e plot\u00eb. Dhe kjo gjithmon\u00eb ndikon shum\u00eb keq n\u00eb performanc\u00ebn. <\/p>\n

<\/p>\n

Dhe ne do t\u00eb jemi n\u00eb gjendje ta shohim k\u00ebt\u00eb n\u00eb monitorim. Dhe tashm\u00eb t\u00eb shohim k\u00ebt\u00eb k\u00ebrkes\u00eb, t\u00eb kryejm\u00eb nj\u00eb shpjegim p\u00ebr t\u00eb, t\u00eb mbledhim statistika, t\u00eb nd\u00ebrtojm\u00eb nj\u00eb indeks t\u00eb ri shtes\u00eb. Dhe tashm\u00eb t\u00eb reagojm\u00eb ndaj k\u00ebsaj problemi. Prandaj, kjo \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 kan\u00eb njohur, sepse \u00ebsht\u00eb shum\u00eb popullor. Kush e p\u00ebrdor n\u00eb projektet e tij. 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\u00eb nj\u00ebsi jan\u00eb t\u00eb dh\u00ebnat n\u00eb byte, pra aty jan\u00eb 5 grafika. K\u00ebto jan\u00eb Insert data, Update data, Delete data, Fetch data dhe Return data. Si nj\u00ebsi matjeje jan\u00eb dh\u00ebn\u00eb byte. Por \u00e7\u00ebshtja \u00ebsht\u00eb se statistika n\u00eb PostgreSQL kthen t\u00eb dh\u00ebnat n\u00eb tuple (rrjeshta). Dhe, p\u00ebr pasoj\u00eb, k\u00ebto grafic\u00eb jan\u00eb nj\u00eb m\u00ebnyr\u00eb shum\u00eb e mir\u00eb p\u00ebr t\u00eb minimizuar ngarkes\u00ebn tuaj p\u00ebr disa her\u00eb, p\u00ebr dhjet\u00ebra her\u00eb, sepse tuple nuk jan\u00eb byte, tuple \u00ebsht\u00eb nj\u00eb rrjesht, ajo p\u00ebrmban shum\u00eb byte dhe gjithmon\u00eb ka gjat\u00ebsi t\u00eb ndryshueshme. Pra, t\u00eb llogarit\u00ebsh ngarkes\u00ebn n\u00eb byte me an\u00eb t\u00eb tuples \u00ebsht\u00eb nj\u00eb detyr\u00eb e pamundur ose shum\u00eb e komplikuar. Prandaj, kur p\u00ebrdorni tabela t\u00eb logaritjes ose monitorim t\u00eb integruar, gjithmon\u00eb \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb kuptoni se ai funksionon si\u00e7 duhet dhe ju kthen t\u00eb dh\u00ebna t\u00eb vler\u00ebsuara sakt\u00ebsisht. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Si t\u00eb merrni statistika p\u00ebr k\u00ebto tabela? P\u00ebr k\u00ebt\u00eb, n\u00eb PostgreSQL ka nj\u00eb grup t\u00eb caktuar t\u00eb pamjeve. Dhe pamja kryesore \u00ebsht\u00eb pg_stat_user_tables<\/a><\/noindex>. User_tables do t\u00eb thot\u00eb se tabelat jan\u00eb krijuar n\u00eb em\u00ebr t\u00eb p\u00ebrdoruesit. N\u00eb kontrast, ka pamje sistemike, t\u00eb cilat p\u00ebrdoren vet\u00eb nga PostgreSQL. Dhe ka nj\u00eb tabel\u00eb p\u00ebrmbledh\u00ebse Alltables, e cila p\u00ebrfshin si sistemet ashtu edhe p\u00ebrdoruesit. Ju mund t\u00eb filloni nga cila do q\u00eb ju p\u00eblqen m\u00eb shum\u00eb.<\/p>\n

<\/p>\n

Sipas fushave t\u00eb m\u00ebsip\u00ebrme, mund t\u00eb vler\u00ebsoni numrin e insert, update dhe delete. Shembulli i tabel\u00ebs q\u00eb p\u00ebrdora, pik\u00ebrisht p\u00ebrdor k\u00ebto fusha p\u00ebr t\u00eb vler\u00ebsuar karakteristikat e ngarkes\u00ebs. Prandaj, ne gjithashtu mund t\u00eb themi nga ato. Por duhet mbajtur mend se k\u00ebto jan\u00eb tuples, jo byte, prandaj nuk mund t\u00eb marr\u00eb dhe ta b\u00ebj k\u00ebt\u00eb n\u00eb byte.<\/p>\n

<\/p>\n

Bazuar n\u00eb k\u00ebto t\u00eb dh\u00ebna, ne mund t\u00eb nd\u00ebrtojm\u00eb ato q\u00eb quhen tabela TopN. P\u00ebr shembull, Top-5, Top-10. Dhe mund t\u00eb ndjekim ato tabela 't\u00eb nxehta' q\u00eb jan\u00eb p\u00ebrdorur m\u00eb shum\u00eb se t\u00eb tjerat. P\u00ebr shembull, 5 tabelat 't\u00eb nxehta' p\u00ebr insert. Dhe n\u00ebp\u00ebrmjet k\u00ebtyre tabelave 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 vendosjeje. <\/p>\n

<\/p>\n

\u00cbsht\u00eb gjithashtu e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsohen p\u00ebrmasat e tabel\u00ebs, sepse ndonj\u00ebher\u00eb zhvilluesit nxjerrin nj\u00eb ve\u00e7ori t\u00eb re dhe tabelat tona fillojn\u00eb t\u00eb zmadhohen n\u00eb p\u00ebrmasa t\u00eb m\u00ebdha, sepse vendos\u00ebn t\u00eb shtojn\u00eb nj\u00eb volum t\u00eb shtuar t\u00eb t\u00eb dh\u00ebnave, por nuk parashikojn\u00eb se si do t\u00eb ndikoj\u00eb kjo n\u00eb madh\u00ebsin\u00eb e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Raste t\u00eb tilla gjithashtu na vijne si surpriza. <\/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 \u00ebsht\u00eb kjo. Cilat k\u00ebrkesa shkaktojn\u00eb ngarkes\u00ebn? Pra, nuk \u00ebsht\u00eb interesante t\u00eb shikosh proceset q\u00eb shkaktojn\u00eb ngarkes\u00ebn. \u00cbsht\u00eb e qart\u00eb se n\u00ebse host-i ka baz\u00ebn e t\u00eb dh\u00ebnave, at\u00ebher\u00eb aty \u00ebsht\u00eb e nisur baza e t\u00eb dh\u00ebnave dhe \u00ebsht\u00eb e qart\u00eb se vet\u00ebm bazat e t\u00eb dh\u00ebnave do t\u00eb shfryt\u00ebzohen aty. N\u00ebse hapim Top, do t\u00eb shohim list\u00ebn e proceseve n\u00eb PostgreSQL, t\u00eb cilat b\u00ebjn\u00eb di\u00e7ka. Nga Top nuk do t\u00eb kuptohet se \u00e7far\u00eb b\u00ebjn\u00eb. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Prandaj, \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 i k\u00ebrkesave, n\u00eb p\u00ebrgjith\u00ebsi, sjell m\u00eb shum\u00eb p\u00ebrfitim se tuning-u i konfigurimit t\u00eb PostgreSQL ose sistemit operativ, ose madje tuning-u i hardware-it. Nga vler\u00ebsimi im \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 rregullosh nj\u00eb k\u00ebrkes\u00eb sesa t\u00eb rregullosh konfigurimin, t\u00eb planifikosh nj\u00eb restart, sidomos n\u00ebse nuk \u00ebsht\u00eb e mundur t\u00eb rinis\u00ebsh baz\u00ebn, ose t\u00eb shtosh harduer. \u00cbsht\u00eb m\u00eb e leht\u00eb ndonj\u00ebher\u00eb t\u00eb rishkruash nj\u00eb k\u00ebrkes\u00eb ose 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, \u00ebsht\u00eb e nevojshme t\u00eb monitorohen k\u00ebrkesat dhe adekuat\u00ebsia e tyre. Le t\u00eb marrim nj\u00eb shembull tjet\u00ebr t\u00eb monitorimit. Dhe k\u00ebtu gjithashtu duket si nj\u00eb monitorim i shk\u00eblqyer. Ka informacion p\u00ebr replikimin, ka informacion p\u00ebr kapacitetin, bllokimet, shfryt\u00ebzimin e burimeve. E gjitha \u00ebsht\u00eb perfekte, por nuk ka informacion p\u00ebr k\u00ebrkesat. Nuk \u00ebsht\u00eb e qart\u00eb se cilat k\u00ebrkesa po kryhen n\u00eb baz\u00ebn ton\u00eb t\u00eb t\u00eb dh\u00ebnave, sa koh\u00eb zgjat ekzekutimi i tyre, sa jan\u00eb k\u00ebto k\u00ebrkesa. Na nevojitet gjithmon\u00eb t\u00eb kemi 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, mund t\u00eb p\u00ebrdorim modul nga pg_stat_statements. Bazuar n\u00eb t\u00eb, mund t\u00eb nd\u00ebrtojm\u00eb grafika t\u00eb ndryshme. P\u00ebr shembull, mund t\u00eb marrim informacion mbi k\u00ebrkesat m\u00eb t\u00eb shpeshta, pra ato k\u00ebrkesa q\u00eb ekzekutohen m\u00eb shpesh. Po, pas p\u00ebrdit\u00ebsimeve \u00ebsht\u00eb shum\u00eb e dobishme t\u00eb shohim k\u00ebto 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, pra ato k\u00ebrkesa q\u00eb ekzekutohen m\u00eb gjat\u00eb. Ato funksionojn\u00eb n\u00eb procesor, konsumojn\u00eb I\/O. K\u00ebt\u00eb mund ta vler\u00ebsojm\u00eb gjithashtu nga fushat total_time, mean_time, blk_write_time dhe blk_read_time. <\/p>\n

<\/p>\n

Mund t\u00eb vler\u00ebsojm\u00eb dhe t\u00eb monitorojm\u00eb k\u00ebrkesat m\u00eb t\u00eb nd\u00ebrlikuara n\u00eb aspektin e p\u00ebrdorimit t\u00eb resurseve, ato q\u00eb lexojn\u00eb nga disku, q\u00eb punojn\u00eb me memorie ose, p\u00ebrkundrazi, q\u00eb krijojn\u00eb ngarkes\u00eb shkrimi.<\/p>\n

<\/p>\n

Mund t\u00eb vler\u00ebsojm\u00eb k\u00ebrkesat m\u00eb bujare. K\u00ebto jan\u00eb k\u00ebrkesat 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 kufi. Ajo thjesht kthen t\u00eb gjith\u00eb p\u00ebrmbajtjen e tabel\u00ebs ose k\u00ebrkesat p\u00ebr tabelat e k\u00ebrkuara.<\/p>\n

<\/p>\n

Dhe gjithashtu mund t\u00eb monitorojm\u00eb k\u00ebrkesat q\u00eb p\u00ebrdorin skedar\u00eb p\u00ebrkoh\u00ebs apo tabela p\u00ebrkoh\u00ebse. <\/p>\n

<\/p>\n

\"Bazat
\nDhe na kan\u00eb mbetur proceset e prapm\u00eb. Proceset e prapm\u00eb jan\u00eb n\u00eb radh\u00eb t\u00eb par\u00eb kontrollet, ose si\u00e7 i quajn\u00eb ndryshe, pik\u00ebkontrolli, autovacuum dhe replikimi. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Nj\u00eb shembull tjet\u00ebr monitorimi. Ka sked\u00ebn Maintenance n\u00eb an\u00ebn e majt\u00eb, kalojm\u00eb n\u00eb t\u00eb dhe shpresojm\u00eb t\u00eb shohim di\u00e7ka t\u00eb dobishme. Por k\u00ebtu \u00ebsht\u00eb vet\u00ebm koha e funksionimit t\u00eb vakumit dhe mbledhjes s\u00eb statistikave, asgj\u00eb m\u00eb shum\u00eb. Kjo \u00ebsht\u00eb nj\u00eb informacion shum\u00eb i varf\u00ebr, prandaj gjithmon\u00eb duhet t\u00eb kemi informacion p\u00ebr m\u00ebnyr\u00ebn se si punojn\u00eb proceset e prapme n\u00eb baz\u00ebn ton\u00eb t\u00eb dh\u00ebnash dhe n\u00ebse ka ndonj\u00eb problem nga puna e tyre. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Kur shqyrtojm\u00eb pik\u00ebkontrollin, duhet t\u00eb mbajm\u00eb parasysh se pik\u00ebkontrolli shkarkon faqet \"e papastra\" nga zona e memories t\u00eb ndara n\u00eb disk, pastaj krijon nj\u00eb pik\u00ebkontroll. Dhe kjo pik\u00ebkontroll tani mund t\u00eb p\u00ebrdoret si nj\u00eb vend gjat\u00eb rikuperimit, n\u00ebse ndonj\u00ebher\u00eb PostgreSQL \u00ebsht\u00eb mbyllur n\u00eb m\u00ebnyr\u00eb t\u00eb paplanifikuar. <\/p>\n

<\/p>\n

Pra ndaj, p\u00ebr t\u00eb r\u00ebn\u00eb posht\u00eb t\u00eb gjitha faqet e \"ndotura\" n\u00eb disk, duhet t\u00eb kryhet nj\u00eb volum i caktuar shkrimi. Dhe, si rregull, n\u00eb sistemet me shum\u00eb memorie \u2013 kjo \u00ebsht\u00eb shum\u00eb. Dhe n\u00ebse b\u00ebrthamat tona b\u00ebhen shum\u00eb shpesh brenda nj\u00eb intervali t\u00eb shkurt\u00ebr, at\u00ebher\u00eb performanca e disku do t\u00eb bjer\u00eb shum\u00eb. Dhe k\u00ebrkesat e klient\u00ebve do t\u00eb vuajn\u00eb nga mungesa e burimeve. Ata do t\u00eb p\u00ebrpiqen p\u00ebr burimet dhe do t'u mungoj\u00eb performanca. <\/p>\n

<\/p>\n

Pra ndaj, n\u00ebp\u00ebrmjet pg_stat_bgwriter, p\u00ebr fushat e specifikuara, ne mund t\u00eb monitorojm\u00eb numrin e b\u00ebrthamave q\u00eb ndodhin. Dhe n\u00ebse ne kemi shum\u00eb b\u00ebrthama brenda nj\u00eb periudhe t\u00eb caktuar kohe (p\u00ebr 10-15-20 minuta, p\u00ebr nj\u00eb gjysm\u00eb ore), p\u00ebr shembull, 3-4-5, at\u00ebher\u00eb kjo mund t\u00eb jet\u00eb nj\u00eb problem. Dhe duhet t\u00eb shikojm\u00eb n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave, t\u00eb kontrollojm\u00eb konfigurimin, se \u00e7far\u00eb e shkakton k\u00ebt\u00eb abundenc\u00eb t\u00eb b\u00ebrthamave. Ndoshta ka ndonj\u00eb shkrim t\u00eb madh n\u00eb vijim. Nga ngarkesa e pun\u00ebs, ne mund ta vler\u00ebsojm\u00eb, sepse grafik\u00ebt e ngarkes\u00ebs s\u00eb pun\u00ebs jan\u00eb tashm\u00eb t\u00eb shtuar. Ne mund t\u00eb rregullojm\u00eb parametrat e pikave 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

Un\u00eb p\u00ebrs\u00ebri kthehem te autovacuum, sepse kjo \u00ebsht\u00eb nj\u00eb gj\u00eb, si\u00e7 kam th\u00ebn\u00eb m\u00eb par\u00eb, q\u00eb mund t\u00eb d\u00ebmtoj\u00eb leht\u00ebsisht performanc\u00ebn e disk\u00ebve dhe 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 t\u00eb autovacuum n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave \u00ebsht\u00eb i kufizuar. Me standard, ata jan\u00eb tre, prandaj n\u00ebse ne kemi gjithnj\u00eb tre punonj\u00ebs q\u00eb punojn\u00eb n\u00eb baz\u00eb, do t\u00eb thot\u00eb se autovacuum \u00ebsht\u00eb keqkonfiguruar, duhet t\u00eb rriten kufijt\u00eb, t\u00eb rishikohen parametrat e autovacuum dhe t\u00eb shohim me konfigurimin.
\n\u00cbsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb vler\u00ebsojm\u00eb se cil\u00ebt punonj\u00ebs t\u00eb vaksinimit po punojn\u00eb. Ose \u00ebsht\u00eb nisur nga nj\u00eb p\u00ebrdorues, DBA ka ardhur dhe ka nisur me duar ndonj\u00eb vaksin\u00eb, dhe kjo ka krijuar ngarkes\u00eb. Ne kemi ndonj\u00eb problem. Ose \u00ebsht\u00eb numri i vaksinave q\u00eb rrotullojn\u00eb numrin e transaksioneve. P\u00ebr disa versione t\u00eb PostgreSQL \u2013 k\u00ebto jan\u00eb vaksina shum\u00eb t\u00eb r\u00ebnda. Dhe ata mund t\u00eb d\u00ebmtojn\u00eb leht\u00ebsisht performanc\u00ebn, sepse ata lexojn\u00eb t\u00eb gjith\u00eb tabel\u00ebn n\u00eb t\u00ebr\u00ebsi, skanojn\u00eb t\u00eb gjitha bloket 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 periudh\u00eb t\u00eb gjat\u00eb, at\u00ebher\u00eb kjo do t\u00eb thot\u00eb se duhet t\u00eb hedhim nj\u00eb sy n\u00eb konfigurimin e vakuumit dhe ndoshta ta rishikojm\u00eb at\u00eb. Sepse mund t\u00eb ndodh\u00eb q\u00eb vakuumi t\u00eb punoj\u00eb mbi nj\u00eb tabel\u00eb p\u00ebr nj\u00eb periudh\u00eb t\u00eb gjat\u00eb (3-4 or\u00eb), por gjat\u00eb k\u00ebsaj kohe n\u00eb tabel\u00eb mund t\u00eb akumulohet p\u00ebrs\u00ebri nj\u00eb sasi e madhe e rreshtave t\u00eb vdekur. Dhe sapo t\u00eb p\u00ebrfundoj\u00eb vakuumi, \u00ebsht\u00eb e nevojshme q\u00eb p\u00ebrs\u00ebri t\u00eb vakuumoj\u00eb k\u00ebt\u00eb tabel\u00eb. Dhe arrijm\u00eb n\u00eb nj\u00eb situat\u00eb t\u00eb vakuumit t\u00eb pafund. N\u00eb k\u00ebt\u00eb rast, vakuumi nuk arrin t\u00eb kryej\u00eb pun\u00ebn e tij, dhe tabelat fillojn\u00eb gradualisht t\u00eb fryhen 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, ne gjithmon\u00eb e shqyrtojm\u00eb konfigurimin dhe p\u00ebrpiqemi ta optimizojm\u00eb, por pa e d\u00ebmtuar performanc\u00ebn e k\u00ebrkesave t\u00eb klient\u00ebve. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

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

<\/p>\n

Replikimi n\u00eb PostgreSQL \u00ebsht\u00eb organizuar p\u00ebrmes regjistrit t\u00eb transaksioneve. Masteri gjeneron regjistrin e transaksioneve. Regjistri i transaksioneve d\u00ebrgohet p\u00ebrmes nj\u00eb lidhjeje rrjetit n\u00eb replik\u00eb, dhe m\u00eb pas n\u00eb replik\u00eb ai riprodhohet. E thjesht\u00eb. <\/p>\n

<\/p>\n

P\u00ebr monitorimin e lagut t\u00eb replikimit p\u00ebrdoret pamja pg_stat_replication. Por me t\u00eb nuk \u00ebsht\u00eb gjith\u00e7ka e thjesht\u00eb. N\u00eb versionin 10, pamja ka p\u00ebsuar disa ndryshime. S\u00eb pari, disa fusha jan\u00eb riem\u00ebruar. Dhe disa fusha jan\u00eb shtuar. N\u00eb versionin 10 jan\u00eb shtuar fusha q\u00eb lejojn\u00eb vler\u00ebsimin e lagut t\u00eb replikimit n\u00eb sekonda. Kjo \u00ebsht\u00eb shum\u00eb e dobishme. Para versionit 10, kishte mund\u00ebsi p\u00ebr t\u00eb vler\u00ebsuar lagun e replikimit n\u00eb byte. Kjo mund\u00ebsi mbetet edhe n\u00eb versionin 10, pra mund t\u00eb zgjidhni se \u00e7far\u00eb ju \u00ebsht\u00eb m\u00eb e p\u00ebrshtatshme - t\u00eb vler\u00ebsoni lagun n\u00eb byte ose n\u00eb sekonda. Shum\u00eb e b\u00ebjn\u00eb t\u00eb dyja.<\/p>\n

<\/p>\n

Megjithat\u00eb, p\u00ebr t\u00eb vler\u00ebsuar lagun e replikimit, duhet t\u00eb dini pozitat e regjistrit n\u00eb transaksion. Dhe k\u00ebto pozita t\u00eb regjistrit t\u00eb transaksioneve jan\u00eb pik\u00ebrisht n\u00eb pamjen pg_stat_replication. N\u00eb m\u00ebnyr\u00eb t\u00eb thjesht\u00eb, me ndihm\u00ebn e funksionit pg_xlog_location_diff() ne mund t\u00eb marrim dy pika n\u00eb regjistrin e transaksioneve. T\u00eb llogarisim delta nd\u00ebrmjet tyre dhe t\u00eb marrim lagun e replikimit n\u00eb byte. Kjo \u00ebsht\u00eb shum\u00eb e dobishme dhe e thjesht\u00eb. <\/p>\n

<\/p>\n

N\u00eb versionin e 10-t\u00eb kjo funksionalitet u riem\u00ebrua n\u00eb pg_wal_lsn_diff(). N\u00eb t\u00ebr\u00ebsi, n\u00eb t\u00eb gjitha funksionet, pamjet, utilitar\u00ebt ku haset fjal\u00eb \"xlog\", ajo u z\u00ebvend\u00ebsua me \"wal\". Kjo ka ndodhur si n\u00eb pamje ashtu edhe n\u00eb funksione. Ky \u00ebsht\u00eb nj\u00eb ndryshim i r\u00ebnd\u00ebsish\u00ebm. <\/p>\n

<\/p>\n

P\u00ebr m\u00eb tep\u00ebr, n\u00eb versionin 10 jan\u00eb shtuar rreshta q\u00eb tregojn\u00eb specifikisht vones\u00ebn. K\u00ebto jan\u00eb write lag, flush lag, replay lag. Pra, \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb monitoroni k\u00ebto gj\u00ebra. N\u00ebse shohim se kemi vones\u00eb replikimi, m\u00eb pas duhet t\u00eb hetojm\u00eb pse ka ndodhur, nga ka ardhur dhe ta zgjidhim problemin. <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

Me metrikat sistemike pothuajse gjith\u00e7ka \u00ebsht\u00eb n\u00eb rregull. Kur krijohet \u00e7do monitorim, ai fillon me metrikat sistemike. Kjo p\u00ebrfshin shfryt\u00ebzimin e procesor\u00ebve, memory, swap, rrjetin dhe diskun. Megjithat\u00eb, shum\u00eb parametra nuk jan\u00eb aty default. <\/p>\n

<\/p>\n

N\u00ebse me shfryt\u00ebzimin e procesit gjith\u00e7ka \u00ebsht\u00eb n\u00eb rregull, at\u00ebher\u00eb me shfryt\u00ebzimin e diskut ka probleme. Zakonisht, zhvilluesit e monitorimeve shtojn\u00eb informacion rreth kapacitetit t\u00eb kalimit. Ky informacion mund t\u00eb jet\u00eb n\u00eb iops ose byte. Por ata harrojn\u00eb p\u00ebr latency dhe shfryt\u00ebzimin e pajisjeve diskore. K\u00ebto jan\u00eb parametra m\u00eb t\u00eb r\u00ebnd\u00ebsish\u00ebm q\u00eb lejojn\u00eb t\u00eb vler\u00ebsojm\u00eb sa e ngarkuar jan\u00eb diskun dhe sa ngadal\u00ebsohen ato. N\u00ebse kemi latency t\u00eb lart\u00eb, at\u00ebher\u00eb kjo tregon se ka disa probleme me diskun. N\u00ebse kemi shfryt\u00ebzim t\u00eb lart\u00eb, ajo tregon se disk\u00ebt nuk po e p\u00ebrballojn\u00eb ngarkes\u00ebn.<\/strong> K\u00ebto jan\u00eb karakteristika m\u00eb cil\u00ebsore se kapaciteti i kalimit.<\/p>\n

<\/p>\n

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

<\/p>\n

E nj\u00ebjta gj\u00eb vlen p\u00ebr nd\u00ebrfaqet rrjet\u00eb. Ka informacion rreth kapacitetit t\u00eb kalimit t\u00eb rrjetit n\u00eb paketa, n\u00eb byte, por megjithat\u00eb nuk ka informacion p\u00ebr latency dhe nuk ka informacion p\u00ebr shfryt\u00ebzimin, megjithat\u00eb kjo \u00ebsht\u00eb gjithashtu informacion i dobish\u00ebm.<\/strong> <\/p>\n

<\/p>\n

\"Bazat<\/p>\n

<\/p>\n

\u00c7do monitorim ka mang\u00ebsi. Dhe \u00e7far\u00ebdo monitorimi q\u00eb t\u00eb merrni, ai gjithmon\u00eb do t\u00eb jet\u00eb i pap\u00ebrshtatsh\u00ebm ndaj disa kritereve. Megjithat\u00eb, ato po zhvillohen, shtohen funksione t\u00eb reja, gj\u00ebra t\u00eb reja, prandaj zgjidhni di\u00e7ka dhe p\u00ebrmir\u00ebsoni. <\/p>\n

<\/p>\n

Dhe p\u00ebr t\u00eb p\u00ebrmir\u00ebsuar, duhet gjithmon\u00eb t\u00eb keni nj\u00eb ide se \u00e7far\u00eb do t\u00eb thot\u00eb statistika e dh\u00ebn\u00eb dhe si mund ta p\u00ebrdorni at\u00eb p\u00ebr t\u00eb zgjidhur probleme. <\/p>\n

<\/p>\n

Dhe disa pika ky\u00e7e:<\/p>\n

<\/p>\n