MySQL & MariaDB Intelligent Proxy
ProxySQL: The MySQL Proxy That Thinks — Query Rules, Multiplexing, and Runtime Control
ProxySQL is a high-performance, Layer 7 intelligent database proxy for MySQL and MariaDB. It inspects SQL packets in real-time to execute regex-based query routing, automatic read/write splitting, query caching, and query blocking via an internal SQL firewall. Its connection multiplexing architecture allows thousands of client applications to share a small pool of backend server connections, cutting database memory consumption by up to 80% while allowing configuration changes at runtime without service restarts.
ProxySQL is the most feature-complete MySQL and MariaDB proxy available. Its query rules engine lets you route, rewrite, cache, or block any query based on regex patterns — with zero restarts. Connection multiplexing makes it the only MySQL proxy that reduces backend connections below the client connection count.
What Makes ProxySQL Unique
ProxySQL parses every MySQL protocol packet and applies a rule engine before forwarding. This intelligence enables capabilities no other MySQL proxy provides — including multiplexing and in-proxy query caching.
Query Rules Engine
Route any query to any hostgroup using regex rules on username, schema, digest, or query text. Rewrite queries on-the-fly. Mirror traffic to a test cluster. All configurable at runtime.
Connection Multiplexing
ProxySQL can serve 10,000 client connections with only 200 server connections using multiplexing — far beyond what MySQL itself supports. Reduces backend MySQL memory by 80%+.
Detailed Query Statistics
stats_mysql_query_digest tracks every unique query digest — count, latency, rows sent, first/last seen. Essential for finding slow queries without enabling MySQL's slow query log.
SQL Firewall
Block queries matching patterns before they reach MySQL. Protect against accidental table scans, DELETE without WHERE, or specific attack patterns. Rules applied at the proxy with zero MySQL overhead.
In-Memory Query Cache
Cache SELECT results in ProxySQL's internal cache. Serve repeated identical queries without touching MySQL. TTL-based expiry. Particularly effective for dashboards and reporting queries.
Runtime Reconfiguration
Every ProxySQL setting — routing rules, backend servers, users, timeouts — can be changed via SQL admin interface without restarting. LOAD TO RUNTIME; SAVE TO DISK applies changes in milliseconds.
Resilience Engineering
ProxySQL Production Failure Modes
Critical proxy and pooling failure scenarios analyzed and remediated by JusDB DBREs to prevent backend connection storms, silent split-brain writes, and proxy memory exhaustion.
Multiplexing Disabling via Stale Session Variables or Prepared Statements
Applications setting user-defined session variables (@var), issuing PREPARE statements without deallocation, or setting session-level system variables cause ProxySQL to lock backend connections to single clients, breaking multiplexing and triggering connection exhaustion on MySQL.
Enforce strict multiplexing rules with mysql-multiplexing=true, identify variable-leaking statements in stats_mysql_global, and configure multiplexing exceptions or rewrite rules to isolate session-pinned queries.
Hostgroup Split-Brain During Uncoordinated Master Failovers
When an automated failover tool (such as Orchestrator or MHA) promotes a new primary without updating ProxySQL's mysql_servers hostgroup mappings, ProxySQL continues routing write traffic to the demoted, read-only former primary.
Deploy automated topology monitoring via Orchestrator webhooks or ProxySQL's native replication hostgroup integration, dynamically updating writer/reader hostgroups with zero manual intervention.
Unbounded Digest Memory Growth in stats_mysql_query_digest
High-cardinality dynamic queries containing unparameterized literals generate unique digest hashes, bloating ProxySQL's internal SQLite stats tables and causing memory fragmentation and proxy thread latency.
Enforce client-side parameterized queries, tune stats-mysql_query_digest_to_db frequency, and automate periodic truncation of stale digests via cron-driven admin maintenance scripts.
Proxy Telemetry
Production ProxySQL Diagnostic Runbooks
Non-blocking SQL commands executed via the ProxySQL administrative interface (port 6032) to inspect backend connection pool saturation, multiplexing efficacy, and query digest latency distributions.
Verifies server connection states, active vs idle backend threads, and global multiplexing metrics to detect connection pinning or pool starvation.
-- Inspect backend connection pool usage and error counts SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, ConnOK, ConnErrors, Queries FROM stats_mysql_connection_pool ORDER BY hostgroup, srv_host; -- Audit client vs backend connection multiplexing efficiency SELECT Variable_Name, Variable_Value FROM stats_mysql_global WHERE Variable_Name IN ( 'Client_Connections_connected', 'Server_Connections_connected', 'ConnPool_memory_bytes', 'Queries_delayed_multiplex' );
Audits top slowest query digests across hostgroups and validates rule matching hit counts to ensure traffic is correctly segregated.
-- Identify top slow query digests and total execution time SELECT hostgroup, digest, digest_text, count_star, sum_time, min_time, max_time FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 10; -- Audit active query rules and hit count distribution SELECT rule_id, active, hits, destination_hostgroup, match_pattern FROM mysql_query_rules ORDER BY hits DESC;
ProxySQL vs MaxScale vs MySQL Router
All three proxy MySQL connections, but with very different feature sets, licensing, and use cases. ProxySQL is the only one that is both fully open-source and feature-complete.
| 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; 10,000 application clients served by 200 MySQL threads, reducing RAM usage by 80%+ | 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 |
| 24/7 Production DBRE & Sub-15m P1 SLA | Continuous stats_mysql_query_digest monitoring, pool starvation alerting, senior DBRE on-call 24/7, and guaranteed <15m P1 response | 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 |
When to Choose Each Proxy
Choose ProxySQL when…
- • You need granular per-query routing control
- • High connection count — multiplexing is essential
- • You want detailed per-query performance analytics
- • SQL firewall / query blocking is required
- • Running Percona XtraDB Cluster or Galera
- • Open-source with no licensing cost is required
- • Runtime config changes without downtime are critical
Choose MaxScale when…
- • You have a MariaDB Enterprise subscription
- • You need MaxScale-specific filters (binlog router, Avro CDC)
- • Deep integration with MariaDB Galera is needed
- • Commercial support from MariaDB is a requirement
Choose MySQL Router when…
- • You run MySQL InnoDB Cluster — Router is the official frontend
- • Simple topology-aware routing is sufficient
- • You want the officially supported Oracle MySQL proxy
- • No advanced query routing rules are needed
JusDB ProxySQL Implementation Service
Hostgroup & Backend Configuration
Define writer hostgroup (primary), reader hostgroup (replicas), and backup hostgroup. Configure health checks, max_connections, and weight for each backend.
Query Rules Design
Build regex-based query rules for read/write split, schema routing, query rewriting, and traffic mirroring. Audit application queries to identify routing patterns.
Connection Multiplexing Tuning
Configure mysql-multiplexing, connection_max_age, and per-user connection limits to maximise multiplexing efficiency and reduce MySQL backend connections.
SQL Firewall Rules
Implement query blocking rules for dangerous patterns: full table scans, DELETE/UPDATE without WHERE, schema enumeration queries.
Stats & Query Digest Monitoring
Set up stats_mysql_query_digest monitoring with Prometheus export for latency distribution, error rates, and top slow query identification.
HA ProxySQL Cluster
Deploy ProxySQL Cluster (multi-instance sync) so all ProxySQL nodes share the same configuration. Pair with keepalived for VIP failover.
FAQ
Take control of your MySQL traffic
JusDB designs and deploys ProxySQL with the right query rules, multiplexing configuration, and HA setup for your specific MySQL/MariaDB workload.