pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedautovacuum with lots of open file references to deleted files
7+ messages / 4 participants
[nested] [flat]
* autovacuum with lots of open file references to deleted files
@ 2012-10-14 21:48 Tore Halset <halset@pvv.ntnu.no>
0 siblings, 1 reply; 7+ messages in thread
From: Tore Halset @ 2012-10-14 21:48 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum with lots of open file references to deleted files
@ 2012-10-15 00:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tore Halset <halset@pvv.ntnu.no>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2012-10-15 00:27 UTC (permalink / raw)
To: Tore Halset <halset@pvv.ntnu.no>; +Cc: pgsql-admin
Tore Halset <halset@pvv.ntnu.no> writes:
> 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.
Hm. I've been able to reproduce some leakage of file descriptors in the
autovac launcher, but it required (a) fairly small shared_buffers and
(b) very heavy update activity on large tables. So I'm not sure that it
would explain the consistent leakage you seem to be seeing. Can you
tell us more about your usage pattern on the development server? What
nondefault settings are you using on it?
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum with lots of open file references to deleted files
@ 2012-11-04 21:38 Tore Halset <halset@pvv.ntnu.no>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Tore Halset @ 2012-11-04 21:38 UTC (permalink / raw)
To: pgsql-admin
On Oct 15, 2012, at 2:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Tore Halset <halset@pvv.ntnu.no> writes:
>> 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.
>
> Hm. I've been able to reproduce some leakage of file descriptors in the
> autovac launcher, but it required (a) fairly small shared_buffers and
> (b) very heavy update activity on large tables. So I'm not sure that it
> would explain the consistent leakage you seem to be seeing. Can you
> tell us more about your usage pattern on the development server?
A cron job dropdb one of the databases and createdb it and then pg_restore. Roughly 80GB dump.
In addition to that database, we have some other copies that are used by some java clients. Only light usage. Not any stored procedures, but a lot of blobs. None of the blobs are over 5MB in size.
lsof show that both autovacuum and idle postgres-processes hold references to deleted files.
Out production PostgreSQL running a 9.1 variant does not have this problem. It does not have the nightly dropdb/createdb/pg_restore, but otherwise similar usage patterins. It is also configured to use more memory.
> What
> nondefault settings are you using on it?
it is pretty default.
max_connections = 100
shared_buffers = 32MB
Regards,
Tore Halset.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum with lots of open file references to deleted files
@ 2012-11-05 00:47 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tore Halset <halset@pvv.ntnu.no>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2012-11-05 00:47 UTC (permalink / raw)
To: Tore Halset <halset@pvv.ntnu.no>; +Cc: pgsql-admin
Tore Halset <halset@pvv.ntnu.no> writes:
> On Oct 15, 2012, at 2:27 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Tore Halset <halset@pvv.ntnu.no> writes:
>>> 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.
>> Hm. I've been able to reproduce some leakage of file descriptors in the
>> autovac launcher, but it required (a) fairly small shared_buffers and
>> (b) very heavy update activity on large tables. So I'm not sure that it
>> would explain the consistent leakage you seem to be seeing. Can you
>> tell us more about your usage pattern on the development server?
> A cron job dropdb one of the databases and createdb it and then pg_restore. Roughly 80GB dump.
So far, my guess is that this is fixed by commits a1f064fc2 + d7598aeea.
> Out production PostgreSQL running a 9.1 variant does not have this problem. It does not have the nightly dropdb/createdb/pg_restore, but otherwise similar usage patterins. It is also configured to use more memory.
9.1 has the same bug (and the same patches), but I think that the aspect
you're running into is specific to DROP DATABASE scenarios.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum with lots of open file references to deleted files
@ 2012-11-05 01:40 Greg Williamson <gwilliamson39@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Greg Williamson @ 2012-11-05 01:40 UTC (permalink / raw)
To: ; +Cc: pgsql-admin
Tom --
<...>
>> A cron job dropdb one of the databases and createdb it and then pg_restore.
> Roughly 80GB dump.
>
> So far, my guess is that this is fixed by commits a1f064fc2 + d7598aeea.
>
>> Out production PostgreSQL running a 9.1 variant does not have this problem.
> It does not have the nightly dropdb/createdb/pg_restore, but otherwise similar
> usage patterins. It is also configured to use more memory.
>
> 9.1 has the same bug (and the same patches), but I think that the aspect
> you're running into is specific to DROP DATABASE scenarios.
>
Is there any idea of when this will be released ?
Thanks,
Greg W.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: autovacuum with lots of open file references to deleted files
@ 2012-11-05 02:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Greg Williamson <gwilliamson39@yahoo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2012-11-05 02:21 UTC (permalink / raw)
To: Greg Williamson <gwilliamson39@yahoo.com>; +Cc: pgsql-admin
Greg Williamson <gwilliamson39@yahoo.com> writes:
>> So far, my guess is that this is fixed by commits a1f064fc2 + d7598aeea.
> Is there any idea of when this will be released ?
No. I'd guess that there will be update releases before the end of the
year, but they are not imminent. We have some open issues that have to
be settled first, notably
http://archives.postgresql.org/pgsql-hackers/2012-10/msg00511.php
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* 'autovacuum with lots of open file references to deleted files' back from 2012
@ 2018-08-16 11:28 Evgeniy Losev <e.losev@i-dgtl.ru>
0 siblings, 0 replies; 7+ messages in thread
From: Evgeniy Losev @ 2018-08-16 11:28 UTC (permalink / raw)
To: pgsql-admin
Hello.
We are running PostgreSQL 9.5.13 and we faced the very same issue as described in this thread: https://www.postgresql.org/message-id/B9BEA448-978F-4A14-A088-3FD82214FFAC%40pvv.ntnu.no
Briefly, we have got heavy update/insert/truncate activities on many tables and we're noticed that autovacuum processes hold a lot of file descriptors to deleted data-files (in sum >300GB).
The last answer from Tom Lane in that thread was:
"So far, my guess is that this is fixed by commits a1f064fc2 + d7598aeea."
Is there any patches/configuration recommendations to solve this problem yet?
Thank you.
--
Evgeniy Losev
Senior System Administrator
i-Digital
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2018-08-16 11:28 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-10-14 21:48 autovacuum with lots of open file references to deleted files Tore Halset <halset@pvv.ntnu.no>
2012-10-15 00:27 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-11-04 21:38 ` Tore Halset <halset@pvv.ntnu.no>
2012-11-05 00:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2012-11-05 01:40 ` Greg Williamson <gwilliamson39@yahoo.com>
2012-11-05 02:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2018-08-16 11:28 'autovacuum with lots of open file references to deleted files' back from 2012 Evgeniy Losev <e.losev@i-dgtl.ru>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox