Обсуждение: Followup Question about Vacuum from newsgroup
My apologies if this posted twice...
HI All,
I found the following posting about Vacuum in the postgres.admin newsgroup (see below my question)
I have the same problem: We are using HUGE amounts of diskspace in our production database, but the standby (created daily from a pgdump / pg_restore of our production server) is about 1/2 the size.
We have a base of about 4 million users so our datafiles are pretty big. I shut the site down and did a VACUUM ANALYZE VERBOSE <tablename> of 2 of the largest tables. It took over 4 hours. When it was all said and done, disk utilization had gone UP.
My suspicion is that there are pg_toast_xxxxx files left in the base directory? If I identify them with oid2name (waiting for netops to build that) is it ok to just delete those toast files? This is the most crucial part of our revenue year (tons of christmas shoppers buying tons) so we absolutely can't have any down time or corruption.
Any advice or links to documentation that covers this specifically would be most helpful.
thanks
----------------------- start newsgroup posting ------------------------------------
Author: Morten Guldager <PostgreSQL-admin@mogul.dk>
Date: Wed 16 Oct 15:09:40 2002 CDT
Subject: Re: [ADMIN] VACUUM FULL fails to free diskspacef
Thread: 2 messages
Date: Wed 16 Oct 15:09:40 2002 CDT
Subject: Re: [ADMIN] VACUUM FULL fails to free diskspacef
Thread: 2 messages
On 2002.10.16 20:18 Bruce Momjian wrote: > Morten Guldager wrote: > > > > My database cluster have one database with one table. (if we don't > > count template0 and 1. > > > > My table has 3 collumns, 2 ints and a bytea. 2.5M rows and it > consumes > > 150G diskspace. I have a unique index on the 2 ints. > > > > I did a TRUNCATE on the table, and a VACUUM FULL. > > > > But PostgreSQL-7.2.3 did not return the space to the filesystem. > (ext3 > > on linux) > > That is interesting. I originally thought maybe the indexes aren't > truncated, but it looks like that is happening. Please try > /contrib/oid2name to find which files are taking the space. Ok, never used oid2name before, but here we go: I located a datafile which must be one of the files holding the non-freed space, it is 1073741824 bytes big. It is: $PGDATA/base/16556/20048694 Next I did a: (mogul is the name of my database) $ oid2name -d mogul -o 20048694 Tablename of oid 20048694 from database "mogul": --------------------------------- 20048694 = pg_toast_20048692 and then: $ oid2name -d mogul -o 20048692 Tablename of oid 20048692 from database "mogul": --------------------------------- 20048692 = regninger And "regninger" is the name of the table I just truncated. I have tried to drop the indexes, that does not help. If I drop the database the space do get freed.
--------------------- end newsgroup posting -----------------
HT Levine <htlevine@ebates.com> writes:
> My suspicion is that there are pg_toast_xxxxx files left in the base
> directory? If I identify them with oid2name (waiting for netops to build
> that) is it ok to just delete those toast files?
No.
In pre-7.3 releases, TRUNCATE TABLE did not automatically truncate the
associated TOAST table, which was a nasty oversight :-(. However, those
releases would also allow you to manually truncate a TOAST table (which
was also a bad oversight, but rather fortunate in hindsight). The bad
news is that they think TOAST tables are system tables --- so the only
way to fully truncate a toastable table in 7.2 is
TRUNCATE TABLE toast-table-for-foo;
TRUNCATE TABLE foo;
in a standalone backend started with -O option :-(
This mess is fixed in 7.3 --- TRUNCATE automatically truncates the toast
table along with its master, when you truncate the master.
regards, tom lane
Now here's a little more information. I did this VACUUM on a table which was NOT truncated. It has many additions and updates, but very few if any deletions. In this case, did I have anything to gain with VACUUM? Does the thing about the toast files still hold? same instructions below? Thanks so much! "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:21502.1039988725@sss.pgh.pa.us... > HT Levine <htlevine@ebates.com> writes: > > My suspicion is that there are pg_toast_xxxxx files left in the base > > directory? If I identify them with oid2name (waiting for netops to build > > that) is it ok to just delete those toast files? > > No. > > In pre-7.3 releases, TRUNCATE TABLE did not automatically truncate the > associated TOAST table, which was a nasty oversight :-(. However, those > releases would also allow you to manually truncate a TOAST table (which > was also a bad oversight, but rather fortunate in hindsight). The bad > news is that they think TOAST tables are system tables --- so the only > way to fully truncate a toastable table in 7.2 is > > TRUNCATE TABLE toast-table-for-foo; > TRUNCATE TABLE foo; > > in a standalone backend started with -O option :-( > > This mess is fixed in 7.3 --- TRUNCATE automatically truncates the toast > table along with its master, when you truncate the master. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org