1. Understand ProxySQL’s role
So we have a running MariaDB Galera Cluster that syncs data with each other. What more do we need? Well, consider the following:
- There is no load balancing in place to ensure high availability.
- There is no single point of contact for your web application to handle fail-over.
- There is no monitoring in place to potentially notify you if a node is down / unreachable.
- There is no read/write split for the nodes.
- There is no auto-recovery for minor issues such as when the Maria DB service on a node is accidentally stopped or the service does not start after a reboot.
- There is no database sharding – this is useful for large databases where you can divide the traffic based on some criteria – Proxy SQL supports user / schema / data-based sharding.
2. Advantages of ProxySQL
- It provides active-active multi-master topology.
- You can read and write to any DB cluster node based on your settings.
- It has an automatic node joining / scaling mechanism.
- Automatic membership control inc. removal of a dropped or failing node from the cluster.
- Has true parallel replication (on row level).
- It allows for multi ProxySQL cluster deployment to allow single point of failure.
- Multi layer configuration system (see ProxySQL’s documentation).
- Runtime – Effective (current) configuration for ProxySQL.
- Memory – Altered when making modifications on the command-line
- Disk – used for persistent configuration changes
3. Any disadvantages of ProxySQL?
- Typically the service is installed on the application level or separate nodes – this means that there are more services in VMs / containers to look after and administer.
- Higher latency than direct client connections – see this series of tests performed by Peter Zaitsev.
- While it is essentially another MySQL service to manage, it takes a bit of time of getting used to, some learning curve is required.
- The clustering of ProxySQL has a few limitations:
- Converge time is not predictable (typically very fast, though).
- There is no protection against split-brain (network partition).
- Variables sync is not enabled between nodes.
- It uses floating IP (VIP) with a TCP keep-alive mechanism (some may consider it basic, as it does not check for other potential issues and remedies).
4. Installation of the ProxySQL service (Debian / Ubuntu)
- As mentioned above, the service should run on nodes that are separate from the Galera cluster – either on the application level (e.g. with your Apache / Nginx or other web server) OR as separate nodes (could be containers).
IN PROGRESS
10. Diving deeper into how the ProxySQL monitoring service works.
- There are two modules that take care of the status of the backends: the Hostgroup Manager & the Monitor.
- See below for an insightful reference into how these two modules work together in detecting a node failure:
Source: Google Group public discussion forum
The Hostgroup Manager is responsible for managing the connection pool, and is able to detect a faulty backend while running queries.
If a backend generates too many errors, it is shunned for some time (10 seconds by default).
After this time passes, it is flagged again as ONLINE, that means it will try again to connect to it (this specifically answer your question).
Simplifying, the Hostgroup Manager:
* shuns a node when it generates errors
* brings it back online after some time
The Hostgroup Manager doesn’t perform any background health check: it doesn’t really need to, because is able to detect failures in real time.
Although, background check is performed by the Monitor.
If the Monitor detects that a node is down, it informs the Hostgroup Manager to shun it and immediately kill all the connections.
Monitor will never inform the Hostgroup Manager that a node is healthy.
That add some information to your question: Hostgroup Manager is the only responsible to bring a node online: it tries to communicate with it.
It is interesting to understand why Monitor exists, if Hostgroup Manager is the main module that shun and re-enable a node.
Hostgroup Manager can detect if a connection is broken most of the time, but there is one exception to this: if a query is sent to an already established connection and a network issue happens while running the query, proxysql doesn’t know if the backend is still processing the query or if there is an network issue.
This is why Monitor check the status of the backends with its own intervals and timeouts, and if pings are failing will inform the Hostgroup Manager that something is wrong with the backend and that all connections should be terminated immediately.
This site uses Akismet to reduce spam. Learn how your comment data is processed.
Leave a Reply