shmem limits and redolog

Hi,

    I'm  currently  hacking  around on a solution for logging all
    database operations at query level that can recover a crashed
    database  from  the last successful backup by redoing all the
    commands.

    Well, I wanted it to be as flexible as can. So I  decided  to
    make  it  per  database  configurable.  One  could  say which
    databases are logged and if a database is, if  it  is  logged
    sync  or async (in sync mode, every COMMIT forces an fsync of
    the actual logfile and controlfiles).

    To make async mode as fast as can, I'm using a shared  memory
    of  32K per database (not per backend) that is used as a wrap
    around  buffer  from  the  backends  to  place  their   query
    information.  So  the  log writer can fall a little behind if
    there are many backends doing  different  things  that  don't
    lock each other.

    Now  I'm  a  little  in  doubt about the shared memory limits
    reported.  Was it a good decision to use shared memory? Am  I
    better off using socket's?

    The  bad  thing  in  what  I  have  up  to now (it's far from
    complete) is, that even if a database isn't currently logged,
    a redolog writer is started and creates the 32K shmem segment
    (plus a semaphore set with 5 semaphores). This is  because  I
    plan to create commands like

        ALTER DATABASE LOG MODE=ASYNC LOGDIR='/somewhere/dbname';

    and the like that can be used at runtime (while more than one
    backend is connected to the database) to turn logging on/off,
    switch  to/from  backup  mode (all other activity is stopped)
    etc.

    So every 32 databases will require another megabyte of shared
    memory.  The  logging  master  controls  which databases have
    activity  and  kills  redolog  writers  after  some  time  of
    inactivity,  and  the shmem is freed then. But it can hurt if
    someone really has many many databases that are all  used  at
    the same time.

    What do the others say?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#======================================== jwieck@debis.com (Jan Wieck) #