> 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