Thread: pg_xlog safety
I reconfigured my database server this weekend based on some notes posted here. What I did was change the 5-disk RAID5 into a 4-disk RAID10 plus one spare drive. Now, this should have made things much faster, but from the looks of it, I'm not so sure it got faster. Reindexing the dump after loading the data took *way* longer than I expected. However, now that it is reindexed and vacuum analyzed, things do appear faster. I'm not sure if that's just from having the indexes rebuilt or what... 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, 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? -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= 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/
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