Database Scaling

Replication Topology

┌──────────────┐     ┌──────────────┐
│  Primary DB  │────▶│  Replica DB 1│ (Read)
│  (Write)     │────▶│  Replica DB 2│ (Read)
└──────────────┘     └──────────────┘
       │
       â–¼
  Application
  (Writes → Primary)
  (Reads → Replicas)

Magento Database Config

// app/etc/env.php
'db' => [
    'connection' => [
        'default' => [
            'host' => 'primary-db.example.com',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1'
        ],
        'indexer' => [
            'host' => 'replica-db.example.com',
            'dbname' => 'magento',
            'username' => 'magento',
            'password' => '***',
            'active' => '1'
        ]
    ]
],

Replication Lag Monitoring

-- Check replication status on replica
SHOW SLAVE STATUS\G

-- Key metrics:
-- Seconds_Behind_Master: replication delay
-- Slave_IO_Running: IO thread status
-- Slave_SQL_Running: SQL thread status

-- Alert if lag > 10 seconds
SELECT 
    IFNULL(SECONDS_BEHIND_MASTER, 999999) as lag
FROM information_schema.PROCESSLIST
WHERE USER = 'system user';

Magento Resource Connections

// app/etc/env.php - Multiple read replicas
'db' => [
    'connection' => [
        'default' => [
            'host' => 'primary-db.example.com',
            'dbname' => 'magento',
            'username' => 'magento_rw',
            'password' => '***',
            'active' => '1'
        ],
        'replica1' => [
            'host' => 'replica1-db.example.com',
            'dbname' => 'magento',
            'username' => 'magento_ro',
            'password' => '***',
            'active' => '1'
        ],
        'replica2' => [
            'host' => 'replica2-db.example.com',
            'dbname' => 'magento',
            'username' => 'magento_ro',
            'password' => '***',
            'active' => '1'
        ]
    ]
],

Connection Distribution

Read Queries (80%):  → Replica 1, Replica 2
Write Queries (20%): → Primary Only

Magento automatically routes:
- Read operations → replica connections
- Write operations → default (primary) connection

Why Connection Pooling?

Without Pooling:              With Pooling:
Request → Open → Use → Close  Request → Pool → Use → Return
Request → Open → Use → Close  Request → Pool → Use → Return

Overhead: ~10ms per conn      Overhead: ~1ms per conn

ProxySQL Connection Pooling

-- ProxySQL config for connection pooling
INSERT INTO mysql_servers (
    hostgroup_id, hostname, port, weight
) VALUES
    (10, 'primary-db.example.com', 3306, 1000),
    (20, 'replica1-db.example.com', 3306, 500),
    (20, 'replica2-db.example.com', 3306, 500);

-- Route writes to primary
INSERT INTO mysql_query_rules (
    rule_id, active, match_pattern, destination_hostgroup
) VALUES
    (1, 1, '^SELECT.*FOR UPDATE$', 10),
    (2, 1, '^SELECT.*', 20);

PHP Connection Pooling

// Increase PHP-FPM connections
// /etc/php/8.1/fpm/pool.d/www.conf
pm.max_children = 50
pm.start_servers = 10
pm.min_spare_servers = 5
pm.max_spare_servers = 20
pm.max_requests = 500

// MySQL max connections
// my.cnf
[mysqld]
max_connections = 500
wait_timeout = 600
interactive_timeout = 600

Write Scaling Approaches

1. Vertical: Bigger primary server
2. Sharding: Split data across servers
3. Queue Writes: Batch write operations
4. Async Writes: Non-critical writes via queue

Write Queue for Non-Critical Operations

// Async order processing
$queue = $objectManager->get(
    \Magento\Framework\MessageQueue\PublisherInterface::class
);

// Non-critical writes go to queue
$queue->publish('order.process', $orderData);
// Database write happens asynchronously

// Critical writes go directly to DB
$resource->save($order);

Batch Write Optimization

-- Instead of individual inserts
INSERT INTO catalog_product_index (entity_id, name) VALUES (1, 'A');
INSERT INTO catalog_product_index (entity_id, name) VALUES (2, 'B');

-- Use bulk insert
INSERT INTO catalog_product_index (entity_id, name) VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C');
-- 3x fewer round trips

Monitoring Write Load

-- Check write throughput
SHOW GLOBAL STATUS LIKE 'Innodb_data_writes';

-- Check write latency
SHOW GLOBAL STATUS LIKE 'Innodb_os_log_written';

-- Monitor replication lag during writes
SHOW SLAVE STATUS\G | grep Seconds_Behind