[wp-hackers] MySQL Replication Latency Issues in wp-admin pages

[dave morris]

I have an environment running WP 3.0.1 with a Master db and two
slaves. I am using HyperDB to force all writes to go to the Master,
and all reads to read from the two slaves.

I am experiencing a variety of issues in wp-admin pages where data is
being written to the master, and WordPress attempts to read from a
slave, and the data has not reached the slave yet. An example of this
is when I hook 'dbx_post_advanced' to preset some category and custom
taxonomy terms on new posts. I have verified that when I configure
HyperDB to read and write from the Master alone, the
'dbx_post_advanced' hook works fine.

I'm looking into the following options:

* Dedicate only one web server to all wp-admin (aka back office)
traffic
** Configure that server to read and write to/from the Master db only
** Configure the load balancer to route all /wp-admin url's to that
web server

* Configure HyperDB to read/write against the Master only for wp-admin
pages

* Setup MySQL Semisynchronous Replication
** http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
** This solution will most likely not work, however, because
semisynchronous replication will only wait until ONE slave has
completed its writes, not both slaves in my case

Let me know if you have any advice for this issue.

Thanks,
Dave