Re: Hardware recommendations to scale to silly load

On Thu, 2003-08-28 at 12:37, Matt Clark wrote:
> > Ok.. I would be surprised if you needed much more actual CPU power. I
> > suspect they're mostly idle waiting on data -- especially with a Quad
> > Xeon (shared memory bus is it not?).
>
> In reality the CPUs get pegged: about 65% PG and 35% system. But I agree that memory throughput and latency is an issue.

system in this case is dealing with disk activity or process switches?

Usually the 65% includes the CPU waiting on a request for data from main
memory. Since you will be moving a lot of data through the CPU, the L1 /
L2 cache doesn't help too much (even large cache), but low latency high
bandwidth memory will make a significant difference. CPUs not having to
wait on other CPUs doing a memory fetch will make an even larger
difference (dedicated memory bus per CPU).

Good memory is the big ticket item. Sun CPUs are not better than Intel
CPUs, for simple DB interaction. It's the additional memory bandwidth
that makes them shine. Incidentally, Suns are quite slow with PG for
calculation intensive work on a small dataset.

> > Write performance won't matter very much. 3000 inserts/second isn't high
> > -- some additional battery backed write cache may be useful but not
> > overly important with enough ram to hold the complete dataset. I suspect
> > those are slow due to things like foreign keys -- which of course are
> > selects.
>
> 3000 inserts/sec isn't high when they're inside one transaction, but if each is inside its own transaction then that's 3000
> commits/second.

Still not anything to concern yourself with. WAL on battery backed
write cache (with a good controller) will more than suffice -- boils
down to the same as if fsync was disabled. You might want to try putting
it onto it's own controller, but I don't think you will see much of a
change. 20k WAL operations / sec would be something to worry about.

> > case, additional ram will keep the system from hitting the disk for
> > writes as well.
>
> How does that work?

Simple. Your OS will buffer writes in memory until they are required to
hit disk (fsync or similar). Modify the appropriate sysctl to inform
the OS it can use more than 10% (10% is the FreeBSD default I believe)
of the memory for writes. Buffering 4GB of work in memory (WAL logs
will ensure this is crash safe) will nearly eliminate I/O.

When the OS is no longer busy, it will filter the writes from ram back
to disk. Visibly, there is no change to the user aside from a speed
increase.

> > You may want to play around with checkpoints. Prevention of a checkpoint
> > during this hour will help prevent peaks. Be warned though, WAL will
> > grow very large, and recovery time should a crash occur could be
> > painful.
>
> Good point. I'll have a think about that.

This is more important with a larger buffer. A checkpoint informs the OS
to dump the buffer to disk so it can guarantee it hit hardware (thus
allowing PG to remove / recycle WAL files).

I do think your best bet is to segregate the DB. Read / write, by user
location, first 4 digits of the credit card, anything will make a much
better system.

Keep a master with all of the data that can take the full week to
process it.