Fórum Root.cz

Hlavní témata => Server => Téma založeno: FKoudelka 05. 05. 2020, 13:56:05

Název: PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: FKoudelka 05. 05. 2020, 13:56:05
Ahoj, vyznáte se v tom někdo ?
Ve struktuře DB na disku a jak se doptat, co se kam píše …
Potřebuju zjistit, proč mi jeden segment na disku bobtná nade všechny meze.
Nevím o této DB nic.
Dík
Název: Re:Postgresql DB na linuxu
Přispěvatel: luvar 05. 05. 2020, 15:20:19
Je mnoho možných vysvetlení. Spúšťa sa aj autovacuum? Updatujú sa často záznamy?

V každom prípade pomôcť vniesť trochu svetla by mohol prvý, či druhý, selekt:

Kód: [Vybrat]
SELECT table_name
    FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
    AND table_schema NOT IN
        ('pg_catalog', 'information_schema');

SELECT column_name
    FROM information_schema.columns
WHERE table_name = 'meno Vasej tabuľôčky';

Nejaký "polopatistický" úvod k low level veciam je tuná: http://rachbelaid.com/introduction-to-postgres-physical-storage/ Odporúčam ako čítanie ku káve a v prípade záujmu sa pozrieť na oficiálnu dokumentáciu priamo od výrobcu :)
Název: Re:Postgresql DB na linuxu
Přispěvatel: Ondrej Nemecek 05. 05. 2020, 15:50:46
Zkontroloval bych autovacuum, velikost indexů a korektní ukončování transakcí.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: FKoudelka 05. 05. 2020, 23:14:03
Díky moc, vypadá to, že stejně nedostanu z vendora heslo do DB :-(
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: kmarty 06. 05. 2020, 00:22:28
Na to ho nepotrebujes. By default ma do ni pristup juzr “postgres”, proste jen tim ze jim ses (sudo nebo su), pac ma autentizaci “ident”, resp. “peer”. Vic v pg_hba.conf.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: geekyfreak 06. 05. 2020, 01:31:34
Postgres boptna, protoze se chova k prostoru na disku podobne jako Java k pameti, "naalokuje co muze". Resp. kdyz budete mit tabulku, kde mate hodne UPDATE a DELETE tak se zachovavaji transakce i data, postgres si oznaci data jako smazane, ale neuvolni misto na disku pro system. Cili to pak vypada, ze vam vytece z disku, ale on nevytece, v pripade, ze zacne mit kriticky malo mista na disku, 1-2GB tak teprve potom zacne tyhle "sektory" pouzivat znovu, sam pro sebe. Autovacuum vam nepomuze, resp. ted nevim jestli je to od verze 11 nebo 12, ale doporucuju udelat FULL VACUUM, bohuzel to ma exklusivni locky, takze opatrne (u 12 ma prikaz vacuumdb moznost nezamikat tabulky - viz manual). Doporucuju vybrat "top 10" nejvetsich databazi, seradit vzestupne dle velikosti, per databazi pak udelat to same s tabulkama a pustit full vacuum per tabulka. Kdyz zacnete s mensima, uvolni to misto na vacuuming vetsich tabulek. Pocitejte, ze na tabulky 1GB je treba 2GB na disku na full vacuum, proto zacinat od mensich, uvolni misto pro vetsi.

Jak na to vsechno viz dokumentace: https://wiki.postgresql.org/wiki/Disk_Usage
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: Pavel Stěhule 06. 05. 2020, 06:35:59
Postgres boptna, protoze se chova k prostoru na disku podobne jako Java k pameti, "naalokuje co muze". Resp. kdyz budete mit tabulku, kde mate hodne UPDATE a DELETE tak se zachovavaji transakce i data, postgres si oznaci data jako smazane, ale neuvolni misto na disku pro system. Cili to pak vypada, ze vam vytece z disku, ale on nevytece, v pripade, ze zacne mit kriticky malo mista na disku, 1-2GB tak teprve potom zacne tyhle "sektory" pouzivat znovu, sam pro sebe. Autovacuum vam nepomuze, resp. ted nevim jestli je to od verze 11 nebo 12, ale doporucuju udelat FULL VACUUM, bohuzel to ma exklusivni locky, takze opatrne (u 12 ma prikaz vacuumdb moznost nezamikat tabulky - viz manual). Doporucuju vybrat "top 10" nejvetsich databazi, seradit vzestupne dle velikosti, per databazi pak udelat to same s tabulkama a pustit full vacuum per tabulka. Kdyz zacnete s mensima, uvolni to misto na vacuuming vetsich tabulek. Pocitejte, ze na tabulky 1GB je treba 2GB na disku na full vacuum, proto zacinat od mensich, uvolni misto pro vetsi.

Jak na to vsechno viz dokumentace: https://wiki.postgresql.org/wiki/Disk_Usage

Takto se to samozřejmě nechová. V detailech jste úplně mimo. Nic ve zlém, ale pokud něčemu nerozumím, tak je lepší mlčet. Bohužel internet je zaplněný špatnými dobrými radami.

Bez přihlášení do db žádnou administraci neuděláte. Lze něco detekovat na souborovém systému, ale je to dost pracnější, a navíc hloupost - pokud zákazník si databázi neadministruje sám, a ani nezplnomocní k tomu Vás, tak to jednou musí dopadnout špatně a je to signál, že něco ve vztahu vy, váš zákazník je špatně.

Podívejte se do tabulky pg_stat_activity jestli nenajdete neuzavřenou transakci - state: "idle in transaction". To blokuje VACUUM, a pokud VACCUUM neaktualizuje mapu volného místa v datovém souboru, tak engine neví, že tam volné místo a zvětšuje soubor. Zkontrolujte log - dost často se jedná o aplikační chybu - čistící funkce padají na syntaktických chybách, timeoutech, ..  Podívejte se jestli nějaké tabulky nejsou přefouklé https://wiki.postgresql.org/wiki/Show_database_bloat - pak aplikujte VACUUM FULL. Zkontrolujte jestli si zákazník nevypnul autovacuum.

Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: Ondrej Nemecek 06. 05. 2020, 14:10:10
Postgres boptna, protoze se chova k prostoru na disku podobne jako Java k pameti, "naalokuje co muze".

Takto se to samozřejmě nechová. V detailech jste úplně mimo. Nic ve zlém, ale pokud něčemu nerozumím, tak je lepší mlčet. Bohužel internet je zaplněný špatnými dobrými radami.

Bez přihlášení do db žádnou administraci neuděláte. Lze něco detekovat na souborovém systému, ale je to dost pracnější, a navíc hloupost - pokud zákazník si databázi neadministruje sám, a ani nezplnomocní k tomu Vás, tak to jednou musí dopadnout špatně a je to signál, že něco ve vztahu vy, váš zákazník je špatně.

Podívejte se do tabulky pg_stat_activity jestli nenajdete neuzavřenou transakci - state: "idle in transaction". To blokuje VACUUM, a pokud VACCUUM neaktualizuje mapu volného místa v datovém souboru, tak engine neví, že tam volné místo a zvětšuje soubor. Zkontrolujte log - dost často se jedná o aplikační chybu - čistící funkce padají na syntaktických chybách, timeoutech, ..  Podívejte se jestli nějaké tabulky nejsou přefouklé https://wiki.postgresql.org/wiki/Show_database_bloat - pak aplikujte VACUUM FULL. Zkontrolujte jestli si zákazník nevypnul autovacuum.

+1  :)

