Re: Scaling with memory & disk planning (was Re: Non-linear Performance)

Doug Fields <dfields-pg-general(at)pexicom(dot)com> writes:
> d) How much extra performance does having the log or indices on a different
> disk buy you, esp. in the instance where you are inserting millions of
> records into a table? An indexed table?

Keeping the logs on a separate drive is a big win, I believe, for heavy
update situations. (For read-only queries, of course the log doesn't
matter.)

Keeping indexes on a separate drive is also traditional database advice,
but I don't have any feeling for how much it matters in Postgres.

> a) Run everything on one 7-drive RAID 5 partition (8th drive as hot spare)
> b) Run logs as a 2-drive mirror and the rest on a 5-drive RAID 5
> c) Run logs on a 2-drive mirror, indices on a 2-drive mirror, and the rest
> on a 3-drive RAID5?
> d) Run logs & indices on a 2-drive mirror and the rest on a 5-drive RAID 5

You could probably get away without mirroring the indices, if you are
willing to incur a little downtime to rebuild them after an index drive
failure. So another possibility is

2-drive mirror for log, 1 plain old drive for indexes, rest for data.

If your data will fit on 2 drives then you could mirror both, still have
your 8th drive as hot spare, and feel pretty secure.

Note that while it is reasonably painless to configure PG with WAL logs
in a special place (after initdb, move the pg_xlog subdirectory and make
a symlink to its new location), it's not currently easy to separate
indexes from data. So the most practical approach in the short term is
probably your (b).

regards, tom lane