Thread: heavly load system spec

I know this is a very general question. But if you guys had to specify
system (could be one server or cluster), with sustainable transaction
rate of 1.5M tps running postgresql, what configuration and hardware
would you be looking for ?
The transaction distribution there is 90% writes/updates and 10% reads.
We're talking 64 linux, Intel/IBM system.

I'm trying to see how that compares with Oracle system.

Thanks.

--
GJ

On Thu, Apr 5, 2012 at 11:39 AM, Gregg Jaskiewicz <gryzman@gmail.com> wrote:
> I know this is a very general question. But if you guys had to specify
> system (could be one server or cluster), with sustainable transaction
> rate of 1.5M tps running postgresql, what configuration and hardware
> would you be looking for ?
> The transaction distribution there is 90% writes/updates and 10% reads.
> We're talking 64 linux, Intel/IBM system.
>
> I'm trying to see how that compares with Oracle system.

1.5 million is a lot of tps, especially if some of them are write
transactions.  On trivial read-only transactions (primary key lookup
on fully cached table), using a 16-core, 64-thread IBM POWER7 box,
pgbench -M prepared -S -n -T 60 -c 64 -j 64:

tps = 455903.743918 (including connections establishing)
tps = 456012.871764 (excluding connections establishing)

That box isn't quite the fastest one I've seen, but it's close.

What hardware is Oracle running on?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Gregg,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Thu, Apr 5, 2012 at 11:39 AM, Gregg Jaskiewicz <gryzman@gmail.com> wrote:
> > I know this is a very general question. But if you guys had to specify
> > system (could be one server or cluster), with sustainable transaction
> > rate of 1.5M tps running postgresql, what configuration and hardware
> > would you be looking for ?
> > The transaction distribution there is 90% writes/updates and 10% reads.
> > We're talking 64 linux, Intel/IBM system.

Just to clarify/verify, you're looking for a system which can handle
1.35M write transactions per second?  That's quite a few and regardless
of RDBMS, I expect you'll need quite an I/O system to handle that.

    Thanks,

        Stephen

On Thu, Apr 5, 2012 at 10:39 AM, Gregg Jaskiewicz <gryzman@gmail.com> wrote:
> I know this is a very general question. But if you guys had to specify
> system (could be one server or cluster), with sustainable transaction
> rate of 1.5M tps running postgresql, what configuration and hardware
> would you be looking for ?
> The transaction distribution there is 90% writes/updates and 10% reads.
> We're talking 64 linux, Intel/IBM system.
>
> I'm trying to see how that compares with Oracle system.

That's not gonna be possible with stock postgres.  You could cluster
out the reads with a (probably cascaded) HS/SR setup but we'd still
have to get 90% of 1.5M tps running in a single instance.  You'd hit
various bottlenecks trying to get write transactions up anywhere near
that figure -- the walinsert lock being the worst since it is
something of an upper bound on tps rates.  I can't speak for Oracle
but I'm skeptical it's possible there in a monolithic system; if it is
in fact possible it would cost megabucks to do it.

At the end of the day your problem needs some serious engineering and
serious engineers.  Any solution is probably going to involve a
cluster of machines to stage the data with various ETL type jobs to
move it into specific services that will do the actual processing.
This is how all solutions that sustain very high transaction rates
work; you divide tasks and develop communications protocols between
various systems.

merlin