Re: [ADMIN] autovacuum with lots of open file references to deleted files
On Oct 15, 2012, at 2:27 AM, Tom Lane t...@sss.pgh.pa.us wrote: Tore Halset hal...@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. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] autovacuum with lots of open file references to deleted files
Tore Halset hal...@pvv.ntnu.no writes: On Oct 15, 2012, at 2:27 AM, Tom Lane t...@sss.pgh.pa.us wrote: Tore Halset hal...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] autovacuum with lots of open file references to deleted files
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. -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] autovacuum with lots of open file references to deleted files
Greg Williamson gwilliamso...@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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
