Sample config for round-robin mysql read server

[1864com]

Hello, from these posts:

XF2 features a new "ReplicationAdapter" which allows separate read and write connections to be made.

You can see a typical example of configuration below:

$config['db']['adapterClass'] = 'XF\Db\Mysqli\ReplicationAdapter';
$config['db']['write'] = [
'host' => '192.168.10.1',
// ... username, password, dbname etc.
];
$config['db']['read'] = [
'host' => '192.168.10.2',
// ... username, password, dbname etc.
];

We only expose a single one (though clearly you could round robin them by choosing one at random via config.php). Of course then there are elements like automatic failover and so on...

Could you please provide an example of a configuration for a round-robin load balancing setup with multiple read (or write) MySQL servers?

 

[digitalpoint]

You probably aren't going to get a true round-robin setup, but it would be easy to pick a random read host...

$readHosts = ['192.168.10.2', '192.168.10.3', '192.168.10.4', '192.168.10.5'];
...
'host' => $readHosts[array_rand($readHosts)]

 

[1864com]

ohhh, I see, thank you so much

 

[digitalpoint]

If you want to get a little fancier, you could also use Nginx as a MySQL load balancer (you could use weighting and you could set it up to temporarily remove servers from the pool if it goes down for example). Nginx isn't just a web server, it's also a load balancer. Then you have a single read IP (the Nginx instance) that picks the MySQL instance to route the request to.

 

[1864com]

You are absolutely right. I'll explore that idea as well.

Thing is that my database server saturated its 1Gbit LAN port (but not using up its CPU) therefore, causing timeout and errors during peak periods. Using nginx as a load balancer will still overload it.... unless I combine it with the random $readHosts variables above and try to be creative while staying within the limited budget.