Re: DB logging (was: Problem with the numbers I reported yesterday)

>>>>> "jwieck" == Jan Wieck <jwieck@debis.com> writes:

    > Kent wrote:
description of current logging method deleted.

    >     Totally right, PostgreSQL doesn't have a log mechanism that
    > collects all the information to recover a corrupted database
    > from a backup.

    >     I hacked around on that a little bit.

    >     When doing a complete after image logging, that is taking
    > all the tuples that are stored on insert/update, the tuple id's
    > of deletes plus the information about transaction id's that
    > commit, the regression tests produce log data that is more than
    > the size of the final regression database.  The performance
    > increase when only syncing the log- and controlfiles (2 control
    > files on different devices and the logfile on a different device
    > from the database files) and running the backends with -F is
    > about 15-20% for the regression test.

Log files do get very big with image logging.  I would not expect a
huge win in performance unless the log device is a raw device.  On a
cooked device (file system) buffer cache effects are very large (all
disk data is being buffered both by postgresql and the OS buffer
cache.  The buffer cache is actual harmfully in this case, since data
is not reused, and the writes are synced.  The number of writes to the
log also flush out other buffer from the cache leading to even more
io.).  If a system does not have raw devices (linux, NT), it would be
very useful if a flag exists to tell the OS that the file will be read
sequential like in the madvise() call for mmap.  Is your code
available anywhere?

    >     I thought this is far too much logging data and so I didn't
    > spent much time trying to implement a recovery. But as far as I
    > got it I can tell that the updates to system catalogs and
    > keeping the indices up to date will be really tricky.

I have not looked at this area of the code.  Do the system catalogs
have a separate storage manager?  I do not see why the could not be
handled like any other data except for keeping the buffer in the cache.

Kent S. Gordon
Architect
iNetSpace Co.
voice: (972)851-3494 fax:(972)702-0384 e-mail:kgor@inetspace.com