Inserting a billion rows in SQLite under a minute

https://avi.im/blag/2021/fast-sqlite-inserts/

by todsacerdoti • 5 years ago

573 144 5 years ago

ThePadawan• 5 years ago

There are some cool ideas in there!

I wonder how much I could speed up my test suites (that don't rely on transaction rollbacks) by disabling journalling. Those milliseconds per query add up to seconds per tests and minutes per PR.

lmz• 5 years ago

I wonder if defining a virtual table (https://www.sqlite.org/vtab.html) and just doing

INSERT INTO t (...) SELECT ... from virtual_table

would be any faster.

rubyfan• 5 years ago

From my armchair here I thought something similar, I think I heard 1) that inserting into the same table has some speed limitations and 2) one of the authors ideas was to spin up 4 processes…

I wonder if you could insert into 10 different tables from 10 threads or processes then

insert into single_table (…) select (…) union.

No idea if insert select does the trick or not but you’re almost at the point of partitioning here. If the application called for it you could do some sort of poor mans partition in order to write all the data and then push some complexity into the read.

I also wondered what if any impact the ID primary key had on inserting at the level of frequency.

/armchair

roberto• 5 years ago

If you define the virtual table would you even need to do the `INSERT`? Just query the virtual table instead of inserting into `t` and querying `t`.

bob10292• 5 years ago

You can go even faster if you can organize your problem such that you simply start from a copy of a template SQLite database each time (it is just a file/byte sequence).

We do SQL evaluation for a lot of business logic throughout our product, and we have found that starting from a template database (i.e. one with the schema predefined and canonical values populated) can save a lot of time when working in tight loops.

jonnycomputer3• 5 years ago

Are you suggesting have a template and then updating rows with random values where necessary?

nezirus• 5 years ago

You just do raw byte copies from sample DB, no SQL or "inserts" or anything similar. Imagine test database consists of 3 parts (all raw bytes)

### PROLOGUE ### Sample row ### EPILOGUE

You copy & write prologue, write 1B sample raws (can optimize this at will, large writes, etc)

Copy & write epilogue and fsync the data. You probably need to modify some metadata, but that should be a few writes at most.

That should be as good as it gets, providing your IO is optimal.

bob10292• 5 years ago

Yes. Start from known-good database and then update or insert with needed deltas. You could even have more than you need in the template file and truncate/drop what is not relevant for the current context. Depending on the situation, it might be faster to start from a database containing all of the possible things.

perlgeek• 5 years ago

I wonder if something like https://paulbradley.org/sqlite-test-data/ could be used to generate the test data in sqlite3 directly, and if it would be any faster.

(It would likely be faster for other DB engines, because there is network overhead in the communication between the program and the DB; no such things for sqlite).

mirekrusin• 5 years ago

If it's single, in memory table, is there really need to use database? Won't language provided data structures suffice?

jonnycomputer3• 5 years ago

It is a good question, but I'm guessing that the whole reason for creating the database in the first place is to test interactions with the database, in which case you'd have to mock up all the interactions being tested (selects, deletes, joins, etc.).

einpoklum5• 5 years ago

Yes, if you want to run non-trivial queries on that data.

Although, frankly, SQLite would not be my choice.

xxs3• 5 years ago

I can't think of a single case where in-memory database is a good option, aside playing w/ SQL.

Whatever queries that might be a hashmap/tree/skiplist, etc. would be a lot better.

adamnemecek• 5 years ago

What would be your choice?

lsuresh• 5 years ago

There are some domains like building cluster managers where having a relational view of the cluster state and being able to query/manipulate it declaratively has significant perks. See for example: https://www.usenix.org/system/files/osdi20-suresh.pdf (disclaimer: I'm an author)

rmetzler2• 5 years ago

Is PRAGMA temp_store = MEMORY the same as putting the file on a TempFS?

elteto• 5 years ago

Maybe, maybe not. I imagine writing directly to memory will always be faster than writing to a filesystem, even if it’s ultimately backed by RAM. Might be a negligible difference though.

DemocracyFTW• 5 years ago

I tried using tmpfs to create and exercise a file-based SQLite DB, and it performed significantly worse than a RAM-based DB (opened with an empty string or ':memory:').

rmetzler2• 5 years ago

