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.
Description: This is a digitally signed message part