Date: Tue, 15 May 2007 18:28:37 -0500
From: "Jim C. Nasby" <decibel@decibel.org>
To: Richard Huxton <dev@archonet.com>
Cc: Tarhon-Onu Victor <mituc@iasi.rdsnet.ro>, pgsql-performance@postgresql.org
Subject: Re: 500 requests per second
Message-ID: <20070515232837.GB20707@nasby.net>
References: 
 <Pine.LNX.4.64.0705120936580.29679@oynpxoyhr.vnfv.npgvirzrqvngrpu.pbz>
	<464825DE.3090007@archonet.com>
	<Pine.LNX.4.64.0705151215070.28576@oynpxoyhr.vnfv.npgvirzrqvngrpu.pbz>
	<46498FC1.6060202@archonet.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <46498FC1.6060202@archonet.com>
User-Agent: Mutt/1.5.13 (2006-08-11)

On Tue, May 15, 2007 at 11:47:29AM +0100, Richard Huxton wrote:
> Tarhon-Onu Victor wrote:
> >On Mon, 14 May 2007, Richard Huxton wrote:
> >
> >>1. Is this one client making 500 requests, or 500 clients making one 
> >>request per second?
> >
> >    Up to 250 clients will make up to 500 requests per second.
> 
> Well, PG is pretty good at handling multiple clients. But if I'm 
> understanding you here, you're talking about potentially 250*500=125000 
> updates per second. If each update writes 1KB to disk, that's 125MB/sec 
> continuously written. Are these figures roughly correct?
 
I'm guessing it's 500TPS overall, not per connection. It'd be rather
challenging just to do 125,000 network round trips per second.

> >>2. Do you expect the indexes at least to fit in RAM?
> >
> >    not entirely... or not all of them.
> 
> Hmm - OK. So you're going to have index reads accessing disk as well. 
> Exactly how big are you looking at here?
> Will it be constantly growing?
> Can you partition the large table(s) by date or similar?
> 
> Well, the two things I'd expect to slow you down are:
> 1. Contention updating index blocks
> 2. Disk I/O as you balance updates and selects.
> 
> Since you're constantly updating, you'll want to have WAL on a separate 
> set of disks from the rest of your database, battery-backed cache on 
> your raid controller etc. Check the mailing list archives for recent 
> discussions about good/bad controllers. You'll also want to 
> substantially increase checkpoint limits, of course.
> 
> If you can cope with the fact that there's a delay, you might want to 
> look at replication (e.g. slony) to have reads happening on a separate 
> machine from writes. That may well not be possible in your case.

Just as a data point, I've worked with some folks that are doing ~250TPS
on a disk array with around 20-30 drives. IIRC a good amount of their
working set did fit into memory, but not all of it.

Your biggest constraint is really going to be I/O operations per second.
If 90% of your data is in cache then you'll need to do a minimum of
50IOPS (realistically you'd probably have to double that). If 50% of
your working set fits in cache you'd then be looking at 250IOPS, which
is a pretty serious rate.

I very strongly encourage you to do benchmarking to get a feel for how
your system performs on a given set of hardware so that you have some
idea of where you need to get to. You should also be looking hard at
your application and system architecture for ways to cut down on your
throughput. There may be some things you can do that would reduce the
amount of database hardware you need to buy.
-- 
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)