Re: Scaling with memory & disk planning (was Re: Non-li

For fairly large DB's we have noticed quite a difference.
Also using 0+1 (RAID 10?) striping helped increaed throughput by 400%!!
I know how to keep the binaries, logs, and data on different spindles.
(Actually the data is striped across about 6 spindles), but how do you
put indices on a different spindle? Also, for Solaris systems, Veritas
caching and DB file system makes a noticeable improvement. It's expensive,
but if you need every last bit of speed...

I've looked through the Postgres FAQ sites, is there any "formula" for
how to set system varibale in general? (i.e. shmem, etc...) I suppose
something like... http://www.samag.com/documents/s=1151/sam0105b/0105b.htm

This is a Oracle/Solaris example, but hopefully you get the idea.

Ray

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Thursday, May 30, 2002 9:34 AM
To: Doug Fields
Cc: Peter A. Daly; pgsql-general(at)postgresql(dot)org
Subject: Re: Scaling with memory & disk planning (was Re: [GENERAL]
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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org