

> -----Original Message-----
> From: Tatsuo Ishii [mailto:t-ishii@sra.co.jp] 
> Sent: 07 March 2003 01:33
> To: Dave Page
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Win32 Powerfail testing
> 
> 
> > > As I said in the previlus mails, open()+_commit() does the
> > > right job with the transaction log files. So probably I think 
> > > I should stick with open()+_commit() approach for ordinary 
> > > table/index files too.
> > 
> > Oh, I didn't see that message. So it's either:
> > 
> > open() + _commit()
> 
> Sorry, I did not mention it explicitly. I meant we use the 
> same implementation as Jan's work. He uses open()+_commit(), 
> I believe.

Ah, but Jan/Katie's code *did not* survive the powerfails. Is there a
relatively easy way we can test open()/_commit against
CreateFile()/FlushFileBuffers() with the FILE_FLAG_WRITE_THROUGH flag as
suggested by Magnus (and indirectly by Merlin I guess)?

Regards, Dave.

Fri, 7 Mar 2003 08:18:47 -0000
"Dave Page" <dpage@vale-housing.co.uk>
Re: Win32 Powerfail testing
> > > > As I said in the previlus mails, open()+_commit() does the
> > > > right job with the transaction log files. So probably I think 
> > > > I should stick with open()+_commit() approach for ordinary 
> > > > table/index files too.
> > > 
> > > Oh, I didn't see that message. So it's either:
> > > 
> > > open() + _commit()
> > 
> > Sorry, I did not mention it explicitly. I meant we use the 
> > same implementation as Jan's work. He uses open()+_commit(), 
> > I believe.
> 
> Ah, but Jan/Katie's code *did not* survive the powerfails. Is there a
> relatively easy way we can test open()/_commit against
> CreateFile()/FlushFileBuffers() with the FILE_FLAG_WRITE_THROUGH flag as
> suggested by Magnus (and indirectly by Merlin I guess)?

There are two stages where a synchronized write is needed. One is WAL
log writing. We confirmed that with open()/_commit this is ok.

The other is checkpoint. Here we need to flush kernel buffers holding
previous write to table/index files. To sync those files, PostgreSQL
uses sync(). I guess Jan's implementatin did not survive in this case
(mine neither).

Today I revisited the implemnetation (replacing sync() with
open/_commit) I made several days ago and found a bug with it (thanks
to Hiroshi). With the fixed version of it, now my Win32 port has
passed your test even right after checkpoint!.
--
Tatsuo Ishii

Fri, 07 Mar 2003 17:37:09 +0900 (JST)
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Win32 Powerfail testing
Tatsuo Ishii wrote:
> Today I revisited the implemnetation (replacing sync() with
> open/_commit) I made several days ago and found a bug with it (thanks
> to Hiroshi). With the fixed version of it, now my Win32 port has
> passed your test even right after checkpoint!.

I presume that this implementation tracks which files have been opened
and uses _commit() to write all the changes to disk for those files?

If so, then it would be of significant value, IMHO, if you could
abstract the changes in such a way that they could be applied to the
Unix side as well.

sync() writes *all* uncommitted buffers to disk, whether or not they
belong to the process or process group that initiated the sync().  On
systems which do more than just host PG, a sync() does more work
(sometimes much more work) than is necessary and will unnecessarily
burden the system with writes.  I think it would be a win, from a
design standpoint if nothing else, if PG committed only those pages
that it was responsible for.

The Unix equivalent of _commit() appears to be fsync() or fdatasync().
So it sounds a lot like a "port" to Unix of the changes you have made
for this might easily be a trivial search and replace.  :-)

-- 
Kevin Brown					      kevin@sysexperts.com

Fri, 7 Mar 2003 02:05:41 -0800
Kevin Brown <kevin@sysexperts.com>
Re: Win32 Powerfail testing
Kevin Brown kirjutas R, 07.03.2003 kell 12:05:
> Tatsuo Ishii wrote:
> > Today I revisited the implemnetation (replacing sync() with
> > open/_commit) I made several days ago and found a bug with it (thanks
> > to Hiroshi). With the fixed version of it, now my Win32 port has
> > passed your test even right after checkpoint!.
> 
> I presume that this implementation tracks which files have been opened
> and uses _commit() to write all the changes to disk for those files?

But are there quarantees that all closed files are flushed to disk as
well ?

Does postgres quarantee it by doing a _commit() before close() or do
file system semantics quarantee that filehits the disk whan close()'d (I
guess it does not not).

-------------
Hannu


