WAL Optimisation - configuration and usage

Hi,

There alot here, so skip to the middle from my WAL settings if you like.

I'm currently investigating the performance on a large database which
consumes email designated as SPAM for the perusal of customers wishing
to check. This incorporates a number of subprocesses - several delivery
daemons, an expiry daemon and a UI which performs large selects. A
considerable amount of UPDATE, SELECT and DELETE are performed continually.

Starting with a stock pg config, I've well understood the importance
increased shared mem, effective cache size and low random_page_cost as
detailed in
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html. After some
system analysis with vmstat and sar we've been able to determin that the
main problem is IO bound and IMO this is due to lots of updates
requiring high drive contention - the array is a RAID0 mirror and the
dataset originally 79GB. Alot of SPAM is being sent from our mail
scanners and coupled with the UI is creating an increasingly lagging system.

Typically all our db servers have these sort of enhancements - 1GB ram,
SMP boxen with SCSI 160 disks :
effective_cache_size = 95694
random_page_cost = 0.5
sort_mem=65536
max_connections = 128
shared_buffers = 15732

My focus today has been on WAL - I've not looked at WAL before. By
increasing the settings thus :

wal_buffers = 64 # need to determin WAL usage
wal_files = 64 # range 0-64
wal_sync_method = fsync # the default varies across platforms:
wal_debug = 0 # range 0-16

# hopefully this should see less LogFlushes per LogInsert - use more WAL
though.
commit_delay = 10000 # range 0-100000
commit_siblings = 2 # range 1-1000
checkpoint_segments = 16 # in logfile segments (16MB each), min 1
checkpoint_timeout = 300 # in seconds, range 30-3600
fsync = true

great improvements have been seen. A vacuumdb -f -a -z went from
processing 1 table in 10 minutes to 10 tables in 1 minute. :) I actually
stopped it after 80 tables (48 hours runtime) because the projected end
time would have been next week. Once I restarted the postmaster with the
above WAL settings, vacuumdb -f -a -z completed all 650 tables by the
following day.

My thinking is therefore to reduce disk context switching as best as
possible within the current hardware limitiations. I'm aiming at keeping
the checkpoint subprocess happy that other backends are about to commit
- hence keep siblings low at 2 - and create a sufficient gap between
internal commital so many commits can be done in a single sync. From the
above config, I believe I've gone some way to acheive this and the
performance I'm now seeing suggests this.

But I think we can get more out of this as the above setting were picked
from thin air and my concern here is being able to determin WAL file
usage and if the system is caught out on the other extreme that we're
not commiting fast enough. Currently I've read that WAL files shouldn't
be more than 2*checkpoint_segments+1 however my pg_xlog directory
contains 74 files. This suggests I'm using more logfiles than I should.
Also I'm not sure what wal_buffers really should be set to.

Can I get any feedback on this ? How to look into pg's WAL usage would
be what I'm looking for. BTW this is an old install I'm afraid 7.2.2 -
it's been impossible to upgrade up until now because it's been too slow.
I have moved the pg_xlog onto the root SCSI disk - it doesn't appear to
have made a huge difference but it could be on the same cable.

Additional information as a bit of background :
I can supply sar output if required. I'm currently running our expiry
daemon which scans all mail for each domain (ie each table) and this
seems to take a few hours to run on a 26GB archive. It's alot faster
than it ever was. Load gets to about 8 as backends are all busy doing
selects, updates and deletes. This process has recently already been run
so it shouldn't be doing too much deleting. Still seems IO bound, and I
don't think I'm going to solve that without a better disk arrangement,
but this is essentially what I'm doing now - exhausting other possibilities.

$ sar -B -s 16:00:00

16:35:55 pgpgin/s pgpgout/s activepg inadtypg inaclnpg inatarpg
16:36:00 3601.60 754.40 143492 87791 10230 48302
16:36:05 5766.40 552.80 143947 88039 10170 48431
16:36:10 3663.20 715.20 144578 88354 9075 48401
16:36:15 3634.40 412.00 144335 88405 9427 48433
16:36:20 5578.40 447.20 143626 88545 9817 48397
16:36:25 4154.40 469.60 143640 88654 10388 48536
16:36:30 3504.00 635.20 143538 88763 9992 48458
16:36:35 3540.80 456.00 142515 88949 10444 48381
16:36:40 3334.40 1067.20 143268 89244 9832 48468

$ vmstat 5
procs memory swap io
system cpu
r b w swpd free buff cache si so bi bo in cs us
sy id
0 7 1 29588 10592 15700 809060 1 0 97 75 0 103
13 9 79
3 8 0 29588 11680 15736 807620 0 0 3313 438 1838 3559 19
13 68
2 13 1 29588 12808 15404 800328 0 0 4470 445 1515 1752
7 7 86
0 9 1 29588 10992 15728 806476 0 0 2933 781 1246 2686 14
10 76
2 5 1 29588 11336 15956 807884 0 0 3354 662 1773 5211 27
17 57
4 5 0 29696 13072 16020 813872 0 24 4282 306 2632 7862 45
25 31
4 6 1 29696 10400 16116 815084 0 0 5086 314 2668 7893 47
26 27
9 2 1 29696 13060 16308 814232 27 0 3927 748 2586 7836 48
29 23
3 8 1 29696 10444 16232 812816 3 0 4015 433 2443 7180 47
28 25
8 4 0 29696 10904 16432 812488 0 0 4537 500 2616 8418 46
30 24
4 6 2 29696 11048 16320 810276 0 0 6076 569 1893 3919 20
14 66
0 5 0 29696 10480 16600 813788 0 0 4595 435 2400 6215 33
21 46
3 6 0 29696 10536 16376 812248 0 0 3802 504 2417 7921 43
25 32
1 6 1 29696 11236 16500 809636 0 0 3691 357 2171 5199 24
15 61
0 14 1 29696 10228 16036 801368 0 0 4038 561 1566 3288 16
12 72

Sorry it's so long but I thought some brief info would be better than
not. Thanks for reading,

--

Rob Fielding
Development
Designer Servers Ltd