Detecting MySQL Replication Lag in HyperDB

Iliya has a new post on detecting MySQL replication lag in HyperDB. It starts off with a few WordPress.com numbers:

On WordPress.com we have over 218 million tables and perform tens of thousands queries per second. To scale all of this, we shard our 24 million blogs across more than 550 MySQL servers. This allows us to cope with load bursts and to handle database servers failures.

The post goes on to describe some of the reasons why you might run into replication lag in MySQL and methods HyperDB uses to deal with that. In the case of WordPress.com there are additional issues to consider, like replication across multiple datacenters.

Something that HyperDB has had for awhile is the ability to force read queries back to the master if a write happened earlier on the same connection:

If a connection modifies data in a given table, then all subsequent SELECTs on the same connection for that table are sent to the master. Chances are replication won’t be fast enough to propagate the changes to the slaves on the same page load.

This is a fairly simple technique than can be remarkably useful.

You can find out more about HyperDB on the HyperDB WordPress Codex page, the HyperDB WordPress plugin page, and the HyperDB mailing list. You can also grab a copy directly from the HyperDB Subversion repository.