Re: autovacuum

In response to Robert Fitzpatrick <lists(at)webtent(dot)net>:

> 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...

Why does everyone leave of the IO subsystem? It's almost as if many
people don't realize that disks exist ...

With 2G of RAM, and a DB that's about 3G, then there's at least a G of
database data _not_ in memory at any time. As a result, disk speed is
important, and _could_ be part of your problem. You're not using RAID
5 are you?

> > > 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

Unless this machine runs programs other than PostgreSQL, raise this to
about 650MB. You might get better performance from even higher values.
The rule of thumb is allocate 1/4 - 1/3 of the available RAM to
shared_buffers ... subtract the RAM that other programs are using first.

> effective_cache_size = 256MB

More like 1300MB (again, unless this machine is doing other things)

> 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.

Watch the system during vacuum to see if it's blocking on IO or CPU.
systat, vmstat, iostat, and top (use 'm' to switch views) are all
good utilities to check on this.

Another possibility is that autovac isn't configured correctly. Watch
your PostgreSQL logs to see if it's running at all. If it is, turn up
the logging level until it tells you which tables it's vacuuming. You
may have to tweak the thresholds to make it more aggressive.

--
Bill Moran
http://www.potentialtech.com