I have a busy postgresql server running running on a raid1 of 2 15k rpm
scsi drives.
I have been running into the problem of maxed out IO bandwidth. I would
like to convert my raid1 into a raid10 but that would require a full
rebuild which is more downtime than I want so I am looking into other
alternatives.
The best one I have come up with is moving the xlog/wal (can someone
confirm whether these are the same thing?) to another physical drive. I
also think it may be beneficial to move some indexes to another drive as
well (same one as xlog).
Some questions on this:
1. Can the database survive loss/corruption of the xlog and indexes in a
recoverable way? To save money (and because I won't need the throughput as
much), I am thinking on making this index/wal/xlog drive a single cheap
sata drive (or maybe a non-raided 15k scsi for 60% more money). However
without the redundancy of a mirror I am concerned about drive failure.
Loss of several mins of recent transactions in a serious crash is
acceptable to be, but full/serious database corruption (the likes of fsync
off) is not.
2. Is there any point using a high performance (ie scsi) disk for this, or
would the mirror containing the majority of the data still be the major
bottleneck causing the disk usage to not exceed sata performance anyway?
3. Is there any easy way to move ALL indexes to another drive? Is this a
good performance idea or would they just bottleneck each other seriously?
Other info for reference
Running postgresql 8.2 on FreeBSD 6.1
server is a core2 with 4gb of ram. CPU usage is moderate.
Also, can anyone recommend a good shared_buffers size? The server is
dedicated to postgres except for half a gig used by memcached. Right now I
have it set at 51200 which may be too high (I've read varying suggestions
with this and I'm not sure how aggressive FreeBSD6's IO cache is).
And any suggestions on what effective_cache_size I should use on this
hardware and OS? I've been using 384MB but I don't know if this is optimal
or not.
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
