pg_xlog safety

On Tue, Jan 21, 2003 at 03:43:40PM -0500, Vivek Khera wrote:

So this leads me to my question: one of the tips for performance is to
move the pg_xlog to its own disk. Now that I have a spare disk I was
considering moving pg_xlog there. However, that's the only disk that

Test this first. On our Sun A5200 arrays, configured using Veritas
and RAID 1+0, I have tried several times and failed to discover a
performance andvantage by putting the WAL on another disk or pair of
disks. (In other configurations, the same is not the case.)

wouldn't be RAID protected. What would happen to the db if that disk
failed? If I were able to perform a clean shutdown, replace that
disk, and start up postgres again, would it be ok with the pg_xlog
directory empty? Is it only used for startup after an unclean
shutdown or for every startup?

I believe you'd need to run pg_resetxlog in this case.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Vivek Khera <khera@kcilink.com> writes:

So this leads me to my question: one of the tips for performance is to
move the pg_xlog to its own disk. Now that I have a spare disk I was
considering moving pg_xlog there. However, that's the only disk that
wouldn't be RAID protected. What would happen to the db if that disk
failed? If I were able to perform a clean shutdown,

That's a mighty big "if". I would rather expect a failure on the WAL
drive to cause PG to shut down uncleanly (there's elog(PANIC) all
through the WAL-writing code). Whereupon you're pretty much hosed;
you can do pg_resetxlog but you then have no idea about the consistency
of your main data area.

I think you'd be tossing away the reliability advantage of having a RAID
array at all.

regards, tom lane

"TL" == Tom Lane <tgl@sss.pgh.pa.us> writes:

TL> I think you'd be tossing away the reliability advantage of having a RAID
TL> array at all.

Thanks. I thought this might be the case but wanted to make sure.

I'll just the spare drive for logging, temp files, etc.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera@kciLink.com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

Andrew Sullivan wrote:

On Tue, Jan 21, 2003 at 03:43:40PM -0500, Vivek Khera wrote:

So this leads me to my question: one of the tips for performance is to
move the pg_xlog to its own disk. Now that I have a spare disk I was
considering moving pg_xlog there. However, that's the only disk that

Test this first. On our Sun A5200 arrays, configured using Veritas
and RAID 1+0, I have tried several times and failed to discover a
performance andvantage by putting the WAL on another disk or pair of
disks. (In other configurations, the same is not the case.)

If you raid has battery-backed RAM cach in your controller, there is no
advantage to putting WAL on a separate disk --- was that the case?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Sun, Jan 26, 2003 at 05:26:15PM -0500, Bruce Momjian wrote:

If you raid has battery-backed RAM cach in your controller, there is no
advantage to putting WAL on a separate disk --- was that the case?

That was always my view, as well. But someone presented me with an
argument that it would somehow be better to use separate disks inside
the array in order to make this faster (i.e. it'd still be faster
because there'd be no contention for spindles). So, I tried it, but
I could see no difference no matter what I did.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Andrew Sullivan wrote:

On Sun, Jan 26, 2003 at 05:26:15PM -0500, Bruce Momjian wrote:

If you raid has battery-backed RAM cach in your controller, there is no
advantage to putting WAL on a separate disk --- was that the case?

That was always my view, as well. But someone presented me with an
argument that it would somehow be better to use separate disks inside
the array in order to make this faster (i.e. it'd still be faster
because there'd be no contention for spindles). So, I tried it, but
I could see no difference no matter what I did.

With battery-backed RAM, you would have to fill up that cache faster
than it can flush it (at its leasure) to the disk. It would take a heck
of a lot of activity to cause that.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On Mon, Jan 27, 2003 at 06:15:12PM -0500, Bruce Momjian wrote:

With battery-backed RAM, you would have to fill up that cache faster
than it can flush it (at its leasure) to the disk. It would take a heck
of a lot of activity to cause that.

Well, we do have a lot of activity :) But not that much, I agree. I
was pretty sceptical. It was nevertheless worth trying, just to test
my assumptions.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110