Scale & Database 1 Million DAU / Peak Concurrency Principal Laravel & MySQL Architect · 6 min architectural read

High-Availability 1 Million DAU Web Architecture

Engineered and scaled high-availability Laravel and MySQL 8 web platforms supporting 1 million daily active users with sub-50ms p95 latency and zero database deadlocks under peak burst traffic.

1M+
Daily Active Users
Sustained burst concurrency
< 50ms
p95 Query Latency
Under peak traffic load
0
Production Deadlocks
Multi-million row tables
70%+
Origin Read Offload
Edge + Redis pre-aggregation
01 // Engineering Context

The Production Challenge

Massive concurrency spikes during peak times caused InnoDB row lock contention, query deadlocks, CPU saturation, and severe response degradation across multi-million row relational tables.

The Engineered Solution

Re-architected table schemas and composite indexes to eliminate filesorts and temporary tables, introduced sticky read/write Eloquent replica routing, added Redis pre-aggregated read caches, and tuned MySQL InnoDB buffer pools and connection pooling.

At 1 million daily active users, database bottlenecks rarely come from total data volume alone—they stem from concurrency amplification. When tens of thousands of users trigger the same hot read paths or update overlapping counters during peak times, even a 150ms unindexed query quickly cascades into connection pool exhaustion and InnoDB lock waits.

Rather than prematurely fragmenting the application into complex microservices, the architecture kept a clean, cohesive Laravel core while ruthlessly optimizing the data access layer: profiling real production queries via EXPLAIN ANALYZE, aligning composite index column order with selectivity, separating read replicas from transactional writes, and moving hot counters out of InnoDB row locks into Redis.

Before Refactor Bottleneck State

Unindexed Joins & Lock Contention

  • High-traffic feed & ledger queries triggered full index scans, temporary tables, and filesorts.
  • Concurrent writes on hot operational rows caused cascading InnoDB deadlocks during traffic bursts.
  • Every user request hit the single primary MySQL instance, saturating CPU at peak hours.
  • p95 API latency spiked past 1,200ms with PHP-FPM worker pool exhaustion.
After Architecture Production Verified

Composite Indexes, Replicas & Redis Hot Path

  • Cardinality-ordered composite indexes satisfied WHERE, JOIN, and ORDER BY in a single B-Tree lookup.
  • Short-lived atomic transactions and Redis lock guards eliminated hot-row lock waits and deadlocks.
  • Replica-aware Eloquent routing offloaded 85%+ of read queries onto horizontal MySQL 8 read replicas.
  • Pre-aggregated Redis read models held p95 response times consistently under 50ms at 1M DAU.
02 // Architecture & Sequence Diagrams

System Topology — Edge Offload to MySQL 8 Read/Write Split

Burst traffic passes through Cloudflare edge caching and Nginx before hitting stateless Laravel workers, Redis pre-aggregated caches, and replica-split MySQL 8.

flowchart LR
   Users["1M DAU Traffic Burst"] --> Edge["Cloudflare Edge & Bot Scrubbing"]
   Edge --> LB["Nginx Load Balancer"]
   LB --> App["Stateless Laravel / PHP-FPM Cluster"]
   App <--> Redis[("Redis Cache & Atomic Locks")]
   App -->|"Writes (Sticky)"| Primary[("MySQL 8 Primary")]
   App -->|"Reads (85%+)"| Replicas[("MySQL 8 Read Replicas")]
   Primary -->|"Async Replication"| Replicas

Request Lifecycle — Preventing Thundering Herds & Deadlocks

How hot read paths and concurrent writes execute without cache stampedes or InnoDB lock escalation.

