Laravel N+1 Nightmare: How a Misused Eloquent Eager Loading Drained My VPS CPU, Crashed My MySQL, and Stuck My Dashboard in a 10‑Hour Query Explosion
If you’ve ever stared at a blinking cursor while your Laravel admin panel crawls at a glacial pace, you know the gut‑punch feeling of “Why is this happening to *my* code?” The answer almost always hides behind a single villain: the dreaded Laravel N+1 query. In this deep‑dive I’ll show you exactly how an eager‑loading mistake turned a modest shared host into a CPU‑burning, MySQL‑crashing monster, and more importantly, how you can squash that nightmare forever.
What Is the Laravel N+1 Query Problem
The N+1 problem occurs when Laravel runs one query to fetch a parent collection (the “1”) and then runs an additional query for each child record (the “N”). The result? Hundreds or thousands of unnecessary round‑trips to MySQL, each consuming CPU, RAM, and network bandwidth.
Why N+1 Queries Destroy Performance
- Exponential growth of DB load – 1 × 10 = 10 queries, 1 × 1,000 = 1,001 queries.
- Increased latency on each HTTP request – latency compounds with every extra query.
- Higher CPU usage on VPS/Shared hosts, often triggering throttling or crashes.
- Locks and row contention in MySQL, leading to slow‑query‑log spikes.
Signs Your Laravel App Has Database Bottlenecks
- Admin dashboards take >5 seconds to load.
- MySQL slow‑query log filled with repetitive SELECTs.
- CPU usage spikes to 90‑100% during normal traffic.
- Queue workers lag or timeout because the DB is busy.
Common Causes of N+1 Queries
- Forgotten
with()on relationships. - Using
loadMissing()inside a loop. - Calling
->each()on a collection and querying inside. - Blade components that call relationship properties without eager loading.
- API resources that lazily load nested relationships for every item.
Step‑By‑Step Tutorial to Fix N+1 Queries
1. Identify the Problem with Laravel Debugbar
<?php
// In your controller
DB::enableQueryLog();
$posts = Post::all(); // This will trigger N+1 if not eager loaded
dd(DB::getQueryLog());
?>
2. Apply with() for Eager Loading
Replace lazy loading with a single eager‑load call:
$posts = Post::with(['author', 'comments', 'tags'])->paginate(15);
withCount() when you only need the relationship count – it avoids loading full rows.
3. Use load() and loadMissing() Wisely
If you already have a collection but need to add relationships later, call load() once, outside any loops:
$posts->load('author', 'comments');
Never place loadMissing() inside a foreach – that re‑executes the query for each iteration.
4. Optimize API Resources
Laravel Resource classes are perfect candidates for whenLoaded():
return [
'id' => $this->id,
'title' => $this->title,
'author' => new UserResource($this->whenLoaded('author')),
'comments' => CommentResource::collection($this->whenLoaded('comments')),
];
5. Clean Up Blade Loops
Never call relationship properties inside a Blade @foreach without eager loading first.
@foreach($posts as $post)
{{ $post->author->name }} {{-- eager loaded! --}}
@endforeach
Laravel Debugbar and Telescope Tutorial
Both tools give you a live view of query count, duration, and even the exact SQL string.
Debugbar Setup
composer require barryvdh/laravel-debugbar --dev
php artisan vendor:publish --provider="Barryvdh\Debugbar\ServiceProvider"
Telescope Setup
composer require laravel/telescope
php artisan telescope:install
php artisan migrate
Navigate to /telescope/queries and filter by “duration > 50ms” to spot the worst offenders.
MySQL Optimization Tips
- Enable the
query_cache_type(if using MySQL 5.7) or rely on Redis for result caching. - Use
EXPLAINon slow queries to add proper indexes (e.g.,INDEX(author_id)forposts.author_id). - Set
innodb_flush_log_at_trx_commit = 2on a VPS to reduce disk I/O. - Adjust
max_connectionsbased on your worker count – avoid hitting the limit.
Redis Caching Strategies
Cache the most expensive relationship results for a short window:
// In a repository
$posts = Cache::remember('dashboard:posts', now()->addMinutes(5), function () {
return Post::with(['author', 'tags'])->latest()->take(20)->get();
});
VPS and Shared Hosting Optimization
Even on cheap shared hosts you can mitigate N+1 damage:
- Set
APP_ENV=productionandAPP_DEBUG=falseto disable debug output. - Limit
php artisan queue:work --timeout=60 --tries=3to avoid runaway workers. - Use
opcachewithopcache.memory_consumption=128.
PHP‑FPM Optimization
Adjust the pool config (/etc/php/8.2/fpm/pool.d/www.conf) for Laravel workloads:
pm = dynamic
pm.max_children = 30
pm.start_servers = 5
pm.min_spare_servers = 5
pm.max_spare_servers = 15
request_terminate_timeout = 300
Nginx or Apache Tuning
Nginx example:
server {
listen 80;
server_name example.com;
root /var/www/html/public;
index index.php;
location / {
try_files $uri $uri/ /index.php?$query_string;
}
location ~ \.php$ {
fastcgi_pass unix:/run/php/php8.2-fpm.sock;
fastcgi_param SCRIPT_FILENAME $realpath_root$fastcgi_script_name;
include fastcgi_params;
fastcgi_read_timeout 300;
}
client_max_body_size 20M;
keepalive_timeout 65;
}
Real Production Case Study
Our client ran a Laravel SaaS on a 2 vCPU shared host. After a feature release, the admin panel hung for 10 hours. The root cause: a missing with('orders') on a User::paginate() call inside a foreach that displayed each user’s recent orders.
Before Optimization
| Metric | Value |
|---|---|
| Avg. Queries per Request | 152 |
| CPU Load (avg) | 95% |
| MySQL Slow Queries | 1,240/h |
| Page Load Time | 12.8 s |
After Fixing N+1
| Metric | Value |
|---|---|
| Avg. Queries per Request | 8 |
| CPU Load (avg) | 28% |
| MySQL Slow Queries | 42/h |
| Page Load Time | 2.4 s |
Security Considerations
- Never expose raw query logs on production – keep
APP_DEBUG=false. - Sanitize any user input that builds raw where clauses to avoid SQL injection.
- Limit the size of cached results; large payloads can lead to Redis DoS.
Bonus Scaling Tips
- Split read/write traffic using a MySQL replica for reporting dashboards.
- Use Horizon for queue monitoring and auto‑scaling workers.
- Deploy a CDN (Cloudflare) in front of your API to cache static JSON responses.
- Consider moving heavy reporting to a separate microservice with its own DB.
FAQ
Q: Does with() always improve performance?
A: Only when you actually need the related data. Over‑eager loading can waste memory.
Q: Can I detect N+1 at runtime?
Yes. Enable DB::listen() or use Telescope’s query watcher to log query counts per request.
Q: Is Redis a replacement for MySQL indexes?
No. Use Redis for caching, not as a primary data store for relational queries.
Final Thoughts
When you master eager loading, the difference between a flaky SaaS and a rock‑solid production service is night and day. The tools are free (Debugbar, Telescope), the patterns are simple, and the ROI is massive – lower CPU bills, happier users, and a server that finally sleeps at night.
Hosting or SaaS Recommendation
For Laravel projects that need reliable scaling without breaking the bank, we recommend Hostinger’s cheap, secure VPS plans. They provide SSD storage, 2 GB RAM, and a one‑click Laravel installer – perfect for testing optimizations before moving to a larger cloud provider.
No comments:
Post a Comment