Autovacuum of pg_shdepend

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

Autovacuum of pg_shdepend

От:
Ondřej Světlík <osvetlik@flexibee.eu>
Дата:
Hello,

we have a strange problem with autovacuum. We have three workers, but 
usually only one works and two are waiting as they are all trying to 
process table pg_shdepend which is shared between all databases.

We have two clusters, both have a few thousands of databases. Because of 
this problem the autovacuum process can't keep up and the planner uses 
old data causing large impact on the performance.

Is there a way to prevent this behaviour, please?

With regards

Ondřej Světlík

Re: Autovacuum of pg_shdepend

От:
Alvaro Herrera <alvherre@2ndquadrant.com>
Дата:
Ondřej Světlík wrote:
> Hello,
> 
> we have a strange problem with autovacuum. We have three workers, but
> usually only one works and two are waiting as they are all trying to process
> table pg_shdepend which is shared between all databases.

Ooh, interesting bug.

As a workaround I suggest connecting to all of your databases except one
and disabling autovacuum for pg_shdepend.  Then the table will be
processed in the one database where you left it enabled, and the other
workers will take care of the rest of the tables.

I'll think about a real fix.

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Autovacuum of pg_shdepend

От:
Victor Yegorov <vyegorov@gmail.com>
Дата:
2016-05-04 18:08 GMT+03:00 Ondřej Světlík <osvetlik@flexibee.eu>:
we have a strange problem with autovacuum. We have three workers, but usually only one works and two are waiting as they are all trying to process table pg_shdepend which is shared between all databases.

I hit the same issue when we've migrated out all large objects (~200Gb total), I've executed `vacuumlo` and then all autovacuums started to process `pg_shdepend`.
They've been picking on this table over and over again from different databases, but were stopping on the truncation phase due to other autovacuums were waiting on the same table.

I ended up manually vacuuming this table in the database, that was cleaned up, and also in the `postgres` DB. It fixed the case for me.
This was on 9.0.23 though.

--
Victor Y. Yegorov

Re: Autovacuum of pg_shdepend

От:
Tom Lane <tgl@sss.pgh.pa.us>
Дата:
Alvaro Herrera  writes:
> Ooh, interesting bug.

> As a workaround I suggest connecting to all of your databases except one
> and disabling autovacuum for pg_shdepend.  Then the table will be
> processed in the one database where you left it enabled, and the other
> workers will take care of the rest of the tables.

> I'll think about a real fix.

Seems like a simple answer is to consider all shared catalogs to "belong"
to only one database for autovac purposes, ie, only autovac workers in
that database would consider vacuuming them.  The problem IIUC is that the
interlock against multiple workers glomming onto the same table only
considers other workers in the same DB.

The fun part might be to decide which DB that is.  I don't think we should
depend on any of the standard databases always being there.

			regards, tom lane

Re: Autovacuum of pg_shdepend

От:
Ondřej Světlík <osvetlik@flexibee.eu>
Дата:
Dne 4.5.2016 v 17:25 Alvaro Herrera napsal(a):
> Ondřej Světlík wrote:
>> Hello,
>>
>> we have a strange problem with autovacuum. We have three workers, but
>> usually only one works and two are waiting as they are all trying to process
>> table pg_shdepend which is shared between all databases.
>
> Ooh, interesting bug.
>
> As a workaround I suggest connecting to all of your databases except one
> and disabling autovacuum for pg_shdepend.  Then the table will be
> processed in the one database where you left it enabled, and the other
> workers will take care of the rest of the tables.
>
> I'll think about a real fix.
>


Thank you, Alvaro,

we have already tried this, but we are not permitted to alter system 
tables. Changing allow_system_table_mods doesn't seem safe enough, is 
there another way to achieve this?

Oh, I forgot to mention this is 9.4.7.

Thanks,

Ondřej

Re: Autovacuum of pg_shdepend

От:
Ondřej Světlík <osvetlik@flexibee.eu>
Дата:
> I hit the same issue when we've migrated out all large objects (~200Gb
> total), I've executed `vacuumlo` and then all autovacuums started to
> process `pg_shdepend`.
> They've been picking on this table over and over again from different
> databases, but were stopping on the truncation phase due to other
> autovacuums were waiting on the same table.
>
> I ended up manually vacuuming this table in the database, that was
> cleaned up, and also in the `postgres` DB. It fixed the case for me.
> This was on 9.0.23 though.
>
> --
> Victor Y. Yegorov


