Better performance with MySQL or LDAP?
Andreas Ahlenstorf
We would like to use Postfix (including virtual, alias and
transport), Courier-IMAP and Cyrus-SASL with MySQL or LDAP.
Now, the question is, what is faster? I think, on small systems I
would use MySQL, because it's easier to handle, but on very big
systems with hundreds of messages every minute and thousands of users
everything depends on a good performance. Now, can anybody tell me
something about your experience with one of this systems? What is
faster?
Thanks,
Andreas Ahlenstorf
Markus Schabel
LDAP is optimized for searches. Because your users are almost statically
I'd prefer LDAP. LDAP's also easy to handle and you can do a LDAP-based
addressbook (works with Mozilla/Netscape and Outlook without problems)
and a lot of authentication via LDAP.
If you just want a small (mail)system, and have knowledge of MySQL you
should use this, but on large systems, where you want one database for
your addressbook, mailserver, authentication (*NIX and Windows) you
should use LDAP.
At least IMHO ;)
regards
Markus Schabel
+--------------------------------------------+
| TGM - Die Schule der Technik, IT-Service |
| A-1200 Wien, Wexstrasse 19-23 |
| Tel.: +43(1)33126/316 Fax: +43(1)33126/154 |
| eMail: markus....@tgm.ac.at |
| markus....@member.fsf.org |
| FSF Associate Member #597 |
| Linux User #259595 (http://counter.li.org) |
+--------------------------------------------+
Computers are like airconditioners:
They stop working properly if you open windows.
Jasen Baker
currently running postfix on a cluster of 8 servers with two LDAP servers
for lookups handling 500,000+ emails per day. The LDAP caching in the new
version of Postfix with LDAP caching has also improved performance as the
slapd processes consume about 10% cpu on a SunV100 500mhz ultra sparc machine.
LDAP is easier to do replication with and in my opinion, is easier to use
than MySQL for a backend DB.
Jasen Baker
Manager - Systems Engineering
http://www.pacifier.com
At 05:55 PM 01/26/2003 +0100, you wrote:
>Hello,
>
>We would like to use Postfix (including virtual, alias and
>transport), Courier-IMAP and Cyrus-SASL with MySQL or LDAP.
>Now, the question is, what is faster? I think, on small systems I
>would use MySQL, because it's easier to handle, but on very big
>systems with hundreds of messages every minute and thousands of users
>everything depends on a good performance. Now, can anybody tell me
>something about your experience with one of this systems? What is
>faster?
>
>
>Thanks,
> Andreas Ahlenstorf
Marcio Merlone
<andreas.a...@gmx.ch> escreveu:
> Hello,
>
> We would like to use Postfix (including virtual, alias and
> transport), Courier-IMAP and Cyrus-SASL with MySQL or LDAP.
> Now, the question is, what is faster? I think, on small systems I
> would use MySQL, because it's easier to handle, but on very big
> systems with hundreds of messages every minute and thousands of users
> everything depends on a good performance. Now, can anybody tell me
> something about your experience with one of this systems? What is
> faster?
>
>
> Thanks,
> Andreas Ahlenstorf
LDAP claims to faster to answer the queries. Since you asked for
experiences, just take great caution if you plan to use mysql or ldap on
FreeBSD. Both make intense use of threads and FBSD has a weak
implementation of threads (at least 4.X series, dont know about 5.X
yet).
I had big troubles with a FBSD server running mysql, refusing more
than 140 simultaneous connections. Perhaps I was just unable to tune
FBSD for mysql, but you may run into the same trouble.
--
Marcio Merlone
Andreas Ahlenstorf
Marcio Merlone schrieb am Montag, 27. Januar 2003 um 13:30:
> LDAP claims to faster to answer the queries. Since you asked for
> experiences, just take great caution if you plan to use mysql or ldap on
> FreeBSD. Both make intense use of threads and FBSD has a weak
> implementation of threads (at least 4.X series, dont know about 5.X
> yet).
> I had big troubles with a FBSD server running mysql, refusing more
> than 140 simultaneous connections. Perhaps I was just unable to tune
> FBSD for mysql, but you may run into the same trouble.
We just plan to use this system on the most popular Linux
distributions (Debian, RedHat, SuSE and United Linux). Now, I like to
know what does 'faster' mean. A lot of people say, LDAP is faster, but is
it one request per second or hundred requests per second more? And
what is better implemented in Postfix?
- Andreas Ahlenstorf
Marcio Merlone
<andreas.a...@gmx.ch> escreveu:
> We just plan to use this system on the most popular Linux
> distributions (Debian, RedHat, SuSE and United Linux). Now, I like to
> know what does 'faster' mean. A lot of people say, LDAP is faster, but
> is it one request per second or hundred requests per second more? And
> what is better implemented in Postfix?
>
> - Andreas Ahlenstorf
It wont help you much, but ldap is A LOT MORE fast than mysql. :) I just
dont know how much.
I dont have any url pointing to benchmarks, if someone has, please tell
us. Regarding postfix implementation, I used to believe everything is
well implemented. :)
Ok, ok, I dont know wich is best implemented in postfix.
--
Marcio Merlone
Michael Alan Dorman
> We just plan to use this system on the most popular Linux
> distributions (Debian, RedHat, SuSE and United Linux). Now, I like
> to know what does 'faster' mean. A lot of people say, LDAP is
> faster, but is it one request per second or hundred requests per
> second more? And what is better implemented in Postfix?
I have multiple systems using LDAP for both relay_domains lookups and
transport_maps, and have achieved in excess of 220 messages/minute
throughput, or roughly 4 per second, and I suspect that the bottleneck
was really the spamassassin check in the content_filter. :-)
So I have personally documented 8 lookups/second on what is pretty
low-end hardware these days---dual PIII/600 with a pair of mirrored
drives on U2 scsi.
I also know of a system using MySQL for virtual_maps, and they pass
quite a lot of traffic, though I don't have a clear view on exactly
how much---the SQL query they're using is pretty bad, though; the
tables were setup in a way that almost guarantees poor performance.
We're working on an overhaul of this system, and I hope to be able to
tune this a bit and get some good numbers off it, though that's a
couple of months away.
I hope this is in someway helpful.
Mike.
zo...@ebonet.net
Here we have around 4700 users and small LANs on MySQL (RedHat 8.0) and it
works very well.
I can't say what's faster, i've tried to setup a configuration with LDAP, since
it's a very wide supported protocol for user lookup & autentication.
But due to lack of time, to learn how to setup an efficient schema, I've ended
up with MySQL since I was more confident with it (have done a lot of database
driven websites with it).
OK. So, on Set/2002, I've setup a mailserver with courier-IMAP/Postfix/MySQL
System is: Dual Pentium III Xeon 2.4 GHz, 4GB RAM
17GB on /var (RAID 5) and mail stored on a storage server via NFS.
I have to say that if you are going to use RedHat, you are better with MySQL
RPM from www.mysql.com, has with these you will be able to change the
max_connections variable.
I use two versions of MySQL files. For server and client I use version 4.0.9
(started with 4.0.4 - MySQL-4.0.x and MySQL-client-4.0.x). For development
libraries needed by Postfix and Courier-IMAP, I use MySQL-shared and MySQL-
devel 3.23.54a (actually 3.23.55).
The good thing with RPM's from www.mysql.com is that these packages are
independent from each other. So, one can mix Server/Client with different
versions of Shared/Devel.
Why two versions?
Because, for the server, I needed the best performance, and 4.0.x performed
best. Version 3.23 is used for compatibility issues because of the version of
the library files (I had troubles compiling MySQL support for Courier-IMAP and
PHP using version 4.0.x of MySQL libraries and headers - actually rebuilding
the RPM packages).
I don't know what is our load on the mailserver but I can tell you that there
is a lot of traffic.
Actually I have an average of 21.21 queries/sec and
553 simultaneous connections on MySQL database. According to MySQL
Documentation, you can have up to 1000 simultaneous connections or more,
depending on hardware and some tweaking.
I've setup a second machine for a NGO - System: P4 512MB, 40GB HD (IDE). With
300 users and performs very well.
Hope this can help.
Oscar Zovo.
Citando Michael Alan Dorman <mdo...@debian.org>:
----------------------------------------------------
Esta mensagem foi enviada através do serviço Mukanda
This mail sent through Mukanda WebMail
http://mukanda.ebonet.net/
EBONet - Provedor de Serviços Internet
HelpDesk: (02) 392183
assis...@ebonet.net
Jure Pecar
Andreas Ahlenstorf <andreas.a...@gmx.ch> wrote:
> We just plan to use this system on the most popular Linux
> distributions (Debian, RedHat, SuSE and United Linux). Now, I like to
> know what does 'faster' mean. A lot of people say, LDAP is faster, but
> is it one request per second or hundred requests per second more? And
> what is better implemented in Postfix?
Yes, LDAP is faster, but only for read queries. It has its value for
example in mail routing setups on larger setups, where the number of
writes is low enough. I found out that in a 170k users setup where most
user info is stored in an OpenLDAP server, where users are changing theri
passwords & stuff, writes can become a serious performance problem.
So what would work best for you mainly depends on your needs and your
setup.
--
Jure Pecar
Kevin Sindhu
>LDAP claims to faster to answer the queries. Since you asked for
>experiences, just take great caution if you plan to use mysql or ldap on
>FreeBSD. Both make intense use of threads and FBSD has a weak
>implementation of threads (at least 4.X series, dont know about 5.X
>yet).
Strictly speaking for Net/Open BSD's, they have just (about two weeks ago),
gotten native threads in -current and many applications are being tested
(including OpenLDAP).
>I had big troubles with a FBSD server running mysql, refusing more
>than 140 simultaneous connections. Perhaps I was just unable to tune
>FBSD for mysql, but you may run into the same trouble.
Similarly, mysql (under very heavy load) crashes both with or without threads:
http://www.sigmasoft.com/~openbsd/archive/openbsd-tech/200301/msg00128.html
http://www.sigmasoft.com/~openbsd/archive/openbsd-tech/200301/msg00219.html
LDAP is a better choice, but you may need to fiddle with some param
(sldap.conf) in order to boost perforamance.
-Kevin
Andreas Ahlenstorf
Jure Pecar schrieb am Montag, 27. Januar 2003 um 19:20:
> writes is low enough. I found out that in a 170k users setup where most
> user info is stored in an OpenLDAP server, where users are changing theri
> passwords & stuff, writes can become a serious performance problem.
> So what would work best for you mainly depends on your needs and your
> setup.
That's a good point. So, with such possibilities for the users, I
shouldn't take LDAP? Because I'll have a webfrontend where the users
can change their passwords, user settings, forwards, aliases and so
on...
Regards,
Andreas
Andreas Ahlenstorf
Michael Alan Dorman schrieb am Montag, 27. Januar 2003 um 16:55:
> I have multiple systems using LDAP for both relay_domains lookups and
> transport_maps, and have achieved in excess of 220 messages/minute
> throughput, or roughly 4 per second, and I suspect that the bottleneck
> was really the spamassassin check in the content_filter. :-)
So, with spamassassin there can't be any difference between LDAP and
MySQL? Very good :)
> I hope this is in someway helpful.
Yes, it is. Another piece in a very big puzzle...
Regards,
Andreas
lst...@kwsoft.de
That depends on how heavy they make use of it and how many users you have.
You can tune the LDAP server for better write performance by disable
"sync-on-writes" but with risk of trashing the database in case of failure.
You can setup one Master for writes and replicated Slaves for reads etc.
You can even setup LDAP for mail-routing and MySQL for user address-book.
It all depends on your budget, your knowledge and you needs.
Regards
Andreas
Bennett Todd
--jRHKVT23PllUwdXP
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
2003-01-26T11:55:10 Andreas Ahlenstorf:
> We would like to use Postfix (including virtual, alias and
> transport), Courier-IMAP and Cyrus-SASL with MySQL or LDAP.
It can be done, of course.
> Now, the question is, what is faster?
That's _a_ question. I don't think of it as _the_ question, indeed I
think it's an uninteresting question.
Rather, which is better suited for a particular application? Both
MySQL and LDAP are poor performers in the world of large-scale
email-handling plants. But they have other strengths. MySQL is a
data store providing robust support for concurrent updates by
separate users without data corruption, and a reasonably efficient
query optimization engine allowing many different sorts of queries
to be performed with pretty good speed.
LDAP is tuned for readonly use, it offers an API for querying that's
pretty ghastly, but it's equally repulsive no matter where you're
coming from, so it's a very popular rendesvous point for
implementing phonebooks and suchlike directories over heterogenous
platforms.
> I think, on small systems I would use MySQL, because it's easier
> to handle, but on very big systems with hundreds of messages
> every minute and thousands of users everything depends on a good
> performance.
Errh, hundreds of messages every minute and thousands of users is a
small system, that's the sort of thing you can very nearly deliver
off a laptop, and can easily handle with a low-end server. If High
Availability is a demand, use a single pair of servers, each of
which offers MTA (through equal-weighted MX records) and MUA
(duplicate A records with random DNS serving, or a load balancer, or
whatever); have them share a mailstore on a NetApp. If you can't
afford a NetApp, you aren't going to have a happy High Availability
experience with a mail-store; oddments can be hacked up, but they
aren't happy-making. But this is still a small email plant.
A big email plant is serving thousands of messages per second and
hundreds of thousands or millions of users; that's where you cannot
coerce any single protocol flow of interest through a single
handling machine; you have to have server farms behind load
balancers, and they need to be highly available (because with so
many boxes, the odds of at least one dying grow annoying).
> Now, can anybody tell me something about your experience with one
> of this systems? What is faster?
They're both perfectly adequate for weeny little systems, and with
sufficient care and tuning you can make more or less functional
email plants up to mid-size on either; the performance differences
between MySQL and LDAP aren't as dramatic as the range of diversity
among different LDAP implementations, or the range of effects you
get based on your performance tuning skills.
For very big email plants, do not make either Mail Transport Agents
(MTAs, like Postfix or qmail) nor Local Delivery Agents (LDAs,
e.g. Courier-maildrop, or Cyrus's deliver program), nor Mail User
Agents (MUAs, e.g. Courier-IMAP, or Cyrus) directly dependant on
_any_ network-shared data store. Instead, build your server farms,
with your stacks of MTA servers and stacks of MUA servers, all
sharing back-end NAS mailbox servers (e.g. NetApps), and make each
and every server that needs user databases (MTA, LDA, MUA) use
private copies in local hash tables. For heavy performance demands,
use CDB wherever possible.
If MySQL is a convenient platform for building an admin interface,
fine; just periodically export from it. If LDAP is convenient
because some data is coming from some other directory service and
LDAP is the public exported view of that data, fine, use ldapsearch
to periodically dump an LDIF of the data you need to present to
MTA/LDA/MUA, and whack it with a perl stick to transform it into the
maps you need. Only let Postfix or Courier or whatever suck directly
off MySQL or LDAP if you aren't going for the limiting case in
high-end scalable systems.
-Bennett
--jRHKVT23PllUwdXP
Content-Type: application/pgp-signature
Content-Disposition: inline
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)
iD8DBQE+NaxuHZWg9mCTffwRAph3AJ412Q4ZbNCg9Lx10wm20sGfGy9M6wCeMNiy
sR0SB1VIrNKGJklJAj3M1rw=
=urpN
-----END PGP SIGNATURE-----
--jRHKVT23PllUwdXP--
Andreas Ahlenstorf
Bennett Todd schrieb am Montag, 27. Januar 2003 um 23:02:
> Errh, hundreds of messages every minute and thousands of users is a
> small system, that's the sort of thing you can very nearly deliver
> off a laptop, and can easily handle with a low-end server. If High
> Availability is a demand, use a single pair of servers, each of
> which offers MTA (through equal-weighted MX records) and MUA
> (duplicate A records with random DNS serving, or a load balancer, or
> whatever); have them share a mailstore on a NetApp. If you can't
> afford a NetApp, you aren't going to have a happy High Availability
> experience with a mail-store; oddments can be hacked up, but they
> aren't happy-making. But this is still a small email plant.
> A big email plant is serving thousands of messages per second and
> hundreds of thousands or millions of users; that's where you cannot
> coerce any single protocol flow of interest through a single
> handling machine; you have to have server farms behind load
> balancers, and they need to be highly available (because with so
> many boxes, the odds of at least one dying grow annoying).
Well, okay, that's a question of my point of view. For me, 100'000
emails every hour are very much! :)
> If MySQL is a convenient platform for building an admin interface,
> fine; just periodically export from it. If LDAP is convenient
> because some data is coming from some other directory service and
> LDAP is the public exported view of that data, fine, use ldapsearch
> to periodically dump an LDIF of the data you need to present to
> MTA/LDA/MUA, and whack it with a perl stick to transform it into the
> maps you need. Only let Postfix or Courier or whatever suck directly
> off MySQL or LDAP if you aren't going for the limiting case in
> high-end scalable systems.
Thanks a lot. That's all I would like to know. EOT :)
Regards,
Andreas