PostgreSQL 64 Bit XIDs - Transaction IDs

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 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

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 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.