Mustafa Uysal

I'm traveling light, it's au revoir…

Advanced Database classes for WordPress

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.

One response

  1. […] is the continuation post of my previous post “Advanced Database classes for WordPress“. Please read that post before continue unless you are not familiar with advanced database […]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.