Database Read Replicas với Laravel: Tối Ưu Query Performance

· 12 min read
#laravel #mysql #postgresql #database #performance #replication

Giới Thiệu

Khi ứng dụng phát triển, database thường trở thành bottleneck đầu tiên. Read Replicas là giải pháp phân tải read queries sang các database copies, giúp primary database tập trung xử lý write operations.

Tại Sao Cần Read Replicas?

Without Replicas:
┌─────────────────┐
│    Primary DB   │  ← ALL reads + writes
│    (Overload)   │
└─────────────────┘

With Replicas:
┌─────────────────┐
│    Primary DB   │  ← writes only
└────────┬────────┘
         │ replication
    ┌────┴────┬────────┐
    ▼         ▼        ▼
┌───────┐ ┌───────┐ ┌───────┐
│Replica│ │Replica│ │Replica│  ← reads distributed
│   1   │ │   2   │ │   3   │
└───────┘ └───────┘ └───────┘

Lợi Ích

  1. Tăng Read Throughput: Phân tải reads sang nhiều replicas
  2. Giảm Load Primary: Primary chỉ xử lý writes
  3. High Availability: Replica có thể promote thành primary
  4. Geographic Distribution: Replicas gần users hơn
  5. Analytics Isolation: Chạy heavy reports trên replica

Cấu Hình MySQL Replication

AWS RDS Setup

# terraform/rds.tf
resource "aws_db_instance" "primary" {
  identifier           = "laravel-primary"
  engine               = "mysql"
  engine_version       = "8.0"
  instance_class       = "db.r6g.large"
  allocated_storage    = 100
  storage_type         = "gp3"
  
  db_name              = "laravel"
  username             = "admin"
  password             = var.db_password
  
  vpc_security_group_ids = [aws_security_group.db.id]
  db_subnet_group_name   = aws_db_subnet_group.main.name
  
  backup_retention_period = 7
  backup_window           = "03:00-04:00"
  maintenance_window      = "Mon:04:00-Mon:05:00"
  
  multi_az               = true
  storage_encrypted      = true
  
  parameter_group_name = aws_db_parameter_group.mysql.name
  
  tags = {
    Name = "laravel-primary"
  }
}

resource "aws_db_instance" "replica_1" {
  identifier           = "laravel-replica-1"
  replicate_source_db  = aws_db_instance.primary.identifier
  instance_class       = "db.r6g.large"
  
  vpc_security_group_ids = [aws_security_group.db.id]
  
  storage_encrypted    = true
  skip_final_snapshot  = true
  
  tags = {
    Name = "laravel-replica-1"
  }
}

resource "aws_db_instance" "replica_2" {
  identifier           = "laravel-replica-2"
  replicate_source_db  = aws_db_instance.primary.identifier
  instance_class       = "db.r6g.large"
  
  vpc_security_group_ids = [aws_security_group.db.id]
  
  storage_encrypted    = true
  skip_final_snapshot  = true
  
  tags = {
    Name = "laravel-replica-2"
  }
}

resource "aws_db_parameter_group" "mysql" {
  family = "mysql8.0"
  name   = "laravel-mysql"

  parameter {
    name  = "slow_query_log"
    value = "1"
  }
  
  parameter {
    name  = "long_query_time"
    value = "1"
  }
  
  parameter {
    name  = "innodb_buffer_pool_size"
    value = "{DBInstanceClassMemory*3/4}"
  }
}

Laravel Configuration

// config/database.php
'mysql' => [
    'read' => [
        'host' => [
            env('DB_READ_HOST_1', 'replica-1.example.com'),
            env('DB_READ_HOST_2', 'replica-2.example.com'),
        ],
    ],
    'write' => [
        'host' => env('DB_WRITE_HOST', 'primary.example.com'),
    ],
    'sticky' => true,
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'database' => env('DB_DATABASE', 'laravel'),
    'username' => env('DB_USERNAME', 'root'),
    'password' => env('DB_PASSWORD', ''),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    'options' => extension_loaded('pdo_mysql') ? array_filter([
        PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
        PDO::ATTR_PERSISTENT => false,
    ]) : [],
],

