On Fri, 2010-04-30 at 06:16 +0100, Graeme Fowler wrote:
> On Wed, 28 Apr 2010 16:47:10 +0100, Ron White <exim.ml@???> wrote:
> > As my ACL grows It has crossed my mind that for every acl stanza making
> > use of a MySQL that fires off, there is the roundtrip cost of the query.
> > Whilst some of the lookups can be optimised by ACL layout, I'm not going
> > to be able to avoid some similar very similar queries when I check my
> > database for certain flags being set on a per user basis.
>
> Please do note that for exactly similar - ie. identical :) - queries in
> the same message session, Exim will cache the results anyway:
>
> http://www.exim.org/exim-html-current/doc/html/spec_html/ch09.html#SECID64
>
> I've built a few systems which handle a relatively large volume (ie.
> >100000 messages/day being delivered) which use MySQL, and in all cases the
> database itself has not been a bottleneck.
>
> If you run a mock SMTP session as follows:
>
> exim -d+all-memory -bh [insert valid IP here] 2>&1 | tee session.out
>
> ...then input all the relevant EHLO/HELO, MAIL FROM and RCPT TO commands
> followed by DATA with a valid message, you'll get all the debug output in
> the file session.out. This should let you check just how (sub)optimal your
> query formation is - what you're looking for are lines like this:
>
> 02:45:30 32556 cached data used for lookup of SELECT dir FROM etable WHERE
> name='graeme@???' limit 1
>
> ooh, I saved myself a single query ;-)
>
> Graeme
>
>
Thanks Graeme,
I know that MySQL is very capable and I'm reasonably sure it won't be an
issue to fire of five or six queries per message. At the moment the
Postfix I'm using makes a large number of (unavoidable) queries per mail
and never misses a beat.
I guess I just felt it would be more 'efficient' to pull out as much
data as I needed in various ACL stanzas in a single hit. Each query is
slightly different as the ACL runs, so they probably would not cache.
Thanks for that useful one liner. I see a few 'cached data used for
lookup of SELECT dom_name FROM domains WHERE dom_name=' in a couple of
places. Up to now I've just been tailing the mysql query log, but this
method is straight from the horses mouth. Thanks :-)
I think I'm pretty happy with a sensible mix of doing both as 'query
balancing'. For sure I don't want it to become a time sync or make
things illegible in the runtime configuration.
Moving on from that - today I turn my attention to Clamav and Exim and
in particular SELinux on the Cent5 box. The installation was really easy
but there are some issues with clam being able to access files in
the /scan directory.
This is a subject I know nothing about, but want to resist the
temptation to do the defacto 'disable SELinux'. Luckily I have the
weekend ahead to study and see if I can work it out. Wish me luck!
