71 lines
3.3 KiB
Markdown
71 lines
3.3 KiB
Markdown
# Plan: Fix Missing Statistics Metrics
|
|
|
|
## Root Causes
|
|
|
|
### 1. WebSocket Connections = 0
|
|
The stats API at [`admin/api/stats.php:33`](admin/api/stats.php:33) queries:
|
|
```php
|
|
$ws_connections = intval($pdo->query("SELECT count(*) FROM pg_stat_activity
|
|
WHERE state = 'active' AND pid != pg_backend_pid()")->fetchColumn());
|
|
```
|
|
This is **wrong** — WebSocket connections are tracked in-memory by the relay via `g_connection_count` in [`src/websockets.c:139`](src/websockets.c:139), not in `pg_stat_activity`. The PHP code is counting PostgreSQL backend processes, not WebSocket clients.
|
|
|
|
**Fix:** Query the `subscriptions` table for distinct `wsi_pointer` values where `event_type = 'created'` and no corresponding `'closed'`/`'disconnected'` event exists. Or simpler: count distinct `wsi_pointer` values from the most recent `'created'` entries.
|
|
|
|
### 2. Active Subscriptions = 0
|
|
The stats API at [`admin/api/stats.php:39`](admin/api/stats.php:39) queries:
|
|
```php
|
|
$active_subscriptions = intval($pdo->query("SELECT count(*) FROM subscriptions
|
|
WHERE active = true")->fetchColumn());
|
|
```
|
|
The `subscriptions` table has **no `active` column** (see [`src/pg_schema.sql:207`](src/pg_schema.sql:207)). It has `event_type` with values 'created', 'closed', 'expired', 'disconnected'.
|
|
|
|
**Fix:** Count subscriptions where `event_type = 'created'` and no matching `'closed'`/`'disconnected'` record exists for the same `(subscription_id, wsi_pointer)`.
|
|
|
|
### 3. Memory Usage = '-'
|
|
The code at [`admin/api/stats.php:46-51`](admin/api/stats.php:46) reads `/proc/meminfo` — this may fail if the PHP-FPM process is running under a restricted user (e.g., `www-data` with `ProtectProc=invisible` or similar systemd sandboxing).
|
|
|
|
**Fix:** Use `file_get_contents('/proc/meminfo')` with proper error handling (already there), or fall back to `sys_getloadavg()` and `shell_exec('free -b')` as alternatives.
|
|
|
|
### 4. Oldest/Newest Event = '-'
|
|
The query at [`admin/api/stats.php:63-64`](admin/api/stats.php:63) uses `to_timestamp(MIN(created_at))` — this should work on 3.3M events but may be slow. Could be timing out.
|
|
|
|
**Fix:** Use `SELECT MIN(created_at), MAX(created_at) FROM events` in a single query, then format in PHP. Add a query timeout safeguard.
|
|
|
|
## Changes Required
|
|
|
|
### File: `admin/api/stats.php`
|
|
|
|
| Line | Current | Fix |
|
|
|------|---------|-----|
|
|
| 33 | `pg_stat_activity` query | Count distinct active WebSocket connections from `subscriptions` table |
|
|
| 39 | `WHERE active = true` | Count active subscriptions using `event_type` logic |
|
|
| 46-51 | `/proc/meminfo` | Add fallback using `shell_exec('free -b')` |
|
|
| 63-64 | Two separate `to_timestamp` queries | Single `SELECT MIN(created_at), MAX(created_at)` query |
|
|
|
|
### SQL for Active Connections
|
|
```sql
|
|
SELECT COUNT(DISTINCT wsi_pointer) FROM subscriptions
|
|
WHERE event_type = 'created'
|
|
AND wsi_pointer NOT IN (
|
|
SELECT wsi_pointer FROM subscriptions
|
|
WHERE event_type IN ('closed', 'disconnected', 'expired')
|
|
)
|
|
```
|
|
|
|
### SQL for Active Subscriptions
|
|
```sql
|
|
SELECT COUNT(*) FROM (
|
|
SELECT subscription_id, wsi_pointer FROM subscriptions
|
|
WHERE event_type = 'created'
|
|
EXCEPT
|
|
SELECT subscription_id, wsi_pointer FROM subscriptions
|
|
WHERE event_type IN ('closed', 'disconnected', 'expired')
|
|
) AS active_subs
|
|
```
|
|
|
|
### SQL for Oldest/Newest
|
|
```sql
|
|
SELECT MIN(created_at) AS oldest, MAX(created_at) AS newest FROM events
|
|
```
|