On 06/07/2013 09:16 PM, Andres Freund wrote:
> On 2013-06-07 20:10:55 +0100, Simon Riggs wrote:
>> On 7 June 2013 19:56, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
>>> On 07.06.2013 21:33, Simon Riggs wrote:
>>>> Now that I consider Greg's line of thought, the idea we focused on
>>>> here was about avoiding freezing. But Greg makes me think that we may
>>>> also wish to look at allowing queries to run longer than one epoch as
>>>> well, if the epoch wrap time is likely to come down substantially.
>>>>
>>>> To do that I think we'll need to hold epoch for relfrozenxid as well,
>>>> amongst other things.
>>> The biggest problem I see with that is that if a snapshot can be older than
>>> 2 billion XIDs, it must be possible to store XIDs on the same page that are
>>> more than 2 billion XIDs apart. All the discussed schemes where we store the
>>> epoch at the page level, either explicitly or derived from the LSN, rely on
>>> the fact that it's not currently necessary to do that. Currently, when one
>>> XID on a page is older than 2 billion XIDs, that old XID can always be
>>> replaced with FrozenXid, because there cannot be a snapshot old enough to
>>> not see it.
>> It does seem that there are two problems: avoiding freezing AND long
>> running queries
>>
>> The long running query problem hasn't ever been looked at, it seems,
>> until here and now.
> I'd say that's because it's prohibitive to run so long transactions
> anyway since it causes too much unremovable bloat. 2bio transactions
> really is a quite a bit, I don't think it's a relevant restriction. Yet.
2bio transaction means at least 2G rows inserted, updated or deleted,
which may or may not bee "too much"
In my simple key/value update tests I was able to do 40k single transaction
updates sec on moderate sized server. at this rate 2G trx takes about 15
hours
which is not really very much.
One may for example have an unfinished 2PC transaction lingering over
weekend and while of course one should have monitoring in place to
avoid this, it still would be nice if this did not cause database shutdown.
--
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