DEV Community

Magevanta
Magevanta

Posted on • Originally published at magevanta.com

Magento 2 MySQL Read/Write Replication Splitting: Scale Your Database Horizontally

#magento2 #performance #mysql #database

When your Magento 2 store outgrows a single database server, the first wall you hit isn't CPU or memory — it's connection contention. MySQL can only handle so many concurrent connections before query latency spikes, transactions queue up, and your store grinds to a crawl.

The standard production fix is read/write replication splitting: offload SELECT queries to replica databases while writes go to the primary. Magento 2 has built-in support for this, but most developers don't configure it correctly — or at all.

In this guide, you'll learn exactly how to set up MySQL replication for Magento 2, configure the read/write connection split, handle replication lag, and monitor everything in production.

Why Replication Splitting Matters for Magento 2

Magento 2 is a database-heavy application. Every page load does dozens — sometimes hundreds — of SELECT queries:

  • Frontend catalog browsing: category pages, product pages, layered navigation — all read-heavy
  • Checkout: reads customer data, quotes, shipping rates, payment info — mix of reads and writes
  • Admin operations: grid loads, report generation, order management — predominantly reads
  • Indexers: massive batch reads and writes during reindex
  • Cron jobs: log cleanup, cache warming, analytics aggregation — mixed workload

On a single database server, all these reads compete with writes for connections, locks, and I/O. The result: read queries block writes, and writes block reads.

With read/write splitting:

  • SELECT queries go to replicas with relaxed transaction isolation
  • INSERT/UPDATE/DELETE go to the primary
  • Read replicas scale horizontally — add more as traffic grows
  • Primary stays fast because it's not serving read traffic

How Magento 2's Read/Write Connection System Works

Magento 2 uses Zend_Db under the hood (in Magento\Framework\DB\Adapter\AdapterInterface). The connection configuration lives in app/etc/env.php under the db key.

Here's the basic out-of-the-box config:

// app/etc/env.php — single server
'db' => [
    'connection' => [
        'indexer' => [
            'host' => 'localhost',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
        ],
        'default' => [
            'host' => 'localhost',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
        ],
    ],
],
Enter fullscreen mode Exit fullscreen mode

Magento has two connection pools by default:

  • default — used by most application code
  • indexer — used exclusively by indexers (can use a separate server)

The Actual ResourceConnection Resolution

Magento's Magento\Framework\App\ResourceConnection uses a simple pattern: when you request connection default, it checks if there's a default_read connection configured. If yes, SELECT queries use that; if not, everything goes to default.

This means your env.php should look like:

'db' => [
    'connection' => [
        'indexer' => [
            'host' => 'db-primary',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
        ],
        'default' => [
            'host' => 'db-primary',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
        ],
        'default_read' => [
            'host' => 'db-replica-1',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
        ],
    ],
],
Enter fullscreen mode Exit fullscreen mode

With this config, Magento automatically sends SELECT queries through default_read (the replica) while writes go through default (the primary).

Step 1: Set Up MySQL Replication

Before Magento can use a read replica, you need MySQL replication running. Here's the minimal production setup.

On the Primary Server 

# /etc/mysql/mariadb.conf.d/replication.cnf
[mysqld]
server_id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G
binlog_do_db = magento
Enter fullscreen mode Exit fullscreen mode

Create a replication user:

CREATE USER 'replicator'@'%' IDENTIFIED BY 'strong-password';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'%';
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode

On the Replica Server 

# /etc/mysql/mariadb.conf.d/replication.cnf
[mysqld]
server_id = 2
log_bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
relay_log = /var/log/mysql/mysql-relay-bin.log
read_only = 1
Enter fullscreen mode Exit fullscreen mode

The read_only = 1 is critical — it prevents any direct writes to the replica.

Initialise replication:

CHANGE MASTER TO
  MASTER_HOST='db-primary',
  MASTER_USER='replicator',
  MASTER_PASSWORD='strong-password',
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=0;
START SLAVE;
SHOW SLAVE STATUS\G
Enter fullscreen mode Exit fullscreen mode

Sync the Existing Data

If you already have a live Magento database, use mysqldump to seed the replica:

mysqldump --single-transaction --quick --routines --triggers \
  --host=db-primary magento > magento_dump.sql

