profiling-transaction-fingerprints

# Profiling Transaction Fingerprints Analyzes historical transaction performance patterns using aggregated SQL statistics to identify high-retry transactions, contention patterns, and commit latency issues. Uses `crdb_internal.transaction_statistics` for time-windowed analysis of retry behavior, commit latency, and statement composition - entirely via SQL without requiring DB Console access. **Complement to profiling-statement-fingerprints:** This skill analyzes transaction-level patterns (groups of statements with retry behavior); for statement-level optimization, see [profiling-statement-fingerprints](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/profiling-statement-fingerprints/SKILL.md). **Complement to triaging-live-sql-activity:** This skill analyzes historical transaction patterns; for immediate triage of currently active transactions, see [triaging-live-sql-activity](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/triaging-live-sql-activity/SKILL.md). ## When to Use This Skill - Identify transactions with high retry counts - Analyze commit latency trends for transaction fingerprints - Find transactions with high contention at transaction boundary - Understand statement composition of problematic transactions - Investigate transaction retry storms or abort patterns - SQL-only historical transaction analysis without DB Console access **For immediate incident response:** Use [triaging-live-sql-activity](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/triaging-live-sql-activity/SKILL.md) to triage currently active transactions and cancel runaway work. **For statement-level optimization:** Use [profiling-statement-fingerprints](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/profiling-statement-fingerprints/SKILL.md) to analyze individual query patterns. ## Prerequisites - SQL connection to CockroachDB cluster - `VIEWACTIVITY` or `VIEWACTIVITYREDACTED` cluster privilege for cluster-wide visibility - Same privilege requirements as profiling-statement-fingerprints - Understanding of transaction performance concepts - Transaction statistics collection enabled (default): `sql.stats.automatic_collection.enabled = true` **Check transaction stats collection:** ```sql SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled; -- Should return: true ``` See [triaging-live-sql-activity permissions reference](https://github.com/cockroachlabs/cockroachdb-skills/blob/main/skills/cockroachdb-observability-and-diagnostics/triaging-live-sql-activity/references/permissions.md) for RBAC setup (same privileges). ## Core Concepts ### Transaction Fingerprints vs Live Transactions **Transaction fingerprint:** Normalized transaction pattern grouping statements with parameterized constants. **Key differences:** - **Time scope:** Historical hourly buckets vs real-time current state - **Granularity:** Aggregated retry/commit stats vs individual transaction instances - **Relationship:** Transaction = collection of statement fingerprints ### Time-Series Bucketing **aggregated_ts:** Hourly UTC buckets (e.g., `2026-02-21 14:00:00` = 14:00-14:59 executions) **Data retention:** Capped by row count, not time. `sql.stats.persisted_rows.max` (default 1,000,000) bounds the persisted statement+transaction rows; older buckets are compacted once the cap is reached. Effective wall-clock window depends on workload diversity. **Best practice:** Always filter by time window: `WHERE aggregated_ts > now() - INTERVAL '24 hours'` ### Aggregated vs Sampled Metrics | Metric Category | JSON Path | Scope | Use Case | |-----------------|-----------|-------|----------| | **Aggregated** | `statistics.statistics.*` | All executions | Retries, commit latency, execution counts | | **Sampled** | `statistics.execution_statistics.*` | Probabilistic sample governed by `sql.txn_stats.sample_rate` (default 0.01) | Contention, network, memory/disk | **Critical:** Sampled metrics have `cnt` field showing sample size. Always check: ```sql WHERE (statistics->'execution_statistics'->>'cnt') IS NOT NULL ``` ### JSON Field Extraction CockroachDB stores transaction metadata and statistics as JSONB. Use these operators: **Operators:** - `->`: Extract JSON object/value (returns JSON) - `->>`: Extract as text (returns text) - `::TYPE`: Cast to specific type - `encode(fingerprint_id, 'hex')`: Convert binary fingerprint to hex string **Transaction-specific examples:** ```sql encode(fingerprint_id, 'hex') AS txn_fingerprint_id -- Hex encoding (statistics->'statistics'->>'maxRetries')::INT -- Max retry count (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 -- Retry latency (seconds) (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 -- Commit latency (seconds) (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 -- Service latency (seconds) metadata->'stmtFingerprintIDs' AS stmt_fingerprint_ids_json -- Statement composition ``` **Units:** - Latency fields: **seconds** (FLOAT8) - CPU/contention: **nanoseconds** (divide by 1e9 for seconds) - Memory/disk: **bytes** (consider / 1048576 for MB) See [JSON field reference](references/json-field-reference.md) for complete schema. ### Statement Composition **metadata.stmtFingerprintIDs:** JSONB array mapping transaction to constituent statements **Use case:** Understand which statements compose high-retry transactions **Cross-reference workflow:** Join transaction_statistics with statement_statistics on fingerprint IDs **Example pattern:** ```sql -- Extract statement fingerprint IDs from transaction metadata -> 'stmtFingerprintIDs' AS stmt_ids -- Use with jsonb_array_elements_text to expand and join jsonb_array_elements_text(metadata->'stmtFingerprintIDs') AS stmt_fingerprint_id ``` ## Core Diagnostic Queries ### Query 1: Top Transactions by Retries and Contention ```sql -- Identify transactions with high retry counts and contention SELECT encode(fingerprint_id, 'hex') AS txn_fingerprint_id, metadata->>'db' AS database, metadata->>'app' AS application, (statistics->'statistics'->>'cnt')::INT AS execution_count, (statistics->'statistics'->>'maxRetries')::INT AS max_retries, (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_seconds, (statistics->'execution_statistics'->'contentionTime'->>'mean')::FLOAT8 / 1e9 AS mean_contention_seconds, aggregated_ts FROM crdb_internal.transaction_statistics WHERE aggregated_ts > now() - INTERVAL '24 hours' AND (statistics->'statistics'->>'maxRetries')::INT > 0 ORDER BY (statistics->'statistics'->>'maxRetries')::INT DESC LIMIT 20; ``` **Key columns:** `max_retries` shows maximum retry count; `mean_retry_lat_seconds` shows time spent in retries; `mean_contention_seconds` shows lock wait time. **Interpretation:** High max_retries (>10) indicates transaction conflicts; correlate with contention to identify lock hotspots. ### Query 2: Statement Composition Analysis ```sql -- Extract statement fingerprints for high-retry transactions SELECT encode(t.fingerprint_id, 'hex') AS txn_fingerprint_id, t.metadata->>'app' AS application, (t.statistics->'statistics'->>'maxRetries')::INT AS max_retries, jsonb_array_length(t.metadata->'stmtFingerprintIDs') AS num_statements, t.metadata->'stmtFingerprintIDs' AS stmt_fingerprint_ids, t.aggregated_ts FROM crdb_internal.transaction_statistics t WHERE t.aggregated_ts > now() - INTERVAL '24 hours' AND (t.statistics->'statistics'->>'maxRetries')::INT > 10 AND t.metadata->'stmtFingerprintIDs' IS NOT NULL ORDER BY max_retries DESC LIMIT 20; ``` **Key columns:** `num_statements` shows transaction complexity; `stmt_fingerprint_ids` contains statement IDs for cross-reference with statement_statistics. **Use case:** Understand which statement combinations cause retries; use Query 7 to drill down to specific statements. ### Query 3: High Commit Latency Transactions ```sql -- Find transactions with slow commit latency SELECT encode(fingerprint_id, 'hex') AS txn_fingerprint_id, metadata->>'db' AS database, metadata->>'app' AS application, (statistics->'statistics'->>'cnt')::INT AS execution_count, (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 AS mean_commit_lat_seconds, (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS mean_service_lat_seconds, ROUND( ((statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 / NULLIF((statistics->'statistics'->'svcLat'->>'mean')::FLOAT8, 0)) * 100, 2 ) AS commit_pct_of_service_lat, aggregated_ts FROM crdb_internal.transaction_statistics WHERE aggregated_ts > now() - INTERVAL '24 hours' AND (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 > 0.1 -- > 100ms commit latency ORDER BY (statistics->'statistics'->'commitLat'->>'mean')::FLOAT8 DESC LIMIT 20; ``` **Key columns:** `mean_commit_lat_seconds` shows 2PC commit time; `commit_pct_of_service_lat` shows what percentage of total latency is commit overhead. **Interpretation:** High commit percentage (>20%) suggests distributed transaction overhead, replication delays, or cross-region writes. ### Query 4: Retry Rate by Application ```sql -- Analyze retry patterns by application SELECT metadata->>'app' AS application, metadata->>'db' AS database, COUNT(*) AS transaction_fingerprint_count, SUM((statistics->'statistics'->>'cnt')::INT) AS total_executions, AVG((statistics->'statistics'->>'maxRetries')::INT) AS avg_max_retries, MAX((statistics->'statistics'->>'maxRetries')::INT) AS overall_max_retries, AVG((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8) AS avg_retry_lat_seconds FROM crdb_internal.transaction_statistics WHERE aggregated_ts > now() - INTERVAL '24 hours' AND (statistics->'statistics'->>'maxRetries')::INT > 0 GROUP BY metadata->>'app', metadata->>'db' ORDER BY avg_max_retries DESC LIMIT 20; ``` **Use case:** Application-level health scorecard; identify which applications have the most problematic transaction patterns. **Customization:** Adjust time window to 7 days for trends; filter by specific database. ### Query 5: Transaction Resource Consumption ```sql -- Find transactions with high resource usage SELECT encode(fingerprint_id, 'hex') AS txn_fingerprint_id, metadata->>'app' AS application, (statistics->'statistics'->>'cnt')::INT AS execution_count, (statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576 AS mean_network_mb, (statistics->'execution_statistics'->'maxMemUsage'->>'mean')::FLOAT8 / 1048576 AS mean_mem_mb, (statistics->'execution_statistics'->'maxDiskUsage'->>'mean')::FLOAT8 / 1048576 AS mean_disk_mb, ROUND( ((statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 / 1048576) * (statistics->'statistics'->>'cnt')::INT, 2 ) AS estimated_total_network_mb, aggregated_ts FROM crdb_internal.transaction_statistics WHERE aggregated_ts > now() - INTERVAL '24 hours' AND (statistics->'execution_statistics'->>'cnt') IS NOT NULL AND (statistics->'execution_statistics'->'networkBytes'->>'mean')::FLOAT8 > 0 ORDER BY estimated_total_network_mb DESC LIMIT 20; ``` **Key columns:** `mean_network_mb` shows distributed transaction overhead; `mean_disk_mb` > 0 indicates memory spill. **Interpretation:** High network bytes suggest cross-region transactions or inefficient distribution; disk usage indicates memory pressure. ### Query 6: Retry Latency Decomposition ```sql -- Understand retry latency as percentage of service latency SELECT encode(fingerprint_id, 'hex') AS txn_fingerprint_id, metadata->>'app' AS application, (statistics->'statistics'->>'cnt')::INT AS execution_count, (statistics->'statistics'->>'maxRetries')::INT AS max_retries, (statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 AS mean_retry_lat_seconds, (statistics->'statistics'->'svcLat'->>'mean')::FLOAT8 AS mean_service_lat_seconds, ROUND( ((statistics->'statistics'->'retryLat'->>'mean')::FLOAT8 /