Thread: 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@aklaver.com

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@aklaver.com>
> *Sent:* Thursday, September 19, 2019 2:06 PM
> *To:* Julie Nishimura <juliezain@hotmail.com>
> *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@aklaver.com>
>> *Sent:* Thursday, September 19, 2019 12:38 PM
>> *To:* Julie Nishimura <juliezain@hotmail.com>; t
>> pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>; 
>> pgsql-general <pgsql-general@postgresql.org>
>> *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@aklaver.com
> 
> 
> -- 
> Adrian Klaver
> adrian.klaver@aklaver.com


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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...
> 
> 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...:(

Should have added to previous post that in this link:

https://www.postgresql.org/docs/8.2/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

there are some queries that can will show you the XID status for tables 
and the database.



> 
> Thanks



-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 9/19/19 2:24 PM, Julie Nishimura wrote:
>> 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.

You're going to have to vacuum *all* of them to get the global freeze
counter to advance, as it's basically just the min of the per-table
pg_class.relfrozenxid values.  It might be worth looking at
age(relfrozenxid) to see which tables are furthest back, as those
are the one(s) holding things up; but if you've not been running
autovacuum then it's likely that they are all old.

Do NOT NOT NOT use VACUUM FULL here.  It will not make things any
better than a plain VACUUM, and it will take a lot longer ... time
you maybe haven't got.

            regards, tom lane

On 9/19/19 2:42 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> On 9/19/19 2:24 PM, Julie Nishimura wrote:
>>> 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.
> 
> You're going to have to vacuum *all* of them to get the global freeze
> counter to advance, as it's basically just the min of the per-table
> pg_class.relfrozenxid values.  It might be worth looking at
> age(relfrozenxid) to see which tables are furthest back, as those
> are the one(s) holding things up; but if you've not been running
> autovacuum then it's likely that they are all old.

So this is the part I need some clarification on. If you start vacuuming 
the tables that are setting the min then that should buy you some time?

> 
> Do NOT NOT NOT use VACUUM FULL here.  It will not make things any
> better than a plain VACUUM, and it will take a lot longer ... time
> you maybe haven't got.
> 
>             regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> So this is the part I need some clarification on. If you start vacuuming 
> the tables that are setting the min then that should buy you some time?

Yes, if there are some small tables that have relfrozenxid even further
back than the big ones, vacuuming them first would be a win.

            regards, tom lane

On 9/19/19 4:55 PM, Tom Lane wrote:
> Adrian Klaver <adrian.klaver@aklaver.com> writes:
>> So this is the part I need some clarification on. If you start vacuuming
>> the tables that are setting the min then that should buy you some time?
> Yes, if there are some small tables that have relfrozenxid even further
> back than the big ones, vacuuming them first would be a win.

With this?

CDSCH02=# select cl.oid,
        COALESCE(ta.schemaname, 'pg_toast'),
        cl.relname,
        cl.relfrozenxid,
        age(cl.relfrozenxid)
from pg_class cl FULL JOIN pg_tables ta
    ON ta.tablename = cl.relname
where not cl.relfrozenxid = xid '0'
order by age(cl.relfrozenxid) desc
;



-- 
Angular momentum makes the world go 'round.