Re: Bundling postgreSQL with my Java application

On Sun, Jul 5, 2009 at 11:48 PM, Saurabh Dave<saurabhdave(at)gmail(dot)com> wrote:
> Hi,
>
> We are bundling PostgreSQL 8.3.7 with our Java based application.
> We observe that in some systems the Database access becomes very slow after
> running it for couple of days.
>
> We understand that postgresql.conf needs to be adjusted as per the system
> specification where postgreSQL is running.
>
> Is there a utility that we can use that can check the system specification
> and change the required parameters in postgresql.conf accordingly?

Assuming autovacuum is enabled still (it is by default) it is likely
that your updates are big enough that you're blowing out the free
space map. Easy to check, take a db that's slowed down and run vacuum
verbose as a super user on any of the dbs in it (postgres is a good
choice) and see what the last 20 or so lines have to say about how
many slots you have and how many you're using. If you need more
slots, then adjust the free space map settings (max slots and max
relations) so they're large enough to keep the db from bloating in the
future. On larger datasets 1M to 10M slots is not uncommon, and since
it only uses 6 bytes per slot, even 10M is only 60M of shared memory.

8.4 has a LOT of improvements in this area, as I understand the whole
FSM stuff has been automated on that version. Note that I haven't
tested 8.4 yet, so I'm just going by what I read.