Thank you :)

siscia2• 5 years ago

Creator of RediSQL / zeeSQL (https://zeesql.com/)

Insertion performance on a single table are very very hard to optimize.

A single process looping it is your best bet.

I would just increase the batch size, which is the most influent factor.

Then another point... When you do batches, you do

    BEGIN TRANSACTION;
    for i in range(1, 50):
       execute_stmt
    COMMIT;

You do not create a long list of parameters.

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

;)

siscia2• 5 years ago

Even though merging strings seems faster...

avinassh7• 5 years ago

That's a great point, let me try it!

You do not create a long list of parameters.

I have done much worse by trying to insert a really long string of 100K params

avinassh7• 5 years ago

Hey all, author here. I didn't expect this to reach front page of HN. I came here to submit and it was here already! I am looking for more ideas to experiment. Here's one idea which someone from another forum gave me: exploring recursive queries and using SQLite random methods to do the insertions.

Another crazy idea is to learn about SQLite file format and just write the pages to disk.

eatonphil• 5 years ago

I am also interested in writing the SQLite or PostgreSQL file format straight to disk as a faster way to do ETL. I'd be curious to hear if anyone has actually tried this. I'd be as curious if you end up trying this too.

tyingq3• 5 years ago

Sqlite has a btree module/api, though there's a lot of "TODO:" notes in the document: https://sqlite.org/btreemodule.html

avinassh7• 5 years ago

yes, I plan to try this next and measure the performance. Others also suggested similar idea to try.

I am also interested in writing the SQLite or PostgreSQL file format straight to disk as a faster way to do ETL.

what exactly you are trying to do here?

oldtablet• 5 years ago

I really like sqlite but I'm curious how fast it would be to write the data to parquet files and read it with duckdb. I've done something similar before and in my case duckdb was faster than sqlite.

exikyut2• 5 years ago

justinclift• 5 years ago

You mention wanting to do a Go version. Not sure if it's useful, but this is a SQLite "bulk data generation" util I threw together ages ago in Go:

https://github.com/sqlitebrowser/sqlitedatagen

There's some initial work to parallelise it with goroutines here:

https://github.com/sqlitebrowser/sqlitedatagen/blob/multi_ro...

Didn't go very far down that track though, as the mostly single threaded nature of writes in SQLite seemed to prevent that from really achieving much. Well, I _think_ that's why it didn't really help. ;)

mathnode• 5 years ago

Very nice! I shall give that a try. I always get excited by SQLite and go projects.

I maintain a similar go tool for work, which I use to stuff around 1TB into MariaDB a time:

https://github.com/rcbensley/brimming

Twirrim• 5 years ago

I gave the suggestion on Twitter, but probably worth mentioning here too as people here might not be aware of it.

https://www.flamingspork.com/projects/libeatmydata/

libeatmydata shouldn't be used in production environments, generally speaking, as it biases for speed over safety (lib-eat-my-data), by disabling fsync, and associated commands for the running process under it. Disabling those commands results in less I/O pressure, but comes with the risk that the program thinks it has written safely and durably, and that may not be true. It essentially stops programs that are written to be crash proof, from being actually crash proof. Which under the circumstances you're operating on you almost certainly don't care.

I've used this when reloading database replicas from a dump from master before, as it drastically speeds up operations there.

the8472• 5 years ago

Overlayfs offers libeatmydata functionality in recent linux kernels without having to intercept libc, so it should work for things that use raw syscalls too.

nh23• 5 years ago

Aside: SQLite has native functionality to do what libeatmydata does (disable fsync):

    pragma synchronous = off

https://www.sqlite.org/pragma.html#pragma_synchronous

mikepurvis• 5 years ago

Also great for stuff like package/container builds where you're not worried about a consistent intermediate state— it either succeeds and you get the end result you want or it fails and you only care about logs.

mst• 5 years ago

I'm curious how you'd fair if you pregenerated the data, with periodic COMMITs, and then just sling it at the sqlite3 binary over STDIN and see how it handled that.

Certainly if I was trying to do the same thing in Pg my first thought would be "batched COPY commands".

anigbrowl• 5 years ago

It's a great article! Always nice to see clear well-written guides that save people time from reinventing the wheel.

