Sep
15
2015
ProxySQL tutorial : seamless replication switchover in less than one second
Posted by Rene' Cannao' on Tue 15 Sep 2015 19:39 UTC

In the previous blog post we setup a replication topology with one master and 2 slaves, and we installed proxysql on a forth servers where the application should connect to.
The post was quite long, and we covered several topics, from installation and configuration of the cluster to installation of proxysql and creation of users and backends, from configuration of query rules for routing and their statistics to monitoring module and replication lag.

But ProxySQL can do more than this.
Among the most interesting features of proxysql is the ability to hold traffic until a backend is available (within a configurable time limit).
For instance, if a query should be send to hostgroup 0 but there is no servers in hostgroup 0, ProxySQL will wait until a server is available in hostgroup 0 or a timeout expires.

This feature has the very interesting effect that using ProxySQL it is possible to perform a planned failover without any error to the application.
In a nutshell, the process is the following:

  1. ProxySQL is configured to disable the master from hostgroup 0 ;
  2. the failover is performed ;
  3. ProxySQL is configured to demote the old master to become a slave, and the new master is moved to hostgroup 0.

Let see these steps in details.
1. ProxySQL is configured to disable the master from hostgroup 0
To make an example, we can run something like this:

vagrant@app:~$ export OldMaster='192.168.124.101'
vagrant@app:~$ export NewMaster='192.168.124.102' 
vagrant@app:~$ mysql -u admin -padmin -h 127.0.0.1 -P6032 -e "UPDATE mysql_servers SET STATUS='OFFLINE_SOFT' WHERE hostname=\"$OldMaster\" AND hostgroup_id=0; LOAD MYSQL SERVERS TO RUNTIME;"


What the above command is simple: the master is disabled (status=OFFLINE_SOFT).
At this stage, no more queries can be sent to old master.


2. Perform the failover
The reason why we configured GTID for replication is to simplify the failover process. In fact, we can use MySQL Utilities to perform the failover. Here an example:

vagrant@app:~$ mysqlrpladmin --discover-slaves-login=root:root --rpl-user=repl:replpass --master=root:root@$OldMaster --new-master=root:root@$NewMaster --demote-master switchover
# Discovering slaves for master at 192.168.124.101:3306
# Discovering slave at 192.168.124.102:3306
# Found slave: 192.168.124.102:3306
# Discovering slave at 192.168.124.103:3306
# Found slave: 192.168.124.103:3306
# Checking privileges.
# Performing switchover from master at 192.168.124.101:3306 to slave at 192.168.124.102:3306.
# Checking candidate slave prerequisites.
# Checking slaves configuration to master.
# Waiting for slaves to catch up to old master.
# Stopping slaves.
# Performing STOP on all slaves.
# Demoting old master to be a slave to the new master.
# Switching slaves to new master.
# Starting all slaves.
# Performing START on all slaves.
# Checking slaves for errors.
# Switchover complete.
#
# Replication Topology Health:
+------------------+-------+---------+--------+------------+---------+
| host             | port  | role    | state  | gtid_mode  | health  |
+------------------+-------+---------+--------+------------+---------+
| 192.168.124.102  | 3306  | MASTER  | UP     | ON         | OK      |
| 192.168.124.101  | 3306  | SLAVE   | UP     | ON         | OK      |
| 192.168.124.103  | 3306  | SLAVE   | UP     | ON         | OK      |
+------------------+-------+---------+--------+------------+---------+
# ...done.
vagrant@app:~$



3. ProxySQL is configured to demote the old master to become a slave, and the new master is moved to hostgroup 0
Similarly, we can quickly reconfigure ProxySQL to perform the traffic switch configuring the old master as a slave and enabling it, and configuring the new master.

vagrant@app:~$ (
> echo "UPDATE mysql_servers SET STATUS='ONLINE', hostgroup_id=1 WHERE hostname=\"$OldMaster\" AND hostgroup_id=0;"
> echo "UPDATE mysql_servers SET hostgroup_id=0 WHERE hostname=\"$NewMaster\" AND hostgroup_id=1;"
> echo "LOAD MYSQL SERVERS TO RUNTIME;"
> ) | mysql -u admin -padmin -h 127.0.0.1 -P6032
Warning: Using a password on the command line interface can be insecure.
vagrant@app:~$




Seems very easy, right?
Let's try to put everything together in a failover script.

vagrant@app:~$ cat ./switchover.sh

#!/bin/sh
if [ $# -ne 2 ]; then
  echo "Usage: ./switchover.sh oldmaster newmaster"
  exit 1
fi
OldMaster=$1
NewMaster=$2

(
echo "UPDATE mysql_servers SET STATUS='OFFLINE_SOFT' WHERE hostname=\"$OldMaster\" AND hostgroup_id=0;"
echo "LOAD MYSQL SERVERS TO RUNTIME;"
) | mysql -u admin -padmin -h 127.0.0.1 -P6032

sleep 1 # let's give some time to current transactions to complete

mysqlrpladmin --discover-slaves-login=root:root --rpl-user=repl:replpass \
  --master=root:root@$OldMaster --new-master=root:root@$NewMaster \
  --demote-master switchover

(
echo "UPDATE mysql_servers SET STATUS='ONLINE', hostgroup_id=1 WHERE hostname=\"$OldMaster\" AND hostgroup_id=0;"
echo "UPDATE mysql_servers SET hostgroup_id=0 WHERE hostname=\"$NewMaster\" AND hostgroup_id=1;"
echo "LOAD MYSQL SERVERS TO RUNTIME;"
) | mysql -u admin -padmin -h 127.0.0.1 -P6032


Note that the script above doesn't perform any sanity check or error handling. This is intentionally, to make the script short and easy to understand.


Seamless replication switchover
At this point we have a script (switchover.sh) that is able to perform a switch in a very short period of time.
Let's put it in action, and while running sysbench (below) in another terminal I will be running:

./switchover.sh 192.168.124.102 192.168.124.101

and then:

./switchover.sh 192.168.124.101 192.168.124.102


Here is the output of sysbench while performing 2 switchovers :


vagrant@app:~$ sysbench --report-interval=1 \
--num-threads=4 --max-requests=0 --max-time=20 \
--test=/home/vagrant/sysbench-0.5/sysbench/tests/db/oltp.lua \
--mysql-user=utest --mysql-password=ptest \
--mysql-host=127.0.0.1 --mysql-port=6033 --mysql-db=test \
--oltp-read-only=off run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 4
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored


Threads started!

[   1s] threads: 4, tps: 137.94, reads: 1958.22, writes: 551.78, response time: 42.52ms (95%), errors: 0.00, reconnects:  0.00
[   2s] threads: 4, tps: 156.01, reads: 2205.11, writes: 635.03, response time: 32.96ms (95%), errors: 0.00, reconnects:  0.00
[   3s] threads: 4, tps: 155.93, reads: 2172.96, writes: 612.71, response time: 36.52ms (95%), errors: 0.00, reconnects:  0.00
[   4s] threads: 4, tps: 156.05, reads: 2200.76, writes: 628.22, response time: 32.25ms (95%), errors: 0.00, reconnects:  0.00
[   5s] threads: 4, tps: 75.00, reads: 996.06, writes: 296.02, response time: 30.29ms (95%), errors: 0.00, reconnects:  0.00
[   6s] threads: 4, tps: 12.00, reads: 197.01, writes: 52.00, response time: 1493.45ms (95%), errors: 0.00, reconnects:  0.00
[   7s] threads: 4, tps: 160.99, reads: 2230.92, writes: 639.98, response time: 33.70ms (95%), errors: 0.00, reconnects:  0.00
[   8s] threads: 4, tps: 158.99, reads: 2275.84, writes: 649.95, response time: 30.82ms (95%), errors: 0.00, reconnects:  0.00
[   9s] threads: 4, tps: 160.02, reads: 2240.22, writes: 642.06, response time: 31.89ms (95%), errors: 0.00, reconnects:  0.00
[  10s] threads: 4, tps: 155.99, reads: 2170.91, writes: 612.97, response time: 34.46ms (95%), errors: 0.00, reconnects:  0.00
[  11s] threads: 4, tps: 132.00, reads: 1825.05, writes: 523.01, response time: 39.40ms (95%), errors: 0.00, reconnects:  0.00
[  12s] threads: 4, tps: 157.00, reads: 2207.00, writes: 628.00, response time: 34.01ms (95%), errors: 0.00, reconnects:  0.00
[  13s] threads: 4, tps: 160.99, reads: 2271.83, writes: 643.95, response time: 31.08ms (95%), errors: 0.00, reconnects:  0.00
[  14s] threads: 4, tps: 164.01, reads: 2286.18, writes: 660.05, response time: 30.51ms (95%), errors: 0.00, reconnects:  0.00
[  15s] threads: 4, tps: 102.01, reads: 1405.07, writes: 405.02, response time: 34.48ms (95%), errors: 1.00, reconnects:  0.00
[  16s] threads: 4, tps: 0.00, reads: 0.00, writes: 0.00, response time: 0.00ms (95%), errors: 0.00, reconnects:  0.00
[  17s] threads: 4, tps: 152.99, reads: 2187.93, writes: 618.98, response time: 32.53ms (95%), errors: 0.00, reconnects:  0.00
[  18s] threads: 4, tps: 162.00, reads: 2277.96, writes: 652.99, response time: 30.47ms (95%), errors: 0.00, reconnects:  0.00
[  19s] threads: 4, tps: 160.78, reads: 2250.98, writes: 640.14, response time: 33.41ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 4, tps: 160.22, reads: 2225.01, writes: 631.85, response time: 32.45ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            37604
        write:                           10741
        other:                           5371
        total:                           53716
    transactions:                        2685   (134.13 per sec.)
    read/write requests:                 48345  (2415.02 per sec.)
    other operations:                    5371   (268.30 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0185s
    total number of events:              2685
    total time taken by event execution: 80.0231s
    response time:
         min:                                 13.86ms
         avg:                                 29.80ms
         max:                               1505.34ms
         approx.  95 percentile:              34.40ms

Threads fairness:
    events (avg/stddev):           671.2500/4.15
    execution time (avg/stddev):   20.0058/0.00


The provided script, using proxysql and mysqlrpladmin, is able to perform a switch in a very short period of time (roughly 1.5 second) without stopping the application and without generating any error.
The application (sysbench in this case) doesn't report any error/failure!
Yet, it is possible to note that sysbench pauses at seconds 5-6 and again at second ~16 . Furthermore, sysbench also reports that the maximum response time is 1505ms : this is exactly the time it took to perform a switch. The trade off for no error is increased latency, within a configurable threshold.

ProxySQL is able to give us some more information about what happened during the switch:

mysql> SELECT max_time, digest_text FROM stats_mysql_query_digest ORDER BY max_time DESC LIMIT 3;
+----------+-------------------------------------+
| max_time | digest_text                         |
+----------+-------------------------------------+
| 1523629  | BEGIN                               |
| 157867   | COMMIT                              |
| 151759   | UPDATE sbtest1 SET k=k+? WHERE id=? |
+----------+-------------------------------------+
3 rows in set (0.00 sec)


BEGIN is the statement that was the slowest among all (1523ms). Let's see why.
When a server is put into OFFLINE_SOFT mode, new incoming connections aren't accepted anymore, while the existing connections are used until they became inactive. In other words, connections are kept in use until the current transaction is completed. This allows to gracefully detach a backend.
With the sysbench executed above, all statements are executed within a transaction that starts with BEGIN , therefore during the failover only the BEGIN statements were blocked: the other statements weren't sent yet.


Even faster seamless replication switchover
In the previous switchover script there is a completely arbitrary "sleep 1" to wait transactions to complete.
This means that if any transaction takes longer than 1 second it is killed, but also that it is possible that the switchover if paused for too long and could be faster.

ProxySQL tracks the number of active connections (in use) in stats_mysql_connection_pool.ConnUsed .
Therefore, if SUM(ConnUsed)==0 it means that no connections are in use.
Let's use this information to improve the script, replacing "sleep 1" with a loop waiting that all connections get free within 1 second.

vagrant@app:~$ cat ./switchover.sh

#!/bin/sh
if [ $# -ne 2 ]; then
  echo "Usage: ./switchover.sh oldmaster newmaster"
  exit 1
fi
OldMaster=$1
NewMaster=$2

(
echo "UPDATE mysql_servers SET STATUS='OFFLINE_SOFT' WHERE hostname=\"$OldMaster\" AND hostgroup_id=0;"
echo "LOAD MYSQL SERVERS TO RUNTIME;"
) | mysql -u admin -padmin -h 127.0.0.1 -P6032


CONNUSED=`mysql -h 127.0.0.1 -P6032 -uadmin -padmin -e 'SELECT IFNULL(SUM(ConnUsed),0) FROM stats_mysql_connection_pool WHERE status="OFFLINE_SOFT" AND hostgroup=0' -B -N 2> /dev/null`
TRIES=0
while [ $CONNUSED -ne 0 -a $TRIES -ne 20 ]
do
  CONNUSED=`mysql -h 127.0.0.1 -P6032 -uadmin -padmin -e 'SELECT IFNULL(SUM(ConnUsed),0) FROM stats_mysql_connection_pool WHERE status="OFFLINE_SOFT" AND hostgroup=0' -B -N 2> /dev/null`
  TRIES=$(($TRIES+1))
  if [ $CONNUSED -ne "0" ]; then
    sleep 0.05
  fi
done

mysqlrpladmin --discover-slaves-login=root:root --rpl-user=repl:replpass \
  --master=root:root@$OldMaster --new-master=root:root@$NewMaster \
  --demote-master switchover

(
echo "UPDATE mysql_servers SET STATUS='ONLINE', hostgroup_id=1 WHERE hostname=\"$OldMaster\" AND hostgroup_id=0;"
echo "UPDATE mysql_servers SET hostgroup_id=0 WHERE hostname=\"$NewMaster\" AND hostgroup_id=1;"
echo "LOAD MYSQL SERVERS TO RUNTIME;"
) | mysql -u admin -padmin -h 127.0.0.1 -P6032



We will now re-run sysbench, while on another terminal we perform 2 switchover:

vagrant@app:~$ sysbench --report-interval=1 --num-threads=4 --max-requests=0 --max-time=20 --test=/home/vagrant/sysbench-0.5/sysbench/tests/db/oltp.lua --mysql-user=utest --mysql-password=p
test --mysql-host=127.0.0.1 --mysql-port=6033 --mysql-db=test --oltp-read-only=off run
sysbench 0.5:  multi-threaded system evaluation benchmark

Running the test with following options:
Number of threads: 4
Report intermediate results every 1 second(s)
Random number generator seed is 0 and will be ignored


Threads started!

[   1s] threads: 4, tps: 132.01, reads: 1885.13, writes: 532.04, response time: 40.72ms (95%), errors: 0.00, reconnects:  0.00
[   2s] threads: 4, tps: 139.95, reads: 1955.36, writes: 555.82, response time: 37.97ms (95%), errors: 0.00, reconnects:  0.00
[   3s] threads: 4, tps: 130.06, reads: 1820.88, writes: 524.25, response time: 43.67ms (95%), errors: 0.00, reconnects:  0.00
[   4s] threads: 4, tps: 138.02, reads: 1935.22, writes: 550.06, response time: 38.49ms (95%), errors: 0.00, reconnects:  0.00
[   5s] threads: 4, tps: 75.00, reads: 1013.98, writes: 297.99, response time: 41.33ms (95%), errors: 0.00, reconnects:  0.00
[   6s] threads: 4, tps: 103.00, reads: 1474.96, writes: 417.99, response time: 46.80ms (95%), errors: 0.00, reconnects:  0.00
[   7s] threads: 4, tps: 140.00, reads: 1955.00, writes: 557.00, response time: 37.95ms (95%), errors: 0.00, reconnects:  0.00
[   8s] threads: 4, tps: 137.99, reads: 1938.81, writes: 548.95, response time: 38.18ms (95%), errors: 0.00, reconnects:  0.00
[   9s] threads: 4, tps: 135.89, reads: 1919.42, writes: 543.55, response time: 36.65ms (95%), errors: 0.00, reconnects:  0.00
[  10s] threads: 4, tps: 133.09, reads: 1846.30, writes: 536.38, response time: 41.17ms (95%), errors: 0.00, reconnects:  0.00
[  11s] threads: 4, tps: 141.03, reads: 1970.43, writes: 560.12, response time: 35.89ms (95%), errors: 0.00, reconnects:  0.00
[  12s] threads: 4, tps: 137.00, reads: 1926.02, writes: 548.01, response time: 39.55ms (95%), errors: 0.00, reconnects:  0.00
[  13s] threads: 4, tps: 141.99, reads: 1961.80, writes: 567.94, response time: 37.37ms (95%), errors: 0.00, reconnects:  0.00
[  14s] threads: 4, tps: 125.01, reads: 1777.13, writes: 504.04, response time: 43.84ms (95%), errors: 0.00, reconnects:  0.00
[  15s] threads: 4, tps: 95.88, reads: 1336.31, writes: 379.52, response time: 59.99ms (95%), errors: 0.00, reconnects:  0.00
[  16s] threads: 4, tps: 122.16, reads: 1716.26, writes: 495.65, response time: 46.88ms (95%), errors: 0.00, reconnects:  0.00
[  17s] threads: 4, tps: 136.99, reads: 1917.90, writes: 543.97, response time: 42.31ms (95%), errors: 0.00, reconnects:  0.00
[  18s] threads: 4, tps: 125.01, reads: 1734.07, writes: 499.02, response time: 45.23ms (95%), errors: 0.00, reconnects:  0.00
[  19s] threads: 4, tps: 135.00, reads: 1912.97, writes: 541.99, response time: 44.89ms (95%), errors: 0.00, reconnects:  0.00
[  20s] threads: 4, tps: 127.00, reads: 1773.07, writes: 512.02, response time: 48.10ms (95%), errors: 0.00, reconnects:  0.00
OLTP test statistics:
    queries performed:
        read:                            35784
        write:                           10224
        other:                           5112
        total:                           51120
    transactions:                        2556   (127.71 per sec.)
    read/write requests:                 46008  (2298.83 per sec.)
    other operations:                    5112   (255.43 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          20.0137s
    total number of events:              2556
    total time taken by event execution: 79.9988s
    response time:
         min:                                 16.54ms
         avg:                                 31.30ms
         max:                                687.98ms
         approx.  95 percentile:              43.43ms

Threads fairness:
    events (avg/stddev):           639.0000/5.48
    execution time (avg/stddev):   19.9997/0.01


The switchover are at second ~5 and ~15 . The complete failover switches took around 0.58s , of which ~0.51s running mysqlrpladmin while ~0.07s to reconfigure proxysql .

Conclusion
In this tutorial we described how it is possible to use ProxySQL to perform a master switchover and reconfiguration of all the slaves in less than one second.
Using ProxySQL to perform a master switchover provides extremely high availability and without returning any error to the application. That is: the application won't be aware that a switchover happened as it doesn't need to be reconfigured, and neither it will get any error. All the application will notice is a subsecond latency while the switchover is running.


Last, a friendly reminder that I will be speaking at PerconaLive Amsterdam on 23rd September about ProxySQL : please come to me session and ask all the questions you have!