Skip to content

ProxysSQL Cluster

Jump to bottom
René Cannaò edited this page Aug 23, 2017 · 5 revisions

Those features are EXPERIMENTAL and subject to changes, especially because not all the features in the roadmap are implemented yet.

Preface

ProxySQL is a decentralized proxy, and it is normally adviced to deploy it close to the application. This approach seems to scale pretty well to hundred(s) of nodes, as it was designed to be easily reconfigurable at runtime.
This allows to coordinate and reconfigure a farm of ProxySQL instances using a configuration management software like Ansible/Chef/Puppet/Salt (in alphabetical order), or a service discovery software like Etcd/Consul/ZooKeeper.
This allows ProxySQL to be highly customizable, and be adopted in any setup using any of these technologies, or even re-configurable by home-made tools.
Although it has some drawback too:

  • it requires and relies on external software (configuration management software itself)
  • the previous point means also that such approach it is not natively supported
  • converge time it is not predictable
  • there is no protection against network split

For this reason, ProxySQL 1.4.x attempts to support clustering natively. As pointed already, these features are EXPERIMENTAL and subject to changes, especially because not all the features in the roadmap are implemented yet.

Currently there are two main components in the ProxySQL clustering solution:

  • monitoring (implemented for many modules)
  • re-configuration (not implemented yet)

More will be added in future, see roadmap.

Monitoring

To support Cluster Monitoring, several new tables, commands and variables were introduced.

Admin variables

  • admin-checksum_mysql_query_rules: boolean variable. When true (default) ProxySQL generates a new configuration checksum every time LOAD MYSQL QUERY RULES TO RUNTIME is executed;
  • admin-checksum_mysql_servers: boolean variable. When true (default) ProxySQL generates a new configuration checksum every time LOAD MYSQL SERVERS TO RUNTIME is executed;
  • admin-checksum_mysql_users: boolean variable. When true (default) ProxySQL generates a new configuration checksum every time LOAD MYSQL USERS TO RUNTIME is executed. If you have millions of users, disable this feature and do not rely on it, as it may be very slow;
  • admin-cluster_username and admin-cluster_password: to monitor other proxysql instances this credential is used. Note that the pair username/password should also be present in admin-admin_credentials, or connection will fail. If admin-cluster_username is not defined, Clustering doesn't perform any check;
  • admin-cluster_check_interval_ms : this variable defines the interval between checksums checks.
    default: 1000. Min: 10 , Max: 300000
  • admin-cluster_check_status_frequency : if greater than 0, this variable defines after how many checksums checks a status check is performed.
    default: 10. Min: 0 , Max: 10000

Configuration tables

Table proxysql_servers

Table definition:

CREATE TABLE proxysql_servers (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 6032,
    weight INT CHECK (weight >= 0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostname, port) )

This table is a configuration table, and defines a list of ProxySQL peers.

  • hostname : peer's hostname/IP
  • port : peer's port
  • weight : currently unused, but in the roadmap for future enhancements
  • comment : free form comment field

Note:

  • configuration file doesn't support this table yet!
  • because this feature is still experimental, the table is not automatically loaded from disk

Table runtime_proxysql_servers

Table definition:

CREATE TABLE runtime_proxysql_servers (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 6032,
    weight INT CHECK (weight >= 0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    PRIMARY KEY (hostname, port) )

Like other runtime_ tables, this is a runtime representation of the base table: proxysql_servers .

Table runtime_checksums_values

Table definition:

CREATE TABLE runtime_checksums_values (
    name VARCHAR NOT NULL,
    version INT NOT NULL,
    epoch INT NOT NULL,
    checksum VARCHAR NOT NULL,
    PRIMARY KEY (name))

Table runtime_checksums_values is the first runtime_ table that is not the runtime representation of a base table. Table runtime_checksums_values shows information of when a LOAD TO RUNTIME command was executed:

  • name : name of the module
  • version : how many times LOAD TO RUNTIME was executed, either explicitly or not (executed internal due to some other event)
  • epoch : timestamp of when LOAD TO RUNTIME was executed
  • checksum : the checksum of the internal memory structure resulting from LOAD TO RUNTIME

Example:

Admin> SELECT * FROM runtime_checksums_values;
+-------------------+---------+------------+--------------------+
| name              | version | epoch      | checksum           |
+-------------------+---------+------------+--------------------+
| admin_variables   | 0       | 0          |                    |
| mysql_query_rules | 5       | 1503442167 | 0xD3BD702F8E759B1E |
| mysql_servers     | 1       | 1503440533 | 0x6F8CEF0F4BD6456E |
| mysql_users       | 1       | 1503440533 | 0xF8BDF26C65A70AC5 |
| mysql_variables   | 0       | 0          |                    |
| proxysql_servers  | 2       | 1503442214 | 0x89768E27E4931C87 |
+-------------------+---------+------------+--------------------+
6 rows in set (0,00 sec)

Note:
Only 4 of the 6 modules generate a checksum for now. LOAD (ALL|ADMIN|MYSQL) VARIABLES TO RUNTIME doesn't generate a checksum yet.

New commands:

  • LOAD PROXYSQL SERVERS FROM MEMORY / LOAD PROXYSQL SERVERS TO RUNTIME
    loads ProxySQL servers from the in-memory database to the runtime data structures
  • SAVE PROXYSQL SERVERS TO MEMORY / SAVE PROXYSQL SERVERS FROM RUNTIME
    persists the ProxySQL Servers from the runtime data structures to the in-memory database
  • LOAD PROXYSQL SERVERS TO MEMORY / LOAD PROXYSQL SERVERS FROM DISK
    loads ProxySQL Servers from the on-disk database to the in-memory database
  • SAVE PROXYSQL SERVERS FROM MEMORY / SAVE PROXYSQL SERVERS TO DISK
    persists the ProxySQL Servers from the in-memory database to the on-disk database

stats tables

3 new tables were added in stats schema

Table stats_proxysql_servers_checksums

Table definition:

Admin> SHOW CREATE TABLE stats.stats_proxysql_servers_checksums\G
*************************** 1. row ***************************
       table: stats_proxysql_servers_checksums
Create Table: CREATE TABLE stats_proxysql_servers_checksums (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 6032,
    name VARCHAR NOT NULL,
    version INT NOT NULL,
    epoch INT NOT NULL,
    checksum VARCHAR NOT NULL,
    changed_at INT NOT NULL,
    updated_at INT NOT NULL,
    diff_check INT NOT NULL,
    PRIMARY KEY (hostname, port, name) )
1 row in set (0,00 sec)

This table shows the checksum of other proxies, and their status:

  • hostname : hostname of the peer
  • port : port of the peer
  • name : name of the module as reported in peer's runtime_checksums_values
  • version : version of checksum's module as reported in peer's runtime_checksums_values
  • epoch : epoch of the checksum's module as reported in peer's runtime_checksums_values
  • checksum : the checksum's module as reported in peer's runtime_checksums_values
  • changed_at : the timestamp of when a checksum change was detected
  • updated_at : the timestamp of when this entry was last refreshed
  • diff_check : a counter that defines for how many checks the checksum of the remote peer's was different than the local checksum. The reconfiguration algorithm will wait a threshold to be reached before triggering a reconfiguration. This is useful in case the same configuration is applied to multiple proxies at the same time, or when proxies are reconfiguring themselves in case of a failover and they will likely converge without the need of resync.

Table stats_proxysql_servers_metrics

Table definition:

Admin> SHOW CREATE TABLE stats.stats_proxysql_servers_metrics\G
*************************** 1. row ***************************
       table: stats_proxysql_servers_metrics
Create Table: CREATE TABLE stats_proxysql_servers_metrics (
    hostname VARCHAR NOT NULL,
    port INT NOT NULL DEFAULT 6032,
    weight INT CHECK (weight >= 0) NOT NULL DEFAULT 0,
    comment VARCHAR NOT NULL DEFAULT '',
    response_time_ms INT NOT NULL,
    Uptime_s INT NOT NULL,
    last_check_ms INT NOT NULL,
    Queries INT NOT NULL,
    Client_Connections_connected INT NOT NULL,
    Client_Connections_created INT NOT NULL,
    PRIMARY KEY (hostname, port) )
1 row in set (0,00 sec)

This table shows some of the metrics that are retrieved when the clustering module executes SHOW MYSQL STATUS in its peers. Columns:

  • hostname : hostname of the peer
  • port : port of the peer
  • weight : same as reported in proxysql_servers.weight
  • comment : same as reported in proxysql_servers.comment
  • response_time_ms : response time while running SHOW MYSQL STATUS, in millisecond
  • Uptime_s : peer's uptime in second
  • last_check_ms : age of the last time a check was executed, in millisecond
  • Queries : number of queries executed by the peer
  • Client_Connections_connected : number of client's connections connected
  • Client_Connections_created : number of client's connections created

Note:
All the status variables are retrieved by the peers, but only few are monitored to be able to check if the peer is up and running and processing traffic.

Table stats_proxysql_servers_status

Currently unused

Bandwidth consideration

In the architecture described above, all nodes monitor all the other nodes. A fully mesh peer-to-peer network.
To reduce network usage, nodes do not always exchange the whole list of checksum: instead the exchange a single checksum resulting from combining all the versions and all the checksums. It this global checksum changed, a detailed list of checksums is retrieved.
Using this technique, a 200 nodes cluster monitoring each other every 1000ms, requires a bandwidth of 50KBpb in/out to/from each node.

Re-configuration

To be implemented

Roadmap

This is an overview of the features related to clustering, and no a complete list. Implementation may be different than what listed right now:

  • support for master election: the word master was intentionally chosen instead of leader
  • only master proxy is writable/configurable
  • implementation of MySQL-like replication from master to slaves, allowing to push configuration in real-time instead pulling it
  • implementation of MySQL-like replication from master to candidate-masters
  • implementation of MySQL-like replication from candidate-masters to slaves
  • creation of a quorum with only candidate-masters: normal slaves are not part of the quorum

 

Clone this wiki locally