ProxySQL, Calm Checkout: Read/Write Split and Pooling for WooCommerce and Laravel (Without the Drama)

The Night a Flash Sale Turned My Database Into a Bottleneck

It was one of those evenings where you swear you did everything right. New campaign, email blast ready, WooCommerce store polished, Laravel back office humming. And then the clock hit 8 PM. Add to cart buttons were getting tapped like a metronome, checkout requests doubled, and the database graph went straight up like a rocket. Connections piled up. CPU got hot. It wasn’t a traffic problem; it was a MySQL connection and concurrency problem.

I remember watching php-fpm spawn more workers, which in turn opened more database connections, which made the database even unhappier. You know that feeling when you see wait_timeout, aborted connects, and slow queries all staring back at you? Not fun. That’s when I pulled a tool out of my kit that’s bailed me out more than once: ProxySQL for read/write splitting and connection pooling.

If you’ve ever had a WooCommerce checkout feel sticky or a Laravel job queue start tripping over database connections, this guide is for you. We’ll talk about how to put ProxySQL in front of MySQL or MariaDB, split reads and writes safely, pool connections so PHP doesn’t hammer your DB directly, and keep the whole thing observable and calm. I’ll share the exact approach I use, the edges to watch, and how I keep stock counts and orders consistent when things get busy.

Why ProxySQL Works So Well for WooCommerce and Laravel

Here’s the thing: PHP apps like WordPress/WooCommerce and Laravel are bursty. They don’t hold a database connection all day long—each request is a tiny world that spins up, does its queries, and disappears. During a sale or a product launch, those worlds multiply. Without a layer in the middle, you can end up with hundreds or thousands of short-lived database connections. MySQL is fast, but it’s not a fan of being used like a connection vending machine.

ProxySQL sits between your app and your database. Your PHP code connects to ProxySQL (usually on 127.0.0.1:6033) instead of hitting MySQL directly. ProxySQL keeps a pool of warm connections open to your primary and replicas, multiplexes short queries across them, and—this is the magic—routes writes to the primary and reads to replicas. That’s read/write splitting in plain terms.

In my experience, WooCommerce and Laravel both benefit in a few big ways: first, checkout stays responsive because writes don’t fight with catalog browsing reads. Second, you use your replicas effectively without changing a ton of application code. Third, connection pooling keeps MySQL from being overwhelmed by sudden spikes of PHP workers. It’s like adding a friendly bouncer at the club door that knows where to send everyone inside.

Now, there are nuances. Stock levels, carts, and orders in WooCommerce need fresh reads after a write. Laravel often bundles order creation and inventory updates in transactions. We’ll handle those safely. But first, let’s set the stage.

The Architecture: Where ProxySQL Sits and How It Thinks

Think of it like this: PHP-FPM talks to ProxySQL, ProxySQL talks to MySQL. If you’ve got a primary and a couple of replicas, ProxySQL knows who can write and who should only read. It health-checks servers, watches replication lag, and reroutes traffic to keep things consistent.

I usually run ProxySQL on the same VM as PHP-FPM or in the same Kubernetes node as a sidecar. Localhost latency helps connection pooling shine. In other setups, a tiny dedicated ProxySQL node (or pair) works fine, as long as you watch for its CPU and memory.

Out of the box, ProxySQL exposes two ports: the admin interface on 6032 and the MySQL listener on 6033. Your app points to 6033 like it’s MySQL. You can configure everything at runtime by connecting to 6032 using the mysql client. Under the hood, ProxySQL stores config in its own SQLite database and keeps a runtime vs. on-disk separation so you can test and roll back cleanly. That separation has saved me from fat-fingered mistakes more than once.

Read/Write Split in Plain English

ProxySQL looks at queries and makes routing decisions. Inserts, updates, and deletes head to the primary. Plain selects go to replicas—unless they’re dangerous (like a SELECT … FOR UPDATE) or they’re inside a transaction that needs stickiness. With replica lag checks enabled, ProxySQL can force sensitive reads to the primary if a replica is behind. That’s the safety net you want for carts and stock counts.

And because ProxySQL pools connections, it can reuse an existing connection to the database for multiple short app requests—like lending the same phone line to different callers one after the other, instead of installing a new line for each call. PHP doesn’t know or care; it’s just faster and calmer under load.

Hands-On: A Practical ProxySQL Setup That Holds Up Under Load

I’ll walk you through a setup I’ve used in production for WooCommerce front-ends and Laravel back-ends. The versions change, but the pattern stays solid. We’ll assume a primary on db-primary:3306 and two replicas on db-replica-1 and db-replica-2.

