autovacuum with lots of open file references to deleted files

From: Tore Halset <halset(at)pvv(dot)ntnu(dot)no>
To: pgsql-admin(at)postgresql(dot)org
Subject: autovacuum with lots of open file references to deleted files
Date: 2012-10-14 21:48:55
Message-ID: B9BEA448-978F-4A14-A088-3FD82214FFAC@pvv.ntnu.no
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Hello.

On this box I drop a 80GB database each night followed by a restore of a similar sized database. It is a restore of our production database to a development server. This box is running 9.2rc1 (sorry).

du and df reported quite different numbers and lsof show that autovacuum is holding lots of deleted files. After killing the autovacuum daemon, some disk space was restored and the du and df numbers was more equal.

autovacuum hold roughly 100GB of deleted files. This running PostgreSQL instance has dumped/restored the 80GB database ~20 times.

Is this something that has been fixed in PostgreSQL or that can be fixed? Or am I doing something wrong? - besides running a release candidate..

postmaste 28573 postgres 360u REG 8,1 1073741824 18319099 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.10 (deleted)
postmaste 28573 postgres 361u REG 8,1 1073741824 18319095 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.11 (deleted)
postmaste 28573 postgres 362u REG 8,1 1073741824 18319101 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.12 (deleted)
postmaste 28573 postgres 363u REG 8,1 1073741824 18319102 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.13 (deleted)
postmaste 28573 postgres 364u REG 8,1 1073741824 18319103 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.14 (deleted)
postmaste 28573 postgres 365u REG 8,1 1073741824 18319100 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.15 (deleted)
postmaste 28573 postgres 366u REG 8,1 1073741824 18319105 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.16 (deleted)
postmaste 28573 postgres 367u REG 8,1 1073741824 18319108 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.21 (deleted)
postmaste 28573 postgres 368u REG 8,1 1073741824 18319111 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.22 (deleted)
postmaste 28573 postgres 369u REG 8,1 1073741824 18319110 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.23 (deleted)
postmaste 28573 postgres 370u REG 8,1 1073741824 18319112 /usr/local/postgresql-9.2rc1/data/base/3211526/11991.25 (deleted)

# ps auxww|grep 28573
root 22536 0.0 0.0 7372 880 pts/0 S+ 23:36 0:00 grep 28573
postgres 28573 0.0 0.2 61372 38048 ? Ss Sep07 44:34 postgres: autovacuum launcher process

Regards,
Tore Halset.

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2012-10-15 00:27:55 Re: autovacuum with lots of open file references to deleted files
Previous Message Tom Lane 2012-10-14 20:15:13 Re: TCPIP connection on local host using md5