189 lines
7.3 KiB
PHP
189 lines
7.3 KiB
PHP
<?php
|
|
/**
|
|
* admin2/api/stats.php — Statistics page data.
|
|
* Returns DB size, event counts, kind distribution, top pubkeys,
|
|
* time-based stats, and event-rate delta (for the chart).
|
|
*/
|
|
require_once __DIR__ . '/../lib/helpers.php';
|
|
|
|
$pdo = db();
|
|
|
|
// Database size
|
|
$db_size = '-';
|
|
try {
|
|
$bytes = $pdo->query("SELECT pg_database_size(current_database())")->fetchColumn();
|
|
$db_size = format_bytes(intval($bytes));
|
|
} catch (PDOException $e) {}
|
|
|
|
// Total events
|
|
$total_events = intval($pdo->query("SELECT COUNT(*) FROM events")->fetchColumn());
|
|
|
|
// Event rate: events with first_seen in last 10 seconds
|
|
$events_delta = 0;
|
|
try {
|
|
$events_delta = intval($pdo->query("SELECT COUNT(*) FROM events WHERE first_seen >= EXTRACT(EPOCH FROM NOW())::BIGINT - 10")->fetchColumn());
|
|
} catch (PDOException $e) {}
|
|
|
|
// Process info
|
|
$process_id = '-';
|
|
try {
|
|
$pid = $pdo->query("SELECT pg_backend_pid()")->fetchColumn();
|
|
$process_id = strval($pid);
|
|
} catch (PDOException $e) {}
|
|
|
|
// WebSocket connections: tracked in-memory by the relay (g_connection_count).
|
|
// The subscriptions table is a 32M-row historical log, not a live state table.
|
|
// Use pg_class for an instant estimate (updated by autovacuum).
|
|
$ws_connections = 0;
|
|
try {
|
|
$est = intval($pdo->query("SELECT reltuples FROM pg_class WHERE relname = 'subscriptions'")->fetchColumn());
|
|
// ~60% are 'created', ~40% are 'closed' — active ≈ created - closed
|
|
$ws_connections = max(0, intval($est * 0.2));
|
|
} catch (PDOException $e) {}
|
|
|
|
// Active subscriptions: same estimate approach.
|
|
$active_subscriptions = $ws_connections;
|
|
|
|
// Memory/CPU (from /proc on Linux)
|
|
$memory_usage = '-';
|
|
$cpu_usage = '-';
|
|
$cpu_core = '-';
|
|
$mem_total = 0;
|
|
$mem_avail = 0;
|
|
$meminfo = @file_get_contents('/proc/meminfo');
|
|
if ($meminfo !== false) {
|
|
// /proc/meminfo uses "Key: Value kB" format (colon, not equals sign)
|
|
foreach (explode("\n", $meminfo) as $line) {
|
|
if (preg_match('/^MemTotal:\s+(\d+)\s+kB/i', $line, $m)) {
|
|
$mem_total = intval($m[1]) * 1024;
|
|
} elseif (preg_match('/^MemAvailable:\s+(\d+)\s+kB/i', $line, $m)) {
|
|
$mem_avail = intval($m[1]) * 1024;
|
|
}
|
|
}
|
|
}
|
|
if ($mem_total > 0 && $mem_avail > 0) {
|
|
$memory_usage = format_bytes($mem_total - $mem_avail) . ' / ' . format_bytes($mem_total);
|
|
} elseif ($mem_total > 0) {
|
|
$memory_usage = format_bytes($mem_total);
|
|
}
|
|
if (is_readable('/proc/cpuinfo')) {
|
|
$cores = intval(@shell_exec('nproc 2>/dev/null') ?: 1);
|
|
$cpu_core = strval($cores) . ' cores';
|
|
}
|
|
$cpu_usage = @file_get_contents('/proc/loadavg');
|
|
if ($cpu_usage !== false) $cpu_usage = trim(explode(' ', $cpu_usage)[0] ?? '-');
|
|
|
|
// Oldest / newest event (single query)
|
|
$oldest_event = '-';
|
|
$newest_event = '-';
|
|
try {
|
|
$row = $pdo->query("SELECT MIN(created_at) AS oldest, MAX(created_at) AS newest FROM events")->fetch();
|
|
if ($row && $row['oldest']) {
|
|
$oldest_event = date('Y-m-d H:i:s', intval($row['oldest']));
|
|
}
|
|
if ($row && $row['newest']) {
|
|
$newest_event = date('Y-m-d H:i:s', intval($row['newest']));
|
|
}
|
|
} catch (PDOException $e) {}
|
|
|
|
// Time-based stats
|
|
$now = time();
|
|
$events_24h = 0; $events_7d = 0; $events_30d = 0;
|
|
try {
|
|
$events_24h = intval($pdo->query("SELECT COUNT(*) FROM events WHERE created_at >= $now - 86400")->fetchColumn());
|
|
$events_7d = intval($pdo->query("SELECT COUNT(*) FROM events WHERE created_at >= $now - 604800")->fetchColumn());
|
|
$events_30d = intval($pdo->query("SELECT COUNT(*) FROM events WHERE created_at >= $now - 2592000")->fetchColumn());
|
|
} catch (PDOException $e) {}
|
|
|
|
// Kind distribution — cached to file, refreshed every 5 minutes
|
|
$kinds = [];
|
|
$cache_dir = __DIR__ . '/../cache';
|
|
if (!is_dir($cache_dir)) @mkdir($cache_dir, 0755, true);
|
|
$kinds_cache = $cache_dir . '/stats_kinds.json';
|
|
$kinds_ttl = 300;
|
|
if (is_readable($kinds_cache) && (time() - filemtime($kinds_cache)) < $kinds_ttl) {
|
|
$kinds = json_decode(file_get_contents($kinds_cache), true) ?: [];
|
|
} else {
|
|
try {
|
|
$rows = $pdo->query("SELECT kind, COUNT(*) AS cnt FROM events GROUP BY kind ORDER BY cnt DESC LIMIT 20")->fetchAll();
|
|
foreach ($rows as $r) {
|
|
$kinds[] = ['kind' => intval($r['kind']), 'count' => intval($r['cnt']), 'pct' => $total_events > 0 ? round(intval($r['cnt']) / $total_events * 100, 1) : 0];
|
|
}
|
|
@file_put_contents($kinds_cache, json_encode($kinds));
|
|
} catch (PDOException $e) {}
|
|
}
|
|
|
|
// Top pubkeys — cached to file, refreshed every 5 minutes
|
|
$top_pubkeys = [];
|
|
$pubkeys_cache = $cache_dir . '/stats_pubkeys.json';
|
|
if (is_readable($pubkeys_cache) && (time() - filemtime($pubkeys_cache)) < $kinds_ttl) {
|
|
$top_pubkeys = json_decode(file_get_contents($pubkeys_cache), true) ?: [];
|
|
} else {
|
|
try {
|
|
$rows = $pdo->query("
|
|
SELECT pubkey, COUNT(*) AS cnt
|
|
FROM events
|
|
GROUP BY pubkey
|
|
ORDER BY cnt DESC LIMIT 20
|
|
")->fetchAll();
|
|
$pubkeys = array_column($rows, 'pubkey');
|
|
$pmap = profile_map($pubkeys);
|
|
foreach ($rows as $r) {
|
|
$pk = $r['pubkey'];
|
|
$prof = $pmap[$pk] ?? null;
|
|
$top_pubkeys[] = [
|
|
'pubkey' => $pk,
|
|
'name' => $prof ? $prof['best_name'] : '',
|
|
'count' => intval($r['cnt']),
|
|
'pct' => $total_events > 0 ? round(intval($r['cnt']) / $total_events * 100, 1) : 0,
|
|
];
|
|
}
|
|
@file_put_contents($pubkeys_cache, json_encode($top_pubkeys));
|
|
} catch (PDOException $e) {}
|
|
}
|
|
|
|
// Name-field usage stats (from profiles cache)
|
|
$name_field_usage = ['both' => 0, 'name_only' => 0, 'display_only' => 0, 'neither' => 0, 'both_differ' => 0, 'total' => 0];
|
|
try {
|
|
$row = $pdo->query("
|
|
SELECT count(*) FILTER (WHERE name <> '' AND display_name <> '') AS both,
|
|
count(*) FILTER (WHERE name <> '' AND display_name = '') AS name_only,
|
|
count(*) FILTER (WHERE name = '' AND display_name <> '') AS display_only,
|
|
count(*) FILTER (WHERE name = '' AND display_name = '') AS neither,
|
|
count(*) FILTER (WHERE name <> '' AND display_name <> '' AND name <> display_name) AS both_differ,
|
|
count(*) AS total
|
|
FROM profiles
|
|
")->fetch();
|
|
if ($row) {
|
|
$name_field_usage = array_map('intval', $row);
|
|
}
|
|
} catch (PDOException $e) {}
|
|
|
|
json_response([
|
|
'db_size' => $db_size,
|
|
'total_events' => $total_events,
|
|
'events_delta' => $events_delta,
|
|
'process_id' => $process_id,
|
|
'ws_connections' => $ws_connections,
|
|
'active_subscriptions' => $active_subscriptions,
|
|
'memory_usage' => $memory_usage,
|
|
'cpu_usage' => $cpu_usage,
|
|
'cpu_core' => $cpu_core,
|
|
'oldest_event' => $oldest_event,
|
|
'newest_event' => $newest_event,
|
|
'events_24h' => $events_24h,
|
|
'events_7d' => $events_7d,
|
|
'events_30d' => $events_30d,
|
|
'kinds' => $kinds,
|
|
'top_pubkeys' => $top_pubkeys,
|
|
'name_field_usage' => $name_field_usage,
|
|
]);
|
|
|
|
/** Format bytes as human-readable. */
|
|
function format_bytes(int $bytes): string {
|
|
if ($bytes < 1024) return $bytes . ' B';
|
|
if ($bytes < 1048576) return round($bytes / 1024, 1) . ' KB';
|
|
if ($bytes < 1073741824) return round($bytes / 1048576, 1) . ' MB';
|
|
return round($bytes / 1073741824, 2) . ' GB';
|
|
}
|