29 — Performance¶

Overview¶

PyroRadius is designed to handle ISP operations at the scale of thousands of customers, multiple NAS devices, and continuous background polling. Performance optimizations span the database (functional indexes, lock management, null-safe comparisons), the cache layer (Redis for hot data), queue offloading (async operations), and the frontend (Recharts rendering, Vite build). This document catalogs all known optimizations, the reasoning behind each, and remaining bottlenecks.

Database Performance¶

Functional Indexes¶

Two functional indexes are defined on the customers table to support case-insensitive lookups that are core to the application:

1. Index on LOWER(pppoe_username)¶

CREATE UNIQUE INDEX idx_customers_pppoe_username_lower
    ON customers (LOWER(pppoe_username));

Why: PPPoE username lookups during RADIUS authentication and in the UI search are case-insensitive. Without this index, every username query would require a sequential scan with LOWER() applied to every row. With the functional index, PostgreSQL can do an index scan directly.

Query pattern this supports:

-- Uses the functional index
SELECT * FROM customers WHERE LOWER(pppoe_username) = LOWER('John.Doe');

-- Also covered in Laravel
Customer::whereRaw('LOWER(pppoe_username) = ?', [strtolower($input)]);

The index is also UNIQUE, enforcing uniqueness regardless of case at the database level (preventing duplicates that differ only in case).

2. Index on LOWER(TRIM(description)) on packages¶

CREATE INDEX idx_packages_description_lower
    ON packages (LOWER(TRIM(description)));

Why: Package name search and import matching uses case-insensitive comparison. The TRIM() in the index handles leading/trailing spaces that may exist in imported data.

PostgreSQL lock_timeout¶

All database sessions in PyroRadius are configured with a 30-second lock timeout:

// config/database.php
'pgsql' => [
    ...
    'options' => [
        PDO::ATTR_STATEMENT_CLASS => ...,
    ],
    'search_path' => 'public',
    'sslmode' => 'prefer',
],

And set via a DB::statement in a service provider or via the options key:

SET lock_timeout = '30s';

Why: Without a lock timeout, a query waiting for a lock (e.g., during bulk RADIUS sync) can block indefinitely, causing the request to hang until PHP-FPM's max execution time kills it. With lock_timeout=30s, PostgreSQL raises a 55P03 error (lock not available) after 30 seconds, which the application can catch and handle gracefully.

55P03 Retry Loop in CustomerController::update()¶

The CustomerController::update() method implements a retry loop for the 55P03 lock-not-available error:

$maxAttempts = 3;
$attempt = 0;

while ($attempt < $maxAttempts) {
    try {
        DB::transaction(function () use ($customer, $validated) {
            $customer->update($validated);
            SyncCustomerRadiusJob::dispatch($customer->id, 'upsert')->onQueue('radius');
        });
        break;
    } catch (\Illuminate\Database\QueryException $e) {
        if ($attempt < $maxAttempts - 1 && str_contains($e->getMessage(), '55P03')) {
            $attempt++;
            usleep(200000 * $attempt); // 200ms, 400ms backoff
            continue;
        }
        throw $e;
    }
}

Why: Customer updates can collide with bulk RADIUS sync operations that lock the customer row. The retry loop with exponential backoff resolves transient lock contention without surfacing an error to the operator. After 3 failed attempts, the exception propagates normally.

IS DISTINCT FROM for Null-Safe Comparisons¶

Used in update queries to avoid unnecessary writes when values haven't changed:

-- Standard null-unsafe (fails when either side is NULL)
WHERE old_value != new_value

-- Null-safe (used in PyroRadius)
WHERE old_value IS DISTINCT FROM new_value

This is the PostgreSQL equivalent of <=> in MySQL (but semantically inverted). It returns FALSE when both sides are equal including both being NULL, and TRUE when they differ (including one being NULL and the other not).

Used extensively in SyncOntAssignments (see below) and in update statements that conditionally write only changed fields.

SyncOntAssignments Optimization¶

Date of fix: 2026-07-13

Command: App\Console\Commands\SyncOntAssignments

Before: The command updated every customer's ont_serial and rx_power on every run, regardless of whether the values had changed. With 5000 customers, this caused 5000 UPDATE statements touching the customers table, acquiring row locks and invalidating PostgreSQL's page cache.

Problem observed: Runtime of ~14 seconds, causing lock contention with concurrent web requests and RADIUS sync jobs.

Fix applied: Added an IS DISTINCT FROM guard so the command only generates UPDATE statements for rows where the data has actually changed:

UPDATE customers
SET
    ont_serial = ont_cache.serial,
    rx_power   = ont_cache.rx_power,
    updated_at = NOW()
FROM ont_cache
WHERE customers.ont_mac = ont_cache.mac
  AND (
      customers.ont_serial IS DISTINCT FROM ont_cache.serial
      OR customers.rx_power IS DISTINCT FROM ont_cache.rx_power
  );

Result after fix: Runtime dropped from ~14 seconds to ~418ms on the same dataset. On a typical run where ONT data changes for only a small fraction of customers (signal fluctuation for a few), the query updates only 30–200 rows instead of 5000.