Fiahil• 5 years ago

I did similar Rust/Python record generation experiments, and I can relate to these numbers.

However, I think your Rust threaded trial might be a little bit off in subtle ways. I would truly expect it to perform about several times better than single threaded Rust and async Rust (async is generally slower on these workloads, but still faster than Python)

Edit : After reading your rust code, you might have room for some improvements :

- don’t rely on random, simply cycle through your values

- pre-allocate your vecs with ˋwith_capacityˋ

- dont use channels, prefer deques..

- ..or even better, don’t use synchronization primitives and open one connection per thread (not sure if it will work with sqlite?)

adontz• 5 years ago

Why don't you create in-memory database (see SQLITE_OPEN_MEMORY, or ":memory:", or sqlite3_vfs_register) and copy result to a real file?

lexfiend• 5 years ago

Possibly because:

The machine I am using is MacBook Pro, 2019 (2.4 GHz Quad Core i5, 8GB, 256GB SSD, Big Sur 11.1)

Given the target schema, 100M rows with only 8GB RAM risks hitting swap hard.

exikyut2• 5 years ago

Potentially interesting:

https://github.com/siara-cc/sqlite_micro_logger_arduino

https://github.com/siara-cc/sqlite_micro_logger_arduino/blob...

This is a heavily subsetted implementation of SQLite3 that can read/write databases (presumably on SD cards) from very small microcontrollers.

It presumably doesn't have the same ACID compliance properties, but with a single <1.5k source file, may represent a particularly efficient way to rapidly learn the intrinsics if you happened to want to directly manipulate files on disk.

Now I'm thinking it could actually be interesting to see what drh thinks of this implementation (and any gotchas in it) because of its small size and accessibility.

asicsp• 5 years ago

The larger DB required more than 30 minutes to generate. So I spent 30+ hours to reduce the 30 mins of running time :p

Worth it.

I could just run s the script again.

Found a typo.

ericlewis• 5 years ago

Cool

jonnycomputer3• 5 years ago

The generated data would be random with following constraints: The area column would hold six digits area code (any six digits would do, no validation). The age would be any of 5, 10, or 15. The active column is either 0 or 1.

That is about 6 million combinations. This is not such a big space that it would be impractical to precompute all possible combinations. I wonder if hashing in to such a precomputed table might help. Might not if the overhead of maintaining the lookup table is too high.

mimir• 5 years ago

Database optimization posts are always interesting, but it's really hard to do any apples to apples comparison. Your performance is going to depend mostly on your hardware, internal database settings and tunings, and OS level tunings. I'm glad this one included some insight into the SQLite settings disabled, but there's always going to be too many factors to easily compare this to your own setup.

For most SQL systems, the fastest way to do inserts is always just going to batched inserts. There's maybe some extra tricks to reduce network costs/optimize batches [0], but at it's core you are still essentially inserting into the table through the normal insert path. You can basically then only try and reduce the amount of work done on the DB side per insert, or optimize your OS for your workload.

Some other DB systems (more common in NoSQL) let you actually do real bulk loads [1] where you are writing direct(ish) database files and actually bypassing much of the normal write path.

[0] https://dev.mysql.com/doc/refman/5.7/en/insert-optimization.... [1] https://blog.cloudera.com/how-to-use-hbase-bulk-loading-and-...

vajrabum• 5 years ago

Oracle, DB2, MySQL, SQL Server, and PostgreSQL all support bulk insert. Two obvious use cases are QA Databases and the L part of ETL which pretty much require it.

sharken2• 5 years ago

For relational databases in the enterprise with large amounts of data, batched inserts work great.

For large deletes it is often better to move the rows that won't be deleted to a new table and rename the table when done.

With large updates it is important to look at the query plan and optimize it with good indexes. Batching also works well in this scenario.

orf2• 5 years ago

How long does it take when using the native CSV import features?

https://www.sqlite.org/csv.html

motoboi• 5 years ago

It’s fast. But then we need a “1 billion CSV lines” article.

orf2• 5 years ago

Which I expect is significantly faster to generate. You could probably combine it with a pipe to avoid all disk IO.

tanin• 5 years ago

I built https://superintendent.app and experimented a lot with this feature.

