Qmail Support Forum

admin · Site Admin Joined: 21 Jun 2005 Posts: 209

Initially I am looking at 50 domains with transport_maps (the majority
of traffic will be transport_maps), and ultimately there will be around
1000 virtual addresses.

The volume varies; I've seen peaks of 500 messages an hour.
Unfortunately I will be consolidating a number of Exchange systems that
I currently do not have proper stats for.

What would be the rule of thumb be for sql vs. ldap?

Thanks,
Max

admin · Site Admin Joined: 21 Jun 2005 Posts: 209

I have never used mySQL with postfix. We put everything in LDAP. So, I
don't know where the two performance curves would meet (if they do).
There are ways to tune LDAP to get good performance, and that would get
you a long ways.

I'm thinking that for a peak of 500 messages an hour, either solution will
work for you. If someone else can confirm this, then it would be a matter
of taste. Would LDAP or mySQL be helpful for something else? We use LDAP
to authenticate IMAP logins, for example. LDAP is optimized for the kinds
of lookups that LDAP does. However, it has poor write performance. LDAP
provides limited querying. It does not offer you the broad flexibility of
mySQL queries. I like the almost instant replication of LDAP. I think
mySQL offers replication, but I don't know its granularity.

admin · Site Admin Joined: 21 Jun 2005 Posts: 209

I've some experience with postfix and MySQL. Altough, none of my servers
have more than 300 accounts and the biggest seems to deal with 250~350
messages/hour on its busiest hours.

Postfix seems to work very fine with postfix. It seems that some
'unnecessary' queries were done on MySQL for each message. I've seen several
users regarding this on the list, and I've confirmed that SEVERAL
'apparently' unnecesary queries are done. Anyway, it performs well. MySQL
databases are extremely small in size ( 28k for 350 accounts ). In this
case, i'm pretty sure that the whole database is kept on cache memory. And
that's pretty fast Smile

I've also Courier-IMAP running on the same machine using MySQL for
authenticating users. Courier-IMAP seems to handle about 1000~1100 pop3/imap
logins/hour on its busiest hours.

I've also seen several messages regarding problems when you have too
much writes/deletes over LDAP tables. Seems that LDAP handles better SELECTs
than INSERTs and DELETEs. I've never heard this kind of problem with MySQL.
SQL servers were build to handle that Smile

I'm pretty sure both LDAP and MySQL have advantages and disadvantages.
I'd strongly suggest that you use the one you have more experience. In fact
500 messages/hour is not that much ......

Sincerily,
Leonardo Rodrigues

admin · Site Admin Joined: 21 Jun 2005 Posts: 209

LDAP is fast.. very fast... however it is VERY hard to setup.

MySQL is easy to use however it is a memory hog.

What I do is use MySQL to manage/create/delete mail accounts and I have a
script that builts the equivalent HASH tables from the MYSQL.. that way I get
the easy of MySQL without postfix using it directly.. ( Postfix uses the hash:
(.db) files generated FROM the MySQL tables instead.

-matt

admin · Site Admin Joined: 21 Jun 2005 Posts: 209

LDAP is read optimized with replicated read servers and typicall
single instance "write-masters". SQL is read/write optimized, with some
implementations supporting distributed writers. LDAP implementations
typically do not offer transactional integrity.

If you want rock solid data inegrity and flexible query tools, go with SQL.
If you want fast replicated reads, and support for 3rd party LDAP-enabled
products, go with LDAP. Generally people use LDAP for email when they are
also already using it for other corporate directory applications or as
part of AD in a Microsoft environment.

admin · Site Admin Joined: 21 Jun 2005 Posts: 209

I like your solution. I am somewhat embarrassed that I didn't see it.

Using my areas of particular importance, your solution breaks down like
this:
1. security - hash files
2. reliability - hash files
3. ability to scale - hash files (same script or variant of script on
multiple servers all querying the same mysql db server, *or* use rsync
and/or nfs)
4. ease of administration - mysql database
5. performance - hash files

I personally can't find a flaw in your solution. It's hard to argue
that anything offers better reliability or performance than a hash
file. Some may argue that you can get more finite control wrt security
using MySQL or LDAP, but for an ISP I don't think that level of finite
security is required. Not 100% sure if hash files can scale as well as
LDAP or MySQL, but I think they may be able to.

You proved that the best solution is often the simplest solution. A
fact I somehow managed to forget.

Just for the record, I am logging your LDAP vs MySQL input as:
1. security - no comment
2. reliability - no comment
3. ability to scale - no comment
4. ease of administration - the nod goes to MySQL
5. performance - the nod goes to LDAP

Thank you.

Sincerely,
Chuck

admin · Site Admin Joined: 21 Jun 2005 Posts: 209

There is a drawback with hash file snapshots of database. They cannot
or should not be updated too frequently. If you can tolerate an O(10)
minute latency from record update in the database, to propagation to MTA,
you are set with hash files. Otherwise you need the live database.

admin · Site Admin Joined: 21 Jun 2005 Posts: 209

This was my general opinion as well. But I started doubting myself
simply because what I perceive as the popularity of LDAP. I was getting
the impression that LDAP was more popular (and more widely deployed) and
I assumed that there must be a reason for it. Some fact(s) that I was
unaware of. It definitely wouldn't be the first time. Smile

That's the kind of detail I was looking for. Thank you.

Are there any "gotta have" or "good to have" 3rd party LDAP-enabled
products that an ISP would be interested in?

Thanks,
Chuck

admin · Site Admin Joined: 21 Jun 2005 Posts: 209

For LDAP on my Postfix servers::

1: security: LDAP with Unix socket to slapd
2: reliability: LDAP - but only the latest, stable OL versions (2.2.15
and greater)
3: ability to scale: Stanford University has 300,000 email accounts with
9 LDAP servers
4: ease of administration: LDAP - depending on how you administrate it
Wink

5: performance: if it's Postfix 2.1 hash probably has the upper hand,
but LDAP with proxymap and a Unix socket to slapd on each server should
probably give you 2-300 operations per second, depending on your OS
(this is stanford University info).

--Tonni