ProxySQL Support and Consulting
ProxySQL is a routing layer that sits in front of the database: it separates read and write traffic without changing the application's connection string, lowers the connection count, and moves traffic to a working node at the moment of failover.
We design and operate the ProxySQL layer
The value of ProxySQL lies not in installing it but in designing the query rules correctly. A single badly written rule sends a write query to a replica and breaks the application silently.
Connection pooling and multiplexing
We serve thousands of connections from the application servers over a few dozen connections to the database, by multiplexing them. On systems pressed against the max_connections limit, this opens room without growing the hardware.
Read and write splitting
We separate traffic into hostgroups and design the query rules (mysql_query_rules) on query patterns and digests. Queries needing read-after-write consistency are marked separately, so replication lag does not reach the application.
Routing during failover
The monitor module continuously watches the reachability of the backend nodes and their read_only flag. When the primary changes, the writer hostgroup is repointed; the application's connection string does not change.
Query rules and traffic control
Query rewriting, result caching, rate limiting and blocking specific query patterns are all configured at the ProxySQL layer. A query threatening production can be stopped without waiting for an application release.
What ProxySQL does, and what it does not
ProxySQL is a routing layer. It holds no data, performs no replication, and provides no high availability on its own. Availability is provided by the cluster architecture behind it; ProxySQL is that architecture’s application-facing side.
The mistaken expectation we meet most often in the field is that installing ProxySQL will remove outages by itself. Putting ProxySQL in front of a single-node MySQL server changes nothing when that server becomes unreachable. In front of a cluster that already exists, on the other hand, ProxySQL is the component that determines how many errors the application receives at the moment of failover.
The connection count limits the database before the hardware does
Each application server opens its own connection pool. Ten application servers,
each with a fifty-connection pool, have opened five hundred connections to the
database. The database allocates memory for every connection; as the number of
concurrently running threads rises, the management cost starts to outgrow the work
itself. The picture that appears once the max_connections limit is reached is a
familiar one: the server’s resources sit idle, and no new connection is accepted.
ProxySQL gathers those connections at a single point and reaches the backend with far fewer. The same backend connection is shared between queries arriving in turn β that behaviour is called multiplexing.
Multiplexing does not work in every case, and that has to be known before the
installation. On connections carrying session state β an open transaction, a
temporary table, a user variable, or a SET command run at session level β
ProxySQL has to keep the connection bound to that session; otherwise the query
runs in the wrong context. A ProxySQL built without measuring how often the
application uses those patterns does not deliver the expected gain. We take that
measurement before the installation and state the expected gain as a number.
Query rules: the real work in a ProxySQL installation
Routing is done through backend groups called hostgroups. In a typical
installation the primary sits in one hostgroup and the read replicas are gathered
in another. Which query goes to which group is determined by the rules in the
mysql_query_rules table; rules are matched on the query text or on the query’s
digest value, and evaluated in order.
Designing the rule set correctly takes far more effort than the installation itself. Three points have to be understood from the start:
- Every query inside an open transaction has to stay on the primary. A
SELECTthat lands on a replica mid-transaction cannot see changes that have not yet been sent. SELECT ... FOR UPDATEis not a read query. It takes a lock and has to go to the primary. A rule set that looks only at whether the query starts withSELECTsends this one to a replica.- Patterns needing read-after-write consistency have to be marked. An application that reads a record immediately after updating it will see data as stale as the replication lag, if that read lands on a replica.
As an additional measure against the third point, we configure the setup to take replicas whose lag exceeds a defined threshold out of the hostgroup. A lagging node stops receiving traffic by itself, and returns once the lag closes.
Query rules are not used only for routing. Query rewriting, result set caching and the outright blocking of specific query patterns are configured at the same layer. Being able to stop a query that threatens production without waiting for an application release is one of the most useful capabilities in incident response.
ProxySQL in cluster architectures
ProxySQL watches the topology behind it not with a generic health check but by reading that architecture’s own state information.
On Galera Cluster and Percona XtraDB Cluster installations the
mysql_galera_hostgroups configuration is used. The joining and synchronisation
state of the nodes is watched, and a node that has not finished synchronising
receives no traffic. In these architectures, which can run multi-writer,
concentrating write traffic on a single node reduces certification conflicts
markedly β and ProxySQL does that without the application noticing.
On Group Replication and InnoDB Cluster installations the
mysql_group_replication_hostgroups configuration takes the group’s own
membership information as its source. Nodes that lose quorum are left out of the
traffic.
Which architecture to build and how to determine the node count is a separate piece of work: High Availability and Cluster Solutions
The proxy layer must not be a single point of failure
Putting ProxySQL between the application and the database adds a new component to the architecture. If a single ProxySQL server is installed, the problem solved does not disappear β it merely moves. We use one of two deployment shapes:
- Local ProxySQL on every application server. The application connects over
localhost, no extra network round trip is introduced, and losing one copy affects only its own server. - A separate ProxySQL layer. Run redundantly behind a virtual IP; preferred where the number of application servers is very high, or where the configuration has to be managed centrally.
In both shapes we keep the configuration consistent between copies with ProxySQL Cluster: a rule change made on one node propagates to the others, with no manual copying.
Monitoring: the proxy layer is where the query profile shows
ProxySQL keeps its own statistics in the stats schema of its admin interface.
Connection usage per hostgroup, the state of the backend nodes, replication lag
and query digests are all read from there. We collect the metrics through Percona
Monitoring and Management (PMM) and watch them on Grafana dashboards.
The stats_mysql_query_digest table has a value of its own: it shows every query
pattern arriving from the application together with its call count and total time.
Where the slow query log records only queries above a threshold, ProxySQL sees all
of the traffic. A query called thousands of times a second that looks fast on its
own is only noticed in this table.
The equivalent work on the database side is a separate engagement: Performance Tuning and Monitoring
How the installation proceeds
- Measurement. The current connection profile, the query patterns and the use of session state are established. The gain to expect from multiplexing rests on that measurement.
- Rule design. The hostgroup scheme and the query rules are written in a test environment and verified against the application’s real traffic.
- Phased cutover. Read traffic is moved through ProxySQL first; once the behaviour is confirmed, write traffic follows.
- Failover rehearsal. The primary is taken out deliberately; the number of errors the application receives and the time to reroute are measured.
- Handover. The rule set, the hostgroup scheme and the monitoring dashboard are documented and handed over.
Related services
ProxySQL is part of connection and user management; backend credentials and access control are planned at this layer too: Database Security and Technical Measures
Taking backup traffic from the read replicas is designed together with the routing rules: Database Backup and Recovery
Our database support services
On the databases we put a ProxySQL layer in front of, we provide the full range of our support and consulting services.