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



Fri, 13 Nov 1998 19:46:20 +0100 (MET)
jwieck@debis.com (Jan Wieck)
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.

Cool.  

I have postings that describe a method of not f-sync'ing the pg_log or
data tables, while allowing proper recoery.  Let me know if you want to
read them.  Hope you will.


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

Shared memory is usually a good idea.  We have researched the other
options.  The only other option was anonymous mmap() of a file, but that
is not supported by many OS'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.

Because most filesystems are 8k blocks, you could reduce it to 16k
shared memory if you think you can write them out fast enough.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 12 Dec 1998 17:40:34 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] shmem limits and redolog