Thread: Simulating WAL on separate disks
We are soon to be doing performance testing. Our testing environment contains 4 SCSI disks in a RAID5 configuration, while our production environment will have 6 SCSI disks, which I'm suggesting we allocate as 4 in RAID5 for data and 2 in RAID1 for WAL. 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? -- Guy Rouillier
"Guy Rouillier" <guyr@masergy.com> writes:
> We are soon to be doing performance testing. Our testing environment
> contains 4 SCSI disks in a RAID5 configuration, while our production
> environment will have 6 SCSI disks, which I'm suggesting we allocate as
> 4 in RAID5 for data and 2 in RAID1 for WAL.
> 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).
regards, tom lane
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
"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
I would also suggest in addition to the other folks comments that you benchmark the 4 disks in RAID 10 in addition to RAID 5. RAID 5 typicaly has poor write performance because it must read the stripe, modify, calculate new parity and write, and if you are doing alot of inserts, you may find that RAID 10 performs better, particularly with large amounts of random writes (I actualy have benchmarking information for the 3ware escalade 9500S series RAID cards that I acquired from AMCC yestarday that shows these numbers to be true - of course each controler will vary with milleage (RAID 10: 91.4MBwrite/sec 256.2 IO/sec write, RAID 5: 75.4MBwrite/sec, 107.2 IO/sec write for a 4 Drive array)). Splitting WAL and Table data is an excellent way to speed up Pgsql, we do this and it is indeed very effective. Alex Turner NetEconomist On Mon, 10 Jan 2005 17:33:51 -0600, Guy Rouillier <guyr@masergy.com> wrote: > We are soon to be doing performance testing. Our testing environment > contains 4 SCSI disks in a RAID5 configuration, while our production > environment will have 6 SCSI disks, which I'm suggesting we allocate as > 4 in RAID5 for data and 2 in RAID1 for WAL. > > 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? > > -- > Guy Rouillier > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html >