Impact: Eliminated the primary source of lock contention during the 10-minute polling cycle. The command no longer competes with customer edit operations.

Redis Caching¶

Online Count Cache¶

The "Online Now" metric (number of customers currently connected) is the most-viewed KPI on the dashboard. Calculating it requires querying the radacct table on the FreeRADIUS database for open sessions, which is expensive (table can have millions of rows).

Solution: The online count is cached in Redis:

// Cache key pattern
$cacheKey = "online_count:{$dealerId}";

// Read (in DashboardController or OnlineCountService)
$count = Cache::remember($cacheKey, now()->addMinutes(2), function () use ($dealerId) {
    return $this->queryOnlineCount($dealerId);
});

// Invalidation: cache expires naturally after 2 minutes
// No explicit invalidation needed — staleness of 2 minutes is acceptable

Why 2 minutes is acceptable: RADIUS session data updates are driven by the NAS. Sessions open/close on the NAS side. A 2-minute cache window means the dashboard count may be up to 2 minutes stale, which is acceptable for a monitoring dashboard. Operators who need real-time counts can trigger a manual refresh.

Why not poll all NAS on every request: A typical deployment has 3–8 NAS devices. Polling each via SNMP or RouterOS API takes 200–800ms per device. Doing this on every dashboard page load would make the dashboard take 2–6 seconds to render. The Redis cache reduces this to a sub-millisecond lookup.

Cache Invalidation¶

The online count cache is invalidated explicitly in two scenarios:

When a customer is disconnected via KickExpiredOnline — the command calls Cache::forget("online_count:{$dealerId}") after sending PoD packets.
When PollTraffic runs — it updates NAS last-seen data and flushes the count cache to pick up any closed sessions.

PollTraffic Caching¶

PollTraffic caches its per-NAS bandwidth summary (current in/out rates) in Redis with a 5-minute TTL. The dashboard traffic widget reads from this cache rather than querying traffic_readings:

$trafficData = Cache::get("nas_traffic:{$nasId}", []);

If the cache is empty (first run or after restart), the widget shows "--" until the next poll cycle completes.

N+1 Query Risks and Eager Loading¶

Known N+1 Locations and Their Fixes¶

Location	Risk	Resolution
`CustomerController::index()`	Listing customers with package name requires N queries for packages	`with('package', 'area', 'nas')` eager load
`InvoiceController::index()`	Invoice list needs customer name	`with('customer')`
`TicketController::index()`	Ticket list needs customer + assigned user	`with('customer', 'assignedTo', 'category')`
`DashboardController`	NAS status loop	NAS loaded once, not per-iteration

Eager loading pattern in controllers:

$customers = Customer::with(['package:id,name,speed_down,speed_up', 'area:id,name', 'nas:id,name'])
    ->where('dealer_id', $user->dealer_id)
    ->paginate(50);

The :id,name column constraint on the relation prevents loading full model data for the relation when only display fields are needed.

Laravel Debugbar / Telescope¶

In development, barryvdh/laravel-debugbar or Laravel Telescope can be enabled to detect N+1 queries automatically. They are disabled in production via APP_ENV=production.

Pagination¶

All list views in PyroRadius are paginated. No endpoint returns an unbounded list.

Resource	Page Size	Notes
Customers	50 per page	Filterable by status, dealer, area, package
Invoices	50 per page	Filterable by status, date range
Payments	50 per page	Filterable by date range
Audit Logs	100 per page	Sorted by created_at DESC
Traffic Readings	100 per page	Time-range filtered
Tickets	25 per page	Filterable by status, priority

Inertia handles pagination via the paginate() method which returns a LengthAwarePaginator. The React page receives data, links, and meta props, and renders a <Pagination> component from the shared components library.

Queue Offloading¶

Heavy operations are always dispatched to the queue rather than executed synchronously in the HTTP request:

Operation	Job Class	Queue	Reason
RADIUS sync on customer create/edit	`SyncCustomerRadiusJob`	radius	Writes to FreeRADIUS DB, can take 200–500ms
RADIUS sync on package change	`SyncCustomerRadiusJob`	radius	Same as above
Bulk RADIUS sync (SyncAllRadius command)	Dispatches multiple `SyncCustomerRadiusJob`	radius	Could take minutes for thousands of customers
WhatsApp message on expiry/payment	`SendWhatsAppNotificationJob`	whatsapp	External HTTP API call, unpredictable latency
Campaign broadcast	`SendCampaignJob`	whatsapp	Thousands of messages, must not block request

The pattern for all queue dispatches:

// Never: do it inline
$radiusService->sync($customer);  // blocks HTTP response for 500ms

// Always: dispatch to queue
SyncCustomerRadiusJob::dispatch($customer->id, 'upsert')->onQueue('radius');
// HTTP response returns immediately

Query Optimization Patterns¶

Select Only Needed Columns¶

Avoid SELECT * when only specific fields are needed:

// List view — only needs display fields
Customer::select('id', 'customer_code', 'full_name', 'mobile', 'status', 'expiry_date', 'package_id')
    ->with('package:id,name')
    ->where('dealer_id', $dealerId)
    ->paginate(50);

