Thread: Adding disks/xlog & index
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.
lists@on-track.ca writes:
> 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.
Yeah, two names for same thing.
> I also think it may be beneficial to move some indexes to another drive as
> well (same one as xlog).
Depends on how the I/O workload works out. On systems that have fairly
heavy write traffic, the standard advice is that you want WAL on its own
dedicated spindle, because the less that head needs to move the faster
you can write WAL, and WAL output speed is going to determine how fast
you can perform updates.
If it's a read-mostly database then maybe you can ignore that advice and
worry more about separating indexes from tables.
> 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).
Do not go cheap on the WAL drive --- you lose WAL, you're in serious
trouble. Indexes can always be rebuilt with REINDEX, so they're maybe
a bit more expendable.
> 3. Is there any easy way to move ALL indexes to another drive?
No, I think you have to move 'em one at a time :-(. The standard advice
for this is to set up a plpgsql function that scans the catalogs and
issues the commands you want (ALTER INDEX SET TABLESPACE in this case).
> Is this a
> good performance idea or would they just bottleneck each other seriously?
Impossible to tell without a lot more details than you provided. I'd
suggest you try it and see.
regards, tom lane
lists@on-track.ca writes:
> 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.
Yeah, two names for same thing.
> I also think it may be beneficial to move some indexes to another drive as
> well (same one as xlog).
Depends on how the I/O workload works out. On systems that have fairly
heavy write traffic, the standard advice is that you want WAL on its own
dedicated spindle, because the less that head needs to move the faster
you can write WAL, and WAL output speed is going to determine how fast
you can perform updates.
If it's a read-mostly database then maybe you can ignore that advice and
worry more about separating indexes from tables.
> 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).
Do not go cheap on the WAL drive --- you lose WAL, you're in serious
trouble. Indexes can always be rebuilt with REINDEX, so they're maybe
a bit more expendable.
> 3. Is there any easy way to move ALL indexes to another drive?
No, I think you have to move 'em one at a time :-(. The standard advice
for this is to set up a plpgsql function that scans the catalogs and
issues the commands you want (ALTER INDEX SET TABLESPACE in this case).
> Is this a
> good performance idea or would they just bottleneck each other seriously?
Impossible to tell without a lot more details than you provided. I'd
suggest you try it and see.
regards, tom lane
<lists@on-track.ca> writes: > 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. Losing any WAL that the database has fsynced is exactly like having fsync off. > 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? Well that depends on your database traffic. In most databases the volume of WAL traffic is substantially less than the i/o traffic to the data drives. So you usually don't need to be able to sustain high i/o bandwidth to the WAL drive. However in some database loads the latency to the WAL drive does matter. This is especially true if you're executing a lot of short transactions and response time is critical. Especially if you aren't executing many such transactions in parallel. So for example if you're processing a serial batch of short transactions and committing each one as a separate transaction. In that case you would want a drive that can fsync fast which either means a battery backed cache or 15kRPM drive. It doesn't necessarily mean you need a bit raid array though. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com