After speaking to someone who knows a lot more about Postgres than I, you all are very right. I didn't understand the sort memory option and will be lowering it.
There seems to be some concern with the 1) DRBD which I was using to network replicate the database. 2) Running 7.4.7 3) Transactions in dbmail To start with, I'm going to ditch DRBD and go to just a plain file system. I will also upgrade to 8.0.3. One area that I'm most interested in with 8.0.3 is that it has a way to limit the process load on the vacuum. As for the root (master process) for the dbmail services, to explain further, if I go to the database and ask it to shutdown, the master dbmail-lmtp, imapd and pop3d processes just die. The child processes are still running, and will continue to run until they reach their max limit. They die, but don't get restarted (obviously since the parent process is dead). -- David A. Niblett | email: [EMAIL PROTECTED] Network Administrator | Phone: (352) 334-3400 Gainesville Regional Utilities | Web: http://www.gru.net/ -----Original Message----- From: Matthew T. O'Connor [mailto:[EMAIL PROTECTED] Sent: Saturday, July 16, 2005 12:40 AM To: DBMail mailinglist Subject: Re: [Dbmail] DBMail + PostgreSQL Problem Niblett, David A wrote: >I'm in serious need of help here. I'm about at my whits >end of dealing with dbmail and getting it to work. I've >had a couple of database crashes now and found things like >if I run dbmail-util my DB process load skyrockets and >the server becomes unusable. > > This sounds like a problem with dbmail-util not handling a database problem very well. Hopefully this can be found and fixed. >I'm hoping that maybe I'm just incapable of tuning PostgreSQL for >performance. At this point I'd like to know if there is anyone out >there that has experience with dbmail-2.0.4 on PostgreSQL-7.4.7 in a >moderately large table size (9-10GB). We are very interested in paying >for some help in the form of consulting if need be. At this point if >we can't work out the bugs then we are going to scrap the entire thing >and go back to our simple Windows based NTMail system. > > Don't do that! DBMail is quite nice once you have it running well for you. I run Dbmail 2.0.4 against Postgresql 8.0.x, I used to run it against 7.4.x, for DBMail's purposes, they are mostly the same. My database size is close to your, but it sounds like I have far fewer simultaneous users. >Some items that seems to happen are: > >1) If I stop/reload the postgres database (normal nice stop which >should allow all transactions to finish) the dbmail master daemon dies >and we seem to get a lot of unconnected messages suddenly in the >database. We see these in the form of no user, no subject messages in >users mailboxes. > > It sounds to me like the problem has to do with the fact that DBMail does not use transaction to maintain database consistency. This is a hold over from the fact that they use MySQL, personally I think this is one of DBMail's biggest failings. >2) When dbmail-util runs, the process load just sky rockets on the db >server. It seems to be related to the large join done for finding the >messageblks that are not connected. > > Can you give more detail here? The load on the server goes up? There are more active postgres connections? What process is actually using the CPU, dbmail-util or postgres? >3) When we vacuum the database the process load screams sky high (like >160+) on the server. The last time we did this it took 4 days for the >vacuum to finish. We believe we have this fixed by using the >pg_autovacuum daemon. > > What is the load on the server normally, and how many concurrent users do you actually have running? Vacuum can be costly, but it shouldn't be that bad, especially with a server like the one you have. Have you tried any of the vacuum delay settings? (BTW, I wrote pg_autovacuum :-) >Our set up is: >DB Server - Dual Xeon 3.06GHz, 1GB RAM, SCSI RAID Ultra320 drives. >DBMail Server - P4 3.06GHz, 1GB RAM, SATA drives. >Running: dbmail-2.0.4, postgresql-7.4.7 on Gentoo with Linux 2.6 kernel > > Dual Xeon tends not to be the best PostgreSQL platform, it has some heavy context switching problems, but even so, I would expect this server to be just fine. >As far as tweaking goes, I've set sort_mem and vacuum_mem on postgres >to each 100M (102400) to help try and stop swaps. I've also increased >the shared memory limit from 32M to 100M. > > 100M of sort mem might be way too high. Remember that sort mem is limited on a per process basis whereas shared memory is allocated on a per cluster basis, meaning if you have 20 Imap clients all doing a sort at the same time you could be asking postgresql to allocate as much as 2G of memory. You might try lowering that value and seeing what happens. >I'd also be very interested in knowing a better way to limit the >database transaction logs such that should I suffer a crash I'm not >having to dump the database and restore. I never really had this issue >with MSSQL. I expect to lose things like the message that is being >delivered, but not corrupt the dbmail_users table and everything else. > > If you are running on stable hardware, you should never have to dump reload postgresql. Again I think the problem is that DBMail is not using transaction to ensure database consistency. Matt _______________________________________________ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail
