Re: [exim] [follow-up?] Re: Performance of MySQL vs text fi…

Αρχική Σελίδα
Delete this message
Reply to this message
Συντάκτης: Heiko Schlittermann
Ημερομηνία:  
Προς: exim-users
Αντικείμενο: Re: [exim] [follow-up?] Re: Performance of MySQL vs text files.
snowcrash+exim-users <schneecrash+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 -

> 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 _know_ that there are differences, but how significant? the
> frequent advice is for "short lists" use flat-file, and as the lists
> get longer, move to sql etc etc.
>
> what's the rule-of-thumb for a list being "long"?
>
> i found this list-thread commented by Nigel M., but no follow up (yet):
>
> http://www.exim.org/mail-archives/exim-users/Week-of-Mon-20051128/msg00017.html
>
> any further thoughts or references?
>
> thanks.
>
> --
> ## List details at http://www.exim.org/mailman/listinfo/exim-users
> ## Exim details at http://www.exim.org/
> ## Please use the Wiki with this list - http://www.exim.org/eximwiki/
>