Mail server with database

am 12.10.2006 20:45:30 von grpprod

Hi all,
is it possible to create a mail server that completely uses a database
(eg MySQL) to store user mailboxes? I suppose that a MTA,MDA and
POP/IMAP servers which all support database (not only for
authentication but also for storage) are needed. Are there such
products? Any good recommendations would be appreciated.

Re: Mail server with database

am 13.10.2006 00:21:38 von Sam

Re: Mail server with database

am 13.10.2006 09:14:53 von grpprod

> Theoretically, yes. Practically, this is one of those things that might
> look good on paper to some PHB, but in practice it turns out to be a real
> clunker, and nobody in their right mind would want to do.
>

Thanks for the reply, Sam. I suppose you're the Courier-IMAP Sam,
right?
So in this case, I have to include some more details. We are planning
to upgrade our whole mail system. We currently use
Sendmail/UW-IMAP/Qpopper/MailScanner/SpamAssassin/ClamAV.

We do not have any specific issues arising, except perhaps the
performance issue. It seems to me that the whole combination is not
efficient enough. For start, I think that MailScanner has grown to be a
somehow 'heavy' and memory consuming project (at least in our case, the
20MB/child turns out to be a myth :)). However its great set of
features, its ease of use and administration, together with the
excellent support from Julian, makes it our choice instead, say, of
using some milter features of sendmail with Clam and SA. Moreover, many
of our users have extremely large mailboxes (starting at 150MB and
growing), and traditional UNIX mailboxes are a nightmare in terms of
performance for such sizes. Finally, there is a chance that in the near
future we will upgrade our hardware as well, with something like
multiple servers storing mail via NFS to a central RAID system
connected in a SAN. I am aware that maildir would be the only solution
to overcome file-locking issues. And of course a database would achieve
the same...

> I think there's something called dmail or dbmail. I maybe come across one
> or two people every other year or so, who claim to be using it.

I found it, it is at www.dmail.org. The big question for me is, would
the advantage of using such an approach be big enough? Or do I have to
consider just upgrading our mail server components?

(I have no reason to stop using sendmail, but its lack of support for
maildirs is the main reason I will. It is normal for you to suggest
Courier (which I think is great). However I definitely want to keep
using MailScanner, and for that I suppose I have to switch to Postfix
as well). Please, everyone share their suggestions with me.

Re: Mail server with database

am 13.10.2006 13:10:41 von Sam

Re: Mail server with database

am 13.10.2006 15:03:50 von grpprod

> In the last five years I've yet to hear a logical argument why putting a
> mail store into a relational database makes sense.
>
Okay, supposing we forget about this. Would you suggest a system with
Courier and Postfix? Or it would be better to use Courier's SMTP server?

Re: Mail server with database

am 13.10.2006 15:46:57 von grschmidt

Sam wrote:

> grpprod writes:
>
>>> I think there's something called dmail or dbmail. I maybe come
>>> across one
>>> or two people every other year or so, who claim to be using it.
>>
>>
>> I found it, it is at www.dmail.org. The big question for me is, would
>> the advantage of using such an approach be big enough? Or do I have to
>> consider just upgrading our mail server components?
>
>
> In the last five years I've yet to hear a logical argument why putting a
> mail store into a relational database makes sense.
>
Well, does single point of recovery have any weight?

Cheers,
Gary B-)

--
____________________________________________________________ __________________
Armful of chairs: Something some people would not know
whether you were up them with or not
- Barry Humphries

Re: Mail server with database

am 14.10.2006 00:26:10 von Sam

Re: Mail server with database

am 14.10.2006 00:30:33 von Sam

Re: Mail server with database

am 14.10.2006 08:31:25 von grschmidt

Sam wrote:

> Gary R. Schmidt writes:
>
>> Sam wrote:
>>
>>> grpprod writes:
>>>
>>>>> I think there's something called dmail or dbmail. I maybe come
>>>>> across one
>>>>> or two people every other year or so, who claim to be using it.
>>>>
>>>>
>>>>
>>>> I found it, it is at www.dmail.org. The big question for me is, would
>>>> the advantage of using such an approach be big enough? Or do I have to
>>>> consider just upgrading our mail server components?
>>>
>>>
>>>
>>> In the last five years I've yet to hear a logical argument why
>>> putting a mail store into a relational database makes sense.
>>>
>> Well, does single point of recovery have any weight?
>
>
> Of course not, no matter how you define "single point of recovery".
>
> Furthermore, if your database becomes corrupted, your entire mail store
> is screwed. Permanently. Even if only a few hundred bytes on one
> database index page gets screwed up, for whatever reason -- can be a
> disk failure -- you're going to lose your entire mail store. There's
> nothing you can do except to wipe everything clean, and restore the
> whole thing from backups.
>
> On the other hand, with non-DB mail servers its extremely unlikely that
> your entire mail store is going to get hosed. 99 times out of 100 if
> you have the same level of corruption -- a single disk block -- you're
> going to lose one folder. 999 times out of 1000 you may lose an entire
> account. Approximately. All other mail accounts will remain online and
> accessible, while you do whatever needs to do to fix the broken mail
> account.
>
> So tell me again how a database-based mail store has better fault
> tolerance?
Backups.

Cheers,
Gary B-)

--
____________________________________________________________ __________________
Armful of chairs: Something some people would not know
whether you were up them with or not
- Barry Humphries

Re: Mail server with database

am 14.10.2006 14:42:04 von Sam

This is a MIME GnuPG-signed message. If you see this text, it means that
your E-mail or Usenet software does not support MIME signed messages.
The Internet standard for MIME PGP messages, RFC 2015, was published in 1996.
To open this message correctly you will need to install E-mail or Usenet
software that supports modern Internet standards.

--=_mimegpg-commodore.email-scan.com-7870-1160829723-0001
Content-Type: text/plain; format=flowed; charset="US-ASCII"
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

Gary R. Schmidt writes:

> Sam wrote:
>
>> Gary R. Schmidt writes:
>>
>>> Sam wrote:
>>>
>>>> grpprod writes:
>>>>
>>>>>> I think there's something called dmail or dbmail. I maybe come
>>>>>> across one
>>>>>> or two people every other year or so, who claim to be using it.
>>>>>
>>>>>
>>>>>
>>>>> I found it, it is at www.dmail.org. The big question for me is, would
>>>>> the advantage of using such an approach be big enough? Or do I have to
>>>>> consider just upgrading our mail server components?
>>>>
>>>>
>>>>
>>>> In the last five years I've yet to hear a logical argument why
>>>> putting a mail store into a relational database makes sense.
>>>>
>>> Well, does single point of recovery have any weight?
>>
>>
>> Of course not, no matter how you define "single point of recovery".
>>
>> Furthermore, if your database becomes corrupted, your entire mail store
>> is screwed. Permanently. Even if only a few hundred bytes on one
>> database index page gets screwed up, for whatever reason -- can be a
>> disk failure -- you're going to lose your entire mail store. There's
>> nothing you can do except to wipe everything clean, and restore the
>> whole thing from backups.
>>
>> On the other hand, with non-DB mail servers its extremely unlikely that
>> your entire mail store is going to get hosed. 99 times out of 100 if
>> you have the same level of corruption -- a single disk block -- you're
>> going to lose one folder. 999 times out of 1000 you may lose an entire
>> account. Approximately. All other mail accounts will remain online and
>> accessible, while you do whatever needs to do to fix the broken mail
>> account.
>>
>> So tell me again how a database-based mail store has better fault
>> tolerance?
> Backups.

Uh - huh. Your entire database is screwed, you have to reload the entire
database from backup.

A single mail folder gets screwed, you have to reload one folder from
backup.

Which one do you think will be done first -- a 100GB database restore, or a
10KB folder restore?

--=_mimegpg-commodore.email-scan.com-7870-1160829723-0001
Content-Type: application/pgp-signature
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQBFMNsbx9p3GYHlUOIRAl99AJ906DWUC9q/L33Y7ZiQf+wGs19M7ACc C+42
TbCWyjBS4z6gvE/9u8jnQqI=
=lCUN
-----END PGP SIGNATURE-----

