MySQL Replication Latency Issues in wp-admin pages

1 Views

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.

Read More

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

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

Thanks,
Dave

Post Views: 1

Related posts

Leave a Reply

3 comments

  1. 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();

  2. I would advise that you deploy another copy of your site with different connection details to http://admin.example.com/ This admin area would use the master connection details and do reads and writes to the master and would not suffer from any issues from data not being available. You can force the admin URL to be different by setting flags in the wp-config.

        define('WP_SITEURL', 'http://admin.example.com');
    define('WP_CONTENT_URL', 'http://admin.example.com');

    The frontend site http://example.com/ would work as it does currently.