The Problem
As part of the WordPress Multi-Site Installation which involved more than 600,000 blogs, the biggest problem was that mySQL installation cannot support so many tables required for as many blogs. The filesystem was the real bottleneck here as there is a limit to the number of subdirectories that can be formed and mySQL will cross this if we create 600k blogs as each blog requires 9 tables.
Solution
The solution is very obvious, to split the blogs into multiple databases. We also wanted to split the databases into multiple servers for better performance. This is where plugins like HyperDB, SharDB and Multi-DB comes into play by making the task of communicatin and integrating with multiple databases smooth.
Summary of comparison between SharDB, HyperDB and Multi-DB
- We tried all the three plugins and decided on Multi-Db just because only Multi-DB provided multi-server support. For HyperDB and SHarDB, there was no support if the databases were spread across servers. It is possible to improve these plugins for multi-server support, but Multi-DB had the features we wanted with minimal changes.
- HyperDB was the first one we tried as it is from WordPress itself. HyperDB expects a Database architecture of Master-Slave where Masters and slaves have the same data and is used for replication or failover. We could not set up HyperDB support multiple Databases for partitioning data.
- SharDB is also free like HyperDB, but you have to pay a nominal charge for getting the installation instructions for SharDB. SharDB supports partitioning of data across multiple databases thought it does not support multiple servers inherently. Another advantage of SharDB is that it comes with a tool/script to create multiple databases and also for migrating vlogs to the various databases.
- Finally, Multi-DB had all the features of SharDB and also supported multiple servers. Hence we opted for MultiDB even though it cost a very small amount to purchase it
SharDb – Steps we did
In SharDb, selection of databases in order of hexadecimals. So the number of databases must be in the power of 16 (16, 256, 4096). Naming format of databases, for example: mydb_e,mydb_0 etc. It will automatically switch blogs into correct database.
Requirements
1) All databases must be in same server
2) All databases must have the same username and password
3) All database must have the same prefix
4) Number of databases must be power of 16 + 1. The 1 is the main/central database which is named “_main”)
5) Name of database must be in the format ‘prefix_hexa’ (Eg : prefix = ‘test_’ hexadecimal = ‘e’ database name must be ‘test_e’)
Installation
DB steps:
- DB_1) Consider you have 16 databases and all of them have the same username and password.
- DB_2) Assume that all of your blogs are in single database and its name is mydb
- DB_3) Create 17 database and name it mydb_0, mydb_1, ……….. , mydb_9, mydb_a, mydb_b, …….. , mydb_f, mydb_home
- DB_4) Rename mydb_home to mydb_main that’s all with DB
Plugin Steps:
1. Download latest version of SharDb from http://wordpress.org/plugins/shardb/
2. Extract zip file. There are three .php files
3. Open db-settings.php
- a. db-setting.php : Its configuration file
- b. db.php : It used to switch database
- c. shardb-admin.php : For migration query
- d. shardb_hash_length = 2 (if no of db =16, 3 if no of db = 3)
- e. $shardb_prefix = ‘my_prefix’ (mydb_ in our example)
- f. Replace add_slave($read_priority, $hostname, $local_hostname, $user, $password); with your db settings
4. Once finished editing db-setting.php upload it to the same folder as wp-config.php
For your WordPress install:
5. Edit your wp-config.php and add the following line after the database settings are defined: require_once(‘db-settings.php’);
6. Upload shardb-admin.php to /wp-content/plugins/.
7. Network activate SharDB admin tools in Network Admin -> Plugins.
8. Migrate your data to your DB shards using the SharDB migration screen under
Network Admin -> Settings on the main site:
9. After migration complete upload db.php to /wp-content/ (We cannot migrate if db.php is in wp-content folder)