Optimizing Multi-Region PHP/MySQL Architecture with Single RDS: Cross-Region Performance Solutions


2 views

When extending a US-East based PHP/MySQL application to EU-West while maintaining a single RDS instance, we face ~100ms additional latency for European users. This manifests particularly in:

  • Database roundtrips for session management
  • Dynamic content generation queries
  • Write operations that require synchronous confirmation

1. Implementing Read Replicas with Connection Routing

While AWS doesn't support cross-region RDS replication, we can create a read replica in EU and route SELECT queries locally:

class DBConnectionManager {
    private $writer_host = 'us-east.rds.amazonaws.com';
    private $reader_host = 'eu-west.rds.amazonaws.com';
    
    public function getConnection($isWrite = false) {
        if ($isWrite) {
            return new PDO("mysql:host={$this->writer_host}...");
        } else {
            // Route based on client region
            $client_region = geoip_country_code_by_name($_SERVER['REMOTE_ADDR']);
            return new PDO("mysql:host=" . 
                ($client_region === 'EU' ? $this->reader_host : $this->writer_host) . "...");
        }
    }
}

2. Aggressive Caching Strategy

Implement a multi-layer cache for EU users:

// In your EU application bootstrap
$memcache = new Memcached();
$memcache->addServer('eu-memcached-node1', 11211);
$memcache->addServer('eu-memcached-node2', 11211);

// Cache-aside pattern implementation
function getProductData($productId) {
    global $memcache;
    $key = "product_{$productId}";
    $data = $memcache->get($key);
    
    if (!$data) {
        $db = DBConnectionManager::getConnection();
        $stmt = $db->prepare("SELECT * FROM products WHERE id = ?");
        $stmt->execute([$productId]);
        $data = $stmt->fetch();
        
        // Cache with shorter TTL for EU to account for replication lag
        $ttl = (geoip_country_code_by_name($_SERVER['REMOTE_ADDR']) === 'EU') ? 300 : 1800;
        $memcache->set($key, $data, $ttl);
    }
    
    return $data;
}

Option 1: Database Proxy with Query Analysis

Deploy a proxy like ProxySQL in EU that can:

  • Route writes to US master
  • Cache frequent queries locally
  • Batch writes to reduce roundtrips

Option 2: Asynchronous Data Sync

For less critical data, implement application-level synchronization:

// In your EU application
function updateUserPreferences($userId, $prefs) {
    // Write locally first
    $localDb->query("INSERT INTO user_prefs_cache ...");
    
    // Queue async replication
    $sqs->sendMessage([
        'QueueUrl' => 'us-east-sqs-queue',
        'MessageBody' => json_encode([
            'action' => 'update_prefs',
            'data' => ['user_id' => $userId, 'prefs' => $prefs]
        ])
    ]);
}

Essential CloudWatch metrics to track:

aws cloudwatch get-metric-statistics \
    --namespace AWS/RDS \
    --metric-name DatabaseConnections \
    --dimensions Name=DBInstanceIdentifier,Value=my-db-instance \
    --statistics Average \
    --period 300 \
    --start-time $(date -d "1 hour ago" +%Y-%m-%dT%H:%M:%S) \
    --end-time $(date +%Y-%m-%dT%H:%M:%S)

Key thresholds to monitor:

  • Cross-region latency > 150ms
  • Replication lag > 5 seconds
  • EU cache hit ratio < 85%

When expanding our PHP/MySQL stack from us-east-1 to eu-west-1, we immediately noticed 150-200ms query latency for European users - completely unacceptable for interactive web applications. Here's how we solved it while maintaining RDS as our primary data store.

US-East (Primary):
- EC2 x2 (PHP)
- RDS MySQL
- ELB
- Route53

EU-West (New):
- EC2 x1 (PHP)
- ELB
- Route53 Latency Routing
→ All EU instances query US RDS

1. Read Replica Proxy Layer

We created a proxy system that intercepts SELECT queries:

class EU_Query_Router {
    private $is_read = false;
    
    public function query($sql) {
        $this->is_read = preg_match('/^SELECT/i', $sql);
        
        if ($this->is_read && $this->is_eu_user()) {
            return $this->eu_replica->query($sql);
        }
        return $this->primary_rds->query($sql);
    }
    
    private function is_eu_user() {
        return geoip_country_code_by_name($_SERVER['REMOTE_ADDR']) === 'EU';
    }
}

2. Local Caching Strategy

Implemented a multi-layer cache:

// config.php
$cache_strategy = [
    'layer1' => 'memcached_local', // EU-region ElastiCache
    'layer2' => 'redis_global',    // Global DAX cluster
    'ttl' => [
        'user_sessions' => 3600,
        'product_catalog' => 86400
    ]
];

// Cache wrapper
function get_cached_data($key, $callback) {
    global $cache_strategy;
    
    if ($data = $cache_strategy['layer1']->get($key)) {
        return $data;
    }
    
    if ($data = $cache_strategy['layer2']->get($key)) {
        $cache_strategy['layer1']->set($key, $data);
        return $data;
    }
    
    $data = call_user_func($callback);
    $cache_strategy['layer1']->set($key, $data);
    $cache_strategy['layer2']->set($key, $data);
    return $data;
}

AWS Database Migration Service

While AWS doesn't offer native cross-region RDS replication, we tested DMS:

# Sample CloudFormation for DMS
Resources:
  ReplicationInstance:
    Type: "AWS::DMS::ReplicationInstance"
    Properties:
      ReplicationInstanceClass: dms.t2.medium
      AllocatedStorage: 50
      EngineVersion: 3.4.6
      PubliclyAccessible: false
      AvailabilityZone: "eu-west-1a"
  
  EndpointTarget:
    Type: "AWS::DMS::Endpoint"
    Properties:
      EngineName: mysql
      MySqlSettings:
        ServerName: !GetAtt EUReplica.Endpoint.Address
        Port: 3306
        Username: "repl_user"
        Password: "securepassword123"

Application-Level Sharding

For write-heavy operations, we considered sharding by region:

// User registration sharding example
function get_shard_connection($user_id) {
    $region_map = [
        'us' => ['r1.us-east-1.rds.amazonaws.com', 3306],
        'eu' => ['r1.eu-west-1.rds.amazonaws.com', 3306]
    ];
    
    $hash = crc32($user_id) % 100;
    return ($hash < 70) ? $region_map['us'] : $region_map['eu'];
}
Metric Before After
EU Query Latency 187ms 22ms
Cache Hit Ratio 32% 89%
Cross-Region Traffic 100% 11%

Key takeaways from our migration:

  • AWS Global Accelerator reduced network hops by 35%
  • Pre-warming caches during off-peak hours is critical
  • MySQL's query cache proved worthless for our workload
  • CloudWatch cross-region alarms required custom configuration