Thread: PostgreSQL 64 Bit XIDs - Transaction IDs
Hello, 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. How can one achieve this with PostgreSQL? What is the status and plan of 64 Bits XIDs? I saw that 64 Bit XIDs were already discussed in 2005 but never found into the code: http://postgresql.1045698.n5.nabble.com/A-couple-of-patches-for-PostgreSQL-64bit-support-td2214264.html Thnx. Ciao, Gerhard
Am 23.03.2012 06:45, schrieb Gerhard Wiesinger: > 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! Just corious: What is causing this many transactions? Cheers, Frank
Hi, On 23 March 2012 19:14, Frank Lanitz <frank@frank.uvena.de> wrote: > Am 23.03.2012 06:45, schrieb Gerhard Wiesinger: >> 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! > > Just corious: What is causing this many transactions? I would be interested to know hardware configuration and name of that mysterious "commercial database system"! -- Ondrej Ivanic (ondrej.ivanic@gmail.com)
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 :-) Cheers, Jan
On 03/22/2012 10:45 PM, Gerhard Wiesinger wrote: > Hello, > > 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. I would say either they got the numbers wrong or someone is pulling your leg. That rate is not going to happen. > > Thnx. > > Ciao, > Gerhard > > -- Adrian Klaver adrian.klaver@gmail.com
Am 23.03.2012 14:23, schrieb Adrian Klaver: > I would say either they got the numbers wrong or someone is pulling > your leg. That rate is not going to happen. Maybe twitter or facebook all in all... Cheers, Frank
On 03/22/12 10:45 PM, 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! bullpucky. that's 2 transactions per NANOSECOND. light can travel about 6" in that time. half a bit at gigE. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
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
On Fri, Mar 23, 2012 at 11:35 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote: > 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. two mitigating factors: 1. read only transactions do not increment xid counter 2. xid wraparound counter is per table. merlin
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
Merlin Moncure <mmoncure@gmail.com> writes:
> On Fri, Mar 23, 2012 at 11:35 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote:
>> So I think XID overflow should be planned for one of the next PostgreSQL
>> releases.
> two mitigating factors:
> 1. read only transactions do not increment xid counter
Yes. Ask your admin what his throughput of *data modifying*
transactions is. I'll bet that number is a few orders of magnitude
smaller again.
> 2. xid wraparound counter is per table.
That unfortunately isn't so, the XID counter is global to an installation.
regards, tom lane
On 03/23/12 9:17 AM, Gerhard Wiesinger wrote: > So in that time autovacuum is triggered. autovacuum runs pretty much continuously in the background, its not an on/off thing. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Fri, Mar 23, 2012 at 1:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> 2. xid wraparound counter is per table. > > That unfortunately isn't so, the XID counter is global to an installation. yup -- thinko: I was thinking about oid counter, not xid. thanks merlin
On Fri, 23 Mar 2012, John R Pierce wrote: > On 03/23/12 9:17 AM, Gerhard Wiesinger wrote: >> So in that time autovacuum is triggered. > > autovacuum runs pretty much continuously in the background, its not an on/off > thing. Yes, I know. I ment that it runs at least once in 1.5 days. Ciao, Gerhard -- http://www.wiesinger.com/
On Fri, 23 Mar 2012, Tom Lane wrote: > Merlin Moncure <mmoncure@gmail.com> writes: >> On Fri, Mar 23, 2012 at 11:35 AM, Gerhard Wiesinger <lists@wiesinger.com> wrote: >>> So I think XID overflow should be planned for one of the next PostgreSQL >>> releases. > >> two mitigating factors: >> 1. read only transactions do not increment xid counter > > Yes. Ask your admin what his throughput of *data modifying* > transactions is. I'll bet that number is a few orders of magnitude > smaller again. That were all small writing transactions benchmarking the database (with the corrected values discussed). Ciao, Gerhard -- http://www.wiesinger.com/
On 03/23/2012 12:12 PM, Gerhard Wiesinger wrote: > On Fri, 23 Mar 2012, John R Pierce wrote: > >> On 03/23/12 9:17 AM, Gerhard Wiesinger wrote: >>> So in that time autovacuum is triggered. >> >> autovacuum runs pretty much continuously in the background, its not an >> on/off thing. > > Yes, I know. I ment that it runs at least once in 1.5 days. http://www.postgresql.org/docs/9.1/interactive/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND > -- Adrian Klaver adrian.klaver@gmail.com
VoltDB maybe ----- Original Message ----- On 06:31 AM 03/23/2012 Frank Lanitz wrote: > Am 23.03.2012 14:23, schrieb Adrian Klaver: > > I would say either they got the numbers wrong or someone is pulling > > your leg. That rate is not going to happen. > > Maybe twitter or facebook all in all... > > Cheers, > Frank > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
On Fri, Mar 23, 2012 at 11:18 PM, Bret Stern <bret_stern@machinemanagement.com> wrote: > VoltDB maybe > ----- Original Message ----- VoltDB has a completely different focus than PostgreSQL really. PostgreSQL is a general purpose database that can achieve some very impressive numbers using super fast hardware, while still being a general purpose db that can do more than just shove through thousands of transactions a second. For instance, I've tested machines with spinning drives that could sustain, for weeks, 5 to 7k tps. Autovacuum suitably tuned to be fast enough to keep up (i.e. no sleep time, greatly increased cost ilmit, lots of checkpoint segments, and very aggressive bgwriter settings as well. I can imagine doubling that with a rack of SSDs behind it. And all on a single machine, that can be running PITR or streaming replication at the same time. VoltDB accomplishes this kind of throughput by running in memory, doing a kind of auto-sharded, RAIDed database setup for redundancy. I imagine a farm of little 1U 8 or 16 core machines running multiple instances of VoltDB (individual instances are single threaded) could just stomp pg right into the ground as a transactional engine. Because now you're scaling horizontally No matter how fast the hardware underlying it gets, pg will have a limit by the laws of physics on an individual machine that VoltDB simply works around by having dozens or hundreds of individual boxes doing the same job, albeit in a very specilalized manner. I've not played with VoltDB and I don't know how stable and reliable it is yet, but it was built by Michael Stonebraker, so I'd expect it to at least hold the promise of stable operation as it matures, if not being stable and reliable now. But the % of db systems that need that level of performance are pretty limited, and generally designed to have a large chunk of the world's population working on them at the same time. For a system that's more typical, i.e. 95% or more read, you can throw memcache in front of even a pretty mediocre box and have it keep up just fine for hundreds or even thousands of writes per second and hundreds of thousands of reads to the cache layer per second. There are real world problems with redundancy and reliability to work out with VoltDB that are quite different from Postgresql, since you need real synchronous replication from two geographically separated data centers, because if something horrible happens in one, (it gets hit by a meteor, or some junior electrician drops a piece of wire into a power conditioner, one whole segment of your db fabric could go dark. And if it's not synchronous, then the replication is useless for a true transactional banking style system. Keep in mind a pg server simply losing power is usually safe from data loss, and a nearby machine with syncrhonous replication is likely to provide a high enough level of redundancy for most transactional systems. Since VoltDB is in memory, you HAVE to have a live backup running somewhere remote, or a simple power loss kills all your data / latest transactions.
On Sat, Mar 24, 2012 at 1:27 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote: > On Fri, Mar 23, 2012 at 11:18 PM, Bret Stern > <bret_stern@machinemanagement.com> wrote: >> VoltDB maybe >> ----- Original Message ----- > > VoltDB has a completely different focus than PostgreSQL really. > PostgreSQL is a general purpose database that can achieve some very > impressive numbers using super fast hardware, while still being a > general purpose db that can do more than just shove through thousands > of transactions a second. There's some good talk on usenix's youtube channel about dbs etc.: One Size Does Not Fit All in DB Systems: www.youtube.com/watch?v=QQdbTpvjITM&lr=1 NewSQL vs. NoSQL for New OLTP by Mike Stonebraker: http://www.youtube.com/watch?v=uhDM4fcI2aI are both excellent presentations.