Install ProxySQL

On Debian/Ubuntu-based hosts, installation is straightforward. The package creates a service and a default admin credential you should immediately change.

# Ubuntu/Debian example
apt-get update && apt-get install -y proxysql mysql-client

# Start and enable
systemctl enable --now proxysql

# Connect to admin (port 6032)
mysql -u admin -p -h 127.0.0.1 -P6032

Once inside the admin console, we’ll define servers, users, and read/write rules. If you like to learn from the source, the official docs are excellent: ProxySQL documentation.

Define Primary and Replicas

We’ll use hostgroups to separate the writer (10) and readers (20). Then, a replication mapping tells ProxySQL who’s who and how to react to lag.

-- Add servers
INSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES
  (10, 'db-primary', 3306),
  (20, 'db-replica-1', 3306),
  (20, 'db-replica-2', 3306);

-- Map writer/reader groups and configure safety valves
INSERT INTO mysql_replication_hostgroups (
  writer_hostgroup, reader_hostgroup, check_type, comment, 
  max_writers, writer_is_also_reader, max_replication_lag
) VALUES (
  10, 20, 'read_only', 'rw split',
  1, 0, 2
);

-- Monitor credentials (create these in MySQL with minimal privileges)
SET mysql-monitor_username='monitor';
SET mysql-monitor_password='yourStrongMonitorPass';
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

-- Load servers and replication mapping
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

The max_replication_lag of 2 seconds is a friendly starting point. If a replica falls behind more than that, ProxySQL will stop sending it reads. You can tune that based on your workload and tolerance for slightly stale reads. When in doubt, lean conservative for carts and checkouts.

Create an App User in ProxySQL

Your application connects to ProxySQL using a MySQL user and password that ProxySQL knows about. ProxySQL then authenticates to the real MySQL servers using the same or different credentials.

-- App user that your PHP app will use when connecting to 127.0.0.1:6033
INSERT INTO mysql_users (
  username, password, default_hostgroup, transaction_persistent, fast_forward
) VALUES (
  'appuser', 'yourStrongAppPass', 10, 1, 0
);

-- Optional: restrict which client addresses may use this user
-- UPDATE mysql_users SET default_schema='shopdb', backend='mysql' WHERE username='appuser';

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

A quick bit of context: transaction_persistent=1 tells ProxySQL to keep a transaction on the same server/hostgroup once started. For WooCommerce and Laravel write-heavy flows, that’s what you want.

Write-Safe, Read-Hungry Query Rules

Here’s a conservative rule set I start with. All writes go to the writer hostgroup. Read-only selects go to the readers, except when they’re hazardous or inside patterns we mark for the primary. The “FOR UPDATE” guard is small but mighty.

-- Route obvious writes to the primary
INSERT INTO mysql_query_rules (
  rule_id, active, match_digest, destination_hostgroup, apply
) VALUES 
  (10, 1, '^(?i)(INSERT|UPDATE|DELETE|REPLACE|CREATE|ALTER|DROP|TRUNCATE)', 10, 1);

-- SELECT ... FOR UPDATE must hit the primary
INSERT INTO mysql_query_rules (
  rule_id, active, match_digest, destination_hostgroup, apply
) VALUES
  (20, 1, '^(?i)SELECT.*FORs+UPDATE', 10, 1);

-- Respect explicit app hints to force the primary
INSERT INTO mysql_query_rules (
  rule_id, active, match_pattern, destination_hostgroup, apply
) VALUES
  (30, 1, '/*s*to:masters**/', 10, 1);

