[SUGGESTION] MySQL Read-Slave, Write-Master

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.

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.

for running the next space shuttle system

+1 for starting with the simple solution first (i.e. master-slave support without regard to replication lag).

It doesn't hurt though to keep replication lag in mind when implementing master-slave - after all, once official master-slave support is there, it shouldn't be too difficult to expand from there (a plugin might do the trick).

1. Dealing with replication lag, I found one solution currently used in Drupal that seems most sensible (there is a lengthy discussion over here):

In cases where a user saves a comment, makes a post, or similar, a routine is called (db_ignore_slave()) that temporarily ignores all slave servers, thus giving time for the saved comment etc. to be propagated to the slave(s). What's important is that the slave is still being used by other users; only the current user who posted the comment etc. would be temporarily using the master to guarantee consistency for that user (i.e. only I have to see the comment I just posted to provide consistency; if others see it my post with a delay due to the replication lag, that's still fine).

To achieve this, a session variable is set for the user specifying the lag time for ignoring the slave(s).

PHP:

function db_ignore_slave() {
  $connection_info = Database::getConnectionInfo();
  // Only set ignore_slave_server if there are slave servers being used, which
  // is assumed if there are more than one.
  if (count($connection_info) > 1) {
    // Five minutes is long enough to allow the slave to break and resume
    // interrupted replication without causing problems on the Drupal site from
    // the old data.
    $duration = variable_get('maximum_replication_lag', 300);
    // Set session variable with amount of time to delay before using slave.
    $_SESSION['ignore_slave_server'] = REQUEST_TIME + $duration;
  }
}

They go with a fixed number of five minutes; it would probably be more sensible to allow the site administrator to specify his own time (for example, on a well-tuned server farm such as digitalpoint's, it would most certainly be OK to set maximum_replication_lag to 5 seconds or less).

2. Regarding automatically detecting certain query types (SELECT) and directing those queries to the slave(s), there are potential issues to consider. MySQL queries allow comments even before the main action (like SELECT), so we would have to parse out the various forms of comments to make sure we get the main action. We'd probably be using regular expressions and string comparisons to identify SELECT, which would mean we'd be devoting processing time to a task whose sole purpose is to speed things up. It's probably not much, but still important to consider. Though what's important to know is that the query parser needs to be smarter than just looking for SELECT and automatically redirecting them to the slave(s). For example, in MySQL you can use the INSERT INTO ... SELECT syntax, which is not read-only. In other words we'd have to find SELECT and verify that the result isn't being used for a write operation. There are also stateful SQL statements, like SELECT SQL_CALC_FOUND_ROWS ... which will lead to a SELECT FOUND_ROWS() that has to be executed on the same connection. Prepared statements also don't necessarily harmonize with automated read/write splitting.

As an alternative we could do manual identification similar to the use of vBulletin's $db->query_read_slave(). We could slowly go through the XF code and update read-only queries. If a query is misclassified as read/write, it just results in non-optimal performance, which could be easily fixed.

We could also go with a hybrid approach, using very conservative autodetection combined with manual identification.

3. FWIW, as long as XF doesn't support read/write splitting in a replication environment, you could also use MySQL Proxy (if you are willing to use alpha software in a production environment). I've tested it in the past and it works quite well (at least in my testing environment). The beauty of MySQL Proxy is the use of lua scripting, which can go far beyond static read/write splitting. But again, it's still alpha...

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.

Cookies sound excellent to me! Since the whole replication lag issue only applies to logged-in members anyways (unless you allow guest posts), and since hardly any logged-in members does not use cookies, I agree that this is a sensible approach.

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?

+1 for this as well.