Implemented [SUGGESTION] MySQL Read-Slave, Write-Master

gwinans

Active member
To assist with scalability, it would benefit XenForo greatly to have the ability to send reads to a secondary server.

Config:
Code:
$config['db']['master']['host'] = ...
...
 
$config['db']['slave'][1]['host'] = ...
...
$config['db']['slave'][5]['host'] = ...
This will allow very simplistic horizontal scaling. It can be done as a round-robin system or each slave can host a specific portion of data.

Checks would need to be made to ensure that the slave being read from isn't behind - if it is significantly behind, read from the master or move to the next slave.
 
Upvote 11
This suggestion has been implemented. Votes are no longer accepted.
I'm not opposed to having lag detection mechanisms in place as long as you can disable them (no point in adding overhead for them if you don't need them). Personally, I'm more interested in a baby step though... just to *have* master/slave support at the more rudimentary level. Reads to one (or more) DB servers, and writes to a different one. Not even having the option of any sort makes it a little more difficult for a very high traffic site to use XF since you can't scale beyond the number of queries that your one DB server can do.

Just trying to figure out if I want to patch the existing DB class that XF uses to support it or if I should just make the big leap to MySQL Cluster where all nodes can handle writes properly (then it just becomes the one DB server we can specify now changes as needed with every page view).
 
Would be really nice if there was a way to extend the DB class so one doesn't need to hack up the Zend Db class every upgrade. In my fantasy world, I want to intercept the Zend_Db_Adapter_Abstract::query() method. :)
 
Just trying to figure out if I want to patch the existing DB class that XF uses to support it or if I should just make the big leap to MySQL Cluster where all nodes can handle writes properly (then it just becomes the one DB server we can specify now changes as needed with every page view).
It wouldn't be that difficult to use the wordpress multi-db solution and adopt it for XF. I think the best solution would be modify the DB class. I would assume there wouldn't be many, if any changes to the DB class between releases to XF. Unless there was bug / security issue in there somewhere.

MySQL Cluster 7.0 benchmarks. 143K TPM on 2 nodes. That is fast! I wonder what the costing is for MySQL cluster? $100K in hardware, that software can't be cheap. :)
http://www.clusterdb.com/mysql-cluster/mysql-cluster-database-7-performance-benchmark/
 
Related to MySQL Cluster... I already have plans to build one out that should be able to handle 8M SQL reads and 2M SQL writes per second, at which point the whole "XF doesn't support slave DB servers" becomes moot anyway. But it still would be a nice thing to have. :)

The MySQL Cluster I'm looking at is a total of 12 servers, 144 Xeon cores, 1,152GB RAM, 43.2TB of drive space with the servers all interconnected via 40GBit InfiniBand. All crammed into 6U of rack space. If you are bored, this is the stuff: http://www.shawnhogan.com/2011/05/starting-to-think-about-server-upgrades.html

Obviously it's not just for running a forum. :)
 
Related to MySQL Cluster... I already have plans to build one out that should be able to handle 8M SQL reads and 2M SQL writes per second, at which point the whole "XF doesn't support slave DB servers" becomes moot anyway. But it still would be a nice thing to have. :)

The MySQL Cluster I'm looking at is a total of 12 servers, 144 Xeon cores, 1,152GB RAM, 43.2TB of drive space with the servers all interconnected via 40GBit InfiniBand. All crammed into 6U of rack space. If you are bored, this is the stuff: http://www.shawnhogan.com/2011/05/starting-to-think-about-server-upgrades.html

Obviously it's not just for running a forum. :)
for running the next space shuttle system
 
I started to build a XF MySQL replication class... now that 1.1.0 beta 4 supports specifying the database namespace, you can just make your own DB class that exists within the library/Zend folder. I got it about half done and then I got sidetracked with all this other XF stuff I need to build... lol

And I decided to start prioritizing what I'm working on related to XF... makes more sense to work on the stuff that I know will never become part of XF by default and then build the stuff that might make it to XF someday after everything else is done (just an efficiency thing... would be a waste of time to build something and then have the XF developers incorporate similar functionality before I even get my site live). :)

