Re: [exim] Message logging with SQL

On May 10, 2010, at 3:58 AM, Frank Heydlauf wrote:

> On Sun, May 09, 2010 at 05:49:03PM -0500, Brian Blood wrote:
>>
>> Our email solution, ECMSquared.com (Exim, Courier, MySQL) stores all configuration information:
> ...
>> and our exim config logs all ips with stats: RDNS, helo, proper QUITs, bytes in/out
>> along with logs for incoming connections, authentications, blocked messages, accepted messages, sent messages, greylists, etc...
>
> could you give us an idea about how far the db-logging scales?
> (how many db-inserts/time and more interesting how many smtp-connects/time)


We built the SQL logging to be as non-blocking as possible.
So, where ever it made sense, we used the INSERT DELAYED synta in our SQL to allow the MySQL client code in exim to be released right away by the MySQL Server and continue processing of a connection. The MySQL server would then perform that INSERT in a separate thread.

Some of the logging can't take advantage of that as we also use INSERT ... ON DUPLICATE KEY UPDATE... quite a bit as well so we can perform inserts/updates without having to check for the existence of a record first.

We've had clients that had 500-600 concurrent SMTP connections easy with some peaks near 900.

Two things to note at that level of traffic:
1 We separated out MySQL to it's own dedicated server.
2. Having MySQL do all the config/logging, you can create a load balanced set of front-ends for your SMTP services.

For local Delivery you have a couple of different options:
- shared networked file systems
- pipe messages into MySQL table for final delivery into mailbox store, which could be on another machine(s)


Brian