Re: [h2] push the insert speed to max

[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.

On Fri, 2024-01-12 at 15:38 +0700, Andreas Reichel wrote:
> 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

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/079a787ee98902bf5847d46b5fc0824546173212.camel%40manticore-projects.com.

Re: [h2] push the insert speed to max

[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

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/6a9dc4688445b554696767c00c68f1f98672ed1c.camel%40manticore-projects.com.

[h2] 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

-- 
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit 
https://groups.google.com/d/msgid/h2-database/31c66241-caa0-4474-8f5f-5ac7deeac906n%40googlegroups.com.

Re: [h2] why insert many record to in-memory database is same speed as embedded-mode

[mche...@gmail.com]

thanks all

On Wednesday 10 January 2024 at 09:38:59 UTC+8 Andrei Tokar wrote:

> Apparently, you assumption about "In-mem should be much faster" is not 
> correct.
> It just makes me wonder, what exactly is the problem at hand? Is that the 
> disk-based database is too fast?
> Or there is an expectation of some "ludicrous speed" 
>  for 
> in-memory mode?  8-)
> And if speed is not ludicrous enough - compare it with other databases out 
> there. I am genuinely interested to see some results posted here.
> Bottleneck most likely is memory manipulation, related to transactions 
> management and non-blocking B-tree access (multi-version store).  Disk 
> writes are done by parallel background threads, and it looks like, they are 
> pretty much free (in this use case).
> Some significant short-cuts can be taken under assumption of a 
> single-client use (non-transactional, non-concurrent), but that is the 
> topic for a wish list.
>
> On Tuesday, January 9, 2024 at 8:26:58 AM UTC-5 mche...@gmail.com wrote:
>
>> no, my program use 20 mins keep inserting to H2 (embed + in memory), both 
>> speed become stable and nearly the same.
>>
>> On Tuesday 9 January 2024 at 01:30:44 UTC+8 Andreas Reichel wrote:
>>
>>> Because its cached and data are written to disk only eventually when the 
>>> cache is full?
>>>
>>> On Mon, 2024-01-08 at 09:20 -0800, mche...@gmail.com wrote:
>>>
>>> hi.
>>>why insert many record to in-memory database is same speed as 
>>> embedded-mode. In-mem should be much faster, right?
>>>
>>>
>>> import java.io.FileNotFoundException;
>>> import java.io.IOException;
>>> import java.sql.Connection;
>>> import java.sql.DriverManager;
>>> import java.sql.PreparedStatement;
>>> import java.sql.SQLException;
>>> import java.sql.Statement;
>>> import me.tongfei.progressbar.ProgressBar;
>>> import org.junit.Test;
>>>
>>> /**
>>>  *
>>>  * @author peter
>>>  */
>>> public class TestH2InsertSpeed {
>>>
>>> @Test
>>> public void test() throws FileNotFoundException, IOException, 
>>> SQLException {
>>>
>>> Connection conn = DriverManager.getConnection("jdbc:h2:./test;CACHE_SIZE=
>>> 13107200 <310-7200>;PAGE_SIZE=10240;CACHE_TYPE=SOFT_LRU;", "sa", "");
>>> // Connection conn = 
>>> DriverManager.getConnection("jdbc:h2:mem:test;CACHE_SIZE=13107200 
>>> <310-7200>;PAGE_SIZE=10240;CACHE_TYPE=SOFT_LRU;");
>>> Statement stmt2 = conn.createStatement();
>>> stmt2.execute("drop table if exists qemu;");
>>> stmt2.execute("""
>>>   CREATE TABLE "PUBLIC"."QEMU"
>>>   (
>>>  "ID" integer auto_increment PRIMARY KEY NOT NULL,
>>>  "SEQUENCE" bigint,
>>>  "DATE" timestamp,
>>>  "COMPUTER" varchar(50),
>>>  "INTERRUPT" boolean,
>>>  "INTERRUPT_CAUSE" bigint,
>>>  "INTERRUPT_DESC" varchar(30),
>>>  "PC" bigint NOT NULL,
>>>  "MHARTID" bigint NOT NULL,
>>>  "MSTATUS" bigint NOT NULL,
>>>  "HSTATUS" bigint NOT NULL,
>>>  "VSSTATUS" bigint NOT NULL,
>>>  "MIP" bigint NOT NULL,
>>>  "MIE" bigint NOT NULL,
>>>  "MIDELEG" bigint NOT NULL,
>>>  "HIDELEG" bigint NOT NULL,
>>>  "MEDELEG" bigint NOT NULL,
>>>  "HEDELEG" bigint NOT NULL,
>>>  "MTVEC" bigint NOT NULL,
>>>  "STVEC" bigint NOT NULL,
>>>  "VSTVEC" bigint NOT NULL,
>>>  "MEPC" bigint NOT NULL,
>>>  "SEPC" bigint NOT NULL,
>>>  "VSEPC" bigint NOT NULL,
>>>  "MCAUSE" bigint NOT NULL,
>>>  "SCAUSE" bigint NOT NULL,
>>>  "VSCAUSE" bigint NOT NULL,
>>>  "MTVAL" bigint NOT NULL,
>>>  "STVAL" bigint NOT NULL,
>>>  "HTVAL" bigint NOT NULL,
>>>  "MTVAL2" bigint NOT NULL,
>>>  "MSCRATCH" bigint NOT NULL,
>>>  "SSCRATCH" bigint NOT NULL,
>>>  "SATP" bigint NOT NULL,
>>>  "X0_ZERO" bigint NOT NULL,
>>>  "X1_RA" bigint NOT NULL,
>>>  "X2_SP" bigint NOT NULL,
>>>  "X3_GP" bigint NOT NULL,
>>>  "X4_TP" bigint NOT NULL,
>>>  "X5_T0" bigint NOT NULL,
>>>  "X6_T1" bigint NOT NULL,
>>>  "X7_T2" bigint NOT NULL,
>>>  "X8_S0" bigint NOT NULL,
>>>  "X9_S1" bigint NOT NULL,
>>>  "X10_A0" bigint NOT NULL,
>>>  "X11_A1" bigint NOT NULL,
>>>  "X12_A2" bigint NOT NULL,
>>>  "X13_A3" bigint NOT NULL,
>>>  "X14_A4" bigint NOT NULL,
>>>  "X15_A5" bigint NOT NULL,
>>>  "X16_A6" bigint NOT NULL,
>>>  "X17_A7" bigint NOT NULL,
>>>  "X18_S2" bigint NOT NULL,
>>>  "X19_S3" bigint NOT NULL,
>>>  "X20_S4" bigint NOT NULL,
>>>  "X21_S5" bigint NOT NULL,
>>>  "X22_S6" bigint NOT NULL,
>>>  "X23_S7" bigint NOT NULL,
>>>  "X24_S8" bigint NOT NULL,
>>>  "X25_S9" bigint NOT NULL,
>>>  "X26_S10" bigint NOT NULL,
>>>  "X27_S11" bigint NOT NULL,
>>>  "X28_T3" bigint NOT NULL,
>>>  "X29_T4" bigint NOT NULL,
>>>  "X30_T5" bigint NOT NULL,
>>>  "X31_T6" bigint NOT NULL,
>>>  "LINENO" bigint,
>>>  "CODE" varchar(200),
>>>  "MEM" boolean,
>>>  "MEMOPERATION" varchar(200),
>>>