Re: [exim] Saving roundtrips to MySQL by storing previous re…

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
>
>

Brilliant!

Now - how can one *temporarily* cause that level of detail - or at least the
'cached data' part - to be logged for a time on a stream of 'live' arrivals?

Bill