GamCo - Mail List wrote:
> Hi Guys
>
> Just some feedback on this issue after pursuing some suggestions from other
> power users on the list ;-
>
> We have been testing several aspects of our problem by trying several
> solutions, and this is the outcome so far :-
>
> 1. MySQL
> Upgrade to the latest MySQL 5.x :: By upgrading to the latest available via
> ports on FreeBSD, we are now running on MySQL 5.0.24 - This version seems to
> be stable, using a lot less resources on the CPU. Previously we had a lot of
> crashes around V4.x which seems to have disappeared.
>
> We have since changed from the traditional TCP connections between Exim and
> MySQL to Unix sockets rather which appears to also add a little to more
> stability and less load on the server.
>
> We are however left with one little snag, getting Signal 11 crashes. Not on
> a daily basis, but at least every second day. I have been reading up on this
> error and it might suggest bad RAM or ulimits.
>
> Anyone care to shed their opinions ?
'Once upon a time' when MySQL was fast, lite, and, IMNSHO, not really as much
an RDBMS as an SQL-syntax flat-file manager, it had a pronounced 'edge' over
heavy-lifter RDBMS'en, such as PostgreSQL, DB2, or Oracle (in order of their age
and seniority).
As MySQL has added 'features' that its designers once said were not necessary,
it has probably retained the speed advatange over, for one, PostgreSQL.
But not by enough to stress a server.
MySQL - while eminently adequate for most of the simple tasks most often asked
of it, still lacks a decade or more of very turbulent and hard-earned experience
at the stability/reliability/predictability edge for more complex RDBMS work.
Moreover, very few smtp environments actually need even a detectable percentage
of the power of a top-class RDBMS.
So - a 'read-mostly' DB, such as CDB, will beat *any* full-house SQL animal for
higher read speed, lower resource load - and ultimately, higher reliability.
If one chooses to maintain and export CBD or similar tables from a 'heavy'
RDBMS, centralized or otherwise, then an SQL RDBMS *may* make more sense.
Otherwise, though we use PostgreSQL on all but a few (non-MX) servers, we do NOT
recommend it, and probably would not do it ourselves, save that 22 years of
RDBMS use has made it easier for us than other choices.
'When all you have is a hammer...'
But don't let yourself be trapped the same way...
>
> 2. Virus Scanning
> We loaded up a Dell 850 2.8 Ghz CPU, 80 GB SATA, 512MB Ram server with
> FreeBSD / Amavisd-new / ClamAV via TCP connections.
>
ClamAV all by itself, invoked during smtp-time, is more than adequate. There IS
NO perfect, or 'real-time' anti-vir DB or heuristic engine.
BUT - most nasties come from rudely-behaved sources, so sane rejection of
zombies, forgeries, dynamic IP's, RBL denizens, will kill perhaps 80-85% of
potential threats before you ever need to look at them for viral payloads.
We also run our ClamAV via Unix sockets, an the total load, run agaisnt just the
11% of traffic offered thta survives to DATA phase, is very light.
> The server at first could not handle the load with the amount of mails
> coming into the scanning server and we saw some mail queues appearing on the
> primary mail server side with some Amavis defers coming from Exim. After we
> upped the amount of server processes, we saw the CPU take strain while
> ClamAV was scanning the mails for viruses.
>
> We have since then disabled the Amavis scanning and went onto testing inline
> scanning with ClamAV / Exim. This appears to be working very well indeed.
> All happening on the same server with little extra load caused by the AV
> scanner via Unix Sockets.
Good move. Better move if you can shed more of the nasty arrivals BEFORE
invoking any scanners.
>
> I am left wondering why the Amavisd / ClamAV route would put such a load on
> the extrernal scanning server as opposed to running the Exim inline scanner
> ?
>
Well - interpreted vs compiled languages, for starters.
Look, for example, at all the bits and pieces a FreeBSD port needs for amavisd,
in perl:
http://www.freebsd.org/cgi/ports.cgi?query=amavisd&stype=all
Then look how much less ClamAV needs, in 'C' - especaily after subtracting the
unarchivers that do not all run all the time:
http://www.freebsd.org/cgi/ports.cgi?query=clamAV&stype=all&sektion=all
And 'online' daemons for another. When Exim uses ClamAV 'inline', it is making
calls to a daemonized binary with a separately-running update binary.
Doesn't get much more efficient than that unless you code it in headerless NCC
Forth (BTDTGTTS) and run it entirely in the on-die CPU cache and registers... ;-)
> 3. We have not yet experimented dumping the SQL database to a flat file and
> using exim to rather do lookings via the flat files. We will experiment over
> the next few days.
>
Highly recommended!
Especially if you have a 'heavy lifter' RDBMS where you can use 'triggers' to
spit out a fresh copy anytime a relevant field has changes. *CAN* make
centralized admin easier, as there are a wealth of browser-based tools for
manipulating the DB that need not touch the MTA directly, nor care what MUA is
in use. In our case, the user ID an PWD are not even related to the email UID:PWD.
That way, the RDBMS need not even be on the same box, can serve a whole
community of MTA's, all of which will still run if the RDBMS is down, or is just
partially offline for maintenance/alterations.
>
> Hope this helps in future with other people having issues as we have been
> experiencing over the past couple of months.
>
>
> Regards
>
> G Marais
MySQL served us almost without fail for over 5 years (2 outages?) on a
Qmail+VpopMail+(all the other debris it needed), so I am not knocking it entirely.
OTOH, we never asked MySQL to do even a small fraction of what we need to do
with/for Exim and other SQL-driven apps.
PostgreSQL, OTOH, proved itself superior to Oracle for our Ars-Difficult OpenACS
and Zope/Plone work, plus being more SQL-standards-compliant, transactional [1],
smarter about locking, and ACID compliance than MySQL, so we have stayed with
that, as it is cheaper than DB2... (and perhaps, better, as well?)
Driving Exim with it was originally a side-project, but has worked with very low
resource loading, even when combined with Zope/Plone on as little as a 1.2 GHz
Celeron in 512 MB of PC-100 SDRAM. Granted - fewer than 10,000 messages per day,
and Squid-SSL fronting for Zope/Plone, no Apache resource-hog either, so...
YMMV,
Regards,
Bill Hacker
*snip* (original post)
[1] Yes, I know about MySQL + InnoDB backend. My GMC pickup can fly faster and
higher than a C-130 as well. So long as I load it as cargo on a C5A.
;-)
