On 23.03.2012 11:16, Jan Kesten wrote:
> On 23.03.2012 06:45, Gerhard Wiesinger wrote:
>
>> With a database admin of a commercial database system I've discussed
>> that they have to provide and they also achieve 2^31 transactions per
>> SECOND!
>> As PostgreSQL uses transaction IDs (XIDs) in the range of 2^31 they
>> would turn around in about one second.
> Wow. What application issues that much transactions? And what is the
> database system that can handle that? I can't think of a single machine
> capable of this - and hardy believe postgresql can came close. 2^31
> transactions mean that a single one lasts 0.5ns. Even the fastest
> DDR3-2133 has cycle times of 4ns.
>
> I have seen a database monster in action - 43 trillion (academic)
> transactions per day, but that's only 5*10^8 transactions per second,
> under a quarter of 2^31 per second.
>
> So, I can't answer your question - but you triggered my curiosity :-)
I'm just answering in one of the posts ...
Ok, talked again to the admin and he was wrong with 3 zeros and per
minute .... :-)
So corrected data are: 1 Mio transaction per minute. 1Mio/60s=16666
transactions/s
2^32*60/1E6=257698s to get to the XID overflow which are 2.98 days (~1.5
days when 2^31 with signed int is the border)
So in that time autovacuum is triggered. Nevertheless we are getting
into the area where XID overflow is an issue in the near future.
In your example with 5E8 transactions per second overflow will be in 4s
(2^31) or 8s (2^32) ...
So I think XID overflow should be planned for one of the next PostgreSQL
releases.
Ciao,
Gerhard
