Let’s talk about WordPress’ advanced database classes. I’m explicitly mentioning HyperDB, but there are other alternatives/forks out there;
HyperDB: Actively maintained and used by the Automattic. It still empowers today’s wp.com infrastructure. https://wordpress.org/plugins/hyperdb/
LudicrousDB: It’s a fork of HyperDB. Developed by JJJ and it supports PHP7, MySQL 5.7, and UTF-8. I highly recommended it. – https://github.com/stuttter/ludicrousdb
shardb: Developed by Ron Rennick. It’s based on HyperDB and implements an MD5 hash based multiple database sharding. I’m not sure, is it still maintained https://github.com/rrennick/shardb
There is also a book for that: https://wpebooks.com/shardb-installation-guide/
multi-db: Developed by WPMUDEV and probably it’s still empowering Edublog’s infrastructure. It’s very similar with shardb and no longer maintained. (you can still find useful scripts on its repo) – https://github.com/wpmudev/multi-db
As a small reminder: WordPress’ core database class is based on famous ezSQL and it’s maintained by WordPress core team. (take a look wp-includes/wp-db.php)
Why we need these drop-ins?
The first thing about HyperDB is, it’s a drop-in, not a standard WordPress plugin. If you didn’t hear the term “drop-in”, it’s a special file that detects automatically by the WordPress core. require_wp_db() function calls the db.php automatically when the file exists on the filesystem.
There are some other drop-ins exist in the WordPress; advanced-cache.php, object-cache.php, sunrise.php … (these are the not subject of this topic)
HyperDB allows us to work with multi-server environment. That means, you can connect to an arbitrary number of database servers and you can route the queries the appropriate database.
Features
- Read and write servers (replication)
- Configurable priority for reading and writing
- Local and remote datacenters
- Private and public networks
- Different tables on different databases/hosts
- Smart post-write master reads
- Failover for downed host
- Advanced statistics for profiling
- WordPress Multisite
(copy/paste without shame 😅)
Installation
As I mentioned earlier, it’s not a standart WordPress plugin. You have to put db.php file under the wp-content/ directory and db-config.php to same directory that holds wp-config.php file. (You can move db-config.php to another place but you have to define the path with DB_CONFIG_FILE constant)
Replication 🤦🏼♂️
Sometimes I check the forum topics about HyperDB and the vast majority of the questions are related to Replication. There are no magic wand folks, HyperDB does not provide replication services, you have to configure god damn, MySQL servers for that. If you don’t understand master/slave things, hire someone who understands those kinds of setup, or you can use managed database services like RDS.
Btw, If you are curious about cloud native stuff, vitess might be the good start.
How does it support the replication?
HyperDB routes queries depend on your configuration.
For example; if you are writing a new post, insert queries go to the master server, and select queries go to the slave servers when someone wants to read your post.
Load Balancing
HyperDB splits queries across the servers and randomly selects database connections from priority groups that you configure.
If you are not going to next wp.com, probably 1(master) + n(slave) in the same datacenter should work.
Failover 🔥
When the connection fails, it tries to connect another server that configured for the same dataset, simple as that.
Partitioning 🎛
The one of my favorite feature of the HyperDB, it supports partitioning. You can put tables to separate servers. This feature is crucial, especially for the multisite. For example; wp_1000_posts placed server A, and wp_4599_posts placed on server B.
You don’t have to use multisite to getting the advantage of partitioning of course.
Backup
Backup is the not subject of this post, probably mysqldump works for many cases. Percona’s XtraBackup can be a better alternative for large players.
Monitoring
MySQL Monitoring and Management like a Boss
Bonus: Barry Abrahamson – dotScale 2013
Bazza had shared some valuable insights from the WP.com. One of my favorite talk about scaling WordPress.
Probably, I will write another post for giving examples and code snippets.