You are here

Home » Blogs » Shinguz's blog

MySQL and Galera Load Balancer (GLB)

Sat, 2012-04-07 10:10 — Shinguz

Table of Contents


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 failover 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:

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-1.0.1.tar.gz
$ tar xf glb-1.0.1.tar.gz
$ cd glb-1.0.1
$ ./configure
$ make
$ make install

Packages for Galera Load Balancer you can find on our download page.

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   # use -i or -w on CentOS
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   # use -i or -w on CentOS
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   # use -i or -w on CentOS
$ echo getinfo | nc -q 1 127.0.0.1 4444   # use -i or -w on CentOS
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 nodes from and adding nodes to 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   # use -i or -w on CentOS

$ echo 192.168.56.103:3306:2 | nc -q 1 127.0.0.1 4444   # use -i or -w on CentOS

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

Taxonomy upgrade extras: 
galera
glb
load balancer

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 Wed, 2012-06-13 07:21

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 Sun, 2013-10-27 23:26 

#
# This is a configuration file for glbd service script
#
# On Red Hat and derivatives it should be placed in /etc/sysconfig/glbd.conf
#
# On Debian  and derivatives it should be placed in /etc/default/glbd.conf
#
# All settings besides LISTEN_ADDR are optional.
#

# Address to listen for client connections at. Mandatory parameter.
# To bind to all interfaces only port should be specified.
#LISTEN_ADDR="0.0.0.0:3306"

# Address for controlling connection. Mandatory part is port.
# If not specified control socket will not be opened
#CONTROL_ADDR="127.0.0.1 8081"

# Control FIFO name. It is opened always. glbd will refuse to start if
# this file already exists.
#CONTROL_FIFO="/var/run/glbd.fifo"

# Number of threads (connection pools) to use. It is always a good idea
# to have a few per CPU core.
#THREADS="4"

# Maximum connections. System open files limit will be modified to accommodate
# at least that many client connections provided sufficient privileges.
# Normally you should set it if you plan to go above 500.
# 3 x max_connections = 3 * 151 = 453
#MAX_CONN="450"

# Target servers for client connections, space separated.
# Target specification: IP[:PORT[:WEIGHT]] (WEIGHT and PORT optional)
# WEIGHT defaults to 1, PORT defaults to LISTEN_ADDR port.
#DEFAULT_TARGETS="192.168.0.1:3306:1 192.168.0.2:3306:1 192.168.0.3:3306:1"

# Other glbd options if any as they would appear on the command line.
# --least --round --single --random --source
#OTHER_OPTIONS="--round"

Shinguzcomment Thu, 2021-05-27 16:32 

#
# /etc/systemd/system/glbd.service
#

[Unit]
Description=Galera Load Balancer Service
After=network.target

[Service]
#
# adjust the EnvironmentFile variable for your distribution
#
# On Redhat and derivatives it should be /etc/sysconfig/glbd.conf
#
# On Debian  and derivatives it should be /etc/default/glbd.conf
#
EnvironmentFile=/etc/sysconfig/glbd.conf
Type=simple
ExecStart=/usr/sbin/glbd --daemon --threads $THREADS --max_conn $MAX_CONN $OTHER_OPTIONS --control $CONTROL_ADDR $LISTEN_ADDR $DEFAULT_TARGETS

[Install]
WantedBy=multi-user.target

Shinguzcomment Thu, 2021-05-27 16:33