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" > <https://tvtropes.org/pmwiki/pmwiki.php/Main/LudicrousSpeed> 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 [email protected] 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, [email protected] 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), >>> "MEMREAD" boolean, >>> "MEMADDR" bigint, >>> "MEMVALUE" bigint, >>> "MEMSIZE" integer, >>> "CCODE" varchar(100), >>> "PRIV" integer, >>> "IRQREQUEST" boolean, >>> "IRQREQUESTNO" integer, >>> "IRQREQUESTLEVEL" integer >>> );"""); >>> >>> String sql = "INSERT INTO `qemu` VALUES (default, ?, >>> CURRENT_TIMESTAMP(), 'quantr-ubuntu', ?, ?, ?"; >>> sql += ",?".repeat(59); >>> sql += ",?,?,?,?,?,?,?,?,?,?,?,?,?)"; >>> PreparedStatement stmt = conn.prepareStatement(sql); >>> >>> ProgressBar pb = new ProgressBar("Insert H2", 1000); >>> for (int x1 = 0; x1 < 1000; x1++) { >>> // System.out.println(x1); >>> for (int x2 = 0; x2 < 10000; x2++) { >>> int x = 1; >>> stmt.setLong(x++, 12345678l); >>> stmt.setBoolean(x++, true); >>> stmt.setLong(x++, 12345678l); >>> stmt.setString(x++, "aslkdjads alksdja ldj"); >>> for (int z = 0; z < 59; z++) { >>> // System.out.println(me.getKey() + "\t= " + me.getValue()); >>> // System.out.println(">" + me.getKey() + "=" + me.getValue()); >>> stmt.setLong(x++, 12345678l); >>> } >>> stmt.setLong(x++, 12345678l); >>> stmt.setString(x++, "askdjasd aljdlasjdlkaj sd"); >>> stmt.setBoolean(x++, true); >>> stmt.setString(x++, "peter cheung"); >>> stmt.setBoolean(x++, false); >>> stmt.setLong(x++, 12345678l); >>> stmt.setLong(x++, 12345678l); >>> stmt.setInt(x++, 12345678); >>> stmt.setString(x++, "asdasd as daasd asda sds das d"); >>> stmt.setInt(x++, 12345678); >>> stmt.setBoolean(x++, true); >>> stmt.setInt(x++, 12345678); >>> stmt.setInt(x++, 12345678); >>> stmt.addBatch(); >>> } >>> stmt.executeBatch(); >>> pb.step(); >>> } >>> pb.refresh(); >>> conn.commit(); >>> conn.close(); >>> } >>> } >>> >>> -- >>> 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 [email protected]. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/h2-database/cd28e1d9-9dbd-45f9-b613-7c49566f307cn%40googlegroups.com >>> >>> <https://groups.google.com/d/msgid/h2-database/cd28e1d9-9dbd-45f9-b613-7c49566f307cn%40googlegroups.com?utm_medium=email&utm_source=footer> >>> . >>> >>> >>> -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/0769f9aa-cb36-47f9-8b60-652edf8815f7n%40googlegroups.com.
