It would be great to support master-master configurations.
I've never setup a master-master configuration myself, but I know that the implementation of db_next_id() in Drupal 5 and below was problematic to support master-master configurations. In Drupal 5, db_next_id() used a custom sequences table, ignoring MySQL's auto_increment feature.
Self-generated keys are a pain when you want to do multi-master replication. If master A and master B both insert a row in the same table, the self-generated keys might conflict.
MySQL 5 introduced a auto_increment_increment and a auto_increment_offset variable to control the behavior of MySQL's auto_increment columns. With these variables, you can configure master A to insert rows with even keys, and you can configure master B to insert rows with odd keys. This effectively avoids conflicts.
In Drupal 6, we reworked db_next_id() to get rid of the custom sequences table and to rely on MySQL's auto_increment feature instead. Thus, as of Drupal 6, you should be able to take advantage of MySQL's auto_increment_increment and auto_increment_offset variables. (Pretty much all databases support auto-incrementing keys nowadays.)
Combined with the proposed API changes, this should pave the path to support master-master replication in Drupal 6. Except that maybe, we need a notion of database sessions so that read-only queries that need to work on an up-to-date copy of the data can go to the right master? Anything else that we are overlooking that we might have to expose to the API?
It would be great to support master-master configurations.
I've never setup a master-master configuration myself, but I know that the implementation of
db_next_id()in Drupal 5 and below was problematic to support master-master configurations. In Drupal 5,db_next_id()used a customsequencestable, ignoring MySQL'sauto_incrementfeature.Self-generated keys are a pain when you want to do multi-master replication. If master A and master B both insert a row in the same table, the self-generated keys might conflict.
MySQL 5 introduced a
auto_increment_incrementand aauto_increment_offsetvariable to control the behavior of MySQL'sauto_incrementcolumns. With these variables, you can configure master A to insert rows with even keys, and you can configure master B to insert rows with odd keys. This effectively avoids conflicts.In Drupal 6, we reworked
db_next_id()to get rid of the customsequencestable and to rely on MySQL'sauto_incrementfeature instead. Thus, as of Drupal 6, you should be able to take advantage of MySQL'sauto_increment_incrementandauto_increment_offsetvariables. (Pretty much all databases support auto-incrementing keys nowadays.)Combined with the proposed API changes, this should pave the path to support master-master replication in Drupal 6. Except that maybe, we need a notion of database sessions so that read-only queries that need to work on an up-to-date copy of the data can go to the right master? Anything else that we are overlooking that we might have to expose to the API?
July 15, 2007 - 09:42