MySQL Replication Latency Issues in wp-admin pages

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 currently looking into the following options to resolve this issue:

• Dedicate only one web server to all wp-admin 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

You didn’t mention a HyperDB revision so I’m assuming trunk @337290.

HyperDB’s SRTM feature (send reads to master) works two ways. First, it keeps track of which tables have received writes during the current script and sends all subsequent reads for those tables to the master. Second, it gives you a way to force all reads to the master.

In the first case, it is still possible for a read to hit the slave after a write to the same table. If the read is a join query or another kind of query that can place table names far from the beginning of the query, it might slip through. If you can inspect the query that is improperly going to the slave, see if that’s the case. If so, try increasing the substr length here:

if ( preg_match($pattern, substr($query, 0, 1000)) )

It’s important to understand that the SRTM feature only keeps track during one script. So if you write a record (in 1st script) and then get redirected (now in 2nd script) and then try to read that record back out of the database, you will probably be reading from the slave on that second script.

Lastly, let me address the is_admin() idea. It’s a fine idea, simple and effective. Add something like this to your db-config file:

if ( is_admin() )
    $wpdb->send_reads_to_masters();