Re: [GENERAL] Optimizations for busy DB??

On Thu, 13 May 1999, Brian wrote:

> We are running a Database that is having between 100-500 simultaneous
> accesses at any given time.  Are their any flags, switches or
> optimizations on the postgres level that can be done?  such as options
> passed to postgres etc?  We run 6.3.2, and start it like:
>
> su postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql

I usually pass back -F -B 256 to the backend (using -o with postmaster).
-F turns off the fsync, and speeds up writes significantly (especially
useful for bulk copies and updates).  However, if your system crashes in
the middle of a transaction, you can lose data.  The -B sets the number
of 8k buffers.  It defaults to 64, but if you have more memory, crank
that number up.  For sorts, -S can be used to specify how much memory to
use (in 1k chunks) before disk files are used.  The default is 512, but
again, if you have the memory to spare, jack that guy up and see how well
it works.  Make sure you preface these backend options with -o, since the
postmaster has its own options distinct from the backend.  Your complete
invocation might look like

postmaster -i -S -D/var/lib/pgsql -o -F -B 256 -S 1024

Experiment and see what works.

Oh, yeah, you should upgrade to 6.4.2!

Brett W. McCoy
                                         http://www.lan2wan.com/~bmccoy
-----------------------------------------------------------------------
The only way to get rid of a temptation is to yield to it.
        -- Oscar Wilde