Subject: Database mailbox storage

From: Dag Nygren <[email-masked]>
Date: Thu, 24 May 2001 10:53:12 +0300

Hi,

I have for a long time had the idea that using a
database (ie. MySQL) for storing the messages would
be a good idea.
Now I checked out the sources for IMAP and found that
it would not be an impossible task to make a driver for
this and fit it into C-client/IMAP.

As the research I made on the subject suprisingly didn't
show up with any similar solutions I have a couple of
questions to the mailexperts on the list:

1. Is this a bad idea ?

2. Has anyone done this already. Wouldn't like to reinvent
  the wheel.

3 Hints, advice and gotchas on what to think about when setting up the
  database layout.

BRGDS

-- 
Dag Nygren                               email: [email-masked]
Oy Espoon NewTech Ab                     phone: +358 9 8024910
Träsktorpet 3                              fax: +358 9 8024916
02360 ESBO                              Mobile: +358 400 426312
FINLAND

-- 
------------------------------------------------------------------
 For information about this mailing list, and its archives, see: 
 http://www.washington.edu/imap/c-client-list.html
------------------------------------------------------------------

---

From: "Julian Hall" <[email-masked]>
Date: Thu, 24 May 2001 11:58:21 +0100

I certainly think this is a good idea.  As far as MySQLs performance goes, I
have had some good experience with it.  I run a few web sites which deal
with large numbers of images and have found that storing the image data
directly in the database generally gives better performance than storing a
file name and having to have the server find and open that file from among a
very large (2,000+files) directory.  Also, MySQL's more recent updates not
only will allow more reliable systems in case of crash (eg transaction
support would help with this) but also additional performance benefits
(delayed updates could be useful in many circumstances...?).  Also
transactional logging would mean that a corrupted mail spool could easily be
recovered.

A database driver also wouldn't suffer from the mailbox-within-mailbox
limitations I have been discussing in another thread recently.

If you need a hand with this, I'm available.  I'll certainly help alpha/beta
test it for you.

Jules

---

From: Sean Witham <[email-masked]>
Date: Thu, 24 May 2001 10:39:44 +0100

> database (ie. MySQL) for storing the messages would

Given the large nature of attachements etc used with email now MySQL
may not be the best approach for storing the actual messages MIME
contenet. I doubt MySQLs blobs would be up to the job. It should do
fine for indexing the message (headers, attachemnts and all), but you
will still probably need something like MBX spool area to store the
actuall messages in. MySQL is good for structed databases, Email
headers are well suited to a structered approach but the body
contenets is often not. Postgress may be able to handle the
unstructred content better with is OO extentions but I haven't checked
this out.

> 1. Is this a bad idea ?

Its a good idea. Their are advantages when managing VERY large
mailboxes. Message can be located and retrived more easily (from a
users point of view at least). It lends itself to both IMAP access and
WEB acesses. The same data can also be used by an MTA when acepting
and deliviring email. Check the exim MTA it has MySQL hocks. It would
also enable the MTA to sort the email into sub folders (like
procmail). You wouldn't need a password file for email accounts. Other
advantages include the ability to generate ALL SORTS of mail
statistics for your server. You may be able to introduce an NFS safe
locking scheem by using the database as the locking engine but I see
very few situtations where that would be useful. There is proabably a
lot more that you could achieve

> 3 Hints, advice and gotchas on what to think about when setting up the

See my previous comments.

Databases can consume large amounts of memory when carrying out
complex querries the results of which will also take a up a lot of
memeory in the imapd process. Just something to bear in mind when you
start using those C APIs to build your driver.

--Sean Witham