Diagnosing a completely broken database is relatively straightforward. Diagnosing a subtle, transient performance regression buried within 100,000 queries per second is one of the hardest challenges in distributed systems engineering.

When your metrics dashboards show p99 latency spikes, but nodetool tpstats looks healthy and OS utilization is nominal, the problem is almost always the application’s query shapes.

Situation

Between 2023 and 2025, the 90-node Cassandra 4.1 cluster experienced intermittent p99 latency spikes that specifically impacted critical business workflows.

The symptoms were classic but infuriating: transient spikes where queries seemed to bypass indexes or trigger unbounded partition scans. However, because these sub-optimal queries were hidden within massive microservice traffic volumes, they were impossible to catch using standard sampling. On-call engineers were overwhelmed by terabytes of unstructured system.log files and vague metric correlations.

The Problem

Cassandra doesn’t have a “slow query log” that is easy to parse under heavy load without degrading performance.

To see exactly what the application is sending, you need to enable Full Query Logging (FQL). FQL uses Chronicle Queue to write a highly optimized binary log of every CQL request. However, generating this data creates two massive problems:

  1. Volume: At 100k QPS, FQL generates gigabytes of binary data per minute.
  2. Analysis: You have to dump the binary to text using fqltool, then manually search through millions of queries to find the culprit (e.g., an IN query scanning 50 partition keys).

We realized that Large Language Models (LLMs) are uniquely suited to synthesize this type of unstructured text anomaly. However, feeding raw database logs to a cloud LLM is a catastrophic security violation. How do you leverage AI reasoning without leaking customer PII or granting the AI dangerous operational access?

The Safe Diagnostic AI Pipeline

The pattern builds an automated Python diagnostic agent that acts as a secure airgap between the Cassandra cluster and the LLM.

The pipeline strictly enforces deterministic data collection, rigorous PII redaction, and a mandatory Database Administrator (DBA) verification gate.

flowchart TD
    subgraph "Production Boundary (Airgapped)"
        Cass(Cassandra Cluster) -->|fqltool dump| FQL[Full Query Logs]
        Cass -->|nodetool| Stats[JMX Metrics]
        
        FQL --> Agent(Python Diagnostic Agent)
        Stats --> Agent
        
        Agent --> Redact[AST/Regex Redaction Engine — Strip PII & Literals]
        Redact --> Bundle[Structured JSON/Markdown — Incident Bundle]
    end
    
    Bundle --> LLM(LLM Reasoning Engine)
    
    subgraph "Human Validation"
        LLM -->|Hypotheses & Validation Commands| DBA{DBA Verification Gate}
        DBA -->|Reject| Drop[Discard]
        DBA -->|Approve| Action[Execute Remediation]
    end

1. FQL Capture

When an alert fired, the Python agent ran nodetool enablefullquerylog for exactly 60 seconds, capturing a massive representative sample, and immediately disabled it to reclaim disk I/O. FQL is a low-overhead workload capture mechanism, but its operational impact must always be measured under your specific production workload.

2. The Redaction Engine

Before leaving the production boundary, the Python agent parsed the CQL statements and stripped all literals, replacing them with ?. SELECT * FROM users WHERE email = '[email protected]'; became SELECT * FROM users WHERE email = ?;. This guaranteed zero PII exposure.

3. LLM Hypothesis Generation

The sanitized FQL dump, along with a snapshot of nodetool tpstats and nodetool tablestats, was packaged into a structured prompt and sent to the LLM. The prompt explicitly instructed the LLM to act as a Senior Cassandra DBA, identify anomalous query shapes (like massive IN clauses or unbounded scans), and provide a ranked list of root-cause hypotheses alongside the deterministic commands required to verify them.

In Practice

During an incident, the output from the LLM pipeline looked like this:

Hypothesis 1 (80% Confidence): Application is executing SELECT statements with massive IN clauses on the partition key. Evidence: FQL sample shows SELECT * FROM orders WHERE user_id IN (?, ?, ..., ?) with up to 200 parameters. This forces the coordinator to fan out reads across up to 200 distinct partitions in a single request, causing the observed coordinator latency spikes. Verification Command: grep -o "IN (" sanitized_fql.txt | wc -l

By structuring the incident bundle, the LLM could instantly synthesize what would take an engineer hours of grepping. The DBA in the loop simply reviewed the LLM’s logic, ran the validation command, and pinpointed the offending microservice.

Where It Breaks

Design ChoiceTradeoffMitigation
Piping Raw Logs to LLMMassive security violation. You will leak PII, passwords, and sensitive data to a third-party model provider.Build a strict, deterministic redaction engine using AST parsing or regex to strip all literals before the LLM step.
LLM Auto-ExecutionAllowing an LLM to automatically execute remediation scripts (like dropping indexes or killing connections) based on its own hypotheses will cause an outage.The DBA Verification Gate is absolute. The AI suggests; the human verifies and types the command.
Leaving FQL OnForgetting to run nodetool disablefullquerylog will eventually fill the OS disk, crashing the node.The Python agent must wrap the FQL capture in a strict try/finally block that guarantees disabling the log.

Modern Cassandra Context: Cassandra 5.0 and Local SLMs

Cassandra 5.0 introduces expanded virtual table diagnostics, making it easier to query running tasks via CQL rather than JMX. Furthermore, the AI landscape has shifted. Instead of sending redacted logs to frontier models, engineering teams are increasingly deploying open-weight Small Language Models (SLMs) locally within their secure VPC boundaries. This entirely eliminates the PII exfiltration risk, allowing for even deeper automated log analysis without redacting query contexts.

What to Do Next

  • Problem: Transient query regressions are impossible to debug with standard metrics, but raw query logs are too massive to parse manually and too sensitive for cloud LLMs.
  • Solution: Capture 60-second FQL samples, deterministically redact all CQL literals, and use an LLM to synthesize the sanitized query shapes and metrics into actionable hypotheses.
  • Proof: This pipeline turns hours of manual grepping through FQL dumps into a short, reviewable list of ranked hypotheses, while strictly enforcing the DBA verification boundary.
  • Action: Test FQL locally. Run nodetool enablefullquerylog --path /tmp/fql, run some queries, stop it, and dump the binary with fqltool dump /tmp/fql. See the volume for yourself.