Re: [ADMIN] vacuum toast loop?

Поиск
Список
Период
Сортировка
От gleeco
Тема Re: [ADMIN] vacuum toast loop?
Дата
Msg-id CADv14s8qPt8f2B5-0g05ThnMqjdDvkJ2QbOPnxPsmiNcd=37sQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: [ADMIN] vacuum toast loop?  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: [ADMIN] vacuum toast loop?
Список 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

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

Предыдущее
От: ProPAAS DBA
Дата:
Сообщение: Re: [ADMIN] Lock Error
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [ADMIN] vacuum toast loop?