pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuum freeze performance, wraparound issues
3+ messages / 2 participants
[nested] [flat]
* vacuum freeze performance, wraparound issues
@ 2013-08-12 23:39 Natalie Wenz <nataliewenz@ebureau.com>
2013-08-13 04:15 ` Re: vacuum freeze performance, wraparound issues Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Natalie Wenz @ 2013-08-12 23:39 UTC (permalink / raw)
To: pgsql-admin
Hi all,
I have a few questions related to recovering from a near-miss with transactionid wraparound.
I'm currently running a vacuum freeze in single user mode on our largest database (about 36 TB). It's been running for about 10 days (since the database shut itself down to avoid xid wraparound). One cpu has been basically running at 100% the whole time (except during short periods of write activity when it drops briefly to around 30%). Any idea how to guess how long this might take? Is there anything I can adjust to speed the vacuum freeze up? It seems to be CPU limited--can anyone tell me what it would be doing that is so CPU intensive?
Runs postgres 9.1.9 on FreeBSD 9.1 with ZFS, database was built --with-segsize=10, storage is a pool of 20 2-disk mirrors
Maintenance_work_mem is set to 10GB; there is about 48 GB of memory in the machine.
The size of our database may be unusual for postgres, but honestly, it has performed quite well for us over the years. Our trouble comes when every once in a while we get a perfect storm that causes the autovacuum to fall behind. We are generally very careful to try to keep this from happening, since all it takes is one query at the wrong time to take a large database offline for days (or weeks). This is definitely our biggest cause of postgres outages, taking one of our databases offline about once a year, it seems. With the speed postgres is capable of, and the ever-falling prices of storage making larger, faster databases possible, has the possibility of changing the transaction id to a 64-bit (or even 128-bit!) value been considered?
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] 3+ messages in thread
* Re: vacuum freeze performance, wraparound issues
2013-08-12 23:39 vacuum freeze performance, wraparound issues Natalie Wenz <nataliewenz@ebureau.com>
@ 2013-08-13 04:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-08-14 14:52 ` Re: vacuum freeze performance, wraparound issues Natalie Wenz <nataliewenz@ebureau.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2013-08-13 04:15 UTC (permalink / raw)
To: Natalie Wenz <nataliewenz@ebureau.com>; +Cc: pgsql-admin
Natalie Wenz <nataliewenz@ebureau.com> writes:
> ... With the speed postgres is capable of, and the ever-falling prices
> of storage making larger, faster databases possible, has the possibility
> of changing the transaction id to a 64-bit (or even 128-bit!) value been
> considered?
Not terribly seriously --- the penalties from making row headers 8 bytes
bigger have always seemed to outweigh the advantages. (128 bits is right
out; we don't even have 128-bit LSNs.)
We'd probably take a patch to make 64-bit XIDs available as a compile-time
option, if someone wanted to do the legwork to write and test it. But
let me ask you this: if such an option existed, would you be willing to
dump and reload your database to take advantage of it? The conversion
costs of changing row header format seem like they'd discourage exactly
those people whom such a feature could help.
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] 3+ messages in thread
* Re: vacuum freeze performance, wraparound issues
2013-08-12 23:39 vacuum freeze performance, wraparound issues Natalie Wenz <nataliewenz@ebureau.com>
2013-08-13 04:15 ` Re: vacuum freeze performance, wraparound issues Tom Lane <tgl@sss.pgh.pa.us>
@ 2013-08-14 14:52 ` Natalie Wenz <nataliewenz@ebureau.com>
0 siblings, 0 replies; 3+ messages in thread
From: Natalie Wenz @ 2013-08-14 14:52 UTC (permalink / raw)
To: pgsql-admin
Yes, absolutely! We would dump and reload all of our large databases in a heartbeat if there were an option for a 64-bit xid!
Another quick question: When this database took itself offline to avoid transaction id wraparound, we opted for a vacuum freeze in single-user mode. Was that the right choice? In other words, what is the fastest way to get a database back online when this occurs? Maybe a plain vacuum would have been better?
Thanks!
Natalie
On Aug 12, 2013, at 11:15 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Natalie Wenz <nataliewenz@ebureau.com> writes:
>> ... With the speed postgres is capable of, and the ever-falling prices
>> of storage making larger, faster databases possible, has the possibility
>> of changing the transaction id to a 64-bit (or even 128-bit!) value been
>> considered?
>
> Not terribly seriously --- the penalties from making row headers 8 bytes
> bigger have always seemed to outweigh the advantages. (128 bits is right
> out; we don't even have 128-bit LSNs.)
>
> We'd probably take a patch to make 64-bit XIDs available as a compile-time
> option, if someone wanted to do the legwork to write and test it. But
> let me ask you this: if such an option existed, would you be willing to
> dump and reload your database to take advantage of it? The conversion
> costs of changing row header format seem like they'd discourage exactly
> those people whom such a feature could help.
>
> 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] 3+ messages in thread
end of thread, other threads:[~2013-08-14 14:52 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-08-12 23:39 vacuum freeze performance, wraparound issues Natalie Wenz <nataliewenz@ebureau.com>
2013-08-13 04:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2013-08-14 14:52 ` Natalie Wenz <nataliewenz@ebureau.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