Read Only Slaves

Architecture

The proposed architecture is to support a single master (write) DB and a single slave (read) DB.  To support multiple read-only slaves, mysql-proxy can be used.

Dream Changes

Dream's DataCatalog class already has support for a read-only slave by passing in an XDoc like this:

<config>
  <!-- DB master -->
  <db-server>dbmaster</db-server>
  <db-port>3306</db-port>
  <db-catalog>wikidb</db-catalog>
  <db-user>wikiuser</db-user>
  <db-password hidden="true">password</db-password>
  <db-options>pooling=true; Connection Timeout=5; Protocol=socket; Min Pool Size=2; Max Pool Size=50; Connection Reset=false;character set=utf8;ProcedureCacheSize=25;Use Procedure Bodies=true;</db-options>
           
  <!-- DB read-only slave -->
  <db-readonly-server>dbslave</db-readonly-server>
  <db-readonly-port>3306</db-readonly-port>
  <db-readonly-catalog>wikidb</db-readonly-catalog>
  <db-readonly-user>wikiuser</db-readonly-user>
  <db-readonly-password hidden="true">password</db-readonly-password>
  <db-readonly-options>pooling=true; Connection Timeout=5; Protocol=socket; Min Pool Size=2; Max Pool Size=50; Connection Reset=false;character set=utf8;ProcedureCacheSize=25;Use Procedure Bodies=true;</db-readonly-options>
</config>

In order to mark a select query as read-only, you must use the following methods:

public DataCommand NewReadOnlyProcedure(string name);
public DataCommand NewReadOnlyQuery(string query);

If no <db-readonly-server> value is specified in the config XDoc, the master database is used for all queries.

MindTouch (Deki) Changes 

The MindTouch codebase needs to be modified to replace all NewQuery/NewProcedure with NewReadOnlyQuery/NewReadOnlyProcedure (for SELECT queries only).

See proposed patch: ReadOnlyQuery.patch

Mysql-Proxy Configuration

mysql-proxy requires very minimal configuration.  Debian for example:

apt-get install mysql-proxy

To configure slave servers, edit /etc/defaults/mysql-proxy

ENABLED="true"
OPTIONS="--proxy-address=db0:3307 --proxy-backend-addresses=db1:3306 --proxy-backend-addresses=db2:3306"

This starts up the proxy on port 3307 and round-robins requests to db1:3306, db2:3306.

mysql-proxy also has the Lua scripting language built in to allow more complex customization.

Notes

Q: How does this differ from mediawiki's solution? 

A: The last time I checked, mediawiki allows you to specify multiple read-only slaves and managed the load balancing of those slaves in PHP.  Our approach delegates that management to mysql-proxy if multiple slaves are required.