Обсуждение: Autovacuum of pg_shdepend
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
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
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
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
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
> 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
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
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
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
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