pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedAutovacuum of pg_database
28+ messages / 8 participants
[nested] [flat]
Commits from this thread:
2016-05-10 15739393e4 2152762c53 7516cdb76a e9a273199f 92ebe509e3 ca4c6d043f Fix autovacuum for shared relations
* Autovacuum of pg_database
@ 2016-05-05 15:16 Ondřej Světlík <osvetlik@flexibee.eu>
0 siblings, 1 reply; 28+ messages in thread
From: Ondřej Světlík @ 2016-05-05 15:16 UTC (permalink / raw)
To: pgsql-admin
Hello again,
we have another strange problem with autovacuum. The process goes well
until it reaches the table pg_database (in some databases, not all),
then it loops over it forever (until I run vacuum full analyze on that
table, simple vacuum doesn't help):
2016-05-05 17:13:33 CEST 34818LOG: automatic vacuum of table
"somedatabase.pg_catalog.pg_database": index scans: 0
pages: 0 removed, 233 remain
tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
buffer usage: 87 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
2016-05-05 17:13:33 CEST 34819LOG: automatic vacuum of table
"somedatabase.pg_catalog.pg_database": index scans: 0
pages: 0 removed, 233 remain
tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
buffer usage: 87 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
2016-05-05 17:13:33 CEST 34820LOG: automatic vacuum of table
"somedatabase.pg_catalog.pg_database": index scans: 0
pages: 0 removed, 233 remain
tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
buffer usage: 87 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
2016-05-05 17:13:34 CEST 34821LOG: automatic vacuum of table
"somedatabase.pg_catalog.pg_database": index scans: 0
pages: 0 removed, 233 remain
tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
buffer usage: 87 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
Not sure if it's a bug or I'm doing something wrong.
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-05 15:19 Ondřej Světlík <osvetlik@flexibee.eu>
parent: Ondřej Světlík <osvetlik@flexibee.eu>
0 siblings, 1 reply; 28+ messages in thread
From: Ondřej Světlík @ 2016-05-05 15:19 UTC (permalink / raw)
To: pgsql-admin
Dne 5.5.2016 v 17:16 Ondřej Světlík napsal(a):
> Hello again,
>
> we have another strange problem with autovacuum. The process goes well
> until it reaches the table pg_database (in some databases, not all),
> then it loops over it forever (until I run vacuum full analyze on that
> table, simple vacuum doesn't help):
>
> 2016-05-05 17:13:33 CEST 34818LOG: automatic vacuum of table
> "somedatabase.pg_catalog.pg_database": index scans: 0
> pages: 0 removed, 233 remain
> tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
> buffer usage: 87 hits, 0 misses, 0 dirtied
> avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
> system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
> 2016-05-05 17:13:33 CEST 34819LOG: automatic vacuum of table
> "somedatabase.pg_catalog.pg_database": index scans: 0
> pages: 0 removed, 233 remain
> tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
> buffer usage: 87 hits, 0 misses, 0 dirtied
> avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
> system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
> 2016-05-05 17:13:33 CEST 34820LOG: automatic vacuum of table
> "somedatabase.pg_catalog.pg_database": index scans: 0
> pages: 0 removed, 233 remain
> tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
> buffer usage: 87 hits, 0 misses, 0 dirtied
> avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
> system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
> 2016-05-05 17:13:34 CEST 34821LOG: automatic vacuum of table
> "somedatabase.pg_catalog.pg_database": index scans: 0
> pages: 0 removed, 233 remain
> tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
> buffer usage: 87 hits, 0 misses, 0 dirtied
> avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
> system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
>
> Not sure if it's a bug or I'm doing something wrong.
>
> Thanks in advance,
>
> Ondřej
Another part of log shows, that the process tries pg_database twice,
than it does one other table and than back to pg_database for the rest
of our days:
2016-05-05 17:16:56 CEST 36675LOG: automatic vacuum of table
"someotherdb.pg_catalog.pg_database": index scans: 0
pages: 0 removed, 233 remain
tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
buffer usage: 36 hits, 50 misses, 2 dirtied
avg read rate: 3.277 MB/s, avg write rate: 0.131 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.11 sec
2016-05-05 17:16:56 CEST 36676LOG: automatic vacuum of table
"someotherdb.pg_catalog.pg_database": index scans: 0
pages: 0 removed, 233 remain
tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
buffer usage: 68 hits, 18 misses, 0 dirtied
avg read rate: 2.563 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.05 sec
2016-05-05 17:16:56 CEST 36674LOG: automatic vacuum of table
"someotherdb.pg_toast.pg_toast_2618": index scans: 1
pages: 58 removed, 50 remain
tuples: 141 removed, 219 remain, 0 are dead but not yet removable
buffer usage: 151 hits, 192 misses, 73 dirtied
avg read rate: 2.728 MB/s, avg write rate: 1.037 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.54 sec
2016-05-05 17:16:56 CEST 36677LOG: automatic vacuum of table
"someotherdb.pg_catalog.pg_database": index scans: 0
pages: 0 removed, 233 remain
tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
buffer usage: 86 hits, 0 misses, 0 dirtied
avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s
system usage: CPU 0.00s/0.00u sec elapsed 0.01 sec
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-05 15:35 Ondřej Světlík <osvetlik@flexibee.eu>
parent: Ondřej Světlík <osvetlik@flexibee.eu>
0 siblings, 1 reply; 28+ messages in thread
From: Ondřej Světlík @ 2016-05-05 15:35 UTC (permalink / raw)
To: pgsql-admin
Dne 5.5.2016 v 17:19 Ondřej Světlík napsal(a):
> Dne 5.5.2016 v 17:16 Ondřej Světlík napsal(a):
>> Hello again,
>>
>> we have another strange problem with autovacuum. The process goes well
>> until it reaches the table pg_database (in some databases, not all),
>> then it loops over it forever (until I run vacuum full analyze on that
>> table, simple vacuum doesn't help):
>>
>> 2016-05-05 17:13:33 CEST 34818LOG: automatic vacuum of table
>> "somedatabase.pg_catalog.pg_database": index scans: 0
>> pages: 0 removed, 233 remain
>> tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
>> buffer usage: 87 hits, 0 misses, 0 dirtied
And now the same goes for pg_authid. Sorry for spamming, I hope this is
the last one.
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-05 15:43 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Ondřej Světlík <osvetlik@flexibee.eu>
0 siblings, 3 replies; 28+ messages in thread
From: Alvaro Herrera @ 2016-05-05 15:43 UTC (permalink / raw)
To: Ondřej Světlík <osvetlik@flexibee.eu>; +Cc: pgsql-admin
Ondřej Světlík wrote:
> Dne 5.5.2016 v 17:19 Ondřej Světlík napsal(a):
> >Dne 5.5.2016 v 17:16 Ondřej Světlík napsal(a):
> >>Hello again,
> >>
> >>we have another strange problem with autovacuum. The process goes well
> >>until it reaches the table pg_database (in some databases, not all),
> >>then it loops over it forever (until I run vacuum full analyze on that
> >>table, simple vacuum doesn't help):
> >>
> >>2016-05-05 17:13:33 CEST 34818LOG: automatic vacuum of table
> >>"somedatabase.pg_catalog.pg_database": index scans: 0
> >> pages: 0 removed, 233 remain
> >> tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
> >> buffer usage: 87 hits, 0 misses, 0 dirtied
>
> And now the same goes for pg_authid. Sorry for spamming, I hope this is the
> last one.
These are all shared catalogs. There are others, so you may still see
more. We got another report for pg_database
https://www.postgresql.org/message-id/A9D40BB7-CFD6-46AF-A0A1-249F04878A2A%40amazon.com
so I suppose there really is a bug. I don't know what's going on there.
--
Á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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-05 15:49 Vicky Soni - Quipment India <vicky.soni@quipment.nl>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
2 siblings, 1 reply; 28+ messages in thread
From: Vicky Soni - Quipment India @ 2016-05-05 15:49 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; Ondřej Světlík <osvetlik@flexibee.eu>; +Cc: pgsql-admin
Hello,
I have observed that after upgrading from 9.3 to 9.4 on windows, autovacuum keep running again and again.
In 9.3 this was not true. Any recommendations?
Thanks & Regards,
Vicky Soni
Database Administrator
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Alvaro Herrera
Sent: 05 May 2016 21:14
To: Ondřej Světlík <osvetlik@flexibee.eu>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Autovacuum of pg_database
Ondřej Světlík wrote:
> Dne 5.5.2016 v 17:19 Ondřej Světlík napsal(a):
> >Dne 5.5.2016 v 17:16 Ondřej Světlík napsal(a):
> >>Hello again,
> >>
> >>we have another strange problem with autovacuum. The process goes
> >>well until it reaches the table pg_database (in some databases, not
> >>all), then it loops over it forever (until I run vacuum full
> >>analyze on that table, simple vacuum doesn't help):
> >>
> >>2016-05-05 17:13:33 CEST 34818LOG: automatic vacuum of table
> >>"somedatabase.pg_catalog.pg_database": index scans: 0
> >> pages: 0 removed, 233 remain
> >> tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
> >> buffer usage: 87 hits, 0 misses, 0 dirtied
>
> And now the same goes for pg_authid. Sorry for spamming, I hope this
> is the last one.
These are all shared catalogs. There are others, so you may still see more. We got another report for pg_database https://www.postgresql.org/message-id/A9D40BB7-CFD6-46AF-A0A1-249F04878A2A%40amazon.com
so I suppose there really is a bug. I don't know what's going on there.
--
Á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
--
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-05 15:51 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Vicky Soni - Quipment India <vicky.soni@quipment.nl>
0 siblings, 2 replies; 28+ messages in thread
From: Alvaro Herrera @ 2016-05-05 15:51 UTC (permalink / raw)
To: Vicky Soni - Quipment India <vicky.soni@quipment.nl>; +Cc: pgsql-admin
Vicky Soni - Quipment India wrote:
> Hello,
>
> I have observed that after upgrading from 9.3 to 9.4 on windows, autovacuum keep running again and again.
>
> In 9.3 this was not true. Any recommendations?
Maybe there is a multixact wraparound problem. What 9.3 were you
running previously, and what 9.4 did you upgrade to? Can you share the
output of pg_controldata? Please paste the output of this query
select datname, datminmxid from pg_database
--
Á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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-05 15:55 Ondřej Světlík <osvetlik@flexibee.eu>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 28+ messages in thread
From: Ondřej Světlík @ 2016-05-05 15:55 UTC (permalink / raw)
To: pgsql-admin
Dne 5.5.2016 v 17:51 Alvaro Herrera napsal(a):
> Vicky Soni - Quipment India wrote:
>> Hello,
>>
>> I have observed that after upgrading from 9.3 to 9.4 on windows, autovacuum keep running again and again.
>>
>> In 9.3 this was not true. Any recommendations?
>
> Maybe there is a multixact wraparound problem. What 9.3 were you
> running previously, and what 9.4 did you upgrade to? Can you share the
> output of pg_controldata? Please paste the output of this query
> select datname, datminmxid from pg_database
>
That sound's reasonable, our autovacuum problems started with processes
labeled as 'to prevent wraparound' (on pg_shdepend). We use 9.4.
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-05 15:56 Vicky Soni - Quipment India <vicky.soni@quipment.nl>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 0 replies; 28+ messages in thread
From: Vicky Soni - Quipment India @ 2016-05-05 15:56 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: pgsql-admin
Output of given query. Not sharing db names except temp database.
'datminmxid'
1
1
1
template0 16696
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
1
Thanks & Regards,
Vicky Soni
Database Administrator
-----Original Message-----
From: Alvaro Herrera [mailto:alvherre@2ndquadrant.com]
Sent: 05 May 2016 21:21
To: Vicky Soni - Quipment India <vicky.soni@quipment.nl>
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Autovacuum of pg_database
Vicky Soni - Quipment India wrote:
> Hello,
>
> I have observed that after upgrading from 9.3 to 9.4 on windows, autovacuum keep running again and again.
>
> In 9.3 this was not true. Any recommendations?
Maybe there is a multixact wraparound problem. What 9.3 were you running previously, and what 9.4 did you upgrade to? Can you share the output of pg_controldata? Please paste the output of this query
select datname, datminmxid from pg_database
--
Á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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-05 15:57 Ondřej Světlík <osvetlik@flexibee.eu>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
2 siblings, 1 reply; 28+ messages in thread
From: Ondřej Světlík @ 2016-05-05 15:57 UTC (permalink / raw)
To: pgsql-admin
Dne 5.5.2016 v 17:43 Alvaro Herrera napsal(a):
> Ondřej Světlík wrote:
>> Dne 5.5.2016 v 17:19 Ondřej Světlík napsal(a):
>>> Dne 5.5.2016 v 17:16 Ondřej Světlík napsal(a):
>>>> Hello again,
>>>>
>>>> we have another strange problem with autovacuum. The process goes well
>>>> until it reaches the table pg_database (in some databases, not all),
>>>> then it loops over it forever (until I run vacuum full analyze on that
>>>> table, simple vacuum doesn't help):
>>>>
>>>> 2016-05-05 17:13:33 CEST 34818LOG: automatic vacuum of table
>>>> "somedatabase.pg_catalog.pg_database": index scans: 0
>>>> pages: 0 removed, 233 remain
>>>> tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
>>>> buffer usage: 87 hits, 0 misses, 0 dirtied
>>
>> And now the same goes for pg_authid. Sorry for spamming, I hope this is the
>> last one.
>
> These are all shared catalogs. There are others, so you may still see
> more. We got another report for pg_database
> https://www.postgresql.org/message-id/A9D40BB7-CFD6-46AF-A0A1-249F04878A2A%40amazon.com
> so I suppose there really is a bug. I don't know what's going on there.
>
Let me know if I can help trace the cause.
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-06 06:26 Vicky Soni - Quipment India <vicky.soni@quipment.nl>
parent: Ondřej Světlík <osvetlik@flexibee.eu>
0 siblings, 0 replies; 28+ messages in thread
From: Vicky Soni - Quipment India @ 2016-05-06 06:26 UTC (permalink / raw)
To: Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
Hello,
In my development setup I promoted slave, and now autovacuum keep running again and again.
Specially on pg_statistics table of one of my OLTP using many temp tables in functions.
Please help.
Thanks & Regards,
Vicky Soni
Database Administrator
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ondrej Svetlík
Sent: 05 May 2016 21:28
To: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Autovacuum of pg_database
Dne 5.5.2016 v 17:43 Alvaro Herrera napsal(a):
> Ondřej Světlík wrote:
>> Dne 5.5.2016 v 17:19 Ondřej Světlík napsal(a):
>>> Dne 5.5.2016 v 17:16 Ondřej Světlík napsal(a):
>>>> Hello again,
>>>>
>>>> we have another strange problem with autovacuum. The process goes
>>>> well until it reaches the table pg_database (in some databases, not
>>>> all), then it loops over it forever (until I run vacuum full
>>>> analyze on that table, simple vacuum doesn't help):
>>>>
>>>> 2016-05-05 17:13:33 CEST 34818LOG: automatic vacuum of table
>>>> "somedatabase.pg_catalog.pg_database": index scans: 0
>>>> pages: 0 removed, 233 remain
>>>> tuples: 0 removed, 5817 remain, 0 are dead but not yet removable
>>>> buffer usage: 87 hits, 0 misses, 0 dirtied
>>
>> And now the same goes for pg_authid. Sorry for spamming, I hope this
>> is the last one.
>
> These are all shared catalogs. There are others, so you may still see
> more. We got another report for pg_database
> https://www.postgresql.org/message-id/A9D40BB7-CFD6-46AF-A0A1-249F0487
> 8A2A%40amazon.com so I suppose there really is a bug. I don't know
> what's going on there.
>
Let me know if I can help trace the cause.
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
--
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-06 14:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
2 siblings, 2 replies; 28+ messages in thread
From: Tom Lane @ 2016-05-06 14:17 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:
> These are all shared catalogs. There are others, so you may still see
> more. We got another report for pg_database
> https://www.postgresql.org/message-id/A9D40BB7-CFD6-46AF-A0A1-249F04878A2A%40amazon.com
> so I suppose there really is a bug. I don't know what's going on there.
I think it's pretty obvious: autovacuum.c's rule for detecting whether
some other worker is already processing table X is wrong when X is a
shared table. I propose the attached patch.
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
Attachments:
autovac-shared-table-handling.patch (text/x-diff, 7.9K) download
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index e2859df..4bc5d42 100644
*** a/src/backend/postmaster/autovacuum.c
--- b/src/backend/postmaster/autovacuum.c
*************** typedef struct autovac_table
*** 203,215 ****
* wi_links entry into free list or running list
* wi_dboid OID of the database this worker is supposed to work on
* wi_tableoid OID of the table currently being vacuumed, if any
* wi_proc pointer to PGPROC of the running worker, NULL if not started
* wi_launchtime Time at which this worker was launched
* wi_cost_* Vacuum cost-based delay parameters current in this worker
*
! * All fields are protected by AutovacuumLock, except for wi_tableoid which is
! * protected by AutovacuumScheduleLock (which is read-only for everyone except
! * that worker itself).
*-------------
*/
typedef struct WorkerInfoData
--- 203,216 ----
* wi_links entry into free list or running list
* wi_dboid OID of the database this worker is supposed to work on
* wi_tableoid OID of the table currently being vacuumed, if any
+ * wi_tableshared true if the table currently being vacuumed is a shared rel
* wi_proc pointer to PGPROC of the running worker, NULL if not started
* wi_launchtime Time at which this worker was launched
* wi_cost_* Vacuum cost-based delay parameters current in this worker
*
! * All fields are protected by AutovacuumLock, except for wi_tableoid and
! * wi_tableshared which are protected by AutovacuumScheduleLock (and are
! * read-only for everyone except that worker itself).
*-------------
*/
typedef struct WorkerInfoData
*************** typedef struct WorkerInfoData
*** 217,222 ****
--- 218,224 ----
dlist_node wi_links;
Oid wi_dboid;
Oid wi_tableoid;
+ bool wi_tableshared;
PGPROC *wi_proc;
TimestampTz wi_launchtime;
bool wi_dobalance;
*************** autovac_balance_cost(void)
*** 1791,1798 ****
}
if (worker->wi_proc != NULL)
! elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u, dobalance=%s cost_limit=%d, cost_limit_base=%d, cost_delay=%d)",
worker->wi_proc->pid, worker->wi_dboid, worker->wi_tableoid,
worker->wi_dobalance ? "yes" : "no",
worker->wi_cost_limit, worker->wi_cost_limit_base,
worker->wi_cost_delay);
--- 1793,1801 ----
}
if (worker->wi_proc != NULL)
! elog(DEBUG2, "autovac_balance_cost(pid=%u db=%u, rel=%u%s, dobalance=%s cost_limit=%d, cost_limit_base=%d, cost_delay=%d)",
worker->wi_proc->pid, worker->wi_dboid, worker->wi_tableoid,
+ worker->wi_tableshared ? " (shared)" : "",
worker->wi_dobalance ? "yes" : "no",
worker->wi_cost_limit, worker->wi_cost_limit_base,
worker->wi_cost_delay);
*************** do_autovacuum(void)
*** 1885,1893 ****
HeapScanDesc relScan;
Form_pg_database dbForm;
List *table_oids = NIL;
HASHCTL ctl;
HTAB *table_toast_map;
! ListCell *volatile cell;
PgStat_StatDBEntry *shared;
PgStat_StatDBEntry *dbentry;
BufferAccessStrategy bstrategy;
--- 1888,1898 ----
HeapScanDesc relScan;
Form_pg_database dbForm;
List *table_oids = NIL;
+ List *table_shares = NIL;
HASHCTL ctl;
HTAB *table_toast_map;
! ListCell *volatile lco;
! ListCell *volatile lcs;
PgStat_StatDBEntry *shared;
PgStat_StatDBEntry *dbentry;
BufferAccessStrategy bstrategy;
*************** do_autovacuum(void)
*** 2004,2009 ****
--- 2009,2015 ----
PgStat_StatTabEntry *tabentry;
AutoVacOpts *relopts;
Oid relid;
+ bool relisshared;
bool dovacuum;
bool doanalyze;
bool wraparound;
*************** do_autovacuum(void)
*** 2013,2022 ****
continue;
relid = HeapTupleGetOid(tuple);
/* Fetch reloptions and the pgstat entry for this table */
relopts = extract_autovac_opts(tuple, pg_class_desc);
! tabentry = get_pgstat_tabentry_relid(relid, classForm->relisshared,
shared, dbentry);
/* Check if it needs vacuum or analyze */
--- 2019,2029 ----
continue;
relid = HeapTupleGetOid(tuple);
+ relisshared = classForm->relisshared;
/* Fetch reloptions and the pgstat entry for this table */
relopts = extract_autovac_opts(tuple, pg_class_desc);
! tabentry = get_pgstat_tabentry_relid(relid, relisshared,
shared, dbentry);
/* Check if it needs vacuum or analyze */
*************** do_autovacuum(void)
*** 2069,2077 ****
}
else
{
! /* relations that need work are added to table_oids */
if (dovacuum || doanalyze)
table_oids = lappend_oid(table_oids, relid);
/*
* Remember the association for the second pass. Note: we must do
--- 2076,2087 ----
}
else
{
! /* relations that need work are added to table_oids/table_shares */
if (dovacuum || doanalyze)
+ {
table_oids = lappend_oid(table_oids, relid);
+ table_shares = lappend_int(table_shares, relisshared);
+ }
/*
* Remember the association for the second pass. Note: we must do
*************** do_autovacuum(void)
*** 2117,2122 ****
--- 2127,2133 ----
Form_pg_class classForm = (Form_pg_class) GETSTRUCT(tuple);
PgStat_StatTabEntry *tabentry;
Oid relid;
+ bool relisshared;
AutoVacOpts *relopts = NULL;
bool dovacuum;
bool doanalyze;
*************** do_autovacuum(void)
*** 2129,2134 ****
--- 2140,2146 ----
continue;
relid = HeapTupleGetOid(tuple);
+ relisshared = classForm->relisshared;
/*
* fetch reloptions -- if this toast table does not have them, try the
*************** do_autovacuum(void)
*** 2146,2152 ****
}
/* Fetch the pgstat entry for this table */
! tabentry = get_pgstat_tabentry_relid(relid, classForm->relisshared,
shared, dbentry);
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
--- 2158,2164 ----
}
/* Fetch the pgstat entry for this table */
! tabentry = get_pgstat_tabentry_relid(relid, relisshared,
shared, dbentry);
relation_needs_vacanalyze(relid, relopts, classForm, tabentry,
*************** do_autovacuum(void)
*** 2155,2161 ****
--- 2167,2176 ----
/* ignore analyze for toast tables */
if (dovacuum)
+ {
table_oids = lappend_oid(table_oids, relid);
+ table_shares = lappend_int(table_shares, relisshared);
+ }
}
heap_endscan(relScan);
*************** do_autovacuum(void)
*** 2181,2189 ****
/*
* Perform operations on collected tables.
*/
! foreach(cell, table_oids)
{
! Oid relid = lfirst_oid(cell);
autovac_table *tab;
bool skipit;
int stdVacuumCostDelay;
--- 2196,2205 ----
/*
* Perform operations on collected tables.
*/
! forboth(lco, table_oids, lcs, table_shares)
{
! Oid relid = lfirst_oid(lco);
! bool relisshared = (bool) lfirst_int(lcs);
autovac_table *tab;
bool skipit;
int stdVacuumCostDelay;
*************** do_autovacuum(void)
*** 2229,2243 ****
if (worker == MyWorkerInfo)
continue;
! /* ignore workers in other databases */
! if (worker->wi_dboid != MyDatabaseId)
continue;
! if (worker->wi_tableoid == relid)
! {
! skipit = true;
! break;
! }
}
LWLockRelease(AutovacuumLock);
if (skipit)
--- 2245,2261 ----
if (worker == MyWorkerInfo)
continue;
! /* not a match if tableoid or shared-rel flag don't match */
! if (worker->wi_tableoid != relid ||
! worker->wi_tableshared != relisshared)
continue;
! /* for unshared table, not a match unless same database */
! if (!relisshared && worker->wi_dboid != MyDatabaseId)
! continue;
!
! skipit = true;
! break;
}
LWLockRelease(AutovacuumLock);
if (skipit)
*************** do_autovacuum(void)
*** 2271,2276 ****
--- 2289,2295 ----
* the lock so that other workers don't vacuum it concurrently.
*/
MyWorkerInfo->wi_tableoid = relid;
+ MyWorkerInfo->wi_tableshared = relisshared;
LWLockRelease(AutovacuumScheduleLock);
/*
* Re: Autovacuum of pg_database
@ 2016-05-06 16:05 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 28+ messages in thread
From: Alvaro Herrera @ 2016-05-06 16:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > These are all shared catalogs. There are others, so you may still see
> > more. We got another report for pg_database
> > https://www.postgresql.org/message-id/A9D40BB7-CFD6-46AF-A0A1-249F04878A2A%40amazon.com
> > so I suppose there really is a bug. I don't know what's going on there.
>
> I think it's pretty obvious: autovacuum.c's rule for detecting whether
> some other worker is already processing table X is wrong when X is a
> shared table. I propose the attached patch.
Hmm, I have pretty much the same patch, except I added the flag to
struct autovac_table and have it populated by table_recheck_autovac.
Haven't tested this yet, which is why I hadn't posted it.
--
Á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
Attachments:
autovac-shared.patch (text/x-diff, 3.0K) download
diff --git a/src/backend/postmaster/autovacuum.c b/src/backend/postmaster/autovacuum.c
index e2859df..6bdaac5 100644
--- a/src/backend/postmaster/autovacuum.c
+++ b/src/backend/postmaster/autovacuum.c
@@ -190,6 +190,7 @@ typedef struct autovac_table
int at_vacuum_cost_delay;
int at_vacuum_cost_limit;
bool at_dobalance;
+ bool at_sharedrel;
char *at_relname;
char *at_nspname;
char *at_datname;
@@ -203,6 +204,7 @@ typedef struct autovac_table
* wi_links entry into free list or running list
* wi_dboid OID of the database this worker is supposed to work on
* wi_tableoid OID of the table currently being vacuumed, if any
+ * wi_sharedrel flag indicating whether table is marked relisshared
* wi_proc pointer to PGPROC of the running worker, NULL if not started
* wi_launchtime Time at which this worker was launched
* wi_cost_* Vacuum cost-based delay parameters current in this worker
@@ -220,6 +222,7 @@ typedef struct WorkerInfoData
PGPROC *wi_proc;
TimestampTz wi_launchtime;
bool wi_dobalance;
+ bool wi_sharedrel;
int wi_cost_delay;
int wi_cost_limit;
int wi_cost_limit_base;
@@ -717,6 +720,7 @@ AutoVacLauncherMain(int argc, char *argv[])
worker = AutoVacuumShmem->av_startingWorker;
worker->wi_dboid = InvalidOid;
worker->wi_tableoid = InvalidOid;
+ worker->wi_sharedrel = false;
worker->wi_proc = NULL;
worker->wi_launchtime = 0;
dlist_push_head(&AutoVacuumShmem->av_freeWorkers,
@@ -1683,6 +1687,7 @@ FreeWorkerInfo(int code, Datum arg)
dlist_delete(&MyWorkerInfo->wi_links);
MyWorkerInfo->wi_dboid = InvalidOid;
MyWorkerInfo->wi_tableoid = InvalidOid;
+ MyWorkerInfo->wi_sharedrel = false;
MyWorkerInfo->wi_proc = NULL;
MyWorkerInfo->wi_launchtime = 0;
MyWorkerInfo->wi_dobalance = false;
@@ -2229,8 +2234,8 @@ do_autovacuum(void)
if (worker == MyWorkerInfo)
continue;
- /* ignore workers in other databases */
- if (worker->wi_dboid != MyDatabaseId)
+ /* ignore workers in other databases (unless table is shared) */
+ if (!worker->wi_sharedrel && worker->wi_dboid != MyDatabaseId)
continue;
if (worker->wi_tableoid == relid)
@@ -2271,6 +2276,7 @@ do_autovacuum(void)
* the lock so that other workers don't vacuum it concurrently.
*/
MyWorkerInfo->wi_tableoid = relid;
+ MyWorkerInfo->wi_sharedrel = tab->at_sharedrel;
LWLockRelease(AutovacuumScheduleLock);
/*
@@ -2382,6 +2388,7 @@ deleted:
*/
LWLockAcquire(AutovacuumLock, LW_EXCLUSIVE);
MyWorkerInfo->wi_tableoid = InvalidOid;
+ MyWorkerInfo->wi_sharedrel = false;
LWLockRelease(AutovacuumLock);
/* restore vacuum cost GUCs for the next iteration */
@@ -2577,6 +2584,7 @@ table_recheck_autovac(Oid relid, HTAB *table_toast_map,
tab = palloc(sizeof(autovac_table));
tab->at_relid = relid;
+ tab->at_sharedrel = classForm->relisshared;
tab->at_vacoptions = VACOPT_SKIPTOAST |
(dovacuum ? VACOPT_VACUUM : 0) |
(doanalyze ? VACOPT_ANALYZE : 0) |
* Re: Autovacuum of pg_database
@ 2016-05-06 16:13 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 28+ messages in thread
From: Tom Lane @ 2016-05-06 16:13 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:
> Tom Lane wrote:
>> I think it's pretty obvious: autovacuum.c's rule for detecting whether
>> some other worker is already processing table X is wrong when X is a
>> shared table. I propose the attached patch.
> Hmm, I have pretty much the same patch, except I added the flag to
> struct autovac_table and have it populated by table_recheck_autovac.
> Haven't tested this yet, which is why I hadn't posted it.
Actually, I realized after looking at it that the new wi_tableshared
field is unnecessary in my patch. The only important part is that
knowledge of relisshared be available when we're looking for conflicting
workers, and that is entirely local in do_autovacuum(). I'd started the
patch by adding wi_tableshared, on the expectation that it would be
necessary, but it ain't ...
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-06 16:19 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 28+ messages in thread
From: Alvaro Herrera @ 2016-05-06 16:19 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > Tom Lane wrote:
> >> I think it's pretty obvious: autovacuum.c's rule for detecting whether
> >> some other worker is already processing table X is wrong when X is a
> >> shared table. I propose the attached patch.
>
> > Hmm, I have pretty much the same patch, except I added the flag to
> > struct autovac_table and have it populated by table_recheck_autovac.
> > Haven't tested this yet, which is why I hadn't posted it.
>
> Actually, I realized after looking at it that the new wi_tableshared
> field is unnecessary in my patch. The only important part is that
> knowledge of relisshared be available when we're looking for conflicting
> workers, and that is entirely local in do_autovacuum(). I'd started the
> patch by adding wi_tableshared, on the expectation that it would be
> necessary, but it ain't ...
OK, if I understand you correctly then that is what my patch does -- the
"sharedrel" flag in my patch is only inside do_autovacuum. Do you
already have a test rig for this? If not, I can set one up so that I
can push the patch hopefully later today.
--
Á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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-06 16:22 Greg Spiegelberg <gspiegelberg@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 1 reply; 28+ messages in thread
From: Greg Spiegelberg @ 2016-05-06 16:22 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
All,
We were bit a couple months ago by a very similar issue where autovacuum
ignored pg_type. A manual vacuum fixed it but since that table is abused
by every client using libpq which is darn near everything I suspect it went
bad in a hurry.
Question is, will this patch be backported to 9.3?
Thanks,
-Greg
On Fri, May 6, 2016 at 10:19 AM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:
> Tom Lane wrote:
> > Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > > Tom Lane wrote:
> > >> I think it's pretty obvious: autovacuum.c's rule for detecting whether
> > >> some other worker is already processing table X is wrong when X is a
> > >> shared table. I propose the attached patch.
> >
> > > Hmm, I have pretty much the same patch, except I added the flag to
> > > struct autovac_table and have it populated by table_recheck_autovac.
> > > Haven't tested this yet, which is why I hadn't posted it.
> >
> > Actually, I realized after looking at it that the new wi_tableshared
> > field is unnecessary in my patch. The only important part is that
> > knowledge of relisshared be available when we're looking for conflicting
> > workers, and that is entirely local in do_autovacuum(). I'd started the
> > patch by adding wi_tableshared, on the expectation that it would be
> > necessary, but it ain't ...
>
> OK, if I understand you correctly then that is what my patch does -- the
> "sharedrel" flag in my patch is only inside do_autovacuum. Do you
> already have a test rig for this? If not, I can set one up so that I
> can push the patch hopefully later today.
>
> --
> Á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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-06 16:33 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Greg Spiegelberg <gspiegelberg@gmail.com>
0 siblings, 1 reply; 28+ messages in thread
From: Alvaro Herrera @ 2016-05-06 16:33 UTC (permalink / raw)
To: Greg Spiegelberg <gspiegelberg@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
Hi Greg,
Greg Spiegelberg wrote:
> We were bit a couple months ago by a very similar issue where autovacuum
> ignored pg_type. A manual vacuum fixed it but since that table is abused
> by every client using libpq which is darn near everything I suspect it went
> bad in a hurry.
Hmm. The current report is about shared catalogs (pg_shdepend and
pg_database were reported as problematic) which pg_type is not, so I
doubt that this bugfix will have any impact in a problem vacuuming
pg_type.
I'm interested in seeing a more detailed report from you about the
pg_type vacuuming failure.
> Question is, will this patch be backported to 9.3?
Yes, in my opinion we would backpatch it, back to 9.1 even.
--
Á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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-06 16:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 1 reply; 28+ messages in thread
From: Tom Lane @ 2016-05-06 16:38 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:
> Tom Lane wrote:
>> Actually, I realized after looking at it that the new wi_tableshared
>> field is unnecessary in my patch. The only important part is that
>> knowledge of relisshared be available when we're looking for conflicting
>> workers, and that is entirely local in do_autovacuum(). I'd started the
>> patch by adding wi_tableshared, on the expectation that it would be
>> necessary, but it ain't ...
> OK, if I understand you correctly then that is what my patch does -- the
> "sharedrel" flag in my patch is only inside do_autovacuum. Do you
> already have a test rig for this?
I didn't attempt to replicate the problem report, if that's what you
mean; I just tested it as far as running the regression tests.
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-06 16:59 Greg Spiegelberg <gspiegelberg@gmail.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 28+ messages in thread
From: Greg Spiegelberg @ 2016-05-06 16:59 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
On Fri, May 6, 2016 at 10:33 AM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:
> Hi Greg,
>
> Greg Spiegelberg wrote:
>
> > We were bit a couple months ago by a very similar issue where autovacuum
> > ignored pg_type. A manual vacuum fixed it but since that table is abused
> > by every client using libpq which is darn near everything I suspect it
> went
> > bad in a hurry.
>
> Hmm. The current report is about shared catalogs (pg_shdepend and
> pg_database were reported as problematic) which pg_type is not, so I
> doubt that this bugfix will have any impact in a problem vacuuming
> pg_type.
>
> I'm interested in seeing a more detailed report from you about the
> pg_type vacuuming failure.
>
>
Not to steer the conversation elsewhere but the short of it is the system
in question is a very active 9.3.5, well aware of more current versions,
and in the early morning on Feb 8th before normal increase in activity and
in the span of a few minutes queries went from normal sub-second responses
to 30 sec to 5 minutes. Took hours to isolate because we never suspected
pg_type until logging was turned up. We had to suspend operations and
suspend pgbouncer to execute a manual VACUUM FULL. EXPLAIN ANALYZE before
reported hundreds of thousands of rows before the vacuum and <2,000 after.
All queries since have performed well since.
Stepping into the wayback machine, before VACUUM FULL:
Seq Scan on pg_type (cost=0.00..155188.24 rows=4788024 width=68) (actual
time=0.007..40951.243 rows=1889 loops=1)
Total runtime: 40952.907 ms
Attempted a simple VACUUM VERBOSE:
vacuum verbose pg_type;
INFO: vacuuming "pg_catalog.pg_type"
INFO: scanned index "pg_type_oid_index" to remove 860 row versions
DETAIL: CPU 0.98s/3.63u sec elapsed 96.52 sec.
INFO: scanned index "pg_type_typname_nsp_index" to remove 860 row versions
DETAIL: CPU 1.89s/7.14u sec elapsed 247.72 sec.
INFO: "pg_type": removed 860 row versions in 36 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.04 sec.
INFO: index "pg_type_oid_index" now contains 2014 row versions in 25824
pages
DETAIL: 715 index row versions were removed.
25717 index pages have been deleted, 25714 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_type_typname_nsp_index" now contains 2014 row versions in
35903 pages
DETAIL: 860 index row versions were removed.
35560 index pages have been deleted, 35488 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_type": found 163 removable, 1909 nonremovable row versions in
72 out of 107308 pages
DETAIL: 36 dead row versions cannot be removed yet.
There were 3056 unused item pointers.
0 pages are entirely empty.
CPU 2.88s/10.79u sec elapsed 344.29 sec.
INFO: "pg_type": stopping truncate due to conflicting lock request
VACUUM
The VACUUM FULL:
vacuum full verbose pg_type;
INFO: vacuuming "pg_catalog.pg_type"
INFO: "pg_type": found 42 removable, 1895 nonremovable row versions in
107308 pages
DETAIL: 4 dead row versions cannot be removed yet.
CPU 0.10s/0.11u sec elapsed 0.22 sec.
VACUUM
Time: 957.159 ms
EXPLAIN ANALYZE then and now:
Seq Scan on pg_type (cost=0.00..680.17 rows=1917 width=754) (actual
time=0.004..10.467 rows=1917 loops=1)
Total runtime: 10.999 ms
Autovacuum settings:
select name,setting from pg_settings where name ~ 'autov';
name | setting
-------------------------------------+-----------
autovacuum | on
autovacuum_analyze_scale_factor | 0.1
autovacuum_analyze_threshold | 50
autovacuum_freeze_max_age | 200000000
autovacuum_max_workers | 3
autovacuum_multixact_freeze_max_age | 400000000
autovacuum_naptime | 60
autovacuum_vacuum_cost_delay | 20
autovacuum_vacuum_cost_limit | -1
autovacuum_vacuum_scale_factor | 0.2
autovacuum_vacuum_threshold | 50
log_autovacuum_min_duration | -1
(12 rows)
I know, not much to go on.
> > Question is, will this patch be backported to 9.3?
>
> Yes, in my opinion we would backpatch it, back to 9.1 even.
>
>
Good news.
Thanks,
-Greg
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-06 17:25 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 28+ messages in thread
From: Alvaro Herrera @ 2016-05-06 17:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > Tom Lane wrote:
> >> Actually, I realized after looking at it that the new wi_tableshared
> >> field is unnecessary in my patch. The only important part is that
> >> knowledge of relisshared be available when we're looking for conflicting
> >> workers, and that is entirely local in do_autovacuum(). I'd started the
> >> patch by adding wi_tableshared, on the expectation that it would be
> >> necessary, but it ain't ...
>
> > OK, if I understand you correctly then that is what my patch does -- the
> > "sharedrel" flag in my patch is only inside do_autovacuum. Do you
> > already have a test rig for this?
>
> I didn't attempt to replicate the problem report, if that's what you
> mean; I just tested it as far as running the regression tests.
OK. I'll do some more targeted testing later before pushing.
--
Á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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-09 21:30 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 28+ messages in thread
From: Alvaro Herrera @ 2016-05-09 21:30 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
Tom Lane wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
> > These are all shared catalogs. There are others, so you may still see
> > more. We got another report for pg_database
> > https://www.postgresql.org/message-id/A9D40BB7-CFD6-46AF-A0A1-249F04878A2A%40amazon.com
> > so I suppose there really is a bug. I don't know what's going on there.
>
> I think it's pretty obvious: autovacuum.c's rule for detecting whether
> some other worker is already processing table X is wrong when X is a
> shared table. I propose the attached patch.
Now that I actually tried this, it turned out that this problem is not
so simple. vacuum.c already has logic to use conditional acquire of the
table-level lock, and if not available it skips the table:
LOG: skipping vacuum of "pg_shdepend" --- lock not available
so an autovacuum worker is never "stuck" behind another worker trying to
vacuum the table. This code is already in 9.2. I suppose the only way
for multiple workers to get stuck is the relatively new logic in
lazy_truncate_heap that retries multiple times when AEL is not
available. I haven't tried to replicate this yet.
In other words, the patches proposed here would not fix the actual
problem. Back to the drawing board, it seems.
--
Á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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-09 22:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 1 reply; 28+ messages in thread
From: Tom Lane @ 2016-05-09 22:30 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:
> Now that I actually tried this, it turned out that this problem is not
> so simple. vacuum.c already has logic to use conditional acquire of the
> table-level lock, and if not available it skips the table:
> LOG: skipping vacuum of "pg_shdepend" --- lock not available
> so an autovacuum worker is never "stuck" behind another worker trying to
> vacuum the table.
Hmm ... but then, how do we have the observed symptom of several workers
concurrently trying to process the same shared catalog? Seems like all
but one should fall out at this point.
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-09 22:42 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 3 replies; 28+ messages in thread
From: Tom Lane @ 2016-05-09 22:42 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
I wrote:
> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>> Now that I actually tried this, it turned out that this problem is not
>> so simple. vacuum.c already has logic to use conditional acquire of the
>> table-level lock, and if not available it skips the table:
>> LOG: skipping vacuum of "pg_shdepend" --- lock not available
>> so an autovacuum worker is never "stuck" behind another worker trying to
>> vacuum the table.
> Hmm ... but then, how do we have the observed symptom of several workers
> concurrently trying to process the same shared catalog? Seems like all
> but one should fall out at this point.
Oh, see table_recheck_autovac:
tab->at_vacoptions = VACOPT_SKIPTOAST |
(dovacuum ? VACOPT_VACUUM : 0) |
(doanalyze ? VACOPT_ANALYZE : 0) |
(!wraparound ? VACOPT_NOWAIT : 0);
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I'll bet they're all trying to do anti-wraparound vacuums. This would
explain why the problem hasn't been observed often enough to have been
fixed long since.
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-10 01:21 Bob Lunney <blunney@meetme.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 28+ messages in thread
From: Bob Lunney @ 2016-05-10 01:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
I had the same problem with 9.1 and vacuuming several databases simultaneously. We just lived with it. Other tables in the databases were far larger than pg_sharedepend, so it wasn't the most significant issue we faced.
Bob Lunney
> On May 9, 2016, at 6:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> I wrote:
>> Alvaro Herrera <alvherre@2ndquadrant.com> writes:
>>> Now that I actually tried this, it turned out that this problem is not
>>> so simple. vacuum.c already has logic to use conditional acquire of the
>>> table-level lock, and if not available it skips the table:
>>> LOG: skipping vacuum of "pg_shdepend" --- lock not available
>>> so an autovacuum worker is never "stuck" behind another worker trying to
>>> vacuum the table.
>
>> Hmm ... but then, how do we have the observed symptom of several workers
>> concurrently trying to process the same shared catalog? Seems like all
>> but one should fall out at this point.
>
> Oh, see table_recheck_autovac:
>
> tab->at_vacoptions = VACOPT_SKIPTOAST |
> (dovacuum ? VACOPT_VACUUM : 0) |
> (doanalyze ? VACOPT_ANALYZE : 0) |
> (!wraparound ? VACOPT_NOWAIT : 0);
> ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
>
> I'll bet they're all trying to do anti-wraparound vacuums. This would
> explain why the problem hasn't been observed often enough to have been
> fixed long since.
>
> 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
--
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-10 20:15 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 28+ messages in thread
From: Alvaro Herrera @ 2016-05-10 20:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Ondřej Světlík <osvetlik@flexibee.eu>; pgsql-admin
Tom Lane wrote:
> I'll bet they're all trying to do anti-wraparound vacuums. This would
> explain why the problem hasn't been observed often enough to have been
> fixed long since.
Ah, that should have been obvious, shouldn't it. I'm able to reproduce
the problem by moving relfrozenxid backwards for a shared catalog, and I
can confirm that it works correctly with the patch proposed, so I have
pushed it to all branches. (I only tested the problem scenario in 9.1
and master, since it's too much manual work).
Thanks for the report!
--
Á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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2016-05-11 12:06 Ondřej Světlík <osvetlik@flexibee.eu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
2 siblings, 0 replies; 28+ messages in thread
From: Ondřej Světlík @ 2016-05-11 12:06 UTC (permalink / raw)
To: pgsql-admin
>
> I'll bet they're all trying to do anti-wraparound vacuums. This would
> explain why the problem hasn't been observed often enough to have been
> fixed long since.
>
> regards, tom lane
>
You are right, sorry I didn't mention it sooner.
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] 28+ messages in thread
* Autovacuum of pg_database
@ 2017-05-15 08:55 Jakub Jedelský <jakub.jedelsky@gmail.com>
0 siblings, 1 reply; 28+ messages in thread
From: Jakub Jedelský @ 2017-05-15 08:55 UTC (permalink / raw)
To: pgsql-admin
Hi all,
we are facing problem, which was there some time ago: [1] and [2].
Autovacuum is hanging from time to time on a random database on shared
table pg_database.
We are using PostgreSQL 9.5.4 with patch provided by [3], but it's still
happening. I'm afraid there isn't problem with a multiple access to shared
table only, but with autovacuum/vacuum/? itself, which somehow hangs on
'vacuum freeze'. PIDs of a autovacuum workers are changing in log - so new
processes are forked every time (once or twice per second) - the log looks
same like in [2], so I'm not sending it again. It's possible to fix it with
manual run of vacuum freeze on pg_database of all DBs with
age(datfrozenxid) > autovacuum_freeze_max_age.
The problem (can) occurs when the pg_database table hits
`autovacuum_freeze_max_age` and I'm able to reporoduce it (but not always)
with this stupid reproducer:
* set autovacuum_freeze_min_age to it's minimum - 100000
* generating ~ 1000 DBs
* make some transactions to almost hit `autovacuum_freeze_max_age`
* `vacuum freeze` all tables in all databases except pg_database
* run a few transactions to hit `autovacuum_freeze_max_age`
About our backend:
One Postgresql server has ~1200 DBs. The problem occurs from time to time
on different servers. We use default values for vacuum_freeze_min_age,
vacuum_freeze_table_age and autovacuum_freeze_max_age.
autovacuum_max_workers is set to 6 workers. We have about 30 transactions
per second on a server in average (that hangs are on servers with higher
TPS).
Qs:
Was there any patch to a newer version, which I missed and which can solve
it? (We are planning upgrade to PostgreSQL 9.6, but it will take some time).
Is it possible to tune it with changing settings? (if I count it well, we
hit default autovacuum_freeze_max_age with ~30 TPS every ~ 77 days. But the
true is that I don't know if it's good or not :))
Or is there anything I missed? I don't think that my solution - monitor
logs and run vacuum freeze manually when the problem occurs - is the best
one.
Thanks a lot,
- jj.
[1]
https://www.postgresql.org/message-id/A9D40BB7-CFD6-46AF-A0A1-249F04878A2A%40amazon.com
[2] https://www.postgresql.org/message-id/572B63B1.3030603%40flexibee.eu
[3]
https://www.postgresql.org/message-id/E1b0Dwr-0003Ms-Mr@gemulon.postgresql.org
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2017-05-15 17:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jakub Jedelský <jakub.jedelsky@gmail.com>
0 siblings, 1 reply; 28+ messages in thread
From: Tom Lane @ 2017-05-15 17:29 UTC (permalink / raw)
To: Jakub Jedelský <jakub.jedelsky@gmail.com>; +Cc: pgsql-admin
=?UTF-8?Q?Jakub_Jedelsk=C3=BD?= <jakub.jedelsky@gmail.com> writes:
> The problem (can) occurs when the pg_database table hits
> `autovacuum_freeze_max_age` and I'm able to reporoduce it (but not always)
> with this stupid reproducer:
> * set autovacuum_freeze_min_age to it's minimum - 100000
> * generating ~ 1000 DBs
> * make some transactions to almost hit `autovacuum_freeze_max_age`
> * `vacuum freeze` all tables in all databases except pg_database
> * run a few transactions to hit `autovacuum_freeze_max_age`
I tried to reverse-engineer a test case out of this description,
without much success. If you have a script that can reproduce
the problem (even if not 100% success rate), could you share it?
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] 28+ messages in thread
* Re: Autovacuum of pg_database
@ 2017-05-15 19:26 Jakub Jedelsky <jakub.jedelsky@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 28+ messages in thread
From: Jakub Jedelsky @ 2017-05-15 19:26 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On Mon, May 15, 2017 at 01:29:28PM -0400, Tom Lane wrote:
> =?UTF-8?Q?Jakub_Jedelsk=C3=BD?= <jakub.jedelsky@gmail.com> writes:
> > The problem (can) occurs when the pg_database table hits
> > `autovacuum_freeze_max_age` and I'm able to reporoduce it (but not always)
> > with this stupid reproducer:
> > * set autovacuum_freeze_min_age to it's minimum - 100000
> > * generating ~ 1000 DBs
> > * make some transactions to almost hit `autovacuum_freeze_max_age`
> > * `vacuum freeze` all tables in all databases except pg_database
> > * run a few transactions to hit `autovacuum_freeze_max_age`
>
> I tried to reverse-engineer a test case out of this description,
> without much success. If you have a script that can reproduce
> the problem (even if not 100% success rate), could you share it?
>
> regards, tom lane
Thanks for reply. I will try to prepare something asap, but it can
take some time, so please be patient. (I just want to make you sure,
that I'm not ignoring that thread :))
Regards, jj.
--
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] 28+ messages in thread
end of thread, other threads:[~2017-05-15 19:26 UTC | newest]
Thread overview: 28+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-05-05 15:16 Autovacuum of pg_database Ondřej Světlík <osvetlik@flexibee.eu>
2016-05-05 15:19 ` Ondřej Světlík <osvetlik@flexibee.eu>
2016-05-05 15:35 ` Ondřej Světlík <osvetlik@flexibee.eu>
2016-05-05 15:43 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2016-05-05 15:49 ` Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-05 15:51 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2016-05-05 15:55 ` Ondřej Světlík <osvetlik@flexibee.eu>
2016-05-05 15:56 ` Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-05 15:57 ` Ondřej Světlík <osvetlik@flexibee.eu>
2016-05-06 06:26 ` Vicky Soni - Quipment India <vicky.soni@quipment.nl>
2016-05-06 14:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-05-06 16:05 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2016-05-06 16:13 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-05-06 16:19 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2016-05-06 16:22 ` Greg Spiegelberg <gspiegelberg@gmail.com>
2016-05-06 16:33 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2016-05-06 16:59 ` Greg Spiegelberg <gspiegelberg@gmail.com>
2016-05-06 16:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-05-06 17:25 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2016-05-09 21:30 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2016-05-09 22:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-05-09 22:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2016-05-10 01:21 ` Bob Lunney <blunney@meetme.com>
2016-05-10 20:15 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2016-05-11 12:06 ` Ondřej Světlík <osvetlik@flexibee.eu>
2017-05-15 08:55 Autovacuum of pg_database Jakub Jedelský <jakub.jedelsky@gmail.com>
2017-05-15 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-15 19:26 ` Jakub Jedelsky <jakub.jedelsky@gmail.com>
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