Diagnosing Intermittent N1QL Latency Spikes in Couchbase with Global Secondary Indexes

Recognizable condition

Users observe periodic spikes in the 99th‑percentile (p99) N1QL query latency, occasional query timeouts, and longer scan times while key‑value (KV) read/write latency remains normal. The spikes often line up with index updates, large background scans, or compaction cycles.

Cause / diagnostic table

Possible cause	What to look for in metrics or logs
Index fragmentation or memory pressure on index nodes	High indexer.memory_quota_used approaching quota, rising index_scan_time, increased indexer.memory_fragmentation.
Insufficient index replicas creating hot spots	One replica shows disproportionately high index_scan_count or index_scan_time while others are idle.
Stale statistics leading the planner to choose a full index scan	Query plan shows #operator = "IndexScan" with no predicates, cardinality estimates far from actual row count.
Data skew amplifying scan cost for a particular key range	Specific index keys exhibit vastly higher index_entry_count compared to peers.
Concurrent rebalance or compaction consuming I/O/CPU	Cluster logs show rebalance or compaction events overlapping latency spikes.

Ordered checks

1. Query Service metrics – From the Couchbase Web UI → Monitoring → Query, or via REST GET /pools/default/metrics?...&entity=query. Look for rising query_latency_p99 and increased active_requests.
2. Index Service metrics – UI → Monitoring → Index, or REST GET /pools/default/metrics?...&entity=index. Check indexer.memory_quota_used, index_scan_time, and per‑replica index_scan_count.
3. Review query plans – Run the suspect query with EXPLAIN in cbq or the Query Workbench. Note whether the plan uses an index scan, primary scan, or a covering index, and examine the cardinality estimates.
4. Check background activity – UI → Server Nodes → Active Tasks, or REST GET /pools/default/tasks. Look for ongoing rebalance, index_build, or compaction tasks that overlap the latency window.

Fixes tied to findings

• Rebuild or defragment the affected GSI – Schedule during low‑traffic windows: cbq -u Administrator -p -c http://:8091 --script="ALTER INDEX . ON BUILD;". Ensure the index node has sufficient free memory and disk I/O before starting.
• Add index replicas – Increase replica count to spread load: cbq ... --script="ALTER INDEX . ON WITH {\"num_replica\": 2};". Verify that the cluster has enough index service capacity (memory quota and CPU) for the extra replicas.
• Refresh statistics – Run cbq ... --script="ALTER INDEX . ON BUILD;" followed by cbq ... --script="CALL INDEX_STATS_RESET('', '');" or simply wait for the automatic stats refresh interval (default 30 s) and verify updated cardinalities in the explain output.
• Rewrite queries to use covering indexes or limit results – Add needed fields to the index definition or add LIMIT/OFFSET clauses to reduce scan size. Example: change SELECT * FROM `travel-sample`.inventory WHERE type='airline' to SELECT name, callsign FROM `travel-sample`.inventory WHERE type='airline' and create an index on (type, name, callsign).

Escalation criteria

• p99 latency stays above the service SLO for more than 15 minutes despite the above checks.
• An index node experiences OOM kills or crash loops (check /opt/couchbase/var/lib/couchbase/logs/indexer.log for OutOfMemoryError).
• Replication lag between index replicas exceeds a configurable threshold (watch indexer.replica_lag via REST).
• Query errors such as indexer_error: index corruption persist after a rebuild.

Verification

1. After applying a fix, compare Query Service latency histograms (p95, p99) before and after the change using the UI or the /pools/default/metrics endpoint.
2. Confirm the index health endpoint (GET /pools/default/buckets//indexes/) returns "state": "online" with no error fields and shows healthy replica states.
3. Re‑run the representative query with EXPLAIN and verify that the plan still uses the intended index and that cardinality estimates are closer to the actual row count (you can cross‑check with a SELECT COUNT(*) on the same predicates).

Example scenario

Suppose the query SELECT * FROM `travel-sample`.inventory WHERE type='airline' AND country='United States' intermittently spikes latency. An EXPLAIN shows a full index scan on def_inventory_type_country with a cardinality estimate of 2 rows while the actual count is 1500. The index node’s memory usage is at 85 % of its quota, and the replica scan count is uneven (replica 0: 1200 scans, replica 1: 30 scans).

Following the ordered checks:

1. Query Service metrics show rising p99 latency.
2. Index Service metrics reveal high memory usage and skewed scan distribution.
3. The explain plan confirms the planner chose the index but with stale stats.
4. No rebalance or compaction tasks are active.

Applied fixes:

• Added a second replica: ALTER INDEX `travel-sample`.def_inventory_type_country ON `travel-sample` WITH {\"num_replica\": 2};
• Refreshed statistics after a rebuild: ALTER INDEX ... BUILD; followed by a wait for the automatic stats refresh.
• Changed the query to request only needed fields and added a covering index: CREATE INDEX def_inventory_cover ON `travel-sample` (type, country, name, callsign) WHERE type='airline';

After the changes, latency histograms returned to baseline, the index reported "state": "online" with balanced replica scan counts, and the explain plan showed the covering index being used with accurate cardinality estimates.