Especially important for the customers table which has many columns including text fields (notes, address) that are never shown on list views.

whereIn vs Separate Queries¶

For batch operations, whereIn is used instead of looping:

// Bad: N queries
foreach ($customerIds as $id) {
    Customer::find($id)->update(['status' => 'expired']);
}

// Good: 1 query
Customer::whereIn('id', $customerIds)->update(['status' => 'expired']);

CheckExpiry and KickExpiredOnline both use whereIn for batch status updates.

Scoped Queries in Dashboard¶

The dashboard runs multiple aggregate queries (total customers, active count, due invoices, overdue count). These are run as separate fast queries rather than joined:

$stats = [
    'total_customers' => Customer::where('dealer_id', $id)->count(),
    'active'          => Customer::where('dealer_id', $id)->where('status', 'active')->count(),
    'expired'         => Customer::where('dealer_id', $id)->where('status', 'expired')->count(),
    'due_invoices'    => Invoice::where('dealer_id', $id)->where('status', 'unpaid')->count(),
];

PostgreSQL COUNT queries with proper indexes return in < 5ms even on large tables.

Frontend Performance¶

Recharts (Client-Side Rendering)¶

The Health system and Dashboard use Recharts (recharts npm package) for line and bar charts. Charts render on the client side — data is sent from the server as JSON arrays and Recharts does the SVG rendering in the browser.

Performance considerations:

Charts are wrapped in <ResponsiveContainer> which lazy-resizes on window resize but does not re-fetch data.
Data points are limited: sys_stats_history queries return at most 288 points per chart (one per 5 minutes over 24 hours). This is well within Recharts' comfortable rendering range.
Charts are rendered inside React's virtual DOM — re-renders only occur when data props change.
React.memo is used on chart wrapper components to prevent re-renders from unrelated parent state changes.

Vite Build Optimization¶

File: vite.config.js

export default defineConfig({
    plugins: [laravel({ input: ['resources/js/app.jsx'] }), react()],
    build: {
        rollupOptions: {
            output: {
                manualChunks: {
                    vendor: ['react', 'react-dom'],
                    charts: ['recharts'],
                    inertia: ['@inertiajs/react'],
                },
            },
        },
    },
});

Manual chunk splitting ensures: - react + react-dom are in a separate vendor chunk (cached aggressively). - recharts is in a separate charts chunk (large library, only loaded on pages that need it). - Most pages load only vendor + inertia + the page-specific chunk.

Hashed filenames (app.abc123.js) enable long-lived browser caching. Cache busting happens automatically when the content changes.

PHP OpCache¶

PHP OpCache is enabled on the production server (php8.3-fpm). Configuration in /etc/php/8.3/fpm/php.ini:

opcache.enable=1
opcache.memory_consumption=256
opcache.max_accelerated_files=20000
opcache.validate_timestamps=0    ; disabled in production (manual reset on deploy)
opcache.revalidate_freq=0

validate_timestamps=0 means PHP never checks if the source file has changed — OpCache always serves the compiled bytecode. This is safe in production because the deploy process explicitly resets OpCache via:

php artisan opcache:clear   # or systemctl restart php8.3-fpm

The result is that PHP file parsing overhead is eliminated entirely for cached files.

Known Performance Bottlenecks and Recommended Fixes¶

Bottleneck	Severity	Description	Recommended Fix
`SyncAllRadius` full rebuild	Medium	Running `radius:sync-all` on 5000+ customers generates 5000+ queue jobs simultaneously, saturating the radius queue for 5–10 minutes	Add a throttle (`Bus::batch()` with concurrency limit) or chunk into time-spread waves
Bulk WhatsApp campaigns	Medium	Large campaigns (1000+ customers) dispatch 1000+ queue jobs, and OpenWA can be rate-limited	Implement token bucket rate limiter per campaign (already partially done via Redis rate limiter)
Export to Excel with no limit	Medium	If an operator exports all customers (5000 rows) to Excel, PhpSpreadsheet loads all records into memory at once	Add streaming export using `Maatwebsite/Laravel-Excel`'s `WithChunkReading`
`radacct` table growth	High	On a busy ISP, `radacct` can accumulate millions of rows within months. Aggregate queries (even with indexes) slow down	Add PostgreSQL table partitioning by `acctstarttime` (monthly partitions); archive old partitions
No database connection pooling	Low	Each PHP-FPM worker opens its own PostgreSQL connection. 50 workers = 50 connections, approaching typical `max_connections=100`	Deploy PgBouncer in transaction mode between PHP-FPM and PostgreSQL
Inertia full-page props on navigation	Low	Every Inertia page visit sends all shared props (permissions, user, company) — small but adds ~2KB per response	Move infrequently-changing props (company profile) to a dedicated API endpoint loaded once
No query result caching for package/area lists	Low	Package and area dropdowns are queried fresh on every customer edit/create page load	Cache `packages` and `areas` per dealer in Redis with a 5-minute TTL; bust on create/update