Stopping Dashboard Crashes: A Database and Asset Optimization Post-Mortem

It was Monday morning. Our telemetry dashboard, which monitors real-time server activity and customer payment flows for an enterprise SaaS client, was dying.

The symptoms were clear. Dashboard requests were taking 15 to 30 seconds to load. Users were greeting us with 502 Bad Gateway timeouts. Our PHP-FPM worker pools were running out of available processes, and the database server CPU was locked at 100%.

Our client was frantic. They were losing visibility over their operations, and customers couldn’t check their payment history.

As a systems developer, I’ve seen this happen a hundred times. A team builds a beautiful admin dashboard on their local machine with 100 test records. It runs in milliseconds. But the moment that same system faces a production database with two million rows and 500 active users, the unoptimized database queries, bloated client-side code, and unconfigured background processes collide to crash the server.

In this guide, I will take you step-by-step through our technical audit. We will locate the slow SQL queries, optimize our database indexing strategies, set up a bulletproof background queue daemon, eliminate frontend browser memory leaks, and automate server deployments.

Part 1: Finding the Slow Queries with System Logs

When a server is choking under load, do not guess what the problem is. Look at your logs.

First, we isolated the database. We configured MySQL to record any query that took longer than one second to run. You can do this live on your database by logging in as root and running these commands:

-- Enable the slow query log
SET GLOBAL slow_query_log = 'ON';

-- Log any query that takes longer than 1.0 seconds
SET GLOBAL long_query_time = 1.0;
-- Define where the log file is saved
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';

We let the server run for twenty minutes during a peak traffic hour. When we opened /var/log/mysql/mysql-slow.log, we found a huge file filled with slow-running statements.

To make sense of the mess, we ran a tool called mysqldumpslow to group similar queries together. The tool showed that one specific query was running thousands of times and taking an average of 4.5 seconds per run:

SELECT DATE(created_at) as day, COUNT(id) as total_jobs, SUM(amount) as total_revenue 
FROM transactions 
WHERE status = 'completed' 
GROUP BY DATE(created_at) 
ORDER BY day DESC 
LIMIT 30;

This query was responsible for drawing the "Last 30 Days Revenue" chart on the main dashboard. Every single time an administrator refreshed their page, this heavy analytical query scanned the entire database.

To see exactly why this query was destroying our CPU, we ran an execution plan by adding EXPLAIN to the beginning of the SQL statement:

EXPLAIN SELECT DATE(created_at) as day, COUNT(id) as total_jobs, SUM(amount) as total_revenue 
FROM transactions 
WHERE status = 'completed' 
GROUP BY DATE(created_at) 
ORDER BY day DESC 
LIMIT 30;

Analyzing execution plans helps us see how the database engine intends to retrieve our data. For a complete guide on reading execution plans, check the official MySQL EXPLAIN documentation.

The execution plan returned these results: type: ALL (This means a full table scan. The database had to read every single row from the disk.) rows: 1,452,098 (It scanned nearly one and a half million rows.) * Extra: Using temporary; Using filesort (The database was forced to create a temporary table on the hard drive to group and sort the results.)

Part 2: SQL Refactoring and Creating Covering Indexes

A full table scan on 1.4 million rows will choke any CPU. We had two major issues to fix in this query.

Issue 1: The DATE() function prevents index usage

When you apply a function like DATE(created_at) in your SQL queries, the database cannot use a standard index on the created_at column. It has to calculate the DATE() result for every single row first, then perform the query.

To fix this, we modified our Laravel code to perform a raw date range comparison instead of wrapping the database column in a function.

Issue 2: Missing composite indexes

We had separate indexes on status and created_at. But MySQL can typically only use one index per table query. The database engine had to pick either the status index or the created_at index, leaving the other column unindexed during the operation.

We needed to create a composite "covering index" that combines all the columns our query uses: status, created_at, and amount.

Let's write a Laravel database migration file to apply these optimizations safely:

dropIndex(['status']);
            $table->dropIndex(['created_at']);

        // Create our high-performance composite covering index
        // We order columns from most selective (filtering) to least selective
        $table->index(['status', 'created_at', 'amount'], 'idx_dashboard_stats_covering');
    });
}

/**
 * Reverse the migrations.
 *
 * @return void
 */
public function down()
{
    Schema::table('transactions', function (Blueprint $table) {
        // Rollback to original state
        $table->dropIndex('idx_dashboard_stats_covering');
        $table->index('status');
        $table->index('created_at');
    });
}


}

Why does a composite covering index speed up the query?

A composite B-Tree index is like a pre-sorted phone book. The database engine can jump straight to the rows where status is 'completed'.

Because created_at is the next column in our index, those filtered rows are already sorted by date.

And because the amount column is also included in the index, MySQL does not even need to read the main data table from the hard drive. It can perform the SUM(amount) mathematical calculations directly from the index tree stored in RAM. This is called an index-only scan, and it is incredibly fast.

When we ran our EXPLAIN command on the modified query, the results looked vastly different: type: range rows: 12,450 (It only scanned the completed transactions for our date range, instead of 1.4 million rows.) * Extra: Using index (The query was executed entirely within memory.)

The average query response time dropped from 4.5 seconds to 14 milliseconds.

Part 3: Laravel 8 Queue Worker and Daemon Configuration

With the main query database issues resolved, the dashboard load times improved, but we noticed the server was still experiencing intermittent CPU spikes.

We looked at our active processes using the terminal utility htop. We discovered that our background queue jobs were running on a basic system cron job. Every minute, the system was running this command:

 *  * * cd /var/www/html && php artisan queue:work --once

Why is running queue:work once via cron a bad practice?

Every single minute, the server had to spin up a new PHP process, load the entire Laravel framework kernel, connect to the database, run a single background job, and then terminate. This process of loading and unloading the PHP environment is called "bootstrapping overhead," and it consumes a massive amount of CPU cycles.

If twenty background jobs arrived at the same time, they had to wait for the next minute to start, causing long processing delays.

We needed our queue workers to run as persistent background daemons. A daemon starts once, loads the Laravel framework into the server's RAM, and stays running forever, waiting for jobs to arrive in real-time.

However, running long-lived PHP processes introduces a risk: memory leaks. If a third-party package has a tiny bug, memory will accumulate inside the process until the server runs out of RAM.

To prevent this, we configure our daemon to monitor its own memory consumption using the --memory flag. This tells the worker: "If you exceed 128MB of RAM, shut down cleanly. The server process manager will instantly restart you with a fresh, clean slate."

Here is how we set up a robust system daemon using Systemd.

Step 1: Create the Systemd service file

Create a file at /etc/systemd/system/laravel-worker.service:

[Unit]
Description=Laravel Background Queue Worker
After=network.target mysql.service

[Service]
# Run the worker as our standard web server user
User=www-data
Group=www-data
WorkingDirectory=/var/www/html

# Run the persistent daemon with strict limits
ExecStart=/usr/bin/php /var/www/html/artisan queue:work --queue=default,emails,telemetry --sleep=3 --tries=3 --timeout=90 --memory=128

# Automatically restart the worker if it exits or crashes
Restart=always
RestartSec=3

# Prevent systemd from killing children processes during restarts
KillMode=process

[Install]
WantedBy=multi-user.target

Step 2: Enable and start the service

Run these commands in your server terminal to register our new background worker:

# Reload the systemd configurations
sudo systemctl daemon-reload

# Enable the worker to start automatically when the server boots
sudo systemctl enable laravel-worker.service

# Start the worker right now
sudo systemctl start laravel-worker.service

Step 3: Monitor your background worker

You can check the real-time activity and ensure your background process is running smoothly using this command:

sudo systemctl status laravel-worker.service

By switching from a cron-based worker to a Systemd daemon, we reduced background task processing delays from minutes to milliseconds, and saved a huge amount of CPU cycles.

Part 4: Rebuilding the Front-End to Stop Memory Leaks

Even though our database and queue processing were now optimized, our client complained that after keeping the dashboard open on their office computer for more than an hour, their web browser became sluggish and eventually crashed.

We opened the Chrome DevTools Performance monitor and observed the memory timeline. The heap memory footprint was climbing constantly, starting at 90MB and climbing to over 1.2GB after an hour.

The old site was built using a highly complex Single Page Application (SPA) architecture. Every time the dashboard received a real-time notification via WebSockets, the framework re-rendered the charts and tables.

However, the previous developers had forgotten to clean up the event listeners and charts from the browser memory. Every dynamic update left dead references behind, preventing the browser's garbage collector from freeing the memory.

We decided to scrap the complex framework architecture. We rebuilt the administration interface using a clean, statically compiled dashboard template. I selected Arcone - Bootstrap 5 & Laravel 8 Admin Dashboard Template + HTML Version.

Why did this change solve our memory leaks?

1. Plain Bootstrap 5 and jQuery/Vanilla JS components: Because the template uses clean, native DOM manipulations instead of virtual DOM abstractions, we can track exactly when elements are created and destroyed.
2. Modular layouts: Arcone is organized logically into isolated files. We don't have global reactive states holding onto references to deleted cards or chart containers.
3. Optimized external plugins: The template comes with pre-configured charting libraries that handle real-time data streaming without leaving memory leaks in the browser heap.

To make sure your frontend templates are safe and lightweight, always acquire your files from trusted suppliers. Many developers download templates from untrusted sources, which can contain hidden malware, cryptocurrency miners, or outdated libraries that introduce severe performance degradation.

If you are looking for secure layouts to jumpstart your projects, I highly recommend going to a reputable platform for your HTML Template download assets. We downloaded our base files from GPLPAL, which gave us access to verified, untouched source code, ensuring we weren't introducing any tracking scripts or performance-degrading bloat.

Let's look at how we wrote our clean, memory-safe real-time chart code inside our new Bootstrap dashboard layout.

// real-time-stats.js

(function() {
    let telemetryChart = null;
    const chartContainer = document.getElementById('telemetry-chart-canvas');
// Store data in a simple fixed-size array to prevent infinite memory growth
const MAX_DATA_POINTS = 20;
let chartDataPoints = [];
let chartLabelPoints = [];

function initChart() {
    if (!chartContainer) return;

    const ctx = chartContainer.getContext('2d');

    // Initialize Chart.js safely
    telemetryChart = new Chart(ctx, {
        type: 'line',
        data: {
            labels: chartLabelPoints,
            datasets: [{
                label: 'Active IoT Connections',
                data: chartDataPoints,
                borderColor: '#4e73df',
                backgroundColor: 'rgba(78, 115, 223, 0.05)',
                tension: 0.3,
                fill: true
            }]
        },
        options: {
            responsive: true,
            maintainAspectRatio: false,
            animation: {
                duration: 0 // Disable heavy rendering animations to save client CPU
            },
            scales: {
                y: {
                    beginAtZero: true
                }
            }
        }
    });
}

// Process new incoming data points from our WebSocket stream
function handleIncomingData(timeString, value) {
    if (!telemetryChart) return;

    // Add new points
    chartDataPoints.push(value);
    chartLabelPoints.push(timeString);

    // Remove oldest points if we exceed our limit
    if (chartDataPoints.length > MAX_DATA_POINTS) {
        chartDataPoints.shift();
        chartLabelPoints.shift();
    }

    // Update the chart visual layer efficiently
    telemetryChart.update('none'); // Update without running costly animations
}

// Clean up memory when user navigates away or dashboard is closed
function destroyTelemetryChart() {
    if (telemetryChart) {
        telemetryChart.destroy();
        telemetryChart = null;
        console.log('Telemetry chart memory cleared.');
    }
    chartDataPoints = [];
    chartLabelPoints = [];
}

// Start everything up
document.addEventListener('DOMContentLoaded', initChart);

// Watch for custom window unload events to clean memory
window.addEventListener('beforeunload', destroyTelemetryChart);

// Expose our dynamic updater globally so our WebSocket listener can trigger it
window.updateDashboardTelemetry = handleIncomingData;


})();

Why is this memory-safe?

1. Fixed array sizes (MAX_DATA_POINTS): The data arrays never grow larger than 20 items. This prevents the browser from holding onto thousands of old data points over time.
2. Animation suppression (duration: 0): By disabling the chart draw animations, we prevent the browser from running complex layout calculations every time a new data point arrives. This saves battery life on laptops and mobile devices.
3. Explicit destruction (destroyTelemetryChart): If the user navigates away, we cleanly call chart.destroy(). This deletes all references to DOM nodes and canvas elements, allowing the browser's garbage collector to immediately reclaim 100% of the allocated memory.

Part 5: Automating the Server Deployment Pipeline

When you run a high-traffic system, deploying changes manually over SSH is dangerous. You might forget to clear the cache, miss a database migration, or leave your queue workers running old code.

To make deployments reliable and fast, we wrote a custom shell script that automates the entire process. This script runs on our build server during every deployment. It puts our application into maintenance mode, clears caches, runs migrations, and triggers our systemd queue workers to restart safely.

Save this script as deploy.sh in your project's root folder:

#!/bin/bash

Set strict error handling. If any command fails, stop the script immediately.
set -e
echo "=== Starting deployment pipeline ==="
Define paths
APP_DIR="/var/www/html"
WEB_USER="www-data"
Navigate to the application root directory
cd $APP_DIR
1. Activate Laravel maintenance mode
echo "Enabling maintenance mode..."
php artisan down --retry=60 || echo "Site is already in maintenance mode."
2. Pull the latest code from git repository
echo "Pulling latest changes from main branch..."
git pull origin main
3. Install composer dependencies (optimized for production)
echo "Installing composer dependencies..."
composer install --no-dev --optimize-autoloader --no-interaction
4. Clear and rebuild configuration and route caches
This compiles our PHP bootstrap routes and configuration files into static arrays
echo "Optimizing configuration caches..."
php artisan config:cache
php artisan route:cache
php artisan view:cache
5. Run database migrations safely
echo "Running database migrations..."
php artisan migrate --force
6. Optimize asset files
if [ -f "artisan" ]; then
    echo "Running static asset cleanup..."
    php artisan cache:clear
fi
7. Restart the background systemd queue workers
This forces the daemons to reload and run our updated PHP code files
echo "Restarting background queue daemon..."
sudo systemctl restart laravel-worker.service
8. Deactivate maintenance mode
echo "Disabling maintenance mode..."
php artisan up
echo "=== Deployment complete! Site is back online ==="

How to use this script in production:

1. Upload this file to your server as deploy.sh.
2. Give it execution permissions using your terminal: bash chmod +x deploy.sh
3. Run it whenever you deploy updates: bash ./deploy.sh

Part 6: Post-Refactoring Performance Metrics

To prove our optimizations worked, we ran a series of load tests and system performance checks before and after applying our database, daemon, and memory improvements.

Here are the technical results:

Strategic Takeaways

Optimizing high-traffic systems doesn't require purchasing expensive server upgrades. It requires understanding database structures, setting up background daemons properly, and keeping your front-end light.

If you are facing performance challenges on your server, follow these rules: Don't search blindly: Turn on your slow query log and run EXPLAIN to understand exactly how your database engine is fetching your data. Stop table scans: Create composite covering indexes that combine your filters, sorting, and aggregate columns into a single index tree in RAM. Ditch cron-based queue processors: Use persistent systemd daemons with strict memory caps to avoid bootstrap overhead and process background tasks in real-time. Avoid bloated SPA code: Use clean, structured HTML and CSS frameworks. Avoid holding dynamic references in memory without cleaning up. * Automate everything: Create deployment scripts to handle cache optimization, database changes, and process restarts automatically to eliminate human error.

By spending a few hours digging into your system logs and restructuring your code, you can build incredibly robust websites that run lightning-fast on minimal server resources.