Inserting 130M SQLite rows per minute from a scripting language | Hacker News Reader

Inserting 130M SQLite rows per minute from a scripting language

blog.metaobject.com

163 pointsmpweiher21 comments

Discussion of the referenced article, Inserting a billion rows in under a minute:

https://news.ycombinator.com/item?id=27872575

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.

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

I’d second the rec for clickhouse.

+1 for clickhouse

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.

How does vanilla SQLite 'create index' fare, with correct pragmas e.g. cache_size?

If you’re trying to use SQLite for this, it’ll be much faster if you import the data first, and then create your indexes.

Also check out DuckDB, which is a similar tool tailored for data science; I haven’t used it but I’ve heard good things. https://duckdb.org/

That indexing perf isn't unique to SQLite; it's true for PostgreSQL too.

It's true for pretty much any rdbms - mostly an artifact of the b-tree implementation.

The other tricks to making SQLite fast:

* Ensure you're in WAL mode. https://sqlite.org/wal.html

* Use prepared statements and transactions.

* Set the cache pragma to ideally hold the DB in RAM.

* Run optimize and vacuum periodically (especially if the data is transactional): https://sqlite.org/pragma.html#pragma_optimize

* Pick the synchronous mode you're comfortable with: https://sqlite.org/pragma.html#pragma_synchronous

> Ensure you're in WAL mode.

That was what I thought as well, but in this case non-WAL turned out to be faster. Not yet sure why that is.

Dunno why it'd be surprising: WAL has higher concurrency but it translates to more overhead, writes have to hit the WAL then be flushed to the database, and reads have to check the database and the WAL.

WAL could have faster writes for small amounts of data, but once you have enough contents that the WAL has to be flushed during processing you're better off skipping the WAL entirely.

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.

https://github.com/avinassh/fast-sqlite3-inserts

> I'm probably doing something wrong.

Yes, it turns out. :-)

The "busy-*" programs just run the loops without doing any SQLite operations, in order to test loop overhead.

On my M1 Air, this is the fastest time I get from Rust:

   > ./bench-threaded-rust.sh 
   Sun Jul 25 07:52:46 CEST 2021 [RUST] threaded_batched.rs (100_000_000) inserts

   real 0m20.765s
   user 0m25.916s
   sys 0m2.303s

Author of the code here and yes, that's correct! Also, it is possible that rust compiler might be optimising this code, remove the random generation calls altogether. (I haven't gotten around to doing the analysis, yet)

I am surprised M1 Air taking 20 seconds, on my old machine its 7seconds ish.

> inserted 10M rows in 4.328 seconds

Not bad really. Objective-S is impressive.

I wish we had a set of SI units for measuring performance such as transactions per second, so that people could stick to that when talking about perf.

Surely this is bandwidth limited? Any language should be fine as long as it's not gratuitously awful.

These days, that turns out not to be the case. The resulting DB file for the 100M rows Objective-S/SQLite benchmark is around 2GB.

The built-in SSD can write that in under a second, if written optimally.

Reply on news.ycombinator.com