5 ms·

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.

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 5y 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.
- tyingq 5y ago
  Sqlite has a btree module/api, though there's a lot of "TODO:" notes in the document: https://sqlite.org/btreemodule.html https://sqlite.org/btreemodule.html
  - avinassh 5y ago
    TIL, this is great! I think this could make things even easier instead of writing the raw pages
- oldtablet 5y 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.
- avinassh 5y 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?
- deleted 5y ago
  [deleted]
- exikyut 5y ago
  See also https://news.ycombinator.com/item?id=27880920 https://news.ycombinator.com/item?id=27880920
deleted 5y ago
[deleted]
justinclift 5y 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 https://github.com/sqlitebrowser/sqlitedatagen There's some initial work to parallelise it with goroutines here: https://github.com/sqlitebrowser/sqlitedatagen/blob/multi_row_v1/main.go 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 5y 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 https://github.com/rcbensley/brimming
Twirrim 5y 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/ 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 5y 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.
  - nh2 5y ago
    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).
- nh2 5y ago
  Aside: SQLite has native functionality to do what libeatmydata does (disable fsync): pragma synchronous = off https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous
- mikepurvis 5y 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 5y 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".
A4ukYkq6ILl 5y ago
https://www.baidu.com https://www.baidu.com
anigbrowl 5y ago
It's a great article! Always nice to see clear well-written guides that save people time from reinventing the wheel.
Fiahil 5y 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 5y 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 5y 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.
exikyut 5y ago
Potentially interesting: https://github.com/siara-cc/sqlite_micro_logger_arduino https://github.com/siara-cc/sqlite_micro_logger_arduino https://github.com/siara-cc/sqlite_micro_logger_arduino/blob/master/src/ulog_sqlite.c 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.

eatonphil 5y 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.

tyingq 5y ago
Sqlite has a btree module/api, though there's a lot of "TODO:" notes in the document: https://sqlite.org/btreemodule.html https://sqlite.org/btreemodule.html
- avinassh 5y ago
  TIL, this is great! I think this could make things even easier instead of writing the raw pages
oldtablet 5y 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.
avinassh 5y 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?
deleted 5y ago
[deleted]
exikyut 5y ago
See also https://news.ycombinator.com/item?id=27880920 https://news.ycombinator.com/item?id=27880920

deleted 5y ago

[deleted]

justinclift 5y 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 https://github.com/sqlitebrowser/sqlitedatagen There's some initial work to parallelise it with goroutines here: https://github.com/sqlitebrowser/sqlitedatagen/blob/multi_row_v1/main.go 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 5y 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 https://github.com/rcbensley/brimming

Twirrim 5y 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/ 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 5y 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.
- nh2 5y ago
  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).
nh2 5y ago
Aside: SQLite has native functionality to do what libeatmydata does (disable fsync): pragma synchronous = off https://www.sqlite.org/pragma.html#pragma_synchronous https://www.sqlite.org/pragma.html#pragma_synchronous
mikepurvis 5y 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 5y 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".

A4ukYkq6ILl 5y ago

https://www.baidu.com https://www.baidu.com

anigbrowl 5y ago

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

Fiahil 5y 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 5y 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 5y 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.

exikyut 5y ago

Potentially interesting: https://github.com/siara-cc/sqlite_micro_logger_arduino https://github.com/siara-cc/sqlite_micro_logger_arduino https://github.com/siara-cc/sqlite_micro_logger_arduino/blob/master/src/ulog_sqlite.c 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.