Re: [h2] push the insert speed to max
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 -- 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/4c369243c7e21456d8229604c1dc38a5b86ce778.camel%40manticore-projects.com.
Re: [h2] push the insert speed to max
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 On Sun, 2024-01-14 at 00:48 -0800, mche...@gmail.com wrote: > 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 > On Sunday 14 January 2024 at 01:21:36 UTC+8 mche...@gmail.com wrote: > > 1xk mean i can insert 10-15 thousand records to h2 per second, > > thanks > > > > On Friday 12 January 2024 at 16:41:03 UTC+8 Andreas Reichel wrote: > > > 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/0d855987-55f3-45ea-bca3-4cf3390f9a08n%40googlegroups.com > . -- 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/2230b650284fee800808d051b6a2ea817ac523ba.camel%40manticore-projects.com.
Re: [h2] push the insert speed to max
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 On Sunday 14 January 2024 at 01:21:36 UTC+8 mche...@gmail.com wrote: > 1xk mean i can insert 10-15 thousand records to h2 per second, thanks > > On Friday 12 January 2024 at 16:41:03 UTC+8 Andreas Reichel wrote: > >> 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/0d855987-55f3-45ea-bca3-4cf3390f9a08n%40googlegroups.com.
Re: [h2] push the insert speed to max
1xk mean i can insert 10-15 thousand records to h2 per second, thanks On Friday 12 January 2024 at 16:41:03 UTC+8 Andreas Reichel wrote: > 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/481700be-0c55-4b4c-8d47-5bb9413cd51cn%40googlegroups.com.
Re: [h2] push the insert speed to max
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
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
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.
