Re: sustained update load of 1-2k/sec - Mailing list pgsql-performance

Alex mentions a nice setup, but I'm pretty sure I know how to beat
that IO subsystems HW's performance by at least 1.5x or 2x.  Possibly
more.  (No, I do NOT work for any vendor I'm about to discuss.)

Start by replacing the WD Raptors with Maxtor Atlas 15K II's.
At 5.5ms average access, 97.4MB/s outer track throughput, 85.9MB/s
average, and 74.4 MB/s inner track throughput, they have the best
performance characteristics of any tested shipping HDs I know
of.  (Supposedly the new SAS versions will _sustain_ ~98MB/s, but
I'll believe that only if I see it under independent testing).
In comparison, the numbers on the WD740GD are 8.1ms average access,
71.8, 62.9, and 53.9 MB/s outer, average and inner track throughputs
respectively.

Be prepared to use as many of them as possible (read: as many you can
afford) if you want to maximize transaction rates, particularly for
small transactions like this application seems to be mentioning.

Next, use a better RAID card.  The TOL enterprise stuff (Xyratex,
Engino, Dot-hill) is probably too expensive, but in the commodity
market benchmarks indicate that that Areca's 1GB buffer RAID cards
currently outperform all the other commodity RAID stuff.

9 Atlas II's per card in a RAID 5 set, or 16 per card in a RAID 10
set, should max the RAID card's throughput and come very close to, if
not attaining, the real world peak bandwidth of the 64b 133MHz PCI-X
bus they are plugged into.  Say somewhere in the 700-800MB/s range.

Repeat the above for as many independent PCI-X buses as you have for
a very fast commodity RAID IO subsystem.

Two such configured cards used in the dame manner as mentioned by
Alex should easily attain 1.5x - 2x the transaction numbers mentioned
by Alex unless there's a bottleneck somewhere else in the system design.

Hope this helps,
Ron Peacetree

At 08:40 AM 8/19/2005, Alex Turner wrote:
>I have managed tx speeds that high from postgresql going even as high
>as 2500/sec for small tables, but it does require a good RAID
>controler card (yes I'm even running with fsync on).  I'm using 3ware
>9500S-8MI with Raptor drives in multiple RAID 10s.  The box wasn't too
>$$$ at just around $7k.  I have two independant controlers on two
>independant PCI buses to give max throughput. on with a 6 drive RAID
>10 and the other with two 4 drive RAID 10s.
>
>Alex Turner
>NetEconomist
>
>On 8/19/05, Mark Cotner <mcotner@yahoo.com> wrote:
> > Hi all,
> > I bet you get tired of the same ole questions over and
> > over.
> >
> > I'm currently working on an application that will poll
> > thousands of cable modems per minute and I would like
> > to use PostgreSQL to maintain state between polls of
> > each device.  This requires a very heavy amount of
> > updates in place on a reasonably large table(100k-500k
> > rows, ~7 columns mostly integers/bigint).  Each row
> > will be refreshed every 15 minutes, or at least that's
> > how fast I can poll via SNMP.  I hope I can tune the
> > DB to keep up.
> >
> > The app is threaded and will likely have well over 100
> > concurrent db connections.  Temp tables for storage
> > aren't a preferred option since this is designed to be
> > a shared nothing approach and I will likely have
> > several polling processes.
> >
> > Here are some of my assumptions so far . . .
> >
> > HUGE WAL
> > Vacuum hourly if not more often
> >
> > I'm getting 1700tx/sec from MySQL and I would REALLY
> > prefer to use PG.  I don't need to match the number,
> > just get close.
> >
> > Is there a global temp table option?  In memory tables
> > would be very beneficial in this case.  I could just
> > flush it to disk occasionally with an insert into blah
> > select from memory table.
> >
> > Any help or creative alternatives would be greatly
> > appreciated.  :)
> >
> > 'njoy,
> > Mark
> >
> >
> > --
> > Writing software requires an intelligent person,
> > creating functional art requires an artist.
> > -- Unknown
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq
> >
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: Don't 'kill -9' the postmaster