Database Load Balancing

[Dmitry A.]

Hello everyone,

We are looking to have the ability to run In-Portal website in Database Load Balanced environment. It's usually necessary for high load/availability websites.

The idea is to have MASTER/SLAVE(s) configuration. This will separate WRITE/READ database requests between 2+ servers (1 Master and 1+ Slaves replicated from Master).

1. all Admin requests go to MASTER

2. all Front READ requests go to SLAVE

3. all Front WRITE requests go to MASTER

4. all Front Search READ go to MASTER

To enable load balancing:

1. add $_CONFIG['Database']['LoadBalancing'] = '1'; to /system/config.php file

2. create /system/db_servers.php file with following content:

<?php

$_CONFIG['Databases'] = Array (

    Array (

        'DBHost' => 'slave.host1',

        'DBUser' => 'slave.user1',

        'DBUserPassword' => 'slave.user.password1',

        'DBLoad' => 1,

    ),

    Array (

        'DBHost' => 'slave.host2',

        'DBUser' => 'slave.user2',

        'DBUserPassword' => 'slave.user.password2',

        'DBLoad' => 1,

        'DBMaxLag' => 15, // optional, slave replication delay in seconds

        'DBMaxThreads' => 100, // optional, when slave thread count is above this number, then it won't be used

    ),

);

3. only Slave Servers are listed in file above, since database server defined in /system/config.php is considered as Master Server for backwards compatibility.

See http://www.mediawiki.org/wiki/Manual:$wgDBservers for more details.

Most likely this functionality will be added to In-Portal 5.2.0, but has been tested on 5.1.3 Beta2 - so can be applied if needed.

Task: 576: Separating Database Master/Slave Requests

http://tracker.in-portal.org/view.php?id=576

Attaching patch developed by Alex and tested by myself on 1 Master and 3 Slave servers.

Additional tests highly recommended and appreciated.

DA

[Alexander Obuhovich]

I suspected, that this task wasn't having it's own discussion in groups. Now it has one.

[Alexander Obuhovich]

You also haven't attached latest patch version with all the bug fixes we've found during testing in 1 master and 3 slave server configuration.

I still need to create that patch from master server.

[Dmitry A.]

Yes, please create a patch for this.

Here is a reference to In-B task - #33346 - Setup MySQL Replication for Tests

DA