{"id":81089,"date":"2020-05-11T01:42:24","date_gmt":"2020-05-10T23:42:24","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/tipovye-oshibki-v-prilozheniyah-kotorye-vedut-k-bloat-v-postgresql-andrej-salnikov"},"modified":"2020-05-11T01:42:24","modified_gmt":"2020-05-10T23:42:24","slug":"tipovye-oshibki-v-prilozheniyah-kotorye-vedut-k-bloat-v-postgresql-andrej-salnikov","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/tipovye-oshibki-v-prilozheniyah-kotorye-vedut-k-bloat-v-postgresql-andrej-salnikov","title":{"rendered":"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>Ju ofroj t\u00eb njiheni me shpjegimin e raportit t\u00eb fillimit t\u00eb vitit 2016 nga Andrei Salnikov &quot;Gabimet standarde n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql&quot;<\/strong><\/p>\n<p><\/p>\n<p>N\u00eb k\u00ebt\u00eb prezantim do t\u00eb shqyrtoj gabimet kryesore n\u00eb aplikacione q\u00eb shfaqen n\u00eb faz\u00ebn e projektimit dhe shkrimit t\u00eb kodit t\u00eb aplikacionit. Do t\u00eb ndalem vet\u00ebm te ato gabime q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Zakonisht, ky \u00ebsht\u00eb fillimi i fundit t\u00eb performanc\u00ebs s\u00eb sistemit tuaj n\u00eb t\u00ebr\u00ebsi, edhe pse n\u00eb fillim nuk dukej se kishte asnj\u00eb shenj\u00eb paralajm\u00ebruese.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/a9e199bfe2e01c76966b32868790f8f0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><noindex><a rel=\"nofollow\" name=\"habracut\"><\/a><\/noindex><\/p>\n<p>P\u00ebrsh\u00ebndetje t\u00eb gjith\u00ebve! Ky prezantim nuk \u00ebsht\u00eb aq teknik sa ai i m\u00ebparshmi nga kolegu im. Ai u drejtohet kryesisht zhvilluesve t\u00eb sistemeve backend, sepse kemi nj\u00eb num\u00ebr t\u00eb madh klient\u00ebsh. Dhe t\u00eb gjith\u00eb ata b\u00ebjn\u00eb t\u00eb nj\u00ebjtat gabime. Pik\u00ebrisht p\u00ebr to do t\u2019ju flas. Do t\u00eb shpjegoj se n\u00eb \u00e7far\u00eb pasojash serioze dhe t\u00eb d\u00ebmshme \u00e7ojn\u00eb k\u00ebto gabime. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/c6c84dbafc595ae3068cd8bf804ccee7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pse b\u00ebhen k\u00ebto gabime? Ato ndodhin p\u00ebr dy arsye: nga mend\u00ebsia \u201cndoshta funksionon k\u00ebshtu\u201d dhe nga mungesa e njohjes s\u00eb disa mekanizmave q\u00eb veprojn\u00eb n\u00eb nivelin midis baz\u00ebs s\u00eb t\u00eb dh\u00ebnave dhe aplikacionit, si edhe brenda vet\u00eb baz\u00ebs. <\/p>\n<p><\/p>\n<p>Do t\u2019ju sjell tre shembuj me ilustrime t\u00eb frikshme se si gjith\u00e7ka p\u00ebrkeq\u00ebsohet. Shkurt do t\u00eb tregoj edhe p\u00ebr mekanizmin q\u00eb vepron aty. Po ashtu do t\u00eb shpjegoj si t\u00eb p\u00ebrballeni me to kur tashm\u00eb kan\u00eb ndodhur dhe cilat metoda parandaluese duhen p\u00ebrdorur p\u00ebr t\u00eb shmangur gabimet. Do t\u00eb flas edhe p\u00ebr mjetet ndihm\u00ebse dhe do t\u00eb jap lidhje t\u00eb dobishme. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b0230d992f5b44f0e542df2e4b4d526f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kam p\u00ebrdorur nj\u00eb baz\u00eb t\u00eb dh\u00ebnash testuese, ku kisha dy tabela. Nj\u00ebra tabel\u00eb p\u00ebrmbante llogarit\u00eb e klient\u00ebve, nd\u00ebrsa tjetra operacionet mbi k\u00ebto llogari. Dhe me nj\u00eb periodicitet t\u00eb caktuar ne p\u00ebrdit\u00ebsojm\u00eb gjendjet n\u00eb k\u00ebto llogari.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/1d2a21278b095b07546e1bb870819dbf.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>T\u00eb dh\u00ebnat fillestare t\u00eb tabel\u00ebs: ajo \u00ebsht\u00eb mjaft e vog\u00ebl, 2 MB. Koha e p\u00ebrgjigjes s\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave dhe konkretisht e k\u00ebsaj tabele \u00ebsht\u00eb gjithashtu shum\u00eb e mir\u00eb. Edhe ngarkesa \u00ebsht\u00eb mjaft e lart\u00eb \u2013 2 000 operacione n\u00eb sekond\u00eb mbi tabel\u00ebn.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/dea5a4b1ac7952f8cfed0e2e1cfc10dd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Gjat\u00eb k\u00ebtij prezantimi do t\u2019ju tregoj grafiqe q\u00eb t\u00eb kuptohet qart\u00eb \u00e7far\u00eb po ndodh. Gjithmon\u00eb do t\u00eb ket\u00eb 2 slajde me grafiqe. Slajdi i par\u00eb tregon se \u00e7far\u00eb po ndodh n\u00eb p\u00ebrgjith\u00ebsi n\u00eb server. <\/p>\n<p><\/p>\n<p>Dhe n\u00eb k\u00ebt\u00eb situat\u00eb shohim se tabela \u00ebsht\u00eb v\u00ebrtet me p\u00ebrmasa t\u00eb vogla. Indeksi \u00ebsht\u00eb i vog\u00ebl, 2 MB. Ky \u00ebsht\u00eb grafiku i par\u00eb majtas. <\/p>\n<p><\/p>\n<p>Koha mesatare e p\u00ebrgjigjes s\u00eb serverit \u00ebsht\u00eb gjithashtu e q\u00ebndrueshme dhe e ul\u00ebt. Ky \u00ebsht\u00eb grafiku i sip\u00ebrm djathtas. <\/p>\n<p><\/p>\n<p>Grafiku posht\u00eb majtas paraqet transaksionet m\u00eb t\u00eb gjata. Shohim se transaksionet ekzekutohen shpejt. Autovacuum k\u00ebtu ende nuk po punon, sepse ky ishte nj\u00eb test fillestar. M\u00eb pas ai do t\u00eb aktivizohet dhe do t\u00eb na jet\u00eb i dobish\u00ebm.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/9e9b46acd9a878ff70ca4a7850adff09.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Slajdi i dyt\u00eb do t'i kushtohet gjithmon\u00eb tabel\u00ebs q\u00eb po testohet. N\u00eb k\u00ebt\u00eb rast, ne p\u00ebrdit\u00ebsojm\u00eb vazhdimisht gjendjet e llogarive t\u00eb klientit. Dhe shohim se koha mesatare e p\u00ebrgjigjes p\u00ebr operacionin e p\u00ebrdit\u00ebsimit \u00ebsht\u00eb mjaft e mir\u00eb, m\u00eb pak se nj\u00eb milisekond\u00eb. Shohim gjithashtu se burimet e procesorit (grafiku sip\u00ebr djathtas) konsumohen n\u00eb m\u00ebnyr\u00eb t\u00eb nj\u00ebtrajtshme dhe n\u00eb sasi relativisht t\u00eb vogla. <\/p>\n<p><\/p>\n<p>Grafiku posht\u00eb djathtas tregon sa memorie operative dhe disku p\u00ebrshkojm\u00eb n\u00eb k\u00ebrkim t\u00eb rreshtit q\u00eb na duhet p\u00ebrpara se ta p\u00ebrdit\u00ebsojm\u00eb. Dhe numri i operacioneve n\u00eb tabel\u00eb \u00ebsht\u00eb 2 000 n\u00eb sekond\u00eb, si\u00e7 e p\u00ebrmenda edhe n\u00eb fillim. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/84760d52716209b8dcfffb462c67a8e1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe tani p\u00ebrballemi me nj\u00eb problem serioz. P\u00ebr ndonj\u00eb arsye shfaqet nj\u00eb transaksion i gjat\u00eb i harruar. Arsyet zakonisht jan\u00eb fare t\u00eb zakonshme: <\/p>\n<p><\/p>\n<ul>\n<li>Nj\u00eb nga rastet m\u00eb t\u00eb shpeshta \u00ebsht\u00eb kur n\u00eb kodin e aplikacionit fillojm\u00eb t\u00eb komunikojm\u00eb me nj\u00eb sh\u00ebrbim t\u00eb jasht\u00ebm. Dhe ky sh\u00ebrbim nuk na p\u00ebrgjigjet. Pra, ne hap\u00ebm nj\u00eb transaksion, b\u00ebm\u00eb nj\u00eb ndryshim n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave dhe m\u00eb pas shkuam nga aplikacioni t\u00eb lexonim post\u00ebn ose t\u00eb kontaktonim nj\u00eb sh\u00ebrbim tjet\u00ebr brenda infrastruktur\u00ebs son\u00eb, por ai p\u00ebr ndonj\u00eb arsye nuk na p\u00ebrgjigjet. Si rezultat, seanca mbetet e varur n\u00eb nj\u00eb gjendje ku nuk dihet se kur do t\u00eb zgjidhet.<\/li>\n<li>Situata e dyt\u00eb \u00ebsht\u00eb kur n\u00eb kod, p\u00ebr ndonj\u00eb arsye, ndodh nj\u00eb exception. Dhe n\u00eb exception nuk e kemi trajtuar mbylljen e transaksionit. Si rezultat, kemi nj\u00eb seanc\u00eb t\u00eb varur me nj\u00eb transaksion t\u00eb hapur. <\/li>\n<li>Dhe rasti i fundit, gjithashtu mjaft i zakonsh\u00ebm, \u00ebsht\u00eb kodi me cil\u00ebsi t\u00eb dob\u00ebt. Disa framework hapin nj\u00eb transaksion. Ai mbetet i varur dhe ju mund t\u00eb mos e dini fare n\u00eb aplikacion q\u00eb ai \u00ebsht\u00eb ende i hapur. <\/li>\n<\/ul>\n<p><\/p>\n<p>Ku \u00e7ojn\u00eb gj\u00ebra t\u00eb tilla? <\/p>\n<p><\/p>\n<p>Ato \u00e7ojn\u00eb n\u00eb fryrje t\u00eb shpejt\u00eb t\u00eb tabelave dhe indekseve. Pik\u00ebrisht ky \u00ebsht\u00eb efekti i quajtur bloat. P\u00ebr baz\u00ebn e t\u00eb dh\u00ebnave kjo do t\u00eb shfaqet me nj\u00eb rritje shum\u00eb t\u00eb shpejt\u00eb t\u00eb koh\u00ebs s\u00eb p\u00ebrgjigjes dhe me rritje t\u00eb ngarkes\u00ebs n\u00eb serverin e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Si p\u00ebrfundim, do t\u00eb vuaj\u00eb aplikacioni. Sepse n\u00ebse m\u00eb par\u00eb n\u00eb kod shpenzonit 10 milisekonda p\u00ebr nj\u00eb k\u00ebrkes\u00eb ndaj baz\u00ebs s\u00eb t\u00eb dh\u00ebnave dhe 10 milisekonda p\u00ebr logjik\u00ebn tuaj, funksioni ekzekutohej p\u00ebr 20 milisekonda. Tani situata do t\u00eb jet\u00eb shum\u00eb m\u00eb e v\u00ebshtir\u00eb. <\/p>\n<p><\/p>\n<p>Dhe le t\u00eb shohim \u00e7far\u00eb po ndodh. Grafiku posht\u00eb majtas tregon se kemi nj\u00eb transaksion t\u00eb gjat\u00eb. Dhe n\u00ebse shohim grafikun sip\u00ebr majtas, v\u00ebrejm\u00eb se madh\u00ebsia e tabel\u00ebs \u00ebsht\u00eb rritur papritur nga dy megabajt n\u00eb 300 megabajt. Nd\u00ebrkoh\u00eb, sasia e t\u00eb dh\u00ebnave n\u00eb tabel\u00eb nuk ka ndryshuar, pra aty ka nj\u00eb sasi mjaft t\u00eb madhe t\u00eb dh\u00ebnash t\u00eb panevojshme.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b1947f6487e440a770a1251a955e62d2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Situata e p\u00ebrgjithshme me koh\u00ebn mesatare t\u00eb p\u00ebrgjigjes s\u00eb serverit gjithashtu ka ndryshuar me disa rend\u00eb madh\u00ebsie. Pra, t\u00eb gjitha k\u00ebrkesat n\u00eb server kan\u00eb nisur t\u00eb ngadal\u00ebsohen ndjesh\u00ebm. Nj\u00ebkoh\u00ebsisht jan\u00eb aktivizuar proceset e brendshme t\u00eb Postgres, konkretisht autovacuum, t\u00eb cilat po p\u00ebrpiqen t\u00eb b\u00ebjn\u00eb di\u00e7ka dhe po konsumojn\u00eb burime.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/8e91f035d1dd04e72e842d714cc611a5.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb po ndodh me tabel\u00ebn ton\u00eb? E nj\u00ebjta gj\u00eb. Koha mesatare e p\u00ebrgjigjes p\u00ebr tabel\u00ebn \u00ebsht\u00eb rritur me disa rend\u00eb madh\u00ebsie. Sa u p\u00ebrket konkretisht burimeve t\u00eb p\u00ebrdorura, shohim se ngarkesa n\u00eb CPU \u00ebsht\u00eb rritur shum\u00eb. Ky \u00ebsht\u00eb grafiku sip\u00ebr djathtas. Dhe \u00ebsht\u00eb rritur sepse CPU-s\u00eb i duhet t\u00eb kaloj\u00eb n\u00ebp\u00ebr nj\u00eb num\u00ebr t\u00eb madh rreshtash t\u00eb padobish\u00ebm p\u00ebr t\u00eb gjetur nj\u00eb t\u00eb vet\u00ebm q\u00eb duhet. Ky \u00ebsht\u00eb grafiku posht\u00eb djathtas. Si rezultat, numri i thirrjeve n\u00eb sekond\u00eb ka nisur t\u00eb bjer\u00eb ndjesh\u00ebm, sepse baza e t\u00eb dh\u00ebnave nuk arrin m\u00eb t\u00eb p\u00ebrpunoj\u00eb t\u00eb nj\u00ebjtin num\u00ebr k\u00ebrkesash. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/424685ce852bc5f3ef77151f8d3d7289.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Duhet ta rikthejm\u00eb sistemin n\u00eb gjendje normale. Hyjm\u00eb n\u00eb internet dhe m\u00ebsojm\u00eb se transaksionet e gjata \u00e7ojn\u00eb te ky problem. E gjejm\u00eb dhe e nd\u00ebrpresim k\u00ebt\u00eb transaksion. Dhe gjith\u00e7ka kthehet n\u00eb normalitet. Gjith\u00e7ka funksionon si\u00e7 duhet. <\/p>\n<p><\/p>\n<p>U qet\u00ebsuam, por pas nj\u00ebfar\u00eb kohe fillojm\u00eb t\u00eb v\u00ebrejm\u00eb se aplikacioni nuk po punon m\u00eb si para incidentit. K\u00ebrkesat gjithsesi p\u00ebrpunohen m\u00eb ngadal\u00eb, madje duksh\u00ebm m\u00eb ngadal\u00eb. N\u00eb shembullin tim konkret, rreth nj\u00eb her\u00eb e gjysm\u00eb deri n\u00eb dy her\u00eb m\u00eb ngadal\u00eb. Edhe ngarkesa n\u00eb server \u00ebsht\u00eb m\u00eb e lart\u00eb se para incidentit. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ee3037719a31f8d18fc42745716c4da5.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe pyetja \u00ebsht\u00eb: \u00ab\u00c7far\u00eb po ndodh me baz\u00ebn e t\u00eb dh\u00ebnave n\u00eb k\u00ebt\u00eb moment?\u00bb. Me baz\u00ebn e t\u00eb dh\u00ebnave po ndodh kjo situat\u00eb. N\u00eb grafikun e transaksioneve shihni se ajo \u00ebsht\u00eb ndalur dhe aty v\u00ebrtet nuk ka transaksione t\u00eb gjata. Por madh\u00ebsia e tabel\u00ebs gjat\u00eb incidentit \u00ebsht\u00eb rritur n\u00eb m\u00ebnyr\u00eb drastike. Dhe q\u00eb at\u00ebher\u00eb nuk \u00ebsht\u00eb zvog\u00ebluar. Koha mesatare p\u00ebr baz\u00ebn e t\u00eb dh\u00ebnave \u00ebsht\u00eb stabilizuar. Dhe p\u00ebrgjigjet, n\u00eb dukje, vijn\u00eb normalisht me nj\u00eb shpejt\u00ebsi t\u00eb pranueshme p\u00ebr ne. Autovacuum \u00ebsht\u00eb b\u00ebr\u00eb m\u00eb aktiv dhe ka nisur t\u00eb b\u00ebj\u00eb di\u00e7ka me tabel\u00ebn, sepse tani duhet t\u00eb p\u00ebrpunoj\u00eb nj\u00eb sasi m\u00eb t\u00eb madhe t\u00eb dh\u00ebnash. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/f6205242c4024dc3ed4cade5844dbf76.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Konkretisht p\u00ebr tabel\u00ebn e testuar me llogari, ku po ndryshojm\u00eb gjendjet: koha e p\u00ebrgjigjes s\u00eb k\u00ebrkes\u00ebs duket se \u00ebsht\u00eb kthyer n\u00eb norm\u00eb. Por n\u00eb fakt ajo \u00ebsht\u00eb nj\u00eb her\u00eb e gjysm\u00eb m\u00eb e lart\u00eb.<\/p>\n<p><\/p>\n<p>Edhe nga ngarkesa e CPU-s\u00eb shohim se ajo nuk \u00ebsht\u00eb kthyer n\u00eb nivelin e duhur para incidentit. Arsyet fshihen pik\u00ebrisht te grafiku posht\u00eb djathtas. Shihet se aty po ndodh skanimi i nj\u00eb sasie t\u00eb caktuar memorieje. Pra, p\u00ebr t\u00eb gjetur rreshtin e duhur, po shpenzojm\u00eb burimet e serverit t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave duke kaluar n\u00ebp\u00ebr t\u00eb dh\u00ebna t\u00eb panevojshme. Numri i transaksioneve p\u00ebr sekond\u00eb \u00ebsht\u00eb stabilizuar. <\/p>\n<p><\/p>\n<p>N\u00eb p\u00ebrgjith\u00ebsi \u00ebsht\u00eb mir\u00eb, por situata \u00ebsht\u00eb m\u00eb e keqe se m\u00eb par\u00eb. Kjo \u00ebsht\u00eb nj\u00eb degradim i qart\u00eb i baz\u00ebs s\u00eb t\u00eb dh\u00ebnave si pasoj\u00eb e aplikacionit ton\u00eb, i cili punon me k\u00ebt\u00eb baz\u00eb t\u00eb dh\u00ebnash. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/0dfc9cd453fd84bc639ffd99cf189fd2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe p\u00ebr t\u00eb kuptuar \u00e7far\u00eb po ndodh aty, n\u00ebse nuk keni qen\u00eb n\u00eb prezantimin e m\u00ebparsh\u00ebm, tani pak teori. Teori p\u00ebr procesin e brendsh\u00ebm. Pse nevojitet autovacuum dhe \u00e7far\u00eb b\u00ebn ai?<\/p>\n<p><\/p>\n<p>Shum\u00eb shkurt, p\u00ebr ta kuptuar. N\u00eb nj\u00eb moment t\u00eb caktuar kemi nj\u00eb tabel\u00eb. N\u00eb tabel\u00eb kemi rreshta. K\u00ebta rreshta mund t\u00eb jen\u00eb aktiv\u00eb, t\u00eb vlefsh\u00ebm, q\u00eb na duhen tani. N\u00eb figur\u00eb ata jan\u00eb sh\u00ebnuar me ngjyr\u00eb t\u00eb gjelb\u00ebr. Ka edhe rreshta t\u00eb vdekur, q\u00eb e kan\u00eb kryer tashm\u00eb funksionin e tyre, jan\u00eb p\u00ebrdit\u00ebsuar dhe p\u00ebr ta jan\u00eb krijuar regjistrime t\u00eb reja. Ata jan\u00eb sh\u00ebnuar si jo m\u00eb me interes p\u00ebr baz\u00ebn e t\u00eb dh\u00ebnave. Por mbeten n\u00eb tabel\u00eb p\u00ebr shkak t\u00eb ve\u00e7orive t\u00eb Postgres.<\/p>\n<p><\/p>\n<p>Pse nevojitet autovacuum? N\u00eb nj\u00eb moment t\u00eb caktuar, autovacuum vjen, i drejtohet baz\u00ebs s\u00eb t\u00eb dh\u00ebnave dhe i k\u00ebrkon: \u00abM\u00eb jep, t\u00eb lutem, ID-n\u00eb e transaksionit m\u00eb t\u00eb vjet\u00ebr q\u00eb \u00ebsht\u00eb aktualisht i hapur n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave\u00bb. Baza e t\u00eb dh\u00ebnave e kthen k\u00ebt\u00eb ID. Pastaj autovacuum, duke u mb\u00ebshtetur te ajo, kalon n\u00ebp\u00ebr rreshtat e tabel\u00ebs. Dhe n\u00ebse sheh se disa rreshta jan\u00eb ndryshuar nga transaksione shum\u00eb m\u00eb t\u00eb vjetra, ai ka t\u00eb drejt\u00eb t\u2019i sh\u00ebnoj\u00eb si rreshta q\u00eb mund t\u2019i rip\u00ebrdorim n\u00eb t\u00eb ardhmen duke shkruar aty t\u00eb dh\u00ebna t\u00eb reja. Ky \u00ebsht\u00eb nj\u00eb proces n\u00eb sfond.<\/p>\n<p><\/p>\n<p>Nd\u00ebrkoh\u00eb ne vazhdojm\u00eb t\u00eb punojm\u00eb me baz\u00ebn e t\u00eb dh\u00ebnave dhe t\u00eb b\u00ebjm\u00eb ndryshime n\u00eb tabel\u00eb. N\u00eb k\u00ebta rreshta q\u00eb mund t\u2019i rip\u00ebrdorim, shkruajm\u00eb t\u00eb dh\u00ebna t\u00eb reja. Dhe n\u00eb k\u00ebt\u00eb m\u00ebnyr\u00eb krijohet nj\u00eb qarkullim i vazhduesh\u00ebm: aty shfaqen vazhdimisht rreshta t\u00eb vjet\u00ebr t\u00eb vdekur, nd\u00ebrsa n\u00eb vend t\u00eb tyre shkruajm\u00eb rreshta t\u00eb rinj q\u00eb na duhen. Dhe kjo \u00ebsht\u00eb nj\u00eb gjendje normale p\u00ebr funksionimin e PostgreSQL.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/98dd4451a7ca5f418775f72822c2840c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb ndodhi gjat\u00eb incidentit? Si zhvillohej ky proces?<\/p>\n<p><\/p>\n<p>Kishim nj\u00eb tabel\u00eb n\u00eb nj\u00eb gjendje t\u00eb caktuar: disa rreshta ishin aktiv\u00eb, disa t\u00eb tjer\u00eb t\u00eb vdekur. Erdhi autovacuum. Ai pyeti baz\u00ebn e t\u00eb dh\u00ebnave se cila ishte transaksioni yn\u00eb m\u00eb i vjet\u00ebr dhe cili ishte ID-ja e tij. Mori k\u00ebt\u00eb ID, e cila mund t\u00eb ishte prej shum\u00eb or\u00ebsh m\u00eb par\u00eb ose prej dhjet\u00eb minutash. Kjo varet nga sa e lart\u00eb \u00ebsht\u00eb ngarkesa n\u00eb baz\u00ebn tuaj t\u00eb t\u00eb dh\u00ebnave. Pastaj nisi t\u00eb k\u00ebrkoj\u00eb rreshtat q\u00eb mund t\u2019i sh\u00ebnonte si t\u00eb rip\u00ebrdorsh\u00ebm. Dhe nuk gjeti t\u00eb till\u00eb n\u00eb tabel\u00ebn ton\u00eb. <\/p>\n<p><\/p>\n<p>Por nd\u00ebrkoh\u00eb ne vazhdojm\u00eb t\u00eb punojm\u00eb me tabel\u00ebn. B\u00ebjm\u00eb di\u00e7ka n\u00eb t\u00eb, p\u00ebrdit\u00ebsojm\u00eb dhe ndryshojm\u00eb t\u00eb dh\u00ebnat. Po baza e t\u00eb dh\u00ebnave \u00e7far\u00eb duhet t\u00eb b\u00ebj\u00eb n\u00eb k\u00ebt\u00eb moment? Asaj nuk i mbetet gj\u00eb tjet\u00ebr ve\u00e7se t\u00eb shtoj\u00eb rreshta t\u00eb rinj n\u00eb fund t\u00eb tabel\u00ebs ekzistuese. Si rezultat, madh\u00ebsia e tabel\u00ebs fillon t\u00eb fryhet. <\/p>\n<p><\/p>\n<p>N\u00eb praktik\u00eb, p\u00ebr pun\u00eb na duhen rreshtat e gjelb\u00ebr. Por gjat\u00eb nj\u00eb problemi t\u00eb till\u00eb, rezulton q\u00eb p\u00ebrqindja e rreshtave t\u00eb gjelb\u00ebr \u00ebsht\u00eb jasht\u00ebzakonisht e ul\u00ebt n\u00eb t\u00eb gjith\u00eb v\u00ebllimin e tabel\u00ebs. <\/p>\n<p><\/p>\n<p>Dhe kur ekzekutojm\u00eb nj\u00eb k\u00ebrkes\u00eb, baza e t\u00eb dh\u00ebnave detyrohet t\u00eb kaloj\u00eb n\u00ebp\u00ebr t\u00eb gjith\u00eb rreshtat, si t\u00eb kuq ashtu edhe t\u00eb gjelb\u00ebr, p\u00ebr t\u00eb gjetur rreshtin e nevojsh\u00ebm. Efekti i fryrjes s\u00eb tabel\u00ebs me t\u00eb dh\u00ebna t\u00eb padobishme quhet \u201cbloat\u201d, dhe ai gjithashtu konsumon hap\u00ebsir\u00ebn ton\u00eb n\u00eb disk. Ju kujtohet, ishin 2 MB, u b\u00ebn\u00eb 300 MB? Tani z\u00ebvend\u00ebsoni megabajt\u00ebt me gigabajt\u00eb dhe shum\u00eb shpejt do t\u00eb mbeteni pa t\u00eb gjitha rezervat e burimeve tuaja n\u00eb disk.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/fe7cb6b5c99610744ebcfe5ad7235d1e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb pasojash mund t\u00eb ket\u00eb kjo p\u00ebr ne? <\/p>\n<p><\/p>\n<ul>\n<li>N\u00eb shembullin tim, tabela dhe indeksi u rrit\u00ebn 150 her\u00eb. Te disa nga klient\u00ebt tan\u00eb ka pasur raste edhe m\u00eb kritike, kur thjesht fillonte t\u00eb mbaronte hap\u00ebsira n\u00eb disk. <\/li>\n<li>Madh\u00ebsia e tabelave n\u00eb vetvete nuk do t\u00eb zvog\u00eblohet kurr\u00eb. Autovacuum n\u00eb disa raste mund t\u00eb pres\u00eb pjes\u00ebn e fundit t\u00eb tabel\u00ebs, n\u00ebse aty ka vet\u00ebm rreshta t\u00eb vdekur. Por, duke qen\u00eb se ndodh rotacion i vazhduesh\u00ebm, nj\u00eb rresht i gjelb\u00ebr mund t\u00eb mbetet n\u00eb fund dhe t\u00eb mos p\u00ebrdit\u00ebsohet, nd\u00ebrsa t\u00eb gjith\u00eb t\u00eb tjer\u00ebt do t\u00eb shkruhen diku n\u00eb fillim t\u00eb tabel\u00ebs. Megjithat\u00eb, q\u00eb tabela t\u00eb zvog\u00eblohet vetvetiu \u00ebsht\u00eb nj\u00eb skenar aq i pamundur, sa nuk ia vlen t\u00eb shpresoni te kjo. <\/li>\n<li>Baza e t\u00eb dh\u00ebnave duhet t\u00eb kaloj\u00eb n\u00ebp\u00ebr t\u00eb gjith\u00eb grumbullin e rreshtave t\u00eb padobish\u00ebm. Dhe ne harxhojm\u00eb burime t\u00eb diskut, burime t\u00eb procesorit dhe energji elektrike. <\/li>\n<li>Dhe kjo ndikon drejtp\u00ebrdrejt te aplikacioni yn\u00eb, sepse n\u00ebse n\u00eb fillim shpenzonim 10 milisekonda p\u00ebr query-n, 10 milisekonda p\u00ebr kodin ton\u00eb, at\u00ebher\u00eb gjat\u00eb incidentit filluam t\u00eb shpenzonim 1 sekond\u00eb p\u00ebr query-n dhe 10 milisekonda p\u00ebr kodin, pra performanca e aplikacionit ra me nj\u00eb rend madh\u00ebsie. Dhe kur incidenti u zgjidh, filluam t\u00eb shpenzonim 20 milisekonda p\u00ebr query-n dhe 10 milisekonda p\u00ebr kodin. Kjo do t\u00eb thot\u00eb se gjithsesi humb\u00ebm rreth 1.5 her\u00eb n\u00eb performanc\u00eb. Dhe e gjitha kjo p\u00ebr shkak t\u00eb nj\u00eb transaksioni t\u00eb vet\u00ebm q\u00eb mbeti i bllokuar, madje ndoshta p\u00ebr fajin ton\u00eb. <\/li>\n<li>Dhe pyetja \u00ebsht\u00eb: \u00abSi ta kthejm\u00eb gjith\u00e7ka mbrapsht?\u00bb, q\u00eb \u00e7do gj\u00eb t\u00eb rikthehet n\u00eb normalitet dhe query-t t\u00eb ekzekutohen s\u00ebrish po aq shpejt sa para incidentit. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/1bf46e8cd9b28ca2862f978f2ca85819.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>P\u00ebr k\u00ebt\u00eb ekziston nj\u00eb cik\u00ebl i caktuar pune q\u00eb duhet ndjekur. <\/p>\n<p><\/p>\n<p>S\u00eb pari duhet t\u00eb gjejm\u00eb tabelat problematike q\u00eb jan\u00eb fryr\u00eb. E kuptojm\u00eb se n\u00eb disa tabela shkrimet b\u00ebhen m\u00eb aktivisht, nd\u00ebrsa n\u00eb disa t\u00eb tjera m\u00eb pak. P\u00ebr k\u00ebt\u00eb p\u00ebrdoret zgjerimi <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/pgstattuple.html\">pgstattuple<\/a><\/noindex>. Pasi ta instaloni k\u00ebt\u00eb zgjerim, mund t\u00eb shkruani query q\u00eb do t\u2019ju ndihmojn\u00eb t\u00eb gjeni tabelat q\u00eb jan\u00eb fryr\u00eb ndjesh\u00ebm. <\/p>\n<p><\/p>\n<p>Pasi t\u2019i keni gjetur k\u00ebto tabela, ato duhet t\u00eb kompaktohen. P\u00ebr k\u00ebt\u00eb tashm\u00eb ekzistojn\u00eb mjete. N\u00eb kompanin\u00eb ton\u00eb p\u00ebrdorim tre mjete. I pari \u00ebsht\u00eb VACUUM FULL i integruar. \u00cbsht\u00eb i ashp\u00ebr, i fort\u00eb dhe i pam\u00ebshirsh\u00ebm, por ndonj\u00ebher\u00eb \u00ebsht\u00eb shum\u00eb i dobish\u00ebm. <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/reorg\/pg_repack\">Pg_repack<\/a><\/noindex> dhe <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pgcompacttable\">pgcompacttable<\/a><\/noindex> jan\u00eb utilitete t\u00eb pal\u00ebve t\u00eb treta p\u00ebr kompaktimin e tabelave. Dhe ato sillen m\u00eb me kujdes ndaj baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. <\/p>\n<p><\/p>\n<p>Ato p\u00ebrdoren n\u00eb var\u00ebsi t\u00eb asaj q\u00eb ju leverdis m\u00eb shum\u00eb. Por p\u00ebr k\u00ebt\u00eb do t\u00eb flas n\u00eb fund. E r\u00ebnd\u00ebsishme \u00ebsht\u00eb se ka tre mjete. Pra, keni mund\u00ebsi zgjedhjeje. <\/p>\n<p><\/p>\n<p>Pasi t\u00eb kemi rregulluar gjith\u00e7ka dhe t\u00eb jemi siguruar se \u00e7do gj\u00eb \u00ebsht\u00eb n\u00eb rregull, duhet t\u00eb dim\u00eb si ta parandalojm\u00eb k\u00ebt\u00eb situat\u00eb n\u00eb t\u00eb ardhmen:<\/p>\n<p><\/p>\n<ul>\n<li>Kjo parandalohet mjaft leht\u00eb. Duhet t\u00eb monitoroni koh\u00ebzgjatjen e sesioneve n\u00eb serverin Master. <strong>Ve\u00e7an\u00ebrisht t\u00eb rrezikshme jan\u00eb sesionet n\u00eb gjendjen idle in transaction<\/strong>. K\u00ebto jan\u00eb ato q\u00eb kan\u00eb hapur nj\u00eb transaksion, kan\u00eb b\u00ebr\u00eb di\u00e7ka dhe m\u00eb pas jan\u00eb l\u00ebn\u00eb pas dore ose thjesht kan\u00eb mbetur t\u00eb varura, t\u00eb humbura n\u00eb kod. <\/li>\n<li>Dhe p\u00ebr ju, si zhvillues, \u00ebsht\u00eb e r\u00ebnd\u00ebsishme ta testoni kodin p\u00ebr raste kur krijohen situata t\u00eb tilla. Kjo nuk \u00ebsht\u00eb e v\u00ebshtir\u00eb p\u00ebr t\u2019u b\u00ebr\u00eb. Do t\u00eb jet\u00eb nj\u00eb kontroll i dobish\u00ebm. Do t\u00eb shmangni nj\u00eb num\u00ebr t\u00eb madh problemesh \u00abfillestare\u00bb q\u00eb lidhen me transaksionet e gjata. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/d55465d96c4018db733954867d23aaba.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00eb k\u00ebto grafik\u00eb doja t\u2019ju tregoja se si ndryshuan tabela dhe sjellja e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave pasi, n\u00eb k\u00ebt\u00eb rast, kalova mbi tabel\u00eb me VACUUM FULL. Kjo te un\u00eb nuk \u00ebsht\u00eb production.<\/p>\n<p><\/p>\n<p>Madh\u00ebsia e tabel\u00ebs u kthye menj\u00ebher\u00eb n\u00eb gjendjen normale t\u00eb pun\u00ebs, n\u00eb disa megabajt. Kjo nuk ndikoi shum\u00eb n\u00eb koh\u00ebn mesatare t\u00eb p\u00ebrgjigjes s\u00eb serverit. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/cbb07ad24dba899395223d1ca25e438e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Por pik\u00ebrisht p\u00ebr tabel\u00ebn ton\u00eb t\u00eb testimit, ku p\u00ebrdit\u00ebsonim gjendjet e llogarive, shohim se koha mesatare e p\u00ebrgjigjes p\u00ebr k\u00ebrkes\u00ebn e p\u00ebrdit\u00ebsimit t\u00eb t\u00eb dh\u00ebnave n\u00eb tabel\u00eb u ul n\u00eb nivelin para incidentit. Edhe burimet e CPU t\u00eb p\u00ebrdorura p\u00ebr ekzekutimin e k\u00ebsaj k\u00ebrkese ran\u00eb n\u00eb nivelin para incidentit. Grafiku posht\u00eb djathtas tregon gjithashtu se tani po gjejm\u00eb menj\u00ebher\u00eb sakt\u00ebsisht rreshtin q\u00eb na duhet, pa kaluar n\u00ebp\u00ebr nj\u00eb grumbull rreshtash t\u00eb vdekur q\u00eb ishin para kompresimit t\u00eb tabel\u00ebs. Nd\u00ebrsa koha mesatare e k\u00ebrkesave mbeti af\u00ebrsisht n\u00eb t\u00eb nj\u00ebjtin nivel. Por k\u00ebtu, me shum\u00eb gjasa, b\u00ebhet fjal\u00eb p\u00ebr kufizimet e harduerit tim.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/c1d90c5442e02fce209dc40e048a8ca2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>K\u00ebtu p\u00ebrfundon historia e par\u00eb. Ajo \u00ebsht\u00eb m\u00eb e zakonshmja. Dhe u ndodh t\u00eb gjith\u00ebve, pavar\u00ebsisht nga p\u00ebrvoja e klientit apo sa t\u00eb kualifikuar jan\u00eb programuesit. Her\u00ebt a von\u00eb, kjo ndodh. <\/p>\n<p><\/p>\n<p>Historia e dyt\u00eb, ku shp\u00ebrndajm\u00eb ngarkes\u00ebn dhe optimizojm\u00eb burimet e serverit<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/cda421c554d1329107ddc935045cc1b9.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Tashm\u00eb jemi rritur dhe jemi b\u00ebr\u00eb lojtar\u00eb serioz\u00eb. E kuptojm\u00eb se kemi nj\u00eb replik\u00eb dhe se do t\u00eb ishte mir\u00eb t\u00eb balanconim ngarkes\u00ebn: t\u00eb shkruajm\u00eb n\u00eb Master dhe t\u00eb lexojm\u00eb nga replika. Zakonisht kjo situat\u00eb lind kur duam t\u00eb p\u00ebrgatisim raporte ose ETL. Dhe biznesi g\u00ebzohet shum\u00eb p\u00ebr k\u00ebt\u00eb. Ai k\u00ebrkon shum\u00eb lloje raportesh me nj\u00eb sasi t\u00eb madhe analitike t\u00eb nd\u00ebrlikuar. <\/li>\n<li>Raportet zgjasin me or\u00eb t\u00eb t\u00ebra, sepse analitika e nd\u00ebrlikuar nuk llogaritet n\u00eb milisekonda. Ne, si djem t\u00eb zot\u00eb, shkruajm\u00eb kod. N\u00eb aplikacion vendosim q\u00eb shkrimet t\u2019i b\u00ebjm\u00eb n\u00eb Master, nd\u00ebrsa raportet t\u2019i ekzekutojm\u00eb n\u00eb replik\u00eb. <\/li>\n<li>Shp\u00ebrndajm\u00eb ngarkes\u00ebn. <\/li>\n<li>Gjith\u00e7ka funksionon shk\u00eblqyesh\u00ebm. Jemi t\u00eb zot\u00ebt. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e94c8c492197a5015ac8aae79389a4c2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Po si duket kjo situat\u00eb? Pik\u00ebrisht n\u00eb k\u00ebta grafik\u00eb, p\u00ebr koh\u00ebzgjatjen e transaksionit, kam shtuar edhe koh\u00ebzgjatjen e transaksioneve nga replika. T\u00eb gjith\u00eb grafik\u00ebt e tjer\u00eb i referohen vet\u00ebm serverit Master. <\/p>\n<p><\/p>\n<p>Tabela me raportet deri n\u00eb k\u00ebt\u00eb moment ishte rritur. Ato u shtuan. Shohim se koha mesatare e p\u00ebrgjigjes s\u00eb serverit \u00ebsht\u00eb e q\u00ebndrueshme. Shohim se n\u00eb replik\u00eb kemi nj\u00eb transaksion t\u00eb gjat\u00eb q\u00eb po punon prej 2 or\u00ebsh. Shohim pun\u00ebn e qet\u00eb t\u00eb autovacuum-it, i cili pastron rreshtat e vdekur. Dhe gjith\u00e7ka \u00ebsht\u00eb n\u00eb rregull. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2da8acf098d12d6f146ab9b9dcb54b81.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Sa i p\u00ebrket tabel\u00ebs n\u00eb testim, ne vazhdojm\u00eb t\u00eb p\u00ebrdit\u00ebsojm\u00eb bilancet n\u00eb llogari. Edhe k\u00ebtu kemi koh\u00eb t\u00eb q\u00ebndrueshme p\u00ebrgjigjeje p\u00ebr k\u00ebrkesat dhe konsum t\u00eb q\u00ebndruesh\u00ebm t\u00eb burimeve. Gjith\u00e7ka \u00ebsht\u00eb n\u00eb rregull. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/3621ef723017bac6fb635caf0a5f4de6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Gjith\u00e7ka \u00ebsht\u00eb mir\u00eb derisa k\u00ebto raporte fillojn\u00eb t\u00eb nd\u00ebrpriten p\u00ebr shkak t\u00eb konfliktit me replikimin. Dhe nd\u00ebrpriten me periodicitet t\u00eb vazhduesh\u00ebm. <\/p>\n<p><\/p>\n<p>Hym\u00eb n\u00eb internet dhe fillojm\u00eb t\u00eb lexojm\u00eb pse po ndodh kjo. Dhe gjejm\u00eb zgjidhjen. <\/p>\n<p><\/p>\n<p>Zgjidhja e par\u00eb \u00ebsht\u00eb t\u00eb rrisim vones\u00ebn e replikimit. E dim\u00eb q\u00eb raporti yn\u00eb zgjat 3 or\u00eb. Vendosim vones\u00ebn e replikimit n\u00eb 3 or\u00eb. E nisim gjith\u00e7ka, por s\u00ebrish vazhdojm\u00eb t\u00eb kemi probleme, sepse raportet her\u00eb pas here p\u00ebrs\u00ebri nd\u00ebrpriten. <\/p>\n<p><\/p>\n<p>Ne duam q\u00eb gjith\u00e7ka t\u00eb jet\u00eb perfekte. Vazhdojm\u00eb t\u00eb k\u00ebrkojm\u00eb. Dhe gjejm\u00eb n\u00eb internet nj\u00eb konfigurim shum\u00eb t\u00eb mir\u00eb: hot_standby_feedback. E aktivizojm\u00eb. Hot_standby_feedback na lejon t\u00eb ngadal\u00ebsojm\u00eb pun\u00ebn e autovacuum-it n\u00eb Master. K\u00ebshtu i eliminojm\u00eb plot\u00ebsisht konfliktet e replikimit. Dhe raportet fillojn\u00eb t\u00eb funksionojn\u00eb si duhet.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ee3c315168bdd3f392d2e8a1c5861004.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Po me serverin Master \u00e7far\u00eb po ndodh nd\u00ebrkoh\u00eb? Me serverin Master po ndodh nj\u00eb katastrof\u00eb e v\u00ebrtet\u00eb. Tani po shohim grafiqet e momentit kur aktivizova t\u00eb dyja k\u00ebto konfigurime. Dhe shohim se sesioni n\u00eb replik\u00eb, n\u00eb nj\u00ebfar\u00eb m\u00ebnyre, ka filluar t\u00eb ndikoj\u00eb n\u00eb situat\u00ebn n\u00eb serverin Master. N\u00eb fakt ndikon, sepse ndaloi autovacuum-in q\u00eb pastron rreshtat e vdekur. Madh\u00ebsia e tabel\u00ebs na k\u00ebrceu s\u00ebrish n\u00eb nivele ekstreme. Edhe koha mesatare e ekzekutimit t\u00eb k\u00ebrkesave n\u00eb t\u00eb gjith\u00eb baz\u00ebn e t\u00eb dh\u00ebnave u rrit jasht\u00ebzakonisht shum\u00eb. Autovacuum-et u ngarkuan disi m\u00eb tep\u00ebr. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/980611dcce8b189dc50f422ffd3d2d72.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Konkretisht p\u00ebr tabel\u00ebn ton\u00eb, shohim se edhe p\u00ebrdit\u00ebsimi i t\u00eb dh\u00ebnave aty u rrit n\u00eb nivele ekstreme. Konsumi i burimeve t\u00eb CPU gjithashtu u rrit shum\u00eb. S\u00ebrish po kalojm\u00eb n\u00ebp\u00ebr nj\u00eb num\u00ebr t\u00eb madh rreshtash t\u00eb vdekur e t\u00eb padobish\u00ebm. Dhe koha e p\u00ebrgjigjes p\u00ebr k\u00ebt\u00eb tabel\u00eb, nd\u00ebrsa numri i transaksioneve, ra. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/a648fd313a5dccf45f0056dc13c54263.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Si do t\u00eb dukej kjo n\u00ebse nuk do ta dinim se p\u00ebr \u00e7far\u00eb fola m\u00eb par\u00eb?<\/p>\n<p><\/p>\n<ul>\n<li>Nisim t\u00eb k\u00ebrkojm\u00eb problemet. N\u00ebse n\u00eb pjes\u00ebn e par\u00eb jemi p\u00ebrballur me probleme, e dim\u00eb se shkaku mund t\u00eb jet\u00eb nj\u00eb transaksion i gjat\u00eb dhe futemi te Master. Problemi \u00ebsht\u00eb te Master. Ai po l\u00ebkundet. Po mbinxehet, Load Average i shkon deri af\u00ebr nj\u00ebqind. <\/li>\n<li>K\u00ebrkesat atje ngadal\u00ebsohen, por nuk shohim asnj\u00eb transaksion t\u00eb gjat\u00eb. Dhe nuk e kuptojm\u00eb ku \u00ebsht\u00eb problemi. Nuk e dim\u00eb ku t\u00eb k\u00ebrkojm\u00eb. <\/li>\n<li>Kontrollojm\u00eb pajisjet e serverit. Ndoshta na \u00ebsht\u00eb prishur RAID. Ndoshta na \u00ebsht\u00eb djegur nj\u00eb modul RAM. Mund t\u00eb jet\u00eb \u00e7far\u00ebdo. Por jo, server\u00ebt jan\u00eb t\u00eb rinj, gjith\u00e7ka funksionon n\u00eb m\u00ebnyr\u00eb perfekte. <\/li>\n<li>T\u00eb gjith\u00eb vrapojn\u00eb: administrator\u00ebt, zhvilluesit dhe drejtori. Asgj\u00eb nuk ndihmon. <\/li>\n<li>Dhe n\u00eb nj\u00eb moment gjith\u00e7ka, krejt papritur, fillon t\u00eb rregullohet vet\u00eb. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/7d4f6417bc5cb3cec33679d843c6661d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00eb replik\u00eb, nd\u00ebrkoh\u00eb, k\u00ebrkesa u p\u00ebrpunua dhe p\u00ebrfundoi. E mor\u00ebm raportin. Biznesi \u00ebsht\u00eb ende i k\u00ebnaqur. Si\u00e7 e shohim, tabela \u00ebsht\u00eb rritur s\u00ebrish dhe nuk ka nd\u00ebrmend t\u00eb zvog\u00eblohet. N\u00eb grafikun e sesioneve lash\u00eb nj\u00eb pjes\u00eb t\u00eb k\u00ebtij transaksioni t\u00eb gjat\u00eb nga replika, q\u00eb t\u00eb mund t\u00eb vler\u00ebsoni sa shum\u00eb koh\u00eb kalon derisa situata t\u00eb stabilizohet. <\/p>\n<p><\/p>\n<p>Sesioni p\u00ebrfundoi. Dhe vet\u00ebm pas nj\u00ebfar\u00eb kohe serveri kthehet pak a shum\u00eb n\u00eb gjendje normale. Edhe koha mesatare e p\u00ebrgjigjes s\u00eb k\u00ebrkesave n\u00eb serverin Master kthehet n\u00eb norm\u00eb. Sepse, m\u00eb n\u00eb fund, autovacuum mori mund\u00ebsin\u00eb t\u00eb pastroj\u00eb dhe t\u00eb sh\u00ebnoj\u00eb k\u00ebto rreshta t\u00eb vdekur. Dhe filloi t\u00eb b\u00ebj\u00eb pun\u00ebn e vet. Dhe sa m\u00eb shpejt ta b\u00ebj\u00eb, aq m\u00eb shpejt do t\u00eb rikthehemi n\u00eb normalitet.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/547d195e08de1566da818150a47218eb.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>P\u00ebr tabel\u00ebn q\u00eb po testohet, ku p\u00ebrdit\u00ebsojm\u00eb gjendjet e llogarive, shohim pik\u00ebrisht t\u00eb nj\u00ebjt\u00ebn pamje. Edhe koha mesatare e p\u00ebrdit\u00ebsimit t\u00eb llogaris\u00eb normalizohet gradualisht. Burimet e p\u00ebrdorura nga CPU gjithashtu ulen. Edhe numri i transaksioneve p\u00ebr sekond\u00eb kthehet n\u00eb norm\u00eb. Por jo m\u00eb n\u00eb at\u00eb norm\u00eb q\u00eb kishim p\u00ebrpara incidentit. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/29372a5e9124690931485a7aea65befc.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00eb \u00e7do rast kemi nj\u00eb r\u00ebnie performance, si edhe n\u00eb rastin e par\u00eb, me 1,5 deri n\u00eb 2 her\u00eb, e ndonj\u00ebher\u00eb edhe m\u00eb shum\u00eb. <\/p>\n<p><\/p>\n<p>Duket sikur b\u00ebm\u00eb gjith\u00e7ka si\u00e7 duhet. E shp\u00ebrndam\u00eb ngarkes\u00ebn. Pajisjet nuk rrin\u00eb bosh. I ndam\u00eb k\u00ebrkesat si\u00e7 duhet, por prap\u00ebseprap\u00eb gjith\u00e7ka doli keq. <\/p>\n<p><\/p>\n<ul>\n<li>T\u00eb mos aktivizohet hot_standby_feedback? Po, pa arsye v\u00ebrtet t\u00eb forta nuk rekomandohet t\u00eb aktivizohet. Sepse ky paramet\u00ebr ndikon drejtp\u00ebrdrejt n\u00eb serverin Master dhe ndalon pun\u00ebn e autovacuum atje. N\u00ebse e aktivizoni n\u00eb nj\u00eb replik\u00eb dhe pastaj e harroni, mund t\u00eb rr\u00ebzoni Master-in dhe t\u00eb krijoni probleme serioze p\u00ebr aplikacionin. <\/li>\n<li>T\u00eb rritet max_standby_streaming_delay? Po, p\u00ebr raportet kjo \u00ebsht\u00eb e arsyeshme. N\u00ebse keni nj\u00eb raport q\u00eb zgjat tre or\u00eb dhe nuk doni q\u00eb ai t\u00eb d\u00ebshtoj\u00eb p\u00ebr shkak t\u00eb konflikteve t\u00eb replikimit, thjesht rrisni vones\u00ebn. Nj\u00eb raport i gjat\u00eb nuk ka nevoj\u00eb p\u00ebr t\u00eb dh\u00ebna q\u00eb sapo kan\u00eb mb\u00ebrritur n\u00eb baz\u00eb. N\u00ebse raporti zgjat tre or\u00eb, do t\u00eb thot\u00eb se e nisni p\u00ebr nj\u00eb periudh\u00eb m\u00eb t\u00eb hershme t\u00eb t\u00eb dh\u00ebnave. Prandaj, qoft\u00eb vones\u00eb tre or\u00eb apo gjasht\u00eb or\u00eb, p\u00ebr ju nuk b\u00ebn ndonj\u00eb ndryshim, por nd\u00ebrkoh\u00eb do t\u2019i merrni raportet n\u00eb m\u00ebnyr\u00eb t\u00eb q\u00ebndrueshme pa hasur probleme me nd\u00ebrprerjen e tyre. <\/li>\n<li>Natyrisht, duhet t\u00eb monitorohen sesionet e gjata n\u00eb replika, ve\u00e7an\u00ebrisht n\u00ebse keni vendosur t\u00eb aktivizoni hot_standby_feedback n\u00eb replik\u00eb. Sepse mund t\u00eb ndodh\u00eb gjith\u00e7ka. Ia dham\u00eb k\u00ebt\u00eb replik\u00eb nj\u00eb zhvilluesi q\u00eb t\u00eb testonte query-t. Ai shkroi nj\u00eb query t\u00eb \u00e7mendur. E nisi dhe shkoi t\u00eb pinte \u00e7aj, nd\u00ebrsa ne mor\u00ebm nj\u00eb Master t\u00eb bllokuar. Ose kemi lejuar aty aplikacionin e gabuar. Situatat mund t\u00eb jen\u00eb t\u00eb ndryshme. Sesionet n\u00eb replika duhen monitoruar po aq me kujdes sa edhe n\u00eb Master. <\/li>\n<li>Dhe n\u00ebse n\u00eb replika keni si query t\u00eb shpejta ashtu edhe t\u00eb gjata, n\u00eb k\u00ebt\u00eb rast \u00ebsht\u00eb m\u00eb mir\u00eb t\u2019i ndani p\u00ebr shp\u00ebrndarjen e ngarkes\u00ebs. Kjo lidhet me streaming_delay. P\u00ebr query-t e shpejta, mbani nj\u00eb replik\u00eb me vones\u00eb t\u00eb vog\u00ebl replikimi. P\u00ebr query-t e gjata t\u00eb raportimit, p\u00ebrdorni nj\u00eb replik\u00eb q\u00eb mund t\u00eb mbetet pas 6 or\u00eb ose edhe nj\u00eb dit\u00eb. Kjo \u00ebsht\u00eb krejt\u00ebsisht normale. <\/li>\n<\/ul>\n<p><\/p>\n<p>Pasojat i eliminojm\u00eb me t\u00eb nj\u00ebjt\u00ebn m\u00ebnyr\u00eb:<\/p>\n<p><\/p>\n<ul>\n<li>Gjejm\u00eb tabelat e fryra.<\/li>\n<li>Dhe i ngjeshim me mjetin m\u00eb t\u00eb p\u00ebrshtatsh\u00ebm p\u00ebr ne. <\/li>\n<\/ul>\n<p><\/p>\n<p>K\u00ebtu p\u00ebrfundon historia e dyt\u00eb. Kalojm\u00eb te historia e tret\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/c8784e0be27883640cb8f6919a1c25e0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Edhe kjo \u00ebsht\u00eb nj\u00eb situat\u00eb mjaft e zakonshme p\u00ebr ne, ku kryejm\u00eb migrim. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/a04b9cd88a0e7e8a3b960ef930eb2657.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>\u00c7do produkt softuerik rritet. K\u00ebrkesat ndaj tij ndryshojn\u00eb. Ne gjithsesi duam t\u00eb zhvillohemi. Dhe ndodh q\u00eb t\u00eb na duhet t\u00eb p\u00ebrdit\u00ebsojm\u00eb t\u00eb dh\u00ebnat n\u00eb tabel\u00eb, pra t\u00eb ekzekutojm\u00eb nj\u00eb update si pjes\u00eb e migrimit ton\u00eb p\u00ebr funksionalitetin e ri q\u00eb po zbatojm\u00eb n\u00eb kuad\u00ebr t\u00eb zhvillimit ton\u00eb. <\/li>\n<li>Formati i vjet\u00ebr i t\u00eb dh\u00ebnave nuk na p\u00ebrshtatet. T\u00eb themi se tani i referohemi tabel\u00ebs s\u00eb dyt\u00eb, ku kam operacionet p\u00ebr k\u00ebto llogari. Dhe supozojm\u00eb se ato ishin n\u00eb rubla, por ne vendos\u00ebm t\u00eb rrisim sakt\u00ebsin\u00eb dhe t\u2019i regjistrojm\u00eb n\u00eb kopek\u00eb. P\u00ebr k\u00ebt\u00eb duhet t\u00eb b\u00ebjm\u00eb nj\u00eb update: fusha me shum\u00ebn e operacionit t\u00eb shum\u00ebzohet me nj\u00ebqind. <\/li>\n<li>N\u00eb bot\u00ebn moderne ne p\u00ebrdorim mjete t\u00eb automatizuara p\u00ebr kontrollin e versioneve t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. P\u00ebr shembull, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.liquibase.org\/\">Liquibase<\/a><\/noindex>. E p\u00ebrfshijm\u00eb aty migrimin ton\u00eb. E testojm\u00eb n\u00eb baz\u00ebn ton\u00eb testuese. Gjith\u00e7ka \u00ebsht\u00eb n\u00eb rregull. Update kalon. E bllokon pun\u00ebn p\u00ebr nj\u00ebfar\u00eb kohe, por n\u00eb k\u00ebmbim marrim t\u00eb dh\u00ebna t\u00eb p\u00ebrdit\u00ebsuara. Dhe mbi k\u00ebt\u00eb mund t\u00eb nisim funksionalitetin e ri. I testuam dhe i verifikuam t\u00eb gjitha. T\u00eb gjith\u00eb e konfirmuan. <\/li>\n<li>U kryen punimet e planifikuara, u realizua migrimi. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/9e046583f7b1723fc1eaf61a03900918.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>K\u00ebtu para jush \u00ebsht\u00eb paraqitur migrimi me update. Meq\u00eb k\u00ebto ishin operacione p\u00ebr llogari, tabela ishte 15 GB. Dhe duke qen\u00eb se p\u00ebrdit\u00ebsojm\u00eb \u00e7do rresht, me update e frym\u00ebzuam tabel\u00ebn dyfish, sepse rishkruam \u00e7do rresht. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/06120c98ea43d0568045e71935c23592.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Gjat\u00eb migrimit nuk mund t\u00eb b\u00ebnim asgj\u00eb me k\u00ebt\u00eb tabel\u00eb, sepse t\u00eb gjitha k\u00ebrkesat ndaj saj hyn\u00eb n\u00eb radh\u00eb dhe prisnin derisa t\u00eb p\u00ebrfundonte ky update. Por k\u00ebtu dua t\u2019ju t\u00ebrheq v\u00ebmendjen te shifrat n\u00eb boshtin vertikal. Pra, kemi koh\u00eb mesatare t\u00eb k\u00ebrkes\u00ebs para migrimit rreth 5 milisekonda dhe ngarkesa e CPU-s\u00eb, si edhe numri i operacioneve bllok t\u00eb leximit nga disku, \u00ebsht\u00eb m\u00eb i ul\u00ebt se 7,5. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/69a3ba4d1d0cec7d39284e09e9d3055b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>E kryem migrimin dhe p\u00ebrs\u00ebri mor\u00ebm probleme. <\/p>\n<p><\/p>\n<p>Migrimi p\u00ebrfundoi me sukses, por:<\/p>\n<p><\/p>\n<ul>\n<li>Funksionaliteti i vjet\u00ebr filloi t\u00eb ekzekutohej m\u00eb ngadal\u00eb. <\/li>\n<li>Tabela u rrit s\u00ebrish n\u00eb madh\u00ebsi. <\/li>\n<li>Ngarkesa n\u00eb server u b\u00eb s\u00ebrish m\u00eb e lart\u00eb se m\u00eb par\u00eb. <\/li>\n<li>Dhe, natyrisht, nd\u00ebrkoh\u00eb q\u00eb ende po merremi me at\u00eb funksionalitet q\u00eb punonte mir\u00eb, e p\u00ebrmir\u00ebsuam pak. <\/li>\n<\/ul>\n<p><\/p>\n<p>Dhe kjo \u00ebsht\u00eb s\u00ebrish bloat, q\u00eb po na e v\u00ebshtir\u00ebson p\u00ebrs\u00ebri pun\u00ebn. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/becd7cce3a1c8821c81c96257f41202b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>K\u00ebtu demonstroj se tabela, ashtu si n\u00eb dy rastet e m\u00ebparshme, nuk ka nd\u00ebrmend t\u00eb kthehet n\u00eb madh\u00ebsin\u00eb e saj t\u00eb m\u00ebparshme. Ngarkesa mesatare e serverit duket n\u00eb rregull. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4d703a60d43a3460bc3782d49865b690.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00ebse i referohemi tabel\u00ebs s\u00eb llogarive, do t\u00eb shohim se koha mesatare e k\u00ebrkes\u00ebs \u00ebsht\u00eb rritur dyfish p\u00ebr k\u00ebt\u00eb tabel\u00eb. Ngarkesa n\u00eb CPU dhe numri i rreshtave t\u00eb p\u00ebrpunuar n\u00eb memorie u rrit mbi 7,5, nd\u00ebrsa m\u00eb par\u00eb ishte m\u00eb e ul\u00ebt. P\u00ebr CPU-t kjo u rrit 2 her\u00eb, nd\u00ebrsa p\u00ebr operacionet me blloqe 1,5 her\u00eb, pra mor\u00ebm degradim t\u00eb performanc\u00ebs s\u00eb serverit. Dhe si pasoj\u00eb, edhe degradim t\u00eb performanc\u00ebs s\u00eb aplikacionit ton\u00eb. Nd\u00ebrkoh\u00eb, numri i thirrjeve mbeti af\u00ebrsisht n\u00eb t\u00eb nj\u00ebjtin nivel. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/80772afa0643b29f5314a2482c9dfe0d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe k\u00ebtu gj\u00ebja kryesore \u00ebsht\u00eb t\u00eb kuptoni si t\u00eb kryhen sakt\u00eb migrime t\u00eb tilla. Ato duhen b\u00ebr\u00eb patjet\u00ebr. Ne i b\u00ebjm\u00eb k\u00ebto migrime rregullisht.<\/p>\n<p><\/p>\n<ul>\n<li>Migrime kaq t\u00eb m\u00ebdha nuk kryhen automatikisht. Ato duhet t\u00eb jen\u00eb gjithmon\u00eb n\u00ebn kontroll. <\/li>\n<li>K\u00ebrkohet mbik\u00ebqyrje nga nj\u00eb person me njohuri. N\u00ebse keni nj\u00eb DBA n\u00eb ekip, le ta b\u00ebj\u00eb DBA-ja. Kjo \u00ebsht\u00eb puna e tij. N\u00ebse jo, at\u00ebher\u00eb duhet ta b\u00ebj\u00eb personi m\u00eb me p\u00ebrvoj\u00eb, q\u00eb di t\u00eb punoj\u00eb me databazat. <\/li>\n<li>Skem\u00ebn e re t\u00eb databaz\u00ebs, edhe kur p\u00ebrdit\u00ebsojm\u00eb vet\u00ebm nj\u00eb kolon\u00eb, e p\u00ebrgatisim gjithmon\u00eb me faza, pra paraprakisht, p\u00ebrpara se t\u00eb publikohet versioni i ri i aplikacionit:<\/li>\n<li>Shtohen fusha t\u00eb reja, ku do t\u00eb shkruhen pik\u00ebrisht t\u00eb dh\u00ebnat e p\u00ebrdit\u00ebsuara. <\/li>\n<li>I transferojm\u00eb t\u00eb dh\u00ebnat nga fusha e vjet\u00ebr n\u00eb fush\u00ebn e re n\u00eb pjes\u00eb t\u00eb vogla. Pse e b\u00ebjm\u00eb k\u00ebt\u00eb? S\u00eb pari, ne e kontrollojm\u00eb vazhdimisht procesin. E dim\u00eb sa batch-e kemi transferuar tashm\u00eb dhe sa kan\u00eb mbetur. <\/li>\n<li>Efekti i dyt\u00eb pozitiv \u00ebsht\u00eb se nd\u00ebrmjet \u00e7do batch-i t\u00eb till\u00eb mbyllim transaksionin, hapim nj\u00eb t\u00eb ri dhe kjo i jep mund\u00ebsi autovacuum-it t\u00eb p\u00ebrpunoj\u00eb tabel\u00ebn dhe t\u00eb sh\u00ebnoj\u00eb rreshtat e vdekur p\u00ebr rip\u00ebrdorim. <\/li>\n<li>P\u00ebr rreshtat q\u00eb do t\u00eb shtohen gjat\u00eb funksionimit t\u00eb aplikacionit (nd\u00ebrkoh\u00eb aplikacioni i vjet\u00ebr vazhdon t\u00eb punoj\u00eb) shtojm\u00eb nj\u00eb trigger q\u00eb shkruan vlerat e reja n\u00eb fushat e reja. N\u00eb rastin ton\u00eb, kjo \u00ebsht\u00eb shum\u00ebzimi me nj\u00ebqind i vler\u00ebs s\u00eb vjet\u00ebr. <\/li>\n<li>N\u00ebse jemi shum\u00eb k\u00ebmb\u00ebngul\u00ebs dhe duam t\u00eb p\u00ebrdorim t\u00eb nj\u00ebjt\u00ebn fush\u00eb, at\u00ebher\u00eb pas p\u00ebrfundimit t\u00eb t\u00eb gjitha migrimeve dhe p\u00ebrpara vendosjes s\u00eb versionit t\u00eb ri t\u00eb aplikacionit, thjesht i riem\u00ebrtojm\u00eb fushat. T\u00eb vjetrat i kalojm\u00eb n\u00eb ndonj\u00eb em\u00ebr t\u00eb p\u00ebrkohsh\u00ebm, nd\u00ebrsa fushat e reja i riem\u00ebrtojm\u00eb me emrat e vjet\u00ebr. <\/li>\n<li>Dhe vet\u00ebm pas k\u00ebsaj nisim versionin e ri t\u00eb aplikacionit. <\/li>\n<\/ul>\n<p><\/p>\n<p>Dhe n\u00eb k\u00ebt\u00eb m\u00ebnyr\u00eb nuk do t\u00eb kemi bloat dhe nuk do t\u00eb p\u00ebsojm\u00eb r\u00ebnie t\u00eb performanc\u00ebs. <\/p>\n<p><\/p>\n<p>K\u00ebtu p\u00ebrfundoi historia e tret\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2afab2906b5ccd30e4c8772248818057.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pg-utils\/blob\/master\/sql\/table_bloat.sql\">https:\/\/github.com\/dataegret\/pg-utils\/blob\/master\/sql\/table_bloat.sql<\/a><\/noindex><\/p>\n<p><\/p>\n<p><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pg-utils\/blob\/master\/sql\/table_bloat_approx.sql\">https:\/\/github.com\/dataegret\/pg-utils\/blob\/master\/sql\/table_bloat_approx.sql<\/a><\/noindex><\/p>\n<p><\/p>\n<p>Dhe tani pak m\u00eb holl\u00ebsisht p\u00ebr mjetet q\u00eb p\u00ebrmenda n\u00eb historin\u00eb e par\u00eb. <\/p>\n<p><\/p>\n<p>Para se t\u00eb k\u00ebrkoni bloat, duhet patjet\u00ebr t\u00eb instaloni shtes\u00ebn <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/pgstattuple.html\">pgstattuple<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Q\u00eb t\u00eb mos shpikni vet\u00eb k\u00ebrkesa, ne i kemi p\u00ebrgatitur tashm\u00eb k\u00ebto k\u00ebrkesa n\u00eb pun\u00ebn ton\u00eb. Mund t'i p\u00ebrdorni. K\u00ebtu paraqiten dy k\u00ebrkesa. <\/p>\n<p><\/p>\n<ul>\n<li>E para ekzekutohet p\u00ebr nj\u00eb koh\u00eb m\u00eb t\u00eb gjat\u00eb, por ju tregon vlerat e sakta t\u00eb bloat p\u00ebr tabel\u00ebn. <\/li>\n<li>E dyta punon m\u00eb shpejt dhe \u00ebsht\u00eb shum\u00eb efektive kur duhet t\u00eb vler\u00ebsoni shpejt n\u00ebse tabela ka apo jo bloat. Duhet t\u00eb kuptoni gjithashtu se bloat n\u00eb tabelat Postgres ekziston gjithmon\u00eb. Kjo \u00ebsht\u00eb nj\u00eb ve\u00e7ori e modelit t\u00eb tij MVCC. <\/li>\n<li>Dhe 20% bloat \u00ebsht\u00eb normale p\u00ebr tabelat n\u00eb shumic\u00ebn e rasteve. Pra, nuk keni pse t\u00eb shqet\u00ebsoheni dhe ta kompaktoni k\u00ebt\u00eb tabel\u00eb. <\/li>\n<\/ul>\n<p><\/p>\n<p>Si t\u00eb identifikojm\u00eb tabelat q\u00eb jan\u00eb fryr\u00eb, konkretisht kur jan\u00eb fryr\u00eb me t\u00eb dh\u00ebna t\u00eb panevojshme, e sqaruam. <\/p>\n<p><\/p>\n<p>Tani, si t\u00eb korrigjohet bloat:<\/p>\n<p><\/p>\n<ul>\n<li>N\u00ebse kemi nj\u00eb tabel\u00eb t\u00eb vog\u00ebl dhe disqe t\u00eb mira, pra p\u00ebr nj\u00eb tabel\u00eb deri n\u00eb 1 GB \u00ebsht\u00eb plot\u00ebsisht e mundur t\u00eb p\u00ebrdoret VACUUM FULL. Do t\u00eb vendos\u00eb nj\u00eb bllokim ekskluziv n\u00eb tabel\u00eb p\u00ebr disa sekonda, por nga ana tjet\u00ebr e kryen pun\u00ebn shpejt dhe n\u00eb m\u00ebnyr\u00eb vendimtare. \u00c7far\u00eb b\u00ebn VACUUM FULL? Ai vendos nj\u00eb bllokim ekskluziv n\u00eb tabel\u00eb dhe i rishkruan rreshtat aktiv\u00eb nga tabela e vjet\u00ebr n\u00eb nj\u00eb tabel\u00eb t\u00eb re. N\u00eb fund, i nd\u00ebrron vendet e tyre. Skedar\u00ebt e vjet\u00ebr i fshin dhe vendos skedar\u00ebt e rinj n\u00eb vend t\u00eb tyre. Por gjat\u00eb ekzekutimit t\u00eb tij, ai mban nj\u00eb bllokim ekskluziv mbi tabel\u00ebn. Kjo do t\u00eb thot\u00eb se me k\u00ebt\u00eb tabel\u00eb nuk do t\u00eb mund t\u00eb b\u00ebni asgj\u00eb: as t\u00eb shkruani n\u00eb t\u00eb, as t\u00eb lexoni prej saj, as ta modifikoni. Dhe VACUUM FULL k\u00ebrkon hap\u00ebsir\u00eb shtes\u00eb n\u00eb disk p\u00ebr t\u00eb shkruar t\u00eb dh\u00ebnat.<\/li>\n<li>Mjeti tjet\u00ebr <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/reorg\/pg_repack\">pg_repack<\/a><\/noindex>. Sipas parimit t\u00eb funksionimit, ai \u00ebsht\u00eb shum\u00eb i ngjash\u00ebm me VACUUM FULL, sepse edhe ai i rishkruan t\u00eb dh\u00ebnat nga skedar\u00ebt e vjet\u00ebr n\u00eb t\u00eb rinj dhe i z\u00ebvend\u00ebson ato n\u00eb tabel\u00eb. Por ai nuk merr bllokim ekskluziv mbi tabel\u00ebn q\u00eb n\u00eb fillim t\u00eb pun\u00ebs s\u00eb tij; e merr vet\u00ebm n\u00eb momentin kur t\u00eb dh\u00ebnat jan\u00eb gati p\u00ebr z\u00ebvend\u00ebsimin e skedar\u00ebve. K\u00ebrkesat p\u00ebr burimet e diskut jan\u00eb t\u00eb ngjashme me ato t\u00eb VACUUM FULL. Ju duhet hap\u00ebsir\u00eb shtes\u00eb n\u00eb disk, dhe kjo ndonj\u00ebher\u00eb b\u00ebhet kritike n\u00ebse keni tabela me p\u00ebrmasa terabajt\u00ebsh. Ai \u00ebsht\u00eb gjithashtu mjaft k\u00ebrkues p\u00ebr CPU, sepse punon intensivisht me hyrje-daljet. <\/li>\n<li>Vegla e tret\u00eb \u00ebsht\u00eb <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pgcompacttable\">pgcompacttable<\/a><\/noindex>. Ajo i trajton m\u00eb me kujdes burimet, sepse funksionon sipas parimeve paksa t\u00eb ndryshme. Thelbi i pgcompacttable \u00ebsht\u00eb se, me an\u00eb t\u00eb update-ve n\u00eb tabel\u00eb, i zhvendos t\u00eb gjitha rreshtat aktiv\u00eb n\u00eb fillim t\u00eb tabel\u00ebs. M\u00eb pas ekzekuton vacuum p\u00ebr at\u00eb tabel\u00eb, sepse e dim\u00eb q\u00eb n\u00eb fillim kemi rreshta aktiv\u00eb, nd\u00ebrsa n\u00eb fund rreshta t\u00eb vdekur. Pastaj vacuum-i vet\u00eb e pret at\u00eb bisht, pra nuk k\u00ebrkon shum\u00eb hap\u00ebsir\u00eb shtes\u00eb n\u00eb disk. P\u00ebr m\u00eb tep\u00ebr, mund t\u00eb kufizohet edhe p\u00ebr sa i p\u00ebrket konsumit t\u00eb burimeve. <\/li>\n<\/ul>\n<p><\/p>\n<p>Kaq p\u00ebr mjetet. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL. Andrey Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4187915e54e54a0b85a342fe0280f78f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00ebse tema e bloat ju duket interesante p\u00ebr ta studiuar m\u00eb thell\u00eb, ja disa lidhje t\u00eb dobishme:<\/p>\n<p><\/p>\n<ul>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/www.slideshare.net\/alexius2Mb\/where-is-the-space-postgres\">https:\/\/www.slideshare.net\/alexius2Mb\/where-is-the-space-postgres<\/a><\/noindex> \u2013 ky \u00ebsht\u00eb prezantimi i kolegut tim. \u00cbsht\u00eb nj\u00eb material i p\u00ebrgjithsh\u00ebm p\u00ebr at\u00eb se ku shkon hap\u00ebsira n\u00eb Postgres gjat\u00eb pun\u00ebs s\u00eb tij. Aty ka edhe nj\u00eb pjes\u00eb shum\u00eb t\u00eb madhe dhe t\u00eb detajuar teknike p\u00ebr administrator\u00ebt e bazave t\u00eb t\u00eb dh\u00ebnave rreth bloat. <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pg-utils\">https:\/\/github.com\/dataegret\/pg-utils<\/a><\/noindex> \u2013 kjo \u00ebsht\u00eb lidhja p\u00ebr te repository yn\u00eb, ku ruajm\u00eb shum\u00eb skripte t\u00eb dobishme p\u00ebr kontrollin e gjendjes s\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Aty mund t\u00eb gjeni skripte p\u00ebr zbulimin e bloat. <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/reorg\/pg_repack\">I tret\u00eb<\/a><\/noindex> dhe <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pgcompacttable\">Kat\u00ebrta<\/a><\/noindex> lidhje p\u00ebr mjetet q\u00eb do t\u2019ju ndihmojn\u00eb t\u00eb ngjeshni tabelat. <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"http:\/\/blog.dataegret.com\/2Mb018\/03\/postgresql-bloatbusters.html\">http:\/\/blog.dataegret.com\/2Mb018\/03\/postgresql-bloatbusters.html<\/a><\/noindex> \u2013 ky \u00ebsht\u00eb nj\u00eb postim i kolegut tim. Aty ai e analizon bloat n\u00eb m\u00ebnyr\u00eb mjaft serioze dhe t\u00eb detajuar nga pik\u00ebpamja teknike, n\u00eb nj\u00eb nivel tashm\u00eb t\u00eb af\u00ebrt me administrator\u00ebt. <\/li>\n<\/ul>\n<p><\/p>\n<p>Un\u00eb k\u00ebtu u p\u00ebrpoqa m\u00eb shum\u00eb t\u00eb tregoj nj\u00eb skenar alarmues p\u00ebr zhvilluesit, sepse ata jan\u00eb p\u00ebrdoruesit tan\u00eb t\u00eb drejtp\u00ebrdrejt\u00eb t\u00eb bazave t\u00eb t\u00eb dh\u00ebnave dhe duhet t\u00eb kuptojn\u00eb se \u00e7far\u00eb pasojash sjellin veprimet e caktuara. Shpresoj q\u00eb ia kam dal\u00eb. Faleminderit p\u00ebr v\u00ebmendjen!<\/p>\n<p><\/p>\n<p>Pyetje<\/p>\n<p><\/p>\n<p><em>Faleminderit p\u00ebr prezantimin! Ju fol\u00ebt p\u00ebr m\u00ebnyrat se si mund t\u00eb zbulohen problemet. Po si mund t\u00eb parandalohen? Pra, kam pasur nj\u00eb situat\u00eb kur query-t nuk rrinin pezull vet\u00ebm sepse i drejtoheshin disa sh\u00ebrbimeve t\u00eb jashtme. Ishin thjesht disa joins krejt t\u00eb \u00e7mendura. Kishte edhe query shum\u00eb t\u00eb vogla, n\u00eb dukje t\u00eb pad\u00ebmshme, q\u00eb rrinin pezull p\u00ebr nj\u00eb dit\u00eb t\u00eb t\u00ebr\u00eb e m\u00eb pas fillonin t\u00eb b\u00ebnin gj\u00ebra t\u00eb pakuptimta. Pra, \u00ebsht\u00eb shum\u00eb e ngjashme me at\u00eb q\u00eb p\u00ebrshkruat ju. Si mund t\u00eb monitorohet kjo? T\u00eb rrish dhe t\u00eb kontrollosh vazhdimisht se cili query ka ngecur? Si mund t\u00eb parandalohet kjo?<\/em><\/p>\n<p><\/p>\n<p>N\u00eb k\u00ebt\u00eb rast, kjo \u00ebsht\u00eb nj\u00eb detyr\u00eb p\u00ebr administrator\u00ebt e kompanis\u00eb suaj, jo domosdoshm\u00ebrisht p\u00ebr DBA.<\/p>\n<p><\/p>\n<p><em>Un\u00eb jam administratore.<\/em><\/p>\n<p><\/p>\n<p>N\u00eb PostgreSQL ekziston nj\u00eb view i quajtur pg_stat_activity, ku shfaqen query-t e ngecura. Dhe ju mund t\u00eb shihni sa koh\u00eb kan\u00eb q\u00ebndruar aty.<\/p>\n<p><\/p>\n<p><em>Duhet t\u00eb hyj e ta kontrolloj \u00e7do 5 minuta?<\/em><\/p>\n<p><\/p>\n<p>Konfiguroni cron dhe monitorojeni. N\u00ebse keni nj\u00eb k\u00ebrkes\u00eb q\u00eb zgjat shum\u00eb, mjafton t\u00eb d\u00ebrgoni nj\u00eb email. Pra, nuk keni nevoj\u00eb ta kontrolloni me sy \u2014 kjo mund t\u00eb automatizohet. Do t\u2019ju vij\u00eb nj\u00eb email dhe ju reagoni ndaj tij. Ose mund ta automatizoni edhe ekzekutimin e veprimeve.<\/p>\n<p><\/p>\n<p><em>A ka arsye t\u00eb qarta pse ndodh kjo?<\/em><\/p>\n<p><\/p>\n<p>Disa prej tyre i p\u00ebrmenda. Shembujt e tjer\u00eb jan\u00eb m\u00eb t\u00eb nd\u00ebrlikuar. Dhe aty biseda mund t\u00eb zgjas\u00eb shum\u00eb.<\/p>\n<p><\/p>\n<p><em>Faleminderit p\u00ebr prezantimin! Doja t\u00eb sqaroja di\u00e7ka p\u00ebr utilit\u00ebn pg_repack. N\u00ebse ajo nuk vendos bllokim ekskluziv, at\u00ebher\u00eb\u2026<\/em><\/p>\n<p><\/p>\n<p>Ajo vendos bllokim ekskluziv. <\/p>\n<p><\/p>\n<p>\u2026 <em>at\u00ebher\u00eb un\u00eb potencialisht mund t\u00eb humbas t\u00eb dh\u00ebna. Aplikacioni im nuk duhet t\u00eb shkruaj\u00eb asgj\u00eb gjat\u00eb asaj kohe?<\/em><\/p>\n<p><\/p>\n<p>Jo, ai punon normalisht me tabel\u00ebn, pra pg_repack fillimisht zhvendos t\u00eb gjitha rreshtat aktiv\u00eb q\u00eb ekzistojn\u00eb. Natyrisht, gjat\u00eb k\u00ebsaj kohe ndodh edhe ndonj\u00eb shkrim n\u00eb tabel\u00eb. Ai thjesht e shton edhe k\u00ebt\u00eb pjes\u00eb t\u00eb mbetur n\u00eb fund. <\/p>\n<p><\/p>\n<p><em>Pra, n\u00eb fund gjithsesi e b\u00ebn?<\/em><\/p>\n<p><\/p>\n<p>N\u00eb fund ai merr nj\u00eb bllokim ekskluziv p\u00ebr t\u00eb z\u00ebvend\u00ebsuar k\u00ebta skedar\u00eb me nj\u00ebri-tjetrin. <\/p>\n<p><\/p>\n<p><em>A do t\u00eb jet\u00eb kjo m\u00eb e shpejt\u00eb se VACUUM FULL?<\/em><\/p>\n<p><\/p>\n<p>VACUUM FULL, sapo nis, merr menj\u00ebher\u00eb bllokim ekskluziv. Dhe derisa t\u00eb p\u00ebrfundoj\u00eb gjith\u00e7ka, nuk e l\u00ebshon. Nd\u00ebrsa pg_repack merr bllokim ekskluziv vet\u00ebm n\u00eb momentin e z\u00ebvend\u00ebsimit t\u00eb skedar\u00ebve. N\u00eb at\u00eb moment nuk do t\u00eb mund t\u00eb shkruani aty, por t\u00eb dh\u00ebnat nuk do t\u00eb humbasin, gjith\u00e7ka do t\u00eb jet\u00eb n\u00eb rregull. <\/p>\n<p><\/p>\n<p><em>P\u00ebrsh\u00ebndetje! Ju fol\u00ebt p\u00ebr m\u00ebnyr\u00ebn si funksionon autovacuum. Aty ishte nj\u00eb grafik me qeliza shkrimi t\u00eb kuqe, t\u00eb verdha dhe t\u00eb gjelbra. Pra, t\u00eb verdhat \u2014 ai i ka sh\u00ebnuar si t\u00eb fshira. Dhe si pasoj\u00eb, a mund t\u00eb shkruhet di\u00e7ka e re n\u00eb to?<\/em><\/p>\n<p><\/p>\n<p>Po. Postgres nuk i fshin rreshtat. Kjo \u00ebsht\u00eb nj\u00eb ve\u00e7ori e tij. N\u00ebse p\u00ebrdit\u00ebsojm\u00eb nj\u00eb rresht, t\u00eb vjetrin e sh\u00ebnojm\u00eb si t\u00eb fshir\u00eb. Aty vendoset ID e transaksionit q\u00eb e ndryshoi at\u00eb rresht, dhe shkruajm\u00eb nj\u00eb rresht t\u00eb ri. Dhe kemi sesione q\u00eb potencialisht mund t\u2019i lexojn\u00eb ato. N\u00eb nj\u00eb moment ato b\u00ebhen krejt\u00ebsisht t\u00eb vjetra. Thelbi i pun\u00ebs s\u00eb autovacuum \u00ebsht\u00eb q\u00eb ai kalon mbi k\u00ebta rreshta dhe i sh\u00ebnon si t\u00eb panevojsh\u00ebm. Dhe aty mund t\u00eb rishkruhen t\u00eb dh\u00ebnat. <\/p>\n<p><\/p>\n<p><em>E kuptova. Por pyetja nuk \u00ebsht\u00eb tamam p\u00ebr k\u00ebt\u00eb. Nuk e p\u00ebrfundova. Supozojm\u00eb q\u00eb kemi nj\u00eb tabel\u00eb. N\u00eb t\u00eb ka fusha me madh\u00ebsi t\u00eb ndryshueshme. Dhe n\u00ebse p\u00ebrpiqem t\u00eb fus di\u00e7ka t\u00eb re, kjo mund t\u00eb mos futet thjesht n\u00eb qeliz\u00ebn e vjet\u00ebr.<\/em> <\/p>\n<p><\/p>\n<p>Jo, n\u00eb \u00e7do rast p\u00ebrdit\u00ebsohet i gjith\u00eb rreshti. N\u00eb Postgres ka dy modele t\u00eb ruajtjes s\u00eb t\u00eb dh\u00ebnave. Zgjedhja varet nga lloji i t\u00eb dh\u00ebnave. Ka t\u00eb dh\u00ebna q\u00eb ruhen drejtp\u00ebrdrejt n\u00eb tabel\u00eb, dhe ka edhe t\u00eb dh\u00ebna TOAST. K\u00ebto jan\u00eb v\u00ebllime t\u00eb m\u00ebdha t\u00eb dh\u00ebnash: tekst, JSON. Ato ruhen n\u00eb tabela t\u00eb ve\u00e7anta. Dhe me k\u00ebto tabela ndodh e nj\u00ebjta histori me bloat, pra gjith\u00e7ka \u00ebsht\u00eb nj\u00ebsoj. Thjesht jan\u00eb nxjerr\u00eb ve\u00e7mas. <\/p>\n<p><\/p>\n<p><em>Faleminderit p\u00ebr prezantimin! Sa e pranueshme \u00ebsht\u00eb t\u00eb p\u00ebrdoret statement timeout p\u00ebr t\u00eb kufizuar koh\u00ebzgjatjen e k\u00ebrkesave?<\/em><\/p>\n<p><\/p>\n<p>\u00cbsht\u00eb plot\u00ebsisht e pranueshme. Ne e p\u00ebrdorim kudo. Duke qen\u00eb se nuk kemi sh\u00ebrbimet tona, por ofrojm\u00eb mb\u00ebshtetje n\u00eb distanc\u00eb, kemi klient\u00eb shum\u00eb t\u00eb ndrysh\u00ebm. Dhe t\u00eb gjith\u00eb jan\u00eb plot\u00ebsisht t\u00eb k\u00ebnaqur me k\u00ebt\u00eb. Pra, kemi detyra n\u00eb cron q\u00eb e kontrollojn\u00eb k\u00ebt\u00eb. Me klientin thjesht bihet dakord p\u00ebr koh\u00ebzgjatjen e sesioneve, para s\u00eb cil\u00ebs ne nuk i nd\u00ebrpresim. Mund t\u00eb jet\u00eb nj\u00eb minut\u00eb, mund t\u00eb jen\u00eb 10 minuta. Kjo varet nga ngarkesa e baz\u00ebs dhe nga q\u00ebllimi i saj. Por te t\u00eb gjith\u00eb ne p\u00ebrdorim pg_stat_activity.<\/p>\n<p><\/p>\n<p><em>Faleminderit p\u00ebr prezantimin! Po p\u00ebrpiqem ta p\u00ebrshtat at\u00eb me aplikacionet e mia. Dhe duket se ne e nisim transaksionin kudo dhe e p\u00ebrfundojm\u00eb gjithmon\u00eb n\u00eb m\u00ebnyr\u00eb eksplicite. N\u00ebse ka ndonj\u00eb exception, gjithsesi ndodh rollback. Dhe k\u00ebtu u mendova. A mund t\u00eb nis\u00eb transaksioni jo n\u00eb m\u00ebnyr\u00eb eksplicite? Ndoshta kjo \u00ebsht\u00eb nj\u00eb pyetje me sugjerim p\u00ebr zonjush\u00ebn. N\u00ebse thjesht b\u00ebj nj\u00eb p\u00ebrdit\u00ebsim t\u00eb nj\u00eb regjistrimi, a do t\u00eb nis\u00eb transaksioni n\u00eb PostgreSQL dhe do t\u00eb p\u00ebrfundoj\u00eb vet\u00ebm kur t\u00eb mbyllet lidhja?<\/em><\/p>\n<p><\/p>\n<p>N\u00ebse po flisni tani p\u00ebr nivelin e aplikacionit, kjo varet nga driver-i q\u00eb p\u00ebrdorni dhe nga ORM-i q\u00eb p\u00ebrdoret. Ka shum\u00eb konfigurime aty. N\u00ebse e keni t\u00eb aktivizuar auto commit on, at\u00ebher\u00eb transaksioni nis dhe mbyllet menj\u00ebher\u00eb.<\/p>\n<p><\/p>\n<p><em>Pra, mbyllet menj\u00ebher\u00eb pas update-it?<\/em><\/p>\n<p><\/p>\n<p>Kjo varet nga konfigurimet. Nj\u00ebrin konfigurim e p\u00ebrmenda: auto commit on. \u00cbsht\u00eb mjaft i zakonsh\u00ebm. N\u00ebse \u00ebsht\u00eb i aktivizuar, transaksioni hapet dhe mbyllet menj\u00ebher\u00eb. N\u00ebse nuk keni th\u00ebn\u00eb n\u00eb m\u00ebnyr\u00eb eksplicite \u201cstart transaction\u201d dhe \u201cend transaction\u201d, por thjesht keni nisur nj\u00eb k\u00ebrkes\u00eb n\u00eb sesion. <\/p>\n<p><\/p>\n<p><em>P\u00ebrsh\u00ebndetje! Faleminderit p\u00ebr prezantimin! Le t\u00eb imagjinojm\u00eb q\u00eb kemi nj\u00eb baz\u00eb q\u00eb fryhet e fryhet dhe pastaj n\u00eb server mbaron hap\u00ebsira. A ka ndonj\u00eb mjet p\u00ebr ta rregulluar k\u00ebt\u00eb situat\u00eb?<\/em> <\/p>\n<p><\/p>\n<p>Hap\u00ebsira n\u00eb server, n\u00eb m\u00ebnyr\u00eb ideale, duhet monitoruar. <\/p>\n<p><\/p>\n<p><em>P\u00ebr shembull, DBA shkoi t\u00eb pij\u00eb \u00e7aj, ishte me pushime e k\u00ebshtu me radh\u00eb.<\/em><\/p>\n<p><\/p>\n<p>Kur krijohet sistemi i skedar\u00ebve, aty zakonisht rezervohet t\u00eb pakt\u00ebn nj\u00eb hap\u00ebsir\u00eb ku t\u00eb dh\u00ebnat nuk shkruhen. <\/p>\n<p><\/p>\n<p><em>Po n\u00ebse s\u2019mbetet fare asnj\u00eb hap\u00ebsir\u00eb?<\/em><\/p>\n<p><\/p>\n<p>Aty quhet pik\u00ebrisht reserved space, dometh\u00ebn\u00eb mund t\u00eb lirohet dhe, n\u00eb var\u00ebsi t\u00eb madh\u00ebsis\u00eb me t\u00eb cil\u00ebn \u00ebsht\u00eb krijuar, ju fitoni hap\u00ebsir\u00eb t\u00eb lir\u00eb. Si parazgjedhje nuk e di sa \u00ebsht\u00eb. N\u00eb rastin tjet\u00ebr, duhet t\u00eb shtoni disqe q\u00eb t\u00eb keni hap\u00ebsir\u00eb p\u00ebr t\u00eb kryer operacionin e rikuperimit. Mund t\u00eb fshini ndonj\u00eb tabel\u00eb p\u00ebr t\u00eb cil\u00ebn jeni t\u00eb sigurt se nuk ju duhet. <\/p>\n<p><\/p>\n<p><em>Nuk ka mjete t\u00eb tjera?<\/em><\/p>\n<p><\/p>\n<p>Kjo \u00ebsht\u00eb gjithmon\u00eb pun\u00eb manuale. Dhe n\u00eb vend p\u00ebrcaktohet \u00e7far\u00eb \u00ebsht\u00eb m\u00eb mir\u00eb t\u00eb b\u00ebhet, sepse ka t\u00eb dh\u00ebna kritike dhe t\u00eb tjera jokritike. P\u00ebr \u00e7do baz\u00eb t\u00eb dh\u00ebnash dhe aplikacion q\u00eb punon me t\u00eb, kjo varet nga biznesi. Gjithmon\u00eb vendoset sipas situat\u00ebs konkrete. <\/p>\n<p><\/p>\n<p><em>Faleminderit p\u00ebr prezantimin! Kam dy pyetje. S\u00eb pari, ju treguat slide ku shihej se, n\u00eb rastin e transaksioneve t\u00eb bllokuara, rritet si v\u00ebllimi i tablespace-it, ashtu edhe madh\u00ebsia e indeksit. M\u00eb pas gjat\u00eb prezantimit pati shum\u00eb utilitete q\u00eb e kompaktuan tabel\u00ebn. Po me indeksin \u00e7far\u00eb ndodh?<\/em><\/p>\n<p><\/p>\n<p>Edhe ato i kompaktojn\u00eb. <\/p>\n<p><\/p>\n<p><em>Por vacuum nuk e prek indeksin?<\/em><\/p>\n<p><\/p>\n<p>Disa prej tyre punojn\u00eb edhe me indeksin. P\u00ebr shembull, pg_rapack, pgcompacttable. Vacuum i rikrijon indekset, pra i prek ato. Thelbi i VACUUM FULL \u00ebsht\u00eb t\u00eb rishkruaj\u00eb gjith\u00e7ka, dometh\u00ebn\u00eb ai punon me t\u00eb gjitha. <\/p>\n<p><\/p>\n<p><em>Dhe pyetja e dyt\u00eb. Nuk e kuptova pse raportet n\u00eb replika varen kaq shum\u00eb nga vet\u00eb replikimi. Mua m\u00eb dukej se raportet jan\u00eb lexim, nd\u00ebrsa replikimi \u00ebsht\u00eb shkrim.<\/em> <\/p>\n<p><\/p>\n<p>Ku lind konflikti i replikimit? Kemi Master-in, ku zhvillohen proceset. Kemi autovacuum. \u00c7far\u00eb b\u00ebn realisht autovacuum? Ai heq disa rreshta t\u00eb vjet\u00ebr. N\u00ebse n\u00eb at\u00eb moment n\u00eb replik\u00eb po ekzekutohet nj\u00eb query q\u00eb lexon k\u00ebta rreshta t\u00eb vjet\u00ebr, dhe n\u00eb Master ka ndodhur situata q\u00eb autovacuum i ka sh\u00ebnuar k\u00ebta rreshta si t\u00eb gatsh\u00ebm p\u00ebr rishkrim, at\u00ebher\u00eb ne i kemi rishkruar. Pastaj vjen paketa e t\u00eb dh\u00ebnave kur duhet t\u00eb rishkruajm\u00eb ato rreshta q\u00eb i duhen query-t n\u00eb replik\u00eb, dhe procesi i replikimit do t\u00eb pres\u00eb timeout-in q\u00eb keni konfiguruar. M\u00eb pas PostgreSQL do t\u00eb vendos\u00eb \u00e7far\u00eb \u00ebsht\u00eb m\u00eb e r\u00ebnd\u00ebsishme p\u00ebr t\u00eb. Dhe p\u00ebr t\u00eb, replikimi \u00ebsht\u00eb m\u00eb i r\u00ebnd\u00ebsish\u00ebm se query, ndaj ai do ta nd\u00ebrpres\u00eb query-n q\u00eb t\u00eb zbatoj\u00eb k\u00ebto ndryshime n\u00eb replik\u00eb. <\/p>\n<p><\/p>\n<p><em>Andrej, kam nj\u00eb pyetje. K\u00ebto grafik\u00eb t\u00eb shk\u00eblqyer q\u00eb treguat gjat\u00eb prezantimit, a jan\u00eb rezultat i pun\u00ebs s\u00eb ndonj\u00eb utilitari tuaj? Me \u00e7far\u00eb i keni nd\u00ebrtuar grafik\u00ebt?<\/em><\/p>\n<p><\/p>\n<p>\u00cbsht\u00eb nj\u00eb sh\u00ebrbim <noindex><a rel=\"nofollow\" href=\"https:\/\/okmeter.io\/\">Okmeter<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p><em>A \u00ebsht\u00eb produkt komercial?<\/em><\/p>\n<p><\/p>\n<p>Po. \u00cbsht\u00eb produkt komercial.<\/p>\n<p>Burimi: <a content=\"nofollow\" rel=\"nofollow\" href=\"https:\/\/habr.com\/ru\/post\/501040\/\">habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"<p>\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u043d\u0430\u0447\u0430\u043b\u0430 2016 \u0433\u043e\u0434\u0430 \u0410\u043d\u0434\u0440\u0435\u044f \u0421\u0430\u043b\u044c\u043d\u0438\u043a\u043e\u0432\u0430 &quot;\u0422\u0438\u043f\u043e\u0432\u044b\u0435 \u043e\u0448\u0438\u0431\u043a\u0438 \u0432 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0432\u0435\u0434\u0443\u0442 \u043a bloat \u0432 postgresql&quot; \u0412 \u0434\u0430\u043d\u043d\u043e\u043c \u0434\u043e\u043a\u043b\u0430\u0434\u0435 \u044f \u0440\u0430\u0437\u0431\u0435\u0440\u0443 \u043e\u0441\u043d\u043e\u0432\u043d\u044b\u0435 \u043e\u0448\u0438\u0431\u043a\u0438 \u0432 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0432\u043e\u0437\u043d\u0438\u043a\u0430\u044e\u0442 \u043d\u0430 \u044d\u0442\u0430\u043f\u0435 \u043f\u0440\u043e\u0435\u043a\u0442\u0438\u0440\u043e\u0432\u0430\u043d\u0438\u044f \u0438 \u043d\u0430\u043f\u0438\u0441\u0430\u043d\u0438\u044f \u043a\u043e\u0434\u0430 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f. \u0418 \u0432\u043e\u0437\u044c\u043c\u0443 \u0442\u043e\u043b\u044c\u043a\u043e \u0442\u0435 \u043e\u0448\u0438\u0431\u043a\u0438, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0432\u0435\u0434\u0443\u0442 \u043a bloat \u0432 Postgresql. \u041a\u0430\u043a \u043f\u0440\u0430\u0432\u0438\u043b\u043e, \u044d\u0442\u043e \u043d\u0430\u0447\u0430\u043b\u043e \u043a\u043e\u043d\u0446\u0430 \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u0438 [&hellip;]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":81090,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-81089","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t<!-- All in One SEO 5.0.2 - aioseo.com -->\n\t<meta name=\"description\" content=\"\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u043d\u0430\u0447\u0430\u043b\u0430 2016 \u0433\u043e\u0434\u0430 \u0410\u043d\u0434\u0440\u0435\u044f \u0421\u0430\u043b\u044c\u043d\u0438\u043a\u043e\u0432\u0430 &quot;\u0422\u0438\u043f\u043e\u0432\u044b\u0435 \u043e\u0448\u0438\u0431\u043a\u0438 \u0432 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0432\u0435\u0434\u0443\u0442 \u043a bloat \u0432 postgresql&quot; \u0412 \u0434\u0430\u043d\u043d\u043e\u043c \u0434\u043e\u043a\u043b\u0430\u0434\u0435 \u044f \u0440\u0430\u0437\u0431\u0435\u0440\u0443 \u043e\u0441\u043d\u043e\u0432\u043d\u044b\u0435.\" \/>\n\t<meta name=\"robots\" content=\"max-image-preview:large\" \/>\n\t<meta name=\"author\" content=\"Yuri Gagarin\"\/>\n\t<link rel=\"canonical\" href=\"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/tipovye-oshibki-v-prilozheniyah-kotorye-vedut-k-bloat-v-postgresql-andrej-salnikov\" \/>\n\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.2\" \/>\n\t\t<meta property=\"og:locale\" content=\"sq_AL\" \/>\n\t\t<meta property=\"og:site_name\" content=\"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b\" \/>\n\t\t<meta property=\"og:type\" content=\"article\" \/>\n\t\t<meta property=\"og:title\" content=\"\ud83e\udd47\u0422\u0438\u043f\u043e\u0432\u044b\u0435 \u043e\u0448\u0438\u0431\u043a\u0438 \u0432 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0432\u0435\u0434\u0443\u0442 \u043a bloat \u0432 postgresql. \u0410\u043d\u0434\u0440\u0435\u0439 \u0421\u0430\u043b\u044c\u043d\u0438\u043a\u043e\u0432 | ProHoster\" \/>\n\t\t<meta property=\"og:description\" content=\"\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u043d\u0430\u0447\u0430\u043b\u0430 2016 \u0433\u043e\u0434\u0430 \u0410\u043d\u0434\u0440\u0435\u044f \u0421\u0430\u043b\u044c\u043d\u0438\u043a\u043e\u0432\u0430 &quot;\u0422\u0438\u043f\u043e\u0432\u044b\u0435 \u043e\u0448\u0438\u0431\u043a\u0438 \u0432 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0432\u0435\u0434\u0443\u0442 \u043a bloat \u0432 postgresql&quot; \u0412 \u0434\u0430\u043d\u043d\u043e\u043c \u0434\u043e\u043a\u043b\u0430\u0434\u0435 \u044f \u0440\u0430\u0437\u0431\u0435\u0440\u0443 \u043e\u0441\u043d\u043e\u0432\u043d\u044b\u0435.\" \/>\n\t\t<meta property=\"og:url\" content=\"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/tipovye-oshibki-v-prilozheniyah-kotorye-vedut-k-bloat-v-postgresql-andrej-salnikov\" \/>\n\t\t<meta property=\"og:image\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:secure_url\" content=\"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg\" \/>\n\t\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t\t<meta property=\"article:published_time\" content=\"2020-05-10T23:42:24+00:00\" \/>\n\t\t<meta property=\"article:modified_time\" content=\"2020-05-10T23:42:24+00:00\" \/>\n\t\t<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/prohoster\" \/>\n\t\t<!-- All in One SEO -->\n\n","aioseo_head_json":{"title":"\ud83e\udd47Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Salnikov | ProHoster","description":"Ju propozoj t\u00eb njiheni me transkriptin e prezantimit t\u00eb fillimit t\u00eb vitit 2016 nga Andrey Salnikov \"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql\". N\u00eb k\u00ebt\u00eb prezantim do t\u00eb shqyrtoj kryesoret.","canonical_url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/tipovye-oshibki-v-prilozheniyah-kotorye-vedut-k-bloat-v-postgresql-andrej-salnikov","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sq_AL","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0422\u0438\u043f\u043e\u0432\u044b\u0435 \u043e\u0448\u0438\u0431\u043a\u0438 \u0432 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0432\u0435\u0434\u0443\u0442 \u043a bloat \u0432 postgresql. \u0410\u043d\u0434\u0440\u0435\u0439 \u0421\u0430\u043b\u044c\u043d\u0438\u043a\u043e\u0432 | ProHoster","og:description":"\u041f\u0440\u0435\u0434\u043b\u0430\u0433\u0430\u044e \u043e\u0437\u043d\u0430\u043a\u043e\u043c\u0438\u0442\u044c\u0441\u044f \u0441 \u0440\u0430\u0441\u0448\u0438\u0444\u0440\u043e\u0432\u043a\u043e\u0439 \u0434\u043e\u043a\u043b\u0430\u0434\u0430 \u043d\u0430\u0447\u0430\u043b\u0430 2016 \u0433\u043e\u0434\u0430 \u0410\u043d\u0434\u0440\u0435\u044f \u0421\u0430\u043b\u044c\u043d\u0438\u043a\u043e\u0432\u0430 &quot;\u0422\u0438\u043f\u043e\u0432\u044b\u0435 \u043e\u0448\u0438\u0431\u043a\u0438 \u0432 \u043f\u0440\u0438\u043b\u043e\u0436\u0435\u043d\u0438\u044f\u0445, \u043a\u043e\u0442\u043e\u0440\u044b\u0435 \u0432\u0435\u0434\u0443\u0442 \u043a bloat \u0432 postgresql&quot; \u0412 \u0434\u0430\u043d\u043d\u043e\u043c \u0434\u043e\u043a\u043b\u0430\u0434\u0435 \u044f \u0440\u0430\u0437\u0431\u0435\u0440\u0443 \u043e\u0441\u043d\u043e\u0432\u043d\u044b\u0435.","og:url":"https:\/\/prohoster.info\/sq\/blog\/administrirovanie\/tipovye-oshibki-v-prilozheniyah-kotorye-vedut-k-bloat-v-postgresql-andrej-salnikov","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-05-10T23:42:24+00:00","article:modified_time":"2020-05-10T23:42:24+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"81089","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 16:05:22","updated":"2022-09-27 16:01:50","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/81089","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/comments?post=81089"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/posts\/81089\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media\/81090"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/media?parent=81089"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/categories?post=81089"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sq\/wp-json\/wp\/v2\/tags?post=81089"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}