Můžu jen dotaz, zda je důvod spouštět ručně FULL VACUUM když mám zapnuté autovacuum?
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: Pavel Stěhule 06. 05. 2020, 16:05:37

+1  :)

Můžu jen dotaz, zda je důvod spouštět ručně FULL VACUUM když mám zapnuté autovacuum?

Těch důvodů může být více - mohou vám neplánovaně narůst tabulky, které pak chcete promazat, a u kterých nechcete riskovat fullscan, který by zbytečně četl 100násobek balastu - aplikační chyby, dos útok, promazání logů, .. autovacuum, připadně samotné vacuum může být z několika důvodů neefektivní - např. dlouho otevřená transakce. VACUUM FULL jako side efekt je ochranou proti přetečení transakčních ID (což by mi autovacuum zajistilo taky, ale to může VACUUM FREEZE pustit v nevhodnou dobu), a sekundární side efekt je reindexace. VACUUM FULL vám může posloužit i jako kontrola čitelnosti integrity databáze.

VACUUM FULL určitě není rutinní záležitost - ale jednou do roka neuškodí. A když mám nějaké malé databáze, na kterých VACUUM FULL běží do minuty, a mohu si dovolit risk exkluzivního zámku, tak pokud pustíte VACUUM FULL, a následně VACUUM ANALYZE, tak jste dostal databázi do výchozího téměř ideálního stavu a není tam co dál moc administrovat, řešit. Jednou, 2x do roka jednoduchá a pro drtivou většinu případů dostatečná údržba databáze.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: FKoudelka 06. 05. 2020, 16:37:39
Na to ho nepotrebujes. By default ma do ni pristup juzr “postgres”, proste jen tim ze jim ses (sudo nebo su), pac ma autentizaci “ident”, resp. “peer”. Vic v pg_hba.conf.
Diky. Na co že to heslo nepotřebuju ?
pg_hba.conf mam takto:
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    cpm             all             127.0.0.1/32            password
local   cpm             all                                 password
host    postgres             all             127.0.0.1/32            password
local   postgres             all                                 password
host    monitoring             all             127.0.0.1/32            password
local   monitoring             all                                 password
local   template1             all                                 password
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: FKoudelka 06. 05. 2020, 16:39:48
Postgres boptna, protoze se chova k prostoru na disku podobne jako Java k pameti, "naalokuje co muze". Resp. kdyz budete mit tabulku, kde mate hodne UPDATE a DELETE tak se zachovavaji transakce i data, postgres si oznaci data jako smazane, ale neuvolni misto na disku pro system. Cili to pak vypada, ze vam vytece z disku, ale on nevytece, v pripade, ze zacne mit kriticky malo mista na disku, 1-2GB tak teprve potom zacne tyhle "sektory" pouzivat znovu, sam pro sebe. Autovacuum vam nepomuze, resp. ted nevim jestli je to od verze 11 nebo 12, ale doporucuju udelat FULL VACUUM, bohuzel to ma exklusivni locky, takze opatrne (u 12 ma prikaz vacuumdb moznost nezamikat tabulky - viz manual). Doporucuju vybrat "top 10" nejvetsich databazi, seradit vzestupne dle velikosti, per databazi pak udelat to same s tabulkama a pustit full vacuum per tabulka. Kdyz zacnete s mensima, uvolni to misto na vacuuming vetsich tabulek. Pocitejte, ze na tabulky 1GB je treba 2GB na disku na full vacuum, proto zacinat od mensich, uvolni misto pro vetsi.

Jak na to vsechno viz dokumentace: https://wiki.postgresql.org/wiki/Disk_Usage

Takto se to samozřejmě nechová. V detailech jste úplně mimo. Nic ve zlém, ale pokud něčemu nerozumím, tak je lepší mlčet. Bohužel internet je zaplněný špatnými dobrými radami.

Bez přihlášení do db žádnou administraci neuděláte. Lze něco detekovat na souborovém systému, ale je to dost pracnější, a navíc hloupost - pokud zákazník si databázi neadministruje sám, a ani nezplnomocní k tomu Vás, tak to jednou musí dopadnout špatně a je to signál, že něco ve vztahu vy, váš zákazník je špatně.

Podívejte se do tabulky pg_stat_activity jestli nenajdete neuzavřenou transakci - state: "idle in transaction". To blokuje VACUUM, a pokud VACCUUM neaktualizuje mapu volného místa v datovém souboru, tak engine neví, že tam volné místo a zvětšuje soubor. Zkontrolujte log - dost často se jedná o aplikační chybu - čistící funkce padají na syntaktických chybách, timeoutech, ..  Podívejte se jestli nějaké tabulky nejsou přefouklé https://wiki.postgresql.org/wiki/Show_database_bloat - pak aplikujte VACUUM FULL. Zkontrolujte jestli si zákazník nevypnul autovacuum.
Diky moc. Jak prectu pg_stat_activity ? Nemam heslo
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: Logik 06. 05. 2020, 16:53:55
Zazálohuj si pg_hba.conf, změň ho tak, aby postgres mohl bez hesla
Kód: [Vybrat]
local   all         postgres                          ident

a reloaduj konfiguraci:
Kód: [Vybrat]
user#  sudo su postgres
postgres#  pg_ctl reload

 (pokud to nezabere, tak restartuj postgres).
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: FKoudelka 06. 05. 2020, 16:57:45
Na to ho nepotrebujes. By default ma do ni pristup juzr “postgres”, proste jen tim ze jim ses (sudo nebo su), pac ma autentizaci “ident”, resp. “peer”. Vic v pg_hba.conf.
Diky. Na co že to heslo nepotřebuju ?
pg_hba.conf mam takto:
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    cpm             all             127.0.0.1/32            password
local   cpm             all                                 password
host    postgres             all             127.0.0.1/32            password
local   postgres             all                                 password
host    monitoring             all             127.0.0.1/32            password
local   monitoring             all                                 password
local   template1             all                                 password

Jsem tam :-)
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: FKoudelka 06. 05. 2020, 17:52:25

+1  :)

Můžu jen dotaz, zda je důvod spouštět ručně FULL VACUUM když mám zapnuté autovacuum?

Těch důvodů může být více - mohou vám neplánovaně narůst tabulky, které pak chcete promazat, a u kterých nechcete riskovat fullscan, který by zbytečně četl 100násobek balastu - aplikační chyby, dos útok, promazání logů, .. autovacuum, připadně samotné vacuum může být z několika důvodů neefektivní - např. dlouho otevřená transakce. VACUUM FULL jako side efekt je ochranou proti přetečení transakčních ID (což by mi autovacuum zajistilo taky, ale to může VACUUM FREEZE pustit v nevhodnou dobu), a sekundární side efekt je reindexace. VACUUM FULL vám může posloužit i jako kontrola čitelnosti integrity databáze.

VACUUM FULL určitě není rutinní záležitost - ale jednou do roka neuškodí. A když mám nějaké malé databáze, na kterých VACUUM FULL běží do minuty, a mohu si dovolit risk exkluzivního zámku, tak pokud pustíte VACUUM FULL, a následně VACUUM ANALYZE, tak jste dostal databázi do výchozího téměř ideálního stavu a není tam co dál moc administrovat, řešit. Jednou, 2x do roka jednoduchá a pro drtivou většinu případů dostatečná údržba databáze.
Dopracoval jsm se ke zjištění, že ta mrcha velká je table pg_largeobject. Co dál ? Cílem je zjistit, kdo do ní píše, třeba podle toho co je tam zapsáno. Abych věděl, která aplikace se zbláznila. P.S. DB je moje, ne zákazníka, ale je produkční, momentálně standby a mám zálohu.
Díky
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: FKoudelka 06. 05. 2020, 18:42:36

+1  :)

Můžu jen dotaz, zda je důvod spouštět ručně FULL VACUUM když mám zapnuté autovacuum?

Těch důvodů může být více - mohou vám neplánovaně narůst tabulky, které pak chcete promazat, a u kterých nechcete riskovat fullscan, který by zbytečně četl 100násobek balastu - aplikační chyby, dos útok, promazání logů, .. autovacuum, připadně samotné vacuum může být z několika důvodů neefektivní - např. dlouho otevřená transakce. VACUUM FULL jako side efekt je ochranou proti přetečení transakčních ID (což by mi autovacuum zajistilo taky, ale to může VACUUM FREEZE pustit v nevhodnou dobu), a sekundární side efekt je reindexace. VACUUM FULL vám může posloužit i jako kontrola čitelnosti integrity databáze.

VACUUM FULL určitě není rutinní záležitost - ale jednou do roka neuškodí. A když mám nějaké malé databáze, na kterých VACUUM FULL běží do minuty, a mohu si dovolit risk exkluzivního zámku, tak pokud pustíte VACUUM FULL, a následně VACUUM ANALYZE, tak jste dostal databázi do výchozího téměř ideálního stavu a není tam co dál moc administrovat, řešit. Jednou, 2x do roka jednoduchá a pro drtivou většinu případů dostatečná údržba databáze.
Už jsem blízko vakuování, jen dvě otázky:
- má smysl předtím pustit vacuum analyze ?
- tabulka má velikost 14GB, ale místo v / je jen 3.8GB (proto to celé dělám) Dojede to do konce ? pokud ne, vadí to ?
Ještě jednou moc díky
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: Pavel Stěhule 06. 05. 2020, 18:53:52