sequenceDiagram
   participant C as Client Request
   participant L as Laravel App
   participant R as Redis Cluster
   participant P as MySQL Primary
   participant S as MySQL Replica
   C->>L: GET /api/dashboard
   L->>R: Check pre-aggregated key
   alt Cache Hit
      R-->>L: Return JSON payload (<5ms)
   else Cache Miss
      L->>R: Acquire singleflight lock (5s)
      L->>S: Execute covering-index SELECT
      S-->>L: Indexed rows (<15ms)
      L->>R: Store hydrated payload + TTL
   end
   L-->>C: HTTP 200 (<50ms p95)
   Note over L,P: Mutations acquire short Redis lock and commit minimal InnoDB transaction
03 // Implementation & Schema Snippets
database/migrations/add_composite_activity_index.sql sql
Cardinality-ordered covering index eliminating Using temporary and Using filesort on multi-million row activity queries.
-- Before: separate single-column indexes forced filesort + temp table
-- EXPLAIN ANALYZE showed ~420ms under peak concurrency

ALTER TABLE user_activities
   ADD INDEX idx_activities_tenant_status_created (
      tenant_id,
      status,
      created_at DESC,
      id
   );

-- MySQL 8 reads matching rows in pre-sorted index order with zero filesort:
SELECT id, user_id, event_type, payload, created_at
FROM user_activities
WHERE tenant_id = ?
  AND status = 'active'
ORDER BY created_at DESC, id DESC
LIMIT 25;
config/database.php + App/Services/HotReadCache.php php
Sticky read/write replica routing paired with atomic lock-protected cache hydration to stop thundering herds.
// 1. config/database.php — Sticky read/write split ensures read-your-writes consistency
'mysql' => [
   'read' => [
      'host' => [env('DB_READ_HOST_1'), env('DB_READ_HOST_2')],
   ],
   'write' => [
      'host' => [env('DB_WRITE_HOST')],
   ],
   'sticky' => true,
   // ...
],

// 2. Singleflight Redis lock prevents 1,000 concurrent cache misses hitting MySQL
public function rememberHotFeed(int $tenantId): array
{
   $cacheKey = "feed:tenant:{$tenantId}:v3";

   return Cache::remember($cacheKey, now()->addSeconds(45), function () use ($tenantId, $cacheKey): array {
      return Cache::lock("lock:{$cacheKey}", 5)->block(3, function () use ($tenantId): array {
         return Activity::query()
            ->where('tenant_id', $tenantId)
            ->where('status', 'active')
            ->latest('created_at')
            ->limit(25)
            ->get(['id', 'user_id', 'event_type', 'created_at'])
            ->toArray();
      });
   });
}
04 // Key Decisions & Tech Stack

Architectural Highlights

  • Replica-aware Eloquent routing with sticky sessions so authenticated writes read immediately from primary without stale replica lag.
  • Composite B-Tree index tuning guided by EXPLAIN ANALYZE, removing temporary tables and filesorts on multi-million row operational tables.
  • Singleflight Redis locks and pre-aggregated read caches shielding MySQL from cache stampedes during peak traffic bursts.
  • Deterministic InnoDB lock ordering and sub-millisecond transaction boundaries maintaining a zero-deadlock production record.
  • Tuned PHP-FPM pools, Nginx keepalive connections, and InnoDB buffer pool sizing matched to working-set memory.

Modular Monolith over Premature Microservices

Keeping domain logic in one well-structured Laravel 13 codebase avoided network serialization hops and distributed transaction complexity while delivering sub-50ms p95 response times.

Composite Indexes Before Hardware Upgrades

Vertical database scaling only masks bad execution plans temporarily. Reordering index columns by selectivity and equality-before-range reduced CPU utilization by over 60% before touching instance sizes.

Pre-Aggregated Redis Models for Burst Counters

Instead of running COUNT(*) or updating shared summary rows inside user request transactions, counters increment atomically in Redis and flush in batched background jobs.

Technologies: Laravel 13 PHP 8.4 MySQL 8 Redis Nginx Cloudflare High Concurrency
Available for UK & Remote Engagements

Designing or rescuing a system like this?

I work directly with engineering teams and startup founders on high-concurrency Laravel, MySQL performance engineering, payment infrastructure, and edge caching.