[Dovecot] MySQL as a storage only.?
Hello,
I was wondering is I could use MySQL as storage only..? Meaning that nouser information, other than the obvious email address associated with an specific email so that each email can be showed to the right user, will be stored in a MySQL database instead of /home/vmail/DOMAIN/<user>
Would I get any advantages.? Would it be better, faster?Thanks
On Wed, Dec 10, 2008 at 02:24:05PM -0600, Romer Ventura wrote:
Hello,
I was wondering is I could use MySQL as storage only..? Meaning that nouser information, other than the obvious email address associated with an specific email so that each email can be showed to the right user, will be stored in a MySQL database instead of /home/vmail/DOMAIN/<user>
Would I get any advantages.? Would it be better, faster?
You may want to have a look at dbmail for this: http://www.dbmail.org/ AFAIK it's currently not in scope for Dovecot.
GeertRomer Ventura wrote:
Hello,
I was wondering is I could use MySQL as storage only..? Meaning that nouser information, other than the obvious email address associated with an specific email so that each email can be showed to the right user, will be stored in a MySQL database instead of /home/vmail/DOMAIN/<user>
Would I get any advantages.? Would it be better, faster?Thanks
I think IF it would be supported in a future version it would be a great addition. Especially if you try to implement cloud-like services, where you have the possibility of links temporarily going down between servers, and mail can come in to any point, and be retrieved or moved at any point.
You really need transactions then, to track every mails change in time, and to replicate those when you get connectivity back. You "can" possibly do it by tracking dovecot logs and do the replication yourself with scripts, but using a database would probably be easier here.
Also I think it might be good if you use imap flags a lot, I see a definitive benefit if dovecot could use sql as an index storage anyway.
For speed just as a plain storage, it would most likely be a bit slower...
So, for some it might be better, for others it would not...
-Roger
On Dec 23, 2008, at 4:51 AM, R A wrote:
Romer Ventura wrote:
Hello,
I was wondering is I could use MySQL as storage only..? Meaning
that no user information, other than the obvious email address associated
with an specific email so that each email can be showed to the right user,
will be stored in a MySQL database instead of /home/vmail/DOMAIN/<user>Would I get any advantages.? Would it be better, faster?
Thanks
I think IF it would be supported in a future version it would be a
great addition.
I did already start it once but didn't get it very well working. It's
a very low priority for me to finish it. Maybe once everything else is
done and I can't really think of anything better to do.. Of course
anyone else is free to implement it. http://dovecot.org/list/dovecot/2007-November/026632.html
Especially if you try to implement cloud-like services, where you have the possibility of links temporarily going down between servers, and mail can come in to any point, and be retrieved or moved at any point.
You really need transactions then, to track every mails change in
time, and to replicate those when you get connectivity back. You "can" possibly do it by tracking dovecot logs and do the replication
yourself with scripts, but using a database would probably be easier here.
I've also planned easy replication support for Dovecot. Also I don't
think doing the SQL replication correctly and without losing any data
on error conditions is as easy as you think.
On Tue, Dec 23, 2008 at 2:20 AM, Timo Sirainen tss@iki.fi wrote:
On Dec 23, 2008, at 4:51 AM, R A wrote:
Especially if you try to implement cloud-like services, where you have the possibility of links temporarily going down between servers, and mail can come in to any point, and be retrieved or moved at any point.
You really need transactions then, to track every mails change in time, and to replicate those when you get connectivity back. You "can" possibly do it by tracking dovecot logs and do the replication yourself with scripts, but using a database would probably be easier here.
I've also planned easy replication support for Dovecot. Also I don't think doing the SQL replication correctly and without losing any data on error conditions is as easy as you think.
+1 Needless to say, replication would be _very_ useful...
Neil wrote:
On Tue, Dec 23, 2008 at 2:20 AM, Timo Sirainen tss@iki.fi wrote:
On Dec 23, 2008, at 4:51 AM, R A wrote:
Especially if you try to implement cloud-like services, where you have the possibility of links temporarily going down between servers, and mail can come in to any point, and be retrieved or moved at any point.
You really need transactions then, to track every mails change in time, and to replicate those when you get connectivity back. You "can" possibly do it by tracking dovecot logs and do the replication yourself with scripts, but using a database would probably be easier here. I've also planned easy replication support for Dovecot. Also I don't think doing the SQL replication correctly and without losing any data on error conditions is as easy as you think.
+1 Needless to say, replication would be _very_ useful...
At least in 5, MySQL replication is very difficult. Based on my experience with amavisd, master <-> master replication does not work if you have foreign key constraints. master <-> slaves can have issues with high activity as a key on the master might get update while a search is happening on the slave (again this is with foreign key constraints). Then you'll probably need innodb for performance so backups become more challenging. Lastly, disk usage triples.
Hate to be a wet blanket, but this is what I've seen. If you don't need the constraints, the problem becomes more manageable. Still your safest bet for replication at this time is to use the slave as a backup with some sort of auto promotion mechanism. The network master daemon (nmdb?) looks promising for straight forward databases.
Don't forget blob sizing.
I'm not sure how Oracle is for replication. Setups/configuration does not sound simple from the dba's I've talked to, although RACK looks decent.
MySQL does work well for index problems (i.e. searches) where the index can be reconstructed if their is a failure and the searching process doesn't seize in a failure.
Just my opinion and warning.
---Jack
Jack Stewart wrote:
Neil wrote:
On Tue, Dec 23, 2008 at 2:20 AM, Timo Sirainen tss@iki.fi wrote:
Especially if you try to implement cloud-like services, where you have the possibility of links temporarily going down between servers, and mail can come in to any point, and be retrieved or moved at any point.
You really need transactions then, to track every mails change in time, and to replicate those when you get connectivity back. You "can" possibly do it by tracking dovecot logs and do the replication yourself with scripts, but using a database would probably be easier here. I've also planned easy replication support for Dovecot. Also I don't
On Dec 23, 2008, at 4:51 AM, R A wrote: think doing the SQL replication correctly and without losing any data on error conditions is as easy as you think.
+1 Needless to say, replication would be _very_ useful...
At least in 5, MySQL replication is very difficult. Based on my experience with amavisd, master <-> master replication does not work if you have foreign key constraints. master <-> slaves can have issues with high activity as a key on the master might get update while a search is happening on the slave (again this is with foreign key constraints). Then you'll probably need innodb for performance so backups become more challenging. Lastly, disk usage triples.
Hate to be a wet blanket, but this is what I've seen. If you don't need the constraints, the problem becomes more manageable. Still your safest bet for replication at this time is to use the slave as a backup with some sort of auto promotion mechanism. The network master daemon (nmdb?) looks promising for straight forward databases.
Don't forget blob sizing.
I'm not sure how Oracle is for replication. Setups/configuration does not sound simple from the dba's I've talked to, although RACK looks decent.
MySQL does work well for index problems (i.e. searches) where the index can be reconstructed if their is a failure and the searching process doesn't seize in a failure.
Just my opinion and warning.
I agree with you from my own experience - using a database in this manner is a huge kludge. maildir/dbox is far better suited for mail storage. For small installations where there is little mail storage and performance is not an issue, maybe. But I'd rather see Timo's time spent on an internal to dovecot master-slave and master-master system.
~Seth
Timo Sirainen wrote:
I've also planned easy replication support for Dovecot. Also I don't think doing the SQL replication correctly and without losing any data on error conditions is as easy as you think.
Multiple master in MySQL is still more of a hack. Also, performance for billion-row SQL tables is rather poor for something interactive like clicking on a message in IMAP and expecting it to pop up right away.
I'm looking forward to master-master replication. Not SQL though; I can't find the link now but I remember reading about a master-master system that Dovecot would use to sync maildir/dbox over N masters or something. A pool of beefy master-master Dovecots with a load balancer in front of them would be wonderful - I'm not too keen on the "user a goes on server a, user b on server b" system. I'd love to help out but my own development schedule is beyond full at the moment. =)
~Seth
Seth Mattinen wrote:
Multiple master in MySQL is still more of a hack. Also, performance for billion-row SQL tables is rather poor for something interactive like clicking on a message in IMAP and expecting it to pop up right away.
I'm looking forward to master-master replication. Not SQL though; I can't find the link now but I remember reading about a master-master system that Dovecot would use to sync maildir/dbox over N masters or something. A pool of beefy master-master Dovecots with a load balancer in front of them would be wonderful - I'm not too keen on the "user a goes on server a, user b on server b" system. I'd love to help out but my own development schedule is beyond full at the moment. =)
~Seth
Well, what I would like to see is multiple servers, where mail can come in simultaniously for user a at both server a,b and c. But a user is only connected to one server at a time, although it can be any of them. DNS would solve this as they all have the name and mx value. The MTA issue is trivial to sync mail between them (as long as sieve filters isnt used or updated anyway and that has not propagated) but IMAP and POP3 would be a bit trickier. No load balancer as say you have one server on one isp in canada, one in australia, and one in germany. Say the link suddenly go down to the server in australia for an hour, once it comes up you want them to resync automatically.
Basically your not to keen scenario, but worse, as b can go on a, and then next time on b. Which means that a user can do things like delete a file twice, move it to two different locations etc. All you have to go on would be timestamps to resolve conflicts, so the servers pretty much would have to use ntp for this to work.
-Roger
R A wrote:
Seth Mattinen wrote:
Multiple master in MySQL is still more of a hack. Also, performance for billion-row SQL tables is rather poor for something interactive like clicking on a message in IMAP and expecting it to pop up right away.
I'm looking forward to master-master replication. Not SQL though; I can't find the link now but I remember reading about a master-master system that Dovecot would use to sync maildir/dbox over N masters or something. A pool of beefy master-master Dovecots with a load balancer in front of them would be wonderful - I'm not too keen on the "user a goes on server a, user b on server b" system. I'd love to help out but my own development schedule is beyond full at the moment. =)
~Seth
Well, what I would like to see is multiple servers, where mail can come in simultaniously for user a at both server a,b and c. But a user is only connected to one server at a time, although it can be any of them. DNS would solve this as they all have the name and mx value. The MTA issue is trivial to sync mail between them (as long as sieve filters isnt used or updated anyway and that has not propagated) but IMAP and POP3 would be a bit trickier. No load balancer as say you have one server on one isp in canada, one in australia, and one in germany. Say the link suddenly go down to the server in australia for an hour, once it comes up you want them to resync automatically.
Basically your not to keen scenario, but worse, as b can go on a, and then next time on b. Which means that a user can do things like delete a file twice, move it to two different locations etc. All you have to go on would be timestamps to resolve conflicts, so the servers pretty much would have to use ntp for this to work.
I found the message: [Dovecot] Replication milestone 1 http://www.dovecot.org/list/dovecot/2008-May/030446.html
This is something I can certainly provide infrastructure for (load balancer plus 3 or 4 servers) and possibly throw my hat in as a sponsor. But I have no idea how much progress Timo has made on this.
~Seth
participants (7)
-
Geert Hendrickx
-
Jack Stewart
-
Neil
-
R A
-
Romer Ventura
-
Seth Mattinen
-
Timo Sirainen