Message-ID: <46498FC1.6060202@archonet.com>
Date: Tue, 15 May 2007 11:47:29 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Tarhon-Onu Victor <mituc@iasi.rdsnet.ro>
Cc: pgsql-performance@postgresql.org
Subject: Re: 500 requests per second
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>
In-Reply-To: 
 <Pine.LNX.4.64.0705151215070.28576@oynpxoyhr.vnfv.npgvirzrqvngrpu.pbz>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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?

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

-- 
   Richard Huxton
   Archonet Ltd