java - Indexing and Query Performance Degradation in GridDB Cloud with High Insert Load - Stack Overflow

admin管理员组

文章数量:1391981

Problem Statement:

I’m using GridDB Cloud to store time-series data from multiple IoT devices. The system ingests about 5,000 records per second while running frequent range queries on timestamps. However, after running smoothly for some time, queries start slowing down drastically—sometimes taking several seconds to execute.

Even more confusing, some queries on the same indexed column return results instantly, while others take ages. The only temporary fix I found is restarting GridDB, but I need a permanent solution.

Code Example - Table Schema & Data Insertion Each IoT device logs temperature and humidity readings every second:

// Define the container schema
ContainerInfo containerInfo = new ContainerInfo("sensor_data",
        Arrays.asList(
            new ColumnInfo("device_id", GSType.STRING),
            new ColumnInfo("timestamp", GSType.TIMESTAMP),
            new ColumnInfo("temperature", GSType.FLOAT),
            new ColumnInfo("humidity", GSType.FLOAT)
        ),
        ContainerType.TIME_SERIES
);

// Create the container (if it doesn't exist)
gridStore.putContainer(containerInfo);

Data ingestion happens at high speed using batch inserts:

// Insert multiple records in a batch
TimeSeries<?> ts = gridStore.getTimeSeries("sensor_data", containerInfo);
List<Row> batch = new ArrayList<>();

for (int i = 0; i < 5000; i++) {
    Row row = ts.createRow();
    row.setString(0, "device_123");
    row.setTimestamp(1, new Date());
    row.setFloat(2, 25.6f);
    row.setFloat(3, 60.3f);
    batch.add(row);
}

// Commit batch insert
ts.multiPut(batch);

Query Performance Issue

I'm running simple range queries on timestamps, but some of them take seconds to complete:

// Query temperature readings for the past hour
String query = "SELECT * FROM sensor_data WHERE timestamp BETWEEN TIMESTAMPADD(HOUR, -1, NOW()) AND NOW()";
Query<QueryResults<Row>> gridQuery = ts.query(query);
QueryResults<Row> results = gridQuery.fetch();

Strange Observations:

• At first, queries return results in milliseconds.
• After running continuously for a while, some queries slow down randomly to several seconds.
• Index is already created on timestamp, but sometimes GridDB does a full scan instead.
• Restarting GridDB Cloud temporarily fixes the issue.

Troubleshooting Attempts

• Confirmed indexing is enabled on timestamp

containerInfo.setIndex("timestamp", IndexType.DEFAULT);

• Tested with different query variations (same problem)
• Checked system logs - no warnings or errors
• Tried partitioning by device_id - no effect

Questions:

• Why is GridDB Cloud sometimes ignoring the index and performing a full scan?
• Is there a way to force index usage in GridDB queries?
• Could this be a memory caching issue causing index slowdowns over time?
• Would partitioning improve performance, and how should it be structured for high insert + high query workloads?

本文标签： javaIndexing and Query Performance Degradation in GridDB Cloud with High Insert LoadStack Overflow