pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase 'template1' vacuum
7+ messages / 4 participants
[nested] [flat]
* Database 'template1' vacuum
@ 2016-07-26 18:08 Natalie Wenz <nataliewenz@ebureau.com>
0 siblings, 1 reply; 7+ messages in thread
From: Natalie Wenz @ 2016-07-26 18:08 UTC (permalink / raw)
To: pgsql-admin
Hi!
I've encountered something I've never seen before, and I am hoping someone can help me understand what's going on behind the scenes with this vacuum/xid behavior.
ERROR: database is not accepting commands to avoid wraparound data loss in database "template1"
This error popped up right after I restarted a database that had just finished running a vacuum freeze in single user mode for the postgres database (the only database we use).
(That was in response to: ERROR: database is not accepting commands to avoid wraparound data loss in database "postgres")
I'm confused about the need to vacuum template1. All of our activity is in the database named postgres. Do all of the databases share the same set of xids? If they do, why would the single user backend vacuum not handle all of the databases at once? If all databases don't share the same set of xids, then what could be going on internally to cause template1 to run through so many xids?
(This is running on 9.5.3, if that makes a difference.)
Thanks!
Natalie
--
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] 7+ messages in thread
* Re: Database 'template1' vacuum
@ 2016-07-26 18:20 Kevin Grittner <kgrittn@gmail.com>
parent: Natalie Wenz <nataliewenz@ebureau.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2016-07-26 18:20 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; +Cc: pgsql-admin
On Tue, Jul 26, 2016 at 1:08 PM, Natalie Wenz
<nataliewenz@ebureau.com> wrote:
> ERROR: database is not accepting commands to avoid wraparound
> data loss in database "template1"
>
> This error popped up right after I restarted a database that had
> just finished running a vacuum freeze in single user mode for the
> postgres database (the only database we use).
> (That was in response to: ERROR: database is not accepting
> commands to avoid wraparound data loss in database "postgres")
>
> I'm confused about the need to vacuum template1. All of our
> activity is in the database named postgres. Do all of the
> databases share the same set of xids?
Yes.
> If they do, why would the single user backend vacuum not handle
> all of the databases at once?
A connection is always to a particular database. The big question
is why autovacuum didn't keep you out of trouble. You didn't turn
that off, did you? If not, please describe your hardware and
workload, and show you vacuum/autovacuum configuration values.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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] 7+ messages in thread
* Re: Database 'template1' vacuum
@ 2016-07-26 19:36 Natalie Wenz <nataliewenz@ebureau.com>
parent: Kevin Grittner <kgrittn@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Natalie Wenz @ 2016-07-26 19:36 UTC (permalink / raw)
To: pgsql-admin; +Cc: Kevin Grittner <kgrittn@gmail.com>
> On Jul 26, 2016, at 1:20 PM, Kevin Grittner <kgrittn@gmail.com> wrote:
>
> On Tue, Jul 26, 2016 at 1:08 PM, Natalie Wenz
> <nataliewenz@ebureau.com> wrote:
>
>> ERROR: database is not accepting commands to avoid wraparound
>> data loss in database "template1"
>>
>> This error popped up right after I restarted a database that had
>> just finished running a vacuum freeze in single user mode for the
>> postgres database (the only database we use).
>> (That was in response to: ERROR: database is not accepting
>> commands to avoid wraparound data loss in database "postgres")
>>
>> I'm confused about the need to vacuum template1. All of our
>> activity is in the database named postgres. Do all of the
>> databases share the same set of xids?
>
> Yes.
>
>> If they do, why would the single user backend vacuum not handle
>> all of the databases at once?
>
> A connection is always to a particular database. The big question
> is why autovacuum didn't keep you out of trouble. You didn't turn
> that off, did you? If not, please describe your hardware and
> workload, and show you vacuum/autovacuum configuration values.
I can see why you would be wondering that. :) Autovacuum is definitely on. This situation might be considered "maintenance fallout". This database is very large (45 TB, currently, I believe), with most of the space being occupied by one table. (The database is basically a big transaction log, with a nearly all traffic being inserts. No deletes or updates on that big table.). I recently migrated it from 9.3 to 9.5, from one Free BSD host to another. As you can imagine, that took some time. I tried to reduce the time by "dumping" the large table in chunks using "copy" and a date range, thereby increasing the concurrency for both dumping and restoring that data. The problem is, I forgot that, even though I didn't use very many xids importing the data that way, it still meant that alllllll of that data would need to be frozen later. Once the data was all migrated and we resumed our regular traffic, we quickly got into trouble with the xids, since the auto vacuum couldn't freeze all 45 TB of that one big table while fielding up to... 500 million inserts per day? (typically around 6k inserts/second) So we disabled external connections to allow the database to vacuum, buffering the new transactions elsewhere. Now that the first enormous vacuum freeze is complete, we are "unspooling" the data that was buffered while the database vacuumed. Between the hardware and postgres software, we easily see this hit 65 thousand inserts per second. (Wooo, modern hardware! Excellent software!) Of course, that means that we've run out of xids again in the span of about 10 hours; no tuning of the auto vacuum that we've tried is able to keep pace with that. So. I'm currently suffering through a cycle of unspool buffered transactions, vacuum freeze while transactions buffer, unspool . . .
At this point, I have just left the auto vacuum defaults in place for this database, and it generally keeps up with our typical 6k insert/second workload. Even so, maintenance is problematic. We are looking into batch inserts in the future, or table partitioning (would that help with concurrency for maintenance? dumping, restoring, vacuuming?), or both. We also keep hoping that maybe, just maybe, we'll see a version of postgres with 64-bit xids? :D
Anyway, template1: this is expected behavior? I'm maybe on my third or fourth round of unspool, then single user vacuum freeze. The auto vacuum is on and free to run while I'm unspooling. I'm surprised that the auto vacuum wouldn't have already have vacuumed that very small database, or that haven't been prompted to vacuum template1 prior to this, if the only transactions in this database would have occurred during the initdb for the postgres database. Does it get used for other things during normal database operations?
--
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] 7+ messages in thread
* Re: Database 'template1' vacuum
@ 2016-07-26 19:50 Kevin Grittner <kgrittn@gmail.com>
parent: Natalie Wenz <nataliewenz@ebureau.com>
0 siblings, 1 reply; 7+ messages in thread
From: Kevin Grittner @ 2016-07-26 19:50 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; +Cc: pgsql-admin
On Tue, Jul 26, 2016 at 2:36 PM, Natalie Wenz <nataliewenz@ebureau.com> wrote:
> Now that the first enormous vacuum freeze is complete, we are
> "unspooling" the data that was buffered while the database
> vacuumed. Between the hardware and postgres software, we easily see
> this hit 65 thousand inserts per second. (Wooo, modern hardware!
> Excellent software!) Of course, that means that we've run out of
> xids again in the span of about 10 hours; no tuning of the auto
> vacuum that we've tried is able to keep pace with that. So. I'm
> currently suffering through a cycle of unspool buffered
> transactions, vacuum freeze while transactions buffer, unspool . . .
There are many reasons to batch inserts, this being one of them.
You might see a big further boost in the insert rate, even with
moderate batch sizes. If you think about it, a batch size of 100
should get you to somewhere around 99% of the benefit of larger
batch sizes. Of course, you need some way to handle an error in a
row or two out of the batch....
> At this point, I have just left the auto vacuum defaults in place
> for this database, and it generally keeps up with our typical 6k
> insert/second workload.
I would increase autovacuum_max_workers (to maybe 10 or 12) and
autovacuum_vacuum_cost_limit (to several thousand).
> Anyway, template1: this is expected behavior?
Yeah. I would just connect to that database as the database
superuser (in single-user mode only if necessary), and run the
command `VACUUM`. No options, just the bare command.
> I'm surprised that the auto vacuum wouldn't have already have
> vacuumed that very small database, or that haven't been prompted to
> vacuum template1 prior to this, if the only transactions in this
> database would have occurred during the initdb for the postgres
> database.
The logic for picking what to vacuum isn't, shall we say, ideal.
It may keep running into the same tables to vacuum whenever it
scans, and never gets to others. A higher worker count should help
it get to more (hopefully all) of the tables. The cost limit is
share among all the active workers, so raising the maximum doesn't
tend to have much direct impact on performance.
> Does it get used for other things during normal database
> operations?
No -- it is normally used just for CREATE DATABASE.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
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] 7+ messages in thread
* Re: Database 'template1' vacuum
@ 2016-08-08 14:43 Natalie Wenz <nataliewenz@ebureau.com>
parent: Kevin Grittner <kgrittn@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Natalie Wenz @ 2016-08-08 14:43 UTC (permalink / raw)
To: pgsql-admin; +Cc: Kevin Grittner <kgrittn@gmail.com>
Ok, I have one more question (for now!) about vacuum behavior:
How are static tables handled? Once every row in a table is marked as frozen, and there are no further inserts, deletes, updates, etc to the table, does the vacuum have to do anything to that table when advancing the relfrozenxid? Is there a way that the vacuum can see "Oh, this whole table is frozen, my work here is done!" or does it still have to scan every page (or worse, every row)?
> On Jul 26, 2016, at 2:50 PM, Kevin Grittner <kgrittn@gmail.com> wrote:
>
> On Tue, Jul 26, 2016 at 2:36 PM, Natalie Wenz <nataliewenz@ebureau.com> wrote:
>
>> Now that the first enormous vacuum freeze is complete, we are
>> "unspooling" the data that was buffered while the database
>> vacuumed. Between the hardware and postgres software, we easily see
>> this hit 65 thousand inserts per second. (Wooo, modern hardware!
>> Excellent software!) Of course, that means that we've run out of
>> xids again in the span of about 10 hours; no tuning of the auto
>> vacuum that we've tried is able to keep pace with that. So. I'm
>> currently suffering through a cycle of unspool buffered
>> transactions, vacuum freeze while transactions buffer, unspool . . .
>
> There are many reasons to batch inserts, this being one of them.
> You might see a big further boost in the insert rate, even with
> moderate batch sizes. If you think about it, a batch size of 100
> should get you to somewhere around 99% of the benefit of larger
> batch sizes. Of course, you need some way to handle an error in a
> row or two out of the batch....
>
>> At this point, I have just left the auto vacuum defaults in place
>> for this database, and it generally keeps up with our typical 6k
>> insert/second workload.
>
> I would increase autovacuum_max_workers (to maybe 10 or 12) and
> autovacuum_vacuum_cost_limit (to several thousand).
>
>> Anyway, template1: this is expected behavior?
>
> Yeah. I would just connect to that database as the database
> superuser (in single-user mode only if necessary), and run the
> command `VACUUM`. No options, just the bare command.
>
>> I'm surprised that the auto vacuum wouldn't have already have
>> vacuumed that very small database, or that haven't been prompted to
>> vacuum template1 prior to this, if the only transactions in this
>> database would have occurred during the initdb for the postgres
>> database.
>
> The logic for picking what to vacuum isn't, shall we say, ideal.
> It may keep running into the same tables to vacuum whenever it
> scans, and never gets to others. A higher worker count should help
> it get to more (hopefully all) of the tables. The cost limit is
> share among all the active workers, so raising the maximum doesn't
> tend to have much direct impact on performance.
>
>> Does it get used for other things during normal database
>> operations?
>
> No -- it is normally used just for CREATE DATABASE.
>
> --
> Kevin Grittner
> EDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> 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] 7+ messages in thread
* Re: Database 'template1' vacuum
@ 2016-08-08 15:00 David G. Johnston <david.g.johnston@gmail.com>
parent: Natalie Wenz <nataliewenz@ebureau.com>
0 siblings, 1 reply; 7+ messages in thread
From: David G. Johnston @ 2016-08-08 15:00 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; +Cc: pgsql-admin; Kevin Grittner <kgrittn@gmail.com>
On Mon, Aug 8, 2016 at 10:43 AM, Natalie Wenz <nataliewenz@ebureau.com>
wrote:
> Ok, I have one more question (for now!) about vacuum behavior:
>
> How are static tables handled? Once every row in a table is marked as
> frozen, and there are no further inserts, deletes, updates, etc to the
> table, does the vacuum have to do anything to that table when advancing the
> relfrozenxid? Is there a way that the vacuum can see "Oh, this whole table
> is frozen, my work here is done!" or does it still have to scan every page
> (or worse, every row)?
>
>
Seems so:
https://wiki.postgresql.org/wiki/VacuumHeadaches
https://www.postgresql.org/docs/current/static/routine-vacuuming.html#VACUUM-FOR-VISIBILITY-MAP
"""
VACUUM normally only scans pages that have been modified since the last
vacuum, but relfrozenxid can only be advanced when the whole table is
scanned. The whole table is scanned when relfrozenxid is more than
vacuum_freeze_table_age transactions old, when VACUUM's FREEZE option is
used, or when all pages happen to require vacuuming to remove dead row
versions.
"""
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Database 'template1' vacuum
@ 2016-08-08 16:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2016-08-08 16:45 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Natalie Wenz <nataliewenz@ebureau.com>; pgsql-admin; Kevin Grittner <kgrittn@gmail.com>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Mon, Aug 8, 2016 at 10:43 AM, Natalie Wenz <nataliewenz@ebureau.com>
> wrote:
>> How are static tables handled? Once every row in a table is marked as
>> frozen, and there are no further inserts, deletes, updates, etc to the
>> table, does the vacuum have to do anything to that table when advancing the
>> relfrozenxid? Is there a way that the vacuum can see "Oh, this whole table
>> is frozen, my work here is done!" or does it still have to scan every page
>> (or worse, every row)?
> Seems so:
> https://wiki.postgresql.org/wiki/VacuumHeadaches
FWIW, this is due to get better in 9.6. Per release notes:
Avoid re-vacuuming pages containing only frozen tuples (Masahiko
Sawada, Robert Haas, Andres Freund)
Formerly, an anti-wraparound vacuum had to visit every page of a
table whether or not there was anything to do there. Now, pages
containing only already-frozen tuples are identified in the
table's visibility map, and can be skipped by vacuum even when
it's doing transaction wraparound prevention. This should greatly
reduce the cost of maintaining large tables containing
mostly-unchanging data.
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] 7+ messages in thread
end of thread, other threads:[~2016-08-08 16:45 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-07-26 18:08 Database 'template1' vacuum Natalie Wenz <nataliewenz@ebureau.com>
2016-07-26 18:20 ` Kevin Grittner <kgrittn@gmail.com>
2016-07-26 19:36 ` Natalie Wenz <nataliewenz@ebureau.com>
2016-07-26 19:50 ` Kevin Grittner <kgrittn@gmail.com>
2016-08-08 14:43 ` Natalie Wenz <nataliewenz@ebureau.com>
2016-08-08 15:00 ` David G. Johnston <david.g.johnston@gmail.com>
2016-08-08 16:45 ` Tom Lane <tgl@sss.pgh.pa.us>
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