Environment Variables

# Primary (Write)
DB_WRITE_HOST=laravel-primary.xxxxx.us-east-1.rds.amazonaws.com

# Replicas (Read)
DB_READ_HOST_1=laravel-replica-1.xxxxx.us-east-1.rds.amazonaws.com
DB_READ_HOST_2=laravel-replica-2.xxxxx.us-east-1.rds.amazonaws.com

DB_DATABASE=laravel
DB_USERNAME=admin
DB_PASSWORD=your-secure-password

PostgreSQL Read Replicas

Configuration

// config/database.php
'pgsql' => [
    'read' => [
        'host' => [
            env('DB_READ_HOST_1', 'replica-1.example.com'),
            env('DB_READ_HOST_2', 'replica-2.example.com'),
        ],
        'port' => env('DB_READ_PORT', '5432'),
    ],
    'write' => [
        'host' => env('DB_WRITE_HOST', 'primary.example.com'),
        'port' => env('DB_WRITE_PORT', '5432'),
    ],
    'sticky' => true,
    'driver' => 'pgsql',
    'database' => env('DB_DATABASE', 'laravel'),
    'username' => env('DB_USERNAME', 'postgres'),
    'password' => env('DB_PASSWORD', ''),
    'charset' => 'utf8',
    'prefix' => '',
    'prefix_indexes' => true,
    'search_path' => 'public',
    'sslmode' => 'require',
],

PgBouncer for Connection Pooling

; /etc/pgbouncer/pgbouncer.ini
[databases]
; Primary for writes
laravel_write = host=primary.example.com port=5432 dbname=laravel

; Replicas for reads (load balanced)
laravel_read = host=replica-1.example.com,replica-2.example.com port=5432 dbname=laravel load_balance_hosts=round-robin

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; Connection pooling settings
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 50
min_pool_size = 10
reserve_pool_size = 25
reserve_pool_timeout = 5

; Timeouts
server_lifetime = 3600
server_idle_timeout = 600
server_connect_timeout = 15
server_login_retry = 15
query_timeout = 300

Hiểu Về Sticky Sessions

Vấn Đề Replication Lag

// Không có sticky sessions - có thể gặp vấn đề
$user = User::create(['name' => 'John']);  // Write to primary

// Ngay lập tức query replica - data có thể chưa sync
$user = User::find($user->id);  // Read from replica - might be NULL!

Sticky Sessions Giải Quyết

// config/database.php
'sticky' => true,  // Enable sticky sessions

// Với sticky = true
$user = User::create(['name' => 'John']);  // Write to primary
// Laravel automatically uses primary for subsequent reads in this request
$user = User::find($user->id);  // Read from PRIMARY (safe!)

Cách Sticky Sessions Hoạt Động

// Trong Laravel DatabaseManager
public function select($query, $bindings = [], $useReadPdo = true)
{
    // Nếu sticky enabled và đã có write trong request này
    if ($this->recordsModified && $this->getConfig('sticky')) {
        $useReadPdo = false;  // Force use write connection
    }
    
    return $this->run($query, $bindings, function ($query, $bindings) use ($useReadPdo) {
        $pdo = $useReadPdo ? $this->getReadPdo() : $this->getPdo();
        // ...
    });
}

Query Routing Strategies

1. Automatic Routing (Default)

// Laravel tự động route queries
User::all();           // → Read replica
User::create([...]);   // → Write primary
User::find(1);         // → Read replica (hoặc primary nếu sticky)

2. Force Write Connection

// Khi cần đọc data mới nhất
$user = User::onWriteConnection()->find($userId);

// Hoặc trong query builder
$users = DB::connection('mysql')
    ->useWritePdo()
    ->table('users')
    ->where('active', true)
    ->get();

3. Explicit Connection Selection

// app/Services/ReportService.php
namespace App\Services;

