[HN Gopher] Inserting 130M SQLite Rows per Minute from a Scripti... ___________________________________________________________________ Inserting 130M SQLite Rows per Minute from a Scripting Language Author : mpweiher Score : 71 points Date : 2021-07-24 20:11 UTC (2 hours ago) (HTM) web link (blog.metaobject.com) (TXT) w3m dump (blog.metaobject.com) | macintux wrote: | Discussion of the referenced article, _Inserting a billion rows | in under a minute_ : | | https://news.ycombinator.com/item?id=27872575 | IfOnlyYouKnew wrote: | Last time this was here, I ran this and got the following | results: /fast-sqlite3-inserts (master)> time | make busy-rust Sun Jul 18 17:04:59 UTC 2021 | [RUST] busy.rs (100_000_000) iterations real | 0m9.816s user 0m9.380s sys 0m0.433s | ________________________________________________________ | Executed in 9.92 secs fish external usr | time 9.43 secs 0.20 millis 9.43 secs sys time | 0.47 secs 1.07 millis 0.47 secs | fast-sqlite3-inserts (master)> time make busy-rust-thread | Sun Jul 18 17:04:48 UTC 2021 [RUST] threaded_busy.rs | (100_000_000) iterations real 0m2.104s | user 0m13.640s sys 0m0.724s | ________________________________________________________ | Executed in 2.33 secs fish external usr | time 13.68 secs 0.20 millis 13.68 secs sys time | 0.78 secs 1.18 millis 0.78 secs | | I'm probably doing something wrong. Or I'm getting the pace | needed for the billion? | | This is on a M1 MacBook Air. | marvel_boy wrote: | > inserted 10M rows in 4.328 seconds | | Not bad really. Objective-S is impressive. | taneq wrote: | Surely this is bandwidth limited? Any language should be fine as | long as it's not gratuitously awful. | aasasd wrote: | Personally I'm more interested in _indexing_ a boatload of data | as fast as possible, with modest resource requirements. Had a | couple of cases where I 'd like to do that on a mid-tier laptop, | with no particular success so far. I have to guess, but it seems | that 'data scientists' either buy big fat boxes with tons of ram | and cpu, or offload everything to big fat boxes in datacenters, | or twiddle thumbs for a quite while. You'd think that by now | writing indexes on all columns at the top sequential drive speed | would be a solved problem from any 'Learn data science in two | days' tutorial. | bob1029 wrote: | It is certainly feasible to saturate NVMe with just index | writes in many niche implementations today. The trick is | usually copious amounts of batching so that IO can do more per | unit. | remram wrote: | How does vanilla SQLite 'create index' fare, with correct | pragmas e.g. cache_size? | pupdogg wrote: | Sorry I don't mean to hijack the original post but for | performant insert and indexing (which I assume is for | analysis), I'd recommend using Clickhouse or QuestDB | wiredfool wrote: | I'd second the rec for clickhouse. ___________________________________________________________________ (page generated 2021-07-24 23:00 UTC)