ProxySQL MySQL Read/Write Splitting and Connection Pooling

Connection overhead and database concurrency

Consider a hypothetical flash sale with a WooCommerce storefront and Laravel back office. As checkout traffic grows, database connections can pile up and CPU demand can rise. The bottleneck may be MySQL connection overhead and concurrency, not just query execution.

In this scenario, additional PHP-FPM workers could open more database connections and increase contention. ProxySQL for read/write splitting and connection pooling is one option for managing that load.

This guide covers ProxySQL connection pooling and deliberate read/write separation for WooCommerce and Laravel. It uses ProxySQL 3.0.8, MySQL 8.4 with traditional asynchronous primary–replica replication, and Laravel 12. Start with the writer, identify reads that tolerate stale data, and enable replica access only for those paths. The examples are for an isolated lab, with rollout and failure tests required before production.

Where pooling and read separation help

Many PHP requests open short-lived database connections; persistent connections and long-running Laravel workers behave differently. More PHP-FPM or queue workers can increase database concurrency beyond the capacity of the queries, locks and storage underneath. Pooling addresses backend connection reuse, while application concurrency limits and query tuning remain necessary.

The application opens a frontend connection to ProxySQL. ProxySQL maintains backend connections to MySQL and can reuse an eligible connection between client sessions. Configured users, hostgroups and query rules determine routing. Neither connection pooling nor a SELECT prefix determines which reads are safe to send to a replica.

Potential benefits include less contention between checkout writes and catalog reads, greater use of replicas, and reduced backend connection churn through pooling. These depend on routing correctness, workload, and available database capacity; pooling does not eliminate overload.

Cart, stock, coupon, order and session workflows can require recent data across multiple requests. They are not automatically covered by one transaction. A primary read avoids replica lag, but application locking, payment idempotency and cache invalidation still need their own correct implementation.

Architecture and the consistency boundary

PHP connects to ProxySQL, which connects to MySQL. In this example hostgroup 10 contains the writer and hostgroup 20 the replicas. Separate identities make the boundary explicit: app_writer uses group 10; app_reader is reserved for reads that tolerate replication delay. Monitoring checks server health and replication state; it does not establish causal freshness.

ProxySQL can run on the application VM, as a sidecar in the same Kubernetes Pod, or on dedicated hosts. A local deployment can reduce network distance but consumes application-host resources; dedicated instances require their own availability and capacity plan. Count every proxy’s backend pools when budgeting connections to a shared MySQL server.

The usual admin port is 6032; the application listener is 6033. Restrict actual listener addresses and firewall access before use. Configuration has separate admin-memory, runtime and disk layers: editing a table changes memory, LOAD activates its module, and SAVE persists it. This supports controlled changes but is not automatic rollback, and some settings require a restart.

Choose replica reads explicitly

Read/write splitting needs deliberate routing, which can use separate users’ default hostgroups without query rules. Writes and locking reads such as SELECT … FOR UPDATE belong on the primary. Only reads that tolerate replica staleness should go to replicas. Lag checks do not guarantee read-after-write consistency or automatic fallback to the primary; sensitive reads need deliberate primary routing, and transactions need consistent routing.

Multiplexing lets different client sessions use a reusable backend connection in turn; it does not run simultaneous queries on that one connection. Active transactions and certain session state prevent reuse. Frontend connections still incur authentication and, when enabled, TLS overhead. Measure backend churn and request latency rather than assuming a particular gain.

An isolated ProxySQL configuration

Assume an existing shopdb database, one intended writable primary at db-primary:3306, and healthy replicas at db-replica-1 and db-replica-2. Replication is already configured. Protect replicas with read_only/super_read_only; do not grant application accounts CONNECTION_ADMIN or SUPER. These examples require unused hostgroups, usernames and rule IDs in a fresh lab. Reconcile existing configuration before adapting them.

Install ProxySQL

Configure the official ProxySQL package repository for your exact Debian/Ubuntu release before installing; availability is not implied by the package name alone. The commands below assume a compatible MySQL client is already installed. Verify the installed version against this guide, restrict the admin interface and replace the default admin credentials before admitting application traffic.

sudo apt update
sudo apt install proxysql
proxysql --version
sudo systemctl enable --now proxysql
mysql -h 127.0.0.1 -P6032 -u admin -p

The remaining SQL blocks run inside the ProxySQL admin console unless stated otherwise. Keep application and management credentials separate and out of shell arguments. Consult the ProxySQL documentation for your package and topology.

