{"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":"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal'nikov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>Ju lutem, njihni dekodimin e raportit t\u00eb fillimit t\u00eb vitit 2016 nga Andrei Salnikov \"Gabimet tipike n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb PostgreSQL\"<\/strong><\/p>\n<p><\/p>\n<p>N\u00eb k\u00ebt\u00eb raport do t\u00eb shqyrtoj gabimet kryesore n\u00eb aplikacione q\u00eb ndodhin gjat\u00eb faz\u00ebs s\u00eb projektimit dhe kodimit t\u00eb aplikacionit. Do t\u00eb p\u00ebrqendrohem vet\u00ebm n\u00eb ato gabime q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb Postgresql. Si rregull, k\u00ebto jan\u00eb fillimi i fundit t\u00eb performanc\u00ebs s\u00eb sistemit tuaj n\u00eb t\u00ebr\u00ebsi, megjith\u00ebse n\u00eb fillim nuk kishte shenja p\u00ebr k\u00ebt\u00eb.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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>M\u00eb vjen mir\u00eb t'ju p\u00ebrsh\u00ebndes t\u00eb gjith\u00ebve! Ky raport nuk \u00ebsht\u00eb kaq teknik sa ai i m\u00ebparshmi nga kolegu im. Ky raport \u00ebsht\u00eb i orientuar kryesisht p\u00ebr zhvilluesit e sistemeve backend, sepse kemi nj\u00eb num\u00ebr t\u00eb konsideruesh\u00ebm klient\u00ebsh. T\u00eb gjith\u00eb ata b\u00ebjn\u00eb t\u00eb nj\u00ebjtat gabime. K\u00ebt\u00eb do t'ju tregoj. Do t'ju shpjegoj se n\u00eb \u00e7far\u00eb m\u00ebnyre k\u00ebto gabime \u00e7ojn\u00eb n\u00eb pasojat fatale dhe negative. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/c6c84dbafc595ae3068cd8bf804ccee7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pse ndodhin gabimet? Ato ndodhin p\u00ebr dy arsye: n\u00eb m\u00ebnyr\u00eb t\u00eb rast\u00ebsishme, ndoshta do t\u00eb kaloj\u00eb dhe p\u00ebr shkak t\u00eb munges\u00ebs s\u00eb njohurive p\u00ebr disa mekanizma q\u00eb ndodhin n\u00eb nivelin midis baz\u00ebs dhe aplikacionit, si dhe brenda vet\u00eb baz\u00ebs. <\/p>\n<p><\/p>\n<p>Do t'ju tregoj tre shembuj me imazhe t\u00eb tmerrshme se si u keq\u00ebsua gjith\u00e7ka. N\u00eb p\u00ebrmbledhje, do t\u00eb flas mbi mekanizmin q\u00eb ndodh atje. Dhe si t\u00eb ballafaqohemi me ta kur ndodhin, si dhe cilat metoda parandaluese mund t\u00eb p\u00ebrdoren p\u00ebr t\u00eb evituar gabimet. Do t\u00eb flas p\u00ebr mjete ndihm\u00ebse dhe do t\u00eb jap lidhje t\u00eb dobishme. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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\u00eb tabel\u00eb me faturat e klient\u00ebve, tjetra me operacionet e k\u00ebtyre faturave. Dhe me nj\u00eb far\u00eb periudhe her\u00eb pas here ne p\u00ebrdit\u00ebsojm\u00eb mbetjet n\u00eb k\u00ebto llogari.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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 p\u00ebr baz\u00ebn dhe ve\u00e7an\u00ebrisht p\u00ebr tabel\u00ebn \u00ebsht\u00eb gjithashtu shum\u00eb e mir\u00eb. Dhe nj\u00eb ngarkes\u00eb mjaft e mir\u00eb - 2,000 operacione n\u00eb sekond\u00eb p\u00ebr tabel\u00ebn.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/dea5a4b1ac7952f8cfed0e2e1cfc10dd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe n\u00ebp\u00ebrmjet k\u00ebtij raporti do t'ju tregoj grafik\u00eb, p\u00ebr t\u00eb qen\u00eb e qart\u00eb se \u00e7far\u00eb po ndodh. Do t\u00eb ket\u00eb gjithmon\u00eb 2 slajde me grafik\u00eb. Slajdi i par\u00eb \u2013 \u00ebsht\u00eb ajo q\u00eb 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 n\u00eb t\u00eb v\u00ebrtet\u00eb tabela jon\u00eb \u00ebsht\u00eb e vog\u00ebl. Indeksi \u00ebsht\u00eb i vog\u00ebl n\u00eb 2 MB. Kjo \u00ebsht\u00eb grafiku i par\u00eb nga e majta. <\/p>\n<p><\/p>\n<p>Koha mesatare e p\u00ebrgjigjes n\u00eb server \u00ebsht\u00eb gjithashtu stabile, e vog\u00ebl. Kjo \u00ebsht\u00eb grafiku i sip\u00ebrm t\u00eb djathtas. <\/p>\n<p><\/p>\n<p>Grafiku i posht\u00ebm t\u00eb majt\u00ebs \u2013 \u00ebsht\u00eb transaksionet m\u00eb t\u00eb gjata. Shohim se transaksionet p\u00ebrfundohen shpejt. Dhe autovacuum k\u00ebtu nuk po punon ende, sepse \u2013 ishte nj\u00eb test fillestar. M\u00eb pas do t\u00eb punoj\u00eb dhe do t\u00eb jet\u00eb e dobishme p\u00ebr ne.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/9e9b46acd9a878ff70ca4a7850adff09.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Slajdi i dyt\u00eb gjithmon\u00eb do t'i kushtohet tabel\u00ebs s\u00eb testuar. N\u00eb k\u00ebt\u00eb situat\u00eb ne vazhdimisht p\u00ebrdit\u00ebsojm\u00eb mbetjet e llogarive t\u00eb klient\u00ebve. 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 se burimet e procesorit (kjo \u00ebsht\u00eb grafiku i sip\u00ebrm t\u00eb djathtas) konsumohen gjithashtu nj\u00eblloj dhe mjaft e vog\u00ebl. <\/p>\n<p><\/p>\n<p>Grafiku i posht\u00ebm t\u00eb djathtas tregon sa memorie operante dhe e diskut po shqyrtojm\u00eb n\u00eb k\u00ebrkim t\u00eb rreshtit ton\u00eb t\u00eb nevojsh\u00ebm, para se ta p\u00ebrdit\u00ebsojm\u00eb. Dhe numri i operacioneve p\u00ebr tabel\u00ebn \u2013 2,000 n\u00eb sekond\u00eb, ashtu si\u00e7 e thash\u00eb m\u00eb par\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/84760d52716209b8dcfffb462c67a8e1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe tani ndodhi tragjedia. P\u00ebr nj\u00eb arsye t\u00eb caktuar ndodhi nj\u00eb transaksion i gjat\u00eb i harruar. Arsyet zakonisht jan\u00eb t\u00eb zakonshme: <\/p>\n<p><\/p>\n<ul>\n<li>Nj\u00eb nga m\u00eb t\u00eb zakonshmet \u2013 \u00ebsht\u00eb se n\u00eb kodin e aplikacionit filluam t\u00eb lidheshim me nj\u00eb sh\u00ebrbim t\u00eb jasht\u00ebm. Dhe ky sh\u00ebrbim nuk na p\u00ebrgjigjet. Do t\u00eb thot\u00eb, h\u00ebm\u00eb nj\u00eb transaksion, b\u00ebm\u00eb nj\u00eb ndryshim n\u00eb baz\u00eb dhe shkuam t\u00eb lexojm\u00eb post\u00ebn ose n\u00eb nj\u00eb sh\u00ebrbim tjet\u00ebr brenda infrastruktur\u00ebs ton\u00eb, dhe p\u00ebr nj\u00eb arsye nuk na p\u00ebrgjigjet. Dhe kemi nj\u00eb sesion t\u00eb ngjitur n\u00eb nj\u00eb gjendje \u2013 nuk dihet se kur do t\u00eb zgjidhet.<\/li>\n<li>Situata e dyt\u00eb \u00ebsht\u00eb kur n\u00eb kod ndodhi nj\u00eb p\u00ebrjashtim. Dhe ne nuk e p\u00ebrpunuam p\u00ebrjashtimin p\u00ebr mbylljen e transaksionit. Dhe kemi nj\u00eb sesion t\u00eb ngjitur me nj\u00eb transaksion t\u00eb hapur. <\/li>\n<li>Dhe e fundit \u2013 \u00ebsht\u00eb gjithashtu nj\u00eb rast mjaft i zakonsh\u00ebm. Kjo \u00ebsht\u00eb nj\u00eb kod i dob\u00ebt. Disa kuadro hapin nj\u00eb transaksion. Ai mbetet i ngjitur, dhe nuk mund ta dini n\u00eb aplikacion se ai \u00ebsht\u00eb i ngjitur. <\/li>\n<\/ul>\n<p><\/p>\n<p>\u00c7far\u00eb ndodhin k\u00ebto? <\/p>\n<p><\/p>\n<p>K\u00ebshtu q\u00eb fillojn\u00eb t\u00eb mbushen me shpejt\u00ebsi tabelat dhe indekset. Ky \u00ebsht\u00eb efekti i bloat. P\u00ebr baz\u00ebn, kjo do t\u00eb shfaqet n\u00eb at\u00eb se koha e p\u00ebrgjigjes s\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave do t\u00eb rritet ndjesh\u00ebm, dhe ngarkesa mbi serverin e baz\u00ebs do t\u00eb rritet. Si rezultat, aplikacioni do t\u00eb vuaj\u00eb. Sepse n\u00ebse n\u00eb kodin tuaj shpenzuat 10 milisekonda p\u00ebr nj\u00eb k\u00ebrkes\u00eb n\u00eb baz\u00eb, 10 milisekonda p\u00ebr logjik\u00ebn tuaj, at\u00ebher\u00eb funksioni juaj do ta p\u00ebrfundonte n\u00eb 20 milisekonda. Tani situata do t\u00eb jet\u00eb krejt\u00ebsisht e trishtuar. <\/p>\n<p><\/p>\n<p>Dhe le t\u00eb shohim se \u00e7far\u00eb po ndodh. Grafiku n\u00eb t\u00eb majt\u00eb posht\u00eb tregon se kemi nj\u00eb transaksion t\u00eb gjat\u00eb. Dhe n\u00ebse shohim grafikun n\u00eb t\u00eb majt\u00eb lart, shohim se madh\u00ebsia e tabel\u00ebs nga dy megabajt \u00ebsht\u00eb rritur papritur n\u00eb 300 megabajt. Nd\u00ebrkoh\u00eb, sasia e t\u00eb dh\u00ebnave n\u00eb tabel\u00eb nuk ka ndryshuar, dometh\u00ebn\u00eb ka nj\u00eb sasi t\u00eb madhe mbetje.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/b1947f6487e440a770a1251a955e62d2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Situata e p\u00ebrgjithshme p\u00ebr koh\u00ebn mesatare t\u00eb p\u00ebrgjigjes s\u00eb serverit gjithashtu ka ndryshuar disa renditje. Dometh\u00ebn\u00eb, t\u00eb gjitha k\u00ebrkesat n\u00eb server kan\u00eb filluar t\u00eb varen ndjesh\u00ebm. Dhe n\u00eb t\u00eb nj\u00ebjt\u00ebn koh\u00eb, proceset e brendshme t\u00eb Postgres jan\u00eb aktivizuar, duke tentuar t\u00eb b\u00ebjn\u00eb di\u00e7ka dhe duke konsumuar burime.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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. Koh\u00eb mesatare e p\u00ebrgjigjes p\u00ebr tabel\u00ebn ka rritur ndjesh\u00ebm. N\u00ebse flasim konkretisht p\u00ebr burimet e konsumuar, shohim se ngarkesa n\u00eb procesor \u00ebsht\u00eb rritur shum\u00eb. Ky \u00ebsht\u00eb grafiku n\u00eb t\u00eb djatht\u00eb lart. Ajo \u00ebsht\u00eb rritur sepse procesori duhet t\u00eb kaloj\u00eb p\u00ebrmes shum\u00eb rreshtave t\u00eb pabesuesh\u00ebm p\u00ebr t\u00eb gjetur nj\u00eb t\u00eb vetme t\u00eb nevojshme. Ky \u00ebsht\u00eb grafiku n\u00eb t\u00eb djatht\u00eb posht\u00eb. Si rezultat, numri i thirrjeve n\u00eb sekond\u00eb ka filluar t\u00eb ulet ndjesh\u00ebm, sepse baza nuk arrin t\u00eb p\u00ebrpunoj\u00eb t\u00eb nj\u00ebjtin num\u00ebr k\u00ebrkesash. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/424685ce852bc5f3ef77151f8d3d7289.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Na nevojitet t\u00eb rikthehemi n\u00eb jet\u00eb. Shk\u00ebmbejm\u00eb informacion n\u00eb internet dhe kuptojm\u00eb se transaksionet e gjata \u00e7ojn\u00eb n\u00eb probleme. Gjejm\u00eb dhe shuajm\u00eb k\u00ebt\u00eb transaksion. Dhe gjith\u00e7ka kthehet n\u00eb normalitet. T\u00eb gjitha funksionojn\u00eb si duhet. <\/p>\n<p><\/p>\n<p>Ne qet\u00ebsohemi, por pas pak kohe fillojm\u00eb t\u00eb v\u00ebm\u00eb re se aplikacioni nuk funksionon si para situat\u00ebs emergjente. K\u00ebrkesat po p\u00ebrpunohen gjithashtu m\u00eb ngadal\u00eb, madje shum\u00eb m\u00eb ngadal\u00eb. Nj\u00eb her\u00eb e gjysm\u00eb deri n\u00eb dy her\u00eb m\u00eb ngadal\u00eb n\u00eb rastin tim konkret. Ngarkesa n\u00eb server gjithashtu \u00ebsht\u00eb m\u00eb e lart\u00eb se ishte para kriz\u00ebs. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/ee3037719a31f8d18fc42745716c4da5.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe pyetja \u00ebsht\u00eb: \"\u00c7far\u00eb po ndodh me baz\u00ebn n\u00eb at\u00eb moment?\". Me baz\u00ebn ndodhin situata t\u00eb tilla. N\u00eb grafikun e transaksioneve shihni se ajo \u00ebsht\u00eb ndalur dhe aty nuk ka transaksione t\u00eb zgjatura. Por madh\u00ebsit\u00eb e tabel\u00ebs gjat\u00eb kriz\u00ebs jan\u00eb rritur kritikisht. Dhe q\u00eb nga at\u00ebher\u00eb nuk jan\u00eb zvog\u00ebluar. Koha mesatare p\u00ebr baz\u00ebn \u00ebsht\u00eb stabilizuar. Dhe p\u00ebrgjigjet duket se jan\u00eb t\u00eb arsyeshme me nj\u00eb shpejt\u00ebsi t\u00eb pranueshme p\u00ebr ne. Auto-vakuumi ka filluar t\u00eb jet\u00eb m\u00eb aktiv dhe t\u00eb b\u00ebj\u00eb di\u00e7ka me tabel\u00ebn, sepse i nevojitet t\u00eb procesoj\u00eb m\u00eb shum\u00eb t\u00eb dh\u00ebna. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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 provuar me llogarit\u00eb, ku ne ndryshojm\u00eb mbetjet: koha e p\u00ebrgjigjes p\u00ebr k\u00ebrkes\u00ebn duket se ka rikthyer normalitetin. Por n\u00eb t\u00eb v\u00ebrtet\u00eb ajo \u00ebsht\u00eb nj\u00eb her\u00eb e gjysm\u00eb m\u00eb e lart\u00eb.<\/p>\n<p><\/p>\n<p>Dhe p\u00ebrsa i p\u00ebrket ngarkes\u00ebs n\u00eb procesor, shohim se ajo nuk \u00ebsht\u00eb rikthyer n\u00eb nivelin e nevojsh\u00ebm para kriz\u00ebs. Dhe arsyet jan\u00eb pik\u00ebrisht n\u00eb grafikun n\u00eb t\u00eb djatht\u00eb posht\u00eb. Duke e par\u00eb, duket se ka nj\u00eb kalim t\u00eb nj\u00eb sasi t\u00eb caktuar t\u00eb memories. Dometh\u00ebn\u00eb, p\u00ebr t\u00eb gjetur rreshtin e nevojsh\u00ebm, po shpenzojm\u00eb burime t\u00eb serverit nga baza e t\u00eb dh\u00ebnave gjat\u00eb p\u00ebrzgjedhjes s\u00eb t\u00eb dh\u00ebnave t\u00eb panevojshme. Numri i transaksioneve n\u00eb sekond\u00eb \u00ebsht\u00eb stabilizuar. <\/p>\n<p><\/p>\n<p>N\u00eb p\u00ebrgjith\u00ebsi mir\u00eb, por situata \u00ebsht\u00eb m\u00eb e keqe se ishte. Ka nj\u00eb degradim t\u00eb duksh\u00ebm t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave si pasoj\u00eb e aplikacionit ton\u00eb, q\u00eb punon me k\u00ebt\u00eb baz\u00eb t\u00eb dh\u00ebnash. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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, n\u00ebse nuk ishit n\u00eb prezantimin e m\u00ebparsh\u00ebm, tani nj\u00eb pak teori. Teoria mbi procesin e brendsh\u00ebm. Pse \u00ebsht\u00eb auto-vakuumi dhe \u00e7far\u00eb b\u00ebn ai?<\/p>\n<p><\/p>\n<p>Shum\u00eb shkurtimisht p\u00ebr kuptimin. N\u00eb nj\u00eb moment, ne kemi nj\u00eb tabel\u00eb. N\u00eb tabel\u00eb kemi rreshta. K\u00ebta rreshta mund t\u00eb jen\u00eb aktiv\u00eb, t\u00eb gjall\u00eb, t\u00eb nevojsh\u00ebm p\u00ebr ne tani. N\u00eb imazh ata jan\u00eb t\u00eb markuar me ngjyr\u00eb t\u00eb gjelb\u00ebr. Dhe ka rreshta t\u00eb vdekur, q\u00eb jan\u00eb p\u00ebrfunduar, jan\u00eb p\u00ebrdit\u00ebsuar, kan\u00eb pasur regjistrime t\u00eb reja. Dhe ata sh\u00ebnohen q\u00eb nuk jan\u00eb m\u00eb t\u00eb interesuar p\u00ebr baz\u00ebn e t\u00eb dh\u00ebnave. Por ndodhin n\u00eb tabel\u00eb p\u00ebr shkak t\u00eb karakteristikave t\u00eb Postgres.<\/p>\n<p><\/p>\n<p>Pse nevojitet auto-vakuumi? N\u00eb nj\u00eb moment, ai vjen, i drejtohet baz\u00ebs s\u00eb t\u00eb dh\u00ebnave dhe pyet: \"M\u00eb jep, t\u00eb lutem, id-n\u00eb e transaksionit m\u00eb t\u00eb vjet\u00ebr, q\u00eb \u00ebsht\u00eb e hapur aktualisht n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave\". Baza e t\u00eb dh\u00ebnave kthen k\u00ebt\u00eb id. Dhe auto-vakuumi, duke u mb\u00ebshtetur n\u00eb t\u00eb, kalon p\u00ebrmes rreshtave n\u00eb tabel\u00eb. Dhe kur sheh se disa rreshta jan\u00eb ndryshuar nga transaksione shum\u00eb m\u00eb t\u00eb vjetra, ai ka t\u00eb drejt\u00eb t'i sh\u00ebnoj\u00eb ato si rreshta, t\u00eb cilat mund t'i rip\u00ebrdorim n\u00eb t\u00eb ardhmen duke regjistruar aty t\u00eb dh\u00ebna t\u00eb reja. Ky \u00ebsht\u00eb nj\u00eb proces i prapa.<\/p>\n<p><\/p>\n<p>N\u00eb k\u00ebt\u00eb koh\u00eb, ne vazhdojm\u00eb t\u00eb punojm\u00eb me baz\u00ebn e t\u00eb dh\u00ebnave, vazhdojm\u00eb t\u00eb b\u00ebjm\u00eb disa ndryshime n\u00eb tabel\u00eb. Dhe n\u00eb k\u00ebta rreshta, q\u00eb mund t'i rip\u00ebrdorim, regjistrojm\u00eb t\u00eb dh\u00ebna t\u00eb reja. Dhe k\u00ebshtu kemi nj\u00eb cik\u00ebl, dometh\u00ebn\u00eb, gjithmon\u00eb kan\u00eb ndodhur disa rreshta t\u00eb vdekur t\u00eb vjet\u00ebr, n\u00eb vend t\u00eb tyre regjistrojm\u00eb rreshta t\u00eb rinj t\u00eb nevojsh\u00ebm p\u00ebr ne. Dhe kjo \u00ebsht\u00eb nj\u00eb gjendje normale p\u00ebr pun\u00ebn e PostgreSQL.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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 kriz\u00ebs? Si ndodhi ky proces?<\/p>\n<p><\/p>\n<p>Ne kishim nj\u00eb tabel\u00eb n\u00eb nj\u00eb gjendje t\u00eb caktuar, disa rreshta ishin t\u00eb gjall\u00eb, disa t\u00eb vdekur. Erdh p\u00ebrheq\u00ebsi automatik. Ai e pyeti baz\u00ebn e t\u00eb dh\u00ebnave p\u00ebr transaksionin m\u00eb t\u00eb vjet\u00ebr q\u00eb kishim, cili ishte id e saj. Mori k\u00ebt\u00eb id, e cila mund t\u00eb ishte nga or\u00eb m\u00eb par\u00eb, ndoshta edhe dhjet\u00eb minuta. Kjo varet nga sa ngarkes\u00eb keni n\u00eb baz\u00ebn e t\u00eb dh\u00ebnave. Dhe shkoi p\u00ebr t\u00eb k\u00ebrkuar rreshtat q\u00eb mund t\u00eb sh\u00ebnonte si t\u00eb rip\u00ebrdorsh\u00ebm. Dhe nuk gjeti asnj\u00eb rresht t\u00eb till\u00eb n\u00eb tabel\u00ebn ton\u00eb. <\/p>\n<p><\/p>\n<p>Por ne vazhdojm\u00eb t\u00eb punojm\u00eb me tabel\u00ebn. B\u00ebjm\u00eb di\u00e7ka n\u00eb t\u00eb, p\u00ebrdit\u00ebsojm\u00eb, ndryshojm\u00eb t\u00eb dh\u00ebnat. Dhe \u00e7far\u00eb duhet t\u00eb b\u00ebj\u00eb baza e t\u00eb dh\u00ebnave n\u00eb k\u00ebt\u00eb koh\u00eb? Ajo nuk i mbetet asgj\u00eb tjet\u00ebr ve\u00e7se t\u00eb shkruaj\u00eb rreshta t\u00eb rinj n\u00eb fund t\u00eb tabel\u00ebs ekzistuese. K\u00ebshtu q\u00eb fillon t\u00eb rritet madh\u00ebsia e tabel\u00ebs. <\/p>\n<p><\/p>\n<p>Realistsh\u00ebm, p\u00ebr pun\u00ebn ton\u00eb na duhen rreshtat e gjelb\u00ebr. Por gjat\u00eb nj\u00eb problemi t\u00eb till\u00eb, ne p\u00ebrfundojm\u00eb me nj\u00eb p\u00ebrqindje shum\u00eb t\u00eb ul\u00ebt t\u00eb rreshtave t\u00eb gjelb\u00ebr n\u00eb gjith\u00eb v\u00ebllimin e tabel\u00ebs. <\/p>\n<p><\/p>\n<p>Dhe kur ne kryejm\u00eb nj\u00eb k\u00ebrkes\u00eb, baza e t\u00eb dh\u00ebnave detyrohet t\u00eb kaloj\u00eb n\u00ebp\u00ebr t\u00eb gjitha rreshtat: edhe t\u00eb kuqit, edhe t\u00eb gjelb\u00ebrit, p\u00ebr t\u00eb gjetur rreshtin e duhur. Efekti i rritjes s\u00eb tabel\u00ebs me t\u00eb dh\u00ebna t\u00eb padobishme quhet \"bloat\", i cili gjithashtu h\u00ebng\u00ebr hap\u00ebsir\u00ebn ton\u00eb n\u00eb disk. E mbani mend, ishim 2 MB, u b\u00eb 300 MB? Tani z\u00ebvend\u00ebsoni megabajt\u00ebt me gigabajt\u00ebt dhe ashtu do t\u00eb humbni shpejt t\u00eb gjitha burimet e diskut tuaj.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/fe7cb6b5c99610744ebcfe5ad7235d1e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Cilat mund t\u00eb jen\u00eb pasojat p\u00ebr ne? <\/p>\n<p><\/p>\n<ul>\n<li>N\u00eb shembullin tim, tabela dhe indeksi u rrit\u00ebn 150 her\u00eb. Disa nga klient\u00ebt tan\u00eb kan\u00eb pasur raste m\u00eb fatale, kur thjesht hap\u00ebsira n\u00eb disk filloi t\u00eb mbaronte. <\/li>\n<li>Madh\u00ebsia e tabelave nuk do t\u00eb zvog\u00eblohet ndonj\u00ebher\u00eb vet\u00eb. P\u00ebrheq\u00ebsi automatik n\u00eb disa raste mund t\u00eb pres\u00eb bishtin e tabel\u00ebs, n\u00ebse aty ka vet\u00ebm rreshta t\u00eb vdekur. Por pasi ndodhin rotacione t\u00eb vazhdueshme, nj\u00eb rresht i gjelb\u00ebr mund t\u00eb ngecet n\u00eb fund dhe t\u00eb mos p\u00ebrdit\u00ebsohet, nd\u00ebrsa t\u00eb tjer\u00ebt do t\u00eb shkruhen diku n\u00eb fillim t\u00eb tabel\u00ebs. Por kjo \u00ebsht\u00eb nj\u00eb ngjarje aq e pamundur, saq\u00eb tabela juaj t\u00eb zvog\u00eblohet natyrsh\u00ebm, prandaj nuk duhen pasur shpresa p\u00ebr k\u00ebt\u00eb. <\/li>\n<li>Baza e t\u00eb dh\u00ebnave duhet t\u00eb kaloj\u00eb n\u00ebp\u00ebr t\u00eb gjith\u00eb radh\u00ebt e padobishme. Dhe ne shpenzojm\u00eb burime diskun, shpenzojm\u00eb burime t\u00eb procesorit dhe energjin\u00eb elektrike. <\/li>\n<li>Dhe kjo ndikon drejtp\u00ebrdrejt n\u00eb aplikacionin ton\u00eb, sepse n\u00eb fillim shpenzonim 10 milisekonda p\u00ebr k\u00ebrkes\u00ebn, 10 milisekonda p\u00ebr kodin ton\u00eb, nd\u00ebrsa gjat\u00eb kriz\u00ebs filluam t\u00eb shpenzojm\u00eb nj\u00eb sekond\u00eb p\u00ebr k\u00ebrkes\u00ebn dhe 10 milisekonda p\u00ebr kodin, dmth. performanca e aplikacionit ra ndjesh\u00ebm. Dhe kur zvog\u00ebluam kriz\u00ebn, na duhej 20 milisekonda p\u00ebr k\u00ebrkes\u00ebn, 10 milisekonda p\u00ebr kodin. Kjo do t\u00eb thot\u00eb se ne fatkeq\u00ebsisht humb\u00ebm 1.5 her\u00eb p\u00ebr performanc\u00ebn. Dhe kjo \u00ebsht\u00eb p\u00ebr shkak t\u00eb nj\u00eb transaksioni q\u00eb ngeci, ndoshta, p\u00ebr fajin ton\u00eb. <\/li>\n<li>Dhe pyetja \u00ebsht\u00eb: \"Si ta kthejm\u00eb gjith\u00e7ka prapa?\" q\u00eb t\u00eb kemi gjith\u00e7ka mir\u00eb dhe k\u00ebrkesat t\u00eb funksionojn\u00eb po aq shpejt sa para kriz\u00ebs. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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 ka nj\u00eb cik\u00ebl t\u00eb caktuar pun\u00ebsh q\u00eb kryhen. <\/p>\n<p><\/p>\n<p>S\u00eb pari, na nevojitet t\u00eb gjejm\u00eb tabelat problematike q\u00eb jan\u00eb rritur. Ne kuptojm\u00eb se p\u00ebr disa tabela, shkrimi po ndodh m\u00eb aktivisht, p\u00ebr disa m\u00eb pak aktivisht. Dhe p\u00ebr k\u00ebt\u00eb p\u00ebrdoret zgjerimi <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/pgstattuple.html\">pgstattuple<\/a><\/noindex>. Duke instaluar k\u00ebt\u00eb zgjerim, mund t\u00eb shkruani k\u00ebrkesa q\u00eb ju ndihmojn\u00eb t\u00eb gjeni tabelat q\u00eb jan\u00eb rritur mjaft. <\/p>\n<p><\/p>\n<p>Pasi t\u00eb keni gjetur k\u00ebto tabela, ato duhet t\u00eb kompresohen. P\u00ebr k\u00ebt\u00eb ka tashm\u00eb mjete. N\u00eb kompanin\u00eb ton\u00eb ne p\u00ebrdorim tre mjete. E para - VACUUM FULL i integruar. Ai \u00ebsht\u00eb i ashp\u00ebr, i ul\u00ebt 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> \u2013 jan\u00eb mjete t\u00eb pal\u00ebve t\u00eb treta p\u00ebr kompresimin e tabelave. Dhe ato jan\u00eb m\u00eb t\u00eb kujdesshme 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 se \u00e7far\u00eb ju \u00ebsht\u00eb m\u00eb e p\u00ebrshtatshme. Por p\u00ebr k\u00ebt\u00eb do t\u00eb flas n\u00eb fund. E r\u00ebnd\u00ebsishme \u00ebsht\u00eb q\u00eb ka tre mjete. Ka p\u00ebr t\u00eb zgjedhur. <\/p>\n<p><\/p>\n<p>Pasi t\u00eb kemi rregulluar gjith\u00e7ka dhe siguruar q\u00eb gjith\u00e7ka \u00ebsht\u00eb mir\u00eb, 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>Parandalimi \u00ebsht\u00eb mjaft i thjesht\u00eb. Duhet t\u00eb ndjekim gjat\u00ebsi e seancave n\u00eb serverin Master. <strong>Ve\u00e7an\u00ebrisht seancat e rrezikshme q\u00eb jan\u00eb n\u00eb gjendje idle in transaction<\/strong>. Ato jan\u00eb ato q\u00eb hap\u00ebn nj\u00eb transaksion, b\u00ebn\u00eb di\u00e7ka dhe u larguan ose thjesht ngel\u00ebn, humb\u00ebn n\u00eb kod. <\/li>\n<li>Dhe p\u00ebr ju, si zhvillues, \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb testoni kodin n\u00eb momentin e shfaqjes s\u00eb k\u00ebtyre situatave. Nuk \u00ebsht\u00eb e v\u00ebshtir\u00eb t\u00eb b\u00ebhet. Kjo do t\u00eb ishte nj\u00eb kontroll i dobish\u00ebm. Do t'ju ndihmoj\u00eb t\u00eb evitoni shum\u00eb \"probleme f\u00ebmij\u00ebsh\" q\u00eb lidhen me transaksionet e gjata. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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'ju tregoj se si u ndryshua tabela dhe sjellja e baz\u00ebs s\u00eb t\u00eb dh\u00ebnave pas kryerjes s\u00eb VACUUM FULL n\u00eb tabel\u00eb. Kjo nuk \u00ebsht\u00eb n\u00eb prodhim.<\/p>\n<p><\/p>\n<p>Madh\u00ebsia e tabel\u00ebs u rikthye menj\u00ebher\u00eb n\u00eb gjendjen normale t\u00eb pun\u00ebs n\u00eb disa megabajt. Kjo nuk e nd impacton ndjesh\u00ebm koh\u00ebn mesatare t\u00eb p\u00ebrgjigjes nga serveri. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/cbb07ad24dba899395223d1ca25e438e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Por konkretisht p\u00ebr tabel\u00ebn ton\u00eb t\u00eb testimit, ku kemi azhurnuar mbetjet n\u00eb llogari, ne shohim se koha mesatare e p\u00ebrgjigjes p\u00ebr k\u00ebrkes\u00ebn e azhurnimit t\u00eb t\u00eb dh\u00ebnave n\u00eb tabel\u00eb ka r\u00ebn\u00eb n\u00eb nivelin para aksidentit. Burimet e konsumuar nga procesori p\u00ebr ekzekutimin e k\u00ebsaj k\u00ebrkese gjithashtu ran\u00eb n\u00eb nivelin para aksidentit. Dhe grafiku n\u00eb fund t\u00eb djatht\u00eb tregon se tani ne jemi duke gjetur pik\u00ebrisht rreshtin q\u00eb na nevojitet menj\u00ebher\u00eb, pa kaluar p\u00ebrmes nj\u00eb grumbulli rreshtash t\u00eb vdekur q\u00eb ishin para kompresimit t\u00eb tabel\u00ebs. Koha mesatare e k\u00ebrkesave mbeti n\u00eb nivelin e nj\u00ebjt\u00eb. Por k\u00ebtu kam, m\u00eb shum\u00eb, nj\u00eb gabim t\u00eb harduerit tim.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/c1d90c5442e02fce209dc40e048a8ca2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kjo \u00ebsht\u00eb historia e par\u00eb. Ajo \u00ebsht\u00eb m\u00eb e zakonshmja. Dhe ndodh me t\u00eb gjith\u00eb, pavar\u00ebsisht nga p\u00ebrvoja e klientit, sa t\u00eb kualifikuar jan\u00eb programuesit atje. Her\u00ebt a von\u00eb, kjo ndodh. <\/p>\n<p><\/p>\n<p>Historia e dyt\u00eb, n\u00eb t\u00eb cil\u00ebn ne shp\u00ebrndajm\u00eb ngarkes\u00ebn dhe optimizojm\u00eb burimet serverike.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/cda421c554d1329107ddc935045cc1b9.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Kemi kaluar tashm\u00eb dhe jemi b\u00ebr\u00eb djem serioz\u00eb. Dhe e kuptojm\u00eb se kemi nj\u00eb replik\u00eb dhe do t\u00eb ishte mir\u00eb t\u00eb balancojm\u00eb ngarkes\u00ebn: t\u00eb shkruajm\u00eb n\u00eb Master dhe t\u00eb lexojm\u00eb nga replika. Zakonisht, kjo situat\u00eb ndodh kur duam t\u00eb p\u00ebrgatisim ndonj\u00eb raport ose ETL. Dhe biznesi \u00ebsht\u00eb shum\u00eb i lumtur p\u00ebr k\u00ebt\u00eb. Ai d\u00ebshiron shum\u00eb raporte me nj\u00eb mori analitikash t\u00eb komplikuara. <\/li>\n<li>Raportet jan\u00eb shum\u00eb-or\u00ebshe, sepse analitika e komplikuar nuk mund t\u00eb llogaritet brenda disa milisekondash. Ne, si djem t\u00eb guximsh\u00ebm, shkruajm\u00eb kod. B\u00ebjm\u00eb n\u00eb aplikacionin e injektuar q\u00eb regjistrimi po b\u00ebhet n\u00eb Master, raportet ekzekutohen n\u00eb replik\u00eb. <\/li>\n<li>Ne shp\u00ebrndajm\u00eb ngarkes\u00ebn. <\/li>\n<li>Gjith\u00e7ka funksionon shk\u00eblqyesh\u00ebm. Ne jemi t\u00eb mrekulluesh\u00ebm. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/e94c8c492197a5015ac8aae79389a4c2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe si duket kjo situat\u00eb? Konkraktisht n\u00eb k\u00ebto grafika, un\u00eb kam shtuar koh\u00ebn e transaksioneve nga replika p\u00ebr koh\u00ebzgjatjen e transaksioneve. T\u00eb gjitha grafikat e tjera lidhen vet\u00ebm me serverin Master. <\/p>\n<p><\/p>\n<p>Tabela me raportet n\u00eb k\u00ebt\u00eb pik\u00eb ka rritur. Ata jan\u00eb b\u00ebr\u00eb m\u00eb shum\u00eb. Ne shohim se koha mesatare e p\u00ebrgjigjes s\u00eb serverit \u00ebsht\u00eb stabile. Ne shohim se n\u00eb replik\u00eb kemi nj\u00eb transaksion t\u00eb gjat\u00eb, i cili po punon p\u00ebr 2 or\u00eb. Shohim pun\u00ebn e qet\u00eb t\u00eb autovakuumit, i cili po p\u00ebrpunon rreshtat e vdekur. Dhe gjith\u00e7ka \u00ebsht\u00eb n\u00eb rregull. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/2da8acf098d12d6f146ab9b9dcb54b81.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Konkretisht p\u00ebr tabel\u00ebn q\u00eb po testojm\u00eb, ne po vazhdojm\u00eb t\u00eb azhurnojm\u00eb mbetjet n\u00eb llogari. Dhe gjithashtu kemi koh\u00eb t\u00eb q\u00ebndrueshme p\u00ebrgjigjeje p\u00ebr k\u00ebrkes\u00ebn, konsum t\u00eb q\u00ebndruesh\u00ebm burimesh. Gjith\u00e7ka \u00ebsht\u00eb n\u00eb rregull. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/3621ef723017bac6fb635caf0a5f4de6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Tjet\u00ebr \u00ebsht\u00eb se gjith\u00e7ka \u00ebsht\u00eb n\u00eb rregull deri n\u00eb momentin kur k\u00ebto raporte fillojn\u00eb t\u00eb p\u00ebrplasen p\u00ebr shkak t\u00eb konflikteve me replikimin. Dhe ato p\u00ebrplasen me nj\u00eb periudh\u00eb t\u00eb vazhdueshme. <\/p>\n<p><\/p>\n<p>Ne po shohim n\u00eb internet dhe po fillojm\u00eb t\u00eb lexojm\u00eb p\u00ebrse ndodh kjo. Dhe gjejm\u00eb nj\u00eb zgjidhje. <\/p>\n<p><\/p>\n<p>Zgjidhja e par\u00eb - t\u00eb rrisim vones\u00ebn e replikimit. Ne e dim\u00eb se raporti yn\u00eb punon p\u00ebr 3 or\u00eb. Vendosim vones\u00ebn e replikimit - 3 or\u00eb. Aktivizojm\u00eb gjith\u00e7ka, por akoma kemi probleme q\u00eb raportet nganj\u00ebher\u00eb p\u00ebrplasen. <\/p>\n<p><\/p>\n<p>Duam q\u00eb gjith\u00e7ka t\u00eb jet\u00eb perfekte. Po k\u00ebrkojm\u00eb m\u00eb tej. Dhe gjejm\u00eb nj\u00eb konfigurim fantastik n\u00eb internet - hot_standby_feedback. E aktivizojm\u00eb. Hot_standby_feedback na lejon t\u00eb mbajm\u00eb pun\u00ebn e autovakuumit n\u00eb Master. K\u00ebshtu ne i shmangim p\u00ebrfundimisht konfliktet e replikimit. Dhe gjith\u00e7ka punon mir\u00eb me raportet.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/ee3c315168bdd3f392d2e8a1c5861004.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>\u00c7far\u00eb ndodh me Master-serverin n\u00eb k\u00ebt\u00eb koh\u00eb? Po n\u00eb Master-serverin ndodhet nj\u00eb kriz\u00eb totale. Tani ne e shohim grafikun kur aktivizova t\u00eb dy k\u00ebto konfigurime. Dhe shohim q\u00eb sesioni n\u00eb replik\u00eb, dikur, ndikon n\u00eb situat\u00ebn n\u00eb Master-server. Ajo v\u00ebrtet ndikon, sepse e ka ndaluar autovakuumin, i cili pastron rreshtat e vdekur. Madh\u00ebsia e tabel\u00ebs s\u00ebrish \u00ebsht\u00eb rritur. Koha mesatare e ekzekutimit t\u00eb k\u00ebrkesave n\u00eb t\u00eb gjith\u00eb baz\u00ebn e t\u00eb dh\u00ebnave gjithashtu \u00ebsht\u00eb rritur. Autovakuimet jan\u00eb pak t\u00eb ngarkuara. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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, ne shohim se azhurnimi i t\u00eb dh\u00ebnave po rritet s\u00ebrish. Konsumi i burimeve t\u00eb procesorit gjithashtu \u00ebsht\u00eb rritur shum\u00eb. Ne po kalojm\u00eb s\u00ebrish nj\u00eb sasi t\u00eb madhe t\u00eb rreshtave t\u00eb vdekur e t\u00eb padobish\u00ebm. Dhe koha e p\u00ebrgjigjes p\u00ebr k\u00ebt\u00eb tabel\u00eb, numri i transaksioneve ka r\u00ebn\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/a648fd313a5dccf45f0056dc13c54263.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Si do t\u00eb duket kjo, n\u00ebse nuk e dim\u00eb p\u00ebr \u00e7far\u00eb kam folur m\u00eb par\u00eb?<\/p>\n<p><\/p>\n<ul>\n<li>Ne fillojm\u00eb t\u00eb k\u00ebrkojm\u00eb probleme. N\u00ebse kemi hasur t\u00eb k\u00ebqijat n\u00eb pjes\u00ebn e par\u00eb, ne e dim\u00eb se mund t\u00eb jet\u00eb arsyeja n\u00eb nj\u00eb transaksion t\u00eb gjat\u00eb dhe shkojm\u00eb te Master. Problemi ndodhet n\u00eb Master. Ai \u00ebsht\u00eb duke u p\u00ebrshpejtuar. Ai ngrohet, Load Average \u00ebsht\u00eb n\u00ebn nj\u00ebqind. <\/li>\n<li>K\u00ebrkesat ngadal\u00ebsohen atje, por nuk shohim ndonj\u00eb transaksion t\u00eb gjat\u00eb. Dhe nuk kuptojm\u00eb se \u00e7far\u00eb ndodh. Nuk e dim\u00eb se ku t\u00eb k\u00ebrkojm\u00eb. <\/li>\n<li>Kontrollojm\u00eb pajisjet serverike. Mund t\u00eb kemi d\u00ebmtuar raid-in. Mund t\u00eb jet\u00eb se na \u00ebsht\u00eb djegur nj\u00eb modul memorje. Mund t\u00eb ndodhin shum\u00eb gj\u00ebra. Por jo, server\u00ebt jan\u00eb t\u00eb rinj, gjith\u00e7ka funksionon shk\u00eblqyesh\u00ebm. <\/li>\n<li>T\u00eb gjith\u00eb po vrapojn\u00eb: administrator\u00ebt, zhvilluesit dhe drejtori. S'gjen ndihm\u00eb. <\/li>\n<li>Dhe n\u00eb nj\u00eb moment, gjith\u00e7ka papritur fillon t\u00eb rregullohet vet\u00eb. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/7d4f6417bc5cb3cec33679d843c6661d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00eb replik\u00eb, nj\u00ebher\u00ebsh, k\u00ebrkesa u p\u00ebrpunua dhe kaloi. Ne mor\u00ebm raportin. Biznesi \u00ebsht\u00eb akoma i k\u00ebnaqur. Si\u00e7 e shohim, tabela jon\u00eb \u00ebsht\u00eb rritur p\u00ebrs\u00ebri dhe nuk ka n\u00eb plan t\u00eb zvog\u00eblohet. N\u00eb grafikun e sesioneve kam l\u00ebn\u00eb nj\u00eb cop\u00eb nga kjo transaksion e gjat\u00eb nga replikimi, n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb mund t\u00eb vler\u00ebsoni sa koh\u00eb kalon derisa situata t\u00eb stabilizohet. <\/p>\n<p><\/p>\n<p>Sesioni u largua. Dhe vet\u00ebm pas nj\u00eb kohe, serveri fillon t\u00eb arrij\u00eb nj\u00eb far\u00eb rendi. Dhe koha mesatare e p\u00ebrgjigjeve p\u00ebr k\u00ebrkesat n\u00eb serverin Master kthehet n\u00eb norm\u00eb. Sepse, p\u00ebrfundimisht, avakuumi automatik mori mund\u00ebsin\u00eb t\u00eb pastronte dhe t\u00eb sh\u00ebnonte k\u00ebto rreshta t\u00eb vdekur. Dhe ai filloi t\u00eb b\u00ebj\u00eb pun\u00ebn e tij. Dhe sa m\u00eb shpejt ta b\u00ebj\u00eb, aq m\u00eb shpejt do t\u00eb arrijm\u00eb n\u00eb rend.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/547d195e08de1566da818150a47218eb.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00eb tabel\u00ebn e testuar, ku ne p\u00ebrdit\u00ebsojm\u00eb saldo, shohim t\u00eb nj\u00ebjt\u00ebn pamje. Koha mesatare e p\u00ebrdit\u00ebsimit gjithashtu normalizohet gradualisht. Burimet e konsumuar nga procesori gjithashtu pak\u00ebsohen. Dhe numri i transaksioneve p\u00ebr sekond\u00eb kthehet n\u00eb norm\u00eb. Por p\u00ebrs\u00ebri, norma nuk \u00ebsht\u00eb ajo q\u00eb kishim para katastrof\u00ebs. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/29372a5e9124690931485a7aea65befc.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>N\u00eb \u00e7do rast, po marrim nj\u00eb r\u00ebnie t\u00eb performances si n\u00eb rastin e par\u00eb, ndoshta 1.5-2 her\u00eb, ndonj\u00ebher\u00eb edhe m\u00eb shum\u00eb. <\/p>\n<p><\/p>\n<p>Duket se b\u00ebjm\u00eb gjith\u00e7ka si\u00e7 duhet. E shp\u00ebrndajm\u00eb ngarkes\u00ebn. Pajisjet nuk jan\u00eb t\u00eb pap\u00ebrdorura. Kemi ndar\u00eb k\u00ebrkesat si\u00e7 duhet, por prap\u00eb gj\u00ebrat nuk funksionojn\u00eb ashtu si\u00e7 duhet. <\/p>\n<p><\/p>\n<ul>\n<li>T\u00eb mos aktivizoni hot_standby_feedback? Po, nuk rekomandohet ta aktivizoni pa ndonj\u00eb arsye t\u00eb fort\u00eb. Sepse ky rregullim ndikon drejtp\u00ebrdrejt n\u00eb serverin Master dhe pezullon pun\u00ebn e avakumit automatik atje. N\u00ebse e aktivizoni n\u00eb ndonj\u00eb replik\u00eb dhe harroni p\u00ebr k\u00ebt\u00eb, mund t\u00eb vrisni serverin Master dhe t\u00eb keni probleme serioze me aplikacionin. <\/li>\n<li>T\u00eb rrisni max_standby_streaming_delay? Po, p\u00ebr raportet \u2013 kjo ashtu \u00ebsht\u00eb. N\u00ebse keni nj\u00eb raport tre or\u00ebsh dhe nuk doni q\u00eb t\u00eb d\u00ebshtoj\u00eb p\u00ebr shkak t\u00eb konflikteve t\u00eb replikimeve, thjesht rrisni vones\u00ebn. Nj\u00eb raport i gjat\u00eb kurr\u00eb nuk k\u00ebrkon t\u00eb dh\u00ebna q\u00eb sapo erdh\u00ebn n\u00eb baz\u00eb. N\u00ebse \u00ebsht\u00eb nj\u00eb raport tre or\u00ebsh, do t\u00eb thot\u00eb se e startoni p\u00ebr nj\u00eb periudh\u00eb t\u00eb vjet\u00ebr t\u00eb t\u00eb dh\u00ebnave. Dhe ju, \u00e7far\u00ebdo q\u00eb t\u00eb jet\u00eb vonesa tre or\u00ebshe ose gjasht\u00eb or\u00ebshe \u2013 nuk ka asnj\u00eb r\u00ebnd\u00ebsi, por k\u00ebshtu do t\u00eb merrni raportet me stabilitet dhe nuk do t\u00eb keni probleme me d\u00ebshtimin e tyre. <\/li>\n<li>Natyrisht, duhet t\u00eb kontrolloni sesionet e gjata n\u00eb replikat, ve\u00e7an\u00ebrisht n\u00ebse vendos\u00ebt t\u00eb aktivizoni hot_standby_feedback n\u00eb replik\u00eb. Sepse mund t\u00eb ndodhin shum\u00eb gj\u00ebra. I dham\u00eb at\u00eb replik\u00eb zhvilluesit q\u00eb t\u00eb testonte k\u00ebrkesat. Ai shkroi nj\u00eb k\u00ebrkes\u00eb t\u00eb \u00e7mendur. E nisi dhe u largua p\u00ebr t\u00eb pir\u00eb \u00e7aj, nd\u00ebrsa ne mor\u00ebm nj\u00eb Master t\u00eb mbingarkuar. Apo mund t\u00eb kemi lejuar nj\u00eb aplikacion t\u00eb gabuar atje. Situatat jan\u00eb t\u00eb ndryshme. Sesionet n\u00eb replikat duhet t\u00eb kontrollohen aq t\u00eb kujdessh\u00ebm sa dhe ato n\u00eb Master. <\/li>\n<li>Dhe n\u00ebse keni k\u00ebrkesa t\u00eb shpejta dhe t\u00eb gjata n\u00eb replikat, at\u00ebher\u00eb n\u00eb k\u00ebt\u00eb rast \u00ebsht\u00eb m\u00eb mir\u00eb t'i shp\u00ebrndani ato. Kjo \u00ebsht\u00eb lidhja p\u00ebr streaming_delay. P\u00ebr t\u00eb shpejtat t\u00eb keni nj\u00eb replik\u00eb me nj\u00eb vones\u00eb t\u00eb vog\u00ebl replikimi. P\u00ebr k\u00ebrkesat e gjata raportuese t\u00eb keni nj\u00eb replik\u00eb q\u00eb mund t\u00eb vonohet p\u00ebr 6 or\u00eb ose nj\u00eb dit\u00eb. Kjo \u00ebsht\u00eb nj\u00eb situat\u00eb krejt normale. <\/li>\n<\/ul>\n<p><\/p>\n<p>T\u00eb heqim pasojat gjithmon\u00eb me t\u00eb nj\u00ebjtin m\u00ebnyr\u00eb:<\/p>\n<p><\/p>\n<ul>\n<li>Gjejm\u00eb tabelat e fryra.<\/li>\n<li>Dhe i kompresojm\u00eb me mjetin m\u00eb t\u00eb p\u00ebrshtatsh\u00ebm q\u00eb kemi. <\/li>\n<\/ul>\n<p><\/p>\n<p>Historia e dyt\u00eb p\u00ebrfundoi k\u00ebtu. Kalohet te historia e tret\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/c8784e0be27883640cb8f6919a1c25e0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Po ashtu, mjaft e zakonshme p\u00ebr ne, n\u00eb t\u00eb cil\u00ebn b\u00ebjm\u00eb migrim. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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 p\u00ebr t\u00eb ndryshojn\u00eb. Ne gjithmon\u00eb duam t\u00eb zhvillohemi. Dhe ndonj\u00ebher\u00eb ndodh q\u00eb na nevojitet t\u00eb p\u00ebrdit\u00ebsojm\u00eb t\u00eb dh\u00ebnat n\u00eb tabel\u00eb, pra t\u00eb b\u00ebjm\u00eb nj\u00eb p\u00ebrdit\u00ebsim p\u00ebr migrimin ton\u00eb n\u00eb funksionalitetin e ri q\u00eb po implementojm\u00eb si pjes\u00eb e zhvillimit ton\u00eb. <\/li>\n<li>Formati i vjet\u00ebr i t\u00eb dh\u00ebnave nuk e p\u00ebrmbush. Supozoni se tani do t\u00eb drejtohemi te tabela e dyt\u00eb, ku kam operacionet p\u00ebr k\u00ebto llogari. Dhe, le t\u00eb themi, q\u00eb ato ishin n\u00eb rubla, dhe vendos\u00ebm t\u00eb rritim sakt\u00ebsin\u00eb dhe t\u00eb b\u00ebjm\u00eb n\u00eb cope. Dhe p\u00ebr k\u00ebt\u00eb na nevojitet t\u00eb b\u00ebjm\u00eb nj\u00eb p\u00ebrdit\u00ebsim: 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 automatizimi p\u00ebr kontrollin e versioneve t\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Supozoni, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.liquibase.org\/\">Liquibase<\/a><\/noindex>. E shkruajm\u00eb migrimin ton\u00eb atje. E testojm\u00eb n\u00eb baz\u00ebn ton\u00eb t\u00eb testimit. Gjith\u00e7ka shkon mir\u00eb. P\u00ebrdit\u00ebsimi kalon. Ndalesa e pun\u00ebs p\u00ebr nj\u00eb koh\u00eb t\u00eb caktuar, por p\u00ebrndryshe marrim t\u00eb dh\u00ebna t\u00eb p\u00ebrdit\u00ebsuara. Dhe mund t\u00eb fillojm\u00eb funksionalitetin e ri me k\u00ebt\u00eb. T\u00eb gjith\u00eb e kemi provuar, kontrolluar. T\u00eb gjith\u00eb e konfirmuan. <\/li>\n<li>Kemi kryer punime planifikuara, kemi b\u00ebr\u00eb migrimin. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/9e046583f7b1723fc1eaf61a03900918.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ja, migrimi me p\u00ebrdit\u00ebsimin \u00ebsht\u00eb para jush. Duke qen\u00eb se k\u00ebto jan\u00eb operacione mbi llogarit\u00eb, tabela kishte 15 GB. Dhe pasi q\u00eb ne po p\u00ebrdit\u00ebsojm\u00eb \u00e7do rresht, ne e rrit\u00ebm tabel\u00ebn me dy her\u00eb p\u00ebr shkak se ripar\u00ebsojm\u00eb \u00e7do rresht. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/06120c98ea43d0568045e71935c23592.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Gjat\u00eb migrimit, ne nuk mund t\u00eb b\u00ebnim asgj\u00eb me k\u00ebt\u00eb tabel\u00eb, sepse t\u00eb gjitha k\u00ebrkesat p\u00ebr t\u00eb ishin n\u00eb pritje dhe prisnin q\u00eb ky p\u00ebrdit\u00ebsim t\u00eb p\u00ebrfundonte. Por dua t\u00eb t\u00ebrheq v\u00ebmendjen tuaj ndaj shifrave q\u00eb jan\u00eb n\u00eb boshtin vertikal. Pra, mesatarja e koh\u00ebs s\u00eb k\u00ebrkes\u00ebs para migrimit ishte rreth 5 milisekonda dhe ngarkesa mbi procesor, numri i operacioneve blokues p\u00ebr leximin e memories t\u00eb diskut ishte m\u00eb pak se 7.5. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/69a3ba4d1d0cec7d39284e09e9d3055b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Kemi kryer migrimin dhe p\u00ebrs\u00ebri mor\u00ebm probleme. <\/p>\n<p><\/p>\n<p>Migrimi kaloi me sukses, por:<\/p>\n<p><\/p>\n<ul>\n<li>Funksionaliteti i vjet\u00ebr filloi t\u00eb realizohet m\u00eb ngadal\u00eb. <\/li>\n<li>Tabela p\u00ebrs\u00ebri u rrit n\u00eb madh\u00ebsi. <\/li>\n<li>Ngarkesa n\u00eb server p\u00ebrs\u00ebri u rrit m\u00eb shum\u00eb se \u00e7'ishte. <\/li>\n<li>Dhe, sigurisht, ne ende merremi me funksionalitetin q\u00eb punonte mir\u00eb, e kemi p\u00ebrmir\u00ebsuar pak. <\/li>\n<\/ul>\n<p><\/p>\n<p>Dhe kjo p\u00ebrs\u00ebri \u00ebsht\u00eb bloat, q\u00eb na shqet\u00ebson p\u00ebrs\u00ebri. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/becd7cce3a1c8821c81c96257f41202b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>K\u00ebtu po demonstroj q\u00eb tabela, ashtu si n\u00eb dy rastet e m\u00ebparshme, nuk po planifikon t\u00eb kthehet n\u00eb madh\u00ebsit\u00eb e m\u00ebparshme. Ngarkesa mesatare mbi server duket e arsyeshme. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/4d703a60d43a3460bc3782d49865b690.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe n\u00ebse i drejtohemi tabel\u00ebs me llogarit\u00eb, do t\u00eb shohim se mesatarja e koh\u00ebs s\u00eb k\u00ebrkes\u00ebs p\u00ebr k\u00ebt\u00eb tabel\u00eb ka dyfishuar. Ngarkesa mbi procesor dhe numri i rreshtave t\u00eb lexuar n\u00eb memory ka shkuar mbi 7.5, kur ishte n\u00ebn k\u00ebt\u00eb nivel. Dhe ngarkesa p\u00ebr procesor\u00ebt \u00ebsht\u00eb dyfishuar, nd\u00ebrsa p\u00ebr operacionet blokues \u00ebsht\u00eb rritur 1.5 her\u00eb, pra kemi marr\u00eb degradim t\u00eb performanc\u00ebs s\u00eb serverit. Dhe si pasoj\u00eb \u2013 degradim t\u00eb performanc\u00ebs s\u00eb aplikacionit ton\u00eb. Nd\u00ebrkoh\u00eb, numri i thirrjeve ka mbetur n\u00eb nivele t\u00eb ngjashme. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" src=\"\/wp-content\/uploads\/2020\/05\/80772afa0643b29f5314a2482c9dfe0d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Dhe k\u00ebtu \u00ebsht\u00eb e r\u00ebnd\u00ebsishme t\u00eb kuptohet se si t\u00eb b\u00ebhen k\u00ebto migrime si\u00e7 duhet. Dhe ato jan\u00eb t\u00eb nevojshme. Ne shpesh b\u00ebjm\u00eb k\u00ebto migrime.<\/p>\n<p><\/p>\n<ul>\n<li>Migrime t\u00eb tilla t\u00eb m\u00ebdha nuk b\u00ebhen automatikisht. Ato gjithmon\u00eb duhet t\u00eb jen\u00eb n\u00ebn kontroll. <\/li>\n<li>Kontrolli \u00ebsht\u00eb i nevojsh\u00ebm nga nj\u00eb person i njohur. N\u00ebse keni nj\u00eb DBA n\u00eb ekipin tuaj, le t'ia besoni DBA-s\u00eb k\u00ebt\u00eb. Kjo \u00ebsht\u00eb puna e tij. N\u00ebse jo, personi m\u00eb i p\u00ebrvoj\u00eb le t\u00eb b\u00ebj\u00eb k\u00ebt\u00eb, ai q\u00eb di si t\u00eb punoj\u00eb me bazat e t\u00eb dh\u00ebnave. <\/li>\n<li>Skema e re e databaz\u00ebs, edhe n\u00ebse ne p\u00ebrdit\u00ebsojm\u00eb vet\u00ebm nj\u00eb kolon\u00eb, gjithmon\u00eb e p\u00ebrgatitim me hapa, q\u00eb do t\u00eb thot\u00eb para se t\u00eb l\u00ebshojm\u00eb versionin e ri t\u00eb aplikacionit:<\/li>\n<li>Shtohet fusha t\u00eb reja, n\u00eb t\u00eb cilat do t\u00eb shkruhen t\u00eb dh\u00ebnat e p\u00ebrdit\u00ebsuara. <\/li>\n<li>Shkarkojm\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 gjithmon\u00eb kontrollojm\u00eb procesin e k\u00ebtij transferimi. Ne e dim\u00eb se sa shum\u00eb batch-e kemi b\u00ebr\u00eb dhe sa na kan\u00eb mbetur. <\/li>\n<li>Dhe efekti pozitiv i dyt\u00eb \u00ebsht\u00eb se midis \u00e7do batch-i t\u00eb till\u00eb, mbyllim transaksionin, hapim nj\u00eb t\u00eb re dhe kjo lejon avto-vakumin t\u00eb funksionoj\u00eb mbi tabel\u00eb, duke sh\u00ebnuar rreshtat e vdekur p\u00ebr ri-shfryt\u00ebzim. <\/li>\n<li>P\u00ebr rreshtat q\u00eb do t\u00eb shfaqen gjat\u00eb funksionimit t\u00eb aplikacionit (ne kemi akoma aplikacionin e vjet\u00ebr n\u00eb pun\u00eb) shtojm\u00eb nj\u00eb trigger q\u00eb shkruan vlerat e reja n\u00eb fushat e reja. N\u00eb rastin ton\u00eb \u2013 \u00ebsht\u00eb nj\u00eb shum\u00ebzim me nj\u00ebqind t\u00eb vler\u00ebs s\u00eb vjet\u00ebr. <\/li>\n<li>N\u00ebse jemi shum\u00eb t\u00eb vendosur 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 para l\u00ebshimit t\u00eb versionit t\u00eb ri t\u00eb aplikacionit, ne thjesht ribem\u00eb emrat e fushave. T\u00eb vjetrat n\u00eb nj\u00eb em\u00ebr t\u00eb sajuar, dhe fushat e reja ribem\u00eb emrat n\u00eb t\u00eb vjetrat. <\/li>\n<li>Dhe vet\u00ebm pasi ta b\u00ebjm\u00eb k\u00ebt\u00eb, e lancojm\u00eb versionin e ri t\u00eb aplikacionit. <\/li>\n<\/ul>\n<p><\/p>\n<p>Dhe n\u00eb k\u00ebt\u00eb m\u00ebnyr\u00eb ne nuk do t\u00eb kemi bloat dhe nuk do t\u00eb kemi degradim t\u00eb performanc\u00ebs. <\/p>\n<p><\/p>\n<p>Kjo p\u00ebrfundoi historin\u00eb e tret\u00eb. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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>Tani pak m\u00eb n\u00eb detaje p\u00ebr mjetet q\u00eb p\u00ebrmenda n\u00eb historin\u00eb time t\u00eb par\u00eb. <\/p>\n<p><\/p>\n<p>Para se t\u00eb k\u00ebrkoni bloat, \u00ebsht\u00eb e domosdoshme t\u00eb instaloni zgjerimin <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/pgstattuple.html\">pgstattuple<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>P\u00ebr t\u00eb mos zbuluar k\u00ebrkesa, ne n\u00eb pun\u00ebn ton\u00eb i kemi shkruar k\u00ebto k\u00ebrkesa. Ju mund t\u2019i p\u00ebrdorni ato. K\u00ebtu jan\u00eb paraqitur dy k\u00ebrkesa. <\/p>\n<p><\/p>\n<ul>\n<li>E para punon p\u00ebr nj\u00eb koh\u00eb t\u00eb gjat\u00eb, por gjithsesi ajo do t'ju tregoj\u00eb 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\u00ebsosh shpejt \u2013 a ka bloat apo jo p\u00ebr tabel\u00ebn. Dhe gjithashtu duhet t\u00eb kuptoni se bloat n\u00eb nj\u00eb tabel\u00eb PostgreSQL \u00ebsht\u00eb gjithmon\u00eb e pranishme. Kjo \u00ebsht\u00eb karakteristika 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 duhet t\u00eb shqet\u00ebsoheni dhe t\u00eb kompresoni k\u00ebt\u00eb tabel\u00eb. <\/li>\n<\/ul>\n<p><\/p>\n<p>Si t\u00eb identifikojm\u00eb tabelat q\u00eb jan\u00eb zgjeruar, ne e shqyrtuam, sidomos kur ato jan\u00eb zgjeruar me t\u00eb dh\u00ebna t\u00eb padobishme. <\/p>\n<p><\/p>\n<p>Tani p\u00ebr at\u00eb se si t\u00eb korrigjojm\u00eb 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 n\u00eb tabel\u00eb deri n\u00eb nj\u00eb gigabajt \u00ebsht\u00eb plot\u00ebsisht e mundur t\u00eb p\u00ebrdorim VACUUM FULL. Ai do t\u00eb marr\u00eb nj\u00eb bllokim ekskluziv p\u00ebr tabel\u00ebn p\u00ebr disa sekonda dhe mjaftuesh\u00ebm, p\u00ebrve\u00e7 k\u00ebsaj, ai b\u00ebn gjith\u00e7ka shpejt dhe me forc\u00eb. \u00c7far\u00eb b\u00ebn VACUUM FULL? Ai merr nj\u00eb bllokim ekskluziv p\u00ebr tabel\u00ebn dhe nga tabelat e vjetra shkruan rreshtat aktiv\u00eb n\u00eb nj\u00eb tabel\u00eb t\u00eb re. Dhe n\u00eb fund i nd\u00ebrron vendet. Fajlet e vjetra i fshin, dhe z\u00ebvend\u00ebsimet e reja i vendos n\u00eb vend t\u00eb k\u00ebtyre. Por gjat\u00eb pun\u00ebs s\u00eb tij ai merr nj\u00eb bllokim ekskluziv p\u00ebr tabel\u00ebn. Kjo do t\u00eb thot\u00eb q\u00eb ju nuk mund t\u00eb b\u00ebni asgj\u00eb me k\u00ebt\u00eb tabel\u00eb: as t\u00eb shkruani n\u00eb t\u00eb, as t\u00eb lexoni nga ajo, 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>. Nga parimi i tij \u00ebsht\u00eb shum\u00eb i ngjash\u00ebm me VACUUM FULL, sepse gjithashtu shkruan t\u00eb dh\u00ebnat nga skedar\u00ebt e vjet\u00ebr n\u00eb t\u00eb rinjt\u00eb dhe i nd\u00ebrron ato n\u00eb tabel\u00eb. Por n\u00eb k\u00ebt\u00eb rast, ai nuk merr nj\u00eb bllokim ekskluziv p\u00ebr tabel\u00ebn n\u00eb fillim, por e merr vet\u00ebm n\u00eb momentin kur ka t\u00eb dh\u00ebna t\u00eb gatshme p\u00ebr t\u00eb z\u00ebvend\u00ebsuar skedar\u00ebt. K\u00ebrkesat p\u00ebr burimet e diskut jan\u00eb t\u00eb ngjashme me ato t\u00eb VACUUM FULL. Ju nevojitet hap\u00ebsir\u00eb e shtuar n\u00eb disk, dhe kjo ndonj\u00ebher\u00eb \u00ebsht\u00eb kritike n\u00ebse keni tabela terabajt\u00ebshe. Ai gjithashtu \u00ebsht\u00eb mjaft k\u00ebrkues n\u00eb procesor, sepse kryen pun\u00eb aktive me hyrje-dalje. <\/li>\n<li>Mjeti i tret\u00eb \u00ebsht\u00eb <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pgcompacttable\">pgcompacttable<\/a><\/noindex>. Ai \u00ebsht\u00eb m\u00eb i kujdessh\u00ebm ndaj burimeve, sepse punon pak ndryshe. Thelbi i pgcompacttable \u00ebsht\u00eb se me azhurnimet n\u00eb tabel\u00eb, ai i zhvendos t\u00eb gjith\u00eb rreshtat aktiv\u00eb n\u00eb fillim t\u00eb tabel\u00ebs. Dhe m\u00eb pas ekzekuton nj\u00eb vakuum mbi k\u00ebt\u00eb tabel\u00eb, sepse ne e dim\u00eb se n\u00eb fillim kemi t\u00eb gjall\u00eb, dhe n\u00eb fund t\u00eb vdekur. Dhe vakuumi vet\u00eb ndalon k\u00ebt\u00eb bisht, pra nuk k\u00ebrkon shum\u00eb hap\u00ebsir\u00eb t\u00eb shtuar n\u00eb disk. Dhe gjithashtu mund t\u00eb optimizohet p\u00ebr burimet. <\/li>\n<\/ul>\n<p><\/p>\n<p>K\u00ebtu p\u00ebrfundon diskutimi p\u00ebr mjetet. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Gabime t\u00eb zakonshme n\u00eb aplikacione q\u00eb \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql. Andrey Sal&#039;nikov\" 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-i ju intereson dhe d\u00ebshironi t\u00eb hulumtoni m\u00eb tej, 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 \u00ebsht\u00eb nj\u00eb referat i kolegut tim. Ai \u00ebsht\u00eb i p\u00ebrgjithsh\u00ebm mbi at\u00eb se ku shkon hap\u00ebsira n\u00eb Postgres gjat\u00eb pun\u00ebs dhe jet\u00ebs s\u00eb tij. Dhe ka nj\u00eb pjes\u00eb t\u00eb madhe dhe shum\u00eb t\u00eb detajuar teknike p\u00ebr administrator\u00ebt e bazave t\u00eb dh\u00ebnash n\u00eb lidhje me bloat-in. <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pg-utils\">https:\/\/github.com\/dataegret\/pg-utils<\/a><\/noindex> \u2013 \u00ebsht\u00eb nj\u00eb lidhje n\u00eb repozitorin ton\u00eb, ku ruajm\u00eb shum\u00eb skripte t\u00eb dobishme p\u00ebr kontrollet e gjendjes s\u00eb baz\u00ebs s\u00eb t\u00eb dh\u00ebnave. Aty mund t\u00eb gjeni skripte p\u00ebr t\u00eb k\u00ebrkuar bloat. <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/reorg\/pg_repack\">E treta<\/a><\/noindex> dhe <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pgcompacttable\">kat\u00ebr<\/a><\/noindex> lidhje p\u00ebr mjetet q\u00eb do t'ju ndihmojn\u00eb t\u00eb optimizoni 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 \u00ebsht\u00eb nj\u00eb postim i kolegut tim. Aty ai analizon n\u00eb m\u00ebnyr\u00eb t\u00eb detajuar bloat-in n\u00eb nj\u00eb nivel m\u00eb t\u00eb af\u00ebrt me administrator\u00ebt. <\/li>\n<\/ul>\n<p><\/p>\n<p>K\u00ebt\u00eb her\u00eb kam p\u00ebrpiquar t\u00eb paraqes nj\u00eb paralajm\u00ebrim p\u00ebr zhvilluesit, sepse ata jan\u00eb klient\u00ebt tan\u00eb t\u00eb drejtp\u00ebrdrejt\u00eb t\u00eb bazave t\u00eb t\u00eb dh\u00ebnave dhe duhet t\u00eb kuptojn\u00eb se cilat veprime sjellin pasojat. Shpresoj t\u00eb kem arritur ta b\u00ebj k\u00ebt\u00eb. Faleminderit p\u00ebr v\u00ebmendjen!<\/p>\n<p><\/p>\n<p>Pyetje<\/p>\n<p><\/p>\n<p><em>Faleminderit p\u00ebr referatin! Ju fol\u00ebt p\u00ebr m\u00ebnyrat se si mund t\u00eb identifikohen problemet. Si mund t\u2019i parandalojm\u00eb ato? Pra, un\u00eb kam pasur nj\u00eb situat\u00eb kur k\u00ebrkesat ishin t\u00eb ngjitura jo vet\u00ebm p\u00ebr shkak se ato lidhen me disa sh\u00ebrbime t\u00eb jashtme. Ishin disa lidhje t\u00eb \u00e7uditshme. Kishin disa k\u00ebrkesa t\u00eb vogla, t\u00eb pad\u00ebmshme, q\u00eb kishin ngecur p\u00ebr nj\u00eb dit\u00eb, e m\u00eb pas fillonin t\u00eb b\u00ebnte \u00e7menduri. Pra, duket shum\u00eb si ajo q\u00eb po p\u00ebrshkruani. Si mund ta monitoroj k\u00ebt\u00eb? T\u00eb rri e t\u00eb shikoj vazhdimisht se cila k\u00ebrkes\u00eb \u00ebsht\u00eb ngjitur? Si mund ta parandaloj k\u00ebt\u00eb?<\/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, nuk \u00ebsht\u00eb domosdoshm\u00ebrisht p\u00ebr DBA.<\/p>\n<p><\/p>\n<p><em>Un\u00eb jam administrator.<\/em><\/p>\n<p><\/p>\n<p>N\u00eb PostgreSQL ekziston nj\u00eb pamje e till\u00eb, si pg_stat_activity, ku shfaqen k\u00ebrkesat q\u00eb jan\u00eb t\u00eb ngjitura. Dhe ju mund t\u00eb shihni se sa gjat\u00eb ka ngecur.<\/p>\n<p><\/p>\n<p><em>A duhet t\u00eb hyj \u00e7do 5 minuta dhe t\u00eb shikoj?<\/em><\/p>\n<p><\/p>\n<p>Konfiguroni cron dhe kontrolloni. N\u00ebse keni nj\u00eb k\u00ebrkes\u00eb t\u00eb gjat\u00eb, d\u00ebrgoni nj\u00eb email dhe mjafton. Do t\u00eb thot\u00eb, nuk \u00ebsht\u00eb e nevojshme t\u00eb shikoni me sy, kjo mund t\u00eb automatizohet. Do t'ju vij\u00eb nj\u00eb email dhe ju reagoni p\u00ebr t\u00eb. Ose mund ta ndalini automatikisht.<\/p>\n<p><\/p>\n<p><em>A ka arsye t\u00eb dukshme p\u00ebrse ndodhin k\u00ebto?<\/em><\/p>\n<p><\/p>\n<p>Un\u00eb disa i kam p\u00ebrmendur. T\u00eb tjerat jan\u00eb shembuj m\u00eb t\u00eb komplikuar. Dhe aty do t\u00eb ishte nj\u00eb bised\u00eb e gjat\u00eb.<\/p>\n<p><\/p>\n<p><em>Faleminderit p\u00ebr referatin! Desha t\u00eb pyes p\u00ebr mjetin pg_repack. N\u00ebse ai nuk b\u00ebn bllokim ekskluziv, at\u00ebher\u00eb...<\/em><\/p>\n<p><\/p>\n<p>Ai b\u00ebn bllokim ekskluziv. <\/p>\n<p><\/p>\n<p>\u2026 <em>at\u00ebher\u00eb un\u00eb potencialisht mund t\u00eb humbas t\u00eb dh\u00ebna. A duhet aplikacioni im t\u00eb mos shkruaj\u00eb n\u00eb k\u00ebt\u00eb koh\u00eb?<\/em><\/p>\n<p><\/p>\n<p>Jo, ai punon normalisht me tabel\u00ebn, pra pg_repack s\u00eb pari transferon t\u00eb gjitha rreshtat aktiv\u00eb q\u00eb ka. Natyrisht, disa shkrime ndodhin n\u00eb tabel\u00eb. Ai thjesht e shton at\u00eb bishtin. <\/p>\n<p><\/p>\n<p><em>Pra, n\u00eb fund ai e b\u00ebn at\u00eb?<\/em><\/p>\n<p><\/p>\n<p>N\u00eb fund, ai merr nj\u00eb bllokim ekskluziv p\u00ebr t\u00eb nd\u00ebrruar vendet e k\u00ebtyre skedar\u00ebve. <\/p>\n<p><\/p>\n<p><em>A do t\u00eb jet\u00eb m\u00eb shpejt se VACUUM FULL?<\/em><\/p>\n<p><\/p>\n<p>VACUUM FULL, si fillon, menj\u00ebher\u00eb merr bllokimin ekskluziv. Dhe derisa t\u00eb mos b\u00ebj\u00eb gjith\u00e7ka, ai nuk do ta l\u00ebr\u00eb at\u00eb. Nd\u00ebrsa pg_repack merr bllokimin ekskluziv vet\u00ebm n\u00eb momentin e z\u00ebvend\u00ebsimit t\u00eb skedar\u00ebve. N\u00eb at\u00eb moment nuk mund t\u00eb shkruani, por t\u00eb dh\u00ebnat nuk do t\u00eb humbasin, \u00e7do gj\u00eb do t\u00eb jet\u00eb n\u00eb rregull. <\/p>\n<p><\/p>\n<p><em>P\u00ebrsh\u00ebndetje! Keni folur rreth pun\u00ebs s\u00eb avtokompresionit. Kishte nj\u00eb grafik me kuti t\u00eb kuqe, t\u00eb verdha dhe t\u00eb gjelbra. Dometh\u00ebn\u00eb, t\u00eb verdhat - ai i sh\u00ebnon si t\u00eb fshir\u00eb. Dhe si rrjedhoj\u00eb, n\u00eb to mund t\u00eb shkruani di\u00e7ka t\u00eb re?<\/em><\/p>\n<p><\/p>\n<p>Po. Postgres nuk i fshin rreshtat. Ai ka nj\u00eb specifik\u00eb t\u00eb till\u00eb. N\u00ebse ne p\u00ebrdit\u00ebsojm\u00eb nj\u00eb rresht, e sh\u00ebnojm\u00eb at\u00eb t\u00eb vjet\u00ebr si t\u00eb fshir\u00eb. Atje vendoset ID e transaksionit q\u00eb e ndryshoi k\u00ebt\u00eb rresht dhe shkruajm\u00eb nj\u00eb rresht t\u00eb ri. Dhe kemi seanca q\u00eb potencialisht mund t'i lexojn\u00eb ato. N\u00eb nj\u00eb moment ata b\u00ebhen krejt\u00ebsisht t\u00eb vjet\u00ebruar. Dhe esenca e funksionimit t\u00eb avtokompresionit \u00ebsht\u00eb q\u00eb ai kalon p\u00ebrmes k\u00ebtyre rreshtave dhe i sh\u00ebnon ato si t\u00eb panevojshme. Dhe aty mund t\u00eb rip\u00ebrshtaten t\u00eb dh\u00ebnat. <\/p>\n<p><\/p>\n<p><em>E kuptova. Por pyetja \u00ebsht\u00eb disi ndryshe. Nuk e p\u00ebrfundova. Supozoni 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, at\u00ebher\u00eb mund t\u00eb mos hyj\u00eb thjesht n\u00eb nj\u00eb qelb t\u00eb vjet\u00ebr.<\/em> <\/p>\n<p><\/p>\n<p>Jo, aty gjithsesi t\u00eb gjith\u00eb rreshti p\u00ebrdit\u00ebsohet. N\u00eb Postgres ka dy modele t\u00eb ruajtjes s\u00eb t\u00eb dh\u00ebnave. Ai zgjidh sipas llojit t\u00eb t\u00eb dh\u00ebnave. Ka t\u00eb dh\u00ebna q\u00eb ruhen drejtp\u00ebrdrejt n\u00eb tabel\u00eb, dhe gjithashtu ka t\u00eb dh\u00ebna tos. K\u00ebto jan\u00eb sasi t\u00eb m\u00ebdha t\u00eb dh\u00ebnash: tekst, json. Ato ruhen n\u00eb tabela t\u00eb ve\u00e7anta. Dhe p\u00ebr k\u00ebto tabela ndodh e nj\u00ebjta histori me bloat, dmth gjith\u00e7ka \u00ebsht\u00eb e nj\u00ebjt\u00eb. Thjesht jan\u00eb t\u00eb ndara. <\/p>\n<p><\/p>\n<p><em>Faleminderit p\u00ebr raportin! Sa e pranueshme \u00ebsht\u00eb t\u00eb p\u00ebrdorim k\u00ebrkesa statement timeout p\u00ebr t\u00eb kufizuar koh\u00ebzgjatjen?<\/em><\/p>\n<p><\/p>\n<p>Shum\u00eb e pranueshme. Ne e p\u00ebrdorim kudo. Dhe duke qen\u00eb se n\u00ebna ton\u00eb nuk kemi sh\u00ebrbime, ofrojm\u00eb mb\u00ebshtetje t\u00eb larg\u00ebt, ka klient\u00eb mjaft t\u00eb ndrysh\u00ebm. Dhe t\u00eb gjith\u00eb jan\u00eb mjaft t\u00eb k\u00ebnaqur me k\u00ebt\u00eb. Dometh\u00ebn\u00eb, kemi detyra n\u00eb cron q\u00eb kontrollojn\u00eb. Thjesht diskutohet me klientin koha e seancave, p\u00ebrpara s\u00eb cil\u00ebs ne nuk nd\u00ebrprem\u00eb. Kjo mund t\u00eb jet\u00eb nj\u00eb minut\u00eb, mund t\u00eb jet\u00eb 10 minuta. Kjo varet nga ngarkesa n\u00eb baz\u00eb dhe objektivi i saj. Por p\u00ebr t\u00eb gjith\u00eb ne p\u00ebrdorim pg_stat_activity.<\/p>\n<p><\/p>\n<p><em>Faleminderit p\u00ebr raportin! Po p\u00ebrpiqem ta p\u00ebrshtas raportin tuaj n\u00eb aplikacionet e mia. Dhe duket se ne gjithandej fillojm\u00eb transaksionin, \u00e7do her\u00eb e p\u00ebrfundojm\u00eb at\u00eb n\u00eb m\u00ebnyr\u00eb t\u00eb qart\u00eb. N\u00ebse ndonj\u00eb p\u00ebrjashtim ndodh, megjithat\u00eb ndodh rollback. Dhe k\u00ebtu e mendova. A mund t\u00eb filloj\u00eb nj\u00eb transaksion n\u00eb m\u00ebnyr\u00eb t\u00eb paqart\u00eb? Kjo \u00ebsht\u00eb nj\u00eb sugjerim p\u00ebr vajz\u00ebn, ndoshta. N\u00ebse thjesht b\u00ebj nj\u00eb p\u00ebrdit\u00ebsim t\u00eb rreshtit, transaksioni do t\u00eb filloj\u00eb n\u00eb PostgreSQL dhe do t\u00eb p\u00ebrfundoj\u00eb vet\u00ebm kur t\u00eb ndodhi \u00e7arja e lidhjes?<\/em><\/p>\n<p><\/p>\n<p>N\u00ebse tani flisni p\u00ebr nivelin e aplikacionit, at\u00ebher\u00eb kjo varet nga ai drejtues q\u00eb po p\u00ebrdorni, nga ai ORM q\u00eb p\u00ebrdoret. Atje ka shum\u00eb cil\u00ebsime. N\u00ebse keni aktivizuar auto commit on, at\u00ebher\u00eb fillon nj\u00eb transaksion, menj\u00ebher\u00eb mbyllet.<\/p>\n<p><\/p>\n<p><em>Dometh\u00ebn\u00eb, ajo mbyllet menj\u00ebher\u00eb pas p\u00ebrdit\u00ebsimit?<\/em><\/p>\n<p><\/p>\n<p>Kjo varet nga cil\u00ebsimet. Nj\u00eb cil\u00ebsim e p\u00ebrmenda. Ky \u00ebsht\u00eb auto commit on. Ai \u00ebsht\u00eb mjaft i zakonsh\u00ebm. N\u00ebse \u00ebsht\u00eb aktivizuar, at\u00ebher\u00eb nj\u00eb transaksion fillohet dhe mbyllet menj\u00ebher\u00eb. N\u00ebse nuk e keni th\u00ebn\u00eb qart\u00eb \"fillo transaksionin\" dhe \"p\u00ebrfundo transaksionin\", por thjesht e keni nisur nj\u00eb pyetje n\u00eb sesion. <\/p>\n<p><\/p>\n<p><em>P\u00ebrsh\u00ebndetje! Faleminderit p\u00ebr raportin! T\u00eb supozojm\u00eb se kemi nj\u00eb baz\u00eb q\u00eb po rritet dhe papritur n\u00eb serveri po i mbaron vendi. A ka ndonj\u00eb mjet p\u00ebr t\u00eb korrigjuar k\u00ebt\u00eb situat\u00eb?<\/em> <\/p>\n<p><\/p>\n<p>Vendin n\u00eb server duhet t\u00eb monitorohet n\u00eb t\u00eb v\u00ebrtet\u00eb. <\/p>\n<p><\/p>\n<p><em>P\u00ebr shembull, DBA shkoi t\u00eb pinte \u00e7aj, ishte n\u00eb pushim etj.<\/em><\/p>\n<p><\/p>\n<p>Kur krijohet sistemi i skedar\u00ebve, ka t\u00eb pakt\u00ebn nj\u00eb hap\u00ebsir\u00eb rezerv\u00eb, ku nuk shkruhen t\u00eb dh\u00ebna. <\/p>\n<p><\/p>\n<p><em>Dhe n\u00ebse \u00ebsht\u00eb krejt\u00ebsisht n\u00eb zero?<\/em><\/p>\n<p><\/p>\n<p>Atje quhet hap\u00ebsir\u00eb e rezervuar, dmth ajo mund t\u00eb \u00e7lirohet dhe n\u00eb var\u00ebsi t\u00eb asaj se sa e madhe \u00ebsht\u00eb krijuar, ju keni marr\u00eb hap\u00ebsir\u00eb t\u00eb lir\u00eb. Nuk e di sa \u00ebsht\u00eb p\u00ebr parazgjedhje atje. N\u00eb nj\u00eb rast tjet\u00ebr \u2013 duhen sjell\u00eb disqe, n\u00eb m\u00ebnyr\u00eb q\u00eb t\u00eb keni hap\u00ebsir\u00eb p\u00ebr t\u00eb realizuar operacionin e rikthimit. Mund t\u00eb fshini ndonj\u00eb tabel\u00eb q\u00eb ju garanton q\u00eb nuk ju nevojitet. <\/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 baz\u00eb t\u00eb vendit, identifikohet se \u00e7far\u00eb \u00ebsht\u00eb m\u00eb e mira t\u00eb b\u00ebhet, sepse ka t\u00eb dh\u00ebna kritike, ka t\u00eb dh\u00ebna jo kritike. Dhe p\u00ebr \u00e7do baz\u00eb dhe aplikacion q\u00eb punon me t\u00eb, kjo varet nga biznesi. Gjithmon\u00eb zgjidhet sipas hap\u00ebsir\u00ebs. <\/p>\n<p><\/p>\n<p><em>Faleminderit p\u00ebr raportin! Kam dy pyetje. E para, ju treguat diapozitiv\u00eb ku tregonit se n\u00eb rastin e transaksioneve t\u00eb ngecura rritet si v\u00ebllimi i hap\u00ebsir\u00ebs tabelore, ashtu edhe madh\u00ebsia e indeksit. Dhe m\u00eb pas n\u00eb raport ishte nj\u00eb s\u00ebr\u00eb utilitar\u00ebsh q\u00eb paketojn\u00eb tabel\u00ebn. Por \u00e7far\u00eb ndodh me indeksin?<\/em><\/p>\n<p><\/p>\n<p>Ata gjithashtu i paketojn\u00eb. <\/p>\n<p><\/p>\n<p><em>Por vakuumi nuk preket indeksin?<\/em><\/p>\n<p><\/p>\n<p>Disa punojn\u00eb me indekset. P\u00ebr shembull, pg_rapack, pgcompacttable. Vakumi krijon p\u00ebrs\u00ebri indekset, e ndikon ato. Q\u00ebllimi i VACUUM FULL \u00ebsht\u00eb q\u00eb t\u00eb rid\u00ebshmoj\u00eb gjith\u00e7ka, dometh\u00ebn\u00eb punon me t\u00eb gjith\u00eb. <\/p>\n<p><\/p>\n<p><em>Dhe pyetja e dyt\u00eb. Nuk e kuptova pse raportet n\u00eb replikat varen kaq shum\u00eb nga vet\u00eb replikimi. M\u00eb dukej se raportet jan\u00eb lexime, nd\u00ebrsa replikimi \u00ebsht\u00eb shkrim.<\/em> <\/p>\n<p><\/p>\n<p>Ku ndodh konflikti i replikimit? Ne kemi nj\u00eb Master, ku ndodhin proceset. Ndodh nj\u00eb avtokombinim. Avtokombinimi, n\u00eb fakt, \u00e7far\u00eb b\u00ebn? Ai eliminon disa rreshta t\u00eb vjet\u00ebr. N\u00ebse n\u00eb k\u00ebt\u00eb koh\u00eb n\u00eb replik\u00eb ka nj\u00eb k\u00ebrkes\u00eb q\u00eb lexon k\u00ebta rreshta t\u00eb vjet\u00ebr, dhe n\u00eb Master ka ndodhur situata q\u00eb avtokombinimi i ka sh\u00ebnuar k\u00ebta rreshta si t\u00eb mundsh\u00ebm p\u00ebr rikonstruktim, ne i kemi rikonstruktuar. Dhe na \u00ebsht\u00eb d\u00ebrguar nj\u00eb paket\u00eb t\u00eb dh\u00ebnash, kur duhet t\u00eb rikonstruktojm\u00eb ata rreshta q\u00eb jan\u00eb t\u00eb nevojsh\u00ebm p\u00ebr k\u00ebrkes\u00ebn n\u00eb replik\u00eb, procesi i replikimit do t\u00eb pres\u00eb at\u00eb koh\u00eb t\u00eb skadimit q\u00eb keni vendosur. Pastaj PostgreSQL do t\u00eb vendos\u00eb se \u00e7far\u00eb \u00ebsht\u00eb m\u00eb e r\u00ebnd\u00ebsishme p\u00ebr t\u00eb. Dhe replikimi \u00ebsht\u00eb m\u00eb i r\u00ebnd\u00ebsish\u00ebm p\u00ebr t\u00eb se sa k\u00ebrkesa dhe ai do t\u00eb ndaloj\u00eb k\u00ebrkes\u00ebn p\u00ebr t\u00eb realizuar k\u00ebto ndryshime n\u00eb replik\u00eb. <\/p>\n<p><\/p>\n<p><em>Andrej, kam nj\u00eb pyetje. K\u00ebto grafik\u00ebt e mrekulluesh\u00ebm q\u00eb treguat gjat\u00eb prezantimit, jan\u00eb rezultati i pun\u00ebs s\u00eb ndonj\u00eb utiliti tuaj? \u00c7far\u00eb p\u00ebrdoret p\u00ebr t\u00eb nd\u00ebrtuar grafik\u00ebt?<\/em><\/p>\n<p><\/p>\n<p>Ky \u00ebsht\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 ky nj\u00eb produkt komercial?<\/em><\/p>\n<p><\/p>\n<p>Po. Ky \u00ebsht\u00eb nj\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.0.1 - 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.0.1\" \/>\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. Andrej Salnikov | ProHoster","description":"T\u00eb ftoj t\u00eb njoh\u00ebsh shpjegimin e raportit t\u00eb fillimit t\u00eb vitit 2016 nga Andrei Salnikov \"Gabimet tipike n\u00eb aplikacione, t\u00eb cilat \u00e7ojn\u00eb n\u00eb bloat n\u00eb postgresql\" N\u00eb k\u00ebt\u00eb raport do t\u00eb analizoj t\u00eb gjith\u00eb themelore.","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}]}}