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