pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_upgrade and frozen xids
8+ messages / 4 participants
[nested] [flat]
* pg_upgrade and frozen xids
@ 2018-03-06 23:12 Natalie Wenz <nataliewenz@ebureau.com>
2018-03-06 23:25 ` Re: pg_upgrade and frozen xids Peter Geoghegan <pg@bowt.ie>
0 siblings, 1 reply; 8+ messages in thread
From: Natalie Wenz @ 2018-03-06 23:12 UTC (permalink / raw)
To: pgsql-admin
What happens to the xid of rows/tables when you upgrade postgres via pg_upgrade?
I upgraded a very large database from 9.6 to 10.1 via pg_upgrade recently, and ever since, the auto vacuum has been busy on a large legacy table that has experienced no changes since the upgrade. If the whole table had been frozen prior to the upgrade, would you expect it to stay frozen?
Thanks,
Natalie
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_upgrade and frozen xids
2018-03-06 23:12 pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
@ 2018-03-06 23:25 ` Peter Geoghegan <pg@bowt.ie>
2018-03-06 23:41 ` Re: pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Geoghegan @ 2018-03-06 23:25 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; +Cc: pgsql-admin
On Tue, Mar 6, 2018 at 3:12 PM, Natalie Wenz <nataliewenz@ebureau.com> wrote:
> What happens to the xid of rows/tables when you upgrade postgres via pg_upgrade?
>
> I upgraded a very large database from 9.6 to 10.1 via pg_upgrade recently, and ever since, the auto vacuum has been busy on a large legacy table that has experienced no changes since the upgrade. If the whole table had been frozen prior to the upgrade, would you expect it to stay frozen?
Yes, you're right to expect it to stay frozen.
I wonder if this is due to a (thankfully relatively benign) bug that
was fixed very recently [1]. Are the relations that autovacuum is
processing materialized views?
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=38b41f182a66b67e36e2adf53d078599b1b65...
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_upgrade and frozen xids
2018-03-06 23:12 pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
2018-03-06 23:25 ` Re: pg_upgrade and frozen xids Peter Geoghegan <pg@bowt.ie>
@ 2018-03-06 23:41 ` Natalie Wenz <nataliewenz@ebureau.com>
2018-03-07 18:47 ` Re: pg_upgrade and frozen xids bricklen <bricklen@gmail.com>
2018-03-07 20:11 ` Re: pg_upgrade and frozen xids Peter Geoghegan <pg@bowt.ie>
0 siblings, 2 replies; 8+ messages in thread
From: Natalie Wenz @ 2018-03-06 23:41 UTC (permalink / raw)
To: pgsql-admin; +Cc: Peter Geoghegan <pg@bowt.ie>
They are not, unfortunately (fortunately?). Just a standard table.
If it helps, the autovacuum always seems to be in the phase "cleaning up indexes" when I look at pg_stat_progress_vacuum.
> On Mar 6, 2018, at 5:25 PM, Peter Geoghegan <pg@bowt.ie> wrote:
>
> Are the relations that autovacuum is
> processing materialized views?
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_upgrade and frozen xids
2018-03-06 23:12 pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
2018-03-06 23:25 ` Re: pg_upgrade and frozen xids Peter Geoghegan <pg@bowt.ie>
2018-03-06 23:41 ` Re: pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
@ 2018-03-07 18:47 ` bricklen <bricklen@gmail.com>
2018-03-07 19:48 ` Re: pg_upgrade and frozen xids Alvaro Herrera <alvherre@2ndquadrant.com>
1 sibling, 1 reply; 8+ messages in thread
From: bricklen @ 2018-03-07 18:47 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; +Cc: pgsql-admin
On Tue, Mar 6, 2018 at 3:41 PM, Natalie Wenz <nataliewenz@ebureau.com>
wrote:
> They are not, unfortunately (fortunately?). Just a standard table.
>
> If it helps, the autovacuum always seems to be in the phase "cleaning up
> indexes" when I look at pg_stat_progress_vacuum.
>
We recently upgraded about 1500 Postgres clusters from 9.3 to 10.1 and we
are seeing similar issues. Specifically:
* An autovacuum worker stuck vacuuming regular tables for days until they
are killed (I've seen "vacuuming indexes" phase), and they are showing 99%
CPU in top. pg_cancel|terminate_backend does not usually kill the process,
only a hard kill does. In the latter case, two (out of four) times it also
crashed Postgres.
* There have been about 10 examples of btree index corruption, across a
handful of databases, in different data centres. In two of the cases, it's
possible there were hardware issues as the hypervisor those two VM's were
running on crashed.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_upgrade and frozen xids
2018-03-06 23:12 pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
2018-03-06 23:25 ` Re: pg_upgrade and frozen xids Peter Geoghegan <pg@bowt.ie>
2018-03-06 23:41 ` Re: pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
2018-03-07 18:47 ` Re: pg_upgrade and frozen xids bricklen <bricklen@gmail.com>
@ 2018-03-07 19:48 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2018-03-07 20:01 ` Re: pg_upgrade and frozen xids Peter Geoghegan <pg@bowt.ie>
0 siblings, 1 reply; 8+ messages in thread
From: Alvaro Herrera @ 2018-03-07 19:48 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin
bricklen wrote:
> We recently upgraded about 1500 Postgres clusters from 9.3 to 10.1 and we
> are seeing similar issues. Specifically:
> * An autovacuum worker stuck vacuuming regular tables for days until they
> are killed (I've seen "vacuuming indexes" phase), and they are showing 99%
> CPU in top. pg_cancel|terminate_backend does not usually kill the process,
> only a hard kill does. In the latter case, two (out of four) times it also
> crashed Postgres.
> * There have been about 10 examples of btree index corruption, across a
> handful of databases, in different data centres. In two of the cases, it's
> possible there were hardware issues as the hypervisor those two VM's were
> running on crashed.
I wonder if this may be related to changes in collation algorithm. If
the indexes are all on text columns, I think you would be better served
by reindexing them all instead of trying to treat this situation as a
code bug.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_upgrade and frozen xids
2018-03-06 23:12 pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
2018-03-06 23:25 ` Re: pg_upgrade and frozen xids Peter Geoghegan <pg@bowt.ie>
2018-03-06 23:41 ` Re: pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
2018-03-07 18:47 ` Re: pg_upgrade and frozen xids bricklen <bricklen@gmail.com>
2018-03-07 19:48 ` Re: pg_upgrade and frozen xids Alvaro Herrera <alvherre@2ndquadrant.com>
@ 2018-03-07 20:01 ` Peter Geoghegan <pg@bowt.ie>
2018-03-07 20:21 ` Re: pg_upgrade and frozen xids bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Geoghegan @ 2018-03-07 20:01 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@2ndquadrant.com>; +Cc: bricklen <bricklen@gmail.com>; Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin
On Wed, Mar 7, 2018 at 11:48 AM, Alvaro Herrera
<alvherre@2ndquadrant.com> wrote:
> I wonder if this may be related to changes in collation algorithm. If
> the indexes are all on text columns, I think you would be better served
> by reindexing them all instead of trying to treat this situation as a
> code bug.
I happen to know that bricklen already ran amcheck. There were errors,
but they were not consistent with a collation issue. Rather, it looked
like something was up with the storage layer -- the sibling links of a
pair of pages were not in mutual agreement.
Even if that wasn't something that I knew already, I still would not
suspect opclass misbehavior of any variety. VACUUM doesn't care about
the ordering of items on the page in the case of nbtree. And, it
performs a physical order scan there (albeit with some extra trickery
to prevent races due to concurrent splits). Index tuples that could
end up being unreachable to index scans due to opclass misbehavior
should remain reachable to VACUUM.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_upgrade and frozen xids
2018-03-06 23:12 pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
2018-03-06 23:25 ` Re: pg_upgrade and frozen xids Peter Geoghegan <pg@bowt.ie>
2018-03-06 23:41 ` Re: pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
2018-03-07 18:47 ` Re: pg_upgrade and frozen xids bricklen <bricklen@gmail.com>
2018-03-07 19:48 ` Re: pg_upgrade and frozen xids Alvaro Herrera <alvherre@2ndquadrant.com>
2018-03-07 20:01 ` Re: pg_upgrade and frozen xids Peter Geoghegan <pg@bowt.ie>
@ 2018-03-07 20:21 ` bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: bricklen @ 2018-03-07 20:21 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: Alvaro Herrera <alvherre@2ndquadrant.com>; Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin
On Wed, Mar 7, 2018 at 12:01 PM, Peter Geoghegan <pg@bowt.ie> wrote:
> I happen to know that bricklen already ran amcheck. There were errors,
> but they were not consistent with a collation issue. Rather, it looked
> like something was up with the storage layer -- the sibling links of a
> pair of pages were not in mutual agreement.
>
> Even if that wasn't something that I knew already, I still would not
> suspect opclass misbehavior of any variety. VACUUM doesn't care about
> the ordering of items on the page in the case of nbtree. And, it
> performs a physical order scan there (albeit with some extra trickery
> to prevent races due to concurrent splits). Index tuples that could
> end up being unreachable to index scans due to opclass misbehavior
> should remain reachable to VACUUM.
>
What little detail I've been able to collect so far is below. All for 10.1
clusters.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: pg_upgrade and frozen xids
2018-03-06 23:12 pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
2018-03-06 23:25 ` Re: pg_upgrade and frozen xids Peter Geoghegan <pg@bowt.ie>
2018-03-06 23:41 ` Re: pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
@ 2018-03-07 20:11 ` Peter Geoghegan <pg@bowt.ie>
1 sibling, 0 replies; 8+ messages in thread
From: Peter Geoghegan @ 2018-03-07 20:11 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; +Cc: pgsql-admin
On Tue, Mar 6, 2018 at 3:41 PM, Natalie Wenz <nataliewenz@ebureau.com> wrote:
> They are not, unfortunately (fortunately?). Just a standard table.
>
> If it helps, the autovacuum always seems to be in the phase "cleaning up
> indexes" when I look at pg_stat_progress_vacuum.
It sounds like it might be a good idea for you to run amcheck on
affected indexes:
https://www.postgresql.org/docs/10/static/amcheck.html
If that doesn't show any issue, you might then use the Github
version's extra "heapallindexed" test on the same indexes:
https://github.com/petergeoghegan/amcheck
The extra "heapallindexed" test might allow you to uncover that some
parts of an index are unreachable. Though using
bt_index_parent_check() instead of bt_index_check() may be just as
effective, and should be considered if you can afford to have a lock
that will block writes to the table during verification.
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2018-03-07 20:21 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-06 23:12 pg_upgrade and frozen xids Natalie Wenz <nataliewenz@ebureau.com>
2018-03-06 23:25 ` Peter Geoghegan <pg@bowt.ie>
2018-03-06 23:41 ` Natalie Wenz <nataliewenz@ebureau.com>
2018-03-07 18:47 ` bricklen <bricklen@gmail.com>
2018-03-07 19:48 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2018-03-07 20:01 ` Peter Geoghegan <pg@bowt.ie>
2018-03-07 20:21 ` bricklen <bricklen@gmail.com>
2018-03-07 20:11 ` Peter Geoghegan <pg@bowt.ie>
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