--=_mimegpg-commodore.email-scan.com-7870-1160829723-0001--

Re: Mail server with database

am 14.10.2006 15:54:53 von grschmidt

Sam wrote:

> Gary R. Schmidt writes:
>
>> Sam wrote:
>>
>>> Gary R. Schmidt writes:
>>>
>>>> Sam wrote:
>>>>
>>>>> grpprod writes:
>>>>>
>>>>>>> I think there's something called dmail or dbmail. I maybe come
>>>>>>> across one
>>>>>>> or two people every other year or so, who claim to be using it.
>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> I found it, it is at www.dmail.org. The big question for me is, would
>>>>>> the advantage of using such an approach be big enough? Or do I
>>>>>> have to
>>>>>> consider just upgrading our mail server components?
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> In the last five years I've yet to hear a logical argument why
>>>>> putting a mail store into a relational database makes sense.
>>>>>
>>>> Well, does single point of recovery have any weight?
>>>
>>>
>>>
>>> Of course not, no matter how you define "single point of recovery".
>>>
>>> Furthermore, if your database becomes corrupted, your entire mail
>>> store is screwed. Permanently. Even if only a few hundred bytes on
>>> one database index page gets screwed up, for whatever reason -- can
>>> be a disk failure -- you're going to lose your entire mail store.
>>> There's nothing you can do except to wipe everything clean, and
>>> restore the whole thing from backups.
>>>
>>> On the other hand, with non-DB mail servers its extremely unlikely
>>> that your entire mail store is going to get hosed. 99 times out of
>>> 100 if you have the same level of corruption -- a single disk block
>>> -- you're going to lose one folder. 999 times out of 1000 you may
>>> lose an entire account. Approximately. All other mail accounts will
>>> remain online and accessible, while you do whatever needs to do to
>>> fix the broken mail account.
>>>
>>> So tell me again how a database-based mail store has better fault
>>> tolerance?
>>
>> Backups.
>
>
> Uh - huh. Your entire database is screwed, you have to reload the
> entire database from backup.
>
> A single mail folder gets screwed, you have to reload one folder from
> backup.
>
> Which one do you think will be done first -- a 100GB database restore,
> or a 10KB folder restore?
>
Oh, it's not *restoring* the database, it's *backing* *up* the 10
gazillion possible mailbox directories and files that get skurfed around
the users home.

Not a problem if you have the resources and *time* to back up each users
home in toto, but as most large sites don't, it's easier to save off a
single entity.

And I would assume that such a database would be implemented such that
it should not have to be restored en masse. Partitioning on a user
basis would, I assume, be the default. (But maybe I'm wearing my
rose-coloured glasses?!?! Could it be that the designers who have
created email systems with database back-ends haven't realised this?
Crivens, perhaps they *aren't* all geniuses! :-))

Oh, and a 100GB database restore is kind of small these days, terabyte
databases are quite common.

Cheers,
Gary B-)

--
____________________________________________________________ __________________
Armful of chairs: Something some people would not know
whether you were up them with or not
- Barry Humphries

Re: Mail server with database

am 14.10.2006 17:33:34 von DFS

Gary R. Schmidt wrote:

> Oh, it's not *restoring* the database, it's *backing* *up* the 10
> gazillion possible mailbox directories and files that get skurfed around
> the users home.

If you use a decent mail server and supporting infrastructure, that's
a non-issue. For example, we use Dovecot, and to back up everyone's
mail, I just have to back up /var/mail and /var/imap. That's it.

I'm unconvinced that a database-backed mail store solves any
real-world problems (and I'm pretty convinced that it will *create*
lots of real-world problems.)

--
David.

Re: Mail server with database

am 14.10.2006 17:39:57 von DFS

grpprod wrote:

