Free audit

View Audit Scope
JusDBProxySQL 2.6 / 2.7 · Layer-7 SQL Proxy & Multiplexer

ProxySQL architecture, admin SQL configuration & routing hub.

ProxySQL sits at the SQL protocol layer between your application fleet and MySQL, Percona Server, or MariaDB backends. This reference hub documents production hostgroup topologies, digest-based read/write splitting, connection multiplexing mechanics, and failover monitoring, backed by our senior DBRE team with a 15-minute P1 initial-response target under a 24x7 support agreement.

ProxySQL

3-Tier Admin Engine (:6032)

ProxySQL 2.6 / 2.7

recommended

In-memory SQLite3 configuration engine with three-tier RUNTIME, MEMORY, and DISK promotion semantics.

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

  • Innoviti
  • Blitz
  • Freecharge
  • Nango
  • Recochain
  • Screen Facts
  • Nexora
  • WeCredit

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.

During covered business hours

2 to 4 hours

Initial response target

Initial-response target during covered business hours, Mon–Fri 09:00–18:00 IST (UTC+5:30).

P1 incidents (under a 24x7 support agreement)

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)

P1 · Critical15 minutes

Production database down, primary unreachable, data corruption risk, or active failover event.

P2 · High30 minutes

Severe latency spike, replication lag climbing rapidly, connection exhaustion, or degraded HA node.

P3 · Medium60 minutes

Non-critical slow query regression, staging issue, backup warning, or replication check alert.

P4 · Low90 minutes

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.

mysql_query_rules · stats_mysql_query_digest · apply=1

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.
stats_mysql_connection_pool · multiplex · mysql-free_connections_pct

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.
mysql_galera_hostgroups · wsrep_local_state · OFFLINE_SOFT

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_servers · stats_proxysql_servers_checksums

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.
replace_pattern · mirror_hostgroup · error_msg

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.
stats_mysql_global · Prometheus :6070 · PMM

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.

Active
  • 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.

Production Diagnostic Specimen · Port 6032 Admin Interface

Eliminating Hostgroup 10 Pool Exhaustion & Stale Post-Write Reads

Before: 500/500 pinned conns · 180ms p99After: 85 active conns · 4.2ms p99

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 LayerCustomer TeamJusDB DBRE Team
Application SQL & ORM connection stringsPoint application pools at ProxySQL port 6033 and own business logicAudit query digests, eliminate session-pinning patterns, and tune client pool sizes
ProxySQL Admin (:6032) & Query RulesApprove routing changes or manage via GitOps PRsAuthor, test, and promote mysql_query_rules, hostgroups, and user credentials to RUNTIME & DISK
Replication & Cluster Health MonitorsProvide VPC/network reachability between ProxySQL and MySQL nodesConfigure mysql_replication_hostgroups, mysql_galera_hostgroups, and shun recovery thresholds
Observability & Incident TriageReceive Slack/PagerDuty notifications on service-level eventsMonitor stats_mysql_* tables, Prometheus exporters, and respond within P1-P4 SLA targets
Upgrades & Failover RehearsalsApprove maintenance windows for major cutoversRun 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 VectorJusDB ProxySQL DBREMySQL Router / Cloud LBIn-House Generalists
Query Rule-Based Routing & SplittingRegex pattern routing, read/write hostgroup segregation, query rewriting, traffic mirroring, and caching without application code changesBasic topology routing without query inspection; no regex matching, rewrite rules, or in-proxy query cachingApplication-level read/write connection splitting with brittle dual datasource configurations and accidental replica write risks
Connection Multiplexing & Memory DropsSub-transaction backend connection multiplexing; for example, 10,000 application clients served by 200 MySQL threads, reducing backend RAM useOne-to-one client-to-server connection mapping or simple thread pooling, maintaining heavy memory overhead on the databaseDirect 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 clustersStatic configuration files requiring proxy process restarts or reload delays that disrupt active database transactionsManual file edits on distributed proxy hosts with configuration drift, syntax errors, and unexpected client disconnects
In-Proxy SQL Firewall & SafeguardsActive query blocking for unindexed table scans, missing WHERE clauses on UPDATE/DELETE, and automated slow query throttlingBasic connection rate limiting without SQL syntax parsing or query-level blast radius containmentRelying on MySQL server-side slow query logs after bad queries have already saturated CPU and locked critical InnoDB tables
Multi-Node HA & Config SynchronizationProxySQL Cluster peer synchronization with Keepalived VRRP virtual IPs, automated Orchestrator failover hooks, and health pollingSingle proxy node or generic cloud load balancer without database replication topology or lag awarenessUnsynchronized proxy instances with stale hostgroup metadata routing writes to read replicas during failover events
Monitoring & Incident ResponseContinuous 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 assistanceEngineers triaging proxy connection lockups and multiplexing timeouts at 3 AM without deep ProxySQL internals expertise
Dedicated Consulting Spoke

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.
Managed DBRE Retainer

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 Engineering Entry Point

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, least-privilege credentials in your vault, and security controls aligned with ISO 27001 and SOC 2 Type II. JusDB's security practices are aligned with ISO 27001 and SOC 2 Type II, and JusDB is pursuing both certifications (see Terms, section 12).
  • 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?

