Native ProxySQL Cluster - part 3 - Sync MySQL Servers

  • Date:
  • Tags: proxy mysql release proxysql ha gpl cluster servers
Clusters

Sync MySQL Servers with ProxySQL Cluster

In the two previous blog posts about ProxySQL Cluster we described how to setup a Cluster, how to add users/rules and propagate them across the Cluster, how to provision new nodes, how to re-sync nodes after they have been out of the Cluster, and some features in the roadmap, especially around the concept of core nodes vs satellite nodes.
We also mention that there are 4 modules/tables that currently ProxySQL Cluster is able to sync:

  • mysql_query_rules
  • mysql_servers
  • mysql_users
  • proxysql_servers

We covered 3 of these, and the last one missing is how to sync mysql_servers, related to MySQL Servers.
This topic needs a dedicated blog post because it important to understand how ProxySQL Cluster will manage reconfiguration of backends, for example during a failover.

Let's start checking the current setup:

Admin130> SELECT * FROM mysql_servers;
Empty set (0.00 sec)

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 1       | 2017-09-07 22:57:16 | 0x0000000000000000 | 2017-09-07 22:57:17 | 2017-09-07 22:58:25 | 0          | 2017-09-07 22:58:25 |
| 172.16.3.131 | 6032 | mysql_servers | 1       | 2017-09-07 22:57:17 | 0x0000000000000000 | 2017-09-07 22:57:18 | 2017-09-07 22:58:25 | 0          | 2017-09-07 22:58:25 |
| 172.16.3.132 | 6032 | mysql_servers | 1       | 2017-09-07 22:57:18 | 0x0000000000000000 | 2017-09-07 22:57:19 | 2017-09-07 22:58:25 | 0          | 2017-09-07 22:58:25 |
| 172.16.3.133 | 6032 | mysql_servers | 1       | 2017-09-07 22:57:19 | 0x0000000000000000 | 2017-09-07 22:57:19 | 2017-09-07 22:58:25 | 0          | 2017-09-07 22:58:25 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

Admin130> INSERT INTO mysql_servers (hostgroup_id, hostname, max_connections) VALUES (10,'172.16.3.141',100), (20,'172.16.3.142',100);
Query OK, 2 rows affected (0.00 sec)

Admin130> SELECT * FROM runtime_mysql_servers;
Empty set (0.00 sec)

Let's now LOAD TO RUNTIME, and see what happens:

Admin130> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin130> SELECT * FROM runtime_mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 20           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 2       | 2017-09-07 22:58:35 | 0xE7F7D98A69B392CA | 2017-09-07 22:58:36 | 2017-09-07 22:58:41 | 0          | 2017-09-07 22:58:41 |
| 172.16.3.131 | 6032 | mysql_servers | 2       | 2017-09-07 22:58:36 | 0xE7F7D98A69B392CA | 2017-09-07 22:58:36 | 2017-09-07 22:58:41 | 0          | 2017-09-07 22:58:41 |
| 172.16.3.132 | 6032 | mysql_servers | 2       | 2017-09-07 22:58:36 | 0xE7F7D98A69B392CA | 2017-09-07 22:58:36 | 2017-09-07 22:58:41 | 0          | 2017-09-07 22:58:41 |
| 172.16.3.133 | 6032 | mysql_servers | 2       | 2017-09-07 22:58:36 | 0xE7F7D98A69B392CA | 2017-09-07 22:58:36 | 2017-09-07 22:58:41 | 0          | 2017-09-07 22:58:41 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

The change is immediately propagated: perfect!

We will now add more servers, and load again to runtime:

Admin130> INSERT INTO mysql_servers (hostgroup_id, hostname, max_connections) VALUES (30,'172.16.3.141',100), (40,'172.16.3.142',100);
Query OK, 2 rows affected (0.00 sec)

Admin130> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin130> SELECT * FROM runtime_mysql_servers;
 +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 40           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 30           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 20           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 3       | 2017-09-07 23:00:09 | 0x67E8B47695B0535F | 2017-09-07 23:00:09 | 2017-09-07 23:00:13 | 0          | 2017-09-07 23:00:13 |
| 172.16.3.131 | 6032 | mysql_servers | 3       | 2017-09-07 23:00:09 | 0x67E8B47695B0535F | 2017-09-07 23:00:10 | 2017-09-07 23:00:13 | 0          | 2017-09-07 23:00:13 |
| 172.16.3.132 | 6032 | mysql_servers | 3       | 2017-09-07 23:00:09 | 0x67E8B47695B0535F | 2017-09-07 23:00:10 | 2017-09-07 23:00:13 | 0          | 2017-09-07 23:00:13 |
| 172.16.3.133 | 6032 | mysql_servers | 3       | 2017-09-07 23:00:09 | 0x67E8B47695B0535F | 2017-09-07 23:00:10 | 2017-09-07 23:00:13 | 0          | 2017-09-07 23:00:13 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

