PRAGMA page_size 65536
Inserting a billion rows in SQLite under a minute
131–140 of 164 posts
Re: Inserting a billion rows in SQLite under a minute
#132PRAGMA page_size 65536
But ensure this is actually in effect (e.g. do vacuum, etc.)
Re: Inserting a billion rows in SQLite under a minute
#133Earlier quoted context omitted.
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.
Yes, that approach makes sense. I thought what I was replying to was suggesting writing b-tree pages themselves, outside of sqlite, for the new data.
I guess if you wanted the fastest creation you could make a custom backend format for sqlite and use that. Especially if query speed was not important.
Re: Inserting a billion rows in SQLite under a minute
#134.. or the throughput of SQLite acting in pure fwrite(3) mode with a big buffer.
Re: Inserting a billion rows in SQLite under a minute
#135Re: Inserting a billion rows in SQLite under a minute
#136Earlier quoted context omitted.
Are you suggesting have a template and then updating rows with random values where necessary?
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…
Assuming SQLite has any internal counters or indexes, let alone B-trees or anything fancy, then this approach won't work. It will work for a raw record format (CSV, JSON, Protobuf, an mmapped array, etc.), but the author wants to actually interact with a real SQLite database. Generating a billion rows in some non-SQLite format still leaves problem to converting that format by loading it into SQLite, which isn't really a reduction of the original problem.
Re: Inserting a billion rows in SQLite under a minute
#137Isn't writing to a MMAP file faster?
Re: Inserting a billion rows in SQLite under a minute
#138INSERT 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'.
This is with in memory database and journaling disabled?
Re: Inserting a billion rows in SQLite under a minute
#139Hey 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/…
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.
Re: Inserting a billion rows in SQLite under a minute
#140For 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).