Re: Opteron/FreeBSD/PostgreSQL performance poor

On 7/7/06, andy rost <andy(dot)rost(at)noaa(dot)gov> 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