{"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\/cs\/blog\/administrirovanie\/tipovye-oshibki-v-prilozheniyah-kotorye-vedut-k-bloat-v-postgresql-andrej-salnikov","title":{"rendered":"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"<p><strong>Doporu\u010duji se sezn\u00e1mit s v\u00fdkladem zpr\u00e1vy Andrey Salnikova z po\u010d\u00e1tku roku 2016 \"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL\"<\/strong><\/p>\n<p><\/p>\n<p>V t\u00e9to zpr\u00e1v\u011b se pod\u00edv\u00e1m na hlavn\u00ed chyby v aplikac\u00edch, kter\u00e9 vznikaj\u00ed ve f\u00e1zi n\u00e1vrhu a psan\u00ed k\u00f3du aplikace. A zam\u011b\u0159\u00edm se pouze na ty chyby, kter\u00e9 vedou k bloat v PostgreSQL. Obvykle je to za\u010d\u00e1tek konce v\u00fdkonu va\u0161eho syst\u00e9mu jako celku, p\u0159i\u010dem\u017e zpo\u010d\u00e1tku nebyly vid\u011bt \u017e\u00e1dn\u00e9 p\u0159edpoklady pro to.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej 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>R\u00e1d v\u00e1s v\u0161echny zdrav\u00edm! Tato zpr\u00e1va nen\u00ed tak technick\u00e1 jako p\u0159edchoz\u00ed od m\u00e9ho kolegy. Tato zpr\u00e1va je ur\u010dena p\u0159edev\u0161\u00edm pro v\u00fdvoj\u00e1\u0159e backendov\u00fdch syst\u00e9m\u016f, proto\u017ee m\u00e1me pom\u011brn\u011b velk\u00e9 mno\u017estv\u00ed klient\u016f. A v\u0161ichni d\u011blaj\u00ed stejn\u00e9 chyby. O t\u011bchto chyb\u00e1ch v\u00e1m pov\u00edm. Vysv\u011btl\u00edm, k \u010demu fat\u00e1ln\u00edmu a \u0161patn\u00e9mu vedou tyto chyby. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/c6c84dbafc595ae3068cd8bf804ccee7.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pro\u010d se chyby d\u011blaj\u00ed? D\u011blaj\u00ed se ze dvou d\u016fvod\u016f: na n\u00e1hodu, mo\u017en\u00e1 to vyjde, a z neznalosti n\u011bkter\u00fdch mechanism\u016f, kter\u00e9 se d\u011bj\u00ed na \u00farovni mezi datab\u00e1z\u00ed a aplikac\u00ed, a tak\u00e9 v samotn\u00e9 datab\u00e1zi. <\/p>\n<p><\/p>\n<p>P\u0159ivedu v\u00e1m t\u0159i p\u0159\u00edklady s hrozn\u00fdmi obr\u00e1zky toho, jak v\u0161e dopadlo \u0161patn\u011b. Stru\u010dn\u011b pop\u00ed\u0161u mechanismus, kter\u00fd tam prob\u00edh\u00e1. A jak s nimi bojovat, kdy\u017e se ji\u017e staly, a jak\u00e9 preventivn\u00ed metody pou\u017e\u00edt k prevenci chyb. Pov\u011bd\u00edm o pomocn\u00fdch n\u00e1stroj\u00edch a poskytnu u\u017eite\u010dn\u00e9 odkazy. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b0230d992f5b44f0e542df2e4b4d526f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pou\u017eil jsem testovac\u00ed datab\u00e1zi, kde jsem m\u011bl dv\u011b tabulky. Jedna tabulka se zahrnut\u00fdmi \u00fa\u010dty klient\u016f, druh\u00e1 s operacemi na t\u011bchto \u00fa\u010dtech. A s ur\u010ditou periodicnost\u00ed aktualizujeme z\u016fstatky na t\u011bchto \u00fa\u010dtech.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/1d2a21278b095b07546e1bb870819dbf.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V\u00fdchoz\u00ed data tabulky: je dost mal\u00e1, 2 MB. Doba odezvy na datab\u00e1zi a konkr\u00e9tn\u011b na tabulku je tak\u00e9 velmi dobr\u00e1. A tak\u00e9 dost dobr\u00e9 zat\u00ed\u017een\u00ed \u2013 2000 operac\u00ed za sekundu na tabulce.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/dea5a4b1ac7952f8cfed0e2e1cfc10dd.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A skrze tuto zpr\u00e1vu v\u00e1m budu ukazovat grafy, aby bylo jasn\u00e9, co se d\u011bje. V\u017edy budou 2 sn\u00edmky s grafy. Prvn\u00ed sn\u00edmek \u2013 to, co se d\u011bje obecn\u011b na serveru. <\/p>\n<p><\/p>\n<p>A v t\u00e9to situaci vid\u00edme, \u017ee skute\u010dn\u011b m\u00e1me tabulku mal\u00e9 velikosti. Index je mal\u00fd, 2 MB. To je prvn\u00ed graf zleva. <\/p>\n<p><\/p>\n<p>Pr\u016fm\u011brn\u00e1 doba odezvy na serveru je tak\u00e9 stabiln\u00ed, mal\u00e1. To je prav\u00fd horn\u00ed graf. <\/p>\n<p><\/p>\n<p>Lev\u00fd doln\u00ed graf ukazuje nejdel\u0161\u00ed transakce. Vid\u00edme, \u017ee transakce jsou prov\u00e1d\u011bny rychle. A automatick\u00fd \u00faklid zat\u00edm nefunguje, proto\u017ee \u2013 to byl startovn\u00ed test. D\u00e1le bude fungovat a bude n\u00e1m u\u017eite\u010dn\u00fd.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/9e9b46acd9a878ff70ca4a7850adff09.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Druh\u00fd sn\u00edmek bude v\u017edy zam\u011b\u0159en na testovac\u00ed tabulku. V t\u00e9to situaci neust\u00e1le aktualizujeme z\u016fstatky na \u00fa\u010dtech klienta. A vid\u00edme, \u017ee pr\u016fm\u011brn\u00e1 doba odezvy p\u0159i aktualizaci je velmi dobr\u00e1, m\u00e9n\u011b ne\u017e milisekundu. Vid\u00edme, \u017ee tak\u00e9 procesorov\u00e9 zdroje (to je prav\u00fd horn\u00ed graf) jsou spot\u0159ebov\u00e1v\u00e1ny rovnom\u011brn\u011b a v dostate\u010dn\u011b mal\u00fdch hodnot\u00e1ch. <\/p>\n<p><\/p>\n<p>Prav\u00fd doln\u00ed graf ukazuje, kolik opera\u010dn\u00ed a diskov\u00e9 pam\u011bti proch\u00e1z\u00edme p\u0159i hled\u00e1n\u00ed na\u0161eho po\u017eadovan\u00e9ho \u0159\u00e1dku, ne\u017e ho aktualizujeme. A po\u010det operac\u00ed na tabulce je 2000 za sekundu, jak jsem \u0159\u00edkal na za\u010d\u00e1tku. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/84760d52716209b8dcfffb462c67a8e1.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A te\u010f se st\u00e1v\u00e1 trag\u00e9die. Z n\u011bjak\u00e9ho d\u016fvodu vznik\u00e1 dlouh\u00e1 zapomenut\u00e1 transakce. P\u0159\u00ed\u010diny jsou v\u011bt\u0161inou ban\u00e1ln\u00ed: <\/p>\n<p><\/p>\n<ul>\n<li>Jednou z nejb\u011b\u017en\u011bj\u0161\u00edch je to, \u017ee jsme v k\u00f3du aplikace za\u010dali volat extern\u00ed slu\u017ebu. A tato slu\u017eba n\u00e1m neodpov\u00edd\u00e1. Tedy jsme otev\u0159eli transakci, provedli zm\u011bnu v datab\u00e1zi a \u0161li si v aplikaci \u010d\u00edst e-maily nebo k jin\u00e9 slu\u017eb\u011b v r\u00e1mci na\u0161\u00ed infrastruktury, a ta n\u00e1m n\u011bjak\u00fdm zp\u016fsobem neodpov\u00edd\u00e1. A m\u00e1me vis\u00edc\u00ed relaci v stavu \u2013 nev\u00edme, kdy se to vy\u0159e\u0161\u00ed.<\/li>\n<li>Druh\u00e1 situace, kdy v na\u0161em k\u00f3du z n\u011bjak\u00e9ho d\u016fvodu do\u0161lo k v\u00fdjimce. A my jsme ve v\u00fdjimce neprovedli uzav\u0159en\u00ed transakce. A m\u00e1me vis\u00edc\u00ed relaci s otev\u0159enou transakc\u00ed. <\/li>\n<li>A posledn\u00ed \u2013 to je tak\u00e9 docela \u010dast\u00fd p\u0159\u00edpad. To je nekvalitn\u00ed k\u00f3d. N\u011bkter\u00e9 frameworky otev\u00edraj\u00ed transakci. Ta vis\u00ed a v aplikaci nemus\u00edte v\u011bd\u011bt, \u017ee v\u00e1m vis\u00ed. <\/li>\n<\/ul>\n<p><\/p>\n<p>Kam tohle vede? <\/p>\n<p><\/p>\n<p>K tomu, \u017ee se n\u00e1m za\u010dnou rapidn\u011b zv\u011bt\u0161ovat tabulky a indexy. To je pr\u00e1v\u011b ono bloat efekt. Pro datab\u00e1zi to bude znamenat, \u017ee n\u00e1m rychle vzroste doba odezvy datab\u00e1ze, zv\u00fd\u0161\u00ed se z\u00e1t\u011b\u017e na datab\u00e1zov\u00fd server. A jako v\u00fdsledek bude trp\u011bt aplikace. Proto\u017ee pokud jste v k\u00f3du tr\u00e1vili 10 milisekund na dotaz do datab\u00e1ze, 10 milisekund na svou logiku, tak va\u0161e funkce pracovala 20 milisekund. A te\u010f bude situace mnohem smutn\u011bj\u0161\u00ed. <\/p>\n<p><\/p>\n<p>A poj\u010fme se pod\u00edvat, co se d\u011bje. Lev\u00fd doln\u00ed graf ukazuje, \u017ee m\u00e1me dlouhou transakci. A kdy\u017e se pod\u00edv\u00e1me na lev\u00fd horn\u00ed graf, vid\u00edme, \u017ee velikost tabulky z dvou megabajt\u016f vyst\u0159elila na 300 megabajt\u016f. P\u0159itom po\u010det dat v tabulce se nezm\u011bnil, tj. je tam dostate\u010dn\u00e9 mno\u017estv\u00ed nepot\u0159ebn\u00fdch dat.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/b1947f6487e440a770a1251a955e62d2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Celkov\u00e1 situace ohledn\u011b pr\u016fm\u011brn\u00e9 doby odezvy serveru se tak\u00e9 zm\u011bnila o n\u011bkolik \u0159\u00e1d\u016f. To znamen\u00e1, \u017ee v\u0161echny po\u017eadavky na server za\u010daly v\u00fdrazn\u011b zpomalovat. A z\u00e1rove\u0148 se spustily intern\u00ed procesy Postgres ve form\u011b autovacuum, kter\u00e9 se sna\u017e\u00ed n\u011bco d\u011blat a spot\u0159ebov\u00e1vaj\u00ed zdroje.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/8e91f035d1dd04e72e842d714cc611a5.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Co se d\u011bje s na\u0161\u00ed tabulkou? To sam\u00e9. Pr\u016fm\u011brn\u00e1 doba odezvy tabulky n\u00e1m vzrostla o n\u011bkolik \u0159\u00e1d\u016f. Pokud konkr\u00e9tn\u011b zhodnot\u00edme spot\u0159ebovan\u00e9 zdroje, vid\u00edme, \u017ee se v\u00fdrazn\u011b zv\u00fd\u0161ila z\u00e1t\u011b\u017e na procesor. To je prav\u00fd horn\u00ed graf. A zv\u00fd\u0161ila se proto, \u017ee procesoru se mus\u00ed proj\u00edt spoustou nepot\u0159ebn\u00fdch \u0159\u00e1dk\u016f p\u0159i hled\u00e1n\u00ed jednoho pot\u0159ebn\u00e9ho. To je prav\u00fd doln\u00ed graf. A jako v\u00fdsledek \u2013 po\u010det vol\u00e1n\u00ed za sekundu za\u010dal v\u00fdrazn\u011b klesat, proto\u017ee datab\u00e1ze nest\u00edh\u00e1 zpracov\u00e1vat stejn\u00e9 mno\u017estv\u00ed po\u017eadavk\u016f. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/424685ce852bc5f3ef77151f8d3d7289.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Mus\u00edme se vr\u00e1tit k \u017eivotu. Pust\u00edme se na internet a zjist\u00edme, \u017ee dlouh\u00e9 transakce zp\u016fsobuj\u00ed probl\u00e9m. Najdeme a zru\u0161\u00edme tuto transakci. A v\u0161e se zase vr\u00e1t\u00ed do norm\u00e1lu. V\u0161echno funguje, jak m\u00e1. <\/p>\n<p><\/p>\n<p>Zklidnili jsme se, ale po n\u011bjak\u00e9 dob\u011b za\u010d\u00edn\u00e1me vn\u00edmat, \u017ee aplikace nefunguje tak, jak p\u0159ed hav\u00e1ri\u00ed. Po\u017eadavky jsou st\u00e1le pomalej\u0161\u00ed, a to v\u00fdrazn\u011b pomalej\u0161\u00ed. V m\u00e9m konkr\u00e9tn\u00edm p\u0159\u00edpad\u011b je to o 50-100 % pomalej\u0161\u00ed. Z\u00e1t\u011b\u017e na serveru je tak\u00e9 vy\u0161\u0161\u00ed ne\u017e p\u0159ed hav\u00e1ri\u00ed. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ee3037719a31f8d18fc42745716c4da5.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A ot\u00e1zka: \u201eCo se s datab\u00e1z\u00ed v tu chv\u00edli d\u011bje?\u201c. A v datab\u00e1zi se d\u011bje n\u00e1sleduj\u00edc\u00ed situace. Na grafu transakc\u00ed vid\u00edte, \u017ee se zastavila a opravdu tam nejsou dlouh\u00e9 transakce. Ale velikosti tabulky b\u011bhem hav\u00e1rie fat\u00e1ln\u011b vzrostly. A od t\u00e9 doby se nezmen\u0161ily. Pr\u016fm\u011brn\u00e1 doba v datab\u00e1zi se stabilizovala. A odpov\u011bdi se zdaj\u00ed b\u00fdt adekv\u00e1tn\u00ed s p\u0159ijatelnou rychlost\u00ed pro n\u00e1s. Autovacuum se stalo aktivn\u011bj\u0161\u00edm a za\u010dalo s tabulkou n\u011bco d\u011blat, proto\u017ee mus\u00ed zpracovat v\u011bt\u0161\u00ed mno\u017estv\u00ed dat. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/f6205242c4024dc3ed4cade5844dbf76.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Konkr\u00e9tn\u011b k testovan\u00e9 tabulce s \u00fa\u010dty, kde m\u011bn\u00edme z\u016fstatky: doba odezvy na dotaz se zd\u00e1, \u017ee se vr\u00e1tila do norm\u00e1lu. Ve skute\u010dnosti je v\u0161ak 1,5kr\u00e1t vy\u0161\u0161\u00ed.<\/p>\n<p><\/p>\n<p>A co se zat\u00ed\u017een\u00ed CPU t\u00fd\u010de, vid\u00edme, \u017ee zat\u00ed\u017een\u00ed procesoru se nevr\u00e1tilo na po\u017eadovanou \u00farove\u0148 p\u0159ed hav\u00e1ri\u00ed. P\u0159\u00ed\u010diny lze nal\u00e9zt v prav\u00e9m doln\u00edm grafu. Je z\u0159ejm\u00e9, \u017ee tam doch\u00e1z\u00ed k proch\u00e1zen\u00ed ur\u010dit\u00e9ho mno\u017estv\u00ed pam\u011bti. To znamen\u00e1, \u017ee p\u0159i hled\u00e1n\u00ed po\u017eadovan\u00e9ho \u0159\u00e1dku utr\u00e1c\u00edme zdroje serveru datab\u00e1ze p\u0159i proch\u00e1zen\u00ed nepot\u0159ebn\u00fdmi daty. Po\u010det transakc\u00ed za sekundu se stabilizoval. <\/p>\n<p><\/p>\n<p>Celkov\u011b je to dobr\u00e9, ale situace je hor\u0161\u00ed ne\u017e byla. Z\u0159ejm\u00e1 degradace datab\u00e1ze jako d\u016fsledek na\u0161\u00ed aplikace, kter\u00e1 s touto datab\u00e1z\u00ed pracuje. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/0dfc9cd453fd84bc639ffd99cf189fd2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A abychom pochopili, co se tam d\u011bje, pokud jste nebyli na p\u0159edchoz\u00ed p\u0159edn\u00e1\u0161ce, te\u010f tro\u0161ku teorie. Teorie o intern\u00edm procesu. Pro\u010d je pot\u0159eba autovakuum a co d\u011bl\u00e1?<\/p>\n<p><\/p>\n<p>Doslova stru\u010dn\u011b pro porozum\u011bn\u00ed. V ur\u010dit\u00e9m okam\u017eiku m\u00e1me tabulku. V tabulce m\u00e1me \u0159\u00e1dky. Tyto \u0159\u00e1dky mohou b\u00fdt aktivn\u00ed, \u017eiv\u00e9, pot\u0159ebn\u00e9 n\u00e1m te\u010f. Na obr\u00e1zku jsou ozna\u010deny zelenou barvou. A jsou tu mrtv\u00e9 \u0159\u00e1dky, kter\u00e9 u\u017e byly zpracov\u00e1ny, byly aktualizov\u00e1ny, objevily se na nich nov\u00e9 z\u00e1znamy. A jsou ozna\u010deny, \u017ee u\u017e nejsou zaj\u00edmav\u00e9 pro datab\u00e1zi. Ale z\u016fst\u00e1vaj\u00ed v tabulce kv\u016fli zvl\u00e1\u0161tnosti Postgresu.<\/p>\n<p><\/p>\n<p>Pro\u010d pot\u0159ebujeme autovakuum? Autovakuum v ur\u010dit\u00e9m okam\u017eiku p\u0159ich\u00e1z\u00ed, obrac\u00ed se na datab\u00e1zi a pt\u00e1 se: \u201eDej mi, pros\u00edm, id nejstar\u0161\u00ed transakce, kter\u00e1 je v tuto chv\u00edli otev\u0159en\u00e1 v datab\u00e1zi.\u201c Datab\u00e1ze vrac\u00ed toto id. A autovakuum, op\u00edraje se o n\u011bj, proch\u00e1z\u00ed \u0159\u00e1dky v tabulce. A pokud vid\u00ed, \u017ee n\u011bkter\u00e9 \u0159\u00e1dky byly zm\u011bn\u011bny mnohem star\u0161\u00edmi transakcemi, m\u00e1 pr\u00e1vo je ozna\u010dit jako \u0159\u00e1dky, kter\u00e9 m\u016f\u017eeme v budoucnu znovu pou\u017e\u00edt, zapisuj\u00edc\u00ed tam nov\u00e1 data. To je pozad\u00ed procesu.<\/p>\n<p><\/p>\n<p>My v t\u00e9 dob\u011b pokra\u010dujeme v pr\u00e1ci s datab\u00e1z\u00ed, nad\u00e1le prov\u00e1d\u00edme n\u011bjak\u00e9 zm\u011bny v tabulce. A na tyto \u0159\u00e1dky, kter\u00e9 m\u016f\u017eeme znovu pou\u017e\u00edt, zapisujeme nov\u00e1 data. Takto m\u00e1me cyklus, to znamen\u00e1, \u017ee se tam neust\u00e1le objevuj\u00ed n\u011bjak\u00e9 mrtv\u00e9 star\u00e9 \u0159\u00e1dky, m\u00edsto nich zapisujeme nov\u00e9 \u0159\u00e1dky, kter\u00e9 pot\u0159ebujeme. A to je norm\u00e1ln\u00ed stav pro pr\u00e1ci s PostgreSQL.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/98dd4451a7ca5f418775f72822c2840c.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Co se stalo b\u011bhem hav\u00e1rie? Jak tento proces prob\u00edhal?<\/p>\n<p><\/p>\n<p>M\u011bli jsme tabulku v ur\u010dit\u00e9m stavu, n\u011bkter\u00e9 \u0159\u00e1dky byly \u017eiv\u00e9, n\u011bkter\u00e9 mrtv\u00e9. P\u0159i\u0161el autovakua. Zeptal se datab\u00e1ze, jak\u00e1 je na\u0161e nejstar\u0161\u00ed transakce a jak\u00e9 m\u00e1 ID. Z\u00edskal toto ID, kter\u00e9 m\u016f\u017ee b\u00fdt n\u011bkolik hodin star\u00e9, nebo m\u016f\u017ee b\u00fdt deset minut star\u00e9. Z\u00e1le\u017e\u00ed to na tom, jak siln\u00e9 je zat\u00ed\u017een\u00ed va\u0161\u00ed datab\u00e1ze. A za\u010dal hledat \u0159\u00e1dky, kter\u00e9 m\u016f\u017ee ozna\u010dit jako znovupou\u017eiteln\u00e9. A takov\u00e9 \u0159\u00e1dky v na\u0161\u00ed tabulce nena\u0161el. <\/p>\n<p><\/p>\n<p>Ale v tu dobu st\u00e1le pracujeme s tabulkou. N\u011bco v n\u00ed d\u011bl\u00e1me, aktualizujeme, m\u011bn\u00edme data. A co m\u00e1 datab\u00e1ze v tu dobu d\u011blat? Nem\u00e1 na v\u00fdb\u011br, ne\u017e zapisovat nov\u00e9 \u0159\u00e1dky na konec existuj\u00edc\u00ed tabulky. T\u00edm se za\u010d\u00edn\u00e1 zvy\u0161ovat velikost tabulky. <\/p>\n<p><\/p>\n<p>Ve skute\u010dnosti pot\u0159ebujeme pro pr\u00e1ci zelen\u00e9 \u0159\u00e1dky. Ale b\u011bhem takov\u00e9ho probl\u00e9mu m\u00e1me \u010dasto velmi n\u00edzk\u00e9 procento zelen\u00fdch \u0159\u00e1dk\u016f v cel\u00e9m objemu tabulky. <\/p>\n<p><\/p>\n<p>A kdy\u017e vykon\u00e1v\u00e1me dotaz, datab\u00e1ze mus\u00ed proj\u00edt v\u0161emi \u0159\u00e1dky: jak \u010derven\u00fdmi, tak zelen\u00fdmi, aby na\u0161la pot\u0159ebn\u00fd \u0159\u00e1dek. A efekt zv\u011bt\u0161en\u00ed tabulky zbyte\u010dn\u00fdmi daty se naz\u00fdv\u00e1 \u201ebloat\u201c, co\u017e n\u00e1m tak\u00e9 \u017eere na\u0161e diskov\u00e9 m\u00edsto. Pamatujete, bylo to 2 MB, nyn\u00ed je to 300 MB? A te\u010f si vym\u011b\u0148te megabyty za gigabyty a rychle p\u0159ijdete o v\u0161echny z\u00e1soby sv\u00fdch diskov\u00fdch zdroj\u016f.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/fe7cb6b5c99610744ebcfe5ad7235d1e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Jak\u00e9 mohou b\u00fdt d\u016fsledky pro n\u00e1s? <\/p>\n<p><\/p>\n<ul>\n<li>V m\u00e9m p\u0159\u00edkladu tabulka a index vzrostly 150kr\u00e1t. N\u011bkte\u0159\u00ed na\u0161i klienti m\u011bli hor\u0161\u00ed p\u0159\u00edpady, kdy za\u010dalo doch\u00e1zet m\u00edsto na disku. <\/li>\n<li>Velikost tabulek se sama o sob\u011b nikdy nezmen\u0161\u00ed. Autovakua m\u016f\u017ee v n\u011bkter\u00fdch p\u0159\u00edpadech od\u0159\u00edznout konec tabulky, pokud tam jsou pouze mrtv\u00e9 \u0159\u00e1dky. Ale proto\u017ee doch\u00e1z\u00ed k neust\u00e1l\u00e9 rotaci, jeden zelen\u00fd \u0159\u00e1dek m\u016f\u017ee na konci z\u016fstat viset a neaktualizovat se, zat\u00edmco v\u0161echny ostatn\u00ed budou kdesi na za\u010d\u00e1tku tabulky zaps\u00e1ny. Ale to je tak nepravd\u011bpodobn\u00e9, \u017ee by se tabulka sama od sebe zmen\u0161ila, na to se ned\u00e1 spol\u00e9hat. <\/li>\n<li>Datab\u00e1ze mus\u00ed proch\u00e1zet celou hromadou zbyte\u010dn\u00fdch \u0159\u00e1dk\u016f. A my pl\u00fdtv\u00e1me diskov\u00fdmi zdroji, pl\u00fdtv\u00e1me procesorov\u00fdmi zdroji a energi\u00ed. <\/li>\n<li>A to p\u0159\u00edmo ovliv\u0148uje na\u0161i aplikaci, proto\u017ee pokud na za\u010d\u00e1tku jsme tr\u00e1vili 10 milisekund na po\u017eadavek a 10 milisekund na n\u00e1\u0161 k\u00f3d, b\u011bhem hav\u00e1rie jsme za\u010dali tr\u00e1vit sekundu na po\u017eadavek a 10 milisekund na k\u00f3d, tedy v\u00fdkon aplikace klesl o \u0159\u00e1d. A kdy\u017e byla hav\u00e1rie odstran\u011bna, za\u010dali jsme tr\u00e1vit 20 milisekund na po\u017eadavek a 10 milisekund na k\u00f3d. To znamen\u00e1, \u017ee jsme stejn\u011b klesli na 1,5kr\u00e1t ni\u017e\u0161\u00ed v\u00fdkon. A to v\u0161e kv\u016fli jedn\u00e9 transakci, kter\u00e1 se zasekla, pravd\u011bpodobn\u011b vinou n\u00e1s. <\/li>\n<li>A ot\u00e1zka zn\u00ed: \"Jak v\u0161e vr\u00e1tit zp\u011bt?\" Abychom dos\u00e1hli toho, \u017ee po\u017eadavky budou znovu b\u011bhat stejn\u011b rychle, jako p\u0159ed hav\u00e1ri\u00ed. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/1bf46e8cd9b28ca2862f978f2ca85819.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>K tomu existuje ur\u010dit\u00fd pracovn\u00ed cyklus, kter\u00fd se prov\u00e1d\u00ed. <\/p>\n<p><\/p>\n<p>Nejprve mus\u00edme naj\u00edt problematick\u00e9 tabulky, kter\u00e9 se nafoukly. Ch\u00e1peme, \u017ee u n\u011bkter\u00fdch tabulek se z\u00e1pis prov\u00e1d\u00ed aktivn\u011bji, u jin\u00fdch m\u00e9n\u011b aktivn\u011b. A k tomu se pou\u017e\u00edv\u00e1 roz\u0161\u00ed\u0159en\u00ed <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/pgstattuple.html\">pgstattuple<\/a><\/noindex>. Po nainstalov\u00e1n\u00ed tohoto roz\u0161\u00ed\u0159en\u00ed m\u016f\u017eete napsat dotazy, kter\u00e9 v\u00e1m pomohou naj\u00edt tabulky, kter\u00e9 se dostate\u010dn\u011b nafoukly. <\/p>\n<p><\/p>\n<p>Pot\u00e9, co jste tyto tabulky na\u0161li, je nutn\u00e9 je zkomprimovat. K tomu ji\u017e existuj\u00ed n\u00e1stroje. V na\u0161\u00ed spole\u010dnosti pou\u017e\u00edv\u00e1me t\u0159i n\u00e1stroje. Prvn\u00ed je vestav\u011bn\u00fd VACUUM FULL. Je p\u0159\u00edsn\u00fd, tvrd\u00fd a bezohledn\u00fd, ale n\u011bkdy je velmi u\u017eite\u010dn\u00fd. <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/reorg\/pg_repack\">Pg_repack<\/a><\/noindex> a <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pgcompacttable\">pgcompacttable<\/a><\/noindex> \u2013 to jsou extern\u00ed utilitky pro kompresi tabulek. A ty se k datab\u00e1zi maj\u00ed bli\u017e\u0161\u00ed p\u0159\u00edstup. <\/p>\n<p><\/p>\n<p>Pou\u017e\u00edvaj\u00ed se podle toho, co je pro v\u00e1s pohodln\u011bj\u0161\u00ed. Ale o tom budu mluvit a\u017e na z\u00e1v\u011br. Hlavn\u00ed je, \u017ee existuj\u00ed t\u0159i n\u00e1stroje. Je z \u010deho vyb\u00edrat. <\/p>\n<p><\/p>\n<p>Pot\u00e9, co jsme v\u0161echno napravili a ujistili se, \u017ee je v\u0161e v po\u0159\u00e1dku, mus\u00edme v\u011bd\u011bt, jak tomuto probl\u00e9mu v budoucnu p\u0159edej\u00edt:<\/p>\n<p><\/p>\n<ul>\n<li>Prevence je dost snadn\u00e1. Je t\u0159eba sledovat d\u00e9lku relac\u00ed na Master serveru. <strong>Zvl\u00e1\u0161t\u011b nebezpe\u010dn\u00e9 jsou relace ve stavu idle in transaction<\/strong>. To jsou ty, kter\u00e9 pr\u00e1v\u011b otev\u0159ely transakci, n\u011bco ud\u011blaly a ode\u0161ly, nebo se pouze zasekly, ztratily se v k\u00f3du. <\/li>\n<li>A pro v\u00e1s, jako pro v\u00fdvoj\u00e1\u0159e, je d\u016fle\u017eit\u00e9 testovat k\u00f3d p\u0159i vzniku t\u011bchto situac\u00ed. Nen\u00ed to slo\u017eit\u00e9 ud\u011blat. Bude to u\u017eite\u010dn\u00e1 kontrola. Vyhnete se velk\u00e9mu mno\u017estv\u00ed \"d\u011btsk\u00fdch\" probl\u00e9m\u016f spojen\u00fdch s dlouh\u00fdmi transakcemi. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/d55465d96c4018db733954867d23aaba.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Na t\u011bchto grafech jsem v\u00e1m cht\u011bl uk\u00e1zat, jak se zm\u011bnila tabulka a chov\u00e1n\u00ed datab\u00e1ze pot\u00e9, co jsem provedl v tomto p\u0159\u00edpad\u011b VACUUM FULL na tabulce. To nen\u00ed v produkci.<\/p>\n<p><\/p>\n<p>Velikost tabulky se okam\u017eit\u011b vr\u00e1tila do norm\u00e1ln\u00edho pracovn\u00edho stavu na p\u00e1r megabajt\u016f. Na pr\u016fm\u011brnou dobu odezvy serveru to nem\u011blo v\u00fdrazn\u00fd vliv. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/cbb07ad24dba899395223d1ca25e438e.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale konkr\u00e9tn\u011b u na\u0161\u00ed zkou\u0161en\u00e9 tabulky, kde jsme aktualizovali z\u016fstatky na \u00fa\u010dtech, vid\u00edme, \u017ee pr\u016fm\u011brn\u00e1 doba odezvy na dotaz na aktualizaci dat v tabulce se sn\u00ed\u017eila na \u00farove\u0148 p\u0159ed hav\u00e1ri\u00ed. Tak\u00e9 zdroje vyu\u017e\u00edvan\u00e9 procesorem pro prov\u00e1d\u011bn\u00ed tohoto dotazu klesly na \u00farove\u0148 p\u0159ed hav\u00e1ri\u00ed. A prav\u00fd doln\u00ed graf ukazuje, \u017ee nyn\u00ed nalezneme p\u0159esn\u011b ten \u0159\u00e1dek, kter\u00fd pot\u0159ebujeme hned, ani\u017e bychom museli proch\u00e1zet hromadu mrtv\u00fdch \u0159\u00e1dk\u016f, kter\u00e9 byly p\u0159ed kompres\u00ed tabulky. A pr\u016fm\u011brn\u00e1 doba dotaz\u016f z\u016fstala p\u0159ibli\u017en\u011b na stejn\u00e9 \u00farovni. Ale tady m\u00e1m sp\u00ed\u0161e chybu m\u00e9ho hardwaru.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/c1d90c5442e02fce209dc40e048a8ca2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Na tom prvn\u00ed p\u0159\u00edb\u011bh kon\u010d\u00ed. Je to ten nejb\u011b\u017en\u011bj\u0161\u00ed. A st\u00e1v\u00e1 se to ka\u017ed\u00e9mu, bez ohledu na zku\u0161enosti klienta, kolik kvalifikovan\u00fdch program\u00e1tor\u016f tam je. D\u0159\u00edve nebo pozd\u011bji se to stane. <\/p>\n<p><\/p>\n<p>Druh\u00fd p\u0159\u00edb\u011bh, ve kter\u00e9m rozd\u011blujeme z\u00e1t\u011b\u017e a optimalizujeme serverov\u00e9 zdroje<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/cda421c554d1329107ddc935045cc1b9.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>U\u017e jsme vyrostli a stali se v\u00e1\u017en\u00fdmi hr\u00e1\u010di. A ch\u00e1peme, \u017ee m\u00e1me repliku a bylo by dobr\u00e9 vyv\u00e1\u017eit z\u00e1t\u011b\u017e: zapisovat na Master a \u010d\u00edst z repliky. A tato situace obvykle nast\u00e1v\u00e1, kdy\u017e chceme p\u0159ipravit n\u011bjak\u00e9 zpr\u00e1vy nebo ETL. A podnik tomu velmi r\u00e1da. Velmi chce r\u016fznorod\u00e9 zpr\u00e1vy s hromadou slo\u017eit\u00e9 anal\u00fdzy. <\/li>\n<li>Zpr\u00e1vy jsou hodinov\u00e9, proto\u017ee slo\u017eitou anal\u00fdzu nelze spo\u010d\u00edtat za milisekundy. My, jako odv\u00e1\u017en\u00ed kluci, p\u00ed\u0161eme k\u00f3d. D\u011bl\u00e1me v aplikaci vkl\u00e1d\u00e1n\u00ed, \u017ee z\u00e1pis prov\u00e1d\u00edme na Master, zpr\u00e1vy realizujeme na replikaci. <\/li>\n<li>Rozd\u011blujeme z\u00e1t\u011b\u017e. <\/li>\n<li>V\u0161echno funguje skv\u011ble. Jsme \u0161ikovn\u00ed. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/e94c8c492197a5015ac8aae79389a4c2.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A jak vypad\u00e1 tato situace? Konkr\u00e9tn\u011b na t\u011bchto grafech jsem tak\u00e9 p\u0159idal dobu transakc\u00ed z repliky. V\u0161echny ostatn\u00ed grafy se vztahuj\u00ed pouze k Master serveru. <\/p>\n<p><\/p>\n<p>Tabulka s reporty mi nyn\u00ed vzrostla. Jejich po\u010det se zv\u00fd\u0161il. Vid\u00edme, \u017ee pr\u016fm\u011brn\u00e1 doba odezvy serveru je stabiln\u00ed. Vid\u00edme, \u017ee na replikaci m\u00e1me dlouhou transakci, kter\u00e1 trv\u00e1 2 hodiny. Pozorujeme klidnou pr\u00e1ci autovacua, kter\u00e9 zpracov\u00e1v\u00e1 mrtv\u00e9 \u0159\u00e1dky. A v\u0161echno je u n\u00e1s v po\u0159\u00e1dku. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/2da8acf098d12d6f146ab9b9dcb54b81.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Konkr\u00e9tn\u011b k testovan\u00e9 tabulce nad\u00e1le aktualizujeme z\u016fstatky na \u00fa\u010dtech. A m\u00e1me tak\u00e9 stabiln\u00ed dobu odezvy na dotazy, stabiln\u00ed spot\u0159ebu zdroj\u016f. V\u0161echno je u n\u00e1s v po\u0159\u00e1dku. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/3621ef723017bac6fb635caf0a5f4de6.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V\u0161echno je v po\u0159\u00e1dku, dokud se tyto reporty neza\u010dnou objevovat z d\u016fvodu konfliktu s replikac\u00ed. A objevuj\u00ed se s konstantn\u00ed periodicitou. <\/p>\n<p><\/p>\n<p>Jdeme na internet a za\u010d\u00edn\u00e1me \u010d\u00edst, pro\u010d se to d\u011bje. A nach\u00e1z\u00edme \u0159e\u0161en\u00ed. <\/p>\n<p><\/p>\n<p>Prvn\u00ed \u0159e\u0161en\u00ed \u2013 zv\u00fd\u0161it zpo\u017ed\u011bn\u00ed replikace. V\u00edme, \u017ee n\u00e1\u0161 report funguje 3 hodiny. Nasad\u00edme zpo\u017ed\u011bn\u00ed replikace \u2013 3 hodiny. Spust\u00edme v\u0161echno, ale p\u0159esto se st\u00e1le objevuj\u00ed probl\u00e9my s t\u00edm, \u017ee se reporty ob\u010das objevuj\u00ed. <\/p>\n<p><\/p>\n<p>Chceme, aby bylo v\u0161e dokonal\u00e9. Jdeme d\u00e1l. A na internetu nach\u00e1z\u00edme skv\u011blou konfiguraci \u2013 hot_standby_feedback. Zapneme ji. Hot_standby_feedback n\u00e1m umo\u017e\u0148uje zadr\u017eet pr\u00e1ci autovacua na Masteru. T\u00edm se zcela zbavujeme konflikt\u016f replikace. A v\u0161e n\u00e1m funguje dob\u0159e s reporty.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/ee3c315168bdd3f392d2e8a1c5861004.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Ale co se v t\u00e9to dob\u011b d\u011bje s Master serverem? S Master serverem se d\u011bje tot\u00e1ln\u00ed katastrofa. Nyn\u00ed sledujeme grafy, kdy\u017e jsem zapnul ob\u011b tyto nastaven\u00ed. A vid\u00edme, \u017ee sezen\u00ed na replikaci n\u011bjak\u00fdm zp\u016fsobem za\u010dalo ovliv\u0148ovat situaci na Master serveru. Opravu ovliv\u0148uje, proto\u017ee zastavilo autovacuum, kter\u00e9 \u010dist\u00ed mrtv\u00e9 \u0159\u00e1dky. Velikost tabulky op\u011bt vzrostla do nebes. Pr\u016fm\u011brn\u00e1 doba prov\u00e1d\u011bn\u00ed dotaz\u016f na cel\u00e9 datab\u00e1zi tak\u00e9 vzrostla do nebes. Autovacuum se m\u00edrn\u011b zapotilo. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/980611dcce8b189dc50f422ffd3d2d72.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Konkr\u00e9tn\u011b k na\u0161\u00ed tabulce vid\u00edme, \u017ee aktualizace dat na n\u00ed tak\u00e9 vzrostla do nebes. Spot\u0159eba procesorov\u00fdch zdroj\u016f podobn\u011b v\u00fdrazn\u011b vzrostla. Op\u011bt proch\u00e1z\u00edme velk\u00e9 mno\u017estv\u00ed mrtv\u00fdch bezcenn\u00fdch \u0159\u00e1dk\u016f. A doba odezvy pro tuto tabulku, po\u010det transakc\u00ed klesl. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/a648fd313a5dccf45f0056dc13c54263.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Jak to bude vypadat, pokud nev\u00edme, o \u010dem jsem mluvil d\u0159\u00edve?<\/p>\n<p><\/p>\n<ul>\n<li>Za\u010d\u00edn\u00e1me hledat probl\u00e9my. Pokud jsme se setkali s probl\u00e9my v prvn\u00ed \u010d\u00e1sti, v\u00edme, \u017ee to m\u016f\u017ee b\u00fdt zp\u016fsobeno dlouhou transakc\u00ed, a zkoum\u00e1me Mistr. Probl\u00e9m m\u00e1me na Mistrovi. Zase se mu neda\u0159\u00ed. Zah\u0159\u00edv\u00e1 se, jeho Load Average je t\u00e9m\u011b\u0159 sto. <\/li>\n<li>\u017d\u00e1dosti tam zpomaluj\u00ed, ale nevid\u00edme \u017e\u00e1dn\u00e9 dlouh\u00e9 transakce. A nech\u00e1peme, v \u010dem je probl\u00e9m. Nev\u00edme, kde hledat. <\/li>\n<li>Kontrolujeme serverov\u00e9 vybaven\u00ed. Mo\u017en\u00e1 se n\u00e1m rozpadl raid. Mo\u017en\u00e1 n\u00e1m sho\u0159el modul pam\u011bti. M\u016f\u017ee se st\u00e1t cokoliv. Ale ne, servery jsou nov\u00e9, v\u0161e funguje skv\u011ble. <\/li>\n<li>B\u011bhaj\u00ed tam v\u0161ichni: administr\u00e1to\u0159i, v\u00fdvoj\u00e1\u0159i a \u0159editel. Nic nepom\u00e1h\u00e1. <\/li>\n<li>A v ur\u010dit\u00e9m okam\u017eiku se v\u0161echno ne\u010dekan\u011b za\u010d\u00edn\u00e1 samo opravovat. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/7d4f6417bc5cb3cec33679d843c6661d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Na replikaci v t\u00e9to dob\u011b po\u017eadavek dokon\u010dil a ode\u0161el. Dostali jsme zpr\u00e1vu. Byznys je st\u00e1le spokojen\u00fd. Jak vid\u00edme, tabulka n\u00e1m op\u011bt vzrostla a nehodl\u00e1 se zmen\u0161ovat. Na grafu sezen\u00ed jsem nechal \u00faryvek t\u00e9to dlouh\u00e9 transakce z repliky, abyste mohli posoudit, jak dlouho trv\u00e1, ne\u017e se situace stabilizuje. <\/p>\n<p><\/p>\n<p>Sezen\u00ed ode\u0161lo. A a\u017e po chv\u00edli server p\u0159ich\u00e1z\u00ed v\u00edcem\u00e9n\u011b do po\u0159\u00e1dku. A pr\u016fm\u011brn\u00e1 doba odezvy po\u017eadavk\u016f na Mistru se vrac\u00ed k norm\u00e1lu. Proto\u017ee kone\u010dn\u011b autovakua dostala mo\u017enost uklidit a ozna\u010dit ty mrtv\u00e9 \u0159\u00e1dky. A za\u010dala vykon\u00e1vat svou pr\u00e1ci. A jak rychle to d\u011bl\u00e1, tak rychle se dostaneme do po\u0159\u00e1dku.<\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/547d195e08de1566da818150a47218eb.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>U zkouman\u00e9 tabulky, kde aktualizujeme z\u016fstatky na \u00fa\u010dtech, vid\u00edme stejn\u00fd obraz. Pr\u016fm\u011brn\u00e1 doba aktualizace \u00fa\u010dtu se tak\u00e9 postupn\u011b normalizuje. Zdroje spot\u0159ebov\u00e1van\u00e9 procesorem se tak\u00e9 sni\u017euj\u00ed. A po\u010det transakc\u00ed za sekundu se vrac\u00ed k norm\u00e1lu. Ale op\u011bt ne k norm\u00e1ln\u00ed \u00farovni, jakou jsme m\u011bli p\u0159ed hav\u00e1ri\u00ed. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/29372a5e9124690931485a7aea65befc.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>V ka\u017ed\u00e9m p\u0159\u00edpad\u011b dost\u00e1v\u00e1me pokles v\u00fdkonnosti jako v prvn\u00edm p\u0159\u00edpad\u011b o p\u016fl a\u017e dv\u011b \u010d\u00edsla, a n\u011bkdy i v\u00edce. <\/p>\n<p><\/p>\n<p>Zd\u00e1 se, \u017ee jsme ud\u011blali v\u0161echno spr\u00e1vn\u011b. Rozlo\u017eili jsme z\u00e1t\u011b\u017e. Vybaven\u00ed neprostoje. Spr\u00e1vn\u011b jsme rozd\u011blili po\u017eadavky, ale stejn\u011b to dopadlo \u0161patn\u011b. <\/p>\n<p><\/p>\n<ul>\n<li>Nem\u00edt zapnut\u00fd hot_standby_feedback? Ano, obecn\u011b se nedoporu\u010duje ho zap\u00ednat bez v\u00e1\u017en\u00e9ho d\u016fvodu. Tato funkce toti\u017e p\u0159\u00edmo ovliv\u0148uje hlavn\u00ed server a pozastavuje \u010dinnost automatick\u00e9ho \u00fadr\u017ebov\u00e9ho procesu. Pokud jej zapnete na n\u011bjak\u00e9 replikaci a zapomenete na to, m\u016f\u017eete zp\u016fsobit probl\u00e9my hlavn\u00edmu serveru a vyvolat v\u00e1\u017en\u00e9 komplikace s aplikac\u00ed. <\/li>\n<li>Zv\u011bt\u0161it max_standby_streaming_delay? Ano, to plat\u00ed pro reporty. Pokud m\u00e1te t\u0159\u00edhodinov\u00fd report a nechcete, aby se zastavil kv\u016fli konflikt\u016fm replikac\u00ed, jednodu\u0161e prodlu\u017ete zpo\u017ed\u011bn\u00ed. Dlouh\u00fd report nikdy nevy\u017eaduje data, kter\u00e1 p\u0159i\u0161la do datab\u00e1ze pr\u00e1v\u011b te\u010f. Pokud jde o t\u0159\u00edhodinov\u00fd report, spou\u0161t\u00edte jej za n\u011bjak\u00e9 star\u0161\u00ed obdob\u00ed dat. A zda m\u00e1te t\u0159\u00edhodinov\u00e9 zpo\u017ed\u011bn\u00ed, nebo \u0161estis hodinov\u00e9, to nehraje \u017e\u00e1dnou roli, ale budete st\u00e1le dost\u00e1vat reporty a nebudete m\u00edt probl\u00e9my s jejich selh\u00e1n\u00edm. <\/li>\n<li>Samoz\u0159ejm\u011b, je t\u0159eba kontrolovat dlouh\u00e9 relace na replik\u00e1ch, zejm\u00e9na pokud jste se rozhodli zapnout hot_standby_feedback na replikaci. Proto\u017ee se m\u016f\u017ee st\u00e1t cokoliv. Dali jste tuto replikaci v\u00fdvoj\u00e1\u0159i, aby otestoval dotazy. Napsal \u0161\u00edlen\u00fd dotaz. Spustil ho a \u0161el si ud\u011blat \u010daj, a my m\u00e1me zahlcen\u00fd hlavn\u00ed server. Nebo jsme tam pustili \u0161patnou aplikaci. Situac\u00ed je nespo\u010det. Relace na replik\u00e1ch je t\u0159eba kontrolovat stejn\u011b pe\u010dliv\u011b, jako na hlavn\u00edm serveru. <\/li>\n<li>A pokud m\u00e1te rychl\u00e9 a dlouh\u00e9 dotazy na replik\u00e1ch, pak je v tomto p\u0159\u00edpad\u011b lep\u0161\u00ed rozd\u011blit je pro vyrovn\u00e1n\u00ed z\u00e1t\u011b\u017ee. To souvis\u00ed se streaming_delay. Pro rychl\u00e9 dotazy m\u00edt jednu replikaci s mal\u00fdm zpo\u017ed\u011bn\u00edm. Pro dlouh\u00e9 reportn\u00ed dotazy m\u00edt replikaci, kter\u00e1 m\u016f\u017ee b\u00fdt pozadu o 6 hodin nebo dokonce o den. To je zcela b\u011b\u017en\u00e1 situace. <\/li>\n<\/ul>\n<p><\/p>\n<p>Odstra\u0148ujeme n\u00e1sledky st\u00e1le stejn\u00fdm zp\u016fsobem:<\/p>\n<p><\/p>\n<ul>\n<li>Nalezneme nafouknut\u00e9 tabulky.<\/li>\n<li>A zkomprimujeme je nejvhodn\u011bj\u0161\u00edm n\u00e1strojem, kter\u00fd m\u00e1me k dispozici. <\/li>\n<\/ul>\n<p><\/p>\n<p>Druh\u00e1 historie na tom skon\u010dila. P\u0159ech\u00e1z\u00edme k t\u0159et\u00ed historii. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/c8784e0be27883640cb8f6919a1c25e0.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Tak\u00e9 docela b\u011b\u017en\u00e1 pro n\u00e1s, ve kter\u00e9 prov\u00e1d\u00edme migraci. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/a04b9cd88a0e7e8a3b960ef930eb2657.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<ul>\n<li>Ka\u017ed\u00fd software roste. M\u011bn\u00ed se po\u017eadavky na n\u011bj. V ka\u017ed\u00e9m p\u0159\u00edpad\u011b se chceme vyv\u00edjet. A n\u011bkdy je pot\u0159eba aktualizovat data v tabulce, tedy prov\u00e9st aktualizaci v r\u00e1mci na\u0161\u00ed migrace pod nov\u00fd funkce, kter\u00e9 zav\u00e1d\u00edme v r\u00e1mci na\u0161eho rozvoje. <\/li>\n<li>Star\u00fd form\u00e1t dat n\u00e1m nevyhovuje. P\u0159edstavme si, \u017ee se nyn\u00ed obr\u00e1t\u00edme na druhou tabulku, kde m\u00e1m operace s t\u011bmito \u00fa\u010dty. A dejme tomu, \u017ee byly v rublech, ale rozhodli jsme se zv\u00fd\u0161it p\u0159esnost a pracovat v kopejk\u00e1ch. A k tomu pot\u0159ebujeme prov\u00e9st aktualizaci: pole se sou\u010dtem operace vyn\u00e1sob\u00edme sto. <\/li>\n<li>V modern\u00edm sv\u011bt\u011b pou\u017e\u00edv\u00e1me automatizovan\u00e9 prost\u0159edky pro spr\u00e1vu verz\u00ed datab\u00e1ze. P\u0159edstavme si, <noindex><a rel=\"nofollow\" href=\"https:\/\/www.liquibase.org\/\">Liquibase<\/a><\/noindex>. Zapisujeme sem na\u0161i migraci. Testujeme ji na na\u0161\u00ed testovac\u00ed datab\u00e1zi. V\u0161e v po\u0159\u00e1dku. Aktualizace prob\u00edh\u00e1. Na n\u011bjakou dobu blokuje provoz, ale dost\u00e1v\u00e1me tak aktualizovan\u00e1 data. A m\u016f\u017eeme spustit novou funkcionalitu. V\u0161e jsme otestovali, zkontrolovali. V\u0161ichni potvrdili. <\/li>\n<li>Provedli jsme pl\u00e1novan\u00e9 pr\u00e1ce, provedli migraci. <\/li>\n<\/ul>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/9e046583f7b1723fc1eaf61a03900918.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Zde je migrace s aktualizac\u00ed p\u0159ed v\u00e1mi. Proto\u017ee se jedn\u00e1 o operace s \u00fa\u010dty, tabulka m\u011bla 15 GB. A proto\u017ee aktualizujeme ka\u017ed\u00fd \u0159\u00e1dek, zv\u011bt\u0161ili jsme tabulku p\u0159i aktualizaci na dvojn\u00e1sobek, proto\u017ee jsme p\u0159epsali ka\u017ed\u00fd \u0159\u00e1dek. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/06120c98ea43d0568045e71935c23592.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>B\u011bhem migrace jsme s touto tabulkou nemohli d\u011blat nic, proto\u017ee v\u0161echny dotazy na ni \u010dekaly v \u0159ad\u011b a \u010dekaly na dokon\u010den\u00ed t\u00e9to aktualizace. Ale zde chci upozornit na \u010d\u00edsla, kter\u00e1 jsou na vertik\u00e1ln\u00ed ose. Tedy m\u00e1me pr\u016fm\u011brnou dobu dotazu p\u0159ed migrac\u00ed kolem 5 milisekund a zat\u00ed\u017een\u00ed procesoru, po\u010det blokov\u00fdch operac\u00ed pro \u010dten\u00ed z disku je m\u00e9n\u011b ne\u017e 7,5. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/69a3ba4d1d0cec7d39284e09e9d3055b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Provedli jsme migraci a znovu jsme dostali probl\u00e9my. <\/p>\n<p><\/p>\n<p>Migrace prob\u011bhla \u00fasp\u011b\u0161n\u011b, ale:<\/p>\n<p><\/p>\n<ul>\n<li>Star\u00fd funk\u010dn\u00ed prvek za\u010dal trvat d\u00e9le. <\/li>\n<li>Tabulka znovu vzrostla na velikosti. <\/li>\n<li>Zat\u00ed\u017een\u00ed serveru bylo znovu vy\u0161\u0161\u00ed, ne\u017e bylo p\u0159edt\u00edm. <\/li>\n<li>A samoz\u0159ejm\u011b st\u00e1le si hrajeme s t\u00edm funk\u010dn\u00edm prvkem, kter\u00fd fungoval dob\u0159e, trochu jsme ho vylep\u0161ili. <\/li>\n<\/ul>\n<p><\/p>\n<p>A to je znovu bloat, kter\u00fd n\u00e1m znovu zt\u011b\u017euje \u017eivot. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/becd7cce3a1c8821c81c96257f41202b.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Zde demonstruji, \u017ee tabulka, jako v p\u0159edchoz\u00edch dvou p\u0159\u00edpadech, se nechyst\u00e1 vr\u00e1tit k p\u0159edchoz\u00edm velikostem. Pr\u016fm\u011brn\u00e9 zat\u00ed\u017een\u00ed serveru se zd\u00e1 b\u00fdt adekv\u00e1tn\u00ed. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4d703a60d43a3460bc3782d49865b690.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A pokud se pod\u00edv\u00e1me na tabulku s \u00fa\u010dty, vid\u00edme, \u017ee pr\u016fm\u011brn\u00e1 doba dotazu se zdvojn\u00e1sobila v\u016f\u010di t\u00e9to tabulce. Zat\u00ed\u017een\u00ed procesoru a po\u010det proch\u00e1zen\u00fdch \u0159\u00e1dk\u016f v pam\u011bti vzrostl nad 7,5, zat\u00edmco byl ni\u017e\u0161\u00ed. A vzrostl v p\u0159\u00edpad\u011b procesor\u016f dvojn\u00e1sobn\u011b, v p\u0159\u00edpad\u011b blokov\u00fdch operac\u00ed o 1,5kr\u00e1t, tedy do\u0161lo k degradaci v\u00fdkonu serveru. A jako d\u016fsledek \u2013 degradaci v\u00fdkonu na\u0161\u00ed aplikace. P\u0159itom po\u010det vol\u00e1n\u00ed z\u016fstal p\u0159ibli\u017en\u011b na stejn\u00e9 \u00farovni. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/80772afa0643b29f5314a2482c9dfe0d.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>A zde je hlavn\u00ed porozum\u011bt tomu, jak spr\u00e1vn\u011b prov\u00e1d\u011bt takov\u00e9 migrace. A ty je nutn\u00e9 prov\u00e1d\u011bt. Tyto migrace realizujeme docela pravideln\u011b.<\/p>\n<p><\/p>\n<ul>\n<li>Takov\u00e9 velk\u00e9 migrace nejsou prov\u00e1d\u011bny automaticky. V\u017edy by m\u011bly b\u00fdt pod kontrolou. <\/li>\n<li>Je pot\u0159ebn\u00fd dohled ze strany znal\u00e9 osoby. Pokud m\u00e1te v t\u00fdmu DBA, nechte to ud\u011blat DBA. To je jeho pr\u00e1ce. Pokud ne, m\u011bl by to ud\u011blat nejzku\u0161en\u011bj\u0161\u00ed \u010dlov\u011bk, kter\u00fd v\u00ed, jak pracovat s datab\u00e1zemi. <\/li>\n<li>Nov\u00e1 sch\u00e9ma datab\u00e1ze, i kdy\u017e aktualizujeme pouze jeden sloupec, v\u017edy p\u0159ipravujeme ve f\u00e1z\u00edch, tedy dop\u0159edu, ne\u017e bude nasazena nov\u00e1 verze aplikace:<\/li>\n<li>P\u0159id\u00e1vaj\u00ed se nov\u00e1 pole, do kter\u00fdch budeme zapisovat pr\u00e1v\u011b aktualizovan\u00e1 data. <\/li>\n<li>P\u0159esouv\u00e1me data ze star\u00e9ho pole do nov\u00e9ho pole po men\u0161\u00edch \u010d\u00e1stech. Pro\u010d to d\u011bl\u00e1me? Za prv\u00e9, v\u017edy kontrolujeme pr\u016fb\u011bh tohoto procesu. V\u00edme, \u017ee jsme p\u0159esunuli tolik a tolik blok\u016f a zb\u00fdv\u00e1 n\u00e1m tolik. <\/li>\n<li>A druh\u00fd pozitivn\u00ed efekt spo\u010d\u00edv\u00e1 v tom, \u017ee mezi ka\u017ed\u00fdm takov\u00fdm blokem uzav\u00edr\u00e1me transakci, otev\u00edr\u00e1me novou a to umo\u017e\u0148uje automatick\u00e9mu \u00faklidu pracovat s tabulkou a ozna\u010dit mrtv\u00e9 \u0159\u00e1dky k op\u011btovn\u00e9mu pou\u017eit\u00ed. <\/li>\n<li>Pro \u0159\u00e1dky, kter\u00e9 se objev\u00ed b\u011bhem pr\u00e1ce aplikace (na\u0161e star\u00e1 aplikace st\u00e1le b\u011b\u017e\u00ed), p\u0159id\u00e1v\u00e1me spou\u0161t\u011b\u010d, kter\u00fd zapisuje nov\u00e9 hodnoty do nov\u00fdch pol\u00ed. V na\u0161em p\u0159\u00edpad\u011b to znamen\u00e1 vyn\u00e1soben\u00ed sta star\u00e9 hodnoty. <\/li>\n<li>Pokud jsme \u00fapln\u011b ne\u00fastupn\u00ed a chceme to stejn\u00e9 pole, po dokon\u010den\u00ed v\u0161ech migrac\u00ed a p\u0159ed nasazen\u00edm nov\u00e9 verze aplikace jednodu\u0161e p\u0159ejmenujeme pole. Star\u00e9 na n\u011bjak\u00fd vymy\u0161len\u00fd n\u00e1zev a nov\u00e1 pole p\u0159ejmenujeme na star\u00e9. <\/li>\n<li>A a\u017e pot\u00e9 spust\u00edme novou verzi aplikace. <\/li>\n<\/ul>\n<p><\/p>\n<p>A p\u0159itom nez\u00edsk\u00e1me bloat a neda\u0159\u00ed se n\u00e1m v\u00fdkon. <\/p>\n<p><\/p>\n<p>A na tomto t\u0159et\u00edm p\u0159\u00edb\u011bhu kon\u010d\u00ed. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej 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>A te\u010f se pod\u00edv\u00e1me trochu podrobn\u011bji na n\u00e1stroje, kter\u00e9 jsem zm\u00ednil v prvn\u00ed historii. <\/p>\n<p><\/p>\n<p>Ne\u017e za\u010dnete hledat bloat, mus\u00edte ur\u010dit\u011b nainstalovat roz\u0161\u00ed\u0159en\u00ed. <noindex><a rel=\"nofollow\" href=\"https:\/\/www.postgresql.org\/docs\/current\/pgstattuple.html\">pgstattuple<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p>Abychom v\u00e1m neusnadnili vym\u00fd\u0161len\u00ed dotaz\u016f, v na\u0161\u00ed pr\u00e1ci jsme ji\u017e tyto dotazy napsali. M\u016f\u017eete je pou\u017e\u00edt. Zde jsou prezentov\u00e1ny dva dotazy. <\/p>\n<p><\/p>\n<ul>\n<li>Prvn\u00ed dotaz trv\u00e1 pom\u011brn\u011b dlouho, ale uk\u00e1\u017ee v\u00e1m p\u0159esn\u00e9 hodnoty bloat podle tabulky. <\/li>\n<li>Druh\u00fd dotaz je rychlej\u0161\u00ed a velmi efektivn\u00ed, kdy\u017e pot\u0159ebujete rychle vyhodnotit, zda existuje bloat nebo ne. A tak\u00e9 byste m\u011bli m\u00edt na pam\u011bti, \u017ee bloat v tabulce Postgres je v\u017edy. Je to vlastnost jeho modelu MVCC. <\/li>\n<li>A 20 % bloat je norm\u00e1ln\u00ed pro tabulky ve v\u011bt\u0161in\u011b p\u0159\u00edpad\u016f. T. j. nemus\u00edte se ob\u00e1vat a komprimovat tuto tabulku. <\/li>\n<\/ul>\n<p><\/p>\n<p>Jak identifikovat tabulky, kter\u00e9 n\u00e1m zbytn\u011bly, jsme si objasnili, a to, kdy\u017e zbytn\u011bly bezcenn\u00fdmi daty. <\/p>\n<p><\/p>\n<p>Te\u010f o tom, jak opravit bloat:<\/p>\n<p><\/p>\n<ul>\n<li>Pokud m\u00e1me malou tabulku a dobr\u00e9 disky, tj. s tabulkou do jednoho gigabytu je zcela mo\u017en\u00e9 pou\u017e\u00edt VACUUM FULL. Z\u00edsk\u00e1 na tabulku exkluzivn\u00ed z\u00e1mek na n\u011bkolik sekund a hotovo, rychle a efektivn\u011b v\u0161echno ud\u011bl\u00e1. Co d\u011bl\u00e1 VACUUM FULL? Z\u00edsk\u00e1 exkluzivn\u00ed z\u00e1mek na tabulku a p\u0159episuje \u017eiv\u00e9 \u0159\u00e1dky ze star\u00fdch tabulek do nov\u00e9 tabulky. A na konci je zam\u011bn\u00ed. Star\u00e9 soubory odstran\u00ed, nov\u00e9 vlo\u017e\u00ed m\u00edsto star\u00fdch. Ale po dobu sv\u00e9 \u010dinnosti z\u00edsk\u00e1v\u00e1 exkluzivn\u00ed z\u00e1mek na tabulku. To znamen\u00e1, \u017ee s touto tabulkou nic nebudete moci ud\u011blat: ani do n\u00ed zapisovat, ani ji \u010d\u00edst, ani ji m\u011bnit. A VACUUM FULL vy\u017eaduje dal\u0161\u00ed m\u00edsto na disku k z\u00e1pisu dat.<\/li>\n<li>Dal\u0161\u00ed n\u00e1stroj <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/reorg\/pg_repack\">pg_repack<\/a><\/noindex>. Podle sv\u00e9ho principu je velmi podobn\u00fd VACUUM FULL, proto\u017ee tak\u00e9 p\u0159episuje data ze star\u00fdch soubor\u016f do nov\u00fdch a m\u011bn\u00ed je v tabulce. Ale na za\u010d\u00e1tku sv\u00e9 \u010dinnosti nez\u00edsk\u00e1v\u00e1 exkluzivn\u00ed z\u00e1mek na tabulku, ale pouze v okam\u017eiku, kdy m\u00e1 ji\u017e p\u0159ipraven\u00e1 data k nahrazen\u00ed soubor\u016f. Po\u017eadavky na diskov\u00e9 zdroje jsou u n\u011bj podobn\u00e9 jako u VACUUM FULL. Pot\u0159ebujete dal\u0161\u00ed m\u00edsto na disku, co\u017e m\u016f\u017ee b\u00fdt n\u011bkdy kritick\u00e9, pokud m\u00e1te terabajtov\u00e9 tabulky. A je dost n\u00e1ro\u010dn\u00fd na procesor, proto\u017ee vykon\u00e1v\u00e1 aktivn\u00ed \u010dinnost s vstupy a v\u00fdstupy. <\/li>\n<li>T\u0159et\u00ed utilita je <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pgcompacttable\">pgcompacttable<\/a><\/noindex>. Je v\u00edce \u0161etrn\u00e1 k prost\u0159edk\u016fm, proto\u017ee funguje na trochu jin\u00fdch principech. Hlavn\u00ed podstatou pgcompacttable je to, \u017ee aktualizacemi v tabulce p\u0159esouv\u00e1 v\u0161echny \u017eiv\u00e9 \u0159\u00e1dky na za\u010d\u00e1tek tabulky. A pot\u00e9 spou\u0161t\u00ed vacuumb na t\u00e9to tabulce, proto\u017ee v\u00edme, \u017ee na za\u010d\u00e1tku m\u00e1me \u017eiv\u00e9 a na konci mrtv\u00e9 \u0159\u00e1dky. A vacuum u\u017e s\u00e1m ust\u0159ihne tento oc\u00e1sek, t. j. nevy\u017eaduje pro sebe p\u0159\u00edli\u0161 dal\u0161\u00ed diskov\u00fd prostor. A je tak\u00e9 mo\u017en\u00e9 jej omezit na prost\u0159edky. <\/li>\n<\/ul>\n<p><\/p>\n<p>Se n\u00e1stroji je v\u0161e. <\/p>\n<p><\/p>\n<p><img decoding=\"async\" alt=\"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloat v PostgreSQL. Andrej Salnikov\" src=\"\/wp-content\/uploads\/2020\/05\/4187915e54e54a0b85a342fe0280f78f.jpg\" style=\"display:block;margin: 0 auto;\" \/><\/p>\n<p><\/p>\n<p>Pokud v\u00e1m t\u00e9ma bloat p\u0159ipad\u00e1 zaj\u00edmav\u00e9 na podrobn\u011bj\u0161\u00ed prozkoum\u00e1n\u00ed, zde jsou n\u011bkter\u00e9 u\u017eite\u010dn\u00e9 odkazy:<\/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 to je prezentace m\u00e9ho kolegy. Je obecn\u011b o tom, kam se ztr\u00e1c\u00ed m\u00edsto v Postgres b\u011bhem jeho pr\u00e1ce a \u017eivota. A tam je velmi velk\u00fd a podrobn\u00fd technick\u00fd kus pro administr\u00e1tory datab\u00e1z\u00ed o bloat. <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pg-utils\">https:\/\/github.com\/dataegret\/pg-utils<\/a><\/noindex> \u2013 to je odkaz na na\u0161e repo, kde uchov\u00e1v\u00e1me spoustu u\u017eite\u010dn\u00fdch skript\u016f pro kontrolu stavu datab\u00e1ze. Tam m\u016f\u017eete naj\u00edt skripty na hled\u00e1n\u00ed bloat. <\/li>\n<li><noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/reorg\/pg_repack\">T\u0159et\u00ed<\/a><\/noindex> a <noindex><a rel=\"nofollow\" href=\"https:\/\/github.com\/dataegret\/pgcompacttable\">\u010dtvrt\u00fd<\/a><\/noindex> odkazy na n\u00e1stroje, kter\u00e9 v\u00e1m pomohou zmen\u0161it tabulky. <\/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 to je p\u0159\u00edsp\u011bvek m\u00e9ho kolegy. Tam se pom\u011brn\u011b v\u00e1\u017en\u011b a podrobn\u011b technicky zab\u00fdv\u00e1 bloat pr\u00e1v\u011b na \u00farovni bl\u00edzk\u00e9 administr\u00e1tor\u016fm. <\/li>\n<\/ul>\n<p><\/p>\n<p>Sna\u017eil jsem se sp\u00ed\u0161e uk\u00e1zat d\u011bsiv\u00fd obraz pro v\u00fdvoj\u00e1\u0159e, proto\u017ee jsou na\u0161imi p\u0159\u00edm\u00fdmi klienty datab\u00e1z\u00ed a m\u011bli by rozum\u011bt, k \u010demu a jak\u00e9 akce vedou. Douf\u00e1m, \u017ee se mi to povedlo. D\u011bkuji za pozornost!<\/p>\n<p><\/p>\n<p>Ot\u00e1zky<\/p>\n<p><\/p>\n<p><em>D\u011bkuji za prezentaci! Mluvili jste o tom, jak lze odhalit probl\u00e9my. Jak je lze p\u0159edch\u00e1zet? T. j. m\u011bl jsem situaci, kdy po\u017eadavky visely nejen kv\u016fli tomu, \u017ee se obracely na n\u011bjak\u00e9 extern\u00ed slu\u017eby. Byly to prost\u011b n\u011bjak\u00e9 \u0161\u00edlen\u00e9 joins. Byly tam n\u011bjak\u00e9 drobn\u00e9 ne\u0161kodn\u00e9 po\u017eadavky, kter\u00e9 visely cel\u00fd den, a pak za\u010daly d\u011blat n\u011bco podivn\u00e9ho. T. j. velmi to p\u0159ipom\u00edn\u00e1, co popisujete. Jak to sledovat? Sed\u011bt a neust\u00e1le se d\u00edvat, kter\u00fd po\u017eadavek vis\u00ed? Jak lze tomu p\u0159edch\u00e1zet?<\/em><\/p>\n<p><\/p>\n<p>V tomto p\u0159\u00edpad\u011b je to \u00fakol pro administr\u00e1tory va\u0161\u00ed spole\u010dnosti, nezbytn\u011b ne pro DBA.<\/p>\n<p><\/p>\n<p><em>Jsem administr\u00e1tor.<\/em><\/p>\n<p><\/p>\n<p>V PostgreSQL existuje takov\u00fd pohled, jako je pg_stat_activity, ve kter\u00e9m jsou uvedeny vis\u00edc\u00ed po\u017eadavky. A m\u016f\u017eete vid\u011bt, jak dlouho tam vis\u00ed.<\/p>\n<p><\/p>\n<p><em>M\u00e1m chodit ka\u017ed\u00fdch 5 minut a d\u00edvat se?<\/em><\/p>\n<p><\/p>\n<p>Nastavte cron a kontrolujte. Pokud m\u00e1te dlouh\u00fd dotaz, napi\u0161te e-mail a hotovo. T. j. nemus\u00edte se d\u00edvat na to, m\u016f\u017eete to automatizovat. P\u0159ijde v\u00e1m e-mail, na kter\u00fd reagujete. M\u016f\u017eete tak\u00e9 automaticky odpov\u011bd\u011bt.<\/p>\n<p><\/p>\n<p><em>Existuj\u00ed jasn\u00e9 d\u016fvody, pro\u010d se to d\u011bje?<\/em><\/p>\n<p><\/p>\n<p>N\u011bkter\u00e9 jsem vyjmenoval. Jin\u00e9 jsou slo\u017eit\u011bj\u0161\u00ed p\u0159\u00edklady. A tam m\u016f\u017ee b\u00fdt debata na dlouho.<\/p>\n<p><\/p>\n<p><em>D\u011bkuji za prezentaci! Cht\u011bl jsem se zeptat na n\u00e1stroj pg_repack. Pokud ned\u011bl\u00e1 exkluzivn\u00ed blokaci, tak...<\/em><\/p>\n<p><\/p>\n<p>D\u011bl\u00e1 exkluzivn\u00ed blokaci. <\/p>\n<p><\/p>\n<p>\u2026 <em>tak potenci\u00e1ln\u011b mohu ztratit data. Moje aplikace by v tu dobu nem\u011bla nic zapisovat?<\/em><\/p>\n<p><\/p>\n<p>Ne, v pohod\u011b pracuje s tabulkou, t. j. pg_repack nejprve p\u0159esune v\u0161echny aktivn\u00ed \u0159\u00e1dky, kter\u00e9 existuj\u00ed. Samoz\u0159ejm\u011b, \u017ee se do tabulky n\u011bco zapisuje. On prost\u011b ten ocas dot\u00e1hne. <\/p>\n<p><\/p>\n<p><em>T. j. nakonec to d\u011blal?<\/em><\/p>\n<p><\/p>\n<p>Na konci vezme exkluzivn\u00ed blokaci, aby zam\u011bnil tyto soubory. <\/p>\n<p><\/p>\n<p><em>Bude to rychlej\u0161\u00ed ne\u017e VACUUM FULL?<\/em><\/p>\n<p><\/p>\n<p>VACUUM FULL, jak za\u010dalo, ihned vzalo exkluzivn\u00ed blokaci. A dokud v\u0161echno nedokon\u010d\u00ed, tak ji nepust\u00ed. A pg_repack bere exkluzivn\u00ed blokaci jen v okam\u017eiku v\u00fdm\u011bny soubor\u016f. V tuto chv\u00edli tam nic nezap\u00ed\u0161ete, ale data se neztrat\u00ed, v\u0161echno bude v po\u0159\u00e1dku. <\/p>\n<p><\/p>\n<p><em>Dobr\u00fd den! Hovo\u0159il jste o pr\u00e1ci autovakuumu. Byl tam graf s \u010derven\u00fdmi, \u017elut\u00fdmi a zelen\u00fdmi bu\u0148kami z\u00e1znamu. T. j. \u017elut\u00e9 \u2013 ozna\u010dil jako smazan\u00e9. A tud\u00ed\u017e do nich m\u016f\u017eeme zapisovat nov\u00e9?<\/em><\/p>\n<p><\/p>\n<p>Ano. Postgres nema\u017ee \u0159\u00e1dky. M\u00e1 to svoji specifiku. Pokud aktualizujeme \u0159\u00e1dek, ozna\u010d\u00edme ten star\u00fd jako smazan\u00fd. Tam se dost\u00e1v\u00e1 id transakce, kter\u00e1 tento \u0159\u00e1dek zm\u011bnila, a zapisujeme nov\u00fd \u0159\u00e1dek. A m\u00e1me sezen\u00ed, kter\u00e1 je potenci\u00e1ln\u011b mohou \u010d\u00edst. V n\u011bjak\u00e9m okam\u017eiku se ji\u017e st\u00e1vaj\u00ed zcela star\u00fdmi. A podstata pr\u00e1ce autovakuumu spo\u010d\u00edv\u00e1 v tom, \u017ee proch\u00e1z\u00ed t\u011bmito \u0159\u00e1dky a ozna\u010duje je jako nepot\u0159ebn\u00e9. A tam lze p\u0159epsat data. <\/p>\n<p><\/p>\n<p><em>Rozum\u00edm. Ale ot\u00e1zka je tro\u0161ku jin\u00e1. Nedokon\u010dil jsem. P\u0159edpokl\u00e1dejme, \u017ee m\u00e1me tabulku. V n\u00ed jsou pole prom\u011bnn\u00e9 velikosti. A pokud se pokus\u00edm vlo\u017eit n\u011bco nov\u00e9ho, m\u016f\u017ee to prost\u011b do star\u00e9 bu\u0148ky nevej\u00edt.<\/em> <\/p>\n<p><\/p>\n<p>Ne, tam se stejn\u011b aktualizuje cel\u00fd \u0159\u00e1dek. V Postgres jsou dva modely ukl\u00e1d\u00e1n\u00ed dat. Rozli\u0161uje podle typu dat. N\u011bkter\u00e1 data se ukl\u00e1daj\u00ed p\u0159\u00edmo v tabulce, zat\u00edmco jin\u00e1 jsou je\u0161t\u011b tos-data. To jsou velk\u00e9 objemy dat: text, json. Ta se ukl\u00e1daj\u00ed v odd\u011blen\u00fdch tabulk\u00e1ch. A u t\u011bchto tabulek plat\u00ed stejn\u00fd probl\u00e9m s bloat, t. j. v\u0161echno stejn\u00e9. Jen jsou vy\u010dlen\u011bny zvl\u00e1\u0161\u0165. <\/p>\n<p><\/p>\n<p><em>D\u011bkuji za p\u0159\u00edsp\u011bvek! Jak p\u0159ijateln\u00e9 je pou\u017e\u00edvat statement timeout k omezen\u00ed doby trv\u00e1n\u00ed dotaz\u016f?<\/em><\/p>\n<p><\/p>\n<p>Velmi p\u0159ijateln\u00e9. Pou\u017e\u00edv\u00e1me to v\u0161ude. A proto\u017ee nem\u00e1me vlastn\u00ed slu\u017eby, poskytujeme vzd\u00e1lenou podporu, tak\u017ee m\u00e1me pom\u011brn\u011b rozmanit\u00e9 klienty. A v\u0161ichni jsou t\u00edmto spokojeni. T. j. m\u00e1me \u00fakoly v cron, kter\u00e9 kontroluj\u00ed. Jen se s klientem dohodneme na dob\u011b trv\u00e1n\u00ed sezen\u00ed, co\u017e m\u016f\u017ee b\u00fdt minuta nebo deset minut. Z\u00e1le\u017e\u00ed to na z\u00e1t\u011b\u017ei datab\u00e1ze a jej\u00edm c\u00edli. Ale v\u0161ude pou\u017e\u00edv\u00e1me pg_stat_activity.<\/p>\n<p><\/p>\n<p><em>D\u011bkuji za p\u0159\u00edsp\u011bvek! Sna\u017e\u00edm se aplikovat v\u00e1\u0161 p\u0159\u00edsp\u011bvek na sv\u00e9 aplikace. A vypad\u00e1 to, \u017ee v\u0161ude zahajujeme transakci a v\u0161ude ji jasn\u011b ukon\u010dujeme. Pokud dojde k n\u011bjak\u00e9 v\u00fdjimce, tak se stejn\u011b provede rollback. A tady jsem se zamyslel. Mohla by p\u0159ece transakce za\u010d\u00edt nejasn\u011b. To je asi n\u00e1pov\u011bda pro d\u00edvku. Pokud ud\u011bl\u00e1m pouze aktualizaci z\u00e1znamu, transakce se v PostgreSQL spust\u00ed a dokon\u010d\u00ed se a\u017e p\u0159i odpojen\u00ed spojen\u00ed?<\/em><\/p>\n<p><\/p>\n<p>Pokud mluv\u00edte te\u010f o \u00farovni aplikace, z\u00e1le\u017e\u00ed to na tom driveru, kter\u00fd pou\u017e\u00edv\u00e1te, a na tom ORM, kter\u00e9 se pou\u017e\u00edv\u00e1. Tam je toho spousta nastaven\u00ed. Jestli\u017ee m\u00e1te zapnutou volbu auto commit on, transakce se spust\u00ed a hned se uzav\u0159e.<\/p>\n<p><\/p>\n<p><em>T. j. uzav\u0159e se hned po aktualizaci?<\/em><\/p>\n<p><\/p>\n<p>Z\u00e1le\u017e\u00ed to na nastaven\u00ed. Uvedl jsem jedno nastaven\u00ed. To je auto commit on. To je docela b\u011b\u017en\u00e9. Pokud je zapnuto, tak se transakce otev\u0159e a hned uzav\u0159e. Pokud jste v\u00fdslovn\u011b ne\u0159ekl \u201estart transaction\u201c a \u201eend transaction\u201c, ale jednodu\u0161e spustil dotaz v relaci. <\/p>\n<p><\/p>\n<p><em>Dobr\u00fd den! D\u011bkuji za p\u0159\u00edsp\u011bvek! P\u0159edstavme si, \u017ee m\u00e1me datab\u00e1zi, kter\u00e1 se zv\u011bt\u0161uje a na serveru doch\u00e1z\u00ed m\u00edsto. Existuj\u00ed n\u011bjak\u00e9 n\u00e1stroje, jak situaci vy\u0159e\u0161it?<\/em> <\/p>\n<p><\/p>\n<p>M\u00edsto na serveru je pot\u0159eba spr\u00e1vn\u011b monitorovat. <\/p>\n<p><\/p>\n<p><em>Nap\u0159\u00edklad, DBA \u0161el na \u010daj, byl na dovolen\u00e9 atd.<\/em><\/p>\n<p><\/p>\n<p>P\u0159i vytv\u00e1\u0159en\u00ed souborov\u00e9ho syst\u00e9mu se v\u017edy vytvo\u0159\u00ed n\u011bjak\u00fd rezervn\u00ed prostor, kam se data nep\u00ed\u0161\u00ed. <\/p>\n<p><\/p>\n<p><em>A co kdy\u017e \u00fapln\u011b na nulu?<\/em><\/p>\n<p><\/p>\n<p>Tomu se \u0159\u00edk\u00e1 rezervovan\u00fd prostor, tedy ho lze uvolnit a v z\u00e1vislosti na tom, jak velk\u00fd byl vytvo\u0159en, m\u016f\u017eete z\u00edskat voln\u00e9 m\u00edsto. Standardn\u011b nev\u00edm, kolik tam je. A v jin\u00e9m p\u0159\u00edpad\u011b by bylo t\u0159eba dodat disky, aby bylo m\u00edsto pro proveden\u00ed obnovovac\u00ed operace. M\u016f\u017eete odstranit n\u011bjakou tabulku, kter\u00e1 v\u00e1m jist\u011b nen\u00ed pot\u0159eba. <\/p>\n<p><\/p>\n<p><em>Nen\u00ed \u017e\u00e1dn\u00fd jin\u00fd n\u00e1stroj?<\/em><\/p>\n<p><\/p>\n<p>To je v\u017edy manu\u00e1ln\u00ed pr\u00e1ce. A na m\u00edst\u011b se rozhoduje, co by bylo lep\u0161\u00ed ud\u011blat, proto\u017ee jsou kritick\u00e1 a nekritick\u00e1 data. A pro ka\u017edou datab\u00e1zi a aplikaci, kter\u00e1 s n\u00ed pracuje, to z\u00e1vis\u00ed na podnik\u00e1n\u00ed. V\u017edy se rozhoduje na m\u00edst\u011b. <\/p>\n<p><\/p>\n<p><em>D\u011bkuji za prezentaci! M\u00e1m dv\u011b ot\u00e1zky. Za prv\u00e9, ukazovali jste slidy, kde bylo uvedeno, \u017ee v p\u0159\u00edpad\u011b zablokovan\u00fdch transakc\u00ed roste jak objem tabulkov\u00e9ho prostoru, tak velikost indexu. A d\u00e1le ve zpr\u00e1v\u011b bylo spoustu utilit, kter\u00e9 bal\u00ed tabulky. A co s indexem?<\/em><\/p>\n<p><\/p>\n<p>I ty se bal\u00ed. <\/p>\n<p><\/p>\n<p><em>Ale vacuum se indexu net\u00fdk\u00e1?<\/em><\/p>\n<p><\/p>\n<p>N\u011bkter\u00e9 pracuj\u00ed s indexem. Nap\u0159\u00edklad pg_rapack, pgcompacttable. Vacuum znovu vytv\u00e1\u0159\u00ed indexy, t\u00fdk\u00e1 se jich. Hlavn\u00ed my\u0161lenka VACUUM FULL je p\u0159epsat v\u0161echno, tedy pracuje se v\u0161emi. <\/p>\n<p><\/p>\n<p><em>A druh\u00e1 ot\u00e1zka. Nech\u00e1pu, pro\u010d se zpr\u00e1vy na replik\u00e1ch tak siln\u011b z\u00e1vis\u00ed na samotn\u00e9 replikaci. Zd\u00e1lo se mi, \u017ee zpr\u00e1vy jsou \u010dten\u00ed, a replikace je z\u00e1pis.<\/em> <\/p>\n<p><\/p>\n<p>V \u010dem vznik\u00e1 konflikt replikace? M\u00e1me Master, na kter\u00e9m prob\u00edhaj\u00ed procesy. Prob\u00edh\u00e1 autovakuum. Co na autovakuum vlastn\u011b d\u011bl\u00e1? Odstra\u0148uje n\u011bkter\u00e9 star\u00e9 \u0159\u00e1dky. Pokud v t\u00e9to dob\u011b na replikaci prob\u00edh\u00e1 dotaz, kter\u00fd \u010dte tyto star\u00e9 \u0159\u00e1dky, a na Masteru do\u0161lo k situaci, kdy autovakuum ozna\u010dilo tyto \u0159\u00e1dky jako mo\u017en\u00e9 pro p\u0159eps\u00e1n\u00ed, p\u0159epsali jsme je. A m\u00e1me p\u0159\u00edchoz\u00ed bal\u00edk dat, kdy bychom m\u011bli p\u0159epsat ty \u0159\u00e1dky, kter\u00e9 po\u017eaduje dotaz na replikaci, proces replikace vy\u010dk\u00e1 na nastaven\u00fd \u010dasov\u00fd limit. A pak PostgreSQL rozhodne, co je pro n\u011bj d\u016fle\u017eit\u011bj\u0161\u00ed. A replikace je pro n\u011bj d\u016fle\u017eit\u011bj\u0161\u00ed ne\u017e dotaz, a tak dotaz zahod\u00ed, aby mohl prov\u00e9st tyto zm\u011bny na replikaci. <\/p>\n<p><\/p>\n<p><em>Andrei, m\u00e1m ot\u00e1zku. Tyto skv\u011bl\u00e9 grafy, kter\u00e9 jste ukazovali b\u011bhem prezentace, jsou v\u00fdsledkem pr\u00e1ce n\u011bjak\u00e9ho va\u0161eho n\u00e1stroje? Jak byly grafy vytvo\u0159eny?<\/em><\/p>\n<p><\/p>\n<p>To je slu\u017eba <noindex><a rel=\"nofollow\" href=\"https:\/\/okmeter.io\/\">Okmeter<\/a><\/noindex>.<\/p>\n<p><\/p>\n<p><em>Je to komer\u010dn\u00ed produkt?<\/em><\/p>\n<p><\/p>\n<p>Ano. Je to komer\u010dn\u00ed produkt.<\/p>\n<p>Zdroj: <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.3 - 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\/cs\/blog\/administrirovanie\/tipovye-oshibki-v-prilozheniyah-kotorye-vedut-k-bloat-v-postgresql-andrej-salnikov\" \/>\n\t\t<meta name=\"generator\" content=\"All in One SEO (AIOSEO) 5.0.3\" \/>\n\t\t<meta property=\"og:locale\" content=\"cs_CZ\" \/>\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\/cs\/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\udd47 Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloatu v postgresql. Andrej Salnikov | ProHoster","description":"Navrhuji se sezn\u00e1mit s v\u00fdkladem zpr\u00e1vy z po\u010d\u00e1tku roku 2016 od Andreje Salnikova \"Typick\u00e9 chyby v aplikac\u00edch, kter\u00e9 vedou k bloatu v postgresql\" V t\u00e9to zpr\u00e1v\u011b podrobn\u011b proberu hlavn\u00ed body.","canonical_url":"https:\/\/prohoster.info\/cs\/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":"cs_CZ","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\/cs\/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\/cs\/wp-json\/wp\/v2\/posts\/81089","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/comments?post=81089"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/posts\/81089\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media\/81090"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/media?parent=81089"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/categories?post=81089"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/cs\/wp-json\/wp\/v2\/tags?post=81089"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}