Re: autovacuum

On Thu, 2007-09-20 at 13:22 -0400, Bill Moran wrote:
> In response to Robert Fitzpatrick <lists(at)webtent(dot)net>:
>
> > I have a large database used with our mail filter. The pg_dumpall
> > results in about 3GB with this being the only database in the system
> > besides templates and postgres.
> >
> > I do a vacuum every night after backup and it takes about an hour, is
> > this normal for this size db?
>
> "normal" is relative. If it's taking an hour to vacuum 3G, I would say
> that either your hardware is undersized/badly configured, or you're
> not vacuuming often enough.

It is a dual P4 processor supermicro server with 2GB of RAM, so I will
need to go over the configuration then? I didn't think it should take so
long...

> That doesn't mean you're vacuuming often enough, however. Switch your
> nightly vacuum to vacuum verbose and capture the output to see how much
> work it has to do. Are your fsm settings high enough?
>
> > Let me know if you need more specifics. Just trying to get some feedback
> > on if my vacuum is taking too long or if both are necessary...thanks for
> > the help!
>
> How much RAM does the system have? What's your shared_buffer settings?
> What's your maintenance_work_mem set to?

Yes, this is the first time I've had to do any tuning to pgsql, so I
most likely need help in this area. This is 8.2.4 on a FreeBSD 6.2
server...here are those settings currently below. I also had to tweak
BSD loader.conf to allow the changes to work...

max_connections = 250
max_fsm_pages = 204800
shared_buffers = 128MB
effective_cache_size = 256MB
work_mem = 64MB
maintenance_work_mem = 256MB

mx1# cat /etc/loader.conf
kern.ipc.semmni=256
kern.ipc.semmns=512
kern.ipc.semmnu=256
mx1# cat /etc/sysctl.conf
# tuning for PostgreSQL
kern.ipc.shm_use_phys=1
kern.ipc.shmmax=1073741824
kern.ipc.shmall=262144
kern.ipc.semmsl=512
kern.ipc.semmap=256

If I don't have it listed above, then it is default settings for
anything else.

Thanks for the help!

>
--
Robert