Advanced Database Interactions with HyperDB

This 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 classes for WP.

Well, it’s time to see some configurations and ideas about how Hyper-DB or relevant DB classes might help you to scale.

There are a bunch of examples and explanations about the configuration on the db-config.php file.

Master-Slave Different Machines

	<?php
	// MASTER machine
	$wpdb->add_database(array(
	'host' => DB_HOST,
	'user' => DB_USER,
	'password' => DB_PASSWORD,
	'name' => DB_NAME,
	'write' => 1,
	'read' => 0,
	));
	// SLAVE 1
	$wpdb->add_database(array(
	'host' => '192.168.1.2',
	'user' => DB_USER,
	'password' => DB_PASSWORD,
	'name' => DB_NAME,
	'write' => 0,
	'read' => 1,
	'dataset' => 'global',
	'timeout' => 0.4,
	));
	// SLAVE 2
	$wpdb->add_database(array(
	'host' => '192.168.1.3',
	'user' => DB_USER,
	'password' => DB_PASSWORD,
	'name' => DB_NAME,
	'write' => 0,
	'read' => 1,
	'dataset' => 'global',
	'timeout' => 0.4,
	));

view raw db-config.php hosted with ❤ by GitHub

Multisite Example

	<?php
	$wpdb->add_database( array(
	'host' => 'global.db.example.com',
	'user' => 'globaluser',
	'password' => 'globalpassword',
	'name' => 'globaldb',
	) );
	$wpdb->add_database( array(
	'host' => 'blog.db.example.com',
	'user' => 'bloguser',
	'password' => 'blogpassword',
	'name' => 'blogdb',
	'dataset' => 'blog',
	) );
	$wpdb->add_callback( 'my_db_callback' );
	// Multisite blog tables are "{$base_prefix}{$blog_id}_*"
	function my_db_callback( $query, $wpdb ) {
	if ( preg_match("/^{$wpdb->base_prefix}\d+_/i", $wpdb->table) ) {
	return 'blog';
	}
	}

view raw db-config.php hosted with ❤ by GitHub

Multisite blog tables are something like “wp4_posts” and the example above says that “hey this table belongs to a blog, send queries to blog dataset”

You can add some logic there, let’s say first 1000 blogs use blogset1 1000-2000 use blogset2 and goes on… (this is a terrible idea, there are some logic that makes this process better)

Another use case; imagine you are hosting one of the enterprise websites on your multisite. (wp.com hosts TechCrunch). You might want to use separate dataset for enterprise customers. So, check the blog id, and return different dataset.

BuddyPress and HyperDB

	<?php
	$wpdb->add_database( array(
	'host' => 'global.db.example.com',
	'user' => 'globaluser',
	'password' => 'globalpassword',
	'name' => 'globaldb',
	) );
	$wpdb->add_database( array(
	'host' => 'bp.db.example.com',
	'user' => 'bpdbuser',
	'password' => 'bpdbpassword',
	'name' => 'bpdb',
	'dataset' => 'bp',
	) );
	$wpdb->add_callback( 'my_db_callback' );
	// depends on your bp prefix bp_core_get_table_prefix() probably not working here
	function my_db_callback( $query, $wpdb ) {
	if ( preg_match("/bp_/", $wpdb->table) ) {
	return 'bp';
	}
	}

view raw db-config.php hosted with ❤ by GitHub

Let’s say you have a successful social network top on the BP. Something like this config will help you to split data between WP and BP.

REST API

	<?php
	// MASTER machine
	$wpdb->add_database(array(
	'host' => DB_HOST,
	'user' => DB_USER,
	'password' => DB_PASSWORD,
	'name' => DB_NAME,
	'write' => 1,
	'read' => 0,
	));
	// REST READ-ONLY
	$wpdb->add_database(array(
	'host' => '192.168.1.9',
	'user' => DB_USER,
	'password' => DB_PASSWORD,
	'name' => DB_NAME,
	'write' => 0,
	'read' => 1,
	'dataset' => 'rest',
	'timeout' => 0.4,
	));
	$wpdb->add_callback( 'my_db_callback' );
	function my_db_callback( $query, $wpdb ) {
	if ( defined( 'REST_REQUEST' ) && REST_REQUEST ){
	return 'rest';
	}
	}

view raw db-config.php hosted with ❤ by GitHub

In this case, we are sharding REST request to a different dataset.

Use a table from another DB

	<?php
	$wpdb->add_database( array(
	'host' => 'global.db.example.com',
	'user' => 'globaluser',
	'password' => 'globalpassword',
	'name' => 'globaldb',
	) );
	$wpdb->add_database( array(
	'host' => '192.168.1.5',
	'user' => DB_USER,
	'password' => DB_PASSWORD,
	'name' => 'mycustomdatabase',
	'write' => 1,
	'read' => 1,
	'dataset' => 'customdataset',
	'timeout' => 0.5,
	) );
	$wpdb->add_table( 'customdataset', 'wp_my_custom_table' );

view raw db-config.php hosted with ❤ by GitHub

The configuration like above, you can use a totally different database from another dataset for a table. Let’s say you are holding a large amount of trivial data, you probably don’t want to keep your main dataset busy. (Of course, we can use callback there too, just want to show add_table works)

Conclusion

HyperDB is a battle-tested and production-grade abstraction layer of the database calls. It’s flexible enough to handle scaling problems out of the box. But careful when planning the sharding scenario across the multiple datasets especially on multisite. (remember Barry’s talk again)

I saw so many wrong assumptions when people try to use this plugin. This is not a normal plugin, as mentioned its description it’s an “advanced database class”. I hope these examples help.

Also, I highly recommended LudicrousDB instead of HyperDB for the new installations.

I just remembered old glories days of the Zend_Db_Table …Which also supports different RDBMS