This page (/databases/tools/proxysql) is our technical architecture and configuration reference hub covering ProxySQL's 3-tier configuration engine, hostgroup states, connection multiplexing rules, and admin SQL diagnostics. If you are looking to scope a commercial architecture review, implementation project, or migration engagement, visit our dedicated commercial spoke at /tools/proxysql/consulting.

How does ProxySQL connection multiplexing work, and what disables it?

ProxySQL decouples client TCP sessions on port 6033 from backend MySQL connections by assigning a backend socket only while a statement or active transaction is executing, then returning the backend socket to the hostgroup pool immediately afterward. Multiplexing is automatically disabled (pinning the backend connection to a single client) whenever a session runs `LOCK TABLES`, `GET_LOCK()`, creates a `TEMPORARY TABLE`, assigns a `@user_variable`, or uses `SQL_CALC_FOUND_ROWS`.

Why should we use query digests instead of regex in mysql_query_rules?

ProxySQL normalizes every parsed SQL statement into a canonical query digest and a 64-bit hash (for example, `0x3A8F19C20B44E102`) before evaluating routing rules. Matching on `match_digest` is a fast hash lookup, whereas `match_pattern` regular expressions require CPU-intensive string evaluation on every query packet. Under high throughput (30,000+ QPS), replacing regex chains with digest rules prevents ProxySQL worker CPU saturation.

How does ProxySQL prevent stale reads when splitting reads and writes?

ProxySQL prevents stale reads through three complementary controls: (1) setting `max_replication_lag` on Reader Hostgroup 20 in `mysql_servers` so lagged replicas are temporarily shunned from read traffic; (2) pinning post-mutation read digests directly to Writer Hostgroup 10 in `mysql_query_rules`; and (3) configuring ProxySQL's native GTID binlog reader (`gtid_port`) for causal consistency tracking.

What is the difference between OFFLINE_SOFT and OFFLINE_HARD in ProxySQL?

Setting a backend server to `OFFLINE_SOFT` stops ProxySQL from routing new transactions or multiplexed queries to that node while allowing currently active transactions and pinned sessions to finish gracefully. `OFFLINE_HARD` immediately drops all active backend connections to that server and returns errors to in-flight queries. Planned maintenance and switchovers should always use `OFFLINE_SOFT`.

How does ProxySQL compare to MySQL Router, MaxScale, and HAProxy?

ProxySQL operates at Layer 7 (SQL protocol awareness), giving you query-digest routing, connection multiplexing, query rewriting, and native support for asynchronous MySQL replication, Galera/PXC, and Group Replication. HAProxy operates at Layer 4 (TCP packet forwarding) with lower CPU overhead but no query awareness or connection multiplexing. MySQL Router is tightly coupled to Oracle MySQL InnoDB Cluster metadata, while MariaDB MaxScale uses a BSL license tailored primarily to MariaDB Enterprise.

What are JusDB's SLA response targets for ProxySQL and MySQL incidents?

Under a 24x7 support agreement, JusDB publishes explicit initial-response targets across all four severity tiers: 15 minutes for P1 (Critical production outage, writer shunning, or connection pool collapse), 30 minutes for P2 (High degradation), 60 minutes for P3 (Medium issue), and 90 minutes for P4 (Low routine request). For business-hours contracts, initial response is 2 to 4 hours during covered business hours (Mon-Fri 09:00-18:00 IST, UTC+5:30).

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.

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.