pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum of pg_shdepend
10+ messages / 5 participants
[nested] [flat]
Commits from this thread:
2016-05-10 15739393e4 2152762c53 7516cdb76a e9a273199f 92ebe509e3 ca4c6d043f Fix autovacuum for shared relations
* Autovacuum of pg_shdepend
@ 2016-05-04 15:08 Ondřej Světlík <osvetlik@flexibee.eu>
0 siblings, 2 replies; 10+ messages in thread
From: Ondřej Světlík @ 2016-05-04 15:08 UTC (permalink / raw)
To: pgsql-admin
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
--
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] 10+ messages in thread
* Re: Autovacuum of pg_shdepend
@ 2016-05-04 15:25 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Ondřej Světlík <osvetlik@flexibee.eu>
1 sibling, 2 replies; 10+ messages in thread
From: Alvaro Herrera @ 2016-05-04 15:25 UTC (permalink / raw)
To: Ondřej Světlík <osvetlik@flexibee.eu>; +Cc: pgsql-admin
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
--
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] 10+ messages in thread
* Re: Autovacuum of pg_shdepend
@ 2016-05-04 18:52 Victor Yegorov <vyegorov@gmail.com>
parent: Ondřej Světlík <osvetlik@flexibee.eu>
1 sibling, 2 replies; 10+ messages in thread
From: Victor Yegorov @ 2016-05-04 18:52 UTC (permalink / raw)
To: Ondřej Světlík <osvetlik@flexibee.eu>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Autovacuum of pg_shdepend
@ 2016-05-04 19:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2016-05-04 19:07 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
Alvaro Herrera <alvherre@2ndquadrant.com> 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
--
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] 10+ messages in thread
* Re: Autovacuum of pg_shdepend
@ 2016-05-04 19:09 Ondřej Světlík <osvetlik@flexibee.eu>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 10+ messages in thread
From: Ondřej Světlík @ 2016-05-04 19:09 UTC (permalink / raw)
To: pgsql-admin
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
--
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] 10+ messages in thread
* Re: Autovacuum of pg_shdepend
@ 2016-05-04 19:12 Ondřej Světlík <osvetlik@flexibee.eu>
parent: Victor Yegorov <vyegorov@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Ondřej Světlík @ 2016-05-04 19:12 UTC (permalink / raw)
To: pgsql-admin
> 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
--
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] 10+ messages in thread
* Re: Autovacuum of pg_shdepend
@ 2016-05-04 20:08 Ondřej Světlík <osvetlik@flexibee.eu>
parent: Victor Yegorov <vyegorov@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Ondřej Světlík @ 2016-05-04 20:08 UTC (permalink / raw)
To: pgsql-admin
Dne 4.5.2016 v 20:52 Victor Yegorov napsal(a):
> 2016-05-04 18:08 GMT+03:00 Ondřej Světlík <osvetlik@flexibee.eu
> <mailto: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
Thank you very much, we have a working autovacuum on one of our clusters
again. That's awesome :-).
Ondřej
--
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] 10+ messages in thread
* Re: Autovacuum of pg_shdepend
@ 2016-05-04 20:27 Ondřej Světlík <osvetlik@flexibee.eu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 10+ messages in thread
From: Ondřej Světlík @ 2016-05-04 20:27 UTC (permalink / raw)
To: pgsql-admin
Dne 4.5.2016 v 21:07 Tom Lane napsal(a):
> Alvaro Herrera <alvherre@2ndquadrant.com> 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
--
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] 10+ messages in thread
* Re: Autovacuum of pg_shdepend
@ 2016-08-02 23:02 Ondřej Světlík <osvetlik@flexibee.eu>
parent: Ondřej Světlík <osvetlik@flexibee.eu>
0 siblings, 1 reply; 10+ messages in thread
From: Ondřej Světlík @ 2016-08-02 23:02 UTC (permalink / raw)
To: pgsql-admin
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 <osvetlik@flexibee.eu
>> <mailto: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
>
> 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
--
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] 10+ messages in thread
* Re: Autovacuum of pg_shdepend
@ 2016-08-03 07:11 Albe Laurenz <laurenz.albe@wien.gv.at>
parent: Ondřej Světlík <osvetlik@flexibee.eu>
0 siblings, 0 replies; 10+ messages in thread
From: Albe Laurenz @ 2016-08-03 07:11 UTC (permalink / raw)
To: 'Ondrej Svetlík *EXTERN*' <osvetlik@flexibee.eu>; pgsql-admin
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
--
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] 10+ messages in thread
end of thread, other threads:[~2016-08-03 07:11 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-05-04 15:08 Autovacuum of pg_shdepend Ondřej Světlík <osvetlik@flexibee.eu>
2016-05-04 15:25 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2016-05-04 19:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-05-04 20:27 ` Ondřej Světlík <osvetlik@flexibee.eu>
2016-05-04 19:09 ` Ondřej Světlík <osvetlik@flexibee.eu>
2016-05-04 18:52 ` Victor Yegorov <vyegorov@gmail.com>
2016-05-04 19:12 ` Ondřej Světlík <osvetlik@flexibee.eu>
2016-05-04 20:08 ` Ondřej Světlík <osvetlik@flexibee.eu>
2016-08-02 23:02 ` Ondřej Světlík <osvetlik@flexibee.eu>
2016-08-03 07:11 ` Albe Laurenz <laurenz.albe@wien.gv.at>
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