I can import 1GB CSV file in 10 seconds on my MacBook. This queries from a virtual table and puts it in actual table

freeqaz• 5 years ago

This is what I did the last time I ran into SQLite being slow with inserts.

I just shat out a CSV file and then imported it. It was much quicker! (like 1000x faster)

This article has some useful notes for me now to try the next time I play with my pet projects. :)

svdr• 5 years ago

I thought one billion was 1000M (and not 100M)?

throwaway210222• 5 years ago

1,000,000 million. In old money.

thanksforfish• 5 years ago

Ref: https://en.m.wikipedia.org/wiki/Billion

kylec• 5 years ago

This is an important comment. It's not a typo in the article, even the source code only does 100M rows, not 1B. The author definitely does not hit the target of 1B rows in a minute.

chrisseaton• 5 years ago

The author doesn’t say a billion is 100 million. They say they’d like to be able to insert a billion, and say they’re able to insert 100 million. It’s not a contradiction.

minitoar2• 5 years ago

Ok but that is a rather clickbaity title. The title makes it sound like they are successfully doing that.

jbverschoor• 5 years ago

Earning $10M/month with a saas

xeromal2• 5 years ago

Yeah, that's my interpretation of a billion too. I vaguely recall that India or Britain interprets a billion differently though. Maybe that's what they're thinking?

atleta• 5 years ago

I think it's the same in UK English as well. But in some (most?) European languages billion actually means 1 000 000 million (so a thousand times more). And we use "milliard" for 1000 million.

mongol2• 5 years ago

In Swedish, and I think in German and other languages too, 1 billion is 1e12. 1e9 is called milliard / miljard in Swedish.

motogpjimbo• 5 years ago

Just about everyone in the UK defines a billion to be 10^9. I've heard about the 10^12 definition but never encountered anyone who uses it - I think it must have been an older usage that fell out of favour.

gunapologist992• 5 years ago

Exactly.

  thousand = 1000
  million  = 1000 * thousand (or 1000^2)
  billion  = 1000 * million (or 1000^3)
  trillion = 1000 * billion (or 1000^4)

(not to discount regional differences)

https://www.brainyquote.com/quotes/everett_dirksen_201172

WhyNotHugo• 5 years ago

This depends a lot.

For English-speaking countries, 1B = 1M * 1k

For Spanish-speaking countries, 1B = 1M * 1M

For other languages it's a big "it varies", though the second definition seems to be the most common. The term "billion" is honestly, as ambiguous as using "06-03" for a date.

Also note that, historically, English also followed the second definition, so for old literature it's also confusing.

einpoklum5• 5 years ago

Recently, I ran into a situation where I needed a test database with lots of rows and needed it fast. So I did what any programmer would do: wrote a Python script to generate the DB.

The author must have a rather low opinion of programmers... generating data with a script is good for 1,000 rows. Maybe 10,000 rows. 100,000 is probably stretching it. Beyond that the slowness will be meaningful.

Anyway, if the "test database" data is given in advance, you want an in-memory DBMS which can load data from files. If it isn't - use an in-memory DBMS which supports generated columns, or apply your random-number-generating function on a trivial column.

(MonetDB standalone/embedded would be a decent option; maybe the columnar storage mechanism in MariaDB? And of course there are commercial offerings.)

Unfortunately, it was slow. Really slow. So I did what any programmer would do: went down the rabbit hole of learning more about SQLite...

That's the wrong approach. SQLite isn't called "lite" for nothing. It's not what you would use for bulk operations on large tables.

Read the SQLite FAQ: https://www.sqlite.org/whentouse.html

it oversells itself a bit, but still says its (only) advantages for data analysis are: "easier to install and use and the resulting database is a single file that can be written to a USB memory stick or emailed."

geofft2• 5 years ago

I suspect these opinions would be less likely to get downvoted if you could back them up with data, specifically:

- Assuming you do have a test database in advance that's represented in a file, but for some reason not a SQLite file or other database, how long does it take to load 100M rows in a database of your choice, and how does that compare to the author's results?

- The author does not in fact have a test database in advance, and is generating random data, which I think is what you're saying by "apply your random-number-generating function on a trivial column" - how long would your approach take to generate 100M rows?

