Re: 500 requests per second

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