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 <paul nfg.nl>
* 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
_______________________________________________
Dbmail mailing list
Dbmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|