Už jsem blízko vakuování, jen dvě otázky:
- má smysl předtím pustit vacuum analyze ?
- tabulka má velikost 14GB, ale místo v / je jen 3.8GB (proto to celé dělám) Dojede to do konce ? pokud ne, vadí to ?
Ještě jednou moc díky

Ne, proč? Pokud má bloat faktor vysoký, tak se to může povést, pokud ne, tak se to nepovede a spadne vám to na nedostatek místa na disku. U toho nedostatku místa na disku je relativně malá šance na poškození databáze, pokud byste tam měl nějakou starší neaktualizovanou verzi PG a zároveň dělal další operace. Pokud má tabulka 14GB a místo na disku je 3.8GB, a nic moc jste tam nepromazal (obsah dat je 12-14GB), tak to moc smysl nedává. Pokud budete mít stejně nějakou odstávku, tak můžete podropovat nějaké indexy, tím si uvolníte prostor na disku, a pak je znovu vytvoříte.

A tak si říkám, jak pak asi zálohujete.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: FKoudelka 06. 05. 2020, 20:00:14

Už jsem blízko vakuování, jen dvě otázky:
- má smysl předtím pustit vacuum analyze ?
- tabulka má velikost 14GB, ale místo v / je jen 3.8GB (proto to celé dělám) Dojede to do konce ? pokud ne, vadí to ?
Ještě jednou moc díky

Ne, proč? Pokud má bloat faktor vysoký, tak se to může povést, pokud ne, tak se to nepovede a spadne vám to na nedostatek místa na disku. U toho nedostatku místa na disku je relativně malá šance na poškození databáze, pokud byste tam měl nějakou starší neaktualizovanou verzi PG a zároveň dělal další operace. Pokud má tabulka 14GB a místo na disku je 3.8GB, a nic moc jste tam nepromazal (obsah dat je 12-14GB), tak to moc smysl nedává. Pokud budete mít stejně nějakou odstávku, tak můžete podropovat nějaké indexy, tím si uvolníte prostor na disku, a pak je znovu vytvoříte.

A tak si říkám, jak pak asi zálohujete.
Díky moc. ....tak to moc smysl nedává... co nedává smysl, dělat to vacuum ?
S tím zálohováním, já se nechci moc odkopat, ale mám obraz té mašiny, nic jiného ani nejde. Navíc je to clusterované , tak ji v nejhorším přeinstaluju.
Problém je v tom, že ta tabulka pg_largeobject je 40x větší než zbytek databáze, tak není moc co podstatného smazat.
Musím něco udělat s ním, zkusím asi to vacuum ?

Edit .. kdepak, místo došlo asi po minutě. Zkusím napřed nafouknout LV.

Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: Pavel Stěhule 06. 05. 2020, 20:18:51

Už jsem blízko vakuování, jen dvě otázky:
- má smysl předtím pustit vacuum analyze ?
- tabulka má velikost 14GB, ale místo v / je jen 3.8GB (proto to celé dělám) Dojede to do konce ? pokud ne, vadí to ?
Ještě jednou moc díky

Ne, proč? Pokud má bloat faktor vysoký, tak se to může povést, pokud ne, tak se to nepovede a spadne vám to na nedostatek místa na disku. U toho nedostatku místa na disku je relativně malá šance na poškození databáze, pokud byste tam měl nějakou starší neaktualizovanou verzi PG a zároveň dělal další operace. Pokud má tabulka 14GB a místo na disku je 3.8GB, a nic moc jste tam nepromazal (obsah dat je 12-14GB), tak to moc smysl nedává. Pokud budete mít stejně nějakou odstávku, tak můžete podropovat nějaké indexy, tím si uvolníte prostor na disku, a pak je znovu vytvoříte.

A tak si říkám, jak pak asi zálohujete.
Díky moc. ....tak to moc smysl nedává... co nedává smysl, dělat to vacuum ?
S tím zálohováním, já se nechci moc odkopat, ale mám obraz té mašiny, nic jiného ani nejde. Navíc je to clusterované , tak ji v nejhorším přeinstaluju.
Problém je v tom, že ta tabulka pg_largeobject je 40x větší než zbytek databáze, tak není moc co podstatného smazat.
Musím něco udělat s ním, zkusím asi to vacuum ?

Edit .. kdepak, místo došlo asi po minutě. Zkusím napřed nafouknout LV.