-- Route plain SELECTs to readers by default
INSERT INTO mysql_query_rules (
  rule_id, active, match_digest, destination_hostgroup, apply
) VALUES
  (40, 1, '^(?i)SELECT', 20, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

A few notes from the field: that comment-based hint (/* to:master */) is a simple way to force a read to the primary when you absolutely need fresh data right after a write. I use it sparingly, usually around stock checks or order review reads that follow inserts. Laravel makes it easy to add comments in queries; we’ll look at that soon.

Pooling and Multiplexing: Let ProxySQL Do Its Thing

ProxySQL’s connection pooling works best when queries are short-lived and you avoid session-level settings that block multiplexing (things like user-defined variables or temp tables). For WooCommerce and most Laravel apps, that’s a fair default. You can still use prepared statements; ProxySQL handles them well, just be mindful of long-lived sessions that disable multiplexing across requests.

-- Sensible defaults for connection pool behavior (tune as needed)
SET mysql-connect_timeout_server=3000;            -- ms
SET mysql-ping_timeout_server=1500;               -- ms
SET mysql-query_processor_time_ms=10000;          -- ms
SET mysql-free_connections_pct=20;                -- keep some idle, ready to serve
SET mysql-session_idle_ms=30000;                  -- close very idle client sessions

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Don’t worry if you don’t memorize those. The idea is to maintain a healthy pool and steer clear of edge cases where slow or sticky sessions pile up. Start with defaults, measure, and adjust.

Point WooCommerce and Laravel at ProxySQL (and Keep Your Data Fresh)

WooCommerce: wp-config.php Aware, Checkout-Friendly

For WordPress/WooCommerce, you simply point DB_HOST to ProxySQL’s listener. If ProxySQL runs locally, it’s usually 127.0.0.1:6033; otherwise, point to its IP and port. Your DB name, user, and password align with what you inserted into ProxySQL’s mysql_users.

// wp-config.php
define('DB_NAME',     'shopdb');
define('DB_USER',     'appuser');
define('DB_PASSWORD', 'yourStrongAppPass');
// If ProxySQL runs locally on the web node
define('DB_HOST',     '127.0.0.1:6033');

WordPress doesn’t use explicit transactions in most places, and WooCommerce carefully handles stock updates with writes and opportunistic reads. With the rules above, catalog browsing gets fanned out to replicas, while writes and transactional reads stick to the primary. If you have a sensitive read-after-write moment—say immediately re-reading stock or the fresh order row—add a targeted application hint. There are filters in WooCommerce/WordPress to inject SQL comments or to bypass the object cache for a specific call; use them like seasoning, not like sauce.

If you’re building on containers, I’ve shared how I wire WordPress and MariaDB neatly for local or small clusters. That approach pairs nicely with ProxySQL when you’re ready to scale reads: WordPress on Docker Compose, Without the Drama: Nginx, MariaDB, Redis, Persistent Volumes, Auto‑Backups, and a Calm Update Flow.

Laravel: Read/Write Splitting Without Turning Your Code Inside Out

Laravel actually has built-in support for read/write connections, which you can keep using even with ProxySQL. But if you’re putting ProxySQL in the middle, you can sometimes simplify and point both reads and writes to ProxySQL, letting it do the heavy lifting. If you prefer explicit control, Laravel’s read/write config works beautifully too. The docs cover it well here: Laravel read and write connections.

To use ProxySQL as your single DB endpoint:

// config/database.php (snippet)
'mysql' => [
    'driver' => 'mysql',
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '6033'), // ProxySQL listener
    'database' => env('DB_DATABASE', 'shopdb'),
    'username' => env('DB_USERNAME', 'appuser'),
    'password' => env('DB_PASSWORD', 'yourStrongAppPass'),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'strict' => true,
    'engine' => null,
],

For read-after-write stickiness, you’ve got two levers: transactions and hints. In code, I often wrap order creation and stock updates in an explicit transaction, which naturally pins the flow to the primary thanks to transaction_persistent.

DB::transaction(function () use ($orderData) {
    // Writes go to primary
    $order = Order::create($orderData);

    // If you must re-read and be 100% fresh, add a tiny hint
    $fresh = DB::select('/* to:master */ SELECT * FROM orders WHERE id = ?', [$order->id]);

    // ... further writes or reads that must be fresh
});

Yes, that comment looks funny. But it’s simple, and ProxySQL’s rule 30 from earlier catches it and routes the query to the writer hostgroup. Don’t sprinkle it everywhere—use it where correctness matters immediately after a write, like stock checks.

What About Caching?

Caching and ProxySQL get along. While ProxySQL smooths out DB contention, smart caching makes everything feel instant. If your catalog pages can tolerate brief cache windows, microcaching on Nginx in front of PHP can be magical for traffic spikes. I’ve written about that approach here: The 1–5 Second Miracle: How Nginx Microcaching Makes PHP Feel Instantly Faster.

Tuning ProxySQL for Performance and Safety

Let’s talk about knobs that matter. You don’t have to turn all of them on day one. Start with a clean baseline, measure, and adjust while watching the stats tables (we’ll get to those next).

Replica Lag and Sensitive Reads

Replication is amazing, but it’s asynchronous by default. A burst of writes can briefly put replicas behind. That’s why the max_replication_lag setting in mysql_replication_hostgroups is your invisible seatbelt. When lag exceeds that threshold, ProxySQL stops sending reads there. You can be even stricter by placing an app hint on reads that must be absolutely current, forcing them to the writer regardless of lag.