use Illuminate\Support\Facades\DB;

class ReportService
{
    public function generateHeavyReport(): array
    {
        // Force read từ replica cho heavy queries
        return DB::connection('mysql')
            ->useReadPdo()
            ->table('orders')
            ->join('order_items', 'orders.id', '=', 'order_items.order_id')
            ->join('products', 'order_items.product_id', '=', 'products.id')
            ->selectRaw('
                DATE(orders.created_at) as date,
                COUNT(DISTINCT orders.id) as order_count,
                SUM(order_items.quantity * order_items.price) as revenue
            ')
            ->where('orders.created_at', '>=', now()->subDays(30))
            ->groupBy('date')
            ->get()
            ->toArray();
    }
}

4. Custom Read/Write Logic

// app/Database/SmartConnection.php
namespace App\Database;

use Illuminate\Database\MySqlConnection;

class SmartConnection extends MySqlConnection
{
    protected array $writeOperations = [
        'insert', 'update', 'delete', 'truncate',
        'create', 'alter', 'drop', 'lock', 'unlock'
    ];
    
    public function select($query, $bindings = [], $useReadPdo = true)
    {
        // Force write cho specific queries
        if ($this->shouldForceWrite($query)) {
            $useReadPdo = false;
        }
        
        return parent::select($query, $bindings, $useReadPdo);
    }
    
    protected function shouldForceWrite(string $query): bool
    {
        // Force write connection cho SELECT ... FOR UPDATE
        if (str_contains(strtoupper($query), 'FOR UPDATE')) {
            return true;
        }
        
        // Force write cho queries có hint
        if (str_contains($query, '/*force_primary*/')) {
            return true;
        }
        
        return false;
    }
}

Handling Replication Lag

1. Monitoring Lag

// app/Services/ReplicationMonitor.php
namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Cache;

class ReplicationMonitor
{
    public function checkLag(): array
    {
        $results = [];
        
        // Check MySQL replication lag
        $replicas = config('database.connections.mysql.read.host');
        
        foreach ($replicas as $index => $host) {
            try {
                $lag = DB::connection('mysql_replica_' . $index)
                    ->selectOne("SHOW SLAVE STATUS");
                
                $results[$host] = [
                    'seconds_behind_master' => $lag->Seconds_Behind_Master ?? null,
                    'slave_io_running' => $lag->Slave_IO_Running ?? 'Unknown',
                    'slave_sql_running' => $lag->Slave_SQL_Running ?? 'Unknown',
                ];
            } catch (\Exception $e) {
                $results[$host] = [
                    'error' => $e->getMessage(),
                ];
            }
        }
        
        return $results;
    }
    
    public function isLagAcceptable(int $maxLagSeconds = 5): bool
    {
        $lag = $this->checkLag();
        
        foreach ($lag as $host => $status) {
            if (isset($status['seconds_behind_master']) 
                && $status['seconds_behind_master'] > $maxLagSeconds) {
                return false;
            }
        }
        
        return true;
    }
}

2. Lag-Aware Queries

// app/Traits/LagAwareQueries.php
namespace App\Traits;

use Illuminate\Support\Facades\Cache;

trait LagAwareQueries
{
    protected function queryWithLagCheck(callable $query, int $maxLagSeconds = 2)
    {
        $lag = Cache::remember('db_replication_lag', 10, function () {
            return app(ReplicationMonitor::class)->checkLag();
        });
        
        // Nếu lag quá cao, force primary
        foreach ($lag as $status) {
            if (($status['seconds_behind_master'] ?? 0) > $maxLagSeconds) {
                return $this->onWriteConnection()->tap($query);
            }
        }
        
        return $query();
    }
}

// Usage
class UserRepository
{
    use LagAwareQueries;
    