To nedává se vázalo na VACUUM ANALYZE. Samozřejmě, že netuším, co v těch blobech je, ale pokud se používají, tak jsou běžně řádově větší než zbytek databáze. Kdysi u jedné aplikace měli vystavený formulář s možností přílohy. Za pár let měli docela slušnou sbírku virů. Databáze měla 300GB, a bloby možná 250.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: geekyfreak 06. 05. 2020, 22:35:47
Postgres boptna, protoze se chova k prostoru na disku podobne jako Java k pameti, "naalokuje co muze". Resp. kdyz budete mit tabulku, kde mate hodne UPDATE a DELETE tak se zachovavaji transakce i data, postgres si oznaci data jako smazane, ale neuvolni misto na disku pro system. Cili to pak vypada, ze vam vytece z disku, ale on nevytece, v pripade, ze zacne mit kriticky malo mista na disku, 1-2GB tak teprve potom zacne tyhle "sektory" pouzivat znovu, sam pro sebe. Autovacuum vam nepomuze, resp. ted nevim jestli je to od verze 11 nebo 12, ale doporucuju udelat FULL VACUUM, bohuzel to ma exklusivni locky, takze opatrne (u 12 ma prikaz vacuumdb moznost nezamikat tabulky - viz manual). Doporucuju vybrat "top 10" nejvetsich databazi, seradit vzestupne dle velikosti, per databazi pak udelat to same s tabulkama a pustit full vacuum per tabulka. Kdyz zacnete s mensima, uvolni to misto na vacuuming vetsich tabulek. Pocitejte, ze na tabulky 1GB je treba 2GB na disku na full vacuum, proto zacinat od mensich, uvolni misto pro vetsi.

Jak na to vsechno viz dokumentace: https://wiki.postgresql.org/wiki/Disk_Usage

Takto se to samozřejmě nechová. V detailech jste úplně mimo. Nic ve zlém, ale pokud něčemu nerozumím, tak je lepší mlčet. Bohužel internet je zaplněný špatnými dobrými radami.

Bez přihlášení do db žádnou administraci neuděláte. Lze něco detekovat na souborovém systému, ale je to dost pracnější, a navíc hloupost - pokud zákazník si databázi neadministruje sám, a ani nezplnomocní k tomu Vás, tak to jednou musí dopadnout špatně a je to signál, že něco ve vztahu vy, váš zákazník je špatně.

Podívejte se do tabulky pg_stat_activity jestli nenajdete neuzavřenou transakci - state: "idle in transaction". To blokuje VACUUM, a pokud VACCUUM neaktualizuje mapu volného místa v datovém souboru, tak engine neví, že tam volné místo a zvětšuje soubor. Zkontrolujte log - dost často se jedná o aplikační chybu - čistící funkce padají na syntaktických chybách, timeoutech, ..  Podívejte se jestli nějaké tabulky nejsou přefouklé https://wiki.postgresql.org/wiki/Show_database_bloat - pak aplikujte VACUUM FULL. Zkontrolujte jestli si zákazník nevypnul autovacuum.

Zdravim Pavle,

z nejakeho duvodu mate jinou zkusenost nez mam ja. Priklad uziti https://moodle.org/mod/forum/discuss.php?d=68558, kdyz pustite napr skript co vam prepise linky v db napr: https://docs.moodle.org/38/en/Search_and_replace_tool , vic dat, vetsi efekt. Postgres vam naboptna celkem hezky, si to klidne vyzkousejte: https://www.percona.com/blog/2018/08/06/basic-understanding-bloat-vacuum-postgresql-mvcc/

https://www.postgresql.org/docs/12/routine-vacuuming.html
https://www.postgresql.org/docs/9.5/routine-vacuuming.html

A chovalo se mi to jak jsem psal, kdyz uz musite ten full vacuum udelat a je malo mista, dobry zacit od mensich tabulek.

@FKoudelka:
Po full vacuum uvidi system vice volneho mista. Kdyz by vam to s volnym mistem nevyslo, tak postgres to ustoji, nic vam nespadne, teda alespon mne nespadnul (uprime jsem cekal ze spadne). To co full vacuum nepretridi , uvolni, budete zpatky kde jste byl "na 3.8GB". Jestli se vam nechce riskovat, tak si treba soupnete nektery tabulky na jinej disk jestli muzete.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: Pavel Stěhule 07. 05. 2020, 06:03:10
Postgres boptna, protoze se chova k prostoru na disku podobne jako Java k pameti, "naalokuje co muze". Resp. kdyz budete mit tabulku, kde mate hodne UPDATE a DELETE tak se zachovavaji transakce i data, postgres si oznaci data jako smazane, ale neuvolni misto na disku pro system. Cili to pak vypada, ze vam vytece z disku, ale on nevytece, v pripade, ze zacne mit kriticky malo mista na disku, 1-2GB tak teprve potom zacne tyhle "sektory" pouzivat znovu, sam pro sebe. Autovacuum vam nepomuze, resp. ted nevim jestli je to od verze 11 nebo 12, ale doporucuju udelat FULL VACUUM, bohuzel to ma exklusivni locky, takze opatrne (u 12 ma prikaz vacuumdb moznost nezamikat tabulky - viz manual). Doporucuju vybrat "top 10" nejvetsich databazi, seradit vzestupne dle velikosti, per databazi pak udelat to same s tabulkama a pustit full vacuum per tabulka. Kdyz zacnete s mensima, uvolni to misto na vacuuming vetsich tabulek. Pocitejte, ze na tabulky 1GB je treba 2GB na disku na full vacuum, proto zacinat od mensich, uvolni misto pro vetsi.

Jak na to vsechno viz dokumentace: https://wiki.postgresql.org/wiki/Disk_Usage

Takto se to samozřejmě nechová. V detailech jste úplně mimo. Nic ve zlém, ale pokud něčemu nerozumím, tak je lepší mlčet. Bohužel internet je zaplněný špatnými dobrými radami.

Bez přihlášení do db žádnou administraci neuděláte. Lze něco detekovat na souborovém systému, ale je to dost pracnější, a navíc hloupost - pokud zákazník si databázi neadministruje sám, a ani nezplnomocní k tomu Vás, tak to jednou musí dopadnout špatně a je to signál, že něco ve vztahu vy, váš zákazník je špatně.