If you want to dive deeper into the underlying replication mechanics, the official MySQL docs are a good companion for a quiet evening: MySQL replication overview.

Connection Pooling and Multiplexing

ProxySQL’s pool reduces the number of physical connections MySQL has to juggle. Multiplexing takes it further by allowing ProxySQL to switch a single backend connection between multiple client sessions, as long as the session state is clean. What breaks multiplexing? Things like user-defined variables, temporary tables, or long-running transactions. In most WooCommerce and Laravel requests, you’re fine.

If you see strange behavior, measure how many of your queries disable multiplexing. The stats tables make this visible, and you can tweak your code or library defaults. As a rule of thumb, avoid setting odd session variables unless needed, and keep transactions tight.

Prepared Statements, Timeouts, and Retries

Prepared statements are standard and ProxySQL handles them well. If you have a workload dominated by prepared statements, keep an eye on statement cache memory. Timeouts are another lever; shorter timeouts keep pools healthy, but don’t set them so low that you turn transient blips into user-visible errors. If a replica is flapping, disable it or increase its threshold rather than rely on aggressive retries.

TLS Between ProxySQL and MySQL

When compliance or paranoia (both good qualities) are in play, enable TLS between ProxySQL and your database servers. ProxySQL can present client certs and validate server certs, and MySQL can enforce SSL at user level. This pairs well with broader security hardening—especially if you’re handling real card data. If that’s your world, I have a friendly checklist that keeps WooCommerce hosts out of trouble: The Calm PCI‑DSS Checklist for WooCommerce Hosting.

Observability, Debugging, and “What Just Happened?” Moments

When I first put ProxySQL in front of a busy store, I found myself staring at its stats like a weather radar. It’s addicting—and useful. The admin interface exposes a handful of tables that tell the story in real time.

Stats You’ll Actually Use

-- Connect to admin again
mysql -u admin -p -h 127.0.0.1 -P6032

-- Connection pool health
SELECT * FROM stats_mysql_connection_pool G

-- Read/write split effectiveness by hostgroup
SELECT hostgroup, srv_host, ConnUsed, Queries FROM stats_mysql_connection_pool ORDER BY hostgroup, srv_host;

-- Query digest: who’s hot, who’s slow
SELECT * FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 20;

-- Which rules are actually matching
SELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY hits DESC;

If you ever wonder “why did this SELECT go to the primary,” check the query rules hits and the digest tables. They’ll show you the pattern that matched. You can also enable log events for rule matches during troubleshooting and then turn them off once calm returns.

For deeper request tracing across PHP, ProxySQL, and MySQL, modern tracing helps a ton. I’ve shared how I stitch Laravel and Node.js into end-to-end traces you can read like a chat transcript: Tracing That Feels Like a Conversation With Your App. Add a DB span around checkout and watch it glide from app server to ProxySQL to DB. It’s oddly satisfying.

Practical Tests Before a Big Day

I like to run a rehearsal. If you have replicas, pull one behind intentionally and watch ProxySQL stop sending it reads. Then restore it and confirm reads resume. Simulate a brief primary restart and see that ProxySQL routes writes back when the primary is writable again (check_type=read_only relies on the server’s read_only flag, which is the right signal in most topologies).

For WooCommerce, put a load test on browsing and checkout flows—keep it respectful, but enough to see the split in action. If you got over-exuberant with read routing and you see a stock mismatch after checkout, add the “/* to:master */” hint where your first post-checkout read happens. It’s a small tweak that often saves you from overthinking consistency.

Maintenance, Deployments, and Staying Calm

One thing I love about ProxySQL is how it separates runtime changes from saved config. You can test changes, check behavior, and only then save to disk. If you mess up a rule, it disappears on restart unless you saved it—like a built-in safety rope.

Keep Your Config, But Don’t Get Bit

ProxySQL stores its persistent config in a SQLite file. Back it up along with your infrastructure-as-code or runbook. If you’re already automating your stack, fold ProxySQL into your playbooks. Whether you’re a Terraform/Ansible fan or prefer other tools, the pattern is the same: declare servers, users, and rules, and keep them version-controlled. If you want a gentle nudge on repeatable first-boot setups, I share my approach here: From Blank VPS to Ready‑to‑Serve: How I Use cloud‑init + Ansible for Users, Security, and Services on First Boot.

Rolling Restarts and Upgrades

ProxySQL restarts are quick, but treat them with respect on big traffic days. If you run a pair of ProxySQL nodes behind a VIP or in Kubernetes, you can roll them one at a time. Always test major upgrades in a staging environment that mirrors production, especially if you rely on newer features around replication awareness or TLS.

