Обсуждение: [ADMIN] vacuum toast loop?

Поиск
Список
Период
Сортировка

[ADMIN] vacuum toast loop?

От
gleeco
Дата:
hello - i've a long-running manual vacuum on a big size table.  It's many TB db that has some toast.  What is odd is that it keeps looping back on the same toast / index, eg.

INFO:  00000: "pg_toast_17283": removed 178956683 row versions in 52264585 pages
DETAIL:  CPU 737.22s/1145.35u sec elapsed 53850.35 sec.
LOCATION:  lazy_vacuum_heap, vacuumlazy.c:1169
INFO:  00000: scanned index "pg_toast_17283_index" to remove 178956681 row versions
DETAIL:  CPU 89.90s/215.38u sec elapsed 4121.92 sec.
LOCATION:  lazy_vacuum_index, vacuumlazy.c:1335
INFO:  00000: "pg_toast_17283": removed 178956681 row versions in 43893618 pages
DETAIL:  CPU 421.92s/1084.34u sec elapsed 23879.84 sec.
LOCATION:  lazy_vacuum_heap, vacuumlazy.c:1169
INFO:  00000: scanned index "pg_toast_17283_index" to remove 178956683 row versions
DETAIL:  CPU 95.21s/211.57u sec elapsed 3479.92 sec.
LOCATION:  lazy_vacuum_index, vacuumlazy.c:1335
INFO:  00000: "pg_toast_17283": removed 178956683 row versions in 43957868 pages
DETAIL:  CPU 459.10s/1202.59u sec elapsed 25266.61 sec.
LOCATION:  lazy_vacuum_heap, vacuumlazy.c:1169

..and so on.  Is this normal?  If so, when does the toast vacuum cease? 

thanks in advance!

-gleeco

Re: [ADMIN] vacuum toast loop?

От
Tom Lane
Дата:
gleeco <gleeco@gmail.com> writes:
> hello - i've a long-running manual vacuum on a big size table.  It's many
> TB db that has some toast.  What is odd is that it keeps looping back on
> the same toast / index, eg.

It's not looping ... it's filling up your maintenance_work_mem (which is
evidently set to 1GB) with dead TIDs and then pausing to do a cycle of
index cleaning.  You have evidently let that table get, um, severely
bloated.  Was autovacuum off?

            regards, tom lane

Re: [ADMIN] vacuum toast loop?

От
gleeco
Дата:
Yes - severe bloat on this toast table is quite true.  I'm trying to get things righted.

A bit more info:

* autovacuum was on, runtime was weeks...
* maintenace_work_mem is higher than 1G
* there is a huge amount of mutation & updates
* ballpark is 500M rows
* after 'looping' on the vacuum of this toast, i see:

    relname     | n_live_tup | n_dead_tup
----------------+------------+------------
 pg_toast_17283 |  173145066 | 2461775286


Main question now:  is there actually new work getting done when it repeats?  ie. am i making progress?

thanks in advance!

-gleeco


On Tue, Jan 10, 2017 at 5:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
gleeco <gleeco@gmail.com> writes:
> hello - i've a long-running manual vacuum on a big size table.  It's many
> TB db that has some toast.  What is odd is that it keeps looping back on
> the same toast / index, eg.

It's not looping ... it's filling up your maintenance_work_mem (which is
evidently set to 1GB) with dead TIDs and then pausing to do a cycle of
index cleaning.  You have evidently let that table get, um, severely
bloated.  Was autovacuum off?

                        regards, tom lane



--

-gleeco

Re: [ADMIN] vacuum toast loop?

От
Tom Lane
Дата:
gleeco <gleeco@gmail.com> writes:
> Main question now:  is there actually new work getting done when it
> repeats?  ie. am i making progress?

Yes, I would expect so, especially since the tuple counts are varying
a bit.  If it were always exactly the same tuple count then there might
be some reason to worry.  If you weren't seeing any verbose output at
all, there would be much more reason to worry.

(FWIW, as of 9.6 or thereabouts there's some support for vacuum progress
tracking via pg_stat_activity.  Won't help you now, but it's out there.)

            regards, tom lane