{"id":1701,"date":"2025-11-11T18:20:15","date_gmt":"2025-11-11T15:20:15","guid":{"rendered":"https:\/\/www.dchost.com\/blog\/proxysql-calm-checkout-read-write-split-and-pooling-for-woocommerce-and-laravel-without-the-drama\/"},"modified":"2025-11-11T18:20:15","modified_gmt":"2025-11-11T15:20:15","slug":"proxysql-calm-checkout-read-write-split-and-pooling-for-woocommerce-and-laravel-without-the-drama","status":"publish","type":"post","link":"https:\/\/www.dchost.com\/blog\/en\/proxysql-calm-checkout-read-write-split-and-pooling-for-woocommerce-and-laravel-without-the-drama\/","title":{"rendered":"ProxySQL, Calm Checkout: Read\/Write Split and Pooling for WooCommerce and Laravel (Without the Drama)"},"content":{"rendered":"<div class=\"dchost-blog-content-wrapper\"><div id=\"toc_container\" class=\"toc_transparent no_bullets\"><p class=\"toc_title\">\u0130&ccedil;indekiler<\/p><ul class=\"toc_list\"><li><a href=\"#The_Night_a_Flash_Sale_Turned_My_Database_Into_a_Bottleneck\"><span class=\"toc_number toc_depth_1\">1<\/span> The Night a Flash Sale Turned My Database Into a Bottleneck<\/a><\/li><li><a href=\"#Why_ProxySQL_Works_So_Well_for_WooCommerce_and_Laravel\"><span class=\"toc_number toc_depth_1\">2<\/span> Why ProxySQL Works So Well for WooCommerce and Laravel<\/a><\/li><li><a href=\"#The_Architecture_Where_ProxySQL_Sits_and_How_It_Thinks\"><span class=\"toc_number toc_depth_1\">3<\/span> The Architecture: Where ProxySQL Sits and How It Thinks<\/a><ul><li><a href=\"#ReadWrite_Split_in_Plain_English\"><span class=\"toc_number toc_depth_2\">3.1<\/span> Read\/Write Split in Plain English<\/a><\/li><\/ul><\/li><li><a href=\"#Hands-On_A_Practical_ProxySQL_Setup_That_Holds_Up_Under_Load\"><span class=\"toc_number toc_depth_1\">4<\/span> Hands-On: A Practical ProxySQL Setup That Holds Up Under Load<\/a><ul><li><a href=\"#Install_ProxySQL\"><span class=\"toc_number toc_depth_2\">4.1<\/span> Install ProxySQL<\/a><\/li><li><a href=\"#Define_Primary_and_Replicas\"><span class=\"toc_number toc_depth_2\">4.2<\/span> Define Primary and Replicas<\/a><\/li><li><a href=\"#Create_an_App_User_in_ProxySQL\"><span class=\"toc_number toc_depth_2\">4.3<\/span> Create an App User in ProxySQL<\/a><\/li><li><a href=\"#Write-Safe_Read-Hungry_Query_Rules\"><span class=\"toc_number toc_depth_2\">4.4<\/span> Write-Safe, Read-Hungry Query Rules<\/a><\/li><li><a href=\"#Pooling_and_Multiplexing_Let_ProxySQL_Do_Its_Thing\"><span class=\"toc_number toc_depth_2\">4.5<\/span> Pooling and Multiplexing: Let ProxySQL Do Its Thing<\/a><\/li><\/ul><\/li><li><a href=\"#Point_WooCommerce_and_Laravel_at_ProxySQL_and_Keep_Your_Data_Fresh\"><span class=\"toc_number toc_depth_1\">5<\/span> Point WooCommerce and Laravel at ProxySQL (and Keep Your Data Fresh)<\/a><ul><li><a href=\"#WooCommerce_wp-configphp_Aware_Checkout-Friendly\"><span class=\"toc_number toc_depth_2\">5.1<\/span> WooCommerce: wp-config.php Aware, Checkout-Friendly<\/a><\/li><li><a href=\"#Laravel_ReadWrite_Splitting_Without_Turning_Your_Code_Inside_Out\"><span class=\"toc_number toc_depth_2\">5.2<\/span> Laravel: Read\/Write Splitting Without Turning Your Code Inside Out<\/a><\/li><li><a href=\"#What_About_Caching\"><span class=\"toc_number toc_depth_2\">5.3<\/span> What About Caching?<\/a><\/li><\/ul><\/li><li><a href=\"#Tuning_ProxySQL_for_Performance_and_Safety\"><span class=\"toc_number toc_depth_1\">6<\/span> Tuning ProxySQL for Performance and Safety<\/a><ul><li><a href=\"#Replica_Lag_and_Sensitive_Reads\"><span class=\"toc_number toc_depth_2\">6.1<\/span> Replica Lag and Sensitive Reads<\/a><\/li><li><a href=\"#Connection_Pooling_and_Multiplexing\"><span class=\"toc_number toc_depth_2\">6.2<\/span> Connection Pooling and Multiplexing<\/a><\/li><li><a href=\"#Prepared_Statements_Timeouts_and_Retries\"><span class=\"toc_number toc_depth_2\">6.3<\/span> Prepared Statements, Timeouts, and Retries<\/a><\/li><li><a href=\"#TLS_Between_ProxySQL_and_MySQL\"><span class=\"toc_number toc_depth_2\">6.4<\/span> TLS Between ProxySQL and MySQL<\/a><\/li><\/ul><\/li><li><a href=\"#Observability_Debugging_and_What_Just_Happened_Moments\"><span class=\"toc_number toc_depth_1\">7<\/span> Observability, Debugging, and \u201cWhat Just Happened?\u201d Moments<\/a><ul><li><a href=\"#Stats_Youll_Actually_Use\"><span class=\"toc_number toc_depth_2\">7.1<\/span> Stats You\u2019ll Actually Use<\/a><\/li><li><a href=\"#Practical_Tests_Before_a_Big_Day\"><span class=\"toc_number toc_depth_2\">7.2<\/span> Practical Tests Before a Big Day<\/a><\/li><\/ul><\/li><li><a href=\"#Maintenance_Deployments_and_Staying_Calm\"><span class=\"toc_number toc_depth_1\">8<\/span> Maintenance, Deployments, and Staying Calm<\/a><ul><li><a href=\"#Keep_Your_Config_But_Dont_Get_Bit\"><span class=\"toc_number toc_depth_2\">8.1<\/span> Keep Your Config, But Don\u2019t Get Bit<\/a><\/li><li><a href=\"#Rolling_Restarts_and_Upgrades\"><span class=\"toc_number toc_depth_2\">8.2<\/span> Rolling Restarts and Upgrades<\/a><\/li><li><a href=\"#Security_Basics_Youll_Thank_Yourself_For\"><span class=\"toc_number toc_depth_2\">8.3<\/span> Security Basics You\u2019ll Thank Yourself For<\/a><\/li><\/ul><\/li><li><a href=\"#Real-World_Scenarios_and_Friendly_Patterns\"><span class=\"toc_number toc_depth_1\">9<\/span> Real-World Scenarios and Friendly Patterns<\/a><ul><li><a href=\"#Surviving_Checkout_Spikes\"><span class=\"toc_number toc_depth_2\">9.1<\/span> Surviving Checkout Spikes<\/a><\/li><li><a href=\"#WooCommerce_Stock_Freshness_Where_It_Matters\"><span class=\"toc_number toc_depth_2\">9.2<\/span> WooCommerce Stock: Freshness Where It Matters<\/a><\/li><li><a href=\"#Laravel_Jobs_and_Queues\"><span class=\"toc_number toc_depth_2\">9.3<\/span> Laravel Jobs and Queues<\/a><\/li><li><a href=\"#When_Caching_Isnt_Enough\"><span class=\"toc_number toc_depth_2\">9.4<\/span> When Caching Isn\u2019t Enough<\/a><\/li><\/ul><\/li><li><a href=\"#Common_Gotchas_And_How_I_Learned_Them\"><span class=\"toc_number toc_depth_1\">10<\/span> Common Gotchas (And How I Learned Them)<\/a><\/li><li><a href=\"#Helpful_Extras_When_Youre_Ready_for_Advanced_Moves\"><span class=\"toc_number toc_depth_1\">11<\/span> Helpful Extras When You\u2019re Ready for Advanced Moves<\/a><\/li><li><a href=\"#Wrap-Up_Calm_Fast_and_Boring_The_Good_Kind\"><span class=\"toc_number toc_depth_1\">12<\/span> Wrap-Up: Calm, Fast, and Boring (The Good Kind)<\/a><\/li><\/ul><\/div>\n<h2 id=\"section-1\"><span id=\"The_Night_a_Flash_Sale_Turned_My_Database_Into_a_Bottleneck\">The Night a Flash Sale Turned My Database Into a Bottleneck<\/span><\/h2>\n<p>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\u2019t a traffic problem; it was a <strong>MySQL connection and concurrency problem<\/strong>.<\/p>\n<p>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\u2019s when I pulled a tool out of my kit that\u2019s bailed me out more than once: <strong>ProxySQL for read\/write splitting and connection pooling<\/strong>.<\/p>\n<p>If you\u2019ve ever had a WooCommerce checkout feel sticky or a Laravel job queue start tripping over database connections, this guide is for you. We\u2019ll talk about how to put ProxySQL in front of MySQL or MariaDB, split reads and writes safely, pool connections so PHP doesn\u2019t hammer your DB directly, and keep the whole thing observable and calm. I\u2019ll share the exact approach I use, the edges to watch, and how I keep stock counts and orders consistent when things get busy.<\/p>\n<h2 id=\"section-2\"><span id=\"Why_ProxySQL_Works_So_Well_for_WooCommerce_and_Laravel\">Why ProxySQL Works So Well for WooCommerce and Laravel<\/span><\/h2>\n<p>Here\u2019s the thing: PHP apps like WordPress\/WooCommerce and Laravel are bursty. They don\u2019t hold a database connection all day long\u2014each 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\u2019s not a fan of being used like a connection vending machine.<\/p>\n<p>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, <strong>multiplexes<\/strong> short queries across them, and\u2014this is the magic\u2014routes <strong>writes to the primary<\/strong> and <strong>reads to replicas<\/strong>. That\u2019s read\/write splitting in plain terms.<\/p>\n<p>In my experience, WooCommerce and Laravel both benefit in a few big ways: first, <strong>checkout stays responsive<\/strong> because writes don\u2019t fight with catalog browsing reads. Second, <strong>you use your replicas effectively<\/strong> without changing a ton of application code. Third, <strong>connection pooling<\/strong> keeps MySQL from being overwhelmed by sudden spikes of PHP workers. It\u2019s like adding a friendly bouncer at the club door that knows where to send everyone inside.<\/p>\n<p>Now, there are nuances. Stock levels, carts, and orders in WooCommerce need <strong>fresh reads<\/strong> after a write. Laravel often bundles order creation and inventory updates in transactions. We\u2019ll handle those safely. But first, let\u2019s set the stage.<\/p>\n<h2 id=\"section-3\"><span id=\"The_Architecture_Where_ProxySQL_Sits_and_How_It_Thinks\">The Architecture: Where ProxySQL Sits and How It Thinks<\/span><\/h2>\n<p>Think of it like this: PHP-FPM talks to ProxySQL, ProxySQL talks to MySQL. If you\u2019ve 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.<\/p>\n<p>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.<\/p>\n<p>Out of the box, ProxySQL exposes two ports: the <strong>admin interface<\/strong> on 6032 and the <strong>MySQL listener<\/strong> on 6033. Your app points to 6033 like it\u2019s 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.<\/p>\n<h3><span id=\"ReadWrite_Split_in_Plain_English\">Read\/Write Split in Plain English<\/span><\/h3>\n<p>ProxySQL looks at queries and makes routing decisions. Inserts, updates, and deletes head to the primary. Plain selects go to replicas\u2014unless they\u2019re dangerous (like a SELECT \u2026 FOR UPDATE) or they\u2019re 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\u2019s the safety net you want for carts and stock counts.<\/p>\n<p>And because ProxySQL pools connections, it can reuse an existing connection to the database for multiple short app requests\u2014like lending the same phone line to different callers one after the other, instead of installing a new line for each call. PHP doesn\u2019t know or care; it\u2019s just faster and calmer under load.<\/p>\n<h2 id=\"section-4\"><span id=\"Hands-On_A_Practical_ProxySQL_Setup_That_Holds_Up_Under_Load\">Hands-On: A Practical ProxySQL Setup That Holds Up Under Load<\/span><\/h2>\n<p>I\u2019ll walk you through a setup I\u2019ve used in production for WooCommerce front-ends and Laravel back-ends. The versions change, but the pattern stays solid. We\u2019ll assume a primary on db-primary:3306 and two replicas on db-replica-1 and db-replica-2.<\/p>\n<h3><span id=\"Install_ProxySQL\">Install ProxySQL<\/span><\/h3>\n<p>On Debian\/Ubuntu-based hosts, installation is straightforward. The package creates a service and a default admin credential you should immediately change.<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\"># Ubuntu\/Debian example\napt-get update &amp;&amp; apt-get install -y proxysql mysql-client\n\n# Start and enable\nsystemctl enable --now proxysql\n\n# Connect to admin (port 6032)\nmysql -u admin -p -h 127.0.0.1 -P6032\n<\/code><\/pre>\n<p>Once inside the admin console, we\u2019ll define servers, users, and read\/write rules. If you like to learn from the source, the official docs are excellent: <a href=\"https:\/\/proxysql.com\/documentation\/\" target=\"_blank\" rel=\"noopener nofollow\">ProxySQL documentation<\/a>.<\/p>\n<h3><span id=\"Define_Primary_and_Replicas\">Define Primary and Replicas<\/span><\/h3>\n<p>We\u2019ll use hostgroups to separate the writer (10) and readers (20). Then, a replication mapping tells ProxySQL who\u2019s who and how to react to lag.<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">-- Add servers\nINSERT INTO mysql_servers(hostgroup_id, hostname, port) VALUES\n  (10, 'db-primary', 3306),\n  (20, 'db-replica-1', 3306),\n  (20, 'db-replica-2', 3306);\n\n-- Map writer\/reader groups and configure safety valves\nINSERT INTO mysql_replication_hostgroups (\n  writer_hostgroup, reader_hostgroup, check_type, comment, \n  max_writers, writer_is_also_reader, max_replication_lag\n) VALUES (\n  10, 20, 'read_only', 'rw split',\n  1, 0, 2\n);\n\n-- Monitor credentials (create these in MySQL with minimal privileges)\nSET mysql-monitor_username='monitor';\nSET mysql-monitor_password='yourStrongMonitorPass';\nLOAD MYSQL VARIABLES TO RUNTIME;\nSAVE MYSQL VARIABLES TO DISK;\n\n-- Load servers and replication mapping\nLOAD MYSQL SERVERS TO RUNTIME;\nSAVE MYSQL SERVERS TO DISK;\n<\/code><\/pre>\n<p>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.<\/p>\n<h3><span id=\"Create_an_App_User_in_ProxySQL\">Create an App User in ProxySQL<\/span><\/h3>\n<p>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.<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">-- App user that your PHP app will use when connecting to 127.0.0.1:6033\nINSERT INTO mysql_users (\n  username, password, default_hostgroup, transaction_persistent, fast_forward\n) VALUES (\n  'appuser', 'yourStrongAppPass', 10, 1, 0\n);\n\n-- Optional: restrict which client addresses may use this user\n-- UPDATE mysql_users SET default_schema='shopdb', backend='mysql' WHERE username='appuser';\n\nLOAD MYSQL USERS TO RUNTIME;\nSAVE MYSQL USERS TO DISK;\n<\/code><\/pre>\n<p>A quick bit of context: <strong>transaction_persistent=1<\/strong> tells ProxySQL to keep a transaction on the same server\/hostgroup once started. For WooCommerce and Laravel write-heavy flows, that\u2019s what you want.<\/p>\n<h3><span id=\"Write-Safe_Read-Hungry_Query_Rules\">Write-Safe, Read-Hungry Query Rules<\/span><\/h3>\n<p>Here\u2019s a conservative rule set I start with. All writes go to the writer hostgroup. Read-only selects go to the readers, except when they\u2019re hazardous or inside patterns we mark for the primary. The \u201cFOR UPDATE\u201d guard is small but mighty.<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">-- Route obvious writes to the primary\nINSERT INTO mysql_query_rules (\n  rule_id, active, match_digest, destination_hostgroup, apply\n) VALUES \n  (10, 1, '^(?i)(INSERT|UPDATE|DELETE|REPLACE|CREATE|ALTER|DROP|TRUNCATE)', 10, 1);\n\n-- SELECT ... FOR UPDATE must hit the primary\nINSERT INTO mysql_query_rules (\n  rule_id, active, match_digest, destination_hostgroup, apply\n) VALUES\n  (20, 1, '^(?i)SELECT.*FORs+UPDATE', 10, 1);\n\n-- Respect explicit app hints to force the primary\nINSERT INTO mysql_query_rules (\n  rule_id, active, match_pattern, destination_hostgroup, apply\n) VALUES\n  (30, 1, '\/*s*to:masters**\/', 10, 1);\n\n-- Route plain SELECTs to readers by default\nINSERT INTO mysql_query_rules (\n  rule_id, active, match_digest, destination_hostgroup, apply\n) VALUES\n  (40, 1, '^(?i)SELECT', 20, 1);\n\nLOAD MYSQL QUERY RULES TO RUNTIME;\nSAVE MYSQL QUERY RULES TO DISK;\n<\/code><\/pre>\n<p>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\u2019ll look at that soon.<\/p>\n<h3><span id=\"Pooling_and_Multiplexing_Let_ProxySQL_Do_Its_Thing\">Pooling and Multiplexing: Let ProxySQL Do Its Thing<\/span><\/h3>\n<p>ProxySQL\u2019s 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\u2019s 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.<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">-- Sensible defaults for connection pool behavior (tune as needed)\nSET mysql-connect_timeout_server=3000;            -- ms\nSET mysql-ping_timeout_server=1500;               -- ms\nSET mysql-query_processor_time_ms=10000;          -- ms\nSET mysql-free_connections_pct=20;                -- keep some idle, ready to serve\nSET mysql-session_idle_ms=30000;                  -- close very idle client sessions\n\nLOAD MYSQL VARIABLES TO RUNTIME;\nSAVE MYSQL VARIABLES TO DISK;\n<\/code><\/pre>\n<p>Don\u2019t worry if you don\u2019t 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.<\/p>\n<h2 id=\"section-5\"><span id=\"Point_WooCommerce_and_Laravel_at_ProxySQL_and_Keep_Your_Data_Fresh\">Point WooCommerce and Laravel at ProxySQL (and Keep Your Data Fresh)<\/span><\/h2>\n<h3><span id=\"WooCommerce_wp-configphp_Aware_Checkout-Friendly\">WooCommerce: wp-config.php Aware, Checkout-Friendly<\/span><\/h3>\n<p>For WordPress\/WooCommerce, you simply point DB_HOST to ProxySQL\u2019s listener. If ProxySQL runs locally, it\u2019s 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\u2019s mysql_users.<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">\/\/ wp-config.php\ndefine('DB_NAME',     'shopdb');\ndefine('DB_USER',     'appuser');\ndefine('DB_PASSWORD', 'yourStrongAppPass');\n\/\/ If ProxySQL runs locally on the web node\ndefine('DB_HOST',     '127.0.0.1:6033');\n<\/code><\/pre>\n<p>WordPress doesn\u2019t 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\u2014say immediately re-reading stock or the fresh order row\u2014add 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.<\/p>\n<p>If you\u2019re building on containers, I\u2019ve shared how I wire WordPress and MariaDB neatly for local or small clusters. That approach pairs nicely with ProxySQL when you\u2019re ready to scale reads: <a href=\"https:\/\/www.dchost.com\/blog\/en\/docker-compose-ile-wordpress-nginx-mariadb-redis-nasil-tatli-tatli-akiyor-kalici-hacimler-otomatik-yedek-ve-guncelleme-akisi\/\">WordPress on Docker Compose, Without the Drama: Nginx, MariaDB, Redis, Persistent Volumes, Auto\u2011Backups, and a Calm Update Flow<\/a>.<\/p>\n<h3><span id=\"Laravel_ReadWrite_Splitting_Without_Turning_Your_Code_Inside_Out\">Laravel: Read\/Write Splitting Without Turning Your Code Inside Out<\/span><\/h3>\n<p>Laravel actually has built-in support for read\/write connections, which you can keep using even with ProxySQL. But if you\u2019re 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\u2019s read\/write config works beautifully too. The docs cover it well here: <a href=\"https:\/\/laravel.com\/docs\/10.x\/database#read-and-write-connections\" target=\"_blank\" rel=\"noopener nofollow\">Laravel read and write connections<\/a>.<\/p>\n<p>To use ProxySQL as your single DB endpoint:<\/p>\n<pre class=\"language-bash line-numbers\"><code class=\"language-bash\">\/\/ config\/database.php (snippet)\n'mysql' =&gt; [\n    'driver' =&gt; 'mysql',\n    'host' =&gt; env('DB_HOST', '127.0.0.1'),\n    'port' =&gt; env('DB_PORT', '6033'), \/\/ ProxySQL listener\n    'database' =&gt; env('DB_DATABASE', 'shopdb'),\n    'username' =&gt; env('DB_USERNAME', 'appuser'),\n    'password' =&gt; env('DB_PASSWORD', 'yourStrongAppPass'),\n    'unix_socket' =&gt; env('DB_SOCKET', ''),\n    'charset' =&gt; 'utf8mb4',\n    'collation' =&gt; 'utf8mb4_unicode_ci',\n    'prefix' =&gt; '',\n    'strict' =&gt; true,\n    'engine' =&gt; null,\n],\n<\/code><\/pre>\n<p>For read-after-write stickiness, you\u2019ve 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.<\/p>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">DB::transaction(function () use ($orderData) {\n    \/\/ Writes go to primary\n    $order = Order::create($orderData);\n\n    \/\/ If you must re-read and be 100% fresh, add a tiny hint\n    $fresh = DB::select('\/* to:master *\/ SELECT * FROM orders WHERE id = ?', [$order-&gt;id]);\n\n    \/\/ ... further writes or reads that must be fresh\n});\n<\/code><\/pre>\n<p>Yes, that comment looks funny. But it\u2019s simple, and ProxySQL\u2019s rule 30 from earlier catches it and routes the query to the writer hostgroup. Don\u2019t sprinkle it everywhere\u2014use it where correctness matters immediately after a write, like stock checks.<\/p>\n<h3><span id=\"What_About_Caching\">What About Caching?<\/span><\/h3>\n<p>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\u2019ve written about that approach here: <a href=\"https:\/\/www.dchost.com\/blog\/en\/nginx-mikro-onbellekleme-ile-php-uygulamalarini-ucurmak-1-5-sn-cache-bypass-ve-purge-ne-zaman-nasil\/\">The 1\u20135 Second Miracle: How Nginx Microcaching Makes PHP Feel Instantly Faster<\/a>.<\/p>\n<h2 id=\"section-6\"><span id=\"Tuning_ProxySQL_for_Performance_and_Safety\">Tuning ProxySQL for Performance and Safety<\/span><\/h2>\n<p>Let\u2019s talk about knobs that matter. You don\u2019t have to turn all of them on day one. Start with a clean baseline, measure, and adjust while watching the stats tables (we\u2019ll get to those next).<\/p>\n<h3><span id=\"Replica_Lag_and_Sensitive_Reads\">Replica Lag and Sensitive Reads<\/span><\/h3>\n<p>Replication is amazing, but it\u2019s asynchronous by default. A burst of writes can briefly put replicas behind. That\u2019s why the <strong>max_replication_lag<\/strong> setting in <em>mysql_replication_hostgroups<\/em> 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.<\/p>\n<p>If you want to dive deeper into the underlying replication mechanics, the official MySQL docs are a good companion for a quiet evening: <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/en\/replication.html\" target=\"_blank\" rel=\"noopener nofollow\">MySQL replication overview<\/a>.<\/p>\n<h3><span id=\"Connection_Pooling_and_Multiplexing\">Connection Pooling and Multiplexing<\/span><\/h3>\n<p>ProxySQL\u2019s 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\u2019re fine.<\/p>\n<p>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.<\/p>\n<h3><span id=\"Prepared_Statements_Timeouts_and_Retries\">Prepared Statements, Timeouts, and Retries<\/span><\/h3>\n<p>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\u2019t 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.<\/p>\n<h3><span id=\"TLS_Between_ProxySQL_and_MySQL\">TLS Between ProxySQL and MySQL<\/span><\/h3>\n<p>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\u2014especially if you\u2019re handling real card data. If that\u2019s your world, I have a friendly checklist that keeps WooCommerce hosts out of trouble: <a href=\"https:\/\/www.dchost.com\/blog\/en\/pci-dss-uyumlu-woocommerce-hosting-kontrol-listesi-saq-a-mi-a%E2%80%91ep-mi-tlste-nereden-baslamaliyiz\/\">The Calm PCI\u2011DSS Checklist for WooCommerce Hosting<\/a>.<\/p>\n<h2 id=\"section-7\"><span id=\"Observability_Debugging_and_What_Just_Happened_Moments\">Observability, Debugging, and \u201cWhat Just Happened?\u201d Moments<\/span><\/h2>\n<p>When I first put ProxySQL in front of a busy store, I found myself staring at its stats like a weather radar. It\u2019s addicting\u2014and useful. The admin interface exposes a handful of tables that tell the story in real time.<\/p>\n<h3><span id=\"Stats_Youll_Actually_Use\">Stats You\u2019ll Actually Use<\/span><\/h3>\n<pre class=\"language-sql line-numbers\"><code class=\"language-sql\">-- Connect to admin again\nmysql -u admin -p -h 127.0.0.1 -P6032\n\n-- Connection pool health\nSELECT * FROM stats_mysql_connection_pool G\n\n-- Read\/write split effectiveness by hostgroup\nSELECT hostgroup, srv_host, ConnUsed, Queries FROM stats_mysql_connection_pool ORDER BY hostgroup, srv_host;\n\n-- Query digest: who\u2019s hot, who\u2019s slow\nSELECT * FROM stats_mysql_query_digest ORDER BY count_star DESC LIMIT 20;\n\n-- Which rules are actually matching\nSELECT rule_id, hits FROM stats_mysql_query_rules ORDER BY hits DESC;\n<\/code><\/pre>\n<p>If you ever wonder \u201cwhy did this SELECT go to the primary,\u201d check the query rules hits and the digest tables. They\u2019ll 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.<\/p>\n<p>For deeper request tracing across PHP, ProxySQL, and MySQL, modern tracing helps a ton. I\u2019ve shared how I stitch Laravel and Node.js into end-to-end traces you can read like a chat transcript: <a href=\"https:\/\/www.dchost.com\/blog\/en\/opentelemetry-ile-izlenebilirlik-laravel-ve-node-jste-jaeger-tempoya-uctan-uca-izler-nasil-kurulur\/\">Tracing That Feels Like a Conversation With Your App<\/a>. Add a DB span around checkout and watch it glide from app server to ProxySQL to DB. It\u2019s oddly satisfying.<\/p>\n<h3><span id=\"Practical_Tests_Before_a_Big_Day\">Practical Tests Before a Big Day<\/span><\/h3>\n<p>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\u2019s read_only flag, which is the right signal in most topologies).<\/p>\n<p>For WooCommerce, put a load test on browsing and checkout flows\u2014keep 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 \u201c\/* to:master *\/\u201d hint where your first post-checkout read happens. It\u2019s a small tweak that often saves you from overthinking consistency.<\/p>\n<h2 id=\"section-8\"><span id=\"Maintenance_Deployments_and_Staying_Calm\">Maintenance, Deployments, and Staying Calm<\/span><\/h2>\n<p>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\u2014like a built-in safety rope.<\/p>\n<h3><span id=\"Keep_Your_Config_But_Dont_Get_Bit\">Keep Your Config, But Don\u2019t Get Bit<\/span><\/h3>\n<p>ProxySQL stores its persistent config in a SQLite file. Back it up along with your infrastructure-as-code or runbook. If you\u2019re already automating your stack, fold ProxySQL into your playbooks. Whether you\u2019re 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: <a href=\"https:\/\/www.dchost.com\/blog\/en\/bulutun-ilk-nefesi-cloud%E2%80%91init-ve-ansible-ile-tekrar-uretilebilir-vps-nasil-kurulur\/\">From Blank VPS to Ready\u2011to\u2011Serve: How I Use cloud\u2011init + Ansible for Users, Security, and Services on First Boot<\/a>.<\/p>\n<h3><span id=\"Rolling_Restarts_and_Upgrades\">Rolling Restarts and Upgrades<\/span><\/h3>\n<p>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.<\/p>\n<h3><span id=\"Security_Basics_Youll_Thank_Yourself_For\">Security Basics You\u2019ll Thank Yourself For<\/span><\/h3>\n<p>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\u2019re putting ProxySQL on separate hosts, firewall it like any other sensitive component. It\u2019s a database gateway; treat it like one.<\/p>\n<h2 id=\"section-9\"><span id=\"Real-World_Scenarios_and_Friendly_Patterns\">Real-World Scenarios and Friendly Patterns<\/span><\/h2>\n<h3><span id=\"Surviving_Checkout_Spikes\">Surviving Checkout Spikes<\/span><\/h3>\n<p>On a recent campaign, we saw 10x normal browsing with a much smaller increase in checkouts. ProxySQL meant the extra reads didn\u2019t drown the primary\u2014the replicas took the brunt. The checkout path stayed smooth because writes and hazardous reads were directed to the primary. We kept a single \u201cfreshness\u201d hint on the post-checkout order confirmation read. That one line was the difference between confidence and \u201cdid the order really go through?\u201d doubts.<\/p>\n<h3><span id=\"WooCommerce_Stock_Freshness_Where_It_Matters\">WooCommerce Stock: Freshness Where It Matters<\/span><\/h3>\n<p>Stock can be touchy. If your store has low stock items and fast buyers, a stale read can cause oversells. You don\u2019t want to force every product page to the primary though\u2014that\u2019s 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.<\/p>\n<h3><span id=\"Laravel_Jobs_and_Queues\">Laravel Jobs and Queues<\/span><\/h3>\n<p>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.<\/p>\n<h3><span id=\"When_Caching_Isnt_Enough\">When Caching Isn\u2019t Enough<\/span><\/h3>\n<p>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\u2019s why I see caching and ProxySQL as complementary. Cache what you can. Use ProxySQL to make the parts you can\u2019t cache efficient and sane.<\/p>\n<h2 id=\"section-10\"><span id=\"Common_Gotchas_And_How_I_Learned_Them\">Common Gotchas (And How I Learned Them)<\/span><\/h2>\n<p>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 \u2026 FOR UPDATE started sending those reads to replicas. The locks weren\u2019t taking effect where they should, and we got a mini disaster. The fix was as simple as we discussed: match \u201cFOR UPDATE\u201d and force it to the primary. I still smile when I add that rule, remembering what it saved me from.<\/p>\n<p>Another one: long transactions from an admin report. Reports that stream rows for minutes can clog a pool if you\u2019re 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.<\/p>\n<p>Finally, don\u2019t forget that replicas must be configured correctly\u2014read_only set, authentication consistent, and replication healthy. Keep your replication status visible and alert on lag. The best ProxySQL rules can\u2019t save a broken replica.<\/p>\n<h2 id=\"section-11\"><span id=\"Helpful_Extras_When_Youre_Ready_for_Advanced_Moves\">Helpful Extras When You\u2019re Ready for Advanced Moves<\/span><\/h2>\n<p>Once you\u2019ve got the basics, consider a few niceties. Use ProxySQL\u2019s 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.<\/p>\n<p>If you pair ProxySQL with solid operational discipline\u2014backups, DR drills, clear runbooks\u2014you\u2019re in a good place. If writing a disaster recovery plan sounds heavy, I promise it doesn\u2019t have to be: practical beats perfect every time.<\/p>\n<h2 id=\"section-12\"><span id=\"Wrap-Up_Calm_Fast_and_Boring_The_Good_Kind\">Wrap-Up: Calm, Fast, and Boring (The Good Kind)<\/span><\/h2>\n<p>There\u2019s 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\u2019t 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\u2014not a small win.<\/p>\n<p>If you\u2019re starting from scratch, keep it simple: deploy ProxySQL close to your app, route writes to the primary and safe reads to replicas, add a \u201cFOR UPDATE\u201d guard and a tiny \u201c\/* to:master *\/\u201d 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.<\/p>\n<p>If you want more reading while your coffee cools, the <a href=\"https:\/\/proxysql.com\/documentation\/\" target=\"_blank\" rel=\"noopener nofollow\">ProxySQL docs<\/a> are a friendly rabbit hole. And when you\u2019re ready to double down on observability, the approach I shared in <a href=\"https:\/\/www.dchost.com\/blog\/en\/opentelemetry-ile-izlenebilirlik-laravel-ve-node-jste-jaeger-tempoya-uctan-uca-izler-nasil-kurulur\/\">tracing that feels like a conversation with your app<\/a> changes how you debug.<\/p>\n<p>Hope this was helpful! If it saves just one checkout night from chaos, it\u2019s a win in my book. See you in the next post\u2014and may your replicas stay fresh, your pools stay warm, and your checkouts stay boring.<\/p>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>\u0130&ccedil;indekiler1 The Night a Flash Sale Turned My Database Into a Bottleneck2 Why ProxySQL Works So Well for WooCommerce and Laravel3 The Architecture: Where ProxySQL Sits and How It Thinks3.1 Read\/Write Split in Plain English4 Hands-On: A Practical ProxySQL Setup That Holds Up Under Load4.1 Install ProxySQL4.2 Define Primary and Replicas4.3 Create an App User [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1702,"comment_status":"","ping_status":"","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-1701","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-teknoloji"],"_links":{"self":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts\/1701","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/comments?post=1701"}],"version-history":[{"count":0,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/posts\/1701\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/media\/1702"}],"wp:attachment":[{"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/media?parent=1701"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/categories?post=1701"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dchost.com\/blog\/en\/wp-json\/wp\/v2\/tags?post=1701"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}