Imagine PECL/mysqlnd_ms could keep certain reads on certain slaves in a MySQL master slave setup. You would have a good chance to profit from hot database caches. You would not only distribute read load, scale out horizonally and so forth due to using MySQL replication. You would also optimize your reads for hot caches.

PECL/mysqlnd_ms is our master slave replication load balancing proof-of-concept mysqlnd plugin playground (see also previous blog posts). It allows you to define a callback which is responsible for picking the server that shall run a query. That userspace callback could be doing the read redirection optimization for hot caches.

The eye-catcher: hot cache optimization

(pecl_mysqlnd_ms_config.ini)

[myapp]
master[] = master_host:port_or_socket
slave[] = slave_host:port_or_socket
pick[] = user
pick[] = random

(PHP configuration file, php.ini)

mysqlnd_ms.enable=1
mysqlnd_ms.ini_file=pecl_mysqlnd_ms_config.ini

(Your PHP application)

function pick_server($connected_host, $query, $master, $slaves, $last_used_connection) {
  if (stristr($query, "FROM table_a"))
    /* optimize for hot cache */
    return "slave_for_table_a";
  else 
    /* use built-in random load balancing policy */
     return NULL;
}
mysqlnd_ms_set_user_pick_server("pick_server");

Tempting, easy, enjoy. You got the eye-catcher, lets talk about how the callback knows where to send the query.

How app and callback/plugin can communicate

Your applications can consist of tens of thousands of lines of code. Rules for optimizing query distribution could be very basic or very complex ones. If basic and, not changing often, the decision making rules are probably easy to implement. If complex, the callback may be the wrong place to decide. Guess you had 50 more of those stristr() calls I used above. It would be better if the callback got a short message from the application where to send the query. Take a step back, replace the word “callback” with “plugin built-in distribution logic”: same story. For complex distribution rules, the application needs to tell the callback/plugin in a simple, easy to understand yet rich language what to do.

The proof-of-concept is using SQL hints to let the application direct queries to any of the configured master or slave servers. A SQL hint is a SQL comment. If a system does not understand it, it ignores it. The hint is harmless. The syntax is short, parsing is fast. The hint can transport a message without the need for additional API calls to send the message. And, the SQL hint can be part of a SQL dump. Any system importing the SQL dump does not need to know anything about the SQL hint. The only drawback I can think of is that the SQL hint blows up the SQL a bit – marginally longer parsing times, marginally bigger log files. However, if that really is an issue, one could filter out the SQL hint after reading it.


/* a sql hint */SELECT * FROM test
/* a sql hint */INSERT INTO test(col_a) VALUES (1)


Searching for the words…

If we look at the SQL hint as sort of a protocol, we find an envelope and a payload: /* payload */. The payload holds the words of the language spoken by application and callback/plugin. The built-in language of the plugin has only three words to make processing fast: use slave, use master, use last used.

The hot-cache-optimization idea, if we wanted to have it built-in, would need another word, for example “use server id=x”. Does that sound familiar to you, server id? Sharding…

My attempt to collect feedback via blog comments worked quite well so far – thank you! It encourages me to ask another question.
Q: Shall there be such a built-in word "use server id=x"?

Simplified Jsonize

You user callback, responsible for picking a server and redirecting queries, could speak simplified jsonize. Simplified jsonize is a language with an indefinite amount of words. I think, the built-in language should not be so rich. It is too complex. But you may need a complex language to keep the rule engine in the callback both short and powerful.

function pick_server($connected_host, $query, $master, $slaves, $last_used_connection) {
  /* default:  built-in random/round robin/... load balancing policy */
  $server_to_use = NULL;

  if (preg_match("@^/\*.+\*/@ismU" $query, $matches)) {
    /* a sql hint */
    if ($json = json_decode($matches[1])) {
       /* we have parsed our own language */
      
        /* ... do something meaningful */
        $server_to_use = $json->properly_used_not_like_in_demo;
    }
  }

   return $server_to_use;
}

Simplified jsonize is using the json format to encode arbitrary words of language in an easy for you to parse way.