For many high-traffic MODX deployments, scalability is a primary concern. A concern that could, until now, only be attacked by writing custom code, increasing the number of web server nodes, or increasing the performance of individual servers through hardware and software optimization. But what always remained was a bottleneck at the most important layer of MODX: the database server.
xPDO Multiple Connection Support
The database server bottleneck is one that we’ve wanted to address for some time, and starting with the xPDO 2.2 and MODX 2.2 releases, you will be able to define any number of supplemental database connections for a single instance of the xPDO/modX class. These connections can be marked as mutable (i.e. writable) or not (i.e. read-only), and you can tell xPDO to target one or the other for the initial connection. The connections are shuffled, and the first one matching the criteria is set for the instance, still only connecting to the database on demand.
Using this feature with MySQL replication will help to distribute the load on the database layer, especially when frequent reads and writes are occurring simultaneously. Combined with custom caching strategies, load-balanced web servers, and other existing techniques should help bring a whole new level of scalability to the MODX platform.
Automatic Connection Switching for Writes
In addition to specifying the initial connection’s mutability, xPDO handles automatically switching to a mutable connection if it detects a database write operation needs to be performed when already connected to a read-only slave connection. Anytime you call save() or remove() on an xPDOObject, use the xPDO->removeObject()/removeCollection()/updateCollection() methods, or call xPDO’s exec() wrapper for the PDO method of the same name, xPDO simply looks for a mutable connection and switches to it.
Easy MODX Configuration
Adding connections and configuring the behavior of the pool is amazingly easy. Once you have installed MODX 2.2 to your replication master, you can simply edit the $config_options variable in your primary config file (typically at core/config/config.inc.php unless MODX_CONFIG_KEY is modified) with the additional connections and instructions on what connection to make by default.
$config_options = array ( /* define the mutability of the master db configured during install */ xPDO::OPT_CONN_MUTABLE => true, /* define an array (or JSON string) of read-only slave connections */ xPDO::OPT_CONNECTIONS => array( array( 'dsn' => 'mysql:host=127.0.0.1:19571;dbname=revo_replication;charset=utf8', 'username' => 'rsandbox', 'password' => 'rsandbox', 'options' => array(xPDO::OPT_CONN_MUTABLE => false), 'driverOptions' => array(), ), array( 'dsn' => 'mysql:host=127.0.0.1:19572;dbname=revo_replication;charset=utf8', 'username' => 'rsandbox', 'password' => 'rsandbox', 'options' => array(xPDO::OPT_CONN_MUTABLE => false), 'driverOptions' => array(), ), ), /* define options for the initial connection to be made, i.e. make a read-only connection */ xPDO::OPT_CONN_INIT => array(xPDO::OPT_CONN_MUTABLE => false) );
Save your changes, and if your connections are defined properly and working, any front-end requests will make a connection to one of the slaves. MODX will force manager and connector requests through the mutable master connection to avoid any integrity issues with delays between writes to the master database and reads from the slaves.
Session Handling Caveat
At the moment, MODX default session handling, which uses the database and needs to be able to write to the master, causes every front-end request to write to the session table, creating two connections. It is recommended that session handling (at least for the front-end Contexts) be customized for master/slave database configurations using another technique or technology. Sticky sessions provided by some load-balancing environments is one solution, as would a memcached session handler implementation.
Be a Pioneer
Those of you with MySQL master/slave configurations at your fingertips, you know you want to try it out now. Get the latest nightly build of MODX Revolution 2.2 and just do it.