On 9/19/19 2:24 PM, Julie Nishimura wrote:
Adrian,
We do run vacuum w/o FULL every day:
SYSTABLES="' pg_catalog.' || relname || ';' from pg_class a,
pg_namespace b where a.relnamespace=b.oid and b.nspname='pg_catalog' an
d a.relkind='r'"
But it does not look like it frees up the space...
It won't return space to the OS it just marks it as available for reuse
by Postgres.
Or you meant we need to run vacuum on 'my_db_name' without parameters,
that it runs for every table? I am just not sure how long it will take
to run for 39 tb...:(
Not sure. The bottom line is you are running out of transaction ids and
if the txid counter wraps things get ugly. You could try vacuuming
individual non-system tables that have a lot of churn(UPDATES/DELETES)
and see if that buys you some ids.
Thanks
------------------------------------------------------------------------
*From:* Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
*Sent:* Thursday, September 19, 2019 2:06 PM
*To:* Julie Nishimura <juliezain@xxxxxxxxxxx>
*Subject:* Re: is it safe to drop 25 tb schema with cascade option?
On 9/19/19 1:30 PM, Julie Nishimura wrote:
Adrian, thanks for your reply. We do run VACUUM on pg_catalog every day
(while the system is online). Should I try to run VACUUM FULL on
pg_catalog? is it the same as you referring system catalogs?
I would avoid VACUUM FULL as it acquires an exclusive lock on the table
and rewrites the table.:
https://www.postgresql.org/docs/8.2/sql-vacuum.html
A VACUUM w/o FULL will make space available for new tuples which is what
you want.
Thank you!
------------------------------------------------------------------------
*From:* Adrian Klaver <adrian.klaver@xxxxxxxxxxx>
*Sent:* Thursday, September 19, 2019 12:38 PM
*To:* Julie Nishimura <juliezain@xxxxxxxxxxx>; t
pgsql-general@xxxxxxxxxxxxxxxxxxxx <pgsql-general@xxxxxxxxxxxxxxxxxxxx>;
pgsql-general <pgsql-general@xxxxxxxxxxxxxx>
*Subject:* Re: is it safe to drop 25 tb schema with cascade option?
On 9/19/19 12:06 PM, Julie Nishimura wrote:
Hello, we've recently inherited large Greenplum system (master with
standby and 8 segment nodes), which is running old version of GP:
20190919:15:22:01:122002 gpstate:hitw-mck-gp1-mdw-1:gpadmin-[INFO]:-
Greenplum initsystem version = 4.3.4.0 build 1
20190919:15:22:01:122002 gpstate:hitw-mck-gp1-mdw-1:gpadmin-[INFO]:-
Greenplum current version = PostgreSQL 8.2.15 (Greenplum
Database 4.3.8.1 build 1) on x86_64-unknown-linux-gnu, compiled by GCC
gcc (GCC) 4.4.2 compiled on Apr 20 2016 08:08:56
20190919:15:22:01:122002 gpstate:hitw-mck-gp1-mdw-1:gpadmin-[INFO]:-
Postgres version = 8.2.15
If I scan logs, for the last 6 months I see the following warning after
every transaction:
04 UTC,0,con9059926,cmd1,seg-1,,,,sx1,"WARNING","01000","database
""my_db_name"" must be vacuumed within 1607900488 transactions",,"To
avoid a database shutdown, execute a full-database VACUUM in
""my_db_name"".",,,,"set client_encoding to 'SQL_ASCII'",0,,"varsup.c",109,
The database "my_db_name" is 32 TB. According to the crontab logs, we
run VACUUM on pg_catalog every day (while the system is online). Should
I try to run VACUUM FULL on pg_catalog first, or I need to run VACUUM on
the entire "my_db_name"? I am not sure what I should try first.
The vacuum warning is about transaction id wrap around:
https://www.postgresql.org/docs/8.2/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
You will need to vacuum more then just pg_catalog. You will need to do
what the message says, vacuum the entire database.
For the full picture: the largest schema on "my_db_name" was "temp", it
was consuming about 25 tb. So what we did - we renamed this schema to
"temp_orig", and created brand new schema "temp" (to make drop objects
from temp_orig easier and isolated). However, I was hesitating to drop
the entire schema that big in one transaction, and started dropping
tables from "temp_orig", however, there are millions of objects in that
schema, and as a result, number of "drop table" transactions are very
high. How safe is it to run "DROPSCHEMAtemp_orig CASCADE" if the schema
is almost 25 tb?
Not sure.
We are running out of space very quickly. we have only 5% left on a device
Last time when we dropped millions of objects from that old schema, we
were able to free up some space, but this time around even though I am
running a lot of "drop tables", the space temporarily goes down
(according to df -h), then it goes back again, even faster than I am
freeing it up. Which makes me believe the system catalog is bloated now.
Probably due to all the other operations hitting the database.
Have you tried vacuuming the system catalogs?
Any advice is appreciated.
Thanks a lot!
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
