Laravel N+1 Nightmare: 10,000 Queries, 100% CPU, and a Slow Dashboard on a Shared VPS – How I Debugged, Fixed Eager Loading, and Nested MySQL Optimization in a Real‑World Production App

If you’ve ever stared at a Laravel admin panel that crawls like a snail while your CPU spikes to 100 %, you know the gut‑punch feeling of a hidden N+1 query monster. In this hands‑on walkthrough I’ll take you through the exact steps I used to track down 10 000+ queries, slash CPU usage, and bring a faltering dashboard back to life – all on a budget‑friendly shared VPS.

What Is the Laravel N+1 Query Problem

The N+1 problem occurs when a parent model loads a collection of children lazily inside a loop, causing an extra query for each child record. In Laravel this usually looks like:

<?php
$posts = App\Models\Post::all(); // 1 query
foreach ($posts as $post) {
    echo $post->author->name; // N additional queries
}
?>

Why N+1 Queries Destroy Performance

• Each extra query adds network latency and CPU overhead.
• On a shared VPS the database connection pool is limited – 10 000 queries will quickly max out the MySQL thread cache.
• High CPU and memory usage trigger throttling, causing time‑outs for your API and admin routes.

Signs Your Laravel App Has Database Bottlenecks

• Slow loading admin tables ( >5 s )
• CPU % at 90‑100 % in top while browsing the dashboard
• Laravel Debugbar shows hundreds of duplicated queries
• Laravel Telescope logs “slow query” warnings for the same endpoint

INFO: The following boxes are colour‑coded for quick scanning – tip, warning, success and general info. Keep them handy while you debug.

Common Causes of N+1 Queries

1. Using belongsTo or hasMany relationships inside Blade loops without eager loading.
2. Calling ->load() inside a foreach instead of loading once.
3. API Resource collections that resolve relationships lazily.
4. Third‑party packages that chain lazy accessors (e.g., Spatie Media Library).

Step‑By‑Step Tutorial to Fix N+1 Queries

1. Install Debugging Tools

First, make sure you have Laravel Debugbar and Laravel Telescope enabled on your local or staging environment.

composer require barryvdh/laravel-debugbar --dev
composer require laravel/telescope
php artisan telescope:install
php artisan migrate

2. Locate the Heavy Route

Open Telescope, filter by GET /admin/dashboard, and note the “Queries” tab – you’ll see 10,237 queries in 4.8 s.

WARNING: Do not enable Debugbar on production – it adds overhead and can expose sensitive data.

3. Apply Eager Loading with with()

Replace the lazy loop with a single eager load:

// Before
$orders = Order::all();
foreach ($orders as $order) {
    echo $order->customer->email;
}

// After
$orders = Order::with('customer')->get();
foreach ($orders as $order) {
    echo $order->customer->email;
}

TIP: Use dot notation for nested relationships: with('customer.profile').

4. Use loadMissing() for Conditional Loads

When you occasionally need extra relations without breaking existing eager loads:

$users = User::with('roles')->get();
$users->loadMissing('profile'); // Only loads profile if not already eager loaded

5. Optimize API Resources

In OrderResource avoid calling relationships directly:

public function toArray($request)
{
    return [
        'id' => $this->id,
        'customer' => new CustomerResource($this->whenLoaded('customer')),
        // Avoid $this->customer->email here
    ];
}

6. Clean Up Blade Loops

Never call a relation inside a Blade @foreach without eager loading. Replace:

@foreach($posts as $post)
    {{ $post->author->name }}
@endforeach

with:

@foreach($posts as $post)
    {{ $post->author_name }} {{-- added via select() --}}
@endforeach

Laravel Debugbar and Telescope Tutorial

Both tools give you a query timeline. In Debugbar, enable the “Queries” panel and sort by “Count”. In Telescope, use the “Slow Queries” filter (queries > 100 ms).

SUCCESS: After eager loading, the same route dropped from 10,237 queries to just 12 queries and CPU fell from 100 % to 12 %.

MySQL Optimization Tips

• Index foreign keys used in JOIN statements (e.g., ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);).
• Enable the query_cache_type = ON for read‑heavy workloads on a shared VPS.
• Adjust innodb_buffer_pool_size to 70 % of available RAM.
• Run EXPLAIN on the longest queries and look for “Using temporary; Using filesort”.

Redis Caching Strategies

Cache heavy look‑ups that don’t change often:

Cache::remember('dashboard:orders', now()->addMinutes(5), function () {
    return Order::with('customer')->count();
});

VPS and Shared Hosting Optimization

• Upgrade to at least 2 CPU cores and 2 GB RAM on the shared plan.
• Use a dedicated MySQL user with limited privileges.
• Place MySQL and PHP‑FPM on separate containers if possible.

PHP‑FPM Optimization

In /etc/php/8.2/fpm/pool.d/www.conf set:

pm = dynamic
pm.max_children = 30
pm.start_servers = 5
pm.min_spare_servers = 5
pm.max_spare_servers = 15

Nginx or Apache Tuning

For Nginx add these snippets to your site block:

gzip on;
gzip_types text/css application/javascript image/svg+xml;
fastcgi_buffer_size 16k;
fastcgi_buffers 8 16k;

Apache users should enable mod_deflate and increase KeepAliveTimeout to 5 seconds.

Real Production Case Study

Metric	Before	After
Total Queries	10,237	12
Avg. Page Load	4.8 s	0.9 s
CPU Usage	100 %	12 %
Memory Footprint	256 MB	84 MB

Security Considerations

• Never expose raw query logs to end‑users.
• Sanitize any dynamic with() inputs to avoid mass‑assignment of relations.
• Use Laravel’s Gate and Policy checks before eager loading sensitive relationships.

Bonus Scaling Tips

1. Queue heavy reporting jobs with redis driver.
2. Offload read‑only queries to a read replica.
3. Implement HTTP caching with Cloudflare page rules for public API endpoints.
4. Consider moving to a managed Laravel VPS (e.g., DigitalOcean App Platform) once you outgrow shared hosting.

FAQ Section

Q: Does withCount() also cause N+1?

A: No. withCount() runs a single aggregated query, but be wary of adding it to large paginated sets without an index.

Q: Can I use lazy() to avoid N+1?

A: lazy() streams results but still fires a query per relationship if you don’t eager load. Combine it with with() for best results.

Q: How do I profile queries in production without Debugbar?

A: Enable Telescope in production‑only for the admin IP range, or use MySQL’s slow query log.

Final Thoughts

The N+1 nightmare is a classic but avoidable Laravel pitfall. By mastering eager loading, leveraging Debugbar/Telescope, and tuning MySQL and your VPS, you can turn a 5‑second admin panel into a sub‑second experience – even on cheap shared hosting.

Hosting or SaaS Recommendation

Looking for an affordable, secure environment that won’t choke on 10k queries? I run my production apps on Hostinger’s Laravel‑optimized shared VPS. Their SSD storage, built‑in PHP‑FPM, and 24/7 support give you headroom for growth without breaking the bank.