    public function findCriticalUser(int $id)
    {
        return $this->queryWithLagCheck(function () use ($id) {
            return User::find($id);
        }, maxLagSeconds: 1);
    }
}

3. Write-then-Read Pattern

// app/Services/OrderService.php
namespace App\Services;

use App\Models\Order;
use Illuminate\Support\Facades\DB;

class OrderService
{
    public function createOrder(array $data): Order
    {
        return DB::transaction(function () use ($data) {
            // Create order
            $order = Order::create($data);
            
            // Load relationships ngay trong transaction
            // Đảm bảo đọc từ primary
            $order->load(['items', 'customer', 'shipping']);
            
            return $order;
        });
    }
    
    public function updateAndFetch(int $orderId, array $data): Order
    {
        DB::transaction(function () use ($orderId, $data) {
            Order::where('id', $orderId)->update($data);
        });
        
        // Force read từ primary sau update
        return Order::onWriteConnection()
            ->with(['items', 'customer'])
            ->findOrFail($orderId);
    }
}

Failover Handling

1. Health Check

// app/Services/DatabaseHealthCheck.php
namespace App\Services;

use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;

class DatabaseHealthCheck
{
    public function checkConnections(): array
    {
        $status = [];
        
        // Check primary
        $status['primary'] = $this->checkConnection('mysql', 'write');
        
        // Check replicas
        $replicas = config('database.connections.mysql.read.host');
        foreach ($replicas as $index => $host) {
            $status["replica_{$index}"] = $this->checkConnection('mysql', 'read', $index);
        }
        
        return $status;
    }
    
    private function checkConnection(string $connection, string $type, ?int $index = null): array
    {
        try {
            $start = microtime(true);
            
            $pdo = $type === 'write' 
                ? DB::connection($connection)->getPdo()
                : DB::connection($connection)->getReadPdo();
            
            $pdo->query('SELECT 1');
            
            return [
                'status' => 'healthy',
                'latency_ms' => round((microtime(true) - $start) * 1000, 2),
            ];
        } catch (\Exception $e) {
            Log::error("Database connection failed", [
                'connection' => $connection,
                'type' => $type,
                'index' => $index,
                'error' => $e->getMessage(),
            ]);
            
            return [
                'status' => 'unhealthy',
                'error' => $e->getMessage(),
            ];
        }
    }
}

2. Automatic Failover với ProxySQL

; /etc/proxysql.cnf
datadir="/var/lib/proxysql"

admin_variables=
{
    admin_credentials="admin:admin"
    mysql_ifaces="0.0.0.0:6032"
}

mysql_variables=
{
    threads=4
    max_connections=2048
    default_query_delay=0
    default_query_timeout=36000000
    have_compress=true
    poll_timeout=2000
    interfaces="0.0.0.0:6033"
    default_schema="information_schema"
    stacksize=1048576
    server_version="8.0.35"
    connect_timeout_server=3000
    monitor_username="monitor"
    monitor_password="monitor_password"
    monitor_history=600000
    monitor_connect_interval=60000
    monitor_ping_interval=10000
    monitor_read_only_interval=1500
    monitor_read_only_timeout=500
    ping_interval_server_msec=120000
    ping_timeout_server=500
    commands_stats=true
    sessions_sort=true
    connect_retries_on_failure=10
}

mysql_servers =
(
    {
        address = "primary.example.com"
        port = 3306
        hostgroup = 0
        weight = 1000
        max_connections = 100
    },
    {
        address = "replica-1.example.com"
        port = 3306
        hostgroup = 1
        weight = 500
        max_connections = 100
    },
    {
        address = "replica-2.example.com"
        port = 3306
        hostgroup = 1
        weight = 500
        max_connections = 100
    }
)

mysql_query_rules =
(
    {
        rule_id = 1
        active = 1
        match_pattern = "^SELECT .* FOR UPDATE"
        destination_hostgroup = 0
        apply = 1
    },
    {
        rule_id = 2
        active = 1
        match_pattern = "^SELECT"
        destination_hostgroup = 1
        apply = 1
    },
    {
        rule_id = 3
        active = 1
        match_pattern = ".*"
        destination_hostgroup = 0
        apply = 1
    }
)

3. Circuit Breaker Pattern

// app/Database/CircuitBreaker.php
namespace App\Database;

use Illuminate\Support\Facades\Cache;

class CircuitBreaker
{
    private const STATE_CLOSED = 'closed';
    private const STATE_OPEN = 'open';
    private const STATE_HALF_OPEN = 'half_open';
    