Define Primary and Replicas

Before adding servers, provision a monitor account on the relevant MySQL nodes and restrict it to the proxy’s actual source address. Login access covers connect/ping/read_only checks; replication lag monitoring requires REPLICATION CLIENT. Replace the secret placeholder securely. Backend TLS configuration and certificates must already be prepared and tested: use_ssl=1 enables that connection mode but does not create trust settings.

-- ProxySQL admin console: fresh isolated lab only.
INSERT INTO mysql_servers
(hostgroup_id, hostname, port, max_connections, max_replication_lag, use_ssl)
VALUES
(10, 'db-primary', 3306, 200, 0, 1),
(20, 'db-replica-1', 3306, 200, 2, 1),
(20, 'db-replica-2', 3306, 200, 2, 1);

INSERT INTO mysql_replication_hostgroups
(writer_hostgroup, reader_hostgroup, check_type, comment)
VALUES (10, 20, 'read_only', 'Traditional asynchronous replication');

SET mysql-monitor_username='monitor';
SET mysql-monitor_password='REPLACE_WITH_MONITOR_SECRET';
SET mysql-monitor_writer_is_also_reader='false';
LOAD MYSQL VARIABLES TO RUNTIME;
LOAD MYSQL SERVERS TO RUNTIME;

For traditional replication, mysql_replication_hostgroups contains writer_hostgroup, reader_hostgroup, check_type and comment. Lag belongs to each mysql_servers.max_replication_lag entry. Here 2 seconds is only an illustrative eligibility threshold; the primary uses 0 to disable this lag check. The monitor’s writer-is-also-reader option is a global variable, set false here to exclude the primary from reader group 20. This setup does not create replication or an automatic writer fallback.

Create separate application identities

Provision app_writer and app_reader as real MySQL accounts first, with matching passwords on each relevant backend and accounts limited to the proxy source address. Grant writer only the application privileges it needs; reader gets only the required SELECT privileges. Verify authentication on the target MySQL version. Inserting rows below configures ProxySQL authentication; it neither creates backend accounts nor grants database permissions.

-- Accounts and matching passwords must already exist on MySQL.
INSERT INTO mysql_users
(username, password, active, default_hostgroup, default_schema,
 transaction_persistent, fast_forward, backend, frontend, max_connections)
VALUES
('app_writer', 'REPLACE_WITH_WRITER_SECRET', 1, 10, 'shopdb', 1, 0, 1, 1, 500),
('app_reader', 'REPLACE_WITH_READER_SECRET', 1, 20, 'shopdb', 1, 0, 1, 1, 500);
LOAD MYSQL USERS TO RUNTIME;

transaction_persistent=1 keeps an active transaction in its already selected hostgroup. It does not choose the writer and cannot move a transaction that began on a reader. With this configuration, begin write workflows using app_writer. The backend/frontend columns are booleans, not a database type or client-address allowlist. Network restrictions and backend account host restrictions remain separate controls.

Writer rules and an optional explicit read hint

Start with no blanket SELECT-routing rule. Default user hostgroups provide the basic split; the early terminating rule below also keeps app_writer in group 10. It is scoped by username and explicitly active. The optional second rule sends a leading /* to:master */ read from app_reader to group 10, where that backend account must also have SELECT access. Check earlier rules and ID conflicts when adapting an existing deployment.

