core - Add independent connections for reading and writing to the database in WordPress

admin管理员组

文章数量:1122833

I want to add a read/write separation to my database in WordPress, to optimize queries. I do it as follows (with a MU-Plugin):

class Custom_Read_Write_DB extends wpdb {
    public $read_connection;
    public $write_connection;

    function __construct() {
        // Initialize the master database (write connection)
        $this->write_connection = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

        // Initialize read connection
        $this->read_connection = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

        // Call parent constructor to ensure proper initialization
        parent::__construct(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
        $this->suppress_errors(true);
    }

    // Custom query routing logic
    function query($query) {
        // Determine if the query is a read operation
        if ($this->is_read_query($query)) {
            // Implement load-balancing among read servers
            return $this->read_connection->query($query);
        } else {
            // For write operations, use the master database
            return $this->write_connection->query($query);
        }
    }

    // Logic to determine if a query is for reading
    function is_read_query($query): bool {
        $read_query_types = ['SELECT', 'DESCRIBE', 'EXPLAIN'];
        foreach ($read_query_types as $type) {
            if (stripos(trim($query), $type) === 0) {
                return true;
            }
        }
        return false;
    }
}

function replace_wpdb() {
    global $wpdb;
    if (!($wpdb instanceof Custom_Read_Write_DB)) {
        $wpdb = new Custom_Read_Write_DB();
    }
    return $wpdb;
}

add_action('init', 'replace_wpdb', 1);

But when doing this, I get notices that suggest that the implementation is failing. Any ideas?

I want to add a read/write separation to my database in WordPress, to optimize queries. I do it as follows (with a MU-Plugin):

class Custom_Read_Write_DB extends wpdb {
    public $read_connection;
    public $write_connection;

    function __construct() {
        // Initialize the master database (write connection)
        $this->write_connection = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

        // Initialize read connection
        $this->read_connection = new wpdb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);

        // Call parent constructor to ensure proper initialization
        parent::__construct(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);
        $this->suppress_errors(true);
    }

    // Custom query routing logic
    function query($query) {
        // Determine if the query is a read operation
        if ($this->is_read_query($query)) {
            // Implement load-balancing among read servers
            return $this->read_connection->query($query);
        } else {
            // For write operations, use the master database
            return $this->write_connection->query($query);
        }
    }

    // Logic to determine if a query is for reading
    function is_read_query($query): bool {
        $read_query_types = ['SELECT', 'DESCRIBE', 'EXPLAIN'];
        foreach ($read_query_types as $type) {
            if (stripos(trim($query), $type) === 0) {
                return true;
            }
        }
        return false;
    }
}

function replace_wpdb() {
    global $wpdb;
    if (!($wpdb instanceof Custom_Read_Write_DB)) {
        $wpdb = new Custom_Read_Write_DB();
    }
    return $wpdb;
}

add_action('init', 'replace_wpdb', 1);

But when doing this, I get notices that suggest that the implementation is failing. Any ideas?

• database
• core

Share Improve this question edited Sep 3, 2024 at 15:55 Álvaro Franz asked Sep 3, 2024 at 15:33 Álvaro FranzÁlvaro Franz 1,09011 gold badge99 silver badges3131 bronze badges 4

• 2 what you're trying to do sounds like something already done by the HyperDB dropin – Tom J Nowell ♦ Commented Sep 3, 2024 at 15:43
• 1 @TomJNowell - Thanks, but the plugin page says: "This plugin hasn’t been tested with the latest 3 major releases of WordPress. It may no longer be maintained or supported and may have compatibility issues when used with more recent versions of WordPress.". That is why I am not using it. Is this still being maintained? – Álvaro Franz Commented Sep 3, 2024 at 15:45
• 1 HyperDB powers WordPress.com and hundreds of other massive sites, if it failed it would get a mention in the newspapers. Tools like HyperDB don't get updated often as they don't need changes often, the effort to update just the tested with value is more than its worth, but it's also likely that HyperDB is powering sites using WP nightly, including WordPress.org itself – Tom J Nowell ♦ Commented Sep 3, 2024 at 17:55
• @TomJNowell Fair enough, thanks for taking your time to clarify Tom. I appreciate it. – Álvaro Franz Commented Sep 5, 2024 at 8:45

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

Use HyperDB

• An open source DB mechanism plugin that supports replication, failover, load balancing, and partitioning.

Link - https://github.com/Automattic/HyperDB

本文标签： coreAdd independent connections for reading and writing to the database in WordPress