ProxySQL architecture, admin SQL configuration & routing hub.
ProxySQL
3-Tier Admin Engine (:6032)
ProxySQL 2.6 / 2.7
recommendedIn-memory SQLite3 configuration engine with three-tier RUNTIME, MEMORY, and DISK promotion semantics.
Hostgroup 10 / 20
supportedAutomatic writer/reader hostgroup movement driven by read_only, replication lag, Galera wsrep_local_state, or Group Replication member_state.
Multiplexing Engine
supportedTransaction-aware connection multiplexing with automatic pinning detection for @user_vars, GET_LOCK(), and SQL_CALC_FOUND_ROWS.
ProxySQL Cluster
supportedPeer-to-peer checksum synchronization of mysql_servers, mysql_users, and mysql_query_rules across horizontal ProxySQL pods.
We manage ProxySQL for MySQL 8.0/8.4 LTS, Percona XtraDB Cluster, and MariaDB Galera.
- P1: 15 min · P2: 30 min
- Initial-response targets under a 24x7 support agreement (P3: 60 min, P4: 90 min); 2 to 4 hours during covered business hours.
- 3-Tier Config Model
- RUNTIME (active traffic) <- MEMORY (main SQLite tables) <-> DISK (proxysql.db persistence) + CONFIG FILE bootstrap.
- Consulting & Support
- Use this page for architecture and SQL configuration patterns, or visit our dedicated ProxySQL Consulting spoke for custom engagements.
Selected clients
Production Failure Modes
Six ProxySQL bottlenecks we diagnose in production.
ProxySQL rarely fails because of a binary crash; it fails when session variables silently disable connection multiplexing, regex rules saturate CPU cores, or hostgroup shun thresholds conflict with replication lag.
Backend connections climb to max_connections despite ProxySQL
Max connect timeout reached while reaching hostgroup 10
Application frameworks are setting session variables (@var), running SET Names/character_set per query, or leaving autocommit=0 idle, forcing ProxySQL to disable multiplexing (ConnUsed spikes while ConnFree drops to 0).
Query stats_mysql_connection_pool and stats_mysql_query_digest for multiplex=0 digests; add mysql_query_rules to strip or handle benign session SET statements.
Stale read-after-write bugs on checkout or user profile updates
SELECT returns pre-commit state from Hostgroup 20
Broad regex routing rules send all SELECTs to Reader Hostgroup 20 before asynchronous binlog replication has replayed the preceding INSERT/UPDATE on the replica.
Pin critical post-write query digests to Writer Hostgroup 10 via digest matching, or configure gtid_port binlog reader tracking and strict max_replication_lag thresholds.
ProxySQL CPU saturates above 40,000 QPS
Worker threads at 95%+ CPU in top / perf
Hundreds of unanchored regular expressions in mysql_query_rules are evaluated sequentially on every incoming SQL packet instead of matching on precomputed 64-bit query digests.
Convert regex match_pattern rules into O(1) digest rules using stats_mysql_query_digest and enable fast_forward only where protocol parsing is not required.
Writers disappear from Hostgroup 10 during brief network jitter
Server is shunned / SHUNNED state flapping
Aggressive mysql-shun_on_failures or low mysql-connect_timeout_server causes transient TCP retransmits to shun the primary or trigger false-positive read_only flips.
Tune mysql-connect_retries_on_failure, mysql-shun_recovery_time_sec, and monitor_ping_interval in step with your orchestrator or InnoDB Cluster election timers.
Configuration changes vanish after a ProxySQL container restart
Rules revert to stale proxysql.cnf state
An operator ran LOAD MYSQL QUERY RULES TO RUNTIME on port 6032 to mitigate an incident but forgot SAVE MYSQL QUERY RULES TO DISK, or launched the container with --initial.
Audit runtime_mysql_* vs disk.mysql_* checksums across all ProxySQL nodes and codify admin SQL promotions in Ansible or Kubernetes init scripts.
Galera or InnoDB Cluster split-brain writes hit two nodes
Duplicate key / WSREP certification conflicts
mysql_galera_hostgroups or mysql_group_replication_hostgroups has max_writers > 1 without writer_is_also_reader and Partition handling aligned to quorum state.
Enforce max_writers = 1 in mysql_galera_hostgroups, isolate backup writers in a dedicated hostgroup, and verify monitor_galera_healthcheck_interval.
Response Targets & Incident Triage
Published P1 to P4 response targets and the first hour of a P1.
When Hostgroup 10 shuns your only writer or connection pools saturate during a traffic spike, you reach a named MySQL/ProxySQL DBRE directly.
2 to 4 hours
Initial response target
Initial-response target during covered business hours, Mon–Fri 09:00–18:00 IST (UTC+5:30).
15 minutes
Initial response target
Initial-response target for P1 incidents where your service agreement includes 24x7 on-call.
Published Severity Matrix (under a 24x7 support agreement)
Production database down, primary unreachable, data corruption risk, or active failover event.
Severe latency spike, replication lag climbing rapidly, connection exhaustion, or degraded HA node.
Non-critical slow query regression, staging issue, backup warning, or replication check alert.
Configuration question, index advisory, planned schema review, or general operational request.
Our engineering team is in India; business-hours coverage is Mon–Fri 09:00–18:00 IST (UTC+5:30), and 24x7 on-call is available under your service agreement. Every 24x7 response target above applies under a 24x7 support agreement and measures initial engineer response, not full incident resolution.
01 · Page
0 to 15 minutes (P1 target under a 24x7 support agreement)
Your alert or call reaches the on-call MySQL/ProxySQL DBRE directly. No Tier-1 ticket triage queue.
02 · Triage
15 to 30 minutes
We connect to the ProxySQL Admin interface (port 6032) and inspect stats_mysql_connection_pool, stats_mysql_free_connections, and stats_mysql_errors to isolate shunned backends or pinned pools.
03 · Mitigate
30 to 60 minutes
We apply live runtime fixes (rerouting broken digests, draining a lagged replica with SOFT_OFFLINE, or adjusting max_connections per server) via LOAD ... TO RUNTIME without dropping active transactions.
04 · Review
Within 24 hours
We persist verified rules to DISK, align ProxySQL Cluster checksums, and deliver a written post-incident finding with permanent admin SQL and Prometheus alert thresholds.
Architecture & Operational Scope
Six ProxySQL subsystems we engineer and operate.
ProxySQL is an in-memory SQL router, connection pooler, query firewall, and cluster topology monitor in one binary. We configure each subsystem with version-controlled Admin SQL.
Digest-Based Read/Write Splitting
Replace fragile `^SELECT` regex chains with deterministic 64-bit query digest rules that keep `SELECT ... FOR UPDATE` and read-after-write critical paths on Writer Hostgroup 10.
- Digest harvesting from stats_mysql_query_digest by schemaname and username.
- Replication-lag-aware fallback from HG 20 to HG 10 when replicas exceed max_replication_lag.
Connection Multiplexing Optimization
Diagnose and eliminate session-state pinning so 10,000+ application connections multiplex cleanly onto a few hundred warm InnoDB backend threads.
- Detection of @user_variable assignments, SQL_CALC_FOUND_ROWS, and temporary tables.
- Safe mysql-auto_increment_delay_multiplex and set_query_lock_on_hostgroup tuning.
Galera, PXC & Group Replication Integration
Native state-table orchestration for Percona XtraDB Cluster, MariaDB Galera, and MySQL InnoDB Cluster so topology changes route in milliseconds.
- Automated writer promotion via mysql_galera_hostgroups and mysql_group_replication_hostgroups.
- Donor/Desynced node draining using OFFLINE_SOFT so in-flight transactions complete cleanly.
ProxySQL Cluster & Kubernetes Sidecars
Deploy horizontally scaled ProxySQL fleets using native peer-to-peer checksum synchronization or Kubernetes DaemonSet/sidecar topologies.
- Checksum convergence tuning for mysql_query_rules, mysql_servers, and mysql_users.
- Readiness probe and graceful connection drain hooks for zero-drop Kubernetes rollouts.
Query Firewalling, Rewriting & Mirroring
Protect production primaries during traffic storms or schema migrations with live query throttling, runtime SQL rewrites, and shadow traffic mirroring.
- Block runaway unindexed queries in seconds using error_msg rules on specific digests.
- Mirror production read/write traffic to MySQL 8.4 LTS staging clusters via mirror_hostgroup.
Observability, PMM & Prometheus Alerting
Full telemetry integration with Percona Monitoring and Management (PMM), Prometheus `mysqld_exporter`/`proxysql_exporter`, and Grafana dashboards.
- Built-in Prometheus REST metrics endpoint (:6070/metrics) and stats_mysql_global tracking.
- Actionable alerts on shunned servers, connection pool saturation, and monitor ping latency.
Live Routing States & Admin SQL Specimen
How ProxySQL behaves across multiplexing, pinning, and failover.
Toggle between steady-state multiplexing, a session-variable pool exhaustion incident, and a graceful OFFLINE_SOFT primary switchover, then inspect the exact port 6032 Admin SQL we run.
ProxySQL Hostgroup & Connection Pool State Simulator
Application and routing
12,000 application connections multiplex onto 180 MySQL 8.4 backend threads; writes route to HG 10 and lag-checked reads route to HG 20.
App Fleet (K8s Pods)
12,000 client sockets -> :6033
TLS frontend · caching_sha2_password
ProxySQL 2.7 Cluster (x3)
Digest Router + Multiplexer
HG 10 (Writer) · HG 20 (Readers)
MySQL Primary + 2 Replicas
60 warm connections per node
read_only monitored every 1000ms
12,000 application connections multiplex onto 180 MySQL 8.4 backend threads; writes route to HG 10 and lag-checked reads route to HG 20.
Application and routing
An ORM upgrade injects `SET @request_id = '...'` per request, disabling multiplexing and pinning 1:1 backend connections until HG 10 hits max_connections.
App Fleet (ORM Session Vars)
Issues SET @request_id on connect
Pins backend socket to client session
ProxySQL Connection Pool
ConnFree = 0 · ConnUsed = 500
New queries stall waiting for free backend
MySQL Primary (HG 10)
500 pinned threads · high context switch
Innodb_row_lock_waits climbing
An ORM upgrade injects `SET @request_id = '...'` per request, disabling multiplexing and pinning 1:1 backend connections until HG 10 hits max_connections.
Application and routing
During planned MySQL 8.4 maintenance or replica promotion, ProxySQL drains in-flight transactions on the old writer and flips HG 10 in under 1 second.
Old Primary (10.0.1.11)
SET read_only = 1 · OFFLINE_SOFT
Drains active transactions, blocks new writes
ProxySQL Monitor Module
Detects read_only=0 on 10.0.1.12
Moves 10.0.1.12 into Writer HG 10
Promoted Primary (10.0.1.12)
ONLINE in HG 10 · Accepts writes
App connection strings unchanged
During planned MySQL 8.4 maintenance or replica promotion, ProxySQL drains in-flight transactions on the old writer and flips HG 10 in under 1 second.
Production Diagnostic Specimen · Port 6032 Admin Interface
Eliminating Hostgroup 10 Pool Exhaustion & Stale Post-Write Reads
1. Diagnose pool saturation & top digests (stats_mysql_*)
mysql -u admin -p -h 127.0.0.1 -P 6032
-- Connect to ProxySQL Admin (port 6032) and inspect pool saturation & pinning
SELECT hostgroup, srv_host, status, ConnUsed, ConnFree, ConnOK, ConnERR, Queries, Latency_us
FROM stats_mysql_connection_pool
ORDER BY hostgroup, srv_host;
-- Identify top query digests that disabled multiplexing or consumed total execution time
SELECT hostgroup, digest, SUBSTR(digest_text, 1, 72) AS query_pattern,
count_star, sum_time / 1000 AS total_ms, sum_rows_sent
FROM stats_mysql_query_digest
ORDER BY sum_time DESC
LIMIT 10;2. Remediate hostgroups & digest rules with 3-tier promotion
ProxySQL Admin SQL Promotion
-- 1. Define Writer (HG 10) and Reader (HG 20) backends with replication lag guardrails INSERT INTO mysql_servers (hostgroup_id, hostname, port, weight, max_connections, max_replication_lag) VALUES (10, '10.0.1.11', 3306, 1000, 250, 0), (20, '10.0.1.12', 3306, 1000, 400, 5), (20, '10.0.1.13', 3306, 1000, 400, 5); -- 2. Keep SELECT ... FOR UPDATE on Writer HG 10; route safe read digests to Reader HG 20 INSERT INTO mysql_query_rules (rule_id, active, match_digest, destination_hostgroup, apply) VALUES (10, 1, '^SELECT.*FOR UPDATE$', 10, 1), (20, 1, '0x3A8F19C20B44E102', 10, 1), -- post-checkout read-after-write digest pinned to writer (30, 1, '^SELECT', 20, 1); LOAD MYSQL SERVERS TO RUNTIME; SAVE MYSQL SERVERS TO DISK; LOAD MYSQL QUERY RULES TO RUNTIME; SAVE MYSQL QUERY RULES TO DISK;
Shared Responsibility & Platforms
What your team owns and what JusDB owns.
Managed cloud databases automate much of the infrastructure layer, but your team still owns query performance, capacity, cost, and incident decisions. We operate ProxySQL across bare-metal Linux, AWS EC2/EKS, GCP GKE, Azure AKS, and alongside RDS/Aurora backends.
| Operational Layer | Customer Team | JusDB DBRE Team |
|---|---|---|
| Application SQL & ORM connection strings | Point application pools at ProxySQL port 6033 and own business logic | Audit query digests, eliminate session-pinning patterns, and tune client pool sizes |
| ProxySQL Admin (:6032) & Query Rules | Approve routing changes or manage via GitOps PRs | Author, test, and promote mysql_query_rules, hostgroups, and user credentials to RUNTIME & DISK |
| Replication & Cluster Health Monitors | Provide VPC/network reachability between ProxySQL and MySQL nodes | Configure mysql_replication_hostgroups, mysql_galera_hostgroups, and shun recovery thresholds |
| Observability & Incident Triage | Receive Slack/PagerDuty notifications on service-level events | Monitor stats_mysql_* tables, Prometheus exporters, and respond within P1-P4 SLA targets |
| Upgrades & Failover Rehearsals | Approve maintenance windows for major cutovers | Run staged OFFLINE_SOFT switchover drills and query-mirroring validation before MySQL upgrades |
Customer Boundary
What stays in your control
- Set business priorities, application requirements, and release plans
- Approve production changes, except routine actions you have pre-approved
- Own your cloud accounts, access policies, and vendor relationships
- Decide when maintenance can happen and how quickly service must recover
- Move the work in-house or to another provider after your agreement's notice period, with your data returned or deleted and the transition help your agreement sets out (Terms, section 8)
JusDB Execution Scope
What our DBREs execute for you
- Monitor database health, investigate alerts, and involve the right people
- Review query performance, replication, capacity, and backup health
- Carry out approved changes and update the operating instructions they affect
- Report completed work, open risks, and recommended next steps
Proxy Comparison & Commercial Engagement Paths
ProxySQL vs alternatives and how to engage JusDB.
Compare ProxySQL against MySQL Router, MaxScale, and HAProxy, then choose between our dedicated ProxySQL Consulting spoke, an ongoing MySQL Support retainer, or a free architecture review.
| Evaluation Vector | JusDB ProxySQL DBRE | MySQL Router / Cloud LB | In-House Generalists |
|---|---|---|---|
| Query Rule-Based Routing & Splitting | Regex pattern routing, read/write hostgroup segregation, query rewriting, traffic mirroring, and caching without application code changes | Basic topology routing without query inspection; no regex matching, rewrite rules, or in-proxy query caching | Application-level read/write connection splitting with brittle dual datasource configurations and accidental replica write risks |
| Connection Multiplexing & Memory Drops | Sub-transaction backend connection multiplexing; for example, 10,000 application clients served by 200 MySQL threads, reducing backend RAM use | One-to-one client-to-server connection mapping or simple thread pooling, maintaining heavy memory overhead on the database | Direct connection storms during traffic spikes exhausting MySQL max_connections and triggering OS out-of-memory kills |
| Runtime Reconfiguration (Zero Restarts) | Multi-tier SQLite admin interface (MEMORY / RUNTIME / DISK) with automated LOAD TO RUNTIME synchronization across proxy clusters | Static configuration files requiring proxy process restarts or reload delays that disrupt active database transactions | Manual file edits on distributed proxy hosts with configuration drift, syntax errors, and unexpected client disconnects |
| In-Proxy SQL Firewall & Safeguards | Active query blocking for unindexed table scans, missing WHERE clauses on UPDATE/DELETE, and automated slow query throttling | Basic connection rate limiting without SQL syntax parsing or query-level blast radius containment | Relying on MySQL server-side slow query logs after bad queries have already saturated CPU and locked critical InnoDB tables |
| Multi-Node HA & Config Synchronization | ProxySQL Cluster peer synchronization with Keepalived VRRP virtual IPs, automated Orchestrator failover hooks, and health polling | Single proxy node or generic cloud load balancer without database replication topology or lag awareness | Unsynchronized proxy instances with stale hostgroup metadata routing writes to read replicas during failover events |
| Monitoring & Incident Response | Continuous stats_mysql_query_digest monitoring, pool starvation alerting, and senior DBREs on your incident bridge. Initial-response targets: 2 to 4 hours in covered business hours; 15 minutes for P1 incidents with 24x7 on-call. Targets, not resolution guarantees. | Standard cloud support tickets with multi-hour response windows and no proxy-level performance tuning assistance | Engineers triaging proxy connection lockups and multiplexing timeouts at 3 AM without deep ProxySQL internals expertise |
ProxySQL Architecture & Implementation Consulting
Fixed-scope engagement to design, deploy, or rescue a production ProxySQL topology for MySQL, Percona XtraDB Cluster, or MariaDB Galera.
- Complete audit of stats_mysql_query_digest and multiplexing pin removal.
- Custom mysql_query_rules, Galera/Group Replication hostgroups, and Prometheus dashboards.
- Rehearsed primary failover and ProxySQL Cluster checksum synchronization.
Ongoing MySQL & ProxySQL Support
Named senior DBREs managing your ProxySQL routing layer and underlying MySQL/MariaDB clusters together under one SLA.
- 15-minute P1, 30-minute P2, 60-minute P3, and 90-minute P4 response targets under a 24x7 support agreement.
- Continuous digest tuning, query firewalling during traffic spikes, and minor version upgrades.
- Weekly health reports covering connection pool efficiency and replica lag.
Free ProxySQL & MySQL Architecture Audit
Not sure why multiplexing is disabled or why hostgroup 10 is shunning backends? Start with a read-only diagnostic review.
- Review of your current proxysql.cnf / admin tables and top 20 query digests.
- Concrete recommendations for hostgroup sizing, max_connections, and lag thresholds.
- Delivered in a 30-minute walkthrough with a senior MySQL DBRE.
Procurement and security
Ready for legal and security review.
- Mutual NDA before any access
- Named individual accounts only
- Read-only access by default
- ISO 27001 & SOC 2 Type II: aligned, certification in progress
Mutual NDA before any access
We sign a mutual NDA before viewing schemas, logs, slow-query captures, or cloud topology.
Least-privilege, named credentials
Engineers connect through your bastion, VPN, or SSM using named accounts with read-only permissions by default.
Change control & approval gates
Production DDL, parameter changes, and failovers follow your change-management workflow with rollback steps prepared first.
Named leadership & direct escalation
Engagements are led by named senior database reliability engineers in India with direct Slack, Teams, and PagerDuty channels.
ProxySQL Technical & Operational FAQ
Frequently asked ProxySQL questions.
Synced 1-to-1 with our structured FAQPage schema. Have a topology-specific question about mysql_query_rules or Galera hostgroups? Book a call with our DBRE team.
Still have a question?
Ask it on a scoping call. No production access needed.
Book a 30-min scoping call (opens Google Calendar in a new tab)How is this ProxySQL Reference Hub different from JusDB's ProxySQL Consulting page?
How does ProxySQL connection multiplexing work, and what disables it?
Why should we use query digests instead of regex in mysql_query_rules?
How does ProxySQL prevent stale reads when splitting reads and writes?
What is the difference between OFFLINE_SOFT and OFFLINE_HARD in ProxySQL?
How does ProxySQL compare to MySQL Router, MaxScale, and HAProxy?
What are JusDB's SLA response targets for ProxySQL and MySQL incidents?
Related Spokes, Tools & Reference Hubs
Continue exploring MySQL & proxy engineering.
Pair ProxySQL connection multiplexing with properly sized InnoDB buffer pools, HA orchestrators, and dedicated consulting support.
ProxySQL Consulting Services
Scoping, deliverables, and fixed-fee implementation engagements for ProxySQL 2.6/2.7.
MySQL Configuration Optimizer
Generate tuned my.cnf settings for InnoDB buffer pool, redo log capacity, and thread limits.
MySQL High Availability Architecture
End-to-end HA design combining ProxySQL with semi-sync replication and InnoDB Cluster.
MariaDB Galera Cluster Operations
Multi-master wsrep quorum, IST/SST recovery, and ProxySQL mysql_galera_hostgroups tuning.
HAProxy Database Routing Reference
When to use Layer-4 TCP load balancing with HTTP xinetd health checks instead of L7 SQL parsing.
MaxScale Database Proxy Reference
Compare ProxySQL admin SQL tables against MaxScale's readwritesplit and binlogrouter modules.
Ready to Harden ProxySQL?
Get a senior MySQL DBRE to review your ProxySQL hostgroups and query rules.
Or request a free audit of one database instance, subject to availability: mutual NDA before read-only access, a prioritized report within one week after access is ready, and no obligation. Or jump straight to our commercial ProxySQL Consulting spoke at /tools/proxysql/consulting.