That being said, I decided to take a cookie based approach vs. a session one because I need it to persist across multiple web servers. If it were ULTRA important that an end user doesn't hit the slave for xx seconds, I could do it via memcache or something, but since it's not really all that important, I decided to not add that network overhead. The worst case scenario would be if the user's browser didn't support cookies and replication was lagging a bit (like 2 or 3 seconds), they wouldn't see whatever was new on the page they were redirected to until they refreshed. 99.999% of the time, replication will be faster than the time it takes to be redirected to a new page, so in the end we are probably talking about less than 1 page view per month that would be affected... someone who doesn't use cookies AND happened to do a write -> redirect at the exact moment in time that replication was lagging by a second or two.
 
Finally got around to coming back to this... It seems to be working, but I haven't done extensive testing to see if there are any weird scenarios I might run into... I'll explain how it works in case anyone can think of any "what ifs" that maybe I didn't think of...

It's done by specifying an adapter namespace within XF's config file like so:
Code:
$config['db']['adapterNamespace'] = 'DigitalPoint';

That lets me have a Mysqli adapter that extends Zend_Db_Adapter_Mysqli.

I'm overriding the _connect() method to connect to one of my slave DB servers (unfortunately I had to hard code the IPs of my slave DB servers since it seems $this->_config ignores any non-default settings). DB connection objects are stored as $this->_connection_master and $this->_connection_slave. Before we bother making the slave DB connection, I'm checking if the client sent a "xf_fm" cookie (XenForo force master), and if it did... we are going to send all reads to the master DB server regardless.

I'm also overriding the query() method and taking a conservative approach... Any query that DOESN'T start with "SELECT" will always be routed to the master DB server. If a query is routed to the master we check if the query doesn't start with "INSERT INTO xf_session_activity" or "INSERT DELAYED"... and if it does not, then we set the xf_fm cookie to 10 seconds and all remaining queries for this page view will be forced to the master DB server (including reads).

The end result is all writes going to master and then "important" writes beyond just the normal session_activity update or view counters causes all queries from that end user to be forced to the master DB server (even for reads) for 10 seconds.

I'm also intercepting the beginTransaction() method and forcing everything to master before the transaction does it's first query. Just seemed like a good idea to never read from a slave during a transaction.

Can anyone think of something I missed?
 
XF2 features a new "ReplicationAdapter" which allows separate read and write connections to be made.

You can see a typical example of configuration below:
PHP:
$config['db']['adapterClass'] = 'XF\Db\Mysqli\ReplicationAdapter';
$config['db']['write'] = [
    'host' => '192.168.10.1',
    // ... username, password, dbname etc.
];
$config['db']['read'] = [
    'host' => '192.168.10.2',
    // ... username, password, dbname etc.
];
 
We only expose a single one (though clearly you could round robin them by choosing one at random via config.php). Of course then there are elements like automatic failover and so on...
 
We only expose a single one (though clearly you could round robin them by choosing one at random via config.php). Of course then there are elements like automatic failover and so on...
Ya, really was just curious more than anything. I stopped using master/slave setups a few years ago and just use multi-master setups exclusively now (database host is just "localhost" regardless of the physical machine it's on or the type of query being executed).
 
XF2 features a new "ReplicationAdapter" which allows separate read and write connections to be made.

You can see a typical example of configuration below:
PHP:
$config['db']['adapterClass'] = 'XF\Db\Mysqli\ReplicationAdapter';
$config['db']['write'] = [
    'host' => '192.168.10.1',
    // ... username, password, dbname etc.
];
$config['db']['read'] = [
    'host' => '192.168.10.2',
    // ... username, password, dbname etc.
];

Multiple host writes do NOT work per test results:

Ex.
PHP:
$config['db']['adapterClass'] = 'XF\Db\Mysqli\ReplicationAdapter';
$config['db']['write'] = [
    'host' => '192.168.10.1',
    // ... username, password, dbname etc.
];
$config['db']['write'] = [
    'host' => '192.168.10.2',
    // ... username, password, dbname etc.
];
$config['db']['read'] = [
    'host' => '192.168.10.2',
    // ... username, password, dbname etc.
];

Write to 192.168.10.1 -> No go
Write to 192.168.10.2 -> OK
 
XF2 features a new "ReplicationAdapter" which allows separate read and write connections to be made.

You can see a typical example of configuration below:
PHP:
$config['db']['adapterClass'] = 'XF\Db\Mysqli\ReplicationAdapter';
$config['db']['write'] = [
    'host' => '192.168.10.1',
    // ... username, password, dbname etc.
];
$config['db']['read'] = [
    'host' => '192.168.10.2',
    // ... username, password, dbname etc.
];

I just spent 30 minutes looking for this info. Put it in the documentation!!! 😉

arn
 
Top Bottom