07 Mar 2003 12:28:26 +0200
Hannu Krosing <hannu@tm.ee>
Re: Win32 Powerfail testing
> > I presume that this implementation tracks which files have been opened
> > and uses _commit() to write all the changes to disk for those files?
> 
> But are there quarantees that all closed files are flushed to disk as
> well ?
> 
> Does postgres quarantee it by doing a _commit() before close() or do
> file system semantics quarantee that filehits the disk whan close()'d (I
> guess it does not not).

Of course no. So I remember all file names to be flushed in a table
(placed on thread global memory) and later open/_commit/close them at
the checkpoint time.
--
Tatsuo Ishii

Fri, 07 Mar 2003 22:04:42 +0900 (JST)
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Win32 Powerfail testing
Kevin Brown wrote:
> Tatsuo Ishii wrote:
> > Today I revisited the implemnetation (replacing sync() with
> > open/_commit) I made several days ago and found a bug with it (thanks
> > to Hiroshi). With the fixed version of it, now my Win32 port has
> > passed your test even right after checkpoint!.
> 
> I presume that this implementation tracks which files have been opened
> and uses _commit() to write all the changes to disk for those files?
> 
> If so, then it would be of significant value, IMHO, if you could
> abstract the changes in such a way that they could be applied to the
> Unix side as well.
> 
> sync() writes *all* uncommitted buffers to disk, whether or not they
> belong to the process or process group that initiated the sync().  On
> systems which do more than just host PG, a sync() does more work
> (sometimes much more work) than is necessary and will unnecessarily
> burden the system with writes.  I think it would be a win, from a
> design standpoint if nothing else, if PG committed only those pages
> that it was responsible for.
> 
> The Unix equivalent of _commit() appears to be fsync() or fdatasync().
> So it sounds a lot like a "port" to Unix of the changes you have made
> for this might easily be a trivial search and replace.  :-)

The idea of using this on Unix is tempting, but Tatsuo is using a
threaded backend, so it is a little easier to do.  However, it would
probably be pretty easy to write a file of modified file names that the
checkpoint could read and open/fsync/close.

Of course, if there are lots of files, sync() may be faster than
opening/fsync/closing all those files.

-- 
  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

Fri, 7 Mar 2003 12:20:46 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Win32 Powerfail testing
Bruce Momjian wrote:
> The idea of using this on Unix is tempting, but Tatsuo is using a
> threaded backend, so it is a little easier to do.  However, it would
> probably be pretty easy to write a file of modified file names that the
> checkpoint could read and open/fsync/close.

Even that's not strictly necessary -- we *do* have shared memory we
can use for this, and even when hundreds of tables have been written
the list will only end up being a few tens of kilobytes in size (plus
whatever overhead is required to track and manipulate the entries).

But even then, we don't actually have to track the *names* of the
files that have changed, just their RelFileNodes, since there's a
mapping function from the RelFileNode to the filename.

> Of course, if there are lots of files, sync() may be faster than
> opening/fsync/closing all those files.

This is true, and is something I hadn't actually thought of.  So it
sounds like some testing would be in order.

Unfortunately I know of no system call which will take an array of
file descriptors (or file names!  May as well go for the gold when
wishing for something :-) and sync them all to disk in the most
optimal way...


-- 
Kevin Brown					      kevin@sysexperts.com

Fri, 7 Mar 2003 12:43:01 -0800
Kevin Brown <kevin@sysexperts.com>
Re: Win32 Powerfail testing
Kevin Brown wrote:
> Bruce Momjian wrote:
> > The idea of using this on Unix is tempting, but Tatsuo is using a
> > threaded backend, so it is a little easier to do.  However, it would
> > probably be pretty easy to write a file of modified file names that the
> > checkpoint could read and open/fsync/close.
> 
> Even that's not strictly necessary -- we *do* have shared memory we
> can use for this, and even when hundreds of tables have been written
> the list will only end up being a few tens of kilobytes in size (plus
> whatever overhead is required to track and manipulate the entries).
> 
> But even then, we don't actually have to track the *names* of the
> files that have changed, just their RelFileNodes, since there's a
> mapping function from the RelFileNode to the filename.

But we have to allow an unlimited number of files.  Perhaps we could
just fall back to sync if the shared memory overflows, and shared memory
is finite.

-- 
  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

