Ulf Wendel

Partitioning support for the PHP replication plugin

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…

PECL/mysqlnd_ms 1.0.1
Master Slave  
| |  
PECL/mysqlnd_ms: load balancer  
PECL/mysqlnd_ms: read-write split  
PHP application using PECL/mysqlnd_ms  

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.

Master Slave Slave Slave  
reports web web web web  
| |  
| Mysqlnd plugin  
| |  
Reporting application Web application  

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.

Master Slave Slave Slave  
reports news shop news news news shop  
| |  
| Mysqlnd plugin  
| |  
Reporting application Web application  

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.

Brainstorming PECL/mysqlnd_ms 1.0.x
Master Slave Slave Slave  
report news shop news news news shop  
  | | | | | |  
  | | Load Balancer (PECL/mysqlnd_ms)  
  Writes Reads  
Read-Write split (PECL/mysqlnd_ms)  
Web app Web app Web app  
Webserver Webserver Webserver  
 
HTTP Load Balancer  

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.

5 Comments

  1. What about allowing the parser to parse speciallly formatted sql comments that tell the plugin to go for master (or slave N)?

    This is a common case for reading stuff immediately after doing some writes: the changes might not have been yet replicated to slaves, so you want reads to be done on the master…

  2. Such functionality is already implemented,

    “The plugin runs read-only statements on the configured MySQL slaves and all other queries on the MySQL master. Statements are considered read-only if they either start with SELECT, the SQL hint /*ms=slave*/ or a slave had been chosen for running the previous query and the query starts with the SQL hint /*ms=last_used*/. In all other cases the query will be send to the MySQL replication master server. “, http://www.php.net/manual/en/mysqlnd-ms.rwsplit.php

    And, Example 3 from http://www.php.net/manual/en/mysqlnd-ms.quickstart.sqlhints.php

  3. Pingback: abcphp.com

  4. I’m not against key/val pairs so long as you make it multi-dimensional. The current syntax would lend itself to this nicely. You could also take the ZF config.ini approach of dot notation. Both of these are slightly easier to write and debug than JSON.

  5. Corey,

    how would you configure an arbitrary number of slaves with dot notation?

    slaves.slave1.host
    slaves.slave2.host

    ZF config.ini seems not to offer a list data type judging from a quick look at the example given in the ZF manual.