Re: pg_restore takes ages

On Fri, 3 Oct 2003, Alvaro Herrera wrote:

> On Fri, Oct 03, 2003 at 01:06:26PM -0600, scott.marlowe wrote:
>
> > also, turning off fsync during the load helps a lot.  Be sure to turn it
> > back on when you're done of course.
>
> I'm not sure I understand why this is so.  If I turn fsync off, it means
> that I won't force the kernel to write WAL logs to disk, but they will
> have to be written eventually.  If you have tons of RAM it may well be
> that the kernel will just keep dirty buffers in RAM, but if not there
> should not be any difference.  Am I missing something?

Yes, you are.  Basically, with fsync on, things have to happen in order.

I.e.

write to WAL what you're gonna do.  WAIT for confirmation on write
write the tuples out.  wait for confirmation
checkpoint the WAL.  wait for confirmation

Notice the wait for confirmation above.  Without fsync, there's no wait,
you just write it all out at once, and hope the machine / database doesn't
ever crash in the middle of a transaction.

Give it a try, turn off fsync, run pgbench -c 4 -t 1000, then turn it back
on and see how much it slows down.

Pull the plug while the transactions are running with fsync on, and your
machine, assuming it has a meta-data journaling file system, will come
right back, and postgresql will replay the WAL files and you'll have a
nice consistent database.

turn off fsync, initiate many transactions, pull the plug, and look at
your corrupted database refuse to start on update.

Note that if you're running on IDE drives, you already ARE probably
running with fsync off if write caching is enabled, so you'll need to turn
it off (hdparm -W0 /dev/hdx in linux) to ensure fsync actually works.