Hacker News Reader

Comment by avinassh | Hacker News Reader

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.

View on HN

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.

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

    pragma synchronous = off

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

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

For people wondering when this is needed:

Go is one of the programming languages that makes syscalls directly, thus libeatmydata has no effect on it (being an LD_PRELOAD to the libc).

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.

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. ;)

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

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.

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?)

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.

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

TIL, this is great! I think this could make things even easier instead of writing the raw pages

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.

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?

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

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?

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.

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

Reply on news.ycombinator.com