MySQL versus OpenLDAP¶

THE SMACKDOWN OF THE CENTURY!!! A benchmark comparison of MySQL and OpenLDAP when using postfix to deliver to virtual mailboxes.

1 MySQL versus OpenLDAP

1.1 summary
1.2 background
1.3 the stress test
1.4 the test data
1.5 the results

summary¶

For database backed virtual mail systems, it is popular wisdom that OpenLDAP is faster than MySQL. However, in a stress test of delivering mail to 10,000 unique users sequentially, MySQL was the faster backend and created less cpu load.

The difference is not great: choose a database based on factors other than performance.

background¶

We have used ldap for years to hold our user database. We made this choice because
ldap is designed primary for read-heavy distributed authentication so we thought it would make the most sense to use the tool which was designed for the task at hand.

However, OpenLDAP has give us several years of grey hair and grief. So, I thought it would be good to revisit the question of OpenLDAP versus MySQL.

Our user database is used to resolve aliases/forwards, deliver mail to the correct mailbox, hold quota information, and authenticate the user. Eventually, it will also direct mail to the correct mail storage host.

Advantages to OpenLDAP:

high performance.
good for databases with few writes.
object structured data instead of relational structured.
good database replication.

Disadvantages to OpenLDAP:

there are not that many people who understand ldap.
our ldap storage seems prone to corruption.
it is a mystic science to optimize openldap.
it is harder to write web applications which use ldap.

Advantages to MySQL:

high performance.
many people with mysql skill.
easy to write web applications for.

Disadvantages to MySQL:

supposedly, MySQL replication is pretty good, but I don’t know.
a relational model is less directly applicable to our data.
maybe joins and stuff will be way slower than ldap.

So, really, the only thing in my mind that is keeping us tied to the horror of OpenLDAP is fear that MySQL will be slower. Is that true? Lets find out.

the stress test¶

Postfix comes with a program called smtp-source. It allows you to flood your postfix server with a ton of messages! Just the thing we need.

Here is the test script:

# -c  Display a running counter
# -l  message length in bytes
# -m  number of messages
# -s  number of sessions to run in parallel
# -N  prepend number to recipient

for i in `seq 1 1`; do
  smtp-source -s 10 -l 32 -m 10000 -c -N \
**f root@localhost -t user@nest.tld localhost:25
done

For the tests where we sent 1000 messages ten times, the seq line was change to ‘seq 1 10’ and -m was changed to 1000.

I set the message size small, because we just care about the time that postfix is taking to hit the database, not the time it takes to write the email.

This test will send dummy messages to 10000 users, from 1user@nest.tld to 10000user@nest.tld in order.

We normally use maildrop to deliver messages. In this case I just used postfix to deliver the messages to each user’s maildir.

Here is the postfix configuration:

######################################
## LDAP VS SQL BENCHMARK

virtual_gid_maps = static:1001
virtual_uid_maps = static:1001
virtual_minimum_uid = 1001
virtual_mailbox_base = /var/maildir
virtual_mailbox_limit = 0
virtual_mailbox_domains = static:nest.tld

######################################
## LDAP TEST

# uncomment these two lines to run the ldap test
#virtual_mailbox_maps = ldap:ldap_mailbox_map
#virtual_alias_maps = ldap:ldap_alias_map

ldap_mailbox_map_server_host = localhost
ldap_mailbox_map_bind = no
ldap_mailbox_map_search_base = ou=People,dc=riseup,dc=net
ldap_mailbox_map_query_filter = mailid=%s
ldap_mailbox_map_result_attribute = maildir
ldap_mailbox_map_version = 3

ldap_alias_map_server_host = localhost
ldap_alias_map_bind = no
ldap_alias_map_search_base = ou=People,dc=riseup,dc=net
ldap_alias_map_query_filter = mailid=%s
ldap_alias_map_result_attribute = maildrop
ldap_alias_map_version = 3

######################################
## SQL TEST

# the maps should be in a different file
# (mysql conf in main.cf is deprecated)

# uncomment these two lines to run the mysql test
#virtual_mailbox_maps = proxy:mysql:sql_mailbox_map
#virtual_alias_maps = proxy:mysql:sql_alias_map

sql_mailbox_map_hosts = 127.0.0.1
sql_mailbox_map_user = testdb
sql_mailbox_map_password = xxxx
sql_mailbox_map_dbname = testdb
sql_mailbox_map_query = SELECT maildir FROM mailboxes WHERE address='%s'

sql_alias_map_hosts = 127.0.0.1
sql_alias_map_user = testdb
sql_alias_map_password = xxxx
sql_alias_map_dbname = testdb
sql_alias_map_query = SELECT dest FROM aliases WHERE source='%s'

Disabling the bind for ldap makes ldap much faster. You just have to make sure that no one can search the ldap directory except from localhost (assuming you have not shell accounts on the machine).

You have to use 127.0.0.1 instead of localhost for the mysql settings because otherwise it will attempt to use a socket. Sockets are normally faster than TCP, but postfix on debian is chrooted. You can get around this by hardlinking the socket file, but my /var/run is in a different partition than /var/spool, so I didn’t use sockets.

