Re: [HACKERS] 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,
Pennsylvania19026