[sqlalchemy] Re: DB Redundancy
On May 8, 7:53 am, Ants Aasma ants.aa...@gmail.com wrote: On May 7, 4:15 pm, goo...@venix.com goo...@venix.com wrote: MySQL has a mechanism for a database to read the log from a master database and replay the commands. This provides a loose coupling with near real-time backup of the data. Should the backup server stop or lose contact, the primary server is unaffected. When the backup server regains contact, it restarts the log processing from the point where it left off. MySQL log based replication is asynchronous. True. That can be an advantage if you are simply looking for a backup of your data. It's only useful if losing some transactions in case of a crash isn't a problem. Not sure what you mean by this. In the event of a crash, you presumably are able to recover what was logged. Any unlogged transactions are lost. This would seem to be independent of replication. This is probably losing sqlalchemy relevance. If you can recommend a URL with strategies for dealing with DB failures, that could be helpful to those who are still following this thread. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: DB Redundancy
On May 7, 4:15 pm, goo...@venix.com goo...@venix.com wrote: MySQL has a mechanism for a database to read the log from a master database and replay the commands. This provides a loose coupling with near real-time backup of the data. Should the backup server stop or lose contact, the primary server is unaffected. When the backup server regains contact, it restarts the log processing from the point where it left off. MySQL log based replication is asynchronous. It's only useful if losing some transactions in case of a crash isn't a problem. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: DB Redundancy
On May 6, 3:43 am, Ants Aasma ants.aa...@gmail.com wrote:
On May 6, 9:07 am, Vic vctr...@gmail.com wrote:
I'm looking for a way to have my DB replicated in REAL TIME to be used
in case I lose my primary copy.
I saw that the two phase commit exist but I'm not sure if that is the
correct option. I have the feeling that it would be abusing a
mechanism purposed for correlating to separate DBs and not creating
replications.
The preferred way to replicate databases is to use ready-made database
replication tools. For postgres the easiest way to do synchronous
replication is currently pgpool-II. With mysql I'm not so sure,
MySQL has a mechanism for a database to read the log from a master
database and replay the commands. This provides a loose coupling with
near real-time backup of the data. Should the backup server stop or
lose contact, the primary server is unaffected. When the backup
server regains contact, it restarts the log processing from the point
where it left off.
there
is the NDB cluster, but it has its own issues, possibly you can find
similar replication middleware for it.
If you must do the replication inside your application, then my advice
is to do it by subclassing sqlalchemy.engine.{Engine,Connection} to
handle distributing requests to multiple backends and managing
transactions across them. You still need 2 phase commits to achieve
consistency, and the corresponding separate transaction management
that goes with it (to rollback/commit prepared transactions in case of
crashes). Also to avoid inconsitencies you have to get sequence values
from one database, and cannot use volatile functions for inserts,
updates. Also, if you are doing this to get high availability, then
you need figure out, how to bring a replica up online. Don't expect
this to be anything near simple or transparent if you want to have any
kind of concurrency for write queries.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: DB Redundancy
On May 6, 9:07 am, Vic vctr...@gmail.com wrote:
I'm looking for a way to have my DB replicated in REAL TIME to be used
in case I lose my primary copy.
I saw that the two phase commit exist but I'm not sure if that is the
correct option. I have the feeling that it would be abusing a
mechanism purposed for correlating to separate DBs and not creating
replications.
The preferred way to replicate databases is to use ready-made database
replication tools. For postgres the easiest way to do synchronous
replication is currently pgpool-II. With mysql I'm not so sure, there
is the NDB cluster, but it has its own issues, possibly you can find
similar replication middleware for it.
If you must do the replication inside your application, then my advice
is to do it by subclassing sqlalchemy.engine.{Engine,Connection} to
handle distributing requests to multiple backends and managing
transactions across them. You still need 2 phase commits to achieve
consistency, and the corresponding separate transaction management
that goes with it (to rollback/commit prepared transactions in case of
crashes). Also to avoid inconsitencies you have to get sequence values
from one database, and cannot use volatile functions for inserts,
updates. Also, if you are doing this to get high availability, then
you need figure out, how to bring a replica up online. Don't expect
this to be anything near simple or transparent if you want to have any
kind of concurrency for write queries.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
