Обсуждение: Wal -long transaction
I would like to know how Postgresql works when all the files (checkpoint_segment *2 + 1) are full , does Postgresql rollback the transaction when all the wal segments are used, or does the server stop with an error message ? (I have tried to make the test but without success for finding a long transaction) thanks regards
On Mon, Mar 13, 2006 at 03:59:33PM +0100, Agnes Bocchino wrote: > I would like to know how Postgresql works when all the files > (checkpoint_segment *2 + 1) > are full , > does Postgresql rollback the transaction when all the wal segments are used, > or does the server stop with an error message ? > (I have tried to make the test but without success for finding a long > transaction) AIUI it just keeps creating more segments. i.e. checkpoint_segment is not a hard limit. It's just the number it keeps around and recycles rather than continually creating and deleteing files. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
Martijn van Oosterhout <kleptog@svana.org> writes:
> On Mon, Mar 13, 2006 at 03:59:33PM +0100, Agnes Bocchino wrote:
>> (I have tried to make the test but without success for finding a long
>> transaction)
> AIUI it just keeps creating more segments. i.e. checkpoint_segment is
> not a hard limit. It's just the number it keeps around and recycles
> rather than continually creating and deleteing files.
More to the point, having a long transaction has nothing to do with this
(we are not Oracle!). The only thing that determines the amount of WAL
space needed is the time between checkpoints. You can have a
transaction that stays open for many checkpoints without causing WAL to
bloat.
Of course, there's no free lunch --- the price we pay for escaping
rollback-segment-overflow is table bloat if you don't vacuum often
enough.
regards, tom lane
Martijn van Oosterhout wrote: >On Mon, Mar 13, 2006 at 03:59:33PM +0100, Agnes Bocchino wrote: > > >>I would like to know how Postgresql works when all the files >>(checkpoint_segment *2 + 1) >>are full , >>does Postgresql rollback the transaction when all the wal segments are used, >>or does the server stop with an error message ? >>(I have tried to make the test but without success for finding a long >>transaction) >> >> > >AIUI it just keeps creating more segments. i.e. checkpoint_segment is >not a hard limit. It's just the number it keeps around and recycles >rather than continually creating and deleteing files. > >Have a nice day, > > Thanks for the answer, that's clarify how it's work I ask this question about 'long transaction' (between two commits) because it was a problem on old release of IDS Informix. Please, what is the meaning of 'AIUI' ...... Thanks Agnès
On Mon, Mar 13, 2006 at 04:51:03PM +0100, Agnes Bocchino wrote: > Please, what is the meaning of 'AIUI' ...... As I Understand It -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
> Please, what is the meaning of 'AIUI' ...... This site was a big help for me as acronyms are popular on this list: http://www.acronymfinder.com Regards, Richard
Tom Lane <tgl@sss.pgh.pa.us> writes: > Of course, there's no free lunch --- the price we pay for escaping > rollback-segment-overflow is table bloat if you don't vacuum often > enough. Well it's worse than that. If you have long-running transactions that would cause rollback-segment-overflow in Oracle then the equivalent price in Postgres would be table bloat *regardless* of how frequently you vacuum. I suppose you can argue it's not "bloat" as long as you reach a steady state. But the extra space in the tables is a performance cost on every sequential scan and on every cache miss it causes whatever you call it. I'm not saying I like rollback segments better, just yes, TANSTAAFL. -- greg
Greg Stark wrote: > > Well it's worse than that. If you have long-running transactions that would > cause rollback-segment-overflow in Oracle then the equivalent price in > Postgres would be table bloat *regardless* of how frequently you vacuum. Isn't that a bit pessimistic? In tables which mostly grow (as opposed to deletes and updates) and where most inserts succeed (instead of rolling back), I would have expected postgresql not to bloat tables no matter how long my transactions last. And it's been a while; but I thought transactions like that could overflow rollback segments in that other database.
> > And it's been a while; but I thought transactions like that could > overflow rollback segments in that other database. > ORA-01555: snapshot too old: rollback segment number string with name "string" too small Cause: Rollback records needed by a reader for consistent read are overwritten by other writers. Action: If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise, use larger rollback segments. In 10g you can do ALTER TABLESPACE UNDO_TS2 RETENTION GUARANTEE; which will automgically grow the undo tablespace until you run out of disk space or the transaction ends.