How to Supercharge Elasticsearch for Billion‑Row Queries: Proven Optimization Techniques

Introduction

The data platform has evolved through three versions, encountering common challenges when handling petabyte‑scale data; this article shares a refined documentation set focused on Elasticsearch (ES) performance optimization.

Requirements

Business tables generate over a hundred million rows per day, partitioned by day, but queries must span months and retain up to a year of history while keeping only three months of raw data on high‑spec hardware.

Improvement goals:

Cross‑month queries and export of data older than one year.

Condition‑based queries returning results within seconds.

ES Retrieval Principles

3.1 Basic ES and Lucene Architecture

Understanding component fundamentals is essential for pinpointing bottlenecks.

Key concepts:

Cluster : a group of Nodes.

Node : a service unit within the cluster.

Index : a logical namespace that may contain multiple physical shards.

Type : classification within an index (limited to one type after ES 6.x).

Document : the basic indexable unit, typically a JSON object.

Shards : work units storing a subset of data; each shard is a Lucene instance.

Replicas : shard copies for data safety and query load distribution.

ES relies heavily on Lucene; optimizing data structures often means optimizing Lucene itself.

Lucene index files consist of dictionaries, inverted tables, forward files, and DocValues.

DocValues provide column‑oriented storage for fast sorting, faceting, and aggregations, but they consume resources; disabling unused DocValues can reduce overhead.

3.3 ES Index and Search Sharding

Data placement follows shard = hash(routing) % number_of_primary_shards. By default, routing uses the document ID (MurmurHash3). Supplying a consistent _routing value groups related data on the same shard, improving performance.

Optimization Cases

4.1 Index Performance

Batch writes (hundreds to thousands of records per batch).

Multi‑threaded ingestion, scaling thread count with node count.

Increase refresh_interval (e.g., set to -1) during bulk loads and manually trigger refresh after completion.

Allocate ~50% of system memory to Lucene file cache; use nodes with 64 GB+ RAM.

Prefer SSDs over HDDs for random I/O.

Use custom IDs aligned with HBase row keys to enable efficient deletes/updates.

Configure segment merge throttling (e.g., indices.store.throttle.max_bytes_per_sec: 200mb) and limit merge threads based on CPU cores.

4.2 Search Performance

Disable DocValues for fields that do not require sorting or aggregation.

Prefer keyword fields over numeric types for exact matches.

Turn off _source storage for fields not needed in search results.

Use filters or constant_score queries to bypass scoring when relevance is irrelevant.

Pagination strategies: from + size can become costly for deep pages (default index.max_result_window: 10000). search_after uses the last hit of the previous page for efficient deep pagination. scroll handles large result sets but requires managing scroll_id.

Combine timestamp and ID into a single long field to simplify sorting.

Allocate CPUs with 16 cores or more for sorting‑intensive workloads.

Set merge.policy.expunge_deletes_allowed: 0 to force deletion of marked records during merges.

{
  "mappings": {
    "data": {
      "dynamic": "false",
      "_source": { "includes": ["XXX"] },
      "properties": {
        "state": { "type": "keyword", "doc_values": false },
        "b": { "type": "long" }
      }
    }
  },
  "settings": { ... }
}

Performance Testing

Benchmarks were conducted to evaluate the impact of each change:

Single‑node test with 50 M–100 M records to assess point‑node capacity.

Cluster test scaling from 100 M to 3 B records, measuring disk I/O, memory, CPU, and network usage.

Random query combinations across data volumes to gauge response times.

SSD vs. HDD performance comparison.

Production Impact

The optimized platform now handles billions of records, returning 100‑row result sets within 3 seconds, with fast page navigation; further scaling can be achieved by adding nodes.

Author: mikevictor Link: https://www.cnblogs.com/mikevictor07/p/10006553.html