Monitoring & Metrics

Overview

SyndrDB collects 140+ metrics across query execution, index operations, transactions, WAL, compaction, sessions, and document operations. Metrics are available via SQL commands and an optional memory-mapped file exporter for integration with external monitoring tools.

All metrics use lock-free atomic counters for zero-overhead collection in hot paths.

Table of Contents

SHOW SERVER STATS

Syntax

-- Show all server metrics
SHOW SERVER STATS

-- Show metrics for a specific category
SHOW SERVER STATS FOR "category"

Available Categories

Category Description
hash_index Hash index operations (puts, gets, deletes, cache hits/misses, latencies)
btree_index B-Tree index operations (inserts, searches, range queries, node splits)
query Query execution metrics (total, full scans, index scans, joins, group bys)
query_latency Query latency histograms (lt_1ms, lt_10ms, lt_100ms, lt_1s, gte_1s)
query_plan_cache Plan cache hits, misses, evictions, invalidations, stale serves
subquery Subquery execution counts and strategy usage
transaction Transactions begun, committed, rolled back, aborted
wal WAL writes, flushes, bytes written, segment rotations, replay stats
compaction Compaction cycles, bytes read/written, duration histograms
session Active sessions, created, terminated, auth failures
document Document inserts, updates, deletes, reads
ghost_cleanup Ghost record cleanup cycles and removal counts
mvcc_gc MVCC garbage collection cycles, versions removed/preserved
datetime DateTime parsing attempts, successes, errors
memory Memory limit exceeded counts, projected memory tracking

Examples

-- Check query performance
SHOW SERVER STATS FOR "query_latency"
-- Returns: query_latency_lt_1ms: 15234, query_latency_lt_10ms: 8721, ...

-- Check index effectiveness
SHOW SERVER STATS FOR "hash_index"
-- Returns: hash_index_cache_hits: 45000, hash_index_cache_misses: 120, ...

-- Monitor WAL health
SHOW SERVER STATS FOR "wal"
-- Returns: wal_writes_total: 12500, wal_flushes_total: 890, ...

SHOW CACHE STATS

SHOW CACHE STATS

Returns plan cache statistics including hits, misses, evictions, invalidations, stale serves, and a calculated hit rate percentage.

Metric Categories

Query Execution

Metric Name Type Description
query_executions_total Counter Total queries executed
query_plan_cache_hits Counter Plan cache hits
query_plan_cache_misses Counter Plan cache misses
query_timeouts_total Counter Queries that timed out
query_full_table_scans Counter Full scan queries
query_index_scans Counter Index-assisted queries
query_joins_total Counter JOIN operations
query_group_bys_total Counter GROUP BY operations
query_latency_lt_1ms Histogram Queries completing in under 1ms
query_latency_lt_10ms Histogram Queries completing in under 10ms
query_latency_lt_100ms Histogram Queries completing in under 100ms
query_latency_lt_1s Histogram Queries completing in under 1s
query_latency_gte_1s Histogram Queries completing in 1s or more

Index Operations

Metric Name Type Description
hash_index_puts_total Counter Hash index put operations
hash_index_gets_total Counter Hash index get operations
hash_index_deletes_total Counter Hash index delete operations
hash_index_cache_hits Counter MemTable cache hits
hash_index_cache_misses Counter MemTable cache misses
btree_index_inserts Counter B-Tree insert operations
btree_index_searches Counter B-Tree search operations
btree_index_range_queries Counter B-Tree range query operations
btree_index_node_splits Counter B-Tree node split events
btree_index_node_merges Counter B-Tree node merge events

Transaction

Metric Name Type Description
transactions_begun Counter Transactions started
transactions_committed Counter Transactions successfully committed
transactions_rolled_back Counter Transactions rolled back
transactions_aborted Counter Transactions aborted due to conflict

WAL

Metric Name Type Description
wal_writes_total Counter Total WAL write operations
wal_flushes_total Counter Total WAL flush (fsync) operations
wal_bytes_written Counter Total bytes written to WAL
wal_segment_rotations Counter WAL file rotation events
wal_replay_entries_total Counter WAL entries replayed during crash recovery
wal_replay_errors_total Counter Errors encountered during WAL replay

Document Operations

Metric Name Type Description
document_inserts_total Counter Documents inserted
document_updates_total Counter Documents updated
document_deletes_total Counter Documents deleted
document_reads_total Counter Documents read

Session

Metric Name Type Description
sessions_active Gauge Current number of active sessions
sessions_created Counter Total sessions created
sessions_terminated Counter Total sessions terminated
session_auth_failures Counter Authentication failures

Per-Database & Per-Bundle Metrics

Metrics are also collected at per-database and per-bundle granularity. These are lazily initialized on first access.

Per-Database Metrics

Metric Name Description
bundles_count Number of bundles in the database
documents_count Total documents across all bundles
total_size_bytes Total storage size in bytes
queries_executed Queries executed against this database
transactions_committed Transactions committed in this database
transactions_rolled_back Transactions rolled back in this database

Per-Bundle Metrics

Metric Name Description
documents_inserted Documents inserted into this bundle
documents_updated Documents updated in this bundle
documents_deleted Documents deleted from this bundle
documents_read Documents read from this bundle
current_doc_count Current document count
total_size_bytes Bundle storage size in bytes
indexes_count Number of indexes on this bundle
full_scans Full table scans against this bundle
index_scans Index-assisted scans against this bundle
cache_hits Page cache hits for this bundle
cache_misses Page cache misses for this bundle
avg_doc_size_bytes Average document size in bytes
max_doc_size_bytes Maximum document size in bytes
min_doc_size_bytes Minimum document size in bytes

Memory-Mapped Exporter

For integration with external monitoring tools (Prometheus, DataDog, etc.), SyndrDB can export metrics via a memory-mapped file.

How It Works

  • A background goroutine serializes all metrics every 1 second
  • Writes to a temp file, then atomically renames it
  • External tools can mmap and read without a database connection

File Format (Binary, Little-Endian)

[version: 4 bytes] [timestamp: 8 bytes] [count: 8 bytes]
For each metric:
  [key_length: 4 bytes] [key: N bytes (UTF-8)] [value: 8 bytes (uint64)]

Default path: /tmp/syndrdb_metrics.mmap

A companion reader tool is provided at src/cmd/metrics_reader/ for debugging and validation.

Configuration

Setting YAML Key Default Description
ExportRealtimeMetrics export_realtime_metrics false Enable memory-mapped metrics export
MetricsEnabled metrics_enabled true Enable metrics collection
MetricsPort metrics_port 9090 HTTP port for metrics endpoint
EnableQueryMonitoring enable_query_monitoring true Enable query metrics
MaxMetricsRetained — 100,000 Maximum metrics entries retained in memory
MetricsPurgeInterval — 5 minutes Interval between purges of old metrics
MetricsRetentionDuration — 30 minutes How long metrics are retained before purge

Best Practices

Do

  • Use SHOW SERVER STATS FOR "query_latency" to identify slow query patterns
  • Monitor session_auth_failures for brute-force detection
  • Check query_plan_cache hit rate — below 80% may indicate too many unique queries
  • Enable the mmap exporter for production monitoring dashboards
  • Compare query_full_table_scans vs query_index_scans to evaluate index coverage

Don't

  • Don't disable metrics in production — the atomic counter overhead is negligible
  • Don't poll SHOW SERVER STATS at high frequency from application code — use the mmap exporter instead
  • Don't ignore rising wal_replay_errors_total values — they indicate potential data corruption risks

Last updated: March 2026