Re: [Dbmail] DB replication patch
Hi Klas, we are currently looking out for some kind of replication - your way of doing it is interesting. However I would prefer a system where the replication is completely transparent for dbmail mainly because of postgresql/other database support: one of the main objectives of dbmail is complete freedom for database choice as fas as dbmail is concerned. Implementing this patch would break the symmetry giving extra functionality only available with mysql. Furthermore could you be more specific on your plan of recovery if one of the machines crashes? Seems to me that you would have to rebuild your dbmail system setting it up for 1 database. Our plan for the moment is to have two databases: one used in production and the other as hot spare. When the live machine is used, the succesfull update/insert/delete queries are performed at the hot spare as well (by means of some script running in the background). At failure, all there is to do is change dbmail.conf and send a SIGHUP to the daemons. This system has one major drawback: if the update/insert/delete query fails on the hot spare but already has succeeded on the production machine. We haven't figured this out completely but then again: if the query fails probably something's really wrong with the hot spare. What do you folks think about it? Klas Handel heeft op vrijdag, 6 dec 2002 om 00:55 (Europe/Amsterdam) het volgende geschreven: Hi, We're using dbmail together with MySQL. Since our database is quite large and we have a lot of users we want to spread our load a little bit. We have setup our database in replication (master/slave) mode. Then what we want to do is force all read (select) activities to our slave (slaves) and all write (delete, update and insert) to our master database. I've created a patch which does this (only MySQL). What you need to do is #define the READ_DATABASE parameter in "db.h". Then there are some addition parameters to be set in "dbmail.conf" (read_db, read_user etc.). If READ_DATABASE isn't defined dbmail should work as normal, i.e use only one database. I did create a patch earlier and sent it to the list, but it's not in the new release so it's probably nothing that people are interested in? Still we are using it so I send it again... (The patch version is the cvs from 2002-12-05) Have a nice day! /Klas [EMAIL PROTECTED]
Re: [Dbmail] DB replication patch
On Fri, 2002-12-06 at 03:31, Roel Rozendaal - IC&S wrote: > Hi Klas, > > Our plan for the moment is to have two databases: one used in > production and the other as hot spare. When the live machine is used, > the succesfull update/insert/delete queries are performed at the hot > spare as well (by means of some script running in the background). At > failure, all there is to do is change dbmail.conf and send a SIGHUP to > the daemons. This system has one major drawback: if the > update/insert/delete query fails on the hot spare but already has > succeeded on the production machine. We haven't figured this out > completely but then again: if the query fails probably something's > really wrong with the hot spare. What do you folks think about it? > Well, for innodb or bdb backed mysql and postgres, you could do it with transactions theoretically, transaction the update/insert/delete statements on the master, then do them on the hot spare, if it fails, roll back, if not, commit. Performance would be the question here. For MyISAM users, another method would have to be devised. -- Ryan Butler <[EMAIL PROTECTED]> ADI Internet Solutions
Re: [Dbmail] DB replication patch
Hi, I must say that Klas's approach seems to be the best to me. MySQL already has very acceptable support for replication and it would be foolish to rely on a script based solution that will never work half as well. PostgreSQL also has one or two replication implementations/projects if I am not mistaken. From the DBmail point of view, the only thing that changes is that we use one connection for READ operations and another for WRITE operations, this could be implemented for each db backend if necessary.. Only queries that succeed on the master server are ran on the slave. If a query that succeeded on the master fail on the slave, replication will be halted until the problem is manually resolved I think. Cedric Le 6 Décembre 2002 04:31, Roel Rozendaal - IC&S a écrit : > Hi Klas, > > we are currently looking out for some kind of replication - your way of > doing it is interesting. However I would prefer a system where the > replication is completely transparent for dbmail mainly because of > postgresql/other database support: one of the main objectives of dbmail > is complete freedom for database choice as fas as dbmail is concerned. > Implementing this patch would break the symmetry giving extra > functionality only available with mysql. > Furthermore could you be more specific on your plan of recovery if one > of the machines crashes? Seems to me that you would have to rebuild > your dbmail system setting it up for 1 database. > > Our plan for the moment is to have two databases: one used in > production and the other as hot spare. When the live machine is used, > the succesfull update/insert/delete queries are performed at the hot > spare as well (by means of some script running in the background). At > failure, all there is to do is change dbmail.conf and send a SIGHUP to > the daemons. This system has one major drawback: if the > update/insert/delete query fails on the hot spare but already has > succeeded on the production machine. We haven't figured this out > completely but then again: if the query fails probably something's > really wrong with the hot spare. What do you folks think about it? > > > Klas Handel heeft op vrijdag, 6 dec 2002 om 00:55 (Europe/Amsterdam) > > het volgende geschreven: > > Hi, > > > > We're using dbmail together with MySQL. Since our database is quite > > large and we have a lot of users we want to spread our load a little > > bit. We have setup our database in replication (master/slave) mode. > > Then what we want to do is force all read (select) activities to > > our slave (slaves) and all write (delete, update and insert) to our > > master database. > > I've created a patch which does this (only MySQL). What you need to do > > is #define the READ_DATABASE parameter in "db.h". Then there are some > > addition parameters to be set in "dbmail.conf" (read_db, read_user > > etc.). > > If READ_DATABASE isn't defined dbmail should work as normal, i.e use > > only one database. > > > > I did create a patch earlier and sent it to the list, but it's not in > > the > > new release so it's probably nothing that people are interested in? > > Still we are using it so I send it again... > > > > (The patch version is the cvs from 2002-12-05) > > > > Have a nice day! > > /Klas > > [EMAIL PROTECTED] > > > > > > ___ > Dbmail mailing list > Dbmail@dbmail.org > https://mailman.fastxs.nl/mailman/listinfo/dbmail
Re: [Dbmail] DB replication patch
Hi Cedric, This will result into inconsistencies. I'll explain by example. Server 1 is the read-only server, server 2 is the write-only server. The imap server will do a select on a folder through server 1. The user deletes a few messagess from this folder (which will be sent as an update to server 2) and, because the imap server _will_ be faster then the replication process, the folder will be reselected with messages that are in fact deleted. After that, selecting those messages again they will result in error messages. This will only work when talking to a real database cluster. In that case you're talking to one ip address and the cluster will handle the query in the cluster, handling it's own replication. In my opinion Mysql and Postgresql are just not ready yet for real replication / clustering. We use replication to have a hot backup.. that's in my eyes the only save usage of mysql / postgresql replication. Best regards, Eelco On vrijdag, dec 6, 2002, at 21:39 Europe/Amsterdam, Cedric Veilleux wrote: Hi, I must say that Klas's approach seems to be the best to me. MySQL already has very acceptable support for replication and it would be foolish to rely on a script based solution that will never work half as well. PostgreSQL also has one or two replication implementations/projects if I am not mistaken. From the DBmail point of view, the only thing that changes is that we use one connection for READ operations and another for WRITE operations, this could be implemented for each db backend if necessary.. Only queries that succeed on the master server are ran on the slave. If a query that succeeded on the master fail on the slave, replication will be halted until the problem is manually resolved I think. Cedric Le 6 Décembre 2002 04:31, Roel Rozendaal - IC&S a écrit : Hi Klas, we are currently looking out for some kind of replication - your way of doing it is interesting. However I would prefer a system where the replication is completely transparent for dbmail mainly because of postgresql/other database support: one of the main objectives of dbmail is complete freedom for database choice as fas as dbmail is concerned. Implementing this patch would break the symmetry giving extra functionality only available with mysql. Furthermore could you be more specific on your plan of recovery if one of the machines crashes? Seems to me that you would have to rebuild your dbmail system setting it up for 1 database. Our plan for the moment is to have two databases: one used in production and the other as hot spare. When the live machine is used, the succesfull update/insert/delete queries are performed at the hot spare as well (by means of some script running in the background). At failure, all there is to do is change dbmail.conf and send a SIGHUP to the daemons. This system has one major drawback: if the update/insert/delete query fails on the hot spare but already has succeeded on the production machine. We haven't figured this out completely but then again: if the query fails probably something's really wrong with the hot spare. What do you folks think about it? Klas Handel heeft op vrijdag, 6 dec 2002 om 00:55 (Europe/Amsterdam) het volgende geschreven: Hi, We're using dbmail together with MySQL. Since our database is quite large and we have a lot of users we want to spread our load a little bit. We have setup our database in replication (master/slave) mode. Then what we want to do is force all read (select) activities to our slave (slaves) and all write (delete, update and insert) to our master database. I've created a patch which does this (only MySQL). What you need to do is #define the READ_DATABASE parameter in "db.h". Then there are some addition parameters to be set in "dbmail.conf" (read_db, read_user etc.). If READ_DATABASE isn't defined dbmail should work as normal, i.e use only one database. I did create a patch earlier and sent it to the list, but it's not in the new release so it's probably nothing that people are interested in? Still we are using it so I send it again... (The patch version is the cvs from 2002-12-05) Have a nice day! /Klas [EMAIL PROTECTED] ___ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail ___ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail _ E.J.A. van Beek ICT Manager IC&S T: +31 30 2322878 F: +31 30 2322305 PGP-key: www.ic-s.nl/keys/eelco.txt