the test data¶

Here is a full data dump of:

The MySQL schema:

CREATE TABLE `aliases` (
  `id` int(11) NOT NULL auto_increment,
  `source` varchar(255) default NULL,
  `dest` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `aliases_source_index` (`source`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `mailboxes` (
  `id` int(11) NOT NULL auto_increment,
  `address` varchar(255) default NULL,
  `password` varchar(255) default NULL,
  `quota` varchar(255) default NULL,
  `maildir` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `mailboxes_address_index` (`address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The LDAP schema:

objectIdentifier OID  1.1
objectIdentifier ldapOID OID:2
objectIdentifier attributetypeOID ldapOID:1
objectIdentifier objectclassOID ldapOID:2

attributeType (
    attributetypeOID:1
    NAME 'mailAddress'
    DESC 'email address(es)'
    EQUALITY caseIgnoreMatch
    SYNTAX 1.3.6.1.4.1.1466.115.121.1.15{256} )

attributeType (
    attributetypeOID:2
    NAME 'maildrop'
    DESC 'Mail addresses where mail is delivered -- ie forwards'
    SUP mailAddress )

attributeType (
    attributetypeOID:3
    NAME 'mailid'
    DESC 'Mail addresses accepted by this account -- ie aliases'
    SUP mailAddress )

attributeType (
    attributetypeOID:4
    NAME 'mailquota'
    DESC 'Bytes of mail quota'
    EQUALITY caseIgnoreMatch
    SUBSTR caseIgnoreSubstringsMatch
    SYNTAX 1.3.6.1.4.1.1466.115.121.1.15{256}
    SINGLE-VALUE )

attributetype (
    attributetypeOID:5
    NAME 'maildir'
    DESC 'where mail is stored'
    EQUALITY caseIgnoreMatch
    SUBSTR caseIgnoreSubstringsMatch
    SYNTAX 1.3.6.1.4.1.1466.115.121.1.15{256}
    SINGLE-VALUE )

objectclass (
    objectclassOID:1
    NAME 'mailAccount'
    DESC 'users who receive mail'
    AUXILIARY
    MAY (maildrop $ mailid $ mailquota $ maildir) )

the results¶

The column headings:

count: number of messages (one per unique user).
10×1000 means deliver 1000 messages to 1000 users
ten times.
con: number of sessions run in parallel.
time: time it takes to fully deliver all messages.
load: the first two load numbers returned by top.
memory: virtual/resident in megs
%cpu: estimate from watching from top

The slapd tests were run with a set_cachesize in DB_CONFIG of 2megs. I tried upping this to 50megs, and it seemed to improve the results by about ten seconds (for both the linear 10k test and the 10×1000 test).

For both ldap and sql, we disabled the log files. The ldap cachesize was set to 12000 entries (more than our entire directory). The mysql rows marked ‘p’ means that we enabled the postfix mysql proxy, the ones with ‘np’ means that we disabled the proxy. Perhaps we didn’t have the proxy featured installed for postfix, because there was no difference with our without the proxy.

These tests were run in the order listed without restarting daemons, other than postfix. The test machine was my desktop machine, running a single Athlon 1700, 1gb of ram, one sata disk. The OS was Ubuntu 6.06 (don’t tell anyone). MySQL version 5.0.22, OpenLDAP version 2.2.26, postfix version 2.2.10.

The main results:

test   count  con  time  load   memory  %cpu
*-------------------------------------------
mysql  10000  10   3:37  16/8   115/35  ~10%  p
mysql  10000  10   3:05  17/12  115/40  ~10%  p
mysql  10000  10   3:25  16/13  115/40  ~11%  p
ldap   10000  10   5:08  25/18  228/18  ~35%
ldap   10000  10   4:45  27/20  295/27  ~40%
ldap   10000  10   4:39  27/21  360/29  ~40%

Other tests run:

ldap   10000   1   6:45   4/6   360/30  ~25%
ldap  10x1000 10   4:38  28/17  360/30  ~40%
mysql  10000  10   3:05  16/13  190/41  ~20%  np
mysql  10000   1   4:53   4/6   190/40   ~6%  np
mysql  10000   1   5:07   4/3   190/41  ~10%  p
mysql 10x1000 10   3:03  14/6   118/41   ~4%  p

Caveats:

No work was done to tune either setup. In test runs not shown, the cachesize was
set very high for slapd, which seemed to modestly improve the speed by about ten seconds.
Slapd is fast. Mysql is fast. In the end, it is probably not meaningful to say that one is faster than the other. It is, however, useful to say that mysql is not slower in this usage.
The mysql database is not third normal form. Aliases —> forwards is actually a many to many relationship. But a join for every lookup would certainly hurt mysql performance. Every single one of the mysql+postfix setups that I could find on the web had some variation on the schema I used here to test.


elijah 2013-06-26	this benchmark is over ten years old, so it take with grain of salt.