In-Portal Issue Tracker

Welcome to the In-Portal Open Source CMS Issue Tracker! This is a central management / tracking tool for all types of tasks / issues / bugs for the In-Portal Project. Before reporting any issues, please make sure to read the Guide into Issue Tracker and How to Properly Test and Report Bugs!

Viewing Issue Simple Details Jump to Notes ] Wiki ] View Advanced ] Issue History ] Print ]
ID Category Type Reproducibility Date Submitted Last Update
0000576 [In-Portal CMS] Database feature request always 2010-02-08 18:40 2012-07-25 05:33
Reporter Dmitry View Status public Project Name In-Portal CMS
Assigned To alex Developer
Priority normal Resolution fixed Fixed in Version 5.2.0-B1
Status closed Product Version 5.1.0 Target Version 5.2.0
Time EstimateNo estimate
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:


$_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$wgDBservers for more details.
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
Tags No tags attached.
Change Log Message added Database Load Balancing
Estimate Points 3
Attached Files patch file icon db_load_balancer.patch [^] (23,887 bytes) 2011-07-05 12:00 [Show Content]
patch file icon db_load_balancer_v2.patch [^] (25,715 bytes) 2011-07-29 03:32 [Show Content]
patch file icon db_load_balancer_v3_520.patch [^] (54,735 bytes) 2011-10-03 08:02 [Show Content]

- Relationships Relation Graph ] Dependency Graph ]

-  Notes
User avatar (0001922)
alex (manager)
2010-04-25 07:11

Moving to Future release, since needs more work.
User avatar (0003579)
alex (manager)
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.
User avatar (0003581)
Dmitry (manager)
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.


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

- (looks like it's in alpha yet)

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.

User avatar (0003584)
alex (manager)
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.
User avatar (0003620)
alex (manager)
2011-07-12 04:22

I see empty left frame after installing In-Portal with this patch applied. Do you see this problem too?
User avatar (0003621)
alex (manager)
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


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.
User avatar (0003643)
alex (manager)
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
User avatar (0003728)
Dmitry (manager)
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.
User avatar (0003881)
Dmitry (manager)
2011-09-23 20:46

New patch for 5.2.0 needed (PHP 5 format)
User avatar (0003965)
alex (manager)
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.
User avatar (0003966)
alex (manager)
2011-10-03 08:04

Will test all together.
User avatar (0003967)
alex (manager)
2011-10-03 08:05

Fix committed to 5.2.x branch. Commit Message:

Fixes 0000576: Separating Database Master/Slave Requests
User avatar (0005092)
alex (manager)
2012-07-25 05:33

Since 5.2.0 version was released.

- Related Changesets
In-Portal CMS: 5.2.x r14609
Timestamp: 2011-10-03 08:05:08
Author: alex
Details ] Diff ]
Fixes 0000576: Separating Database Master/Slave Requests
mod - /in-portal/branches/5.2.x/core/install.php Diff ] File ]
mod - /in-portal/branches/5.2.x/core/kernel/application.php Diff ] File ]
mod - /in-portal/branches/5.2.x/core/kernel/db/db_connection.php Diff ] File ]
add - /in-portal/branches/5.2.x/core/kernel/db/db_load_balancer.php File ]
mod - /in-portal/branches/5.2.x/core/kernel/globals.php Diff ] File ]
mod - /in-portal/branches/5.2.x/core/kernel/startup.php Diff ] File ]
mod - /in-portal/branches/5.2.x/core/kernel/utility/cache.php Diff ] File ]
mod - /in-portal/branches/5.2.x/core/kernel/utility/debugger.php Diff ] File ]
mod - /in-portal/branches/5.2.x/core/units/admin/admin_events_handler.php Diff ] File ]

- 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 =>
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 View Revisions
2011-07-05 05:09 alex Description Updated View Revisions
2011-07-04 18:29 Dmitry Note Edited: 0003581 View Revisions
2011-07-04 18:24 Dmitry Note Edited: 0003581 View Revisions
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 View Revisions
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

Web Development by Intechnic
In-Portal Open Source CMS
In-Portal Open Source CMS
Copyright © 2000 - 2009 MantisBT Group

Powered by Mantis Bugtracker