Also in this case, changes are immediately propagated everywhere!

We will now change max_connections in mysql_servers, instead of simply adding nodes. This to verify that every change is propogated:

Admin130> SELECT * FROM mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 20           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 30           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
| 40           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 100             | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Admin130> UPDATE mysql_servers SET max_connections=50;
Query OK, 4 rows affected (0.00 sec)

Admin130> SELECT * FROM mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 20           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 30           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 40           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Admin130> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin130> SELECT * FROM runtime_mysql_servers;
 +--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 40           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 30           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 20           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Admin130> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
 +--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:48 | 0x59F2D71F3B77900F | 2017-09-07 23:02:48 | 2017-09-07 23:02:56 | 0          | 2017-09-07 23:02:56 |
| 172.16.3.131 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:49 | 0x59F2D71F3B77900F | 2017-09-07 23:02:49 | 2017-09-07 23:02:56 | 0          | 2017-09-07 23:02:56 |
| 172.16.3.132 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:49 | 0x59F2D71F3B77900F | 2017-09-07 23:02:49 | 2017-09-07 23:02:56 | 0          | 2017-09-07 23:02:56 |
| 172.16.3.133 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:49 | 0x59F2D71F3B77900F | 2017-09-07 23:02:49 | 2017-09-07 23:02:56 | 0          | 2017-09-07 23:02:56 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

As expected, all the changes are immediately propagated!

Verify status on another node

So far we only made changes in proxysql130, and accordng to stats_proxysql_servers_checksums the change was propagated everywhere. To be fair, to check the status of mysql_servers in other nodes we should check directly in the other nodes.
Let's check on proxysql131:

Admin131> SELECT * FROM mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 40           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 30           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 20           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:48 | 0x59F2D71F3B77900F | 2017-09-07 23:02:48 | 2017-09-07 23:10:29 | 0          | 2017-09-07 23:10:29 |
| 172.16.3.131 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:49 | 0x59F2D71F3B77900F | 2017-09-07 23:02:49 | 2017-09-07 23:10:29 | 0          | 2017-09-07 23:10:29 |
| 172.16.3.132 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:49 | 0x59F2D71F3B77900F | 2017-09-07 23:02:49 | 2017-09-07 23:10:29 | 0          | 2017-09-07 23:10:29 |
| 172.16.3.133 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:49 | 0x59F2D71F3B77900F | 2017-09-07 23:02:49 | 2017-09-07 23:10:29 | 0          | 2017-09-07 23:10:29 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

All looks good! We have confirmed that the change was successfully propagated to proxysql31.

Configuraton vs status

What I didn't mention so far (and it is important) is that the servers added in mysql_servers don't really exist:

Admin131> SELECT * FROM mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;
+--------------+------+------------------+----------------------+-------------------------------------------------------+
| hostname     | port | time_start_us    | ping_success_time_us | ping_error                                            |
+--------------+------+------------------+----------------------+-------------------------------------------------------+
| 172.16.3.142 | 3306 | 1504825997658908 | 0                    | Can't connect to MySQL server on '172.16.3.142' (107) |
| 172.16.3.141 | 3306 | 1504825997643833 | 0                    | Can't connect to MySQL server on '172.16.3.141' (107) |
| 172.16.3.142 | 3306 | 1504825937658863 | 0                    | Can't connect to MySQL server on '172.16.3.142' (107) |
| 172.16.3.141 | 3306 | 1504825937643760 | 0                    | Can't connect to MySQL server on '172.16.3.141' (107) |
| 172.16.3.142 | 3306 | 1504825877658756 | 0                    | Can't connect to MySQL server on '172.16.3.142' (107) |
| 172.16.3.141 | 3306 | 1504825877643660 | 0                    | Can't connect to MySQL server on '172.16.3.141' (107) |
| 172.16.3.142 | 3306 | 1504825817658691 | 0                    | Can't connect to MySQL server on '172.16.3.142' (107) |
| 172.16.3.141 | 3306 | 1504825817643588 | 0                    | Can't connect to MySQL server on '172.16.3.141' (107) |
| 172.16.3.142 | 3306 | 1504825757658569 | 0                    | Can't connect to MySQL server on '172.16.3.142' (107) |
| 172.16.3.141 | 3306 | 1504825757643465 | 0                    | Can't connect to MySQL server on '172.16.3.141' (107) |
+--------------+------+------------------+----------------------+-------------------------------------------------------+
10 rows in set (0.00 sec)

