Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Tino Wildenhain wrote: the standard approach for master-master replicated data is to use an UUID-Datatype (see http://en.wikipedia.org/wiki/UUID ) Good point, although we only have space for 64 bits, so the chance of collision would be higher than it usually is in a UUID system. That is a serious issue that will lead to database corruption. The commit and pack locks need to be cluster-wide. Does MySQL have a way to do that? Well, synchronous replication is a hard beast, master-master synchronous even harder (and you have to ask what problem you really want to solve with it, since the trade-offs are massive) True. Master-master replication can be done, but it often turns out to perform *worse* than a single master, and the mere notion of a cluster-wide lock frightens little children who want their daddy to come home rather than debug clusters all night. I'd rather look at master-slave replication with a little enhancement: RelStorage already has distinct load and store connections, so it could choose a slave when loading and the master when storing. Shane ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Russ Ferriday wrote: (You could get away with a byte for the server ID, leaving masses of bits for the item ID.) That's probably a good idea, but I'd prefer to use the least significant byte for the server ID, effectively allocating OIDs modulo 256. :-) Also, it's becoming clear that each server should have an independent new_oid table (rather than replicate it), and we should do something to ensure the server ID component of the OID is always correct. Shane ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Hi, Shane Hathaway wrote: Stefan H. Holek wrote: We have put up two boxes in a MySQL master-master replication setup [1]. As long as we only write to one of the masters all is fine. Writing to both masters (and expecting MySQL to sort it out) is giving us trouble. We see things like: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '41860' for key 1' on query. Default database: 'prod_zodb'. Query: 'INSERT INTO new_oid VALUES ()' Perhaps we can solve this by making each server allocate a different set of OIDs. For example, one server might allocate odd-numbered OIDs while the other allocates even-numbered OIDs. Even better would be some kind of standard MySQL solution, but I haven't found any yet. the standard approach for master-master replicated data is to use an UUID-Datatype (see http://en.wikipedia.org/wiki/UUID ) Last_Errno: 1050 Last_Error: Error 'Table 'temp_store' already exists' on query. Default database: 'prod_zodb'. Query: 'CREATE TEMPORARY TABLE temp_store ( zoidBIGINT NOT NULL PRIMARY KEY, prev_tidBIGINT NOT NULL, md5 CHAR(32), state LONGBLOB ) ENGINE MyISAM' Uh-oh. The commit lock should have prevented this, so this error suggests that each master has a completely independent set of locks! surprise ;) That is a serious issue that will lead to database corruption. The commit and pack locks need to be cluster-wide. Does MySQL have a way to do that? Well, synchronous replication is a hard beast, master-master synchronous even harder (and you have to ask what problem you really want to solve with it, since the trade-offs are massive) Tino smime.p7s Description: S/MIME Cryptographic Signature ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Stefan! This is like holding all your eyelids apart and shouting out for somebody with a sharp stick! ;) Watcha gonna do when you add a third server? er.. lemme see... Multiply all OIDs by three and allocate new OIDs modulo three? Eeek! Why not make the OID a composite of server ID and sequence? Even if these were both packed in the same 64 bits, just so you keep it a *little bit nasty*, and then maybe just get one eye poked out! ;) (You could get away with a byte for the server ID, leaving masses of bits for the item ID.) Best wishes! --r On 22 May 2008, at 14:49, Stefan H. Holek wrote: Hi Russ, This is correct. I should have mentioned that the two servers are configured so that server A creates odd, server B even sequence numbers. This makes the duplicate id error pretty mysterious, IMO ;-) Stefan On 22.05.2008, at 13:15, Russ Ferriday wrote: I have not read the reference, but it seems that this scheme can never work without keys (OIDs) being qualified by which master they originated on. Otherwise one of the mySQLs would need to be responsible for handing out unique oids. You need an OID origination namespace for each master. -- Stefan H. Holek [EMAIL PROTECTED] Russ Ferriday - Topia Systems - Open Source content management with Plone and Zope [EMAIL PROTECTED] - office: +44 2076 1777588 - mobile: +44 7789 338868 - skype: ferriday ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Stefan H. Holek wrote: We have put up two boxes in a MySQL master-master replication setup [1]. As long as we only write to one of the masters all is fine. Writing to both masters (and expecting MySQL to sort it out) is giving us trouble. We see things like: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '41860' for key 1' on query. Default database: 'prod_zodb'. Query: 'INSERT INTO new_oid VALUES ()' Perhaps we can solve this by making each server allocate a different set of OIDs. For example, one server might allocate odd-numbered OIDs while the other allocates even-numbered OIDs. Even better would be some kind of standard MySQL solution, but I haven't found any yet. Last_Errno: 1050 Last_Error: Error 'Table 'temp_store' already exists' on query. Default database: 'prod_zodb'. Query: 'CREATE TEMPORARY TABLE temp_store ( zoidBIGINT NOT NULL PRIMARY KEY, prev_tidBIGINT NOT NULL, md5 CHAR(32), state LONGBLOB ) ENGINE MyISAM' Uh-oh. The commit lock should have prevented this, so this error suggests that each master has a completely independent set of locks! That is a serious issue that will lead to database corruption. The commit and pack locks need to be cluster-wide. Does MySQL have a way to do that? Thanks for working on this! Shane ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Hi Russ, This is correct. I should have mentioned that the two servers are configured so that server A creates odd, server B even sequence numbers. This makes the duplicate id error pretty mysterious, IMO ;-) Stefan On 22.05.2008, at 13:15, Russ Ferriday wrote: I have not read the reference, but it seems that this scheme can never work without keys (OIDs) being qualified by which master they originated on. Otherwise one of the mySQLs would need to be responsible for handing out unique oids. You need an OID origination namespace for each master. -- Stefan H. Holek [EMAIL PROTECTED] ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Stefan, Thanks for opening this thread here. I'm not actually doing anything like this, but it's great to see you pushing the envelop. I have done a lot of work on replicated data, over the years, that's where the following comment comes from. On 22 May 2008, at 10:36, Stefan H. Holek wrote: We have put up two boxes in a MySQL master-master replication setup [1]. As long as we only write to one of the masters all is fine. Writing to both masters (and expecting MySQL to sort it out) is giving us trouble. We see things like: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '41860' for key 1' on query. Default database: 'prod_zodb'. Query: 'INSERT INTO new_oid VALUES ()' I have not read the reference, but it seems that this scheme can never work without keys (OIDs) being qualified by which master they originated on. Otherwise one of the mySQLs would need to be responsible for handing out unique oids. You need an OID origination namespace for each master. On the other issue, no idea. Best, --r. and Last_Errno: 1050 Last_Error: Error 'Table 'temp_store' already exists' on query. Default database: 'prod_zodb'. Query: 'CREATE TEMPORARY TABLE temp_store ( zoidBIGINT NOT NULL PRIMARY KEY, prev_tidBIGINT NOT NULL, md5 CHAR(32), state LONGBLOB ) ENGINE MyISAM' Stefan [1] http://www.howtoforge.com/mysql_master_master_replication On 20.05.2008, at 18:36, Shane Hathaway wrote: That is mostly correct. You need to replicate transaction, object_state, current_object, and do something with new_oid. There is no requirement to replicate the other tables, since they are used only for packing, although replicating object_ref and object_refs_added may save some work. The new_oid table is special to the MySQL adapter. RelStorage uses the new_oid table as a kind of sequence. Like any sequence, it is intentionally non-transactional. Perhaps there would be no ill effects in switching it to the InnoDB engine. Which MySQL replication method are you looking into? -- Stefan H. Holek [EMAIL PROTECTED] ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev Russ Ferriday - Topia Systems - Open Source content management with Plone and Zope [EMAIL PROTECTED] - office: +44 2076 1777588 - mobile: +44 7789 338868 - skype: ferriday ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
We have put up two boxes in a MySQL master-master replication setup [1]. As long as we only write to one of the masters all is fine. Writing to both masters (and expecting MySQL to sort it out) is giving us trouble. We see things like: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '41860' for key 1' on query. Default database: 'prod_zodb'. Query: 'INSERT INTO new_oid VALUES ()' and Last_Errno: 1050 Last_Error: Error 'Table 'temp_store' already exists' on query. Default database: 'prod_zodb'. Query: 'CREATE TEMPORARY TABLE temp_store ( zoidBIGINT NOT NULL PRIMARY KEY, prev_tidBIGINT NOT NULL, md5 CHAR(32), state LONGBLOB ) ENGINE MyISAM' Stefan [1] http://www.howtoforge.com/mysql_master_master_replication On 20.05.2008, at 18:36, Shane Hathaway wrote: That is mostly correct. You need to replicate transaction, object_state, current_object, and do something with new_oid. There is no requirement to replicate the other tables, since they are used only for packing, although replicating object_ref and object_refs_added may save some work. The new_oid table is special to the MySQL adapter. RelStorage uses the new_oid table as a kind of sequence. Like any sequence, it is intentionally non-transactional. Perhaps there would be no ill effects in switching it to the InnoDB engine. Which MySQL replication method are you looking into? -- Stefan H. Holek [EMAIL PROTECTED] ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Stefan H. Holek wrote: I am playing with database replication (MySQL) and have found the following caveats: - don't mix engine types (MyISAM, InnoDB) - don't use temporary tables Now, RelStorage does both and so I was wondering how to proceed with replication. Would it be prudent to assume that all I ever need to replicate are the InnoDB tables "transaction", "object_state", and "current_object"? All other tables don't seem to be used across transaction boundaries, but I may be missing something here. That is mostly correct. You need to replicate transaction, object_state, current_object, and do something with new_oid. There is no requirement to replicate the other tables, since they are used only for packing, although replicating object_ref and object_refs_added may save some work. The new_oid table is special to the MySQL adapter. RelStorage uses the new_oid table as a kind of sequence. Like any sequence, it is intentionally non-transactional. Perhaps there would be no ill effects in switching it to the InnoDB engine. Which MySQL replication method are you looking into? Shane ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Let me rephrase this. Would it be ok to change *all* tables to use the InnoDB engine (except new_oid)? Stefan On 19.05.2008, at 10:55, Stefan H. Holek wrote: I am playing with database replication (MySQL) and have found the following caveats: - don't mix engine types (MyISAM, InnoDB) - don't use temporary tables Now, RelStorage does both and so I was wondering how to proceed with replication. Would it be prudent to assume that all I ever need to replicate are the InnoDB tables "transaction", "object_state", and "current_object"? All other tables don't seem to be used across transaction boundaries, but I may be missing something here. -- Stefan H. Holek [EMAIL PROTECTED] ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Thanks Tino. I am aware of alternatives. I am however looking for an answer for the specific case of MySQL master-master replication. Exciting times! Stefan On 19.05.2008, at 12:04, Tino Wildenhain wrote: Stefan H. Holek wrote: I am playing with database replication (MySQL) and have found the following caveats: - don't mix engine types (MyISAM, InnoDB) - don't use temporary tables Now, RelStorage does both and so I was wondering how to proceed with replication. Would it be prudent to assume that all I ever need to Maybe use postgres and slony instead? The mysql shortcomings on replication and integrity should be well known. It should also be possible to replicate on ZODB layer instead. At least I read something in the ML beside ZRE of course :-) Cheers Tino -- Stefan H. Holek [EMAIL PROTECTED] ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
Re: [ZODB-Dev] RelStorage: Which tables to replicate?
Stefan H. Holek wrote: I am playing with database replication (MySQL) and have found the following caveats: - don't mix engine types (MyISAM, InnoDB) - don't use temporary tables Now, RelStorage does both and so I was wondering how to proceed with replication. Would it be prudent to assume that all I ever need to Maybe use postgres and slony instead? The mysql shortcomings on replication and integrity should be well known. It should also be possible to replicate on ZODB layer instead. At least I read something in the ML beside ZRE of course :-) Cheers Tino smime.p7s Description: S/MIME Cryptographic Signature ___ For more information about ZODB, see the ZODB Wiki: http://www.zope.org/Wikis/ZODB/ ZODB-Dev mailing list - ZODB-Dev@zope.org http://mail.zope.org/mailman/listinfo/zodb-dev
