Re: [exim] Mail Implementation Design

GamCo - Mail List wrote:

*snip*

>
> Your MySQL box sounds plenty fast enough. If you're seeing memory problems,
> you might consider upgrading your MySQL (and preferably use the binary
> supplied by MySQL). You might also want to lower some of the memory usage in
> my.cnf. As far as multiple queries go, assuming you have the query cache
> turned on and aren't making continuous changes to the tables involved, the
> query cache should reduce the overhead of the subsequent duplicate queries
> to almost nothing, as far as the mysql box is concerned. Do you have a
> steady increase in the Qcache_hits field of 'show status'? If not, you might
> double check the duplicate queries to make sure they are completely
> identical.
>
>
>>We upgraded from 4.x to 5.x which got rid of all our mysql memory
> > problems. I think that's about the only positive change we made over the
>> past weeks...
>
>
> I can't speak to the FreeBSD part and I promise I'm not trying to start a
> flame, but you might want to try running MySQL on a recent linux distro to
> see if it helps with your memory issues.

Not a flame - but IIRC (haven't run it in nearly a year) MySQL, which ran fine
for us under FreeBSD for circa 6 years - *is* 'optimized' to a better fit with
the Linux threading model than to that of (at least) pre-6.1 FreeBSD.

First thing I would suggest is to move off FreeBSD 5.X to 6.1 (or back to 4.11).

That said, our comparable use of FreeBSD 4.11 (still our single-CPU workhorse)
and 6.1 AMD-64 (Intel dual-core) with Exim, Dovecot, AND (on a few of the
servers) Zope/Plone CMS making use of PostgreSQL [1] indicates that an
SQL-driven Exim should not really show anything like the load reported.

I suspect - admittedly only on the evidence that we make far *FAR* more queries
per-connection, per-message, and per smtp and IMAP auth [2] than remarked in the
original post - that the OP's DB structure and SQL query syntax are suboptimal,
and/or something is seriously wrong with that MySQL install.

That's where I would look if 6.1 and a clean re-install doesn't solve the problem.

And yes - exporting to cbd or flat files will be faster in use. More robust, as
well.

JM2CW

Bill


[1] Our 'mail' db has just two tables, one of 9 fields, another of 42.

These list domains, users, departments, supervisory/subordinate relationships,
four different 'sets' of user ID and clear/crypted passwords, each of which are
multi-part and have to be concatenated and formatted when called up.

Likewise nearly a dozen flags and integer user prefs for AV scanning, smtp
protocol strictness, white/black/brownlisting, 4 different spam / quarantine
thresholds, some of which result in dual-delivery to different mailstore,
archiving settings (separate for incoming/outgoing and dodgy traffic).

Additionally, we store the per-user mailstore type (Maildir or MBox), storage
location, sharing, aliasing/forwarding, splitting/multi-drop delivery, and even
the per-domain HELO used on outbound traffic.

[2] The DB call made to authenticate each smtp submission requires concatenating
departmental+personal UID and departmental+personal password to generate
effective smtp-submission user loginID:loginPWD.

[3] The DB call made to authenticate IMAP likewise requires concatenating
departmental+personal UID and departmental+personal password to generate
effective IMAP user loginID:loginPWD.

The user and departmenatl admin, smtp and IMAP ID's & passwords are distinct.
Webmail is yet another set..

Complex? Hell yes! But as Topsy said, 'It just growed'.

But - run via a Unix socket, it barely keeps the server warm.

Literally. We ran one 2U server for over six months with the CPU fan failed.
(Underclocked Athlon, copper heat-pipe HS, decent case fans).