This makes things interesting, because the status of the servers is not what expected. In fact:

Admin131> SELECT * FROM mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 40           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 30           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 20           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Admin131> SELECT * FROM runtime_mysql_servers;
+--------------+--------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status  | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.3.141 | 3306 | SHUNNED | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 30           | 172.16.3.141 | 3306 | SHUNNED | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 40           | 172.16.3.142 | 3306 | SHUNNED | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 20           | 172.16.3.142 | 3306 | SHUNNED | 1      | 0           | 50              | 0                   | 0       | 0              |         |
+--------------+--------------+------+---------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Although all the proxies know that the status of the servers is SHUNNED, their configuration says ONLINE, and this what it was propogated to all the nodes.

Apply changes from another node

Till now, all the changes were applied from proxysql130 . To make sure that we can apply change to any node, we will now trigger a reconfiguration from proxysql131:

Admin131> SELECT * FROM mysql_servers ORDER BY hostgroup_id;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 10           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 20           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 30           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
| 40           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
4 rows in set (0.00 sec)

Admin131> DELETE FROM mysql_servers WHERE hostgroup_id > 20;
Query OK, 2 rows affected (0.00 sec)

Admin131> UPDATE mysql_servers SET comment = hostname || "--" || hostgroup_id;
Query OK, 2 rows affected (0.00 sec)

Admin131> SELECT * FROM mysql_servers ORDER BY hostgroup_id;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment          |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------+
| 10           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              | 172.16.3.141--10 |
| 20           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              | 172.16.3.142--20 |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------+
2 rows in set (0.00 sec)

Let's now apply the change:

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:48 | 0x59F2D71F3B77900F | 2017-09-07 23:02:48 | 2017-09-07 23:26:40 | 0          | 2017-09-07 23:26:40 |
| 172.16.3.131 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:49 | 0x59F2D71F3B77900F | 2017-09-07 23:02:49 | 2017-09-07 23:26:40 | 0          | 2017-09-07 23:26:40 |
| 172.16.3.132 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:49 | 0x59F2D71F3B77900F | 2017-09-07 23:02:49 | 2017-09-07 23:26:40 | 0          | 2017-09-07 23:26:40 |
| 172.16.3.133 | 6032 | mysql_servers | 4       | 2017-09-07 23:02:49 | 0x59F2D71F3B77900F | 2017-09-07 23:02:49 | 2017-09-07 23:26:40 | 0          | 2017-09-07 23:26:40 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

Admin131> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 5       | 2017-09-07 23:43:41 | 0x7F629B1EB839E7A0 | 2017-09-07 23:43:41 | 2017-09-07 23:43:42 | 0          | 2017-09-07 23:43:42 |
| 172.16.3.131 | 6032 | mysql_servers | 5       | 2017-09-07 23:43:40 | 0x7F629B1EB839E7A0 | 2017-09-07 23:43:40 | 2017-09-07 23:43:42 | 0          | 2017-09-07 23:43:42 |
| 172.16.3.132 | 6032 | mysql_servers | 5       | 2017-09-07 23:43:41 | 0x7F629B1EB839E7A0 | 2017-09-07 23:43:41 | 2017-09-07 23:43:42 | 0          | 2017-09-07 23:43:42 |
| 172.16.3.133 | 6032 | mysql_servers | 5       | 2017-09-07 23:43:41 | 0x7F629B1EB839E7A0 | 2017-09-07 23:43:41 | 2017-09-07 23:43:42 | 0          | 2017-09-07 23:43:42 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

Admin131> SELECT * FROM runtime_mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment          |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------+
| 20           | 172.16.3.142 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              | 172.16.3.142--20 |
| 10           | 172.16.3.141 | 3306 | ONLINE | 1      | 0           | 50              | 0                   | 0       | 0              | 172.16.3.141--10 |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+------------------+
2 rows in set (0.00 sec)

Apply changes from any node

So far we saw that it is possible to apply changes to any node, and the changes will be propaged everywhere.
This is extremely useful if ProxySQL is combined with any replication management software (MHA, Orchestator, Replication Manager) that can operate directly to ANY of the proxysql instance. Let's make an example...

For now we will cover a simple case in which node are added to any proxysql instance, completely randomly. To know in which node we are adding a new server, we will use hostgroup_id as a reference:

# for i in `seq 1 24` ; do I=$(($RANDOM %4)) ; echo "INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES ($I , \"172.16.4.$(($RANDOM % 100))\", $RANDOM); LOAD MYSQL SERVERS TO RUNTIME;" | mysql -u cluster1 -psecret1pass -h 172.16.3.13$I -P6032 ; sleep 5; done
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
...
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.

In the above, we run the INSERT and then executed LOAD MYSQL SERVERS TO RUNTIME.
Let's check what is the outcome in ProxySQL:

Admin131> select * from mysql_servers;
+--------------+-------------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname    | port  | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | 172.16.4.65 | 11462 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 172.16.4.48 | 26685 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 172.16.4.70 | 2227  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 172.16.4.88 | 15722 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 172.16.4.54 | 6058  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 172.16.4.50 | 7821  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 3            | 172.16.4.70 | 27899 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.4.82 | 16969 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.4.42 | 26424 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.4.44 | 235   | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.4.13 | 19296 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.4.76 | 1700  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.4.45 | 24339 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 1            | 172.16.4.28 | 16056 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.4.59 | 15986 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.4.48 | 22938 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.4.25 | 14090 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.4.76 | 28533 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.4.78 | 14302 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 2            | 172.16.4.58 | 22086 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.9  | 7954  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.77 | 16101 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.60 | 9564  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.73 | 2161  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-------------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
24 rows in set (0.00 sec)

