ProxySQL — High-Performance MySQL Proxy with Query Routing

Introduction

ProxySQL sits between your application and MySQL, acting as an intelligent traffic manager. It multiplexes thousands of application connections into a small pool of backend connections, routes reads to replicas, and caches frequent queries — all configurable at runtime without downtime.

What ProxySQL Does

• Multiplexes application connections to reduce backend connection overhead
• Routes queries to different backend servers based on rules (read/write splitting)
• Caches query results in memory to offload repetitive reads
• Supports query rewriting, mirroring, and firewall-style blocking rules
• Allows runtime reconfiguration via a MySQL-compatible admin interface

Architecture Overview

ProxySQL is written in C++ for maximum throughput. It uses a multi-threaded event-driven architecture with epoll. Configuration lives in an embedded SQLite database with three layers: disk, memory, and runtime. Changes flow from memory to runtime atomically. The query processor evaluates regex-based rules to decide routing, caching, and rewriting per query.

Self-Hosting & Configuration

• Install via official packages for Debian, Ubuntu, CentOS, or Docker
• Admin interface listens on port 6032 (MySQL protocol) for runtime config changes
• Application traffic connects on port 6033 by default
• Define hostgroups for primary and replica servers, then create query rules for routing
• Monitor backend health with built-in ping, replication lag, and read-only checks

Key Features

• Runtime reconfiguration with no restarts or connection drops
• Connection multiplexing reduces backend load from thousands of app connections
• Query-level routing rules with regex matching and digest-based classification
• Built-in query cache with TTL control per rule
• Supports MySQL, Percona Server, MariaDB, and MySQL Group Replication

Comparison with Similar Tools

• HAProxy — general-purpose TCP/HTTP load balancer; ProxySQL understands MySQL protocol for smarter routing
• MySQL Router — official Oracle proxy for InnoDB Cluster; ProxySQL supports more backends and advanced query rules
• MaxScale (MariaDB) — similar feature set with BSL license; ProxySQL is fully open source under GPL
• PgBouncer — connection pooler for PostgreSQL; ProxySQL serves the same role for the MySQL ecosystem

FAQ

Q: Does ProxySQL add significant latency? A: Overhead is typically sub-millisecond. Connection multiplexing and caching often result in net performance gains.

Q: Can I use it with MySQL Group Replication or Galera? A: Yes. ProxySQL has native support for monitoring group replication and Galera cluster status to route traffic correctly.

Q: How do I handle failover? A: ProxySQL monitors backend health and automatically shuns unhealthy servers, rerouting traffic to available nodes.

Q: Is the admin interface secure? A: Bind the admin port to localhost or a private network and change default credentials. TLS is supported for admin connections.

Sources

• https://github.com/sysown/proxysql
• https://proxysql.com/documentation/