SEO

Tom Lane wrote:
>> Because we don't have separate disks for WAL in our test environment,
>> I'd like to minimize the effect of WAL. I've read the sections in
>> both the tuning guide and the base documentation, and I'm still a
>> little unclear about fsync and wal_sync_method. If I set fsync
>> FALSE, does this disable WAL entirely?
>
> No, but in any case it would entirely invalidate any performance
> testing you might do. fsync off is a completely different regime of
> operation (unless perhaps you have hardly any updates).

Thanks, Tom. This app has very high insert activity. What's the best
way to minimize the effect of WAL given that we don't have separate
disks to put it on in our test environment?

--
Guy Rouillier


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

"Guy Rouillier" <guyr@masergy.com> writes:
> Thanks, Tom. This app has very high insert activity. What's the best
> way to minimize the effect of WAL given that we don't have separate
> disks to put it on in our test environment?

There's not a lot you can do about it :-(. You can take some amount of
comfort in the idea that the numbers you get should be a lower bound on
the performance with WAL on a separate disk, though.

To some extent you are looking at this backwards. In a high-update
situation it is WAL that is the bottleneck, by design (ie that is the
way it should be). Rather than "minimizing the effect of WAL" what you
want to minimize is the effects of having to move the disk arms away
from the WAL file to write data files.

Thinking along that line, you want to try to avoid doing data file
writes except at checkpoints, and spread out checkpoints as far as
possible. So bump up the checkpoint spacing parameters, and maybe
increase shared_buffers more than you normally would, and if you are
using 8.0 then disable the bgwriter. This will result in bigger and
uglier spikes at checkpoints than the real environment, but perhaps
you can discount those in your testing.

Obviously this is just a crude approximation but it's about the best you
are going to do, given that the disk setup is fundamentally different
from what you intend to do in production.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match