mysql --host=db-replica-1 magento < magento_dump.sql
Enter fullscreen mode Exit fullscreen mode

The --single-transaction flag gives you a consistent snapshot without locking all tables — crucial for a production primary.

Step 2: Configure Magento for Read/Write Splitting

Once replication is running, update app/etc/env.php:

// app/etc/env.php
'db' => [
    'table_prefix' => '',
    'connection' => [
        'default' => [
            'host' => 'db-primary',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
            'driver_options' => [
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8;',
            ],
        ],
        'default_read' => [
            'host' => 'db-replica-1',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
            'model' => 'mysql4',
            'engine' => 'innodb',
            'initStatements' => 'SET NAMES utf8;',
            'driver_options' => [
                PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8;',
            ],
        ],
        'indexer' => [
            'host' => 'db-primary',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1',
        ],
    ],
    'slave_connection' => [
        'default_read',
    ],
],
Enter fullscreen mode Exit fullscreen mode

Multiple Read Replicas

Need more read capacity? Magento 2 supports multiple read connections. Add them as separate entries:

'default_read' => [
    'host' => 'db-replica-1',
],
'default_read_2' => [
    'host' => 'db-replica-2',
],
'slave_connection' => [
    'default_read',
    'default_read_2',
],
Enter fullscreen mode Exit fullscreen mode

Magento's ResourceConnection picks one randomly from the available read connections — simple round-robin load balancing.

Verify It's Working

Run this from the Magento root:

bin/magento setup:db:status
Enter fullscreen mode Exit fullscreen mode

Then check which server queries hit by inspecting the general log on your replicas, or by adding a temporary connection log:

$resource = $objectManager->get(\Magento\Framework\App\ResourceConnection::class);

$readConnection = $resource->getConnection('default_read');
echo 'Read server: ' . $readConnection->fetchOne('SELECT @@hostname;');

$writeConnection = $resource->getConnection('default');
echo 'Write server: ' . $writeConnection->fetchOne('SELECT @@hostname;');
Enter fullscreen mode Exit fullscreen mode

Step 3: Handle Replication Lag

Replication lag is the #1 challenge with read/write splitting. If a customer places an order and the next page load reads from a lagging replica, their order won't appear — which is a terrible experience.

The After-Write Read Pattern

In checkout and customer account areas, force reads to the primary when consistency matters:

public function aroundGetCheckoutData(
    \Magento\Checkout\Model\Session $subject,
    callable $proceed
) {
    if ($this->session->getLastOrderId()) {
        $this->resourceConnection->setUseWriteConnection(true);
    }
    return $proceed();
}
Enter fullscreen mode Exit fullscreen mode

Set Max Acceptable Lag

Use MySQL's Seconds_Behind_Master metric. Build a health-check script that swaps connections when lag exceeds a threshold:

#!/bin/bash
LAG=$(mysql -h db-replica-1 -e "SHOW SLAVE STATUS\G" | \
  grep "Seconds_Behind_Master" | awk '{print $2}')
if [ "$LAG" -gt 30 ]; then
    php bin/magento config:set system/db/read_host db-primary
fi
Enter fullscreen mode Exit fullscreen mode

Run this as a cron job every minute for best results.

Step 4: ProxySQL — The Production-Grade Alternative

Configuring read/write splitting in env.php works, but it has limitations:

  • No connection pooling — each PHP-FPM worker opens its own connection
  • No lag-aware routing — Magento doesn't know if a replica is behind
  • No query-level routing — all SELECTs go to replicas, even transactional ones

ProxySQL solves all of these. It sits between Magento and MySQL as a transparent proxy:

Magento → ProxySQL (6033) → Primary
                         → Replica 1
                         → Replica 2
Enter fullscreen mode Exit fullscreen mode

Install ProxySQL 

apt install proxysql
systemctl enable proxysql
systemctl start proxysql
Enter fullscreen mode Exit fullscreen mode

Configure Query Rules 

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES
  (0, 'db-primary', 3306),    -- writes
  (1, 'db-replica-1', 3306),  -- reads
  (1, 'db-replica-2', 3306);  -- reads

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES
  ('magento', '***', 0);

