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

91–100 of 164 posts

Re: Inserting a billion rows in SQLite under a minute

#91

Post by mst »

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'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".

Re: Inserting a billion rows in SQLite under a minute

#94

Post by mathnode »

justinclift wrote: post #59
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.

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

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

Re: Inserting a billion rows in SQLite under a minute

#95

Post by pksebben »

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

Re: Inserting a billion rows in SQLite under a minute

#96

Post by [deleted] »

eatonphil wrote: post #19
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 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.

[deleted]

Re: Inserting a billion rows in SQLite under a minute

#97

Post by roman-holovin »

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'.

Re: Inserting a billion rows in SQLite under a minute

#98

Post by nh2 »

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

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

    pragma synchronous = off
https://www.sqlite.org/pragma.html#pragma_synchronous

Re: Inserting a billion rows in SQLite under a minute

#99

Post by gfodor »

roman-holovin wrote: post #97

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'.

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.

Re: Inserting a billion rows in SQLite under a minute

#100

Post by lsuresh »

mirekrusin wrote: post #6

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

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)
Post reply on HN