Question on Performance

[geraldineoo]

Hi All,

We're doing a trial run with Oracle NoSQL.. and found that as the table grows bigger, the performance degrades and inserts become slower and slower. No errors. I'm really new to this - so not sure what to check or how we can improve the performance. We have some 2bil rows in the table

thanks

[dario-vega]

Thanks for reaching out — if you can reply with the following, it’ll be much easier to pinpoint the bottleneck:

• What you observed over time:

  • At the beginning, what insert latency/throughput did you see (ms per put or rows/sec)?
  • After how many rows (or how many hours/days) did it start to degrade?
  • How bad did it get (e.g., 2x slower, 10x slower), and is it steady or in spikes?

• Ingest pattern / client behavior:

  • Current rows/sec target and average achieved throughput
  • writer threads/connections and whether writes are parallel
  • Batch size (single-row writes vs multi-row/batched writes)
  • Any read-before-write / existence checks / updates vs pure inserts
  • Driver/SDK + version and language/runtime

• Data model and distribution:

  • Table DDL + primary key/shard key pattern (are keys sequential/time-based?)
  • Secondary indexes (how many, and what fields)

• Deployment and capacity:

  • CE vs EE and exact NoSQL version
  • KVStore topology (# storage nodes, # shards/rep groups, repl factor)
  • Hardware details (CPU/RAM/disk type & IOPS, network)

• What the system looks like when it slows down:

  • Any node pegged on CPU, heavy GC, disk latency/IOPS saturation, or low disk space
  • Any warnings in logs (even if the client sees no errors)

[geraldineoo]

Hello Dario,
Thank you - We're completely new to this - so the setup may not be ideal. Please do share if you have any suggestions.

Oracle NoSQL: CE 25.1.13
3 servers (same specs for each server)
- 250GB Ram
- 32 CPU
- 3 X 1.7TB SSD disks

Topology:
store=Store numPartitions=20 sequence=36
zn: id=zn1 name=dms00 repFactor=3 type=PRIMARY allowArbiters=false masterAffinity=false

sn=[sn1] zn:[id=zn1 name=dms00] donald.fppoc.pcc.lan:5000 capacity=3 RUNNING
[rg1-rn1] RUNNING
single-op avg latency=0 ms multi-op avg latency=0 ms
[rg2-rn1] RUNNING
single-op avg latency=0 ms multi-op avg latency=0 ms
[rg3-rn1] RUNNING
single-op avg latency=0 ms multi-op avg latency=0 ms
sn=[sn2] zn:[id=zn1 name=dms00] goofy.fppoc.pcc.lan:5000 capacity=3 RUNNING
[rg1-rn2] RUNNING
single-op avg latency=0 ms multi-op avg latency=0 ms
[rg2-rn2] RUNNING
single-op avg latency=0 ms multi-op avg latency=0 ms
[rg3-rn2] RUNNING
single-op avg latency=0 ms multi-op avg latency=0 ms
sn=[sn3] zn:[id=zn1 name=dms00] mickey.fppoc.pcc.lan:5000 capacity=3 RUNNING
[rg1-rn3] RUNNING
single-op avg latency=0 ms multi-op avg latency=0 ms
[rg2-rn3] RUNNING
single-op avg latency=0 ms multi-op avg latency=0 ms
[rg3-rn3] RUNNING
single-op avg latency=0 ms multi-op avg latency=0 ms

numShards=3
shard=[rg1] num partitions=7
[rg1-rn1] sn=sn1
[rg1-rn2] sn=sn2
[rg1-rn3] sn=sn3
shard=[rg2] num partitions=7
[rg2-rn1] sn=sn1
[rg2-rn2] sn=sn2
[rg2-rn3] sn=sn3
shard=[rg3] num partitions=6
[rg3-rn1] sn=sn1
[rg3-rn2] sn=sn2
[rg3-rn3] sn=sn3

Workload Description
Operation type: Insert‑only workload
Pattern: Continuous bulk inserts
Duration: Long‑running ingestion until system instability
Dataset size: From initial empty state up to ~2+ billion records

Testing for migration of data from dynamoDB to oracle NoSQL.
Expect to have very heavy inserts to setup, and eventually daily inserts of
several mil rows.

All inserts are to 1 single table only.

Table structure:-
CREATE TABLE IF NOT EXISTS fp_ns:fingerprint (
key STRING,
version INTEGER,
loadingTime LONG,
fp BINARY,
jobId INTEGER,
printingMatrix BINARY,
roilIndex INTEGER,
source STRING,
trigIndex INTEGER,

PRIMARY KEY (key)
);

No additional indexes.

Inserts made with sql.jar with COMMIT_SYNC

Initial insert rate is relatively high (≈50–60k inserts/sec).
Performance gradually declines over time and does not stabilize.
At the tail end of the test (after some 20+ hrs ) with ~2 billion rows in the table the inserts falls to approximately 10k inserts/sec.

I did not see any errors in the logs.

Write performance is good and stable throughout.

Thanks

[dario-vega]

After reviewing your response, my first recommendation is to increase the number of partitions—your current count is quite low. As a baseline, we generally recommend a minimum of 20 partitions per shard. With ~2 billion records, you should be closer to ~100 partitions per shard. For the version, please use the latest release available: CE 25.3.21.

I also recommend reviewing the Initial Capacity Planning guidance.

A couple of clarifying questions:

• How are you loading the data using sql.jar ?
• Is your target deployment on-premises or cloud?
• For the DynamoDB side, which language/SDK are you using ?

[geraldineoo]

Hello,

Thanks, i tried the initial planning capacity - seems we'll need 9 machines to cater to the estimated 4B rows.

How are you loading the data using sql.jar ?

using java "Table SDK" (bulk inserts with streams)
Is your target deployment on-premises or cloud?
on-prem, with the client running collocated in one node
For the DynamoDB side, which language/SDK are you using ?
Java 17+, with stock dynamo driver

Thank you for your time and advice :) Will go back to more testing.