{"id":4620,"date":"2026-09-20T04:01:24","date_gmt":"2026-09-19T22:31:24","guid":{"rendered":"https:\/\/cpanelfree.com\/blog\/configuring-proxysql-query-routing-readwrite-splitting-and-connection-caching\/"},"modified":"2026-09-20T04:01:24","modified_gmt":"2026-09-19T22:31:24","slug":"configuring-proxysql-query-routing-readwrite-splitting-and-connection-caching","status":"publish","type":"post","link":"https:\/\/cpanelfree.com\/blog\/configuring-proxysql-query-routing-readwrite-splitting-and-connection-caching\/","title":{"rendered":"Configuring ProxySQL Query Routing, Read\/Write Splitting, and Connection Caching"},"content":{"rendered":"

Enterprise relational database clusters frequently hit performance walls not because the underlying storage engines lack raw compute, but because client connection churn, unsegregated query paths, and thread contention overwhelm primary database nodes. In high-traffic environments, decoupling application database drivers from physical backend topologies using an intelligent layer-7 database proxy is the gold standard for achieving horizontal read scalability and sub-millisecond query delivery. By deploying an optimized ProxySQL layer on CpanelFree<\/a> high-performance infrastructure, systems engineers can transparently implement dynamic read\/write splitting, sub-microsecond connection multiplexing, and in-memory query result caching without altering a single line of application source code.<\/p>\n

<\/p>\n

What is ProxySQL Read\/Write Splitting and Query Routing?<\/h2>\n
\n Direct Answer:<\/strong> A ProxySQL read write split configuration<\/strong> is an intelligent Layer-7 database proxy architecture that inspects incoming SQL traffic in real time, automatically routing data-modifying queries (INSERT, UPDATE, DELETE) to primary writer hostgroups while directing analytical and idempotent read queries (SELECT) across healthy read-replica hostgroups with millisecond-level connection caching.\n<\/div>\n

Unlike basic Layer-4 TCP balancers (such as HAProxy in raw stream mode) that merely balance incoming socket connections across backend endpoints, ProxySQL operates with deep MySQL protocol intelligence. It continuously analyzes SQL statement syntax, abstracts backend connection pools from client threads, monitors replication lag, and enforces fine-grained traffic policies at line rate. This architectural separation resolves three critical database scaling challenges:<\/p>\n

    \n
  • Thundering Herd Connection Overhead:<\/strong> Each direct MySQL connection consumes substantial memory (default thread stack, per-connection buffers, and OS descriptors). ProxySQL enables tens of thousands of client frontend connections to share a compact, persistent pool of backend connections via multiplexing.<\/li>\n
  • Application Coupling:<\/strong> Traditional read\/write splitting requires application frameworks to maintain separate database connection handles (e.g., master vs. slave database pools). ProxySQL centralizes this logic into regular-expression query rules, decoupling infrastructure topology from software codebases.<\/li>\n
  • Failover and High Availability:<\/strong> During master node failovers or replica maintenance, ProxySQL dynamically repoints traffic without throwing application connection termination errors, handling transient disconnects gracefully.<\/li>\n<\/ul>\n
    \n Architecture Note:<\/strong> ProxySQL implements a three-tier configuration runtime: RUNTIME<\/em> (active memory structures processing queries), MEMORY<\/em> (in-memory SQLite database accessible via the admin interface on port 6032), and DISK<\/em> (persisted SQLite database file or static configuration). Any configuration change modified via the SQLite interface must be explicitly loaded to runtime and saved to disk to survive restarts.<\/div>\n

    ProxySQL Performance & Architectural Comparison<\/h2>\n

    Before examining concrete configuration directives, review how a tuned ProxySQL proxy deployment contrasts with legacy direct-to-database connections under high concurrent throughput:<\/p>\n\n\n\n\n\n\n\n\n\n\n
    Feature \/ Metric<\/th>\nStandard \/ Direct MySQL<\/th>\nTuned ProxySQL Production<\/th>\n<\/tr>\n<\/thead>\n
    Connection Handshake Latency<\/td>\n15ms – 45ms per client thread (SSL + Auth)<\/td>\n< 0.8ms (Backend Pool Multiplexing)<\/td>\n<\/tr>\n
    Maximum Concurrent Frontend Clients<\/td>\nLimited by `max_connections` (typically 500-1,500)<\/td>\n10,000+ active client sockets<\/td>\n<\/tr>\n
    Read\/Write Separation Method<\/td>\nHardcoded application-level connection routing<\/td>\nTransparent Layer-7 Regex & Digest Query Rules<\/td>\n<\/tr>\n
    Replication Lag Management<\/td>\nManual monitoring or app-level replica shedding<\/td>\nAutomated host shunning when `max_lag_ms` breached<\/td>\n<\/tr>\n
    Repetitive Query Caching<\/td>\nExternal Redis\/Memcached cluster required<\/td>\nIn-Memory Native ProxySQL Query Cache with TTL<\/td>\n<\/tr>\n
    Master Failover Impact<\/td>\nApplication throws 2006\/2013 connection drop errors<\/td>\nSeamless traffic pause and replay to promoted node<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    Linux Kernel & Operating System Tuning for ProxySQL<\/h2>\n

    ProxySQL is an event-driven, multi-threaded C++ engine utilizing non-blocking epoll sockets. Under enterprise workloads handling thousands of concurrent transactions, the Linux kernel must be tuned to prevent TCP socket exhaustion, SYN queue drops, and file descriptor starvation.<\/p>\n

    Create a dedicated sysctl configuration file at \/etc\/sysctl.d\/99-proxysql-networking.conf<\/code> with the following production-grade kernel parameters:<\/p>\n

    # \/etc\/sysctl.d\/99-proxysql-networking.conf\n# Production Kernel Network Optimization for ProxySQL Database Gateways\n\n# Maximize socket listen backlog for burst connections\nnet.core.somaxconn = 65535\nnet.ipv4.tcp_max_syn_backlog = 65535\n\n# Optimize ephemeral port range for massive backend connection pools\nnet.ipv4.ip_local_port_range = 1024 65535\n\n# Enable fast reuse of TIME_WAIT sockets for outgoing connections\nnet.ipv4.tcp_tw_reuse = 1\n\n# Reduce TCP keepalive parameters to rapidly detect dead backend nodes\nnet.ipv4.tcp_keepalive_time = 300\nnet.ipv4.tcp_keepalive_intvl = 15\nnet.ipv4.tcp_keepalive_probes = 5\n\n# TCP buffer sizing for high throughput (min, default, max in bytes)\nnet.ipv4.tcp_rmem = 4096 87380 16777216\nnet.ipv4.tcp_wmem = 4096 65536 16777216\nnet.core.rmem_max = 16777216\nnet.core.wmem_max = 16777216\n\n# Protect against SYN flood attacks while maintaining responsiveness\nnet.ipv4.tcp_syncookies = 1\nnet.ipv4.tcp_fin_timeout = 15\n\n# Increase system-wide file descriptor limit\nfs.file-max = 2097152<\/code><\/pre>\n
    Apply the parameters immediately without rebooting:<\/p>\n
    sudo sysctl --system<\/code><\/pre>\n
    Next, configure process limits for the ProxySQL systemd service to prevent EMFILE: Too many open files<\/code> errors. Create a systemd drop-in override at \/etc\/systemd\/system\/proxysql.service.d\/override.conf<\/code>:<\/p>\n
    # \/etc\/systemd\/system\/proxysql.service.d\/override.conf\n[Service]\nLimitNOFILE=1048576\nLimitNPROC=524288\nLimitMEMLOCK=infinity\nRestart=always\nRestartSec=5s<\/code><\/pre>\n
    Reload systemd daemon configurations and restart ProxySQL:<\/p>\n
    sudo systemctl daemon-reload\nsudo systemctl restart proxysql<\/code><\/pre>\n
    Production ProxySQL Global Configuration File (\/etc\/proxysql.cnf)<\/h2>\n
    The global configuration file establishes the administrative socket, daemon threading model, and memory buffers. The admin interface is hosted by default on TCP port 6032<\/code>, while incoming application MySQL traffic binds to TCP port 6033<\/code>.<\/p>\n
    # \/etc\/proxysql.cnf\n# Enterprise ProxySQL Configuration for High-Concurrency Read\/Write Splitting\n\ndatadir=\"\/var\/lib\/proxysql\"\n\nadmin_variables={\n    admin_credentials=\"admin:CpanelFree_Admin_Sec2026;radmin:CpanelFree_RAdmin_Sec2026\"\n    mysql_ifaces=\"127.0.0.1:6032;\/var\/run\/proxysql\/proxysql_admin.sock\"\n    refresh_interval=2000\n}\n\nmysql_variables={\n    threads=8                                  # Match dedicated CPU physical cores\n    max_connections=10000                      # Frontend client connection ceiling\n    default_query_delay=0\n    default_query_timeout=3600000              # 1 hour timeout protection\n    have_compress=true\n    poll_timeout=2000                          # Epoll timeout in microseconds\n    interfaces=\"0.0.0.0:6033;\/var\/run\/proxysql\/proxysql.sock\"\n    default_schema=\"information_schema\"\n    stacksize=1048576\n    server_version=\"8.0.36-ProxySQL\"\n    connect_timeout_server=3000                # Timeout connecting to MySQL backends (ms)\n    monitor_history=600000                     # 10 minutes monitor telemetry\n    monitor_connect_interval=60000\n    monitor_ping_interval=10000\n    monitor_read_only_interval=1500\n    monitor_read_only_timeout=800\n    ping_interval_server_msec=120000\n    ping_timeout_server=500\n    commands_stats=true\n    sessions_sort=true\n    connect_retries_on_failure=10\n    query_cache_size_MB=256                    # In-memory query result cache pool\n}<\/code><\/pre>\n
    Configuring Hostgroups, Servers, and Replication Awareness<\/h2>\n
    ProxySQL organizes backend database nodes into logical numeric entities known as Hostgroups<\/strong>. In standard Master-Replica topologies, we assign:<\/p>\n
      \n
    • Hostgroup 10:<\/strong> Primary Master (Writer) node(s). All data mutation statements target this hostgroup.<\/li>\n
    • Hostgroup 20:<\/strong> Read Replica (Reader) nodes. All read queries target this group for load distribution.<\/li>\n<\/ul>\n
      Connect to the ProxySQL administrative interface using the MySQL client:<\/p>\n
      mysql -u admin -pCpanelFree_Admin_Sec2026 -h 127.0.0.1 -P 6032 --prompt='ProxySQL Admin> '<\/code><\/pre>\n
      Execute the following SQL script to define the backend topology, configure replication lag thresholds, and set up health checks:<\/p>\n
      -- 1. Purge existing staging server definitions\nDELETE FROM mysql_servers;\n\n-- 2. Register Primary Master into Hostgroup 10 (Writer)\nINSERT INTO mysql_servers (\n    hostgroup_id, hostname, port, status, weight, compression, max_connections, max_replication_lag, use_ssl\n) VALUES (\n    10, '192.168.10.101', 3306, 'ONLINE', 1000, 0, 1000, 0, 1\n);\n\n-- 3. Register Read Replicas into Hostgroup 20 (Readers)\nINSERT INTO mysql_servers (\n    hostgroup_id, hostname, port, status, weight, compression, max_connections, max_replication_lag, use_ssl\n) VALUES \n(20, '192.168.10.102', 3306, 'ONLINE', 100, 0, 1000, 5, 1),\n(20, '192.168.10.103', 3306, 'ONLINE', 100, 0, 1000, 5, 1),\n(20, '192.168.10.104', 3306, 'ONLINE', 100, 0, 1000, 5, 1);\n\n-- 4. Enable Dynamic Replication Hostgroups (Automatic read_only tracking)\n-- If read_only=0 on a node, ProxySQL places it in writer_hostgroup (10).\n-- If read_only=1 on a node, ProxySQL places it in reader_hostgroup (20).\nDELETE FROM mysql_replication_hostgroups;\nINSERT INTO mysql_replication_hostgroups (\n    writer_hostgroup, reader_hostgroup, check_type, comment\n) VALUES (\n    10, 20, 'read_only', 'Automated Master\/Replica Read-Only State Tracker'\n);\n\n-- 5. Commit server topology to RUNTIME and persist to DISK\nLOAD MYSQL SERVERS TO RUNTIME;\nSAVE MYSQL SERVERS TO DISK;<\/code><\/pre>\n
      \n  Replication Lag Safety:<\/strong> Setting max_replication_lag=5<\/code> instructs ProxySQL to inspect Seconds_Behind_Master<\/code> on replicas every second. If replica lag exceeds 5 seconds, ProxySQL automatically changes the node’s status to SHUNNED<\/code>, diverting read traffic to the remaining healthy replicas to guarantee read consistency.<\/div>\n
      Configuring Application Users and Credentials<\/h2>\n
      ProxySQL must be aware of application users so it can authenticate client sessions and match credentials when opening backend connections. Define the application user and configure default hostgroup routing:<\/p>\n
      -- Register Application User\nDELETE FROM mysql_users WHERE username='app_production';\n\nINSERT INTO mysql_users (\n    username, password, active, default_hostgroup, default_schema, transaction_persistent, fast_forward\n) VALUES (\n    'app_production', 'Str0ng_Pr0d_Db_P@ss2026', 1, 10, 'app_db', 1, 0\n);\n\n-- Configure Monitor User for Heartbeat & Replication Telemetry\nSET admin-stats_credentials='monitor:Mon1t0r_Sec_P@ss2026';\nSET mysql-monitor_username='proxysql_monitor';\nSET mysql-monitor_password='Mon1t0r_Sec_P@ss2026';\n\n-- Commit Users to RUNTIME and DISK\nLOAD MYSQL USERS TO RUNTIME;\nSAVE MYSQL USERS TO DISK;\nLOAD ADMIN VARIABLES TO RUNTIME;\nSAVE ADMIN VARIABLES TO DISK;\nLOAD MYSQL VARIABLES TO RUNTIME;\nSAVE MYSQL VARIABLES TO DISK;<\/code><\/pre>\n
      \n  Crucial Parameter:<\/strong> Setting transaction_persistent=1<\/code> ensures that once a client issues a BEGIN<\/code> or START TRANSACTION<\/code>, all subsequent queries within that session remain pinned to the writer hostgroup (10) until a COMMIT<\/code> or ROLLBACK<\/code> occurs. This prevents catastrophic read-your-own-writes inconsistencies inside transactional blocks.<\/div>\n
      Advanced Query Routing and Read\/Write Splitting Rules<\/h2>\n
      ProxySQL processes queries through the mysql_query_rules<\/code> table in ascending order of rule_id<\/code>. When a query arrives, ProxySQL checks the rule chain. If a rule matches and specifies apply=1<\/code>, rule evaluation halts, and the query routes to the target hostgroup.<\/p>\n
      A production-ready read\/write splitting rule chain must implement four fundamental protections:<\/p>\n
        \n
      1. Locking Read Protection:<\/strong> Divert SELECT ... FOR UPDATE<\/code> and LOCK IN SHARE MODE<\/code> to the Master (Hostgroup 10).<\/li>\n
      2. Default Writer Anchor:<\/strong> Ensure non-SELECT statements (INSERT, UPDATE, DELETE, REPLACE, DDL) route to Hostgroup 10.<\/li>\n
      3. Read Offloading:<\/strong> Route standard read-only SELECT<\/code> statements to the Reader pool (Hostgroup 20).<\/li>\n
      4. Selective In-Memory Result Caching:<\/strong> Cache idempotent, high-frequency lookup queries directly in ProxySQL RAM with a tailored TTL.<\/li>\n<\/ol>\n
        Execute the following SQL script to install this enterprise rule set:<\/p>\n
        -- Clear old rules\nDELETE FROM mysql_query_rules;\n\n-- Rule 10: Fast-track and protect SELECT ... FOR UPDATE \/ LOCK IN SHARE MODE (Send to Master)\nINSERT INTO mysql_query_rules (\n    rule_id, active, match_pattern, destination_hostgroup, apply, comment\n) VALUES (\n    10, 1, '^SELECT.*FOR UPDATE|^SELECT.*LOCK IN SHARE MODE', 10, 1, 'Locking reads routed to Master'\n);\n\n-- Rule 20: Cache high-frequency, read-heavy catalog\/metadata queries in ProxySQL RAM (TTL: 3000ms)\nINSERT INTO mysql_query_rules (\n    rule_id, active, match_pattern, destination_hostgroup, cache_ttl, apply, comment\n) VALUES (\n    20, 1, '^SELECT.*FROM `system_settings`|^SELECT.*FROM `global_config`', 20, 3000, 1, 'In-memory cached lookup queries'\n);\n\n-- Rule 30: Route all standard SELECT queries to Reader Hostgroup (Hostgroup 20)\nINSERT INTO mysql_query_rules (\n    rule_id, active, match_pattern, destination_hostgroup, apply, comment\n) VALUES (\n    30, 1, '^SELECT .*', 20, 1, 'Standard reads routed to Replicas'\n);\n\n-- Rule 40: Explicit fallback for all write and data modification operations (Hostgroup 10)\nINSERT INTO mysql_query_rules (\n    rule_id, active, match_pattern, destination_hostgroup, apply, comment\n) VALUES (\n    40, 1, '.*', 10, 1, 'Catch-all writes, DDL, and transactions routed to Master'\n);\n\n-- Commit rules to RUNTIME and DISK\nLOAD MYSQL QUERY RULES TO RUNTIME;\nSAVE MYSQL QUERY RULES TO DISK;<\/code><\/pre>\n
        Connection Caching and Multiplexing Optimization<\/h2>\n
        ProxySQL connection multiplexing is its most powerful performance mechanism. In standard setups, when 2,000 PHP-FPM or Node.js workers connect, MySQL spawns 2,000 execution threads. With ProxySQL multiplexing enabled, those 2,000 client sessions are managed inside an epoll event loop and mapped onto a compact pool of just 30 to 50 active backend connections.<\/p>\n
        Multiplexing operates at the individual statement boundary. Between queries, a backend connection is released back into the pool. However, certain SQL actions can temporarily disable multiplexing for a session (known as multiplexing inhibitors):<\/p>\n