From: Josh Berkus <josh@agliodbs.com>
To: pgsql-performance@postgresql.org
Subject: Autovaccum with cost_delay does not complete on one solaris 5.10 machine
Date: Thu, 15 Apr 2010 12:44:31 -0700
Message-ID: <4BC76C9F.1020200@agliodbs.com> (raw)
All,
We're having a very strange problem where autovacuum does not complete
on a Postgres 8.3.8/Solaris 5.10 system. The reason I say strange is:
this is one of a twin pair of identical systems,and the other system
does not have this issue.
Basically, vacuuming of a table which normally takes about 20 minutes
interactively with vacuum_cost_delay set to 20 had not completed after
14 hours. When I trussed it, I saw activity which indicated to me that
autovacuum was doing a pollsys, presumably for cost_limit, every data page.
Autovacuum was running with vacuum_cost_limit = 200 and
autovacuum_vacuum_cost_delay = 20, which I believe is the default for 8.3.
Truss output:
pollsys(0xFFFFFD7FFFDF83E0, 0, 0xFFFFFD7FFFDF8470, 0x00000000) = 0
lseek(4, 0x2AD9C000, SEEK_SET) = 0x2AD9C000
write(4, " L\v\0\010F0F8 a01\0\0\0".., 8192) = 8192
lseek(4, 0x2ADDC000, SEEK_SET) = 0x2ADDC000
read(4, " L\v\0\0108FFD a01\0\0\0".., 8192) = 8192
pollsys(0xFFFFFD7FFFDF83E0, 0, 0xFFFFFD7FFFDF8470, 0x00000000) = 0
lseek(4, 0x2AD9E000, SEEK_SET) = 0x2AD9E000
write(4, " L\v\0\0 X15F9 a01\0\0\0".., 8192) = 8192
lseek(4, 0x2ADDE000, SEEK_SET) = 0x2ADDE000
read(4, " L\v\0\080B0FD a01\0\0\0".., 8192) = 8192
pollsys(0xFFFFFD7FFFDF83E0, 0, 0xFFFFFD7FFFDF8470, 0x00000000) = 0
lseek(4, 0x2ADA0000, SEEK_SET) = 0x2ADA0000
write(4, " L\v\0\0D0 6F9 a01\0\0\0".., 8192) = 8192
lseek(4, 0x2ADE0000, SEEK_SET) = 0x2ADE0000
read(4, " L\v\0\0F8D1FD a01\0\0\0".., 8192) = 8192
Note that this is VERY different from the truss output for a manual
vacuum on the same machine (although I think the above is an index and
the below is the main table):
pollsys(0xFFFFFD7FFFDF88C0, 0, 0xFFFFFD7FFFDF8950, 0x00000000) = 0
read(14, " (\v\0\010\v 19501\001\0".., 8192) = 8192
read(14, " !\v\0\0B8 qFF9701\001\0".., 8192) = 8192
read(14, " -\v\0\08895 WBC01\001\0".., 8192) = 8192
read(14, " (\v\0\0\b I 19501\001\0".., 8192) = 8192
read(14, " :\v\0\0 ( ;BCCD01\001\0".., 8192) = 8192
read(14, " (\v\0\0 @89 19501\001\0".., 8192) = 8192
read(14, " D\v\0\0B0 7 e l01\001\0".., 8192) = 8192
read(14, " (\v\0\0B0C7 19501\001\0".., 8192) = 8192
read(14, " -\v\0\0B8 5 XBC01\001\0".., 8192) = 8192
read(14, " (\v\0\0 ( 3 29501\001\0".., 8192) = 8192
read(14, " (\v\0\0 X R 29501\001\0".., 8192) = 8192
read(14, " :\v\0\0 CEBCCD01\001\0".., 8192) = 8192
read(14, " !\v\0\0D8A0 9801\001\0".., 8192) = 8192
read(14, " (\v\0\0C0C6 29501\001\0".., 8192) = 8192
read(14, "1C\v\0\0D0 u g [01\001\0".., 8192) = 8192
read(14, " !\v\0\0A0 `81 [01\001\0".., 8192) = 8192
read(14, " -\v\0\0 0ED XBC01\001\0".., 8192) = 8192
read(14, " 7\v\0\0C8 UECD901\001\0".., 8192) = 8192
read(14, "1A\v\0\0107F W z01\001\0".., 8192) = 8192
read(14, " !\v\0\0 p ZB5A401\001\0".., 8192) = 8192
read(14, " -\v\0\0A0D5 YBC01\001\0".., 8192) = 8192
read(14, " z\v\0\0 81AFB9A01\001\0".., 8192) = 8192
read(14, "1A\v\0\080 { X z01\001\0".., 8192) = 8192
read(14, " (\v\0\080 ] 39501\001\0".., 8192) = 8192
read(14, " (\v\0\0A8 | 39501\001\0".., 8192) = 8192
read(14, " :\v\0\0\09ABDCD01\001\0".., 8192) = 8192
Ideas on where to look next, anyone?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: josh@agliodbs.com
Subject: Re: Autovaccum with cost_delay does not complete on one solaris 5.10 machine
In-Reply-To: <4BC76C9F.1020200@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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