Jesse, I'm like you, there are only two of us here and we are learning PostgreSQL seat-of-pants style.
Wow, then I'm completely lost looking at your numbers. I set my shmall and shmmax to 805306368, so that the DB (which is the only thing using memory on the box) could have 800M of shared RAM. Am I crazy here? I set my shared_buffers to 40000 which should be ~327M of shared memory available. Is this a mistake? I wanted to let PostgreSQL use all the RAM it could get it's hands on for performance. My max_fsm_pages is 200000 and relations are 10000. I'm guessing that the more fsm_pages, the less you have to run VACUUM FULL. Do you think it might be better to lower the shared_buffers, and increase the max_fsm_pages? I do know about some of the really time intensive queries, and I wish I had the time to go through and optimize them. I'll look at my logs and see if I'm getting hit with that problem. As for the dbmail-util, I would love not to run that part each night, but I thought that was recommended. Maybe Paul or one of the other developers could shed some light on the recommended dbmail-util schedule. I checked my logs and I've never had a single message be found for null message, integrity, etc. So I was thinking about running the dbmail-util purge/delete (-pd) options once a day, and just delete 3 other times of the day. I will probably still do the "everything" option, but just weekly. Thoughts? -- David A. Niblett | email: [EMAIL PROTECTED] Network Administrator | Phone: (352) 334-3400 Gainesville Regional Utilities | Web: http://www.gru.net/ -----Original Message----- From: Jesse Norell [mailto:[EMAIL PROTECTED] Sent: Thursday, November 17, 2005 2:34 PM To: DBMail mailinglist Subject: RE: [Dbmail] Size, vacuum, performance, etc Hello, The postgres setup I'm referring here is the same that Dave Logan replied about earlier, fyi. We have no postgres experts on staff, just Dave and I, and we do what we can. As of 7.4, unbounded growth problems have gone away and with some trial and error, we've not had any major database-induced issues in some time. > I have set my shared memory space at 512M, I would like to > go to more, but PostgreSQL doesn't seem to do anything with the extra > RAM. # cat /proc/sys/kernel/shmall 16777216 # cat /proc/sys/kernel/shmmax 67108864 > Admittedly it seems like some of the selects that dbmail > does take quite a long time to return. One of the worst is the > dbmail-util check for incorrect is_header flag. That seems to take > about 3 hours. I don't think it's ever found a problem looking in my > log, so should I not bother to do this? I'm pretty sure that's intended to be a run-once type feature for upgrading from a previous dbmail install that didn't support is_header. All new messages that get inserted should have that set properly. > What are you setting your shared_buffers at (mine is 40000)? shared_buffers = 3072 > Do you modify any of the items like FSM and such in the config? Yes; if you're having to run vacuum full to reclaim space, you probably need to increase fsm settings and/or vacuum more frequently. We have: max_fsm_relations = 100 max_fsm_pages = 5000000 A few other comments: if you're comfortable running development releases, 2.1 should give you better performance. Also, Dave tracked down a specific pop3 UPDATE query that was inducing a huge load on our system that I believe was fixed in at least 2.1 svn, if not all three branches. You might update to the latest svn (in 2.0 or 2.1), and see if this issue still exists (check the source, level 5 logs or postgres log for what updates are being run). Refer to this thread: http://mailman.fastxs.net/pipermail/dbmail-dev/2005-October/007594.html and this bug: http://www.dbmail.org/mantis/view.php?id=278 -- Jesse Norell - [EMAIL PROTECTED] Kentec Communications, Inc. _______________________________________________ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
