Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Inserting a billion rows in SQLite under a minute

avi.im

Post reply on HN
Print view View original

71–80 of 164 posts

Re: Inserting a billion rows in SQLite under a minute

#71

Post by Twirrim »

avinassh wrote: post #12

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.

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.

Re: Inserting a billion rows in SQLite under a minute

#72

Post by infogulch »

minitoar wrote: post #39
chrisseaton wrote: post #38

Earlier quoted context omitted.

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.

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

Perhaps "Towards Inserting 1B Rows in SQLite in Under a Minute" would be a better title.

Re: Inserting a billion rows in SQLite under a minute

#73

Post by the8472 »

Twirrim wrote: post #71
avinassh wrote: post #12

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.

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/…

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

Re: Inserting a billion rows in SQLite under a minute

#74

Post by fridif »

chrisseaton wrote: post #70
fridif wrote: post #67

Earlier quoted context omitted.

"USA LANDS MAN ON MARS BEFORE SOVIET UNION" "WELL ACTUALLY IT WAS THE MOON BUT YOU GET THE IDEA"

There’s no need to be snarky. I didn’t write the title, I’m just explaining what the author means.

I'm just memeing what it sounded like in my head, nothing personal

Re: Inserting a billion rows in SQLite under a minute

#75

Post by manigandham »

xxs wrote: post #24
einpoklum wrote: post #17

Earlier quoted context omitted.

Yes, if you want to run non-trivial queries on that data. Although, frankly, SQLite would not be my choice.

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.

What do you think a relational database does to provide you the querying flexibility? It builds the same exact data structures, but does it automatically and dynamically, while offering much more usability.

Most attempts to query using raw data structures just means you end up rebuilding a (very poor) relational database with none of the features.

Re: Inserting a billion rows in SQLite under a minute

#76

Post by motogpjimbo »

xeromal wrote: post #40
svdr wrote: post #28

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

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?

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.

Re: Inserting a billion rows in SQLite under a minute

#77

Post by minitoar »

avinassh wrote: post #60
minitoar wrote: post #39

Earlier quoted context omitted.

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

Hey, sorry for the misleading title. I started with ‘Fast SQLite Inserts’ and it had many iterations. In the title, I wanted to intend that I want to insert 1 billion rows under a minute on my machine. I thought the current title is fine, since I got LGTM for earlier drafts. The detail about on my machine is also important since mine is a two year old laptop and all the measurements are done on it. Also I got another…

Thanks for the explanation. I think it only needs a minor tweak. Maybe prefix with “trying to” or something like that. I am empathetic to the challenge of naming things concisely and accurately.

Re: Inserting a billion rows in SQLite under a minute

#78

Post by mpweiher »

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  id.
      var  done.
      var  title.
      -description { "". }
      +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.
     }.

Re: Inserting a billion rows in SQLite under a minute

#79

Post by [deleted] »

ndepoel wrote: post #63
tyingq wrote: post #22

Earlier quoted context omitted.

Do you mean crafting all the various database page btree structures and entries yourself? I'd be concerned about subtle bugs.

An SQLite database is just a file. You can build the empty database with schema and base values ahead of time, save it to a file (or an in-memory byte buffer) and then every time you want to create a new database, you just copy that file. No need to do any expensive initialization queries that way. If raw high-speed throughput is needed, skipping that step can make a significant difference.

[deleted]
Post reply on HN