> (I have no reason to stop using sendmail, but its lack of support for
> maildirs is the main reason I will.

Sendmail doesn't support (or not) maildirs. That's the job of the
LDA.

I find Dovecot to be pretty good (it suppords maildirs, though we're
small enough to use old-style mbox storage), and if you want huge
scalability, Cyrus IMAP can distribute your mailboxes across a cluster
of back-end machines.

Regards,

David.

Re: Mail server with database

am 14.10.2006 18:22:13 von Sam

This is a MIME GnuPG-signed message. If you see this text, it means that
your E-mail or Usenet software does not support MIME signed messages.
The Internet standard for MIME PGP messages, RFC 2015, was published in 1996.
To open this message correctly you will need to install E-mail or Usenet
software that supports modern Internet standards.

--=_mimegpg-commodore.email-scan.com-4454-1160842932-0001
Content-Type: text/plain; format=flowed; charset="US-ASCII"
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

Gary R. Schmidt writes:

> Sam wrote:
>
>> Which one do you think will be done first -- a 100GB database restore,
>> or a 10KB folder restore?
>>
> Oh, it's not *restoring* the database, it's *backing* *up* the 10
> gazillion possible mailbox directories and files that get skurfed around
> the users home.

So? It's not like you have to type them up by hand, individually. tar, or
rsync, and/or a simple shell script won't mind.

> And I would assume that such a database would be implemented such that
> it should not have to be restored en masse.

Oh? Point me to any commercial database that lets you restore a single
table, or -- as this case would be -- a /subset/ of an individual table's
data.

> Partitioning on a user
> basis would, I assume, be the default. (But maybe I'm wearing my
> rose-coloured glasses?!?!

The problem with that is that with commercial databases, each partition
immediately allocates its set storage. Anyone who runs a large mail store
will tell you that with a 100gb quota you will not have every account taking
up its maximum space. Nobody in their right mind provisions a mail store by
taking # of accounts multiplied by the maximum mailbox size.

Even the few DB-based mail stores in existence don't do that.

> Could it be that the designers who have
> created email systems with database back-ends haven't realised this?
> Crivens, perhaps they *aren't* all geniuses! :-))

The designers who designed email systems with database back-ends were mostly
designing a solution in search of a problem.

Or working around a technically-defective operating system's filesystem
limitations.

> Oh, and a 100GB database restore is kind of small these days, terabyte
> databases are quite common.

Right. Go up to an Exchange admin, and ask him what he would do if one
account in a terabyte Exchange database got screwed up so that the whole
Exchange server bluescreens every time the user logs in. The possible
answers are:

1) Restore the account's data from backups

2) Restore the entire terabyte Exchange DB from backups

I'm anxiously awaiting for an answer.

We don't even have to consider a situation that involves a storage failure.
Suppose the CEO walks in and tells you that he accidentally deleted a folder
with all of his por^H^H^Himportant business data, and he wants you to
restore the single folder.

Now, the last time this came up, I was told that the only thing your poor
Exchange admin could do is to restore the entire backed-up Exchange database
on another server, and pull out the individual folder. That was the answer
a few years ago. I'm willing to consider the possibility that this is no
longer the case, but I'm skeptical.

The equivalent answer, in the alternative, would usually be nothing more
than "tar xzf /dev/st0 var/mail/t/th/theboss/Maildir/.hornymidgets", and
walk away as the front-end servers continue to do their business while tar
finds the folder on the tape, and restores the single mail folder.

--=_mimegpg-commodore.email-scan.com-4454-1160842932-0001
Content-Type: application/pgp-signature
Content-Transfer-Encoding: 7bit

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.5 (GNU/Linux)

iD8DBQBFMQ60x9p3GYHlUOIRAlH/AJ9ufSybT/AqsZ6MYjdaZRIeBieQnQCf WJkd
hofPpWfize0xTpPGnKSGQ1k=
=PXG4
-----END PGP SIGNATURE-----

--=_mimegpg-commodore.email-scan.com-4454-1160842932-0001--

Re: Mail server with database

am 16.10.2006 13:47:40 von Peter Peters

On Fri, 13 Oct 2006 06:10:41 -0500, Sam wrote:

