mstallmo.com
forked from
smokesignal.events/quickdid
QuickDID is a high-performance AT Protocol identity resolution service written in Rust. It provides handle-to-DID resolution with Redis-backed caching and queue processing.
1-- Test script to verify all metrics queries work correctly
2-- Run this after sending test metrics with send-metrics.sh
3
4\echo '===== CHECKING AVAILABLE TABLES ====='
5SELECT table_name
6FROM information_schema.tables
7WHERE table_schema = 'public'
8 AND table_name LIKE 'quickdid%'
9ORDER BY table_name;
10
11\echo ''
12\echo '===== CHECKING TABLE STRUCTURES ====='
13\echo 'Structure of quickdid.http.request.count table:'
14\d "quickdid.http.request.count"
15
16\echo ''
17\echo 'Structure of quickdid.http.request.duration_ms table:'
18\d "quickdid.http.request.duration_ms"
19
20\echo ''
21\echo '===== QUERY 1: Recent HTTP Request Counts ====='
22SELECT
23 time,
24 tags,
25 tags->>'method' as method,
26 tags->>'path' as path,
27 tags->>'status' as status,
28 value
29FROM "quickdid.http.request.count"
30WHERE time > NOW() - INTERVAL '1 hour'
31ORDER BY time DESC
32LIMIT 10;
33
34\echo ''
35\echo '===== QUERY 2: HTTP Request Duration Statistics by Endpoint ====='
36SELECT
37 time_bucket('1 minute', time) AS minute,
38 tags->>'method' as method,
39 tags->>'path' as path,
40 tags->>'status' as status,
41 COUNT(*) as request_count,
42 AVG(mean) as avg_duration_ms,
43 MAX(p99) as p99_duration_ms,
44 MIN(mean) as min_duration_ms
45FROM "quickdid.http.request.duration_ms"
46WHERE time > NOW() - INTERVAL '1 hour'
47 AND tags IS NOT NULL
48GROUP BY minute, tags->>'method', tags->>'path', tags->>'status'
49ORDER BY minute DESC
50LIMIT 10;
51
52\echo ''
53\echo '===== QUERY 3: Rate Limiter Status Over Time ====='
54SELECT
55 time,
56 value as available_permits
57FROM "quickdid.resolver.rate_limit.available_permits"
58WHERE time > NOW() - INTERVAL '1 hour'
59ORDER BY time DESC
60LIMIT 10;
61
62\echo ''
63\echo '===== QUERY 4: Resolver Performance Comparison ====='
64SELECT
65 tags->>'resolver' as resolver_type,
66 COUNT(*) as sample_count,
67 AVG(mean) as avg_resolution_time_ms,
68 MAX(p99) as p99_resolution_time_ms,
69 MIN(mean) as min_resolution_time_ms
70FROM "quickdid.resolver.resolution_time"
71WHERE time > NOW() - INTERVAL '1 hour'
72 AND tags->>'resolver' IS NOT NULL
73GROUP BY tags->>'resolver'
74ORDER BY avg_resolution_time_ms;
75
76\echo ''
77\echo '===== QUERY 5: Cache Hit Rate Analysis ====='
78WITH cache_stats AS (
79 SELECT
80 'hits' as metric_type,
81 SUM(value) as total_count
82 FROM "quickdid.cache.hit.count"
83 WHERE time > NOW() - INTERVAL '1 hour'
84 UNION ALL
85 SELECT
86 'misses' as metric_type,
87 SUM(value) as total_count
88 FROM "quickdid.cache.miss.count"
89 WHERE time > NOW() - INTERVAL '1 hour'
90)
91SELECT
92 SUM(CASE WHEN metric_type = 'hits' THEN total_count ELSE 0 END) as total_hits,
93 SUM(CASE WHEN metric_type = 'misses' THEN total_count ELSE 0 END) as total_misses,
94 CASE
95 WHEN SUM(total_count) > 0 THEN
96 ROUND(100.0 * SUM(CASE WHEN metric_type = 'hits' THEN total_count ELSE 0 END) / SUM(total_count), 2)
97 ELSE 0
98 END as hit_rate_percentage
99FROM cache_stats;
100
101\echo ''
102\echo '===== QUERY 6: Hypertable Information ====='
103SELECT
104 hypertable_schema,
105 hypertable_name,
106 owner,
107 num_dimensions,
108 num_chunks,
109 compression_enabled
110FROM timescaledb_information.hypertables
111WHERE hypertable_name LIKE 'quickdid%'
112ORDER BY hypertable_name;
113
114\echo ''
115\echo '===== QUERY 7: HTTP Error Rate by Endpoint ====='
116WITH status_counts AS (
117 SELECT
118 time_bucket('5 minutes', time) as period,
119 tags->>'path' as path,
120 CASE
121 WHEN (tags->>'status')::int >= 400 THEN 'error'
122 ELSE 'success'
123 END as status_category,
124 SUM(value) as request_count
125 FROM "quickdid.http.request.count"
126 WHERE time > NOW() - INTERVAL '1 hour'
127 GROUP BY period, path, status_category
128)
129SELECT
130 period,
131 path,
132 SUM(CASE WHEN status_category = 'error' THEN request_count ELSE 0 END) as error_count,
133 SUM(CASE WHEN status_category = 'success' THEN request_count ELSE 0 END) as success_count,
134 CASE
135 WHEN SUM(request_count) > 0 THEN
136 ROUND(100.0 * SUM(CASE WHEN status_category = 'error' THEN request_count ELSE 0 END) / SUM(request_count), 2)
137 ELSE 0
138 END as error_rate_percentage
139FROM status_counts
140GROUP BY period, path
141HAVING SUM(request_count) > 0
142ORDER BY period DESC, error_rate_percentage DESC;
143
144\echo ''
145\echo '===== TEST COMPLETED ====='