On 7/7/06, andy rost <andy.rost@xxxxxxxx> wrote:
Hi Merlin, Thanks for the input. Please see below ...
no problem! [aside: jeff, great advice on tps determination]
>> fsync = on # turns forced synchronization > have you tried turning this off and measuring performance? No, not yet. We're trying a couple of outher avenues before manipulating this parameter.
ok. just keep in mind that keeping fsync on keeps an aboslute upper limit on your tps that is largely driven by hardware. with a raid controller write caching controller caching writes the penalty is extremely low but without write caching you might get ~150 tps on a relatively high end raid system. moreover, your disks are totally utilized providing those 150 tps. (transactions with writing, that is) symptoms of sync bottleneck are little/no cpu utilization, sustained high iowait, and extremely poor performance and an unresponsive server. the best solution to sync issues is to have a hardware/software strategy designed to deal with it. if you are having periodic performance issues, you might be having checkpoint storms. Thse are controllable by tuning the wal and especially the bgwriter. These are easy to spot: you can do manual checkpoints in psql and closely monitor the load.
We've reduced the load significantly primarily by moving pg_xlog to its own drive and by increasing the effective cache size. While we still see high load levels, they don't last very long. We're trying improve performance from several angles but are taking it one step at a time. Eventually we'll experiment with fsynch
good! merlin
