The Mysqlnd replication and load balancing plugin alpha release has focused on laying foundations for read-write splitting and load balancing. Thus, we can now look into more juicy topics such as support of schemata based partitioning to increase the granularity of load balancing. Not every slave server needs to replicate all schemata (databases) and tables of the master. The plugin load balancer shall be aware of it and transparently pick the server which has the entities required to execute a statement. Some brainstorming…

Replicating different databases to different servers

Replicating different databases to different servers is a standard MySQL replication pattern. There’s a magnitude of possible applications. For example, your MySQL master server may serve more than one application, say it serves a public web application and a private reporting application monitoring the web application. The private reporting application does not need to be scaled-out using MySQL replication. It has only few admin users. Also, it is sort of private and shall not be mirrored on other machines. Whereas the main web application has a very high read load and needs to be replicated to some slaves for load distribution.

Let the plugin work for you

As your business and the database web grows, you may decide to split the database further (divide and conquer). You partition web into news and shop. Assume that news are far more popular than the merchandise shop and require more database capacity. The MySQL slaves you dedicate to the news part, shall not have to replicate the shop database to reduce replication traffic and workload.

So far, so good, dear MySQL fan boy… BUT, have you ever though of the application developers work caused by schemata changes and the relocation? Sure, easy, we improve the PECL/mysqlnd_ms load balancer…

Teaching the plugin where tables are located

If the PECL/mysqlnd_ms would know which server replicates which databases and tables such a replication growth scenario would case little need for application updates. Brainstorming…

The php.ini-like plugin configuration file syntax chosen for release 1.0.1 is good for key-value pairs. But key-value pairs are not not suited to describe complex replication structures.

[myapp]
master[]=192.168.2.23
slave[]=192.168.2.24
slave[]=192.168.2.25
slave[]=192.168.2.26

JSON or XML is much better suited. Every PHP today speaks JSON, it is lightweight and well known. Let’s introduce a new, alternative JSON-based configuration file syntax in 1.0.2. Remember, the plugin has been labeled alpha and there’s a good reason for the change. This is a fictive example of JSON-based plugin configuration file.

Again, this is brainstorming: no promises on the syntax, no promises on features, … – open for suggestions.

{
  "myapp":
  {
  "master":
    {
      "host":"192.168.2.23"
    },
  "slaves":
    [
      {
         "host":"192.168.2.24",
         "tables":["news.%"]
      },
      {
        "host":"192.168.2.25",
        "tables":["news.%"]
      },
      {
        "host":"192.168.2.26",
      }
    ]
  }
}

The plugin knows from the configuration file which tables are available on the slaves. Granularity is tables and not databases. Databases are a super-set. Supporting table-level granularity also makes your life easier, if you really ever consider to split a database into two, such as the above example the database web gets split into news and shop. The split may look like a good idea from a design perspective. But from an application developers point of view it may be much nicer to leave the database web untouched and do the partitioning based on the table name: web.news_%, web.shop_%. You probably use table name prefixes anyway. This way, you application your application may not need be updated to reflect the schema change.

The syntax shall support pattern for convenience. We should focus on read requests and pattern for slaves only in the first step.

If no table pattern is given (see slave 192.168.2.26), it is assumed that the slave can serve all read request.

The pattern "news.%" does match all tables from the database "news". When executing a statement the plugin needs to analyze on which database it will be run. Detecting the database can be based on (at least) the following:

• current database selected when connecting
• current database set through API call
• current database set with USE
• database name found in FROM-clause

We should try to cover as much as possible.

If we detect that a SELECT statement queries more than one table, we either pick a slave which has all tables or – in the wost case – pick the master. Details are to be determined. The goal is to keep complexity low for the first shot.

If more than one slave has the database (table) required for running a statement, we apply standard load balancing rules to pick a slave from the matching slaves. The above table illustrates it. First we do the read write split. Then, we pick a server (or a group of) for load balancing.

End of brainstorming

All this is early brainstorming. I would be happy to hear from you, if it will of any use for you. As we are at a very early stage, still being unsure about details ourselves, feedback can easily incorporated.

Andrey has just started to do first infrastructure changes in trunk (PECL/mysqlnd_ms repository). Don’t be surprised and disappointed if we break the build from time to time in the near future… it is early brainstorming times.