pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump and XID limit
6+ messages / 4 participants
[nested] [flat]
* pg_dump and XID limit
@ 2010-11-24 02:18 Elliot Chance <elliotchance@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Elliot Chance @ 2010-11-24 02:18 UTC (permalink / raw)
To: pgsql-admin
Hi,
This is a hypothetical problem but not an impossible situation. Just curious about what would happen.
Lets say you have an OLTP server that keeps very busy on a large database. In this large database you have one or more tables on super fast storage like a fusion IO card which is handling (for the sake of argument) 1 million transactions per second.
Even though only one or a few tables are using almost all of the IO, pg_dump has to export a consistent snapshot of all the tables to somewhere else every 24 hours. But because it's such a large dataset (or perhaps just network congestion) the daily backup takes 2 hours.
Heres the question, during that 2 hours more than 4 billion transactions could of occurred - so what's going to happen to your backup and/or database?
- Elliot
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump and XID limit
@ 2010-11-24 06:07 Tom Lane <tgl@sss.pgh.pa.us>
parent: Elliot Chance <elliotchance@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2010-11-24 06:07 UTC (permalink / raw)
To: Elliot Chance <elliotchance@gmail.com>; +Cc: pgsql-admin
Elliot Chance <elliotchance@gmail.com> writes:
> This is a hypothetical problem but not an impossible situation. Just curious about what would happen.
> Lets say you have an OLTP server that keeps very busy on a large database. In this large database you have one or more tables on super fast storage like a fusion IO card which is handling (for the sake of argument) 1 million transactions per second.
> Even though only one or a few tables are using almost all of the IO, pg_dump has to export a consistent snapshot of all the tables to somewhere else every 24 hours. But because it's such a large dataset (or perhaps just network congestion) the daily backup takes 2 hours.
> Heres the question, during that 2 hours more than 4 billion transactions could of occurred - so what's going to happen to your backup and/or database?
The DB will shut down to prevent wraparound once it gets 2 billion XIDs
in front of the oldest open snaphot.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump and XID limit
@ 2010-11-24 09:59 Elliot Chance <elliotchance@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Elliot Chance @ 2010-11-24 09:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On 24/11/2010, at 5:07 PM, Tom Lane wrote:
> Elliot Chance <elliotchance@gmail.com> writes:
>> This is a hypothetical problem but not an impossible situation. Just curious about what would happen.
>
>> Lets say you have an OLTP server that keeps very busy on a large database. In this large database you have one or more tables on super fast storage like a fusion IO card which is handling (for the sake of argument) 1 million transactions per second.
>
>> Even though only one or a few tables are using almost all of the IO, pg_dump has to export a consistent snapshot of all the tables to somewhere else every 24 hours. But because it's such a large dataset (or perhaps just network congestion) the daily backup takes 2 hours.
>
>> Heres the question, during that 2 hours more than 4 billion transactions could of occurred - so what's going to happen to your backup and/or database?
>
> The DB will shut down to prevent wraparound once it gets 2 billion XIDs
> in front of the oldest open snaphot.
>
> regards, tom lane
Wouldn't that mean at some point it would be advisable to be using 64bit transaction IDs? Or would that change too much of the codebase?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump and XID limit
@ 2010-11-24 11:18 Vladimir Rusinov <vladimir@greenmice.info>
parent: Elliot Chance <elliotchance@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Vladimir Rusinov @ 2010-11-24 11:18 UTC (permalink / raw)
To: Elliot Chance <elliotchance@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-admin
On Wed, Nov 24, 2010 at 12:59 PM, Elliot Chance <elliotchance@gmail.com>wrote:
> > Elliot Chance <elliotchance@gmail.com> writes:
> >> This is a hypothetical problem but not an impossible situation. Just
> curious about what would happen.
> >
> >> Lets say you have an OLTP server that keeps very busy on a large
> database. In this large database you have one or more tables on super fast
> storage like a fusion IO card which is handling (for the sake of argument) 1
> million transactions per second.
> >
> >> Even though only one or a few tables are using almost all of the IO,
> pg_dump has to export a consistent snapshot of all the tables to somewhere
> else every 24 hours. But because it's such a large dataset (or perhaps just
> network congestion) the daily backup takes 2 hours.
> >
> >> Heres the question, during that 2 hours more than 4 billion transactions
> could of occurred - so what's going to happen to your backup and/or
> database?
> >
> > The DB will shut down to prevent wraparound once it gets 2 billion XIDs
> > in front of the oldest open snaphot.
> >
> > regards, tom lane
>
> Wouldn't that mean at some point it would be advisable to be using 64bit
> transaction IDs? Or would that change too much of the codebase?
I think it would be advisable not to use pg_dump on such load. Use fs- or
storage-level snapshots instead.
--
Vladimir Rusinov
http://greenmice.info/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump and XID limit
@ 2010-11-24 14:54 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Vladimir Rusinov <vladimir@greenmice.info>
0 siblings, 0 replies; 6+ messages in thread
From: Kevin Grittner @ 2010-11-24 14:54 UTC (permalink / raw)
To: Elliot Chance <elliotchance@gmail.com>; Vladimir Rusinov <vladimir@greenmice.info>; +Cc: pgsql-admin; Tom Lane <tgl@sss.pgh.pa.us>
Vladimir Rusinov <vladimir@greenmice.info> wrote:
> I think it would be advisable not to use pg_dump on such load.
Agreed.
> Use fs- or storage-level snapshots instead.
Or PITR backup techniques. Or hot/warm standby. Or streaming
replication. Or one of the many good trigger-based replication
products. Just about any of the alternatives would be better than
pg_dump in the described scenario. If you leave any transaction
open for hours during a heavy OLTP transaction load you're probably
going to get unpleasant bloat in your database.
-Kevin
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: pg_dump and XID limit
@ 2010-11-24 15:16 Tom Lane <tgl@sss.pgh.pa.us>
parent: Elliot Chance <elliotchance@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Tom Lane @ 2010-11-24 15:16 UTC (permalink / raw)
To: Elliot Chance <elliotchance@gmail.com>; +Cc: pgsql-admin
Elliot Chance <elliotchance@gmail.com> writes:
> Wouldn't that mean at some point it would be advisable to be using 64bit transaction IDs? Or would that change too much of the codebase?
It's not so much "too much of the codebase" as "nobody wants another 8
bytes added to per-row overhead". Holding a transaction open for
circa 2G transactions would be disastrous anyway, because of table bloat
from inability to reclaim dead rows.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2010-11-24 15:16 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-24 02:18 pg_dump and XID limit Elliot Chance <elliotchance@gmail.com>
2010-11-24 06:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-11-24 09:59 ` Elliot Chance <elliotchance@gmail.com>
2010-11-24 11:18 ` Vladimir Rusinov <vladimir@greenmice.info>
2010-11-24 14:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-24 15:16 ` 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