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
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
