MySQL Load Balancing
Some people might run some larger pools with high hashrates and may notice that dashboard updates will cause more load the more shares you have submitted in your setup data timerange for the API calls. If you wish to keep those fast, high data volume updates you might consider setting up a load balancing cluster in MySQL. Luckily, since MPOS is using MySQLnd as the backend driver to communicate with it's nodes, this can be done quite simply by adding the mysqlnd_ms plugin to your PHP installation! This guide will explain you how to install, configure and run a load balancing setup with MPOS.
Keep in mind that we are relying on a third party tool here which I can and will not support fully. It did work for me but I have yet to see a larger pool running this.
Installing the new plugin is rather simple, it is supplied via PECL. I am using Mac OS for this guide, any other distribution that has PECL available should work the same way.
To install simply run: pecl install mysqlnd_ms. This will download, configure and make the plugin for your local PHP setup.
After the module was installed, you will get some hints on what to do next:
Installing '/opt/local/lib/php/extensions/no-debug-non-zts-20090626/mysqlnd_ms.so'
install ok: channel://pecl.php.net/mysqlnd_ms-1.5.2
php.ini "/private/etc/php.ini" does not exist
You should add "extension=mysqlnd_ms.so" to php.ini
Please do as suggested and add this new extension to your PHP installation so it becomes available to MPOS. Mac OS came with a sample line inside the php.ini which I just commented back in:
extension=mysqlnd_ms.so
mysqlnd_ms.enable=1
mysqlnd_ms.config_file=/opt/local/etc/mysqlnd_ms_config.ini
Now with the extension installed and configured in PHP we have to create the configuration file. I will put down a simple two-node configuration with a master and slave declaration. As this is my own laptop running MySQL and I have no slaves available, I will add this machine twice. In a real setup, just point the configuration towards your read/write master and read-only slave! Here my sample configuration for MPOS:
{
"mpos-loadbalancing": {
"master": {
"master_0": {
"host": "127.0.0.1",
"port": "3306"
}
},
"slave": {
"slave_0": {
"host": "127.0.0.1",
"port": "3306"
}
}
}
}
We have defined our cluster, now we can configure MPOS to use it. Luckily this is super simple and done in less than 30 seconds! Open your global configuration file and change your MySQL Host to mpos-loadbalancing:
$config['db']['host'] = 'mpos-loadbalancing';
You can keep the username and password as is, they are used on both the master and slave by default.
Nothing serious, but you must restart/reload Apache each time you change the configuration in mysqlnd_ms! So now do a apachectl restart and you should have it loaded.
You can confirm it works as intended by creating a PHP page and display the phpinfo() content:
As you can see, the application side of things to setup a load balancing system for MySQL is pretty easy. Please keep in mind that MPOS is not doing any failover checks (yet) if a slave fails to respond to a query sent to it. You can read details about failover scenarios here which are supported by this plugin.