MySQL master slave configuration

jenis

Is vanilla providing support to make use of MySQL master slave configuration? Kindly confirm.

x00

The framework is agnostic towards any replication arrangement of the database and therefore doesn't provide config for this. Suggest using a proxy for high availability reads.

Bleistivt

I believe there's actually a config setting that uses a different database connection for all SELECT queries:

$Configuration['Database']['Slave']['Name'] = '';
$Configuration['Database']['Slave']['Host'] = '';
$Configuration['Database']['Slave']['User'] = '';
$Configuration['Database']['Slave']['Password'] = '';

No idea, if it works though.

Linc

We've never used this database setup. It may work. If you identify a specific problem, we'd likely accept a pull request to patch it. A bug report of this configuration simply not working is likely to be ignored, however. It's not a priority for us.

If you're experiencing heavy READ db traffic, I suggest investigating Varnish instead.

x00

I think a proxy would be a better approach especially for high availability and redundancy. Separation of concerns. A framework shouldn't be concerned about database replication IMO.

jenis

Thanks for your inputs.

@Linc @x00

But I am bit curious, as seems like some "Slave" configuration support is being available as mentioned by @Bleistivt

So can you please confirm.

Can we make use of these configs setting for using slave db connection?

$Configuration['Database']['Slave']['Name'] = '';
$Configuration['Database']['Slave']['Host'] = '';
$Configuration['Database']['Slave']['User'] = '';
$Configuration['Database']['Slave']['Password'] = '';

x00

nobody responding has used the setup you would have to try. I would still recommend a proxy, becuase it abstracts that functionality out form the framework and will be more flexible, e.g muliple slaves.

R_J

Look at the classes Gdn_Database, PostController and the CommentModel. The PostController and the CommentModel both use ['Slave' => false] as options in some queries in order to force this queries to be made against the master database.

In the Gdn_Database you find:

$config = Gdn::config('Database');

and

        if (array_key_exists('Slave', $config)) {
            $this->_SlaveConfig = $config['Slave'];
        }

Therefore the keys bleistivt mentioned are correct.

The database class has a method which returns the normal connection if there is no slave configured, but if there is a config for a slave db, a connection to the slave is returned.

In the query() method, you can find the following code:

        // We will retry this query a few times if it fails.
        $tries = $this->ConnectRetries + 1;
        if ($tries < 1) {
            $tries = 1;
        }

        for ($try = 0; $try < $tries; $try++) {
            if (val('Type', $options) == 'select' && val('Slave', $options, null) !== false) {
                $pDO = $this->slave();
                $this->LastInfo['connection'] = 'slave';
            } else {
                $pDO = $this->connection();
                $this->LastInfo['connection'] = 'master';
            }

As you can see, if there is no dedicated ['Slave' => false] option in the query, the query is run against the connection returned by the slave() method (which is Slave if there is a slave or Master if there is no slave)

Therefore I'm quite sure that this Slave config parameters will work. But the ['Slave' => false] is used very sparsely and how it is used, I do not expect any performance wins on using such a setup (I'm no db expert, though)

Linc

@R_J said:
But the ['Slave' => false] is used very sparsely and how it is used, I do not expect any performance wins on using such a setup

That's my take as well. We probably added it pre-v1 and have never actually made a concerted effort to continue implementing it once we realized it wasn't necessary. I'd be more inclined to remove it than improve it.