Load Balancer

---

Configuration File

Load Balancer Configuration File

 

Local Configuration:

This file is for sites that have a pool of database servers they would like to load balance between (e.g., BNL, PDSF).

This file should be pointed to by the environmental variable DB_SERVER_LOCAL_CONFIG.

Please replace the DNS names of the nodes in the pools with your nodes/slave.  Pools can be added and removed with out any problems but the needs to be at least one pool of available slaves for general load balancing.

Below is a sample xml with annotations: 

 

<Scatalog version="1.0.1">

<!--Below is a pool of servers accessble only by user priv2 in read only mode
     This pool would be used for production or any other type of operation that needed
     exclusive access to a group of nodes
--!>
                <Server scope="Production" user="priv2" accessMode="read">
                        <Host name="db02.star.bnl.gov"  port="3316"/>
                        <Host name="db03.star.bnl.gov"  port="3316"/>
                        <Host name="db04.star.bnl.gov"  port="3316"/>
                        <Host name="db05.star.bnl.gov"  port="3316"/>
                </Server>

<!--Below is a pool of servers access by ANYBODY in read only mode
     This pool is for general consumption
--!>
                <Server scope="Analysis" accessMode="read">
                        <Host name="db07.star.bnl.gov"  port="3316"/>
                        <Host name="db06.star.bnl.gov"  port="3316"/>
                        <Host name="db08.star.bnl.gov"  port="3316"/>
                </Server>

<!--Below is an example of  Pool (one in this case) of nodes that Only becone active at "Night"
      Night is between 11 pm and 7 am relative to the local system clock
--!>
                <Server scope="Analysis" whenActive="night" accessMode="read">
                        <Host name="db01.star.bnl.gov" port="3316"/>
                </Server>

<!--Below is an example of  Pool (one in this case) of nodes that is reserved for the for users assigned to it.
This is useful for a development node.
 --!>
           
               <Server scope="Analysis" user="john,paul,george,ringo" accessMode="read">
                        <Host name="db01.star.bnl.gov" port="3316"/>
                </Server>

<!--Below is an example of  Pool (one in this case) of nodes that is reserved for write.  Outside of BNL, this should only be allowed on
nodes ONLY being used for development and debugging. At BNL this is reserved for the MASTER.  The element accessMode corresponds
to an environmental variable which is set to read by default
 --!>
                <Server scope="Analysis" accessMode="write">
                        <Host name="robinson.star.bnl.gov" port="3306"/>
                </Server>

</Scatalog>

The label assigned to scope does not matter to the code, it is for bookkeeping purposes only.
Nodes can be moved in and out of pools at the administrators discretion.  A  node can also be a member of more than one pool.

a list of possible features is as follows:

for Sever - attributes are:

  • scope
  • accessMode
  • whenActive
  • user

host - attributes are:

  • name
  • port
  • machinePower
  • cap

Machine power is a weighting mechanism - determining the percentage of jobs that an administrator wants to direct to a particular node.  The default value =1, So
  a machine power of 100 means most requests will go to that node also a machinePower of 0.1 means propotional to the other nodes very few requests will go to that node.

For example
<Server scope="Analysis" whenActive="night" accessMode="read">
                        <Host name="db1.star.bnl.gov" port="3316" machinePower = 90/>
                         <Host name="db2.star.bnl.gov" port="3316"/>
                        <Host name="db3.star.bnl.gov" port="3316" machinePower = 10/>
                </Server>
says that node db1 will get most requests
db2 almost nothing (default value = 1)
db3 very few requests

Cap is a limit of connections allowed on a particular node

Please refer to the attached paper for detailed discussion about each of these attributes/features.

 

Load Balancer Connection

The load balancer makes its decision as to which node to connect to, based on the number of active connections on each node.
It will choose the node with the least number of connections.

In order to do this it must make a connection to each node  in a group.

The load balancer will need an account on the database server with a password associated with it.

The account is:
user = loadbalancer

please contact an administrator for the password associated with the account.
631-344-2499

The load this operation creates is minimal.

so

something like
 grant process on *.* to 'loadbalancer'@'%.bnl.gov' identified by 'CALL 631-344-2499';
of coarse the location should be local.