Adding Mysql Master/Slave support to QCodo

Keywords : mysql, replication, qcodo, ORM, code-gen

Qcodo is a Php web framework, that has ORM, integrated Ajax, easy form processing. Unlike Rails, Qcodo generate PHP classes (codegen) from database schema. Rails figures out class attributes dynamically.

We use Qcodo at Uloop. We have bunch of machines running different tasks (main website, data feeds, reports ..etc). But they all hit a single database server. As the site traffic grew, we found ourselves hitting the ceiling of single database limitation. One solution was to use mysql replication and use master/slave setup.

But QCodo doesn't support mysql master/slave by default. Here I will talk about how I added master/slave support to Qcodo

Qcodo uses database adapter classes to talk to specific databases. There are adapters like QMySqli5Database (mysql), QPostgreSqlDatabase (postgres), QSqlServerDatabase (MS SQL). Each of these adapters implement a common 'interface' of QDatabaseBase. Some of the methods are

abstract public function Connect();
abstract public function Query($strQuery);
abstract public function NonQuery($strNonQuery);
// and so on

The Approach

Qcodo internally hydrates attibute accesses. For example, when we say

$objBook->Author->Name;

This is how qcodo decodes it

select A.name from author A, book B, where A.id = B.author_id and B.id = $objBook->Id

And this query is executed via QUERY function; all selects are. Any updates / delete / insert is done using NonQuery method.

So tweak these methods to work with master/salve.

Query goes to SLAVE
NonQuery goes to MASTER

bingo!

Implementation

Here is how to enable master/slave in Qcodoe.

3 files need to be modified for this

main config file
a new db driver
QApplication class in prepend.inc.php

1) configuration.inc.php

// ------------- configuration (configuration.inc.php) ------------------
// ....
 switch (SERVER_INSTANCE) {
        case 'dev':
              // original single db connection
             //define('DB_CONNECTION_1', serialize(array('adapter' => 'MySqli5', 'server' => 'localhost', 'port' => null, 'database' => 'uloop', 'username' => 'user1', 'password' => 'xxx', 'encoding' => 'utf8', 'profiling' => false)));
             
              // master-slave
            define('DB_CONNECTION_1', serialize(array(
                            // slave connection
                            'adapter' => 'MySqli5MasterSlave', 
                            'server' => 'localhost', 
                            'port' => null, 
                            'database' => 'uloop', 
                            'username' => 'readonly_user1', 
                            'password' => 'xxx', 'encoding' => 'utf8', 'profiling' => false,
                    
                            // master connection
                            'master_server' => 'msqlmaster',
                            'master_port' => null,
                            'master_database' => 'uloop',
                            'master_username' => 'user1',
                            'master_password' => 'yyy',
                            )));

So the new db connection has two sections, one for master and one for slave. Notice the following:

the slave host is almost always localhost, this way READS don't have to go over the network
always use a READONLY user for slave. This way, there won't be any accidental writes to the slave
master db configuration is pretty straightforward. The host probably will be a remote host. And the db user needs write access

2) Database Driver (QMysqlMasterSlave.class.php)

This is the actual driver. Goes in 'includes/qcodo/_core/database' directory.
QMySqli5MasterSlaveDatabase.class.php
Highlighted Code

Explanation

QMySqli5MasterSlaveDatabase extends QMySqli5Database, and uses many of the super class functions.

It has a reference to MASTER database ($objMsqliMaster). The slave pointer is provided by super class (objMySqli)

The following methods are of interest

IsInMasterSlaveMode (line 017)
checks if the driver is in master/slave mode
QueryMaster (line 054)
sends READ queries to Master. Even though READS normally go to SLAVEs, some times you want to query master (for highly interactive (ajax) applications, to fight replication lag - read below for details)
Query (line 075)
In a simplified version, all read queries (selects) go to SLAVE. But this one uses a switch () to send queries to master or slave.
NonQuery (line 138)
NonQueries all ways go to MASTER
_get (line 255)
reads config data like master_host and master_port ..etc

QCodo always in Master/Slave mode

Once you setup Qcodo with master slave setup, Qcodo is by default in master/slave mode. That is, all reads to go slave and all writes to to master.
This is different from some other framworks like Rails. Where when you want to use slaves, you have to explicitly ask for it
Order.find(:id => 123, :use_slave => true)

This mode works pretty well, because you want to be using master/slave for bulk of your db operations. But this can be an issue for highly interactive applicatins because of replication lag. We will see how to fix this in the next section.

Highly interactive applications and Replication Lag

Slaves lag behind master. It is a fact of replication. This delay can be seconds to milli seconds, depending on work load, how busy the servers are , and replication settings like (sync_binlog).

Typically, applications can ignore this delay. For example, when viewing a YouTube video, if we are showing 598 of 600 comments at this time - missing last two comments that were just posted and being replicated down - it is okay. As long as eventually we show all comments. This 'new thinking' is called 'eventually correct' :-)

But this replication lag, even sub second ones, can be a big problem for highly interactive applications like an Ajax application.
Imagine, doing some form processing, some one fills up page1 and hits next button. The next page tries to read the partial form from the database and do some validations. Remember, the read goes to slave, which may not have the upto date data that was just saved. So you get unexpected application error (empty form data, or random errors).

This problem is exercebated if you set sync_binlog = 0 (for performance reasons). There are tons of discussions on sync_binlog online. Do read them to know more.

One way to work around is, in these circumstances, doing reads and writes on master - forget the master/slave setup for a second.

The way I choose to do this is via setting a flag in QApplication.

3) prepend.inc.php

// ------------- prepend.inc.php ------------------
// ....
abstract class QApplication extends QApplicationBase {
    // ...
    
    public static $MysqlForceMaster = false;
    // ...

}

By default, this flag is FALSE. So qcodo behaves in master/slave mode. if this flag is set to TRUE, then all reads and writes go to master. Here is how to enable it.

// ------------- myform.php ------------------
require('../includes/prepend.inc.php');

QApplication::$MysqlForceMaster = true;

class PostForm extends QForm {
    // business as usual
}

Next steps:

The codegen doesn't work in master slave mode. So for codegen purposes, I use the default database drive
This setup works well for us at Uloop. I think it is generic enough for public use. Look forward to hearing some feedback.

** Comment on this article **

Sujee Maniyam

Life / Technology