On Thu, 2007-09-20 at 13:22 -0400, Bill Moran wrote: > In response to Robert Fitzpatrick <lists@xxxxxxxxxxx>: > > > 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 ---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
