Re: Adding disks/xlog & index

lists(at)on-track(dot)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