    public function __construct(
        private string $service,
        private int $failureThreshold = 5,
        private int $recoveryTime = 30
    ) {}
    
    public function execute(callable $operation)
    {
        $state = $this->getState();
        
        if ($state === self::STATE_OPEN) {
            if ($this->shouldAttemptRecovery()) {
                $this->setState(self::STATE_HALF_OPEN);
            } else {
                throw new CircuitBreakerOpenException("Circuit breaker is open for {$this->service}");
            }
        }
        
        try {
            $result = $operation();
            $this->recordSuccess();
            return $result;
        } catch (\Exception $e) {
            $this->recordFailure();
            throw $e;
        }
    }
    
    private function getState(): string
    {
        return Cache::get("circuit_breaker:{$this->service}:state", self::STATE_CLOSED);
    }
    
    private function setState(string $state): void
    {
        Cache::put("circuit_breaker:{$this->service}:state", $state, 3600);
    }
    
    private function recordSuccess(): void
    {
        $state = $this->getState();
        
        if ($state === self::STATE_HALF_OPEN) {
            $this->setState(self::STATE_CLOSED);
            Cache::forget("circuit_breaker:{$this->service}:failures");
        }
    }
    
    private function recordFailure(): void
    {
        $failures = Cache::increment("circuit_breaker:{$this->service}:failures");
        
        if ($failures >= $this->failureThreshold) {
            $this->setState(self::STATE_OPEN);
            Cache::put("circuit_breaker:{$this->service}:opened_at", now()->timestamp, 3600);
        }
    }
    
    private function shouldAttemptRecovery(): bool
    {
        $openedAt = Cache::get("circuit_breaker:{$this->service}:opened_at", 0);
        return (now()->timestamp - $openedAt) >= $this->recoveryTime;
    }
}

Best Practices

1. Connection Pooling

// config/database.php
'mysql' => [
    // ... other config
    'options' => [
        PDO::ATTR_PERSISTENT => true,  // Persistent connections
        PDO::ATTR_TIMEOUT => 5,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true,
    ],
],

2. Query Hints

// app/Models/User.php
class User extends Model
{
    public function scopeForcePrimary($query)
    {
        return $query->useWritePdo();
    }
    
    public function scopeForceReplica($query)
    {
        return $query->useReadPdo();
    }
}

// Usage
$user = User::forcePrimary()->find($id);
$users = User::forceReplica()->active()->get();

3. Monitoring Queries

// app/Providers/AppServiceProvider.php
public function boot(): void
{
    DB::listen(function ($query) {
        $connection = $query->connectionName;
        $time = $query->time;
        
        // Log slow queries
        if ($time > 1000) {
            Log::warning('Slow query detected', [
                'sql' => $query->sql,
                'bindings' => $query->bindings,
                'time' => $time,
                'connection' => $connection,
            ]);
        }
        
        // Track metrics
        if (app()->bound('prometheus')) {
            app('prometheus')->histogram(
                'database_query_duration_seconds',
                $time / 1000,
                ['connection' => $connection]
            );
        }
    });
}

Kết Luận

Read Replicas giúp:

  1. Scale read capacity mà không ảnh hưởng writes
  2. Improve performance bằng cách phân tải queries
  3. Increase availability với failover options
  4. Isolate workloads (analytics vs production)

Checklist Implementation

  • Cấu hình read/write connections
  • Enable sticky sessions
  • Setup monitoring replication lag
  • Implement health checks
  • Configure connection pooling
  • Test failover scenarios
  • Setup alerting for lag > threshold

Tài Liệu Tham Khảo

Bình luận