pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum toast loop?
4+ messages / 2 participants
[nested] [flat]
* vacuum toast loop?
@ 2017-01-10 08:53 gleeco <gleeco@gmail.com>
2017-01-10 13:04 ` Re: vacuum toast loop? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: gleeco @ 2017-01-10 08:53 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuum toast loop?
2017-01-10 08:53 vacuum toast loop? gleeco <gleeco@gmail.com>
@ 2017-01-10 13:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-01-11 19:30 ` Re: vacuum toast loop? gleeco <gleeco@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2017-01-10 13:04 UTC (permalink / raw)
To: gleeco <gleeco@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuum toast loop?
2017-01-10 08:53 vacuum toast loop? gleeco <gleeco@gmail.com>
2017-01-10 13:04 ` Re: vacuum toast loop? Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-01-11 19:30 ` gleeco <gleeco@gmail.com>
2017-01-11 22:46 ` Re: vacuum toast loop? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: gleeco @ 2017-01-11 19:30 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: vacuum toast loop?
2017-01-10 08:53 vacuum toast loop? gleeco <gleeco@gmail.com>
2017-01-10 13:04 ` Re: vacuum toast loop? Tom Lane <tgl@sss.pgh.pa.us>
2017-01-11 19:30 ` Re: vacuum toast loop? gleeco <gleeco@gmail.com>
@ 2017-01-11 22:46 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2017-01-11 22:46 UTC (permalink / raw)
To: gleeco <gleeco@gmail.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2017-01-11 22:46 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-01-10 08:53 vacuum toast loop? gleeco <gleeco@gmail.com>
2017-01-10 13:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-01-11 19:30 ` gleeco <gleeco@gmail.com>
2017-01-11 22:46 ` Tom Lane <tgl@sss.pgh.pa.us>
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