Security Basics You’ll Thank Yourself For

Restrict the admin interface to localhost or a management network. Rotate admin credentials. Limit which client addresses can connect with your appuser in ProxySQL. And if you’re putting ProxySQL on separate hosts, firewall it like any other sensitive component. It’s a database gateway; treat it like one.

Real-World Scenarios and Friendly Patterns

Surviving Checkout Spikes

On a recent campaign, we saw 10x normal browsing with a much smaller increase in checkouts. ProxySQL meant the extra reads didn’t drown the primary—the replicas took the brunt. The checkout path stayed smooth because writes and hazardous reads were directed to the primary. We kept a single “freshness” hint on the post-checkout order confirmation read. That one line was the difference between confidence and “did the order really go through?” doubts.

WooCommerce Stock: Freshness Where It Matters

Stock can be touchy. If your store has low stock items and fast buyers, a stale read can cause oversells. You don’t want to force every product page to the primary though—that’s the quickest way to throw away the benefits of splitting. My practical rule: allow catalog reads to hit replicas, but force the reads closest to the cart and checkout to the primary. The number of those reads is tiny compared to general browsing. The net result feels like magic under load.

Laravel Jobs and Queues

Queue workers can be chatty with the database. Point them to ProxySQL too. In fact, because queues often run on separate nodes, they stand to benefit the most from pooled connections. If a job needs fresh reads right after it writes, add a hint or keep the flow inside a transaction. Simple and predictable.

When Caching Isn’t Enough

Caching is a speed boost, not a database strategy. The moment you have personalized carts, coupons, and real-time stock, you still need your database to be responsive. That’s why I see caching and ProxySQL as complementary. Cache what you can. Use ProxySQL to make the parts you can’t cache efficient and sane.

Common Gotchas (And How I Learned Them)

Early on, I got overly excited and routed all SELECTs to replicas without exceptions. Worked great for a day, until a coupon system that relied on SELECT … FOR UPDATE started sending those reads to replicas. The locks weren’t taking effect where they should, and we got a mini disaster. The fix was as simple as we discussed: match “FOR UPDATE” and force it to the primary. I still smile when I add that rule, remembering what it saved me from.

Another one: long transactions from an admin report. Reports that stream rows for minutes can clog a pool if you’re not careful. If you can paginate, do it. If you must stream, run it off-hours or point that job to a dedicated replica. ProxySQL gives you the knobs (client_addr, username-based routing) to send specific users to specific hostgroups, which is handy for this exact scenario.

Finally, don’t forget that replicas must be configured correctly—read_only set, authentication consistent, and replication healthy. Keep your replication status visible and alert on lag. The best ProxySQL rules can’t save a broken replica.

Helpful Extras When You’re Ready for Advanced Moves

Once you’ve got the basics, consider a few niceties. Use ProxySQL’s scheduler for lightweight health checks or to toggle a server in or out during maintenance windows. Explore query mirroring if you want to shadow traffic to a new database version safely. And yes, you can teach ProxySQL to understand roles in MySQL 8 or permissions in MariaDB, but keep the mapping simple at first.

If you pair ProxySQL with solid operational discipline—backups, DR drills, clear runbooks—you’re in a good place. If writing a disaster recovery plan sounds heavy, I promise it doesn’t have to be: practical beats perfect every time.

Wrap-Up: Calm, Fast, and Boring (The Good Kind)

There’s a certain peace when checkout traffic spikes and your graphs barely flinch. ProxySQL gives you that, by splitting reads from writes, pooling connections so MySQL isn’t harassed, and giving you knobs to keep data fresh where it matters. For WooCommerce, that means smooth catalog browsing and confident stock and order reads. For Laravel, it means fewer architectural contortions to scale the database side—not a small win.

If you’re starting from scratch, keep it simple: deploy ProxySQL close to your app, route writes to the primary and safe reads to replicas, add a “FOR UPDATE” guard and a tiny “/* to:master */” hint where you need absolute freshness. Watch the stats tables for a week, then tune based on what you see. Layer in caching where it helps. And measure, always.

If you want more reading while your coffee cools, the ProxySQL docs are a friendly rabbit hole. And when you’re ready to double down on observability, the approach I shared in tracing that feels like a conversation with your app changes how you debug.

Hope this was helpful! If it saves just one checkout night from chaos, it’s a win in my book. See you in the next post—and may your replicas stay fresh, your pools stay warm, and your checkouts stay boring.