INSERT INTO mysql_query_rules
  (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES
  (1, 1, '^SELECT .*', 1, 1),
  (2, 1, '^INSERT.*', 0, 1),
  (3, 1, '^UPDATE.*', 0, 1),
  (4, 1, '^DELETE.*', 0, 1);

LOAD MYSQL SERVERS TO RUNTIME;
LOAD MYSQL USERS TO RUNTIME;
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL USERS TO DISK;
SAVE MYSQL QUERY RULES TO DISK;
Enter fullscreen mode Exit fullscreen mode

Then update env.php to point at ProxySQL:

'default' => [
    'host' => '127.0.0.1',
    'port' => '6033',
],
Enter fullscreen mode Exit fullscreen mode

ProxySQL handles connection pooling (reusing connections between PHP-FPM workers) and query-level routing, which is far more granular than connection-level splitting.

What NOT to Route to Replicas

These operations must always hit the primary:

Operation Reason
Checkout quote reads Must see the latest quote state
Customer session reads Must reflect recent login
Admin ACL checks Must see permission changes immediately
Cache tag invalidation Race conditions
Queries inside transactions Transaction scope consistency

Magento's ResourceConnection automatically routes queries inside a transaction to the write connection. For explicit control in checkout or admin code:

$resource->getConnection('checkout')->query('SELECT ...');
Enter fullscreen mode Exit fullscreen mode

Monitoring Replication Health

You need visibility into three things:

1. Replication Lag 

SHOW SLAVE STATUS\G
Enter fullscreen mode Exit fullscreen mode

Set alerts at 10 seconds (warning) and 30 seconds (critical).

2. Read/Write Ratio 

SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE 'Com_update';
SHOW GLOBAL STATUS LIKE 'Com_delete';
Enter fullscreen mode Exit fullscreen mode

On a typical Magento store, you'll see 80-90% reads, 10-20% writes. If your read ratio is below 70%, you might not benefit much from read replicas.

3. Connection Distribution (ProxySQL) 

SELECT * FROM stats_mysql_connection_pool;
Enter fullscreen mode Exit fullscreen mode

Performance Impact: What to Expect

Metric Single DB Read Replica ProxySQL + 2 Replicas
Catalog page load (p50) 420ms 310ms 260ms
Checkout submit (p95) 1.8s 1.6s 1.4s
Max concurrent requests 200 450 800
Primary CPU load (peaks) 85% 45% 30%

The biggest win isn't latency — it's capacity. Offloading reads frees the primary to handle writes faster, and your store can handle 2-4x more traffic before hitting a bottleneck.

Common Pitfalls

❌ Using the Same Server for Read and Write

If both default and default_read point to the same host, you're adding overhead with zero benefit. Use separate servers.

❌ Skipping read_only on Replicas

Set read_only = 1 in MySQL config on all replicas. If Magento accidentally sends a write, read_only prevents data corruption.

❌ Not Warming the Replica Buffer Pool

A fresh replica needs to warm its buffer pool. Run full scans on your largest tables:

for table in catalog_product_entity catalog_category_product \
             sales_order sales_order_item quote quote_item; do
  mysql -h db-replica-1 magento -e "SELECT COUNT(*) FROM $table;"
done
Enter fullscreen mode Exit fullscreen mode

Without this, the first 24 hours of queries will be slow.

❌ Ignoring Long-Running Queries

Replicas share the same schema as the primary. If a slow query causes issues on the primary, it will also cause issues on the replica. Profile both servers with pt-query-digest.

When to NOT Use Read/Write Splitting

Read/write splitting adds operational complexity. Skip it if:

  • Your store handles fewer than 50,000 monthly visitors
  • Your MySQL server runs below 60% CPU during peak
  • Average page loads are under 400ms on a single server
  • You don't have DevOps support for the infrastructure

For smaller stores, invest in caching (Redis, Varnish) and query optimization first — bigger wins, less operational overhead.

Recap

Magento 2's built-in read/write connection support makes horizontal database scaling accessible:

  1. Set up MySQL replication with binlog_format = ROW
  2. Add default_read connection in env.php
  3. Handle replication lag with after-write reads and lag monitoring
  4. For production scale, use ProxySQL for query-level split and connection pooling
  5. Monitor Seconds_Behind_Master and connection distribution

The ceiling on Magento's database performance isn't the application — it's the architecture. Read/write replication splitting removes the single-server bottleneck and gives your store room to grow through peak traffic, flash sales, and catalog expansion without a full replatform.

Top comments (0)