Re: is it safe to drop 25 tb schema with cascade option?
От | Adrian Klaver |
---|---|
Тема | Re: is it safe to drop 25 tb schema with cascade option? |
Дата | |
Msg-id | d11cac74-fc57-6949-dfc5-cc50d0c0810d@aklaver.com обсуждение исходный текст |
Ответ на | Re: is it safe to drop 25 tb schema with cascade option? (Julie Nishimura <juliezain@hotmail.com>) |
Ответы |
Re: is it safe to drop 25 tb schema with cascade option?
|
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Предыдущее
От: Julie NishimuraДата:
Сообщение: Re: is it safe to drop 25 tb schema with cascade option?
Следующее
От: Adrian KlaverДата:
Сообщение: Re: is it safe to drop 25 tb schema with cascade option?