From hs@schlittermann.de Mon Jan 1 20:07:07 2007 Date: Mon, 1 Jan 2007 20:07:07 +0100 From: Heiko Schlittermann To: exim-users@exim.org Subject: Re: [exim] [follow-up?] Re: Performance of MySQL vs text files. snowcrash+exim-users (So 31 Dez 2006 23:29:49 CET): > i though this was going to be a bit easier ... > > i'm interested in benchmarked performance of flatfile vs > cdb/sqlite/mysql lookup performance. I'm doing some greylist experiments. Currently I've about 5000 IP addresses in some database. First, time for exim startup: # time exim4 -bV >/dev/null real 0m0.032s user 0m0.020s sys 0m0.010s Now, flat file (about 5000 lines) # time exim4 -be '${lookup{217.72.192.234}lsearch{/tmp/seen.txt}}' # looking up the first entry in this file: real 0m0.031s user 0m0.030s sys 0m0.000s # looking up the very last entry in this file: real 0m0.036s user 0m0.020s sys 0m0.020s Now, same with DBM (about 5000 entries) # time exim4 -be '${lookup{217.72.192.234}dbm{/tmp/seen.db}}' # (there's no difference between the last and the first # entry, as expected) real 0m0.032s user 0m0.030s sys 0m0.000s And now using sqlite # time exim4 -be '${lookup sqlite{/tmp/seen.sql SELECT * FROM seen WHERE item = "217.72.192.234"}} real 0m0.033s user 0m0.030s sys 0m0.000s And finally mysql (using local unix socket) # time exim4 -be '${lookup mysql{SELECT * FROM seen WHERE item = "217.72.192.234"}} real 0m0.034s user 0m0.010s sys 0m0.020s Until here we do not see some significant difference, but it should be expected that DBM and SQLite/MySQL will scale better than the flat file. (For getting more significant data and to eleminate the time exim loads itself I'd create a small perl "plugin" executing several hundreds of lookups via Exim::expand_string().) The conclusion is up to you :) Some points to think about: - readability of config file - number of lookups - update procedure (from within exim, external, periodically, "just in time") Best regards from Dresden Viele Grüße aus Dresden Heiko Schlittermann -- SCHLITTERMANN.de ---------------------------- internet & unix support - Heiko Schlittermann HS12-RIPE ----------------------------------------- gnupg encrypted messages are welcome - key ID: 48D0359B --------------- gnupg fingerprint: 3061 CFBF 2D88 F034 E8D2 7E92 EE4E AC98 48D0 359B -