Which one is faster: IMAP server or MySQL server?
Charles
I am planning to develop a webmail application focused on speed and I
was wondering where to store messages to get the fastest system
possible, with gigabytes and hundreds of thousands of emails. I could
keep messages with the IMAP server or I could store them with MySQL.
One should take into account that I will have no control over the IMAP
server: I will have to accept whatever IMAP server is installed, even
if it's not optimized, with mbox, etc...Also, I have no control over
the way the user organizes his mail, for instance he may accumulate
all his mail in his inbox (you know how sluggish it can get after some
time), nothing I can do against it. On the other hand, a standard
MySQL server should be pretty fast out of the box.
What I want to do is, with a common mail server (ie: Pentium IV 2Ghz,
1GB RAM, 2 HDDs in Raid-1), if I search a keyword among 500,000
emails, I want to get the search results on screen after half a second
at most.
Speed is the most important criteria I want to take into account.
What do you think is the best route in my case?
Thanks.
Cydrome Leader
don't expect to get high speeds on a random imap server you can't tune.
some tips for fast webmail.
- separate the webmail stuff from the mailserver. Both on one machine will
cause incredible contention.
- you want lots of RAM on the IMAP server to cache reads
- mbox format is incredibly slow for deleting messages, since the mbox
will be rewritten for each change.
- searching maildirs is fairly slow, there's lots of I/O from opening and
reading all the files/messages. That's where more RAM helps. Backing up
maildirs is slow. Restoring them is slow, but they scale better than mbox
stuff and are hard to corrupt.
- you may consider tuning your filesystem if you use maildirs, since you
will be dealing with lots of files
- imapproxy is great for really chatty webmail apps like squirrelmail
- use a really fast machine for webmail. It makes a difference.
- if you use a database for virtual mail control and user authentication,
that may benefit from tweaking as well. You can have hundreds of
connections between an imap server and the database machine. A quick and
dirty mysql install won't handle this properly.
Charles
> don't expect to get high speeds on a random imap server you can't tune.
Thanks, this is what I thought.
> some tips for fast webmail.
>
> - separate the webmail stuff from the mailserver. Both on one machine will
> cause incredible contention.
> - you want lots of RAM on the IMAP server to cache reads
> - mbox format is incredibly slow for deleting messages, since the mbox
> will be rewritten for each change.
> - searching maildirs is fairly slow, there's lots of I/O from opening and
> reading all the files/messages. That's where more RAM helps. Backing up
> maildirs is slow. Restoring them is slow, but they scale better than mbox
> stuff and are hard to corrupt.
> - you may consider tuning your filesystem if you use maildirs, since you
> will be dealing with lots of files
> - imapproxy is great for really chatty webmail apps like squirrelmail
> - use a really fast machine for webmail. It makes a difference.
> - if you use a database for virtual mail control and user authentication,
> that may benefit from tweaking as well. You can have hundreds of
> connections between an imap server and the database machine. A quick and
> dirty mysql install won't handle this properly.
Thanks for all these tips! :)
NM Public
> [...]
>
> What I want to do is, with a common mail server (ie: Pentium IV
> 2Ghz, 1GB RAM, 2 HDDs in Raid-1), if I search a keyword among
> 500,000 emails, I want to get the search results on screen
> after half a second at most.
>
> Speed is the most important criteria I want to take into
> account.
>
> What do you think is the best route in my case?
If you think your users might ever want to use IMAP, which is
highly likely thanks to Gmail popularizing IMAP, I suggest you
use IMAP. I'm able to do very fast searches using a Cyrus IMAP
server and keeping all my 100,000+ messages in one mailbox. If
you are thinking about using IMAP *AND* MySQL, I recommend that
you read my blog item about this:
For IMAP, "SQL just sucks"
<http://deflexion.com/2006/06/for-imap-sql-just-sucks>
Also read the Dovecot mailing-list thread that I mention in this
blog item:
Using MySQL to store email?
<http://thread.gmane.org/gmane.mail.imap.dovecot/12817>
Good luck with your project,
Nancy
Charles
> If you think your users might ever want to use IMAP, which is
> highly likely thanks to Gmail popularizing IMAP, I suggest you
> use IMAP.
Actually no, this would be web-access only. Pretty much like a forum
application vs. Usenet, but for email. People who want to access using
IMAP would use existing IMAP desktop clients or IMAP webmails.
> I'm able to do very fast searches using a Cyrus IMAP
> server and keeping all my 100,000+ messages in one mailbox.
Yeah, I'm convinced it's possible to set up properly a mail server and
to fine-tune it for large mail boxes and for speed. But not everybody
has access to do it.
> If
> you are thinking about using IMAP *AND* MySQL, I recommend that
> you read my blog item about this:
>
> For IMAP, "SQL just sucks"
> <http://deflexion.com/2006/06/for-imap-sql-just-sucks>
>
> Also read the Dovecot mailing-list thread that I mention in this
> blog item:
>
> Using MySQL to store email?
> <http://thread.gmane.org/gmane.mail.imap.dovecot/12817>
Wow, thanks Nancy. I read the whole thread and the blog entry. Very
interesting discussion. We can see that developers can say completely
different things in the Dovecot list. I think a MySQL database to
store text messages shouldn't be any different from a large forum
database, right? Assuming we store attachments on the file system, and
that we don't support IMAP.
Timo Sirainen
> For IMAP, "SQL just sucks"
> <http://deflexion.com/2006/06/for-imap-sql-just-sucks>
I see it has a comment about Zimbra storing metadata to SQL and message
bodies to files. My original comments about "why SQL sucks for IMAP" was
actually more about metadata than bodies.
Implementing a well performing IMAP server is mostly about minimizing
disk I/O usage as much as possible. Designing index files specifically
for IMAP can get a lot better performance than using a generic SQL
server. For example some data may be accessed much faster if the data is
stored in column-oriented way rather than row-oriented. Or the server may
even store the data in both ways to optimize for two different clients or
commands (e.g. SORT (RECEIVED) vs. FETCH 1:* (INTERNALDATE ENVELOPE
BODY)).
IMAP also requires that server notifies client of changes done to mailbox
by another connection. Probably the fastest way for server to do this is
by writing all changes to a transaction log file. Each process can then
read the file to see the new changes instead of reading and going through
all message uids/flags and figuring out what had changed. Of course this
is also possible with SQL by creating a transaction table logging the
changes, but that's just extra writes that could have been avoided by
using the SQL server's internal transaction log. I don't think any SQL
servers allow that.
Anyway I thought I'd run a couple of benchmarks to see how some of the
SQL-based IMAP servers performed using my imaptest tool
(http://imapwiki.org/ImapTest).
The tests show that performance is pretty similar with all SQL-based IMAP
servers. I guess there's a bottleneck somewhere. If anyone has
suggestions, let me know and I'll rerun the tests. I don't think
PostgreSQL settings matter much because the database is so small (3000
mails). Also I'd have thought that Zimbra optimized itself for best
performance since it installs everything itself.
So, the benchmark:
System: Intel Core 2 6600, ext3 filesystem. 2GB of memory of which 1GB
was allocated to kvm virtual machine running Zimbra. So Zimbra only got
one CPU core to use while others got 2 cores.
Appending 3000 mails. Each IMAP connection is appending to a different
mailbox (test/0 .. test/5). MULTIAPPEND extension isn't used, but LITERAL+
is and commands are pipelined. Dovecot calls fsync() for each saved mail,
I assume SQL servers call fsync() when committing as well.
./imaptest box=test/%u - logout=0 append=100,0 msgs=100000 clients=5
dovecot 1.1 / dbox: 3.2s
dovecot 1.1 / maildir: 6.7s
dbmail 2.3.2 / postgresql: 73s (48s with 10 clients)
archiveopteryx 2.06 / postgresql: 39s (same with 10 clients)
zimbra 5.01: 72.5s
5 clients were used because Zimbra has a 5 connections per user limit, and
I didn't bother modifying my test to use different users. Increasing
clients from 10 didn't seem to improve dbmail speed.
2) Fetching message bodies
Modified imaptest to send FETCH (UID BODY.PEEK[]) commands for one random
message at a time for 20 seconds. Again commands are pipelined. The
speeds are after all messages were cached to memory, so disk I/O didn't
really matter.
./imaptest box=test/%u - logout=0 fetch2=100 clients=5 seed=1 secs=20
dovecot 1.1 / dbox: 135000 (6000-7000/s)
dovecot 1.1 / maildir: 135000 (6000-7000/s)
dbmail 2.3.2 / postgresql: 3500 (160-180/s)
archiveopteryx 2.06 / postgresql: 3300 (160-170/s)
zimbra 5.01: 3400 (160-180/s)
(dovecot 1.1 / maildir / data not cached in memory: 4300, 200-300/s)
3) Fetching simple metadata
Modified imaptest to fetch only UID and FLAGS. Each FETCH fetches 100
messages (or all messages if mailbox has less):
./imaptest box=test/%u - logout=0 fetch=100 clients=5 seed=1 secs=20
dovecot 1.1 / dbox: 6300 (310-330/s)
dovecot 1.1 / maildir: 5700 (280-290/s)
dbmail 2.3.2 / postgresql: 2200 (110/s)
archiveopteryx 2.06 / postgresql: 2100 (100/s)
zimbra 5.01: 2200 (110/s)
Timo Sirainen
> On Fri, 08 Feb 2008 13:46:27 +0000, NM Public wrote:
>
>> For IMAP, "SQL just sucks"
>> <http://deflexion.com/2006/06/for-imap-sql-just-sucks>
>
> I see it has a comment about Zimbra storing metadata to SQL and message
> bodies to files. My original comments about "why SQL sucks for IMAP" was
> actually more about metadata than bodies.
Oh and one more thing I forgot to mention: SQL servers are often
advocated because of their ability to do full text search indexing to
provide fast message body searches. Unfortunately using this feature with
IMAP's SEARCH TEXT/BODY command violates the IMAP protocol, because it
can't do proper substring matching. Some servers do it anyway.
Sam
> Anyway I thought I'd run a couple of benchmarks to see how some of the
> SQL-based IMAP servers performed using my imaptest tool
> (http://imapwiki.org/ImapTest).
>
> The tests show that performance is pretty similar with all SQL-based IMAP
> servers. I guess there's a bottleneck somewhere.
There is no bottleneck anywhere. Or, in other words: the bottleneck is in
the overall design. Or, in still other words; it's a completely fucked up
idea; it was always a fucked up idea; and it will always be a fucked up
idea.
Every other month, or so, someone always waltzes in here, gushing forth his
latest wet dream about putting an IMAP store in a traditional relational
database. Without fail, the only thing they know about IMAP is what they
read in some marketing blurb, or a "technical details of X for CEO dummies"
type of an article in a pseudo-intellectual rag like Forbes. They might have
some superficial exposure to IMAP, but it's limited to no more than pushing
a button to make it go, or reading its log files. But they really have
absolutely no idea what IMAP is, what it does, or have written a single line
of code that ventured anywhere near anything that has ever pretend to be
anything associated with IMAP. They just think it's a cool thing to do
because either someone told them so, or they came up with it on its own, and
it sounds (emphasis on the word "sounds") fantastic.
It won't do any good talking to these folks. The end results are just like
trying to reason with someone high on crack. They just won't listen, won't
understand, and lack even the capability to understand. The best thing to do
is just wish them good luck, and let us know how it turns out.
So, off they go, to cobble together something with various bits and pieces
of code they find somewhere on the net. Then, when the results turn out to
be utter crap, they act dumbfounded and surprised.
No shit, Sherlock.
The reason why this kind of a setup will always turn out to be crap is
pretty much the same reason why you can't drive an automobile on the bottom
of the ocean, except in a James Bond movie. You can't fit a square peg into
a round hole, you can't fly no matter how hard you flap your arms, and you
can't put an IMAP store in a traditional relational database. If you: 1)
have actually written production IMAP software, and 2) have actually written
production software that uses traditional "heavy-iron" relational databases,
like Oracle, Sybase, or Informix, or even "light" or "medium"-iron databases
like MySQL or PostgreSQL, and thus understand what they do or do not, and
how they behave, then you'll understand why. Unfortunately it's not
something that subjects itself to a capsule summary limited to a single
Usenet post.
So, forget about the whole idea of putting IMAP into an SQL server. Just
drop it. The whole idea is dead. It's no more. It's ceased to be. It's
expired, and gone to meet it's maker. It's completely stiff, bereft of life,
and is resting in peace. It kicked the bucket, shuffled off its mortal coil,
ran down the curtain, and joined the bleedin' choir invisible.
There are no absolute technical barriers that prevent one from doing such a
thing, of course, but the end result will always be crap. And if you ever
happen to run into an IMAP SQL implementation that seems to return
reasonable performance, let me have a look, and I'm fairly optimistic that I
can come up with a couple of IMAP queries that will bring this beast to its
knees (and if it just upchucks and dies, then it will forfeit the technical
privilege of calling itself an IMAP server).
A database has to be specifically designed to accomodate a back-end to an
IMAP server, in order to have any kind of a glimmer of hope of getting any
kind of reasonable results, you just can't foist one on top of it. There's a
clear analogy here with XML. Like IMAP, you cannot fit XML into a
traditional relational database model. Well, like IMAP, you really can but
will get similarly crappy results.
But:
Newer databases now provide specific resources for accomodating XML data.
They implement dedicated object types that accept and store XML-formatted
data, and offer offer various XML operations on it. The same thing will need
to be done for IMAP as well: the database engine itself has to explicitly
implement IMAP primitive on IMAP objects, before anyone has any glimmer of a
hope of using a database-backed IMAP store.
Cydrome Leader
> On Fri, 08 Feb 2008 13:46:27 +0000, NM Public wrote:
>
>> For IMAP, "SQL just sucks"
>> <http://deflexion.com/2006/06/for-imap-sql-just-sucks>
>
> I see it has a comment about Zimbra storing metadata to SQL and message
> bodies to files. My original comments about "why SQL sucks for IMAP" was
> actually more about metadata than bodies.
>
> Implementing a well performing IMAP server is mostly about minimizing
> disk I/O usage as much as possible. Designing index files specifically
> for IMAP can get a lot better performance than using a generic SQL
> server. For example some data may be accessed much faster if the data is
> stored in column-oriented way rather than row-oriented. Or the server may
> even store the data in both ways to optimize for two different clients or
> commands (e.g. SORT (RECEIVED) vs. FETCH 1:* (INTERNALDATE ENVELOPE
> BODY)).
while obviously not IMAP based, the original hotmail was able to scale to
a large size because they split all message headers and metadata from the
bodies of the messages.
Until you actually checked a message, the message body wasn't even
touched.
Charles
> There is no bottleneck anywhere. Or, in other words: the bottleneck is in
> the overall design. Or, in still other words; it's a completely fucked up
> idea; it was always a fucked up idea; and it will always be a fucked up
> idea.
In my original message, I didn't mention IMAP with a MySQL database
backend (that would have been a bad idea), I mentioned either:
- A regular mail server with IMAP
- A regular MySQL server without IMAP
Maybe I wasn't clear enough, but anyway. I chose the second solution,
especially after suffered myself from sluggish IMAP servers in shared
environment where I don't have access to fine-tune it, and also after
reading a number of threads where technicians basically say there's no
way to have in a *standard* email server gigabytes of messages and to
perform quick and advanced searches like one could do within a forum.
For instance:
- http://tinyurl.com/2acwy2
- http://tinyurl.com/28zq32
- http://tinyurl.com/ysa3tk
- http://tinyurl.com/ysgghd
Nothing against IMAP, though. I think it's fine when:
- You have total control over your system
- You don't have too many messages
- You want to use your MUA
My webmail's not going to support IMAP, but there are already plenty
of IMAP webmails around already, so there's no problem. In my webmail,
I'm going to use email piping to receive emails and to store them to a
database (MySQL, SQLite or Berkeley DB). Then I'll retrieve emails
using PHP and the database connector. It's basically going to be a
content managent system.
Cheers,
Charles
> 2) Fetching message bodies
>
> Modified imaptest to send FETCH (UID BODY.PEEK[]) commands for one random
> message at a time for 20 seconds. Again commands are pipelined. The
> speeds are after all messages were cached to memory, so disk I/O didn't
> really matter.
>
> ./imaptest box=test/%u - logout=0 fetch2=100 clients=5 seed=1 secs=20
>
> dovecot 1.1 / dbox: 135000 (6000-7000/s)
> dovecot 1.1 / maildir: 135000 (6000-7000/s)
> dbmail 2.3.2 / postgresql: 3500 (160-180/s)
> archiveopteryx 2.06 / postgresql: 3300 (160-170/s)
> zimbra 5.01: 3400 (160-180/s)
> (dovecot 1.1 / maildir / data not cached in memory: 4300, 200-300/s)
Although I trust your results, for me they are just an aberration.
When I perform a search in a forum, I expect to wait no more that 0-2
secs to have the results. I expect no less from an IMAP server.
Cheers,