- If you generated a database of about the same size in a database of your choice, how do bulk operations on large tables perform in comparison to SQLite? (My understanding is that it's "lite" because it's not client/server, not because its performance is poor.)

I don't think any of these experiments would be terribly hard to run.

arusahni2• 5 years ago

[ @dang, please delete if/when you get the chance, thank you ]

kangalioo• 5 years ago

Did you mean to post this to https://news.ycombinator.com/item?id=27872596 ?

arusahni2• 5 years ago

Yes. Not sure how I got mixed up.

kebman• 5 years ago

Mini summary: Do not use this in production. xD

MobiusHorizons• 5 years ago

I would recommend looking into dtrace or other observability tools to figure out what to optimize next. So far you have basically had to guess where the slowness is and optimize that.

Lastly, how long does it take to make a copy of an already prepared 1b row SQLite file?that seems easier than generating a new one.

bilekas• 5 years ago

Threads / async may not be faster always

This is actually a little bit mind blowing for me. I'm gonna go and play with this. But what a cool read!

mongol2• 5 years ago

1 billion rows, 8GB RAM. I think there will be some limitation from available RAM as well.

pachico• 5 years ago

I've been dealing with lots of data and SQLite and my outtakes are:

- language has very little to do since the bottleneck will most likely be the way you insert data

- indeed prepared statements are useful but the performance didn't change much when I did long transactions and commit every certain amount of thousand of rows

- having lots of rows in your table is good but certain queries, like aggregation over many rows, are not what SQLite is great about.

- ClickHouse can easily ingest that and more in a laptop without even any scripting language.

mpweiher• 5 years ago

Interesting!

