Re: pg_restore takes ages

scott.marlowe wrote:
> > > Ok, I just tested it. I put pg_xlog and pg_clog on a drive that was set
> > > to write cache disabled, and left the data on a drive where caching was
> > > enabled. The tps on a pgbench -c 5 -t 500 on the single drive was 45 to
> > > 55. With the pg_[xc]log moved to another drive and all, I got up to 108
> > > tps. About double performance, as you'd expect. I didn't test the data
> > > drive with write caching disabled, but my guess is it wouldn't be any
> > > slower since pgsql doesn't wait on the rest.
> > >
> > > I pulled the plug three times, and all three times the database came up in
> > > recovery mode and sucessfully recovered. I didn't bother testing to see
> > > if write caching would corrupt it as I'm pretty sure it would, it
> > > certainly did when everything was on one drive.
> >
> > You would have had to pull the plug between the time the system did a
> > checkpoint (and wrote to the write cache), and before it flushed the
> > write cache to disk --- no idea how you would find that window, but my
> > guess is that if you pulled the plug right after the checkpoint
> > completed, the WAL recovery would fail.
>
> I'm not sure what you mean. Are you talking about the failure more with
> write cache enabled? That always failed when I tested it. I was testing
> it with 80 parallel transactions, by the way. I'll try it anyway just to
> be sure that it causes the problem I'm expecting it to (i.e. write cache
> enabled on pg_xlog causes database corruption under heavy parallel load
> when plug is pulled.)

The issue is that a force write to disk is required for the sync() that
is run before the WAL files are recycled. You can get by with a write
cache enabled on the data drive as long as the crash doesn't happen in
the window between the sync (and WAL files removed) and the data
actually making it to the platers. If it does, I don't think the system
will recover, or if it does, it will not be consistent.

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