Thank you very much, Victor, I am going to try your solution. In fact, 
I'm already trying :-).

With regards,

Ondřej

Re: Autovacuum of pg_shdepend

От:
Ondřej Světlík <osvetlik@flexibee.eu>
Дата:
Dne 4.5.2016 v 20:52 Victor Yegorov napsal(a):
> 2016-05-04 18:08 GMT+03:00 Ondřej Světlík  >:
>
>     we have a strange problem with autovacuum. We have three workers,
>     but usually only one works and two are waiting as they are all
>     trying to process table pg_shdepend which is shared between all
>     databases.
>
>
> I hit the same issue when we've migrated out all large objects (~200Gb
> total), I've executed `vacuumlo` and then all autovacuums started to
> process `pg_shdepend`.
> They've been picking on this table over and over again from different
> databases, but were stopping on the truncation phase due to other
> autovacuums were waiting on the same table.
>
> I ended up manually vacuuming this table in the database, that was
> cleaned up, and also in the `postgres` DB. It fixed the case for me.
> This was on 9.0.23 though.
>
> --
> Victor Y. Yegorov

Thank you very much, we have a working autovacuum on one of our clusters 
again. That's awesome :-).

Ondřej

Re: Autovacuum of pg_shdepend

От:
Ondřej Světlík <osvetlik@flexibee.eu>
Дата:
Dne 4.5.2016 v 21:07 Tom Lane napsal(a):
> Alvaro Herrera  writes:
>> Ooh, interesting bug.
>
>> As a workaround I suggest connecting to all of your databases except one
>> and disabling autovacuum for pg_shdepend.  Then the table will be
>> processed in the one database where you left it enabled, and the other
>> workers will take care of the rest of the tables.
>
>> I'll think about a real fix.
>
> Seems like a simple answer is to consider all shared catalogs to "belong"
> to only one database for autovac purposes, ie, only autovac workers in
> that database would consider vacuuming them.  The problem IIUC is that the
> interlock against multiple workers glomming onto the same table only
> considers other workers in the same DB.
>
> The fun part might be to decide which DB that is.  I don't think we should
> depend on any of the standard databases always being there.
>
> 			regards, tom lane
>

How about a new configuration option, something like

#autovacuum_maintenance_database = postgres

Ondřej

Re: Autovacuum of pg_shdepend

От:
Ondřej Světlík <osvetlik@flexibee.eu>
Дата:
Dne 4.5.2016 v 22:08 Ondřej Světlík napsal(a):
> Dne 4.5.2016 v 20:52 Victor Yegorov napsal(a):
>> 2016-05-04 18:08 GMT+03:00 Ondřej Světlík > >:
>>
>>     we have a strange problem with autovacuum. We have three workers,
>>     but usually only one works and two are waiting as they are all
>>     trying to process table pg_shdepend which is shared between all
>>     databases.
>>
>>
>> I hit the same issue when we've migrated out all large objects (~200Gb
>> total), I've executed `vacuumlo` and then all autovacuums started to
>> process `pg_shdepend`.
>> They've been picking on this table over and over again from different
>> databases, but were stopping on the truncation phase due to other
>> autovacuums were waiting on the same table.
>>
>> I ended up manually vacuuming this table in the database, that was
>> cleaned up, and also in the `postgres` DB. It fixed the case for me.
>> This was on 9.0.23 though.
>>
>> --
>> Victor Y. Yegorov
>
> Thank you very much, we have a working autovacuum on one of our clusters
> again. That's awesome :-).
>
> Ondřej


Hello again,

as this is still not fixed in any released version and the problem 
occurred again, let me ask one question. Which form of manual VACUUM is 
sufficient to prevent wraparound? Is it VACUUM FREEZE or do I have to 
use VACUUM FULL?

Thanks in advance,

Ondřej

Re: Autovacuum of pg_shdepend

От:
Albe Laurenz <laurenz.albe@wien.gv.at>
Дата:
Ondrej Svetlík wrote:
> as this is still not fixed in any released version and the problem
> occurred again, let me ask one question. Which form of manual VACUUM is
> sufficient to prevent wraparound? Is it VACUUM FREEZE or do I have to
> use VACUUM FULL?

Plain VACUUM will do.

Yours,
Laurenz Albe
FAQ