In-Portal Issue Tracker - In-Portal CMS
Viewing Issue Advanced Details
ID: Category: Type: Reproducibility: Date Submitted: Last Update:
576 [In-Portal CMS] Database feature request always 2010-02-08 18:40 2012-07-25 05:33
Reporter: Dmitry Platform:  
Assigned To: alex OS:  
Priority: normal OS Version:  
Status: closed Product Version: 5.1.0  
Product Build: Resolution: fixed  
 
ETA: none Fixed in Version: 5.2.0-B1  
Reference: https://groups.google.com/d/topic/in-portal-dev/tq_MUQiFDmQ/discussion
Change Log Message: added Database Load Balancing
Estimate Points: 3
Summary: 0000576: Separating Database Master/Slave Requests
Description: Add ability to have MASTER/SLAVE(s) configuration. This will separate WRITE/READ database requests between 2 servers.

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

For live examples refer to VO project.

In the future, we'd like to add function for have a Pool of Slaves servers.

--------

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.
Steps To Reproduce:
Additional Information: Database name isn't mentioned in slave server configuration, since it's must much during replication anyway.


Additional notes in In-B 23001
Relationships
Attached Files: patch db_load_balancer.patch (23,887) 2011-07-05 12:00
http://tracker.in-portal.org/file_download.php?file_id=1054&type=bug
patch db_load_balancer_v2.patch (25,715) 2011-07-29 03:32
http://tracker.in-portal.org/file_download.php?file_id=1086&type=bug
patch db_load_balancer_v3_520.patch (54,735) 2011-10-03 08:02
http://tracker.in-portal.org/file_download.php?file_id=1181&type=bug
Issue History
Date Modified Username Field Change
2012-07-25 05:33 alex Note Added: 0005092
2012-07-25 05:33 alex Status resolved => closed
2011-10-22 05:33 alex Estimate Points => 3
2011-10-03 08:05 alex Note Added: 0003967
2011-10-03 08:05 alex Status reviewed and tested => resolved
2011-10-03 08:05 alex Fixed in Version => 5.2.0-B1
2011-10-03 08:05 alex Resolution open => fixed
2011-10-03 08:05 alex Assigned To !COMMUNITY => alex
2011-10-03 08:05 alex Changeset attached 5.2.x r14609
2011-10-03 08:04 alex Note Added: 0003966
2011-10-03 08:04 alex Status needs testing => reviewed and tested
2011-10-03 08:03 alex Time Estimate Removed 1 =>
2011-10-03 08:03 alex Note Added: 0003965
2011-10-03 08:03 alex Status needs work => needs testing
2011-10-03 08:02 alex File Added: db_load_balancer_v3_520.patch
2011-09-26 04:53 alex Time Estimate Added 1
2011-09-23 20:46 Dmitry Status needs testing => needs work
2011-09-23 20:46 Dmitry Note Added: 0003881
2011-09-23 20:31 Dmitry Target Version Icebox => 5.2.0
2011-09-11 21:17 Dmitry Issue Monitored: alex
2011-09-11 21:17 Dmitry Note Added: 0003728
2011-09-11 21:02 Dmitry Reference => https://groups.google.com/d/topic/in-portal-dev/tq_MUQiFDmQ/discussion
2011-09-11 21:02 Dmitry Change Log Message => added Database Load Balancing
2011-07-29 03:32 alex Assigned To alex => !COMMUNITY
2011-07-29 03:32 alex Status needs work => needs testing
2011-07-29 03:32 alex File Added: db_load_balancer_v2.patch
2011-07-29 03:32 alex Note Added: 0003643
2011-07-29 03:32 alex Assigned To !COMMUNITY => alex
2011-07-29 03:32 alex Status needs testing => needs work
2011-07-12 05:58 alex Note Added: 0003621
2011-07-12 04:22 alex Note Added: 0003620
2011-07-05 12:03 alex Note Added: 0003584
2011-07-05 12:03 alex Assigned To => !COMMUNITY
2011-07-05 12:03 alex Developer => alex
2011-07-05 12:03 alex Status active => needs testing
2011-07-05 12:00 alex File Added: db_load_balancer.patch
2011-07-05 05:10 alex Additional Information Updated bug_revision_view_page.php?rev_id=733#r733
2011-07-05 05:09 alex Description Updated bug_revision_view_page.php?rev_id=731#r731
2011-07-04 18:29 Dmitry Note Edited: 0003581 bug_revision_view_page.php?bugnote_id=0003581#r730
2011-07-04 18:24 Dmitry Note Edited: 0003581 bug_revision_view_page.php?bugnote_id=0003581#r729
2011-07-04 18:23 Dmitry Note Added: 0003581
2011-06-30 11:29 alex Note Added: 0003579
2011-06-30 11:23 alex Description Updated bug_revision_view_page.php?rev_id=727#r727
2010-08-31 14:16 alex version => 5.1.0
2010-04-25 07:11 alex Note Added: 0001922
2010-04-25 07:11 alex Target Version 5.1.0 => Icebox
2010-02-08 18:40 Dmitry New Issue

Notes
(0001922)
alex   
2010-04-25 07:11   
Moving to Future release, since needs more work.
(0003579)
alex   
2011-06-30 11:29   
We have connection reference in all classes, accessible via $this->Conn attribute.

Now we need to dynamically choose what connection to use based on:
- sql given (SELECT/DESCRIBE will use SLAVE DB; other sqls will use MASTER DB)
- select slave based on their load/weight when slave db should be used

I suggest to create kDBLoadBalancer class, that will hold connection references to master/slave database and will:
1. choose db, based on sql
2. pass call requested method of given connection

Since LoadBalancer don't have Query/GetCol/etc. methods, then we need to use __call function to do that OR we need to create facade methods to do all this.
(0003581)
Dmitry   
2011-07-04 18:23   
(edited on: 2011-07-04 18:29)
We need to add the following functionality:


1. Fallback to Master in case if all Slaves are down/inaccessible.

This can be checked/cached on every page load.


2. Switching Masters During Failover

Switching Masters During Failover - Make Slave as Master if Master is down/inaccessible. This requires more advanced technique of selecting new Master server from pool of all Slaves and replacing replacing old Master. We MUST somehow remember newly picked Master server and send ALL requests (writes/reads) to it without any load balancing (master/slave). All normal operations should be resumed by developer once Synchronization between new Master and other Slaves are back to normal.


NOTEs:

We need to find a way to store a new Master status so all requests are send there and not all Master until we explicitly specify this.

Useful Resources

- http://dev.mysql.com/doc/refman/5.5/en/replication-solutions-switch.html
- http://www.php.net/manual/en/intro.mysqlnd-ms.php (looks like it's in alpha yet)
- http://www.fromdual.com/sites/default/files/mm-single-slave-repl.pdf
- http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html

Additional thoughts:

May be we can store new Master in Cache + update ALL Slaves (DB) once a new master is picked so we don['t write to them at any point.
(0003584)
alex   
2011-07-05 12:03   
To test you need to:
1. create 3+ database servers and 1 web server.
2. create a database on all servers
3. setup replication to make one server as master and all other as slaves
4. enter master db server credentials in config.php
5. enter slave db server credentials in db_servers.php

Then use "ab" or other tool to test server under load and verify, that read requests are equally separated between slave servers.
(0003620)
alex   
2011-07-12 04:22   
I see empty left frame after installing In-Portal with this patch applied. Do you see this problem too?
(0003621)
alex   
2011-07-12 05:58   
Reminder sent to: Dmitry

I've found some serious issues while testing this on provided servers:
------------------------------------

1. $this->Conn->nextQueryFromMaster had no effect
2. methods kDBConnection::doInsert and kDBConnection::doUpdate were executed on slave connection, while they definitely change database
3. when slave connect failed, then error code was retrieved from master connection resulting all dead slave connection to be used ad normal
4. replication lag cache retrieval ended on infinite loop, while trying to get slave connection inside cache init method

Ideas:
------

We need to create replication monitoring tool for Admin in In-Portal. Also that tool should be checked against security, since we are not planning to specify user credentials with REPLICATION CLIENT privileges in /system/db_servers.php file I guess.

We can create ReplicationStatus table in InPortal, that will have these columns:
- SlaveId
- SlaveHostname
- LastErrorCode
- LastErrorMessage
- ReplicationDelay

Then some even non-inportal origin script will call "SHOW SLAVE STATUS" and put all related data in that table.

From In-Portal there could be a CRON agent, that will check on that table and report if replication dies.

In "System Tools" section we can display that table content if needed.
(0003643)
alex   
2011-07-29 03:32   
Issues found:
1. $this->Conn->nextQueryFromMaster was ignored
2. methods kDBConnection::doInsert and kDBConnection::doUpdate were using last slave connection and not master connection
3. index of master server is not efficiently cached, when all slaves were down
4. fail to detect slave down status, because of master connection stats was used by mistake
(0003728)
Dmitry   
2011-09-11 21:17   
Reminder sent to: alex

Let's setup time to test this through. Also, it might be a good idea to run test against eGov project once it's upgrade to 5.1.3 since it has some real data and will be using this script for sure.
(0003881)
Dmitry   
2011-09-23 20:46   
New patch for 5.2.0 needed (PHP 5 format)
(0003965)
alex   
2011-10-03 08:03   
In "db_load_balancer_v3_520.patch" patch also fixed issue, when "$this->Conn->nextQueryCacheable = true;" had no effect, since kDBLoadBalancer property was set instead of underlying kDBConnection class object's.
(0003966)
alex   
2011-10-03 08:04   
Will test all together.
(0003967)
alex   
2011-10-03 08:05   
Fix committed to 5.2.x branch. Commit Message:

Fixes 0000576: Separating Database Master/Slave Requests
(0005092)
alex   
2012-07-25 05:33   
Since 5.2.0 version was released.