From: Adrian 'just my €.02' von Bidder (avbidderfortytwo.ch)
Date: Mon May 30 2005 - 04:29:44 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

On Monday 30 May 2005 10.47, Sven Riedel wrote:
> Hi,
>
> I've read several times that postfix-"native" index files are
> a lot faster than SQL lookups. While I no problems believing
> that statement for maps up to a certain number of entries (below
> several thousand, say), I am a little curious if there is a
> certain number of entries where the lookup speed is faster
> in SQL than in the index files.

I'm quite confident that berkeley db index files will nearly always be
faster than SQL lookups: the SQL server can not just 'magically' do a
blindingly fast lookup, but it will use some indexing algorithm to do that
- and likely, the indexing algorithm used by the SQL database is very
similar the the one used by the berkeley db file. So, in other words,
internally, an internal lookup of a table row in an SQL database will be
approximately equal to the speed to lookup a record in a berkeley db file.

BUT: the SQL database has to parse SQL, typically runs some sort of query
optimizer (which won't take long on a small query, but won't be able to
optimize much, either, so it's probably a small net performance loss for
typical table lookups for most postfix tables.), additionally the query has
to be sent to the SQL database first whereas internal lookups are
(presumably - and assuming they don't go via proxymap) run directly in the
address space of the process which needs the data - so a lot of data
serialization an transmission can be omitted.

So SQL table lookups only make sense if you
 - have all necessary data in SQL anyway (you user management runs off an
SQL database.) or
 - you have a postfix cluster, where accessing centralized tables via SQL
may make sense. (I don't have any large systems experience, but from a
reliability pov I'd think hard if I couldn't live with tables being
generated and then distributed to each node in the cluster every 15min or
whatever.)

cheers
-- vbi

--
Beware of the FUD - know your enemies. This week
    * The Alexis de Toqueville Institue *
http://fortytwo.ch/opinion/adti

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)
Comment: get my key from http://fortytwo.ch/gpg/92082481

iKcEABECAGcFAkKa3QhgGmh0dHA6Ly9mb3J0eXR3by5jaC9sZWdhbC9ncGcvZW1h
aWwuMjAwMjA4MjI/dmVyc2lvbj0xLjUmbWQ1c3VtPTVkZmY4NjhkMTE4NDMyNzYw
NzFiMjVlYjcwMDZkYTNlAAoJECqqZti935l6WtIAmgKPi21cC2f+MSTkZpf65uhJ
IH/NAJ4oiGXDSEdS17r8kvVF/ueEc/9fPg==
=ybHq
-----END PGP SIGNATURE-----

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]