On Fri, 2010-04-30 at 06:16 +0100, Graeme Fowler wrote: > On Wed, 28 Apr 2010 16:47:10 +0100, Ron White <[email protected]> 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='[email protected]' 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! -- ## List details at http://lists.exim.org/mailman/listinfo/exim-users ## Exim details at http://www.exim.org/ ## Please use the Wiki with this list - http://wiki.exim.org/