Podívejte se do tabulky pg_stat_activity jestli nenajdete neuzavřenou transakci - state: "idle in transaction". To blokuje VACUUM, a pokud VACCUUM neaktualizuje mapu volného místa v datovém souboru, tak engine neví, že tam volné místo a zvětšuje soubor. Zkontrolujte log - dost často se jedná o aplikační chybu - čistící funkce padají na syntaktických chybách, timeoutech, ..  Podívejte se jestli nějaké tabulky nejsou přefouklé https://wiki.postgresql.org/wiki/Show_database_bloat - pak aplikujte VACUUM FULL. Zkontrolujte jestli si zákazník nevypnul autovacuum.

Zdravim Pavle,

z nejakeho duvodu mate jinou zkusenost nez mam ja. Priklad uziti https://moodle.org/mod/forum/discuss.php?d=68558, kdyz pustite napr skript co vam prepise linky v db napr: https://docs.moodle.org/38/en/Search_and_replace_tool , vic dat, vetsi efekt. Postgres vam naboptna celkem hezky, si to klidne vyzkousejte: https://www.percona.com/blog/2018/08/06/basic-understanding-bloat-vacuum-postgresql-mvcc/

https://www.postgresql.org/docs/12/routine-vacuuming.html
https://www.postgresql.org/docs/9.5/routine-vacuuming.html

A chovalo se mi to jak jsem psal, kdyz uz musite ten full vacuum udelat a je malo mista, dobry zacit od mensich tabulek.

@FKoudelka:
Po full vacuum uvidi system vice volneho mista. Kdyz by vam to s volnym mistem nevyslo, tak postgres to ustoji, nic vam nespadne, teda alespon mne nespadnul (uprime jsem cekal ze spadne). To co full vacuum nepretridi , uvolni, budete zpatky kde jste byl "na 3.8GB". Jestli se vam nechce riskovat, tak si treba soupnete nektery tabulky na jinej disk jestli muzete.

Jde o to, co a jak jste napsal. Nedá se říct, že to, co jste napsal, by nebyla pravda, ale minimálně to správně nepostihuje popisovaný problém, a může svádět k nesprávným doměnkám o tom, jak se vlastně VACUUM (potažmo Postgres) chová. "Postgres si nenaalokuje co může" - tahle věta je nesmysl. Jde o to, že datový soubor je halda, data jsou umístěna náhodně, a pokud nějaká data smažete, tak se datový soubor nemůže (bez defragmentace - což je to co dělá VACUUM FULL) zmenšit. S tím nedostatkem místa na disku a znovu opětovným použitím prostoru - dělám s Postgresem 20 let a nevím, že by tam byl podobný mechanismus, resp. Vám garantuji, že tam nid takového není. Postgres si DELETEm označkuje data, ale engine je nezačne přepisovat ihned, protože nějaká jiná transakce je ještě může vidět. Začnou se přepisovat až po  a) potvrzení, že smazaná data už nejsou součástí žádného snapshotu aktivní transakce, b) až se aktualizuje free space mapa (aby engine věděl, že datová stránka obsahuje volné místo). Obojí zajišťuje příkaz VACUUM. Ten může být spuštěn ručně nebo automatem (autovacuum), pokud se přesáhne 220% modifikovaných řádků v tabulce (autovacuum_vacuum_scale_factor - výchozí konfigurace) a ne častěji než 1x za minutu (autovacuum_naptime - výchozí konfigurace). Postgres vůbec nesleduje a neví kolik je místa na disku. Jelikož je datový soubor u Postgresu vždy pro jednu tabulku, tak alokované místo na disku bude do VACUUM FULL už vždy alokované pro tu konkrétní tabulku, které ji může využít. Pro tabulku,která má 1GB je třeba 2GB místa na disku - to může platit pro konkrétní aplikaci, ale jako obecné tvrzení je to nesmyslné.  Postgres překopíruje data ze staré tabulky do nové a nad tou novou vytvoří nové indexy. Potřebujete tolik místa, kolik tabulka obsahuje dat. V případě, že nedojde k redukci, tak v jeden moment máte 2kopie jedné tabulky -- tady minimálně u vaší formulace není jednoznačné jestli se těmi 2GB míní celkové místo na disku nebo volné místo na disku (tak to může vyznít) - je to ještě v závislosti, jak bude nová tabulka vypadat.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: FKoudelka 07. 05. 2020, 09:42:57

Už jsem blízko vakuování, jen dvě otázky:
- má smysl předtím pustit vacuum analyze ?
- tabulka má velikost 14GB, ale místo v / je jen 3.8GB (proto to celé dělám) Dojede to do konce ? pokud ne, vadí to ?
Ještě jednou moc díky

Ne, proč? Pokud má bloat faktor vysoký, tak se to může povést, pokud ne, tak se to nepovede a spadne vám to na nedostatek místa na disku. U toho nedostatku místa na disku je relativně malá šance na poškození databáze, pokud byste tam měl nějakou starší neaktualizovanou verzi PG a zároveň dělal další operace. Pokud má tabulka 14GB a místo na disku je 3.8GB, a nic moc jste tam nepromazal (obsah dat je 12-14GB), tak to moc smysl nedává. Pokud budete mít stejně nějakou odstávku, tak můžete podropovat nějaké indexy, tím si uvolníte prostor na disku, a pak je znovu vytvoříte.

A tak si říkám, jak pak asi zálohujete.
Díky moc. ....tak to moc smysl nedává... co nedává smysl, dělat to vacuum ?
S tím zálohováním, já se nechci moc odkopat, ale mám obraz té mašiny, nic jiného ani nejde. Navíc je to clusterované , tak ji v nejhorším přeinstaluju.
Problém je v tom, že ta tabulka pg_largeobject je 40x větší než zbytek databáze, tak není moc co podstatného smazat.
Musím něco udělat s ním, zkusím asi to vacuum ?

Edit .. kdepak, místo došlo asi po minutě. Zkusím napřed nafouknout LV.
Nevite kam si pri tom vacuum full sype ty docasne soubory, ze bych tam primontoval novy LV fs/ ktery bych pak smazal ? Nafukovat / nechci. A velikost pridaneho mista ... 2x jako tabulka ? Nebo = tabulka ?
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: Pavel Stěhule 07. 05. 2020, 10:14:33

Nevite kam si pri tom vacuum full sype ty docasne soubory, ze bych tam primontoval novy LV fs/ ktery bych pak smazal ? Nafukovat / nechci. A velikost pridaneho mista ... 2x jako tabulka ? Nebo = tabulka ?

pri VACUUM FULL vam budou vznikat datove soubory v podadresari, ktery odpovida databazi - pripadne jeste mohou vzniknout docasne (pracovni soubory) v podadresari datoveho adresare postgresu.

Kdyz si zjistite ve kterem adresari mate db, tak tento adresar muzete presunout na nove LV, a propojit s puvodnim adresarem symlinkem.  Database v postgresu se mapuje do adresare fs. POZOR: PRESUN MUZETE UDELAT POUZE PRI VYPNUTEM POSTGRESU.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: geekyfreak 07. 05. 2020, 12:17:44
Postgres boptna, protoze se chova k prostoru na disku podobne jako Java k pameti, "naalokuje co muze". Resp. kdyz budete mit tabulku, kde mate hodne UPDATE a DELETE tak se zachovavaji transakce i data, postgres si oznaci data jako smazane, ale neuvolni misto na disku pro system. Cili to pak vypada, ze vam vytece z disku, ale on nevytece, v pripade, ze zacne mit kriticky malo mista na disku, 1-2GB tak teprve potom zacne tyhle "sektory" pouzivat znovu, sam pro sebe. Autovacuum vam nepomuze, resp. ted nevim jestli je to od verze 11 nebo 12, ale doporucuju udelat FULL VACUUM, bohuzel to ma exklusivni locky, takze opatrne (u 12 ma prikaz vacuumdb moznost nezamikat tabulky - viz manual). Doporucuju vybrat "top 10" nejvetsich databazi, seradit vzestupne dle velikosti, per databazi pak udelat to same s tabulkama a pustit full vacuum per tabulka. Kdyz zacnete s mensima, uvolni to misto na vacuuming vetsich tabulek. Pocitejte, ze na tabulky 1GB je treba 2GB na disku na full vacuum, proto zacinat od mensich, uvolni misto pro vetsi.

Jak na to vsechno viz dokumentace: https://wiki.postgresql.org/wiki/Disk_Usage

Takto se to samozřejmě nechová. V detailech jste úplně mimo. Nic ve zlém, ale pokud něčemu nerozumím, tak je lepší mlčet. Bohužel internet je zaplněný špatnými dobrými radami.

Bez přihlášení do db žádnou administraci neuděláte. Lze něco detekovat na souborovém systému, ale je to dost pracnější, a navíc hloupost - pokud zákazník si databázi neadministruje sám, a ani nezplnomocní k tomu Vás, tak to jednou musí dopadnout špatně a je to signál, že něco ve vztahu vy, váš zákazník je špatně.

Podívejte se do tabulky pg_stat_activity jestli nenajdete neuzavřenou transakci - state: "idle in transaction". To blokuje VACUUM, a pokud VACCUUM neaktualizuje mapu volného místa v datovém souboru, tak engine neví, že tam volné místo a zvětšuje soubor. Zkontrolujte log - dost často se jedná o aplikační chybu - čistící funkce padají na syntaktických chybách, timeoutech, ..  Podívejte se jestli nějaké tabulky nejsou přefouklé https://wiki.postgresql.org/wiki/Show_database_bloat - pak aplikujte VACUUM FULL. Zkontrolujte jestli si zákazník nevypnul autovacuum.

Zdravim Pavle,

z nejakeho duvodu mate jinou zkusenost nez mam ja. Priklad uziti https://moodle.org/mod/forum/discuss.php?d=68558, kdyz pustite napr skript co vam prepise linky v db napr: https://docs.moodle.org/38/en/Search_and_replace_tool , vic dat, vetsi efekt. Postgres vam naboptna celkem hezky, si to klidne vyzkousejte: https://www.percona.com/blog/2018/08/06/basic-understanding-bloat-vacuum-postgresql-mvcc/

https://www.postgresql.org/docs/12/routine-vacuuming.html
https://www.postgresql.org/docs/9.5/routine-vacuuming.html

A chovalo se mi to jak jsem psal, kdyz uz musite ten full vacuum udelat a je malo mista, dobry zacit od mensich tabulek.

@FKoudelka:
Po full vacuum uvidi system vice volneho mista. Kdyz by vam to s volnym mistem nevyslo, tak postgres to ustoji, nic vam nespadne, teda alespon mne nespadnul (uprime jsem cekal ze spadne). To co full vacuum nepretridi , uvolni, budete zpatky kde jste byl "na 3.8GB". Jestli se vam nechce riskovat, tak si treba soupnete nektery tabulky na jinej disk jestli muzete.

Jde o to, co a jak jste napsal. Nedá se říct, že to, co jste napsal, by nebyla pravda, ale minimálně to správně nepostihuje popisovaný problém, a může svádět k nesprávným doměnkám o tom, jak se vlastně VACUUM (potažmo Postgres) chová. "Postgres si nenaalokuje co může" - tahle věta je nesmysl. Jde o to, že datový soubor je halda, data jsou umístěna náhodně, a pokud nějaká data smažete, tak se datový soubor nemůže (bez defragmentace - což je to co dělá VACUUM FULL) zmenšit. S tím nedostatkem místa na disku a znovu opětovným použitím prostoru - dělám s Postgresem 20 let a nevím, že by tam byl podobný mechanismus, resp. Vám garantuji, že tam nid takového není. Postgres si DELETEm označkuje data, ale engine je nezačne přepisovat ihned, protože nějaká jiná transakce je ještě může vidět. Začnou se přepisovat až po  a) potvrzení, že smazaná data už nejsou součástí žádného snapshotu aktivní transakce, b) až se aktualizuje free space mapa (aby engine věděl, že datová stránka obsahuje volné místo). Obojí zajišťuje příkaz VACUUM. Ten může být spuštěn ručně nebo automatem (autovacuum), pokud se přesáhne 220% modifikovaných řádků v tabulce (autovacuum_vacuum_scale_factor - výchozí konfigurace) a ne častěji než 1x za minutu (autovacuum_naptime - výchozí konfigurace). Postgres vůbec nesleduje a neví kolik je místa na disku. Jelikož je datový soubor u Postgresu vždy pro jednu tabulku, tak alokované místo na disku bude do VACUUM FULL už vždy alokované pro tu konkrétní tabulku, které ji může využít. Pro tabulku,která má 1GB je třeba 2GB místa na disku - to může platit pro konkrétní aplikaci, ale jako obecné tvrzení je to nesmyslné.  Postgres překopíruje data ze staré tabulky do nové a nad tou novou vytvoří nové indexy. Potřebujete tolik místa, kolik tabulka obsahuje dat. V případě, že nedojde k redukci, tak v jeden moment máte 2kopie jedné tabulky -- tady minimálně u vaší formulace není jednoznačné jestli se těmi 2GB míní celkové místo na disku nebo volné místo na disku (tak to může vyznít) - je to ještě v závislosti, jak bude nová tabulka vypadat.

Chapu, vyjadril jsem se dost vagne. Nicmene velikostne, kdyz mate 1GB tabulky, potrebujete 1GB volneho mista na FULL VACUUM. Je to neco co jsem vypozoroval, samozrejme to velmi zalezi na povaze aplikace a dat, takze matematiku za tim nehledejte. To ze postgres nevytece, kdyz delate full vacuum i presto, ze mu dojde misto mi funguje. Bude se to chovat jinak, kdyz budete delat full vacuum tabulky moje_sessions nebo moje_logy atd. Muze se vam stat, kdyz pustite full vacuum na databazi co vam zabira treba 100GB, ze se smrskne na 30GB, fakt zalezi jak dlouho se full vacuum nedelal a kolik se updatlo a smazalo.

Podivejte se na velikosti, indexy atd.: https://www.tutorialdba.com/2018/06/how-to-get-table-size-database-size_26.html
Zjistite si co mate zabloatovano a pustite to treba jenom na par tabulek, co to potrebujou.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: Ondrej Nemecek 07. 05. 2020, 15:31:03

+1  :)

Můžu jen dotaz, zda je důvod spouštět ručně FULL VACUUM když mám zapnuté autovacuum?

Těch důvodů může být více - mohou vám neplánovaně narůst tabulky, které pak chcete promazat, a u kterých nechcete riskovat fullscan, který by zbytečně četl 100násobek balastu - aplikační chyby, dos útok, promazání logů, .. autovacuum, připadně samotné vacuum může být z několika důvodů neefektivní - např. dlouho otevřená transakce.

(...)

Děkuji.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: FKoudelka 08. 05. 2020, 08:50:53
Ahoj, vyznáte se v tom někdo ?
Ve struktuře DB na disku a jak se doptat, co se kam píše …
Potřebuju zjistit, proč mi jeden segment na disku bobtná nade všechny meze.
Nevím o této DB nic.
Dík
Ahoj , díky všem. Hodně jsem se naučil. Po  vakuu jsem ušetřil 2GB , což nestačí, tak jsem nafoukl LV a nechám to být.
Název: Re:PostgreSQL DB na Linuxu roste nad všechny meze
Přispěvatel: luvar 08. 05. 2020, 09:31:52
Este ma napadla jedna drobnost. Co vyuziva tu databazu? Ak je tam hibernate ORM na strane aplikacie, tak som pocul o takej lahode, kde sa bloby ukladali rucne (teda za pomoci zle nakonfigurovaneho ORM) do pg_largeobject a odkazovali sa cez idecko ulozene vo varchar stlpci v normalnej tabulke. Nasledne, ked sa povodny odkaz zrusil, tak ORM nespravilo unlink a dany large object zostal donekonecna visiet v "lugte". Nasledne je potrebne nakodit si vlastny "garbage collector" a upratovat takuto DB. Minimalne jednorazovo, ked sa fixne setup ORM-ka.

PS: Pardon za vagnejsi popis, ale tuto info mam z druhej ruky a je to kus davnejsie, co som do hlbky rozumel tejto problematike... Mozno stoji za to precitat https://wiki.postgresql.org/wiki/Largeobject_Enhancement a pripadne sa daco naucit o pouzivani v danom pripade.