Admin131> SELECT hostgroup_id, COUNT(*) FROM mysql_servers GROUP BY hostgroup_id;
+--------------+----------+
| hostgroup_id | COUNT(*) |
+--------------+----------+
| 0            | 5        |
| 1            | 7        |
| 2            | 6        |
| 3            | 6        |
+--------------+----------+
4 rows in set (0.00 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 48      | 2017-09-08 01:05:15 | 0xC7D49F8E6CAB7D29 | 2017-09-08 01:05:15 | 2017-09-08 01:06:18 | 0          | 2017-09-08 01:06:18 |
| 172.16.3.131 | 6032 | mysql_servers | 48      | 2017-09-08 01:05:15 | 0xC7D49F8E6CAB7D29 | 2017-09-08 01:05:15 | 2017-09-08 01:06:18 | 0          | 2017-09-08 01:06:18 |
| 172.16.3.132 | 6032 | mysql_servers | 48      | 2017-09-08 01:05:14 | 0xC7D49F8E6CAB7D29 | 2017-09-08 01:05:14 | 2017-09-08 01:06:18 | 0          | 2017-09-08 01:06:18 |
| 172.16.3.133 | 6032 | mysql_servers | 47      | 2017-09-08 01:05:15 | 0xC7D49F8E6CAB7D29 | 2017-09-08 01:05:15 | 2017-09-08 01:06:18 | 0          | 2017-09-08 01:06:18 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

All mysql servers (24 servers) were added randomly in any of the proxysql nodes, and all the nodes are now in sync.

Why we added a sleep 5 while inserting new mysql servers?
Because we are inserting them completely randomly (write anywhere), if we write too fast the proxysql instances won't have the time to converge, and this will easily result in conflicting configuration and inability to synchronize.
This limitation is caused by the fact that conflict resolution is timestamp based (for now).

Instead, if we use only one writer, we can make changes faster.
For example, we can push changed every 2 seconds:

# mysql -u cluster1 -psecret1pass -h 172.16.3.130 -P6032 -e "DELETE FROM mysql_servers; LOAD MYSQL SERVERS TO RUNTIME;" ; sleep 2
mysql: [Warning] Using a password on the command line interface can be insecure.
# for i in `seq 1 24` ; do echo "INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,  \"172.16.4.$(($RANDOM % 100))\", $RANDOM); LOAD MYSQL SERVERS TO RUNTIME;" | mysql -u cluster1 -psecret1pass -h 172.16.3.130 -P6032 ; sleep 2 ; done 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
...
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.

Let's check what is the outcome in ProxySQL:

Admin131> SELECT * FROM mysql_servers;
+--------------+-------------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname    | port  | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-------------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 0            | 172.16.4.46 | 7586  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.35 | 24065 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.94 | 7210  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.85 | 23401 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.83 | 22379 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.18 | 30858 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.31 | 25929 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.87 | 23052 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.9  | 15909 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.72 | 13013 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.31 | 6956  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.9  | 17246 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.29 | 25152 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.23 | 19281 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.48 | 5552  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.11 | 30345 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.28 | 4574  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.82 | 18989 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.69 | 336   | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.63 | 28288 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.50 | 1081  | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.2  | 17660 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.75 | 26555 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 0            | 172.16.4.76 | 17480 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+-------------+-------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
24 rows in set (0.00 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 123     | 2017-09-08 01:27:13 | 0x3DF39DBA39C3BB12 | 2017-09-08 01:27:13 | 2017-09-08 01:28:48 | 0          | 2017-09-08 01:28:48 |
| 172.16.3.131 | 6032 | mysql_servers | 111     | 2017-09-08 01:27:14 | 0x3DF39DBA39C3BB12 | 2017-09-08 01:27:14 | 2017-09-08 01:28:48 | 0          | 2017-09-08 01:28:48 |
| 172.16.3.132 | 6032 | mysql_servers | 111     | 2017-09-08 01:27:14 | 0x3DF39DBA39C3BB12 | 2017-09-08 01:27:14 | 2017-09-08 01:28:48 | 0          | 2017-09-08 01:28:48 |
| 172.16.3.133 | 6032 | mysql_servers | 110     | 2017-09-08 01:27:14 | 0x3DF39DBA39C3BB12 | 2017-09-08 01:27:14 | 2017-09-08 01:28:48 | 0          | 2017-09-08 01:28:48 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

Also in this case, all mysql servers (24 servers) were added, and all the nodes are now in sync.

Relaxing converge time

In the previous section we show that because of timestamp conflict resolution:

  • using multiple writers, we need to give the cluster some time to converge
  • using a single writer, converge time is more efficient/faster , but we should not go lower than 2 seconds

Why 2 seconds?

  • conflict resolution is timestamp based, with 1 second resolution
  • cluster_check_interval_ms is set to 200, and cluster_mysql_servers_diffs_before_sync is set to 3 : if within 800ms a new configuration is available, nodes start synchronizing
  • if a new configuration is applied while nodes are synchronizing, they may end in an inconsistent state

Therefore the safest way to apply changes faster it to defer the synchronization.
For example, if we do not synchronize within 3 seconds, we can make changes to a single writer a lot faster.
Note that synchronization after 3 seconds is the default, as cluster_check_interval_ms=1000 by default.

Let's set cluster_check_interval_ms=1000:

# for i in 130 131 132 133 ; do mysql -u cluster1 -psecret1pass -h 172.16.3.$i -P6032  -e "SET admin-cluster_check_interval_ms=1000; LOAD ADMIN VARIABLES TO RUNTIME; SAVE ADMIN VARIABLES TO DISK;" ; done
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.

Next we will apply 100 changes in a single node (proxysql130) at 0.2s interval:

# mysql -u cluster1 -psecret1pass -h 172.16.3.130 -P6032 -e "DELETE FROM mysql_servers; LOAD MYSQL SERVERS TO RUNTIME;"
mysql: [Warning] Using a password on the command line interface can be insecure.
# time for i in `seq 1 100` ; do echo "INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES (0,  \"172.16.4.$(($RANDOM % 100))\", $RANDOM); LOAD MYSQL SERVERS TO RUNTIME;" | mysql -u cluster1 -psecret1pass -h 172.16.3.130 -P6032 ; sleep 0.2 ; done 2> /dev/null 

real    0m20.597s
user    0m0.028s
sys     0m0.020s

Checking the status of the cluster in another node (proxysql131):

Admin131> SELECT COUNT(*) FROM mysql_servers;
+----------+
| COUNT(*) |
+----------+
| 100      |
+----------+
1 row in set (0.00 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 170     | 2017-09-08 02:00:34 | 0x44C5D7E1E01EDB71 | 2017-09-08 02:00:34 | 2017-09-08 02:00:59 | 0          | 2017-09-08 02:01:00 |
| 172.16.3.131 | 6032 | mysql_servers | 7       | 2017-09-08 02:00:37 | 0x44C5D7E1E01EDB71 | 2017-09-08 02:00:38 | 2017-09-08 02:00:59 | 0          | 2017-09-08 02:01:00 |
| 172.16.3.132 | 6032 | mysql_servers | 6       | 2017-09-08 02:00:38 | 0x44C5D7E1E01EDB71 | 2017-09-08 02:00:38 | 2017-09-08 02:00:59 | 0          | 2017-09-08 02:01:00 |
| 172.16.3.133 | 6032 | mysql_servers | 6       | 2017-09-08 02:00:38 | 0x44C5D7E1E01EDB71 | 2017-09-08 02:00:38 | 2017-09-08 02:00:59 | 0          | 2017-09-08 02:01:00 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

What is interesting to note from the above output is:

  • epoch on proxysql130 is 3-4 seconds less than the other nodes: in other words, the other nodes synched with 3-4 seconds delay
  • version on proxysql130 is way higher than the other nodes: this means that while proxysql130 was changing its configuration quickly, the other nodes weren't syncing: this because they wait 3 seconds from the last change before syncing.

Going back to replication management software (MHA, Orchestator, Replication Manager): no matter how quickly proxysql nodes try to synchronize, if the replication management applies the same configuration change to one or more proxysql nodes, the whole cluster will converge.
This makes integration a lot easier: replication management tools need to apply the change to one or more proxysql nodes, and proxysql nodes will do the rest.

ProxySQL Cluster and mysql_replication_hostgroups

ProxySQL Cluster supports also mysql_replication_hostgroups.

In one node we check that the table is empty:

Admin131> SELECT * FROM mysql_replication_hostgroups;
Empty set (0.00 sec)

In another node we add a row:

Admin130> INSERT INTO mysql_replication_hostgroups VALUES (10,20,'my test'); 
Query OK, 1 row affected (0.00 sec)

Admin130> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.00 sec)

And after few seconds, the other nodes did synchronize:

Admin131> SELECT * FROM mysql_replication_hostgroups;
+------------------+------------------+---------+
| writer_hostgroup | reader_hostgroup | comment |
+------------------+------------------+---------+
| 10               | 20               | my test |
+------------------+------------------+---------+
1 row in set (0.00 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;                                                                                                                                                                                                                                                             +--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 171     | 2017-09-08 02:45:42 | 0xED2038D16C1B903A | 2017-09-08 02:45:42 | 2017-09-08 02:46:50 | 0          | 2017-09-08 02:46:51 |
| 172.16.3.131 | 6032 | mysql_servers | 8       | 2017-09-08 02:45:45 | 0xED2038D16C1B903A | 2017-09-08 02:45:46 | 2017-09-08 02:46:51 | 0          | 2017-09-08 02:46:51 |
| 172.16.3.132 | 6032 | mysql_servers | 7       | 2017-09-08 02:45:45 | 0xED2038D16C1B903A | 2017-09-08 02:45:45 | 2017-09-08 02:46:50 | 0          | 2017-09-08 02:46:51 |
| 172.16.3.133 | 6032 | mysql_servers | 7       | 2017-09-08 02:45:45 | 0xED2038D16C1B903A | 2017-09-08 02:45:45 | 2017-09-08 02:46:50 | 0          | 2017-09-08 02:46:51 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

Let's now add a server where mysqld is running:

Admin131> DELETE FROM mysql_servers;
Query OK, 100 rows affected (0.00 sec)

Admin131> INSERT INTO mysql_servers (hostgroup_id, hostname) VALUES (20,'172.16.3.130');
Query OK, 1 row affected (0.00 sec)

Admin131> LOAD MYSQL SERVERS TO RUNTIME;
Query OK, 0 rows affected (0.01 sec)

Admin131> SELECT * FROM mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 20           | 172.16.3.130 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
1 row in set (0.00 sec)

Admin131> SELECT * FROM runtime_mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 20           | 172.16.3.130 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
1 row in set (0.00 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 172     | 2017-09-08 02:48:46 | 0x6A3E4DBEA66A8BC2 | 2017-09-08 02:48:47 | 2017-09-08 02:48:57 | 0          | 2017-09-08 02:48:58 |
| 172.16.3.131 | 6032 | mysql_servers | 9       | 2017-09-08 02:48:43 | 0x6A3E4DBEA66A8BC2 | 2017-09-08 02:48:44 | 2017-09-08 02:48:58 | 0          | 2017-09-08 02:48:58 |
| 172.16.3.132 | 6032 | mysql_servers | 8       | 2017-09-08 02:48:47 | 0x6A3E4DBEA66A8BC2 | 2017-09-08 02:48:47 | 2017-09-08 02:48:57 | 0          | 2017-09-08 02:48:58 |
| 172.16.3.133 | 6032 | mysql_servers | 8       | 2017-09-08 02:48:47 | 0x6A3E4DBEA66A8BC2 | 2017-09-08 02:48:47 | 2017-09-08 02:48:57 | 0          | 2017-09-08 02:48:58 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

Next we enable access to the mysql servers to the monitor user, that is now able to check read_only. Because the server has read_only=0, the proxies will auto-reconfigure the server in the right hostgroup(s):

Admin131> SELECT * FROM runtime_mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 20           | 172.16.3.130 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
| 10           | 172.16.3.130 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.01 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 173     | 2017-09-08 02:52:16 | 0x5E4091B1EB54AB00 | 2017-09-08 02:52:16 | 2017-09-08 02:52:39 | 0          | 2017-09-08 02:52:40 |
| 172.16.3.131 | 6032 | mysql_servers | 10      | 2017-09-08 02:52:17 | 0x5E4091B1EB54AB00 | 2017-09-08 02:52:17 | 2017-09-08 02:52:40 | 0          | 2017-09-08 02:52:40 |
| 172.16.3.132 | 6032 | mysql_servers | 9       | 2017-09-08 02:52:16 | 0x5E4091B1EB54AB00 | 2017-09-08 02:52:17 | 2017-09-08 02:52:39 | 0          | 2017-09-08 02:52:40 |
| 172.16.3.133 | 6032 | mysql_servers | 9       | 2017-09-08 02:52:16 | 0x5E4091B1EB54AB00 | 2017-09-08 02:52:17 | 2017-09-08 02:52:39 | 0          | 2017-09-08 02:52:40 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

What happened here?

  • version has increased, that means that each proxysql instance has executed a re-configuration
  • epochs are not 3 seconds apart, so maybe there was no resync between the nodes

Let's check the log of one of the node (proxysql131) to see what really happened:

2017-09-08 03:52:16 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.130:6032, version 173, epoch 1504839136, checksum 0x5E4091B1EB54AB00 . Not syncing yet ...
2017-09-08 03:52:17 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2017-09-08 03:52:17 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: SHUNNED , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2017-09-08 03:52:17 MySQL_HostGroups_Manager.cpp:971:generate_mysql_replication_hostgroups_table(): [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 10 , reader_hostgroup: 20, my test
2017-09-08 03:52:17 MySQL_HostGroups_Manager.cpp:1007:generate_mysql_group_replication_hostgroups_table(): [INFO] New mysql_group_replication_hostgroups table
2017-09-08 03:52:17 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2017-09-08 03:52:17 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.131:6032, version 10, epoch 1504839137, checksum 0x5E4091B1EB54AB00 . Not syncing yet ...
2017-09-08 03:52:17 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.131:6032 matches with local checksum 0x5E4091B1EB54AB00 , we won't sync.
2017-09-08 03:52:17 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.133:6032, version 9, epoch 1504839136, checksum 0x5E4091B1EB54AB00 . Not syncing yet ...
2017-09-08 03:52:17 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.133:6032 matches with local checksum 0x5E4091B1EB54AB00 , we won't sync.
2017-09-08 03:52:17 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.132:6032, version 9, epoch 1504839136, checksum 0x5E4091B1EB54AB00 . Not syncing yet ...
2017-09-08 03:52:17 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.132:6032 matches with local checksum 0x5E4091B1EB54AB00 , we won't sync.
2017-09-08 03:52:28 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 

The steps listed in this log are:

  • proxysql131 detects that proxysql130 has a different checksum, but won't sync yet because it needs to wait cluster_check_interval_ms times cluster_mysql_servers_diffs_before_sync
  • Monitor detects that mysqld on 172.16.3.130 is alive, and bring it online
  • it also detect that is writable, so it configures 172.16.3.130 on HG10 and HG20
  • Custer detects that the other nodes have a new checksum, but it matches with its own, so it won't sync.

Let's change read_only=1, and then we check the status in proxyql131:

Admin131> SELECT * FROM runtime_mysql_servers;
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 20           | 172.16.3.130 | 3306 | ONLINE | 1      | 0           | 1000            | 0                   | 0       | 0              |         |
+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
1 row in set (0.00 sec)

Admin131> SELECT hostname, port, name, version, FROM_UNIXTIME(epoch) epoch, checksum, FROM_UNIXTIME(changed_at) changed_at, FROM_UNIXTIME(updated_at) updated_at, diff_check, DATETIME('NOW') FROM stats_proxysql_servers_checksums WHERE name='mysql_servers' ORDER BY hostname;
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| hostname     | port | name          | version | epoch               | checksum           | changed_at          | updated_at          | diff_check | DATETIME('NOW')     |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
| 172.16.3.130 | 6032 | mysql_servers | 174     | 2017-09-08 03:09:40 | 0x6A3E4DBEA66A8BC2 | 2017-09-08 03:09:41 | 2017-09-08 03:10:02 | 0          | 2017-09-08 03:10:02 |
| 172.16.3.131 | 6032 | mysql_servers | 11      | 2017-09-08 03:09:40 | 0x6A3E4DBEA66A8BC2 | 2017-09-08 03:09:40 | 2017-09-08 03:10:01 | 0          | 2017-09-08 03:10:02 |
| 172.16.3.132 | 6032 | mysql_servers | 10      | 2017-09-08 03:09:40 | 0x6A3E4DBEA66A8BC2 | 2017-09-08 03:09:40 | 2017-09-08 03:10:02 | 0          | 2017-09-08 03:10:02 |
| 172.16.3.133 | 6032 | mysql_servers | 10      | 2017-09-08 03:09:40 | 0x6A3E4DBEA66A8BC2 | 2017-09-08 03:09:40 | 2017-09-08 03:10:02 | 0          | 2017-09-08 03:10:02 |
+--------------+------+---------------+---------+---------------------+--------------------+---------------------+---------------------+------------+---------------------+
4 rows in set (0.00 sec)

Because mysqld on 172.16.3.130 now has read_only=1, the server is listed only on HG20 (not HG10), and it seems that all the proxysql instances have the same configuration.
Did they sync? The answer is no. Checking the log:

2017-09-08 04:09:40 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.133:6032, version 10, epoch 1504840180, checksum 0x6A3E4DBEA66A8BC2 . Not syncing yet ...
2017-09-08 04:09:40 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.132:6032, version 10, epoch 1504840180, checksum 0x6A3E4DBEA66A8BC2 . Not syncing yet ...
2017-09-08 04:09:40 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2017-09-08 04:09:40 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2017-09-08 04:09:40 MySQL_HostGroups_Manager.cpp:564:commit(): [WARNING] Removed server at address 140487914770816, hostgroup 10, address 172.16.3.130 port 3306. Setting status OFFLINE HARD and immediately dropping all free connections. Used connections will be dropped when trying to use them
2017-09-08 04:09:40 MySQL_HostGroups_Manager.cpp:971:generate_mysql_replication_hostgroups_table(): [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 10 , reader_hostgroup: 20, my test
2017-09-08 04:09:40 MySQL_HostGroups_Manager.cpp:1007:generate_mysql_group_replication_hostgroups_table(): [INFO] New mysql_group_replication_hostgroups table
2017-09-08 04:09:40 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
HID: 10 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: OFFLINE_HARD , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 
2017-09-08 04:09:40 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.131:6032, version 11, epoch 1504840180, checksum 0x6A3E4DBEA66A8BC2 . Not syncing yet ...
2017-09-08 04:09:40 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.131:6032 matches with local checksum 0x6A3E4DBEA66A8BC2 , we won't sync.
2017-09-08 04:09:41 ProxySQL_Cluster.cpp:345:set_checksums(): [INFO] Cluster: detected a new checksum for mysql_servers from peer 172.16.3.130:6032, version 174, epoch 1504840180, checksum 0x6A3E4DBEA66A8BC2 . Not syncing yet ...
2017-09-08 04:09:41 ProxySQL_Cluster.cpp:347:set_checksums(): [INFO] Cluster: checksum for mysql_servers from peer 172.16.3.130:6032 matches with local checksum 0x6A3E4DBEA66A8BC2 , we won't sync.
2017-09-08 04:09:57 MySQL_HostGroups_Manager.cpp:880:generate_mysql_servers_table(): [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 20 , address: 172.16.3.130 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 0 , use_ssl: 0 , max_latency_ms: 0 , comment: 

What this log shows is:

  • Cluster detects a configuration change in proxysql132 and proxysql133 (they auto-reconfigured quickly)
  • Monitor detects that 172.16.3.130 is read-only, and triggers an auto-reconfiguration
  • 172.16.3.130 is removed from HG10
  • Cluster module detects that also proxysql130 and proxysql131 have a new checksum, but is identical to its own checksum so it won't sync

Skipping unnecessary synchronizaton

Why the test with mysql_replication_hostgroups is interesting?
If nodes in a cluster are re-configured (explicitly or implicitly) in the same way and synchronization is not too aggressive, the nodes themselves can understand if they needs to synchonize or not.
That also means that if a monitoring script executed by the local scheduler or a built-in monitoring triggers the same re-configuration, proxysql instances do not need to synchronize, and will not. In other words, if tuned correctly Cluster re-configuration can works together with local re-configuration without creating conflicts.

Conclusion

In this third blog post about ProxySQL Cluster we showed how this solution allows adding and removing backends (MySQL servers) either always on a single ProxySQL node, or on any random ProxySQL node (write anywhere). We also pointed some limitations, mostly the fact that conflict resolution is timestamp based.
These limitations will be address in future, adding further features like quorum and master/leader election.