GSI index partitioning changes in Couchbase Server 7.x: concurrent N1QL latency when partition count exceeds Index node cores

Answer to the Core Questions

1. Partition‑to‑core rule

• Each GSI partition is serviced by a dedicated scan thread.
• The Index node schedules these threads on the CPU cores allocated for scanning.
• To keep scan latency low, keep the number of partitions per node **≤ the number of cores** dedicated to indexing.
• For replica indexes, the thread count is multiplied by the replica count, so the safe ratio becomes partitions ≤ (cores / replicas).
• Memory‑optimized indexes use lighter scan threads than plasma; the same ratio may hold but the threshold can be slightly higher.

2. Latency pattern with and without excess partitions

• Unpartitioned (single‑partition) index: latency grows roughly linearly with concurrent SELECTs, no abrupt spikes.
• Partitioned index with partitions > cores: a sudden latency spike occurs once concurrent scans exceed the core count; after the spike, latency rises more slowly due to the cost of merging results from multiple partitions.

3. Query‑plan signals

• Head‑of‑Line Blocking: high scan_wait time, increasing index_scan_queue_depth, low aggregation time.
• Cross‑Partition Aggregation Overhead: rising aggregation_time, many partition_merge operators in the EXPLAIN output.

Practical Verification Steps

1. Identify the number of CPU cores allocated for index scanning on the node (you can find this in the node’s couchbase-cli server-info output under cpu_cores or via the Web Console).
2. Enable debug logging: curl -X POST http://:8091/setting/index -d 'index.debug=true'.
3. Run a controlled concurrent N1QL SELECT workload (e.g., 100 parallel queries) against the same bucket and index.
4. Vary the index partition count (1, 2, 4, 8, 16) on a node with a known core count (e.g., 8 cores) while keeping the query load constant.
5. Collect metrics via /stats or cbq:
• index_scan_latency
• index_scan_queue_depth
• aggregation_time
6. Use EXPLAIN SELECT … to capture the scan_wait and partition_merge operators for each configuration.
7. Correlate spikes in index_scan_latency with either rising scan_wait (blocking) or rising aggregation_time (overhead).

Missing Diagnostic Detail Needed

To fine‑tune the partition‑to‑core recommendation for your cluster, please provide the number of CPU cores that the Index node reserves for scanning. This detail determines the exact safe partition count threshold for your environment.