>grpprod writes:
>
>>> I think there's something called dmail or dbmail. I maybe come across one
>>> or two people every other year or so, who claim to be using it.
>>
>> I found it, it is at www.dmail.org. The big question for me is, would
>> the advantage of using such an approach be big enough? Or do I have to
>> consider just upgrading our mail server components?
>
>In the last five years I've yet to hear a logical argument why putting a
>mail store into a relational database makes sense.

Whether it is a logical argument or not I have heard it being
investigated by an ISP that (still) supports UUCP next to mailboxes and
bSMTP.

Sometimes customers want to use UUCP and sometimes bSMTP. In other
situations they might want to get mail send to specific addresses in
their domain before UUCP gets the rest.

--
Peter Peters, senior netwerkbeheerder
Dienst Informatietechnologie, Bibliotheek en Educatie (ITBE)
Universiteit Twente, Postbus 217, 7500 AE Enschede
telefoon: 053 - 489 2301, fax: 053 - 489 2383, http://www.utwente.nl/itbe

Re: Mail server with database

am 17.10.2006 00:27:23 von Sam

Re: Mail server with database

am 17.10.2006 11:14:55 von Peter Peters

On Mon, 16 Oct 2006 17:27:23 -0500, Sam wrote:

>> Sometimes customers want to use UUCP and sometimes bSMTP. In other
>> situations they might want to get mail send to specific addresses in
>> their domain before UUCP gets the rest.
>
>And how exactly does that translate into storing the contents of E-mail
>messages in relational databases?

The idea was to store all messages in a database and delivering the
messages in the format needed for the transport asked for by the client.
So extract all messages for a specific user if that user makes an IMAP
or POP3 connection and extract all messages for a specific domain when
the server for that domain makes an UUCP or bSMTP connection.

--
Peter Peters, senior netwerkbeheerder
Dienst Informatietechnologie, Bibliotheek en Educatie (ITBE)
Universiteit Twente, Postbus 217, 7500 AE Enschede
telefoon: 053 - 489 2301, fax: 053 - 489 2383, http://www.utwente.nl/itbe

Re: Mail server with database

am 17.10.2006 13:05:07 von Sam

Re: Mail server with database

am 17.10.2006 18:01:51 von Mark Crispin

On Tue, 17 Oct 2006, Peter Peters wrote:
> The idea was to store all messages in a database and delivering the
> messages in the format needed for the transport asked for by the client.

The problem with that idea is that the cost of the transformations becomes
quite high.

Microsoft did this with Exchange, and quickly ran up against a big
problem; the POP3 and especially IMAP protocols require the transmission
of *sizes* of message data (and in the case of IMAP, parts of message
data). They assumed that the sizes were just advisory, and did the
obvious thing and estimated those sizes rather than calculate the exact
values (which would require generating the associated message part from
the database).

The problem is, IMAP requires that the sizes be exact, and if you think
about how partial fetching works in IMAP there really is no way around
that requirement. So they advised customers to switch on the option to
calculate the exact values, which substantially slowed down Exchange.

In the end, they fixed the problem by storing the message in Internet (RFC
[2]822) format, thus having a form where all those sizes were readily
available.

The same fate awaits any other mail store database which is incautious
about the requirements of whatever is going to access that database.

Although I disagree with the claims that it can't/shouldn't be done; it is
a daunting task to get it right, especially if you hope to support access
other than Internet. IMAP once paid lip service to X.400 interoperability
but eventually gave up the pretense when it became clear that it had no
such thing.

I agree that a general purpose database (such as MySQL) probably can not
do a good job of being a mail store. Any database chosen has to be
specifically designed for mail and the requiresments of the access means.
Once you go that far, you have to do some serious cost/benefit comparison
with existing mail stores.

-- Mark --

http://panda.com/mrc
Democracy is two wolves and a sheep deciding what to eat for lunch.
Liberty is a well-armed sheep contesting the vote.

Re: Mail server with database

am 18.10.2006 08:55:33 von Peter Peters

On Tue, 17 Oct 2006 09:01:51 -0700, Mark Crispin
wrote:

>On Tue, 17 Oct 2006, Peter Peters wrote:
>> The idea was to store all messages in a database and delivering the
>> messages in the format needed for the transport asked for by the client.
>
>The problem with that idea is that the cost of the transformations becomes
>quite high.

That is one of reasons the project never started.

>In the end, they fixed the problem by storing the message in Internet (RFC
>[2]822) format, thus having a form where all those sizes were readily
>available.

What they think is RFC[2]822 format. Or what they think was the original
message.

Because when they save the message as a RFC-message why is it changed
when you forward that message as an attachment. A number of the
"internet headers" as they call it, are gone.

>I agree that a general purpose database (such as MySQL) probably can not
>do a good job of being a mail store. Any database chosen has to be
>specifically designed for mail and the requiresments of the access means.
>Once you go that far, you have to do some serious cost/benefit comparison
>with existing mail stores.

It ofcourse also depends on the database engine. MySQL is merely a
frontend to a number of database engines.

--
Peter Peters, senior netwerkbeheerder
Dienst Informatietechnologie, Bibliotheek en Educatie (ITBE)
Universiteit Twente, Postbus 217, 7500 AE Enschede
telefoon: 053 - 489 2301, fax: 053 - 489 2383, http://www.utwente.nl/itbe

Re: Mail server with database

am 18.10.2006 18:19:50 von Mark Crispin

On Wed, 18 Oct 2006, Peter Peters wrote:
> Because when they save the message as a RFC-message why is it changed
> when you forward that message as an attachment. A number of the
> "internet headers" as they call it, are gone.

That sounds like a client issue rather than a server issue.

Sadly, there are people in the world who think that Outlook is the world's
best email client.

-- Mark --

http://panda.com/mrc
Democracy is two wolves and a sheep deciding what to eat for lunch.
Liberty is a well-armed sheep contesting the vote.

Re: Mail server with database

am 18.10.2006 23:40:55 von Sam

Re: Mail server with database

am 19.10.2006 00:08:03 von Mark Crispin

On Wed, 18 Oct 2006, Sam wrote:
>> Sadly, there are people in the world who think that Outlook is the world's
>> best email client.
> Outlook is not an E-mail client. It is a groupware client. A part of that
> involves E-mail, but that's not the whole story.

True. However, I suspect that the overwhelming majority of Outlook users
only use it as an email client. And, as an email client, it's wretchedly
bad.

It's not bad for calender, task, and contact management though.

> There is no equivalent free software groupware client, so Outlook wins by
> default.

Also true.

HUGE sigh here. Chandler still isn't ready. In the meantime, Outlook is
making inroads that aren't going to be easily unrooted.

If the free software community spent half the time writing code that they
spend on fighting ridiculous ideological "free as in freedom" vs. "free as
in free beer" wars, we wouldn't be in this situation.

The latest issue of Forbes has an article that basically says that RMS is
out to destroy Linux, so you had better stay with Microsoft and other
proprietary solutions. The article is a lot of FUD, but there's truth in
it.

-- Mark --

http://panda.com/mrc
Democracy is two wolves and a sheep deciding what to eat for lunch.
Liberty is a well-armed sheep contesting the vote.

Re: Mail server with database

am 19.10.2006 10:00:38 von Peter Peters

On Wed, 18 Oct 2006 09:19:50 -0700, Mark Crispin
wrote:

>On Wed, 18 Oct 2006, Peter Peters wrote:
>> Because when they save the message as a RFC-message why is it changed
>> when you forward that message as an attachment. A number of the
>> "internet headers" as they call it, are gone.
>
>That sounds like a client issue rather than a server issue.

No, it is not. At least when you view the attachment on the recipient
side you'll notice a header claiming Exchange has (re)build the
attachment.

--
Peter Peters, senior netwerkbeheerder
Dienst Informatietechnologie, Bibliotheek en Educatie (ITBE)
Universiteit Twente, Postbus 217, 7500 AE Enschede
telefoon: 053 - 489 2301, fax: 053 - 489 2383, http://www.utwente.nl/itbe