Head Energy

As part of my work with a large client, we are finding ourselves implementing and adapting an open source PHP CMS as part of an ongoing project to migrate existing content systems onto a new platform. The platform (Concrete5) uses MySQL as it’s data store and so we know we require a MySQL database infrastructure that is compatible with Concrete5 but also meets their own requirements for a scalable, robust and resilient infrastructure which can support their expected load and traffic levels. Concrete5 does come with a robust caching system, but due to the shear dynamicism of the website coupled with the low cost approach to design, we’re unable to eagerly scaffold the cache in a similar way to Basecamp (interesting post about that here), at least not without a much larger project lifecycle.

So,  lets move on to what we know we want:

• Multi A-Z, Multi-Region AWS (Amazon Web Services) deployment that will be resilient to entire region failure worldwide.
• Horizontally scaleable, preferably taking advantage of the elastic properties of EC2. This system also needs to be scalable within the initially UK focused userbase, but also to allow geographic disparate environments (e.g. the Americas and/or Asia) which would still be updated from the UK administrators and be required to share data between all locales, preferably without the enormously costly (and not necessarily well fitting) Akamai family of services.
• Be compatible with a PHP CMS that is unable to split read/write database queries (in this case, the rather aged and questionable PHP ADODB abstraction layer).
• Provide a single synchronous file mount for all nodes in any region.
• The system will support up to an anticipated concurrency level of 250 database connections at any one time.
• Resiliant, the database layer needs to be robust, ensuring that in the event of a database server going offline that access to the data is not limited. The Web Servers need to automatically failover to a working node without users noticing.

There is some light in these demanding (to say the least) requirements, in that although all end users might require database writes for registration, form filling or survey completion in addition to the requirements of the site administrators to update content, the nature of use is predominately a read-heavy environment.

Analysis of Methods

There are a number of key problems in our requirements that are driving us away from native mysql replication. The following ideas were considered but discounted:

Native MySQL Replication

Native MySQL replication is very easy to set up and does offer a fast replication mechanism, it has two key drawbacks in our context:

1. It is asyncronous
2. With more than two servers in the environment, reads and writes from the application layer would need to be split such that writes only go to the master(s). It’s not practical to modify the core of an open-source CMS to carry this separation out. Furthermore, the often touted solution of MySQL Proxy by the community isn’t mature and production ready (- and doesn’t appear as though it ever will be thanks to Oracle politics) and would introduce a fairly inflexible single point of failure, thereby increasing an already slightly complex architecture a little too much for my inner calm. PHP’s own mysqlnd-ms seems to be another possibility with an added bonus of being local to the box, but again amounts to little more than a check for “SELECT” at the beginning of outgoing statements.

Native MySQL Cluster

MySQL cluster offers a clustering technology which can be described, simply, as RAID for a database. While it is a fast, synchronous clustering technology which supports writes on all nodes, it also has a number of limitations which excludes it for consideration.

1. It requires the use of the NDB cluster engine for all tables in the cluster.
2. The maximum size for a table row is 14 kilobytes, not counting BLOB values.
3. The NDBCLUSTER engine does not support foreign key constraints. As with MyISAM tables, if these are specified in a CREATE TABLE or ALTER TABLE statement, they are ignored.
4. Fulltext searching is not supported.
5. Indexes exist in memory on each cluster node, requiring signifcantly more RAM than on a non-cluster server with the same dataset.

Tungsten Replicator

While Tungsten replicator can provide a synchronous and parallel replication environment, it still requires reads and writes to be split when the cluster has more than 2 servers.

The recommendation

The recommended technology which meets all requirements is Galera Cluser from Codership. A follow up blog post will be written to discuss why in the next few days, stay tuned!