5 ms·
Inserting 130M SQLite rows per minute from a scripting language
- aasasd 5y agoPersonally 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.
- pupdogg 5y agoSorry 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
- bob1029 5y agoIt 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 5y agoHow does vanilla SQLite 'create index' fare, with correct pragmas e.g. cache_size?
- jitl 5y agoIf 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/ https://duckdb.org/
- paulddraper 5y agoThat indexing perf isn't unique to SQLite; it's true for PostgreSQL too.
- tehlike 5y agoIt's true for pretty much any rdbms - mostly an artifact of the b-tree implementation.
- mceachen 5y agoThe other tricks to making SQLite fast: * Ensure you're in WAL mode. https://sqlite.org/wal.html 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 https://sqlite.org/pragma.html#pragma_optimize * Pick the synchronous mode you're comfortable with: https://sqlite.org/pragma.html#pragma_synchronous https://sqlite.org/pragma.html#pragma_synchronous
- mpweiher 5y ago> 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.
- masklinn 5y agoDunno 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.
- marvel_boy 5y ago> inserted 10M rows in 4.328 seconds Not bad really. Objective-S is impressive.
- macintux 5y agoDiscussion of the referenced article, Inserting a billion rows in under a minute: https://news.ycombinator.com/item?id=27872575 https://news.ycombinator.com/item?id=27872575
- IfOnlyYouKnew 5y agoLast 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.
- tln 5y agohttps://github.com/avinassh/fast-sqlite3-inserts https://github.com/avinassh/fast-sqlite3-inserts
- mpweiher 5y ago> 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
- avinassh 5y agoAuthor 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.
- taneq 5y agoSurely this is bandwidth limited? Any language should be fine as long as it's not gratuitously awful.
- mpweiher 5y agoThese 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.
- mehdix 5y agoI 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.