Re: Persistent connections to database?

["Joshua D. Drake" <..hidden..>]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Fri, 16 Nov 2007 19:09:53 -0600
Charley Tiggs <..hidden..> wrote:


> > Bingo :)...
> > 
> > increase your max_fsm_pages to 250000
> > 
> > What about the rest of your conf (please attach)... shared_memory,
> > work_mem, fsync, wal_sync_method etc...> 
> conf attached...
> 
> Could you comment on why I need to increase the free space map pages? 

- From the docs:

max_fsm_pages (integer)

    Sets the maximum number of disk pages for which free space will be
tracked in the shared free-space map. Six bytes of shared memory are
consumed for each page slot. This setting must be at least 16 *
max_fsm_relations. The default is chosen by initdb depending on the
amount of available memory, and can range from 20k to 200k pages. This
parameter can only be set at server start. 

In short if you can't track free space, you get bloat. 


> I'm going to do it.  I'm just trying to understand why I'm doing it.
> 

As far as your conf... it is a bloody mess :). It appears that someone
just installed PostgreSQL without actually reviewing the configuration
files. No offense, but that is a seriously bad idea with something as
critical as your accounting system.

I would suggest at a mininum you do the following:

1. Increase max_fsm_pages
2. Increase shared_buffers to something more sane line 256M
3. Chnage wal_sync_method to open_sync
4. Increase checkpoint_segments to 10
5. Set effective_cache_size to 512M
6. Increase default_statistics_size to 150

When you restart it will likely fail because of shared memory. Take the
number it gives you on failure, add 32 megs to it and add that to your
sysctl.conf.

http://www.postgresql.org/docs/8.2/interactive/kernel-resources.html

Once you are back up, run a VACUUM VERBOSE ANALYZE and report back the
last 5 lines again.

Sincerely,

Joshua D. Drake



> Charley
> 


- -- 

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
			UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHQcLMATb/zqfZUUQRAjC6AJ9Xr8cFgPUNPj8wdENOluJ/zMGL+ACeJP00
uCKtMZyLAoTlhalI0uoibs4=
=mqU7
-----END PGP SIGNATURE-----