So Timo, Have you considered the idea of storing all the email in a MySQL database? Seems to me that MySQL could somplify all the backend stuff that everyone struggles with and with replication one could create very massive and reliable systems. What would it take to use MySQL that way?
On Tue, Jun 06, 2006 at 05:38:41AM -0700, Marc Perkel wrote:> So Timo, > > Have you considered the idea of storing all the email in a MySQL > database? Seems to me that MySQL could somplify all the backend stuff > that everyone struggles with and with replication one could create very > massive and reliable systems. What would it take to use MySQL that way?PowerMail does that, but unlike PowerDNS there doesn't seem to be loads of users / testimonials / active mailing lists. It's open source though, so you can download and try it out :-) PowerDNS by the same author is definitely best-of-breed, a must-have if you'd like your DNS in SQL. http://www.powerdns.com/en/products.aspx
* On 06/06/06 05:38 -0700, Marc Perkel wrote:
| So Timo,
|
| Have you considered the idea of storing all the email in a MySQL
| database? Seems to me that MySQL could somplify all the backend stuff
| that everyone struggles with and with replication one could create very
| massive and reliable systems. What would it take to use MySQL that way?
Hi Marc,
Did I miss something? I thought the mail is delivered by another daemon
different than dovecot ;)
-Wash
http://www.netmeister.org/news/learn2quote.html
DISCLAIMER: See http://www.wananchi.com/bms/terms.php
--
+======================================================================+
|\ _,,,---,,_ | Odhiambo Washington <wash@wananchi.com>
Zzz /,`.-'`' -. ;-;;,_ | Wananchi Online Ltd. www.wananchi.com
|,4- ) )-,_. ,\ ( `'-'| Tel: +254 20 313985-9 +254 20 313922
'---''(_/--' `-'\_) | GSM: +254 722 743223 +254 733
744121
+======================================================================+
Do what comes naturally now. Seethe and fume and throw a tantrum.
> So Timo, > > Have you considered the idea of storing all the email in a MySQL > database?dovecot already writes to a storage backend optimised for this task - the filesystem.> Seems to me that MySQL could somplify all the backend stuff > that everyone strugglesWho is "everyone"? What problems are you having?> with and with replication one could create very > massive and reliable systems. What would it take to use MySQL that way?Massive and reliable? What examples can you give where a MySQL backend for a mail system would improve things? How would it improve them? Putting everything in a database would provide one benefit: * Less storage space needed due to duplicated e-mail NOT putting everything in a database provides plays to Linux's strengths: everything is a file, meaning we can use all of the standard file-focused text processing tools. If everything is a file, backups and restores are a piece of cake.
> On 06-06-2006 15:48:20 +0200, nodata wrote: >> Putting everything in a database would provide one benefit: >> * Less storage space needed due to duplicated e-mail > > ... assuming that the mails are completely identical (i.e. have noslightly different headers) and that the database backend does expensive duplicate elimination on its string heaps. Yes, like Exchange does (unless you did something "clever" with the headers).> So the real benefit you get I think is: > * full text searching capabilities based on the IR-like indices > provided/maintained by the database backend, such as for instance:http://dev.mysql.com/doc/refman/5.0/en/fulltext-query-expansion.html If the original poster if struggling with a filesystem version of dovecot, and wants to create "massive and reliable systems" using MySQL, then he's going to have trouble. Adding MySQL won't do that.
Marc Perkel trolled:> So Timo, > > Have you considered the idea of storing all the email in a MySQL > database? Seems to me that MySQL could somplify all the backend stuff > that everyone struggles with and with replication one could createvery> massive and reliable systems. What would it take to use MySQL thatway? Maybe we could recode Dovecot in PHP, too. Sorry, that was nasty. However, having recently experimented with a very large mail storage system using mysql, I strongly advise against this avenue. In particular, the two main backends have serious deficiencies when you get up to the multi-gigabyte ranges: * MyISAM doesn't restart in constant time. * InnoDB's next-key locking kills performance on large blob inserts & deletes. Anyway, what do you mean, everyone struggles with backends? Dovecot's backends are fast, modular and reliable. Regarding replication; all good logical volume managers can do this. Regarding massive; all good filesystems can do this. Regarding reliable; I guarantee that mysql reliability (when properly managed) is still lower than that of a filesystem (when properly managed). In fact, the whole argument is specious. Filesystems already are databases, optimised for storing arbitrary-sized data objects using a hierarchical key, which is practically the definition of mail storage. /jg
Marc Perkel:> Suppose I want to move all message from a specific host to anotherfolder.> UPDATE table SET folder=new-folder WHERE host=value; > > With databases you can do things you would have to write complexprograms> to do with files. Things that you would consider to be impossible are > trivial with databases. > > This outside the box.People who optimise their systems for shiny cute features rather than the common case get all the hacks they deserve. Repeat after me: design for the common case. Also your proposed sql above and implications (arbitrary operations based on mail metadata i.e. headers) seems to involve indexing every mail header on delivery. Good luck with that one large scale mail storage. Did you want a mail database, or an OLAP system? >-) /jg
On Tue, 2006-06-06 at 05:38 -0700, Marc Perkel wrote:> Have you considered the idea of storing all the email in a MySQL > database? Seems to me that MySQL could somplify all the backend stuff > that everyone struggles with and with replication one could create very > massive and reliable systems. What would it take to use MySQL that way?I'm not against Dovecot supporting SQL backend. I've even started coding it a couple of times. But I don't think it's really all that great idea, at least from performance point of view. Dovecot's current indexes are highly optimized for what IMAP server needs to do, and SQL just doesn't support many such features in any efficient way. As for faster searching, the problem with that is still the same as I mentioned a month or so ago: A fully compliant IMAP server can't use any normal full text search indexers to implement SEARCH command. I'm still going to add some search extension which does allow doing this and Dovecot will have fast full text search support, without requiring a SQL database. An SQL database might be a good idea if your client (eg. webmail) used it directly and the database's schema was specifically designed for the client. But for IMAP server which needs to be able to do many things and restricted by the way the IMAP RFC requires things to be done, SQL just sucks. Also I think others already mentioned that MySQL's replication allows only read-only slaves, which I don't think helps all that much. As for multi-master replication, what databases really support that? I think with Oracle it supports it only when the filesystem itself is already shared between the computers, and Dovecot already supports running in shared filesystems, so Oracle would be just extra overhead there. -------------- next part -------------- A non-text attachment was scrubbed... Name: not available Type: application/pgp-signature Size: 189 bytes Desc: This is a digitally signed message part Url : http://dovecot.org/pipermail/dovecot/attachments/20060607/4a0efae5/attachment.pgp
Marc Perkel scribbled:> Repeat after me - think outside the box.Did you miss the part where I said we've already tried this? Not only have we thought outside the box, but we've smashed it up and built several alternatives to see which one sucked the least. Turns out, SQL backends suck the most. Please try to keep up :) Marc Perkel also scribbled:> To do this with MySQL would be trivial. To do it without a database > isn't possible.Actually you could do that with a tiny Sieve extension. I don't think you really understand what you are even proposing. Your statement would be better phrased "to do it without an index would be slow". If you wanted to create a database server for mail, with a mail-specific query language, then that database might be called "Dovecot", and the query language might be called "IMAP". Innovation is not an excuse for lousy engineering. As usual, it turns out to be bad design practice to say "let's deploy shinytoy for feature X!" rather than say "we need capability X! What's a creative way to achieve that?". Which is why necessity is the mother of invention. /JG