0

I'm trying to make my WordPress database a bit more resilient, with MySQL master/slave replication. To go along with that, I've installed HyperDB, with the master as a read/write database, and the slave as read-only.

As long as both master and slave database servers are running, everything is alright. The Web server sends queries to both master and slave databases. But as soon as I shut down the master database server, my sites become non-responsive. I expected the sites to still function, just not to be able to post new articles and such.

Do I have unrealistic expectations of the HyperDB plugin, or is there something I might have misconfigured?

Below is the relevant section of db-config.php. It's not just straight $wpdb->add_database() calls because a little bit of logic is needed to work out the "correct" database server. For testing, I tried replacing all of it with simple add_database() calls and had the same issue.

// Returns wpdb-style array for use with add_database() copying settings from wp-config
function wshdb_get_template_db($host, $readpref = 1, $writepref = 1) {
    $db_tmpl = array(
            'host' => $host,
            'server' => '',  // only here to suppress a PHP error
            'dataset' => 'global',
            'lag_threshold' => NULL,
            'user' => DB_USER,
            'password' => DB_PASSWORD,
            'name' => DB_NAME,
            'timeout' => 0.2,
            'read' => $readpref,
            'write' => $writepref
    );
    return $db_tmpl;
}

// Populate the wpdb array based on where we're running
$tmp_dbhost = explode(".", strtolower(gethostname()));
$host = $tmp_dbhost[0];
switch($host) {
    // WP4 stage
    case 'webserver1':
    case 'webserver2':
            $wpdb->add_database(wshdb_get_template_db('masterdb'));
            $wpdb->add_database(wshdb_get_template_db('slavedb', 1, 0));
            break;

    // a bunch of other cases omitted

    default:
            $wpdb->add_database(wshdb_get_template_db('localhost'));
            break;
}
2
  • 1
    If a write happens, and no write servers are available, then it will fail. Even loading a page can trigger one of the background pieces to do some form of writing. Log your queries, see what is happening that is not a simple SELECT.
    – Otto
    Feb 5, 2015 at 23:43
  • That's exactly what's happening, it looks like. Enabled the query logger, put a query dump at the bottom of the page, and the theme is doing UPDATEs for no apparent reason. Going back to the theme devs to see what's up. Thanks! Feb 6, 2015 at 15:48

1 Answer 1

2

I just came across a similar issue. The problem was that the theme was updating options on the 'init' action hook.

function theme_setup() {
    update_option('thumbnail_size_w', 170);
    update_option('medium_size_w', 470);
    update_option('large_size_w', 970);
}
add_action('init', 'theme_setup');

That is called on every page load. This is better done using the 'after_switch_theme' action hook which only takes place when the theme is activated.

function theme_setup() {
    update_option('thumbnail_size_w', 170);
    update_option('medium_size_w', 470);
    update_option('large_size_w', 970);
}
add_action('after_switch_theme', 'theme_setup');

This change saved 3 database updates on every page load.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.