Query Analytics and Slow Query Logging
HeliosDB Lite shows developers where query time goes without an external APM agent: SET helios.trace_queries = on records per-phase timing for each statement of the session, SHOW helios.trace_report summarises parse, plan, optimize and execute percentiles, the bottleneck phase and the slowest queries, and EXPLAIN (TRACE) breaks down a single query, so slow statements surface during development and load tests.
Problem Being Solved
Core Problem Statement
Application performance degradation from database queries goes undetected until production incidents occur, because developers lack real-time visibility into query execution times, patterns, and resource consumption. Traditional APM solutions require separate agents, network instrumentation, and monthly SaaS fees, creating friction that prevents teams from instrumenting development and staging environments. By the time slow queries reach production, customer impact is already occurring, and root cause analysis requires expensive forensic investigation.
Who Suffers Most
Enterprise DevOps Teams (500-5K employees): Mandated to instrument all applications with APM; database agent deployment complex (network proxies, SSL cert management); 6-month procurement cycle for new APM tools; developers work around monitoring in dev environments (too slow); production-only monitoring means bugs ship to customers.
HeliosDB Lite Solution
Architecture Overview
┌────────────────────────────────────────────────────────────────┐
│ Application Process │
│ ┌──────────────────────────────────────────────────────────┐ │
│ │ Application Code (Any Language) │ │
│ │ - HTTP request handling │ │
│ │ - Business logic │ │
│ │ - ORM (Hibernate, EF, SQLAlchemy) │ │
│ └────────────────────────┬─────────────────────────────────┘ │
│ │ Database queries │
│ ▼ │
│ ┌──────────────────────────────────────────────────────────┐ │
│ │ HeliosDB-Lite Query Engine │ │
│ │ ┌────────────────────────────────────────────────────┐ │ │
│ │ │ Query Interceptor (HeliosProxy) │ │ │
│ │ │ - Captures query text │ │ │
│ │ │ - Records start timestamp (high-precision) │ │ │
│ │ │ - Extracts call stack (optional) │ │ │
│ │ └────────────────────┬───────────────────────────────┘ │ │
│ │ ▼ │ │
│ │ ┌────────────────────────────────────────────────────┐ │ │
│ │ │ Query Executor │ │ │
│ │ │ - Parse SQL │ │ │
│ │ │ - Generate execution plan │ │ │
│ │ │ - Execute query │ │ │
│ │ │ - Return results │ │ │
│ │ └────────────────────┬───────────────────────────────┘ │ │
│ │ ▼ │ │
│ │ ┌────────────────────────────────────────────────────┐ │ │
│ │ │ Query Analytics Engine │ │ │
│ │ │ - Calculate execution time │ │ │
│ │ │ - Update statistics (P50, P95, P99) │ │ │
│ │ │ - Detect slow queries (>threshold) │ │ │
│ │ │ - Identify N+1 patterns (>10 similar queries) │ │ │
│ │ │ - Check for missing indexes (full table scans) │ │ │
│ │ └────────────────────┬───────────────────────────────┘ │ │
│ │ ▼ │ │
│ │ ┌────────────────────────────────────────────────────┐ │ │
│ │ │ Metrics Storage (SQLite tables) │ │ │
│ │ │ - query_log (raw queries) │ │ │
│ │ │ - query_stats (aggregated metrics) │ │ │
│ │ │ - slow_queries (>threshold) │ │ │
│ │ │ - optimization_hints (recommendations) │ │ │
│ │ └────────────────────┬───────────────────────────────┘ │ │
│ └───────────────────────┼─────────────────────────────────┘ │
│ │ │
│ ┌───────────────────────────────────────────────────────────┐ │
│ │ Analytics Dashboard (Built-In) │ │
│ │ ┌─────────────────────────────────────────────────────┐ │ │
│ │ │ Web UI (localhost:9091/dashboard) │ │ │
│ │ │ - Real-time query stream │ │ │
│ │ │ - Latency percentiles (P50, P95, P99) │ │ │
│ │ │ - Top 10 slowest queries │ │ │
│ │ │ - N+1 query warnings │ │ │
│ │ │ - Index recommendations │ │ │
│ │ │ - Query plan visualizer │ │ │
│ │ └─────────────────────────────────────────────────────┘ │ │
│ └───────────────────────────────────────────────────────────┘ │
│ │
│ ┌───────────────────────────────────────────────────────────┐ │
│ │ Prometheus Exporter (Optional) │ │
│ │ - /metrics endpoint (port 9091) │ │
│ │ - helios_query_duration_seconds (histogram) │ │
│ │ - helios_queries_total (counter) │ │
│ │ - helios_slow_queries_total (counter) │ │
│ │ - helios_cache_hit_rate (gauge) │ │
│ └───────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────────┘
Concrete Examples with Code, Config & Architecture
Example 1: Embedded Configuration with Query Analytics
HeliosDB Lite Configuration (helios_analytics.toml):
[database]
type = "embedded"
path = "./app_data.db"
mode = "readwrite-create"
page_size = 4096
cache_size_mb = 512
wal_mode = true
[query_analytics]
enabled = true
log_all_queries = true
log_slow_queries_only = false
slow_query_threshold_ms = 100
detect_n_plus_one = true
n_plus_one_threshold = 10
detect_missing_indexes = true
full_scan_threshold_rows = 1000 # Flag full scans on tables >1K rows
[query_analytics.storage]
# Store metrics in separate database to avoid performance impact
metrics_db_path = "./metrics.db"
retention_days = 30
aggregate_interval_seconds = 60
max_query_log_size_mb = 500
[query_analytics.dashboard]
enabled = true
listen_address = "127.0.0.1"
listen_port = 9091
auth_enabled = false # Enable with username/password in production
[query_analytics.alerts]
enabled = true
alert_on_slow_query = true
alert_on_n_plus_one = true
alert_on_missing_index = true
# Alert destinations
[query_analytics.alerts.destinations]
log_file = "./alerts.log"
webhook_url = "https://hooks.slack.com/services/YOUR/WEBHOOK"
email = "devops@example.com"
[prometheus]
enabled = true
metrics_path = "/metrics"
metrics_port = 9091
# Custom metric labels
[prometheus.labels]
environment = "production"
application = "myapp"
version = "1.0.0"
Example 2: Python Application with Query Analytics
Testing the N+1 Detection:
# Test N+1 pattern (BAD)
$ curl http://localhost:5000/api/users-with-orders-bad
# Server output: N+1 query pattern! Duration: 2.850s
# Test optimized query (GOOD)
$ curl http://localhost:5000/api/users-with-orders-good
# Server output: Optimized query! Duration: 0.045s
# Get analytics summary
$ curl http://localhost:5000/api/analytics/summary
{
"total_queries": 1003,
"slow_queries": 1000,
"n_plus_one_detected": 1,
"avg_query_time_ms": 2.75,
"p95_query_time_ms": 3.2,
"p99_query_time_ms": 4.8,
"cache_hit_rate": 0.873
}
# Get optimization hints
$ curl http://localhost:5000/api/analytics/optimization-hints
[
{
"query_text": "SELECT id, order_date, total FROM orders WHERE user_id = ?",
"hint_type": "n_plus_one",
"recommendation": "Detected N+1 query pattern: 1000 similar queries. Consider using a JOIN or batch query.",
},
{
"query_text": "SELECT id, order_date, total FROM orders WHERE user_id = ?",
"hint_type": "missing_index",
"recommendation": "CREATE INDEX idx_orders_user_id ON orders(user_id);",
}
]
Example 3: Prometheus Integration & Grafana Dashboard
Prometheus Configuration (prometheus.yml):
global:
scrape_interval: 15s
evaluation_interval: 15s
scrape_configs:
- job_name: 'heliosdb-lite'
static_configs:
- targets: ['localhost:9091']
labels:
environment: 'production'
application: 'myapp'
Grafana Dashboard JSON (excerpt):
{
"dashboard": {
"title": "HeliosDB-Lite Query Analytics",
"panels": [
{
"title": "Query Rate (QPS)",
"type": "graph",
"targets": [
{
"expr": "rate(helios_queries_total[1m])",
"legendFormat": "{{query_type}}"
}
]
},
{
"title": "P95 Query Latency",
"type": "graph",
"targets": [
{
"expr": "histogram_quantile(0.95, rate(helios_query_duration_seconds_bucket[5m]))",
"legendFormat": "P95"
}
]
},
{
"title": "Slow Queries per Minute",
"type": "graph",
"targets": [
{
"expr": "rate(helios_slow_queries_total[1m])",
"legendFormat": "Slow Queries"
}
]
},
{
"title": "Cache Hit Rate",
"type": "gauge",
"targets": [
{
"expr": "helios_cache_hit_rate",
"legendFormat": "Hit Rate"
}
]
}
]
}
}
Prometheus Metrics Exported:
# HELP helios_queries_total Total number of queries executed
# TYPE helios_queries_total counter
helios_queries_total{query_type="SELECT",environment="production"} 45823
helios_queries_total{query_type="INSERT",environment="production"} 3421
helios_queries_total{query_type="UPDATE",environment="production"} 1832
helios_queries_total{query_type="DELETE",environment="production"} 234
# HELP helios_query_duration_seconds Query execution time histogram
# TYPE helios_query_duration_seconds histogram
helios_query_duration_seconds_bucket{le="0.001"} 12453
helios_query_duration_seconds_bucket{le="0.005"} 38921
helios_query_duration_seconds_bucket{le="0.01"} 43234
helios_query_duration_seconds_bucket{le="0.05"} 48234
helios_query_duration_seconds_bucket{le="0.1"} 49832
helios_query_duration_seconds_bucket{le="0.5"} 50123
helios_query_duration_seconds_bucket{le="+Inf"} 51310
helios_query_duration_seconds_sum 142.34
helios_query_duration_seconds_count 51310
# TYPE helios_slow_queries_total counter
helios_slow_queries_total{environment="production"} 1478
# HELP helios_cache_hit_rate Cache hit rate (0.0-1.0)
# TYPE helios_cache_hit_rate gauge
helios_cache_hit_rate{environment="production"} 0.873
# HELP helios_n_plus_one_detected N+1 query patterns detected
# TYPE helios_n_plus_one_detected counter
helios_n_plus_one_detected{environment="production"} 3
Adoption Strategy
Phase 1: Enable in Development (Week 1)
- Add
analytics_enabled=trueto config - Access dashboard at localhost:9091
- Fix N+1 queries before code review
Phase 2: Staging Deployment (Week 2)
- Deploy with analytics enabled
- Run load tests
- Identify missing indexes
Phase 3: Production Rollout (Week 3-4)
- Enable in production with alerts
- Monitor Prometheus metrics
- Optimize slow queries proactively
Performance depends on workload and hardware. Published figures are measured on representative fixtures; see the benchmarks page and reproduce them on your own workload.