push the insert speed to max

[mche...@gmail.com]

hi. I am running AMD 3900x with 128GB ram and a nvme ssd. Now i can insert 1xk record per seconds, which is very fast. But how can I make is 10 times more? what hardware can do that?

thanks

Peter

[Andreas Reichel]

Greetings.

On Fri, 2024-01-12 at 00:17 -0800, mche...@gmail.com wrote:

hi. I am running AMD 3900x with 128GB ram and a nvme ssd. Now i can insert 1xk record per seconds, which is very fast. But how can I make is 10 times more? what hardware can do that?

1) Ensure that all Indexes and constraints are turned off

2) Reduce the commit size. As far as I can see you create one very large commit over all records. Instead, commit as per 1k or 4k records or so.

3) Ensure that your filesystem does not do COW or compression.

4) use RAID and ensure that there is ZERO waitIO or swapping

What exactly does "1xk" mean? 

If you are really serious about loading speed you will end up with Oracle Loader. Not that I am promoting this shit, but in reality it is the fastest way for pumping data into a DB. 

Best regards

Andreas

[Andreas Reichel]

Forgot one:

try multi threading, e. g. populating one prepared statement while another is executed/written.

Not guaranteed if this really will be faster though.

[mche...@gmail.com]

1xk mean i can insert 10-15 thousand records to h2 per second, thanks

[mche...@gmail.com]

1) Ensure that all Indexes and constraints are turned off

yes, faster

2) Reduce the commit size. As far as I can see you create one very large commit over all records. Instead, commit as per 1k or 4k records or so.

i tried 1k and 10k per commit, not much different, sometimes 1k is slower than 10k.

thanks Andreas

[Andreas Reichel]

Greetings!

How about by-passing JDBC/H2 and pushing the data into the MV Store directly?

This way you eliminate any possible bottleneck.

Next was to compare MV Store performance vs. other implementation (e .g. EHCache).

Next next was comparing against Postgres LOAD and or DuckDB read from Parquet. You will need to establish a kind of benchmark first before you can really say what is possible or shall be expected.

I don't know how well MVStore itself performs and where exactly any limitation may come from.

For very most of us, H2/MVStore is just good enough and I assume the most relevant use-case is to have a minimum deploy/maintenance all batteries included Jar DB -- not so much the raw performance.

Cheers

Andreas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/0d855987-55f3-45ea-bca3-4cf3390f9a08n%40googlegroups.com.

[Andreas Reichel]

This seems to be a useful and interesting link: https://commons.apache.org/proper/commons-jcs/JCSvsEHCache.html

I suggest you take it up and add MVStore and/or NitroCache to it for establishing a benchmark.

When you find MVStore competitive (enough) and find the achievable speed matches your needs, you/we could write a kind of "H2 Loader" pumping data directly into the MVStore. I am actually interested in that too because I face similar challenges of reading LARGE datasets into H2 databases.

One more thought: by CACHE design, you will need to decide about your priorities: a) FAST reading vs. b) FAST fetching. The fastest data-pump can easily result in the slowest data read and somehow you will may want to balance your expectations. (For me, reading is always more important than writing.)

Cheers

Andreas