Something went wrong. Try again.
QuickDID is a high-performance AT Protocol identity resolution service written in Rust. It provides handle-to-DID resolution with Redis-back
Something went wrong. Try again.
4.3 kB · 145 lines
SQL
at main
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145-- Test script to verify all metrics queries work correctly-- Run this after sending test metrics with send-metrics.sh
\echo '===== CHECKING AVAILABLE TABLES ====='SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_name LIKE 'quickdid%'ORDER BY table_name;
\echo ''\echo '===== CHECKING TABLE STRUCTURES ====='\echo 'Structure of quickdid.http.request.count table:'\d "quickdid.http.request.count"
\echo ''\echo 'Structure of quickdid.http.request.duration_ms table:'\d "quickdid.http.request.duration_ms"
\echo ''\echo '===== QUERY 1: Recent HTTP Request Counts ====='SELECT time, tags, tags->>'method' as method, tags->>'path' as path, tags->>'status' as status, valueFROM "quickdid.http.request.count"WHERE time > NOW() - INTERVAL '1 hour'ORDER BY time DESCLIMIT 10;
\echo ''\echo '===== QUERY 2: HTTP Request Duration Statistics by Endpoint ====='SELECT time_bucket('1 minute', time) AS minute, tags->>'method' as method, tags->>'path' as path, tags->>'status' as status, COUNT(*) as request_count, AVG(mean) as avg_duration_ms, MAX(p99) as p99_duration_ms, MIN(mean) as min_duration_msFROM "quickdid.http.request.duration_ms"WHERE time > NOW() - INTERVAL '1 hour' AND tags IS NOT NULLGROUP BY minute, tags->>'method', tags->>'path', tags->>'status'ORDER BY minute DESCLIMIT 10;
\echo ''\echo '===== QUERY 3: Rate Limiter Status Over Time ====='SELECT time, value as available_permitsFROM "quickdid.resolver.rate_limit.available_permits"WHERE time > NOW() - INTERVAL '1 hour'ORDER BY time DESCLIMIT 10;
\echo ''\echo '===== QUERY 4: Resolver Performance Comparison ====='SELECT tags->>'resolver' as resolver_type, COUNT(*) as sample_count, AVG(mean) as avg_resolution_time_ms, MAX(p99) as p99_resolution_time_ms, MIN(mean) as min_resolution_time_msFROM "quickdid.resolver.resolution_time"WHERE time > NOW() - INTERVAL '1 hour' AND tags->>'resolver' IS NOT NULLGROUP BY tags->>'resolver'ORDER BY avg_resolution_time_ms;
\echo ''\echo '===== QUERY 5: Cache Hit Rate Analysis ====='WITH cache_stats AS ( SELECT 'hits' as metric_type, SUM(value) as total_count FROM "quickdid.cache.hit.count" WHERE time > NOW() - INTERVAL '1 hour' UNION ALL SELECT 'misses' as metric_type, SUM(value) as total_count FROM "quickdid.cache.miss.count" WHERE time > NOW() - INTERVAL '1 hour')SELECT SUM(CASE WHEN metric_type = 'hits' THEN total_count ELSE 0 END) as total_hits, SUM(CASE WHEN metric_type = 'misses' THEN total_count ELSE 0 END) as total_misses, CASE WHEN SUM(total_count) > 0 THEN ROUND(100.0 * SUM(CASE WHEN metric_type = 'hits' THEN total_count ELSE 0 END) / SUM(total_count), 2) ELSE 0 END as hit_rate_percentageFROM cache_stats;
\echo ''\echo '===== QUERY 6: Hypertable Information ====='SELECT hypertable_schema, hypertable_name, owner, num_dimensions, num_chunks, compression_enabledFROM timescaledb_information.hypertablesWHERE hypertable_name LIKE 'quickdid%'ORDER BY hypertable_name;
\echo ''\echo '===== QUERY 7: HTTP Error Rate by Endpoint ====='WITH status_counts AS ( SELECT time_bucket('5 minutes', time) as period, tags->>'path' as path, CASE WHEN (tags->>'status')::int >= 400 THEN 'error' ELSE 'success' END as status_category, SUM(value) as request_count FROM "quickdid.http.request.count" WHERE time > NOW() - INTERVAL '1 hour' GROUP BY period, path, status_category)SELECT period, path, SUM(CASE WHEN status_category = 'error' THEN request_count ELSE 0 END) as error_count, SUM(CASE WHEN status_category = 'success' THEN request_count ELSE 0 END) as success_count, CASE WHEN SUM(request_count) > 0 THEN ROUND(100.0 * SUM(CASE WHEN status_category = 'error' THEN request_count ELSE 0 END) / SUM(request_count), 2) ELSE 0 END as error_rate_percentageFROM status_countsGROUP BY period, pathHAVING SUM(request_count) > 0ORDER BY period DESC, error_rate_percentage DESC;
\echo ''\echo '===== TEST COMPLETED ====='