Fri, 7 Mar 2003 16:01:18 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Win32 Powerfail testing
Bruce Momjian wrote:
> Kevin Brown wrote:
> > Bruce Momjian wrote:
> > > The idea of using this on Unix is tempting, but Tatsuo is using a
> > > threaded backend, so it is a little easier to do.  However, it would
> > > probably be pretty easy to write a file of modified file names that the
> > > checkpoint could read and open/fsync/close.
> > 
> > Even that's not strictly necessary -- we *do* have shared memory we
> > can use for this, and even when hundreds of tables have been written
> > the list will only end up being a few tens of kilobytes in size (plus
> > whatever overhead is required to track and manipulate the entries).
> > 
> > But even then, we don't actually have to track the *names* of the
> > files that have changed, just their RelFileNodes, since there's a
> > mapping function from the RelFileNode to the filename.
> 
> But we have to allow an unlimited number of files.  Perhaps we could
> just fall back to sync if the shared memory overflows, and shared memory
> is finite.

True.

Hmm...perhaps there's another way to do this?  Let me explain:

When we do a checkpoint what we're really doing is writing any
committed transactions in the transaction log to the associated data
files, right?

Or, so the theory goes.  PG may do something quite different than
that.  I'm not terribly familiar with the source and so it may be no
surprise that I'm having difficulty finding any code that converts
transactions stored in the transaction log into changes to the data
files...

Anyway, if a checkpoint really does take transactions and commit them
to the data files, then the transactions themselves contain all the
information we need.  So there would be no need to maintain a separate
list: the list has already been stored on disk for us.  All we'd have
to do is build a list at checkpoint time and fsync/fdatasync each file
in the list at the very end.  The list wouldn't need to be shared
because the only process that would care is the one doing the
checkpointing.

Or so the theory goes.  Since I'm having so much trouble finding code
that actually does any of what I describe, I'd have no trouble
believing that how PG works is very different than I envision...



-- 
Kevin Brown					      kevin@sysexperts.com

Fri, 7 Mar 2003 14:07:55 -0800
Kevin Brown <kevin@sysexperts.com>
Re: Win32 Powerfail testing
> But even then, we don't actually have to track the *names* of the
> files that have changed, just their RelFileNodes, since there's a
> mapping function from the RelFileNode to the filename.

Right. I have noticed that too and have made changes to my
implementaion.

BTW, you need to track the block number as well. Files > 1GB may be
splitted into separate files (segments).

> > Of course, if there are lots of files, sync() may be faster than
> > opening/fsync/closing all those files.
> 
> This is true, and is something I hadn't actually thought of.  So it
> sounds like some testing would be in order.

I regard the difference between sync() and fsync() does not affect too
much to the whole performance. Checkpoint process is performed as a
separate process and the fsync() part of checkpoint does nothing with
WAL, that means other backend processes, busy with WAL IO will
not be bothered.
--
Tatsuo Ishii

Sat, 08 Mar 2003 09:05:30 +0900 (JST)
Tatsuo Ishii <t-ishii@sra.co.jp>
Re: Win32 Powerfail testing

Kevin Brown <kevin@sysexperts.com> writes:

> Even that's not strictly necessary -- we *do* have shared memory we
> can use for this, and even when hundreds of tables have been written
> the list will only end up being a few tens of kilobytes in size (plus
> whatever overhead is required to track and manipulate the entries).

Why not just fsync _all_ the database table files? If there's no I/O pending
on them then the fsync might be very quick. I guess that's something to test,
how fast is fsyncing a few hundred file descriptors that have no pending
changes. Hell, if you keep all the file descriptors open (yes I know some
systems have problems with that) you don't even need to open and close them,
just loop calling fsync umpteen times.

> > Of course, if there are lots of files, sync() may be faster than
> > opening/fsync/closing all those files.
> 
> This is true, and is something I hadn't actually thought of.  So it
> sounds like some testing would be in order.

Note that the average case here isn't nearly as important as the worst-case.
It would really suck to find out that running some other program (say a backup
restore on another partition?) suddenly kills your database response because
that sync suddenly has gigabytes of data to sync. 

Actually the real problem with sync is that it's, er, asynchronous. The kernel
will return before the data is actually synced. Waiting an arbitrary amount of
time might work in the average case but when something else fills the kernel
buffers with pending i/o that arbitrary time might prove to be inadequate and
it could open a window where a crash could corrupt the database integrity.

> Unfortunately I know of no system call which will take an array of
> file descriptors (or file names!  May as well go for the gold when
> wishing for something :-) and sync them all to disk in the most
> optimal way...

mmmmm, yum.

-- 
greg


07 Mar 2003 21:32:43 -0500
Greg Stark <gsstark@mit.edu>
Re: Win32 Powerfail testing