VACUUM FULL fails to free diskspace
VACUUM FULL fails to free diskspace
От:
Morten Guldager <PostgreSQL-admin@mogul.dk>
Дата:
Hi, 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) /Morten %-) (This message may show up twice if the list-moderator is too fast. I failed to post from the rigth address the first try :-)
Re: VACUUM FULL fails to free diskspacef
От:
Bruce Momjian <pgman@candle.pha.pa.us>
Дата:
Morten Guldager wrote: > Hi, > > 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. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Re: VACUUM FULL fails to free diskspacef
От:
Morten Guldager <PostgreSQL-admin@mogul.dk>
Дата:
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. /Morten %-)