Autovaccum with cost_delay does not complete on one solaris 5.10 machine

Поиск
Список
Период
Сортировка
От Josh Berkus
Тема Autovaccum with cost_delay does not complete on one solaris 5.10 machine
Дата
Msg-id 4BC76C9F.1020200@agliodbs.com
обсуждение исходный текст
Ответы Re: Autovaccum with cost_delay does not complete on one solaris 5.10 machine
Список pgsql-performance
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

В списке pgsql-performance по дате отправления:

Предыдущее
От: "Kevin Grittner"
Дата:
Сообщение: Re: 8.3.9 - latency spikes with Linux (and tuning for consistently low latency)
Следующее
От: Alvaro Herrera
Дата:
Сообщение: Re: Autovaccum with cost_delay does not complete on one solaris 5.10 machine