I was actually just working on SQLite speed for Objective-S (http://objective.st), partly as a driver for getting some of the more glaring inefficiencies out.

Using a "to do list" schema, I currently get the 100M rows out in 56 seconds, which is around half the speed of the Rust example given here, but 3 times the speed of PyPy and almost 10x faster than Python.

This is from an interpreted script that not only does the inserts and creates the objects to insert in the first place, but also defines the actual class.

The lower-level code is written in Objective-C, like the rest of Objective-S.

Class definition:

    class Task {
      var <int> id.
      var <bool> done.
      var <NSString> title.
      -description { "<Task: title: {this:title} done: {this:done}>". }
      +sqlForCreate {
          '( [id] INTEGER PRIMARY KEY, [title] NVARCHAR(220) NOT NULL, [done] INTEGER );'.
      }
    }.

Code to insert a computed array of tasks 10 times:

     1 to:10 do: {
         this:tasksTable insert:taskList.
     }.

IfOnlyYouKnew• 5 years ago

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.

nzealand• 5 years ago

If you insert 4 rows into a table, and join it on itself without any conditional criteria, it will result in a cartesian product of 16 rows (Select * from a, a)

Do that cartesian join three more times, and you have over 4 billion results.

Then you simply need to use the random function in conjunction with division, rounding and case statements to get the desired random numbers.

elephantum2• 5 years ago

I bet, that this is mostly random.randint benchmark and not SQLite.

Also interesting whether batched numpy version would compare better to Rust.

elephantum2• 5 years ago

Correction: random.randint + string interpolation

pksebben• 5 years ago

I'm pretty new to this sort of optimization stuff, so forgive me if this is ridiculous for any reason, but I'm wondering about two things reading this:

1: is it useful / feasible to set a sort of "lower bound" to these optimizations by profiling the raw write time on a set of a certain size?

2. assuming that writes are ultimately the limiting factor, could you gain performance by calculating the minimum batch size as it's write time intersects in-memory calculation, and as soon as you hit that size, pop a thread to write that batch to disk - then return some async signal when the write is done to trigger the next batch of writes? is it possible to "stitch together" these batches post-writes?

edit: mobile formatting is le suck

roman-holovin3• 5 years ago

INSERT INTO user (area, age, active) SELECT abs(random()) % 1000000, (abs(random()) % 3 + 1) * 5, abs(random()) % 2 FROM generate_series(1, 100000000, 1)

Faster by 10% than fastest author implementation on my machine - 19 seconds against 21 for 'threaded_batched'.

gfodor• 5 years ago

I have to assume this was rejected as a valid approach, but if not, this whole thread gets 10x more interesting than it already was. I hope the author responds.

roman-holovin3• 5 years ago

Well, even if you just insert zeros instead of random values, it takes 9 seconds on my computer to insert 100M rows, so even that is not a 1B rows per minute.

And I think INSERT INTO ... SELECT is the fastest way to bulk insert data into sqlite.

Also, I have tried to use carray sqlite feature that allow to share memory with sqlite and use recursive CTE to query it, but it is slower. Though, you can pass values you've generated from Rust instead of using random().

nojvek2• 5 years ago

This is with in memory database and journaling disabled?

roman-holovin3• 5 years ago

I have used pragmas from the article.

Since it is only 100M rows, it takes 1.8 GB on the disk, so I've used tmpfs for this which essentially is a ramdisk. But I have a gen4 pcie nvme SSD - it can reliably write at 4GB/s sequentially, so writing takes a ~500ms for 100M rows, it is not a bottleneck here. random() takes ~half of the insert time. Generating those values with Rust, for example, is faster, but sharing this data with sqlite takes more time than generating it with random().

Maybe implementing custom virtual table in C or Rust like build-in generate_series, but the one that will produce user table fields will be faster, but that is significantly more effort than my query.

This query with random() and generate_series executed in sqlite CLI takes whooping 8MB of the RAM, so you don't even have to close all Electron-based applications to run it on a computer with 8GB of RAM.

nh23• 5 years ago

My run on NixOS 21.05 with sqlite 3.35.2, without randomness, on an i7-7500U:

Invocation:

    command time sqlite3 ':memory:' '
    create table IF NOT EXISTS user
    (
        id INTEGER not null primary key,
        area CHAR(6),
        age INTEGER not null,
        active INTEGER not null
    );
    INSERT INTO user (area, age, active) SELECT 0, 1, 2 FROM generate_series(1, 100000000, 1);
    '

Result:

    16.34user 0.43system 0:16.89elapsed 99%CPU (0avgtext+0avgdata 1477320maxresident)k
    11inputs+0outputs (0major+369851minor)pagefaults 0swaps

Invocation with pragmas:

    command time sqlite3 ':memory:' '
    PRAGMA journal_mode = OFF;
    PRAGMA synchronous = 0;
    PRAGMA cache_size = 1000000;
    PRAGMA locking_mode = EXCLUSIVE;
    PRAGMA temp_store = MEMORY;
    create table IF NOT EXISTS user
    (
        id INTEGER not null primary key,
        area CHAR(6),
        age INTEGER not null,
        active INTEGER not null
    );
    INSERT INTO user (area, age, active) SELECT 0, 1, 2 FROM generate_series(1, 100000000, 1);
    '

Result with pragmas:

    17.31user 0.41system 0:17.85elapsed 99%CPU (0avgtext+0avgdata 1477288maxresident)k
    11inputs+0outputs (0major+369850minor)pagefaults 0swaps

As expected, the pragmas make no difference when using `:memory:` -- 17 seconds, 1.4 GB RAM each on my laptop.

Seattle3503• 5 years ago

I've used the other pragma, but does

PRAGMA locking_mode = EXCLUSIVE;

improve performance much?

ajnin• 5 years ago

Here's my solution : generate the 1B records .sqlite file once beforehand, then before running each test, copy it over the test DB file ;)

polyrand• 5 years ago

Really good article, I’ve experimented with this kind of workloads in SQLite a few times and found it insightful. One note though, using:

  pragma temp_store = memory;

only affects temporary tables and indices, not the main database itself[0]

[0] https://sqlite.org/pragma.html#pragma_temp_store

Groxx• 5 years ago

Yep - I discovered most of this a while back when I needed to do a bunch of exploring of ~200m rows of data (like 50GB). I chucked it into SQLite and was done with everything in a day, it was absolutely wonderful. This post is a pretty good summary of what to change and why.

Prepared statements should be considered the norm for SQLite - they have pretty major performance benefits, and any decent ORM or query engine can probably do it for you implicitly or with a single flag, so it's practically free for many applications. I'm always surprised how often I see applications or benchmarks or "how to be fast with SQLite" not using them, so I'm definitely glad to see them covered here :)

Egret• 5 years ago

Here is an example using Tcl to create an SQL script using recursive common table expressions:

# createSQL.tcl

set increment 100000

set loop [expr {100000000 / $increment}]

puts {PRAGMA journal_mode = OFF; PRAGMA synchronous = 0; PRAGMA cache_size = 1000000; PRAGMA locking_mode = EXCLUSIVE; PRAGMA temp_store = MEMORY;

CREATE TABLE user( pk INTEGER PRIMARY KEY, area INTEGER, age INTEGER, active INTEGER );}

for {set i 0} {$i < $loop} {incr i} { puts " WITH RECURSIVE tmp(pk,area,age,active) AS ( SELECT [expr {$i * $increment}], 500001, 5, 0 UNION ALL SELECT pk+1, 10000*(abs(random())%9+1)+abs(random())%100000, 5*(abs(random()%3)+1), abs(random()%2) FROM tmp WHERE pk < [expr {($i+1)*$increment-1}] ) INSERT INTO user SELECT * FROM tmp;" }

#EOF

Core 2 Duo 2.53 GHz, 4GB RAM, SSD:

$ time -p tclsh createsql.tcl | sqlite3 test.db

off

exclusive

real 213.37

user 208.10

sys 4.58

quest88• 5 years ago

If it's a test database, can you simply generate it once, commit it, and use that in all future tests?

stevage2• 5 years ago

Inserting One Billion Rows in SQLite Under A Minute

But the results given are all for inserting 0.1B rows, not 1B rows. What are the full results?

discordance2• 5 years ago

Multiply the reported number by 10

wuxb• 5 years ago

Just tried to insert 100M KV pairs of the same data format in a KV store. It takes about 26s on a Xeon Silver 4210, including fully writing down the data to the filesystem/SSD.

The source code can be found here: https://github.com/wuxb45/remixdb/blob/master/i100m.c

    $ make i100m.out libremixdb.so
    $ numactl -N 0 ./i100m.out
    insert time without sync: 24.865s, with sync: 25.801s

malkia2• 5 years ago

PRAGMA page_size 65536

malkia2• 5 years ago

But ensure this is actually in effect (e.g. do vacuum, etc.)

lallysingh• 5 years ago

.. or the throughput of SQLite acting in pure fwrite(3) mode with a big buffer.

nojvek2• 5 years ago

For the table you have 3 integer rows and a char(6) row. Taking integer as 32 bit that is a total of 18 bytes of data per row.

At billion rows, that is 18GB of data. With some overhead for storing page info, let’s call it 20GB flat.

A modern SSD can deliver ~500MB/s write speed. That means writing 20GB of data can be done in 40 seconds.

Therefore a billion rows in a minute is quite plausible. At-least not bottlenecked by disk speed (if we can saturate disk).

sharken2• 5 years ago

It's a good insight to know what the hardware is capable of.

The SSD in the authors machine can do 1300MB/s and the latest M1 model can do 2100MB/s.

For current gen SSDs with PCIe 4.0, that number increases to 6600MB/s for the Sabrent Rocket 4 Plus.

That would mean just around ~5 seconds for writing to disk.

https://www.pcgamer.com/best-pcie-4-ssd-for-gaming/

patrec• 5 years ago

A modern SSD can deliver ~500MB/s write speed.

More like ~5GB/s.

teekert3• 5 years ago

Webapps (or webapp frameworks) like Nextcloud and Django, come, by default, with SQLite. That makes it easy to start, but they all warn you: When you get serious, get a real DB like MariaDB or PostgreSQL... And with stuff like this I wonder, is this really necessary?

I really like SQLite, it's well supported in Python, backup-restore is very simple. What is the real difference (probably application depended) between SQLite and a "real" DB engine?

tcbasche• 5 years ago

I guess the idea is you can scale the app independently of the DB. How do you scale out a single file? (Genuinely curious)

teekert3• 5 years ago

Hmm, never thought of this :) I come more from a selfhosting angle. And my django website is prototype only so far… But it is a point I never considered so thanx.

I wonder at what scales this starts to count (in human readable units ;))

discordance2• 5 years ago

This spells out where it’s appropriate and where it isn’t:

https://www.sqlite.org/whentouse.html

teekert3• 5 years ago

Thanx!

Inserting a billion rows in SQLite under a minute

Related Stories