Matthew T. O'Connor wrote:
> Niblett, David A wrote:
>
>> 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.
In my experience 8.0 is faster with dbmail than 7.4. If possible I'd
recommend to upgrade.
But that doesn't explain the high load.
>> 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.
>From Changelog:
2004-12-17 Paul Stevens <[EMAIL PROTECTED]>
* db.c, pipe.c: added transactional support to message delivery.
I checked the code (2.0.4, current svn): insert_messages() is the only
place where transactions are used, so all IMAP/POP3 commands seem to rn
unprotected.
>> 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.
Even worse
(http://www.postgresql.org/docs/7.4/interactive/runtime-config.html):
"Note that for a complex query, several sort or hash operations might be
running in parallel; each one will be allowed to use as much memory as
this value specifies before it starts to put data into temporary files."
If there are enough concurrent connections the server could swap to
death, that would explain the high load. What about your HD IO?
>> 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.
But how could missing transactions corrupt dbmail_users ? That sounds
very strange.
I've never seen a corrupted Postgres database - the only dump and reload
I ever had to do were for upgrades.
BTW: even on a crash you shouldn't loose the message that is inserted.
My setup is:
exim -> lmtp -> dbmail -> postgresql
dbmail-lmtpd should only report 'OK' if Postgres inserted the message
(in a transaction), then exim can delete it. If there is a crash before
the 'OK' exim still has the message, if there is a crash after the 'OK'
the message is on stable storage (the transaction succeeded).
Thomas
