On Mon, 5 Dec 2005, Thomas Harold wrote:
(noob question incoming)
Section 26.4 WAL Internals
http://www.postgresql.org/docs/8.1/interactive/wal-internals.html
This seems to be the applicable chapter. They talk about creating a symlink
for the data/pg_xlog folder to point at another disk set.
If I have (2) RAID1 sets with LVM2, can I instead create a logical volume on
the 2nd disk set and just mount data/pg_xlog to point at the logical volume
on the 2nd disk set?
For example, I have an LVM on my primary mirror called 'pgsql'. And I've
created a 2nd LVM on my secondary mirror called 'pgxlog'. These are mounted
as:
/dev/vgraida/pgsql on /var/lib/postgresql type ext3 (rw,noatime)
/dev/vgraidb/pgxlog on /var/lib/postgresql/data/pg_xlog type ext3
(rw,noatime)
From the application's P.O.V., it's the same thing, right? (It seems to be
working, I'm just trying to double-check that I'm not missing something.)
the application can' tell the difference, but the reason for seperating
them isn't for the application, it's so that different pieces of hardware
can work on different things without having to bounce back and forth
between them.
useing the same drives with LVM doesn't achieve this goal.
the problem is that the WAL is doing a LOT of writes, and postgres waits
until each write is completed before going on to the next thing (for
safety), if a disk is dedicated to the WAL then the head doesn't move
much. if the disk is used for other things as well then the heads have to
move across the disk surface between the WAL and where the data is. this
drasticly slows down the number of items that can go into the WAL, and
therefor slows down the entire system.
this slowdown isn't even something as simple as cutting your speed in half
(half the time spent working on the WAL, half spent on the data itself),
it's more like 10% spent on the WAL, 10% spent on the data, and 80%
moveing back and forth between them (I am probably wrong on the exact
numbers, but it is something similarly drastic)
this is also the reason why it's so good to have a filesystem journal on a
different drive.
David Lang
---------------------------(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