-- Fresh rule set; verify rule IDs and ordering before adapting.
INSERT INTO mysql_query_rules
(rule_id, active, username, destination_hostgroup, apply)
VALUES (1, 1, 'app_writer', 10, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;
-- Optional: explicitly marked reads by app_reader may use the primary.
INSERT INTO mysql_query_rules
(rule_id, active, username, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, 'app_reader',
'^[[:space:]]*[/][*][[:space:]]*to:master[[:space:]]*[*][/]', 10, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;

The comment is an application convention interpreted by this exact raw-text rule, not a built-in freshness guarantee. It cannot override an active reader transaction when transaction persistence is enabled. Prefer the writer identity for critical workflows from the start. flagIN and flagOUT chain query rules; they do not detect whether a transaction needs the primary. A rule with no explicit active=1 is inactive by default.

Pool capacity and idle connections

Each server row’s max_connections=200 is a backend limit; each user’s max_connections=500 is a frontend limit. These are lab values, not sizing recommendations. Budget all proxy instances, hostgroups and direct clients against each MySQL server’s capacity. Long transactions, locks, temporary tables and user variables can occupy connections or prevent multiplexing, so measure the real application workload.

-- Read the current settings before changing them.
SELECT variable_name, variable_value FROM global_variables
WHERE variable_name IN ('mysql-free_connections_pct', 'mysql-session_idle_ms');

-- Optional lab value: upper retained idle fraction, not preallocation.
SET mysql-free_connections_pct=20;
LOAD MYSQL VARIABLES TO RUNTIME;

mysql-free_connections_pct limits retained idle backend connections relative to the server connection ceiling; this setting alone does not preopen that many connections. mysql-session_idle_ms moves idle sessions to an auxiliary thread rather than closing them. Leave unrelated timeout settings at their measured baseline; shortening timeouts can turn waits into application errors without increasing throughput.

WooCommerce and Laravel connection setup

WooCommerce: keep the normal connection on the writer

For this local lab, update the existing database constants in wp-config.php, rather than defining them twice. The writer password must match the account above; use the deployment’s protected secret mechanism. This configuration keeps WooCommerce’s normal database traffic on the primary.

// Existing wp-config.php database settings; local ProxySQL only.
define('DB_NAME', 'shopdb');
define('DB_USER', 'app_writer');
define('DB_PASSWORD', 'REPLACE_WITH_WRITER_SECRET');
define('DB_HOST', '127.0.0.1:6033');

Do not assume checkout or administration runs in one transaction. Keep order, stock, coupon, session and payment-related reads on the writer. Replica reads require a separately reviewed integration or reporting workload. HPOS may make dedicated order tables authoritative; legacy storage uses posts/postmeta, and the real prefix need not be wp_. Matching wp_wc_orders or shop_order alone cannot cover checkout consistency. Use storage-aware WooCommerce CRUD APIs and test the actual extensions and storage mode.

For container-based development, WordPress with Docker Compose provides related deployment context. Container networking changes the reachable hostname; 127.0.0.1 here works only when the application can reach the local proxy in its own network namespace. This guide’s database example remains MySQL 8.4.

Laravel 12: distinguish read and write users

Laravel supports per-side read/write configuration. The historical Laravel 10 read/write reference covers that earlier release; the behavior and code below are checked against Laravel 12. Separate usernames let Laravel’s write PDO select a path that ProxySQL keeps on the primary, even though both sides use the same TCP address.

First validate the application with all traffic using the writer. Enable the read section below only after classifying replica-tolerant paths and explicitly directing critical reads to the writer. Merge the fragment into the existing connections array in config/database.php; both password environment variables must match the provisioned accounts.

// Merge into the existing config/database.php connections array.
'mysql' => [
    'driver' => 'mysql',
    'host' => '127.0.0.1',
    'port' => 6033,
    'database' => env('DB_DATABASE', 'shopdb'),
    'username' => 'app_writer',
    'password' => env('DB_WRITE_PASSWORD'),
    'unix_socket' => '',
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'read' => [
        'username' => 'app_reader',
        'password' => env('DB_READ_PASSWORD'),
    ],
    'write' => [
        'username' => 'app_writer',
        'password' => env('DB_WRITE_PASSWORD'),
    ],
    'sticky' => true,
],

With sticky=true, Laravel uses its write connection for reads after a write during the same request cycle. This depends on the connection’s modification state, not a seconds timer. A later HTTP request or queue job needs its own consistency decision. Within a Laravel transaction, reads use the write PDO; here that PDO authenticates as app_writer.

use Illuminate\Support\Facades\DB;

// Existing Order model uses the mysql connection; $orderData is validated.
$order = DB::connection('mysql')->transaction(function () use ($orderData) {
    $order = Order::on('mysql')->create($orderData);
    return Order::on('mysql')->whereKey($order->id)->firstOrFail();
});

// A critical read in a later request/job must choose the writer explicitly.
$fresh = DB::connection('mysql')->table('orders')
    ->useWritePdo()->where('id', $order->id)->first();

The example assumes an existing Order model on the named mysql connection, validated $orderData and an orders table; it is not a complete payment or inventory workflow. In a later request or job, use the known order ID with the demonstrated useWritePdo() read. The optional SQL comment rule is unnecessary for this explicit writer selection. Verify configuration reload and connection lifecycle for long-running workers.

What About Caching?

Caching can reduce repeat reads, but the cache policy must exclude private or rapidly changing responses where appropriate. A cart or checkout response cannot be treated like an anonymous catalog page. See Nginx microcaching for PHP for related cache design; measure hit rate and verify bypass/invalidation behavior rather than assuming instant responses.

Replica health, session state and TLS

Keep a baseline of application errors, checkout success, p95/p99 latency, active connections and replication state. Change one routing or pool decision at a time. A faster average query is not sufficient if tail latency or correctness regresses.

Replica Lag and Sensitive Reads

MySQL asynchronous replicas may not yet contain a committed primary write. The per-server max_replication_lag threshold helps remove lagging readers; it cannot prove that a particular transaction is visible. Check missing/error lag samples and replication health too. If group 20 has no eligible server, reads may wait or fail; this configuration does not automatically reroute them to group 10. A controlled application fallback must select the writer identity and account for its extra load.

For background, the retained MySQL 8.0 replication overview describes the earlier release. This example targets MySQL 8.4: verify actual receiver/applier health, the current source and SHOW REPLICA STATUS output. A low reported lag alone is not a causal-read guarantee or a failover decision.

Connection Pooling and Multiplexing

A reusable backend connection can serve multiple frontend sessions in turn. Active transactions, user variables, temporary tables and certain locks can inhibit reuse. Multiplexing being disabled does not also disable routing: a query sent to another backend cannot see a temporary table from the previous session. Stateful workloads may need a dedicated identity and hostgroup.

Inspect current session state alongside connection-pool and query statistics. Keep transactions as short as the business operation allows, and test libraries that set session variables. Avoid forcing multiplexing back on simply to reduce a connection-count graph when the session needs its state preserved.

Prepared Statements, Timeouts, and Retries

Protocol prepared statements and SQL-text PREPARE have different multiplexing behavior; test the protocol used by the actual PHP driver. Monitor statement and connection memory, query duration and errors. Apply retries only where the operation is safe to repeat: a timeout does not prove a write or payment failed before taking effect. Do not hide replica failures by increasing the lag threshold without considering data freshness.

TLS Between ProxySQL and MySQL

Frontend application–ProxySQL TLS and backend ProxySQL–MySQL TLS are independent. The loopback frontend example does not claim encryption; a remote deployment must configure and require TLS on that hop, including mysql_users.use_ssl=1 where required. Backend mysql_servers.use_ssl=1 needs the corresponding CA/certificate setup. Encryption being enabled does not alone prove peer identity validation: test both successful and invalid-certificate connections and certificate renewal. For broader payment-hosting context, see the WooCommerce hosting PCI DSS checklist; ProxySQL configuration alone does not establish compliance.

Observe actual routing and failures

Use the admin connection opened earlier to run the following SQL. Compare the user and hostgroup for expected workloads with backend connection counts and replication monitor errors. Capture comparable intervals and account for restarts or counter resets.

Connection, digest and rule statistics

-- SQL only: run inside the ProxySQL admin console.
SELECT hostgroup, srv_host, srv_port, status, ConnUsed, ConnFree, ConnERR, Queries
FROM stats_mysql_connection_pool ORDER BY hostgroup, srv_host;

SELECT username, hostgroup, digest, digest_text, count_star,
       ROUND(sum_time / NULLIF(count_star, 0) / 1000.0, 2) AS avg_ms
FROM stats_mysql_query_digest ORDER BY sum_time DESC LIMIT 20;

SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY rule_id;
SELECT hostname, port, repl_lag, error
FROM monitor.mysql_server_replication_lag_log
ORDER BY time_start_us DESC LIMIT 10;

The digest mean is converted from microseconds to milliseconds and can include waiting within ProxySQL, not just MySQL execution. Rule hit counts show aggregate matches; they do not establish the whole path of one request. Use targeted logging and application traces for ambiguous cases, with access and retention limits because query text can contain sensitive data.

Connect checkout traces to database timings so that connection waiting, SQL execution and external payment latency can be distinguished. The related guide to application tracing describes that approach. Verify useful correlation in your instrumentation rather than assuming spans automatically cover every hop.

Test the application’s failure behavior

In the isolated lab, introduce replica lag and loss, confirm monitor errors and reader eligibility changes, then verify recovery. Also test all readers being unavailable. Restart a lab primary only as a planned failure drill and verify application errors and recovery; ProxySQL monitoring does not promote a replacement or fence an old primary. A read_only change alone is not proof that a server is the safe writer.

Test browsing, concurrent stock updates, checkout, payment returns, order confirmation, administration and background jobs with the actual storage mode and extensions. Confirm critical traffic uses app_writer and hostgroup 10. If results are stale or inconsistent, restore the writer-only application path and diagnose the entire flow; adding one comment to the final SELECT is not a general repair.

Persistence, rollback and upgrades

The examples LOAD changes to runtime without immediately saving them. After authentication, routing, TLS and failure tests pass, persist the intended memory configuration. Before changing an existing system, retain the previous rows and application settings. Rollback means restoring the relevant prior values, loading the affected module, validating behavior and persisting the correction; restarting is not a universal undo operation.

-- After routing, authentication, TLS and failure tests pass:
SAVE MYSQL VARIABLES TO DISK;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;

Keep recoverable configuration

Protect a consistent backup of ProxySQL’s persistent configuration and record changes in deployment tooling. Password-bearing configuration should not enter an ordinary public or broadly readable repository. cloud-init and Ansible provisioning can help make host setup repeatable; secret handling and configuration restore still need explicit tests.

Rolling Restarts and Upgrades

A proxy restart interrupts its client sessions. Multiple proxies and a tested frontend failover path can reduce service impact but do not transparently preserve every transaction. Drain and check pinned sessions before maintenance; OFFLINE_SOFT does not immediately terminate existing pinned backend work. Test upgrades with representative authentication, prepared statements, replication monitoring and TLS before rollout.

Security Basics You’ll Thank Yourself For

Limit admin access to the required local or management addresses, rotate credentials and firewall the application listener. Real MySQL accounts should accept only the proxy sources and the privileges each workload needs. Routing fields such as client_addr select query rules; they are not a substitute for a complete network access policy.

Apply the boundary to common workloads

Campaign traffic

For a hypothetical campaign with many repeated catalog views, caching may remove a large share of repeated work. Separately reviewed, stale-tolerant reporting or catalog integrations may use app_reader. Keep normal WooCommerce traffic on the writer until that integration is proven, and compare measured checkout success and latency at the same offered load. No fixed speedup follows from installing ProxySQL.

Stock and order visibility

A primary read avoids reading an unapplied replica copy, but does not alone prevent overselling. Concurrent buyers still require the application’s atomic stock handling and transaction/locking design. Test low-stock contention, retries and payment callbacks, along with order and object-cache visibility across requests.

Laravel Jobs and Queues

Queue jobs may keep connections longer than web requests. Route consistency-critical jobs through the writer from their start, even when a previous job already wrote data. Do not assume sticky state forms a freshness contract between jobs. Verify worker configuration reload, cleanup of transactions/session state and safe retry behavior.

When Caching Isn’t Enough

Personalized carts, coupons and live inventory cannot all share one public cache policy. Combine a measured cache strategy with controlled application concurrency and the appropriate database identity. Cache misses and invalidation errors must be included in the test workload.

Routing mistakes to avoid

A blanket SELECT rule includes locking and freshness-sensitive reads; adding only a FOR UPDATE exception misses FOR SHARE, legacy LOCK IN SHARE MODE and ordinary reads that need recent data. Keep the writer as the initial path and route only reviewed replica-tolerant work. Query text alone cannot recover missing application intent.

A report that holds a transaction or streams rows for minutes can occupy a backend connection for that duration. Consider pagination or a dedicated reporting identity/hostgroup where the required data staleness permits it. The default_hostgroup and username-scoped rules provide that separation; the report’s database privileges and resource budget must match its purpose.

Verify that only the intended primary is writable, replicas are protected and replication is healthy, and backend authentication works on every relevant node. In MySQL, read_only permits some privileged clients to write; super_read_only provides stronger client-write restrictions while replication threads can still apply changes. Never grant broad administrative privileges merely to bypass an authentication or replication problem.

Advanced features need separate validation

Treat scheduler automation and query mirroring as separate changes requiring version-specific validation. Promotion and fencing still need a tested failover controller/runbook. Before shadowing traffic, establish which statements would execute at the destination and what side effects they could cause; use an isolated, sanitized test system and an explicit workload policy. MySQL roles and permissions remain backend authorization responsibilities; adding routing rules does not reproduce them automatically.

Keep a recovery runbook covering database backups, proxy configuration, credentials, the application endpoint and the former writer’s isolation. A failover test should record what happened to in-flight writes and whether any acknowledged transactions were lost, rather than treating an ONLINE status as recovery proof.

Production acceptance criteria

The configuration is ready for gradual rollout when writer workflows stay in group 10, replica access is limited to reviewed paths, and reader loss produces the expected application response. Connection budgets, authentication/TLS tests, persistence and rollback must also be verified. Compare performance under the same workload and preserve correctness as an explicit acceptance criterion.