PostgreSQL DB na Linuxu roste nad všechny meze

PostgreSQL DB na Linuxu roste nad všechny meze
« kdy: 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
« Poslední změna: 05. 05. 2020, 16:58:56 od Petr Krčmář »


luvar

  • ***
  • 114
    • Zobrazit profil
    • E-mail
Re:Postgresql DB na linuxu
« Odpověď #1 kdy: 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 :)

Re:Postgresql DB na linuxu
« Odpověď #2 kdy: 05. 05. 2020, 15:50:46 »
Zkontroloval bych autovacuum, velikost indexů a korektní ukončování transakcí.

Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #3 kdy: 05. 05. 2020, 23:14:03 »
Díky moc, vypadá to, že stejně nedostanu z vendora heslo do DB :-(

kmarty

  • ***
  • 186
    • Zobrazit profil
Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #4 kdy: 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.


Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #5 kdy: 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

Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #6 kdy: 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.


Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #7 kdy: 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?

Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #8 kdy: 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.

Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #9 kdy: 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

Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #10 kdy: 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

Logik

  • *****
  • 817
    • Zobrazit profil
    • E-mail
Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #11 kdy: 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).

Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #12 kdy: 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 :-)

Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #13 kdy: 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
« Poslední změna: 06. 05. 2020, 17:56:06 od FKoudelka »

Re:PostgreSQL DB na Linuxu roste nad všechny meze
« Odpověď #14 kdy: 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