You are here

MySQL and Galera Load Balancer (GLB)

When you install a Galera Cluster for MySQL for High Availability (HA) it is not enough to install the Database Cluster to achieve this goal. You also have to make the application aware of this HA functionality. This is typically done with some kind of load balancing mechanism between the database and the application.

We have several possibilities how to make such a load balancing possible:

  • We build such a load balancing mechanism directly into the application.
  • When we use Java or PHP we can use the fail-over functionality of the connectors (Connector/J, mysqlnd-ms).
  • If we cannot touch the application we can put a load balancing mechanism between the application and the database. This can be done with:
    • a Hardware Load Balancer,
    • a Software Load Balancer (Pen, GLB, LVS, HAProxy, SQLproxy, MaxScale and others...),

Building the Galera Load Balancer

As an example we look at the Galera Load Balancer (GLB). The documentation about it you can find in the README file.

It can be built as follows:

wget http://www.codership.com/files/glb/glb-0.7.4.tar.gz
tar xf glb-0.7.4.tar.gz
cd glb-0.7.4
./configure
make
make install

Starting the Galera Load Balancer

The Galera Load Balancer will be started as follows:

./glbd --daemon --threads 6 --control 127.0.0.1:4444 127.0.0.1:3306 \
192.168.56.101:3306:1 192.168.56.102:3306:1 192.168.56.103:3306:1
Incoming address:       127.0.0.1:3306 , control FIFO: /tmp/glbd.fifo
Control  address:        127.0.0.1:4444
Number of threads: 6, source tracking: OFF, verbose: OFF, daemon: YES
Destinations: 3
   0:  192.168.56.101:3306 , w: 1.000
   1:  192.168.56.102:3306 , w: 1.000
   2:  192.168.56.103:3306 , w: 1.000

Querying the Galera Load Balancer

It can be queried as follows:

echo getinfo | nc -q 1 127.0.0.1 4444
Router:
----------------------------------------------------
        Address       :   weight   usage   conns
 192.168.56.101:3306  :    1.000   0.667     2
 192.168.56.102:3306  :    1.000   0.500     1
 192.168.56.103:3306  :    1.000   0.500     1
----------------------------------------------------
Destinations: 3, total connections: 4

and

echo getstats | nc -q 1 127.0.0.1 4444
in: 37349 out: 52598 recv: 89947 / 1989 send: 89947 / 1768 conns: 225 / 4
poll: 1989 / 0 / 1989 elapsed: 76.59987

Draining nodes with Galera Load Balancer

Let's assume, we want to take out node 192.168.56.101 from the Load Balancer for maintenance purposes, this can be done as follows:

echo 192.168.56.101:3306:0 | nc -q 1 127.0.0.1 4444
echo getinfo | nc -q 1 127.0.0.1 4444
Router:
----------------------------------------------------
        Address       :   weight   usage   conns
 192.168.56.101:3306  :    0.000   1.000     0
 192.168.56.102:3306  :    1.000   0.667     2
 192.168.56.103:3306  :    1.000   0.667     2
----------------------------------------------------
Destinations: 3, total connections: 4

Removing and adding nodes from Galera Load Balancer

If you want to shrink or grow your database cluster, removing and adding nodes works as follows:

echo 192.168.56.103:3306:-1 | nc -q 1 127.0.0.1 4444

echo 192.168.56.103:3306:2 | nc -q 1 127.0.0.1 4444

And now have fun playing around with your Galera Load Balancer...

Taxonomy upgrade extras: 

Comments

Daniel the Hazard wrote us: just wanted to leave this complement to your GLB post: a init script for GLB that supports adding and removing targets Thanks Daniel!
#!/bin/sh
#
# glbd          Start/Stop the Galera Load Balancer daemon.
#
# processname: glbd
# chkconfig: 2345 90 60
# description: GLB is a TCP load balancer similar to Pen. \
#              It lacks most of advanced Pen features, as \
#              the aim was to make a user-space TCP proxy which is \
#              as fast as possible. It can utilize multiple CPU cores. \
#              A list of destinations can be configured at runtime. \
#              Destination "draining" is supported. It features \
#              weight-based connection balancing (which becomes \
#              round-robin if weights are equal).

### BEGIN INIT INFO
# Provides: glbd
# Required-Start: $local_fs
# Required-Stop: $local_fs
# Default-Start:  2345
# Default-Stop: 90
# Short-Description: run glbd daemon
# Description: GLB is a TCP load balancer similar to Pen.
### END INIT INFO

prog="glbd"
proc=glbd
exec=/usr/sbin/glbd

LISTEN_PORT="8010"
CONTROL_PORT="8011"
THREADS="2"
DEFAULT_TARGETS="djdb2:8000:0.75 djdb3:8000:0.75 divjobs3:8000:0.75 divjobs4:8000:1"

stop() {
  echo -n "[`date`] $prog: stopping... "
  killall $exec &> /dev/null
  if [ $? -ne 0 ]; then
    echo "failed."
    return
  fi
  echo "done."
}

start() {
  if pidof $prog &> /dev/null ; then
    echo "[`date`] $prog: already running...";
    exit -1
  fi
  echo "[`date`] $prog: starting..."
  wait_for_connections_to_drop
  $exec --daemon --control 127.0.0.1:$CONTROL_PORT --threads $THREADS $LISTEN_PORT $DEFAULT_TARGETS
  PID=$!
  if [ $? -ne 0 ]; then
    echo "[`date`] $prog: failed to start."
    exit -1
  fi
  echo "[`date`] $prog: started, pid=$PID"
  exit 0
}

restart() {
  echo "[`date`] $prog: restarting..."
  stop
  start
}

wait_for_connections_to_drop() {
  while (netstat -na | grep -m 1 ":$LISTEN_PORT" &> /dev/null); do
    echo "[`date`] $prog: waiting for lingering sockets to clear up..."
    sleep 1s
  done;
}

getinfo() {
  echo getinfo | nc 127.0.0.1 $CONTROL_PORT && exit 0
  echo "[`date`] $prog: failed to query 'getinfo' from 127.0.0.1:$CONTROL_PORT"
  exit -1
}

getstats() {
  echo getstats | nc 127.0.0.1 $CONTROL_PORT && exit 0
  echo "[`date`] $prog: failed to query 'getstats' from 127.0.0.1:$CONTROL_PORT"
  exit -1
}

add() {
  if [ "$1" == "" ]; then
    echo $"Usage: $0 add :[:]"
    exit -1
  fi
  if [ "`echo "$1" | nc 127.0.0.1 $CONTROL_PORT`" == "Ok" ]; then
    echo "[`date`] $prog: added '$1' successfully"
    #getinfo
    exit 0
  fi
  echo "[`date`] $prog: failed to add target '$1'."
  exit -1
}

remove() {
  if [ "$1" == "" ]; then
    echo $"Usage: $0 remove :"
    exit -1
  fi
  if [ "`echo "$1:-1" | nc 127.0.0.1 $CONTROL_PORT`" == "Ok" ]; then
    echo "[`date`] $prog: removed '$1' successfully"
    #getinfo
    exit 0
  fi
  echo "[`date`] $prog: failed to remove target '$1'."
  exit -1
}

case $1 in
  start)
    start
  ;;
  stop)
    stop
  ;;
  restart)
    restart
  ;;
  getinfo)
    getinfo
  ;;
  getstats)
    getstats
  ;;
  status)
    getinfo
  ;;
  add)
    add $2
  ;;
  remove)
    remove $2
  ;;
  *)
    echo $"Usage: $0 {start|stop|restart|status|getstats|getinfo|add|remove}"
  exit 2
esac
olicomment

Thanks Oli for the very helpful post, it really helped me to understand how to use and operate GLB, but I'd like to mention here that the option "-q" works only in Ubuntu, but in CentOS, it should be replaced by "-s" to be used as follows:

- To query the GLB on CentOS, the following command should be used instead:
echo getinfo | nc -s 1 127.0.0.1 4444

- To drain a node from the GLB, the following command should be used:

echo 192.168.56.101:3306:0 | nc -s 1 127.0.0.1 4444

- To add or remove a node from the GLB, the following commands should be used:

echo 192.168.56.103:3306:-1 | nc -s 1 127.0.0.1 4444
echo 192.168.56.103:3306:1 | nc -s 1 127.0.0.1 4444
abdel-mawlacomment