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

151–160 of 164 posts

Re: Inserting a billion rows in SQLite under a minute

#151

Post by teekert »

tcbasche wrote: post #146
teekert wrote: post #145

Webapps (or webapp frameworks) like Nextcloud and Django, come, by default, with SQLite. That makes it easy to start, but they all warn you: When you get serious, get a real DB like MariaDB or PostgreSQL... And with stuff like this I wonder, is this really necessary? I really like SQLite, it's well supported in Python, backup-restore is very simple. What is the real difference (probably application depended) between…

I guess the idea is you can scale the app independently of the DB. How do you scale out a single file? (Genuinely curious)

Hmm, never thought of this :) I come more from a selfhosting angle. And my django website is prototype only so far… But it is a point I never considered so thanx.

I wonder at what scales this starts to count (in human readable units ;))

Re: Inserting a billion rows in SQLite under a minute

#152

Post by WhyNotHugo »

svdr wrote: post #28

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

This depends a lot.

For English-speaking countries, 1B = 1M * 1k

For Spanish-speaking countries, 1B = 1M * 1M

For other languages it's a big "it varies", though the second definition seems to be the most common. The term "billion" is honestly, as ambiguous as using "06-03" for a date.

Also note that, historically, English also followed the second definition, so for old literature it's also confusing.

Re: Inserting a billion rows in SQLite under a minute

#153

Post by discordance »

teekert wrote: post #145

Webapps (or webapp frameworks) like Nextcloud and Django, come, by default, with SQLite. That makes it easy to start, but they all warn you: When you get serious, get a real DB like MariaDB or PostgreSQL... And with stuff like this I wonder, is this really necessary? I really like SQLite, it's well supported in Python, backup-restore is very simple. What is the real difference (probably application depended) between…

This spells out where it’s appropriate and where it isn’t:

https://www.sqlite.org/whentouse.html

Re: Inserting a billion rows in SQLite under a minute

#155

Post by roman-holovin »

nojvek wrote: post #138
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'.

This is with in memory database and journaling disabled?

I have used pragmas from the article.

Since it is only 100M rows, it takes 1.8 GB on the disk, so I've used tmpfs for this which essentially is a ramdisk. But I have a gen4 pcie nvme SSD - it can reliably write at 4GB/s sequentially, so writing takes a ~500ms for 100M rows, it is not a bottleneck here. random() takes ~half of the insert time. Generating those values with Rust, for example, is faster, but sharing this data with sqlite takes more time than generating it with random().

Maybe implementing custom virtual table in C or Rust like build-in generate_series, but the one that will produce user table fields will be faster, but that is significantly more effort than my query.

This query with random() and generate_series executed in sqlite CLI takes whooping 8MB of the RAM, so you don't even have to close all Electron-based applications to run it on a computer with 8GB of RAM.

Re: Inserting a billion rows in SQLite under a minute

#156

Post by teekert »

discordance wrote: post #153
teekert wrote: post #145

Webapps (or webapp frameworks) like Nextcloud and Django, come, by default, with SQLite. That makes it easy to start, but they all warn you: When you get serious, get a real DB like MariaDB or PostgreSQL... And with stuff like this I wonder, is this really necessary? I really like SQLite, it's well supported in Python, backup-restore is very simple. What is the real difference (probably application depended) between…

This spells out where it’s appropriate and where it isn’t: https://www.sqlite.org/whentouse.html

Thanx!

Re: Inserting a billion rows in SQLite under a minute

#157

Post by patrec »

nojvek wrote: post #140

For 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 bottlenecke…

> A modern SSD can deliver ~500MB/s write speed.

More like ~5GB/s.

Re: Inserting a billion rows in SQLite under a minute

#158

Post by nh2 »

nojvek wrote: post #138
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'.

This is with in memory database and journaling disabled?

My run on NixOS 21.05 with sqlite 3.35.2, without randomness, on an i7-7500U:

Invocation:

    command time sqlite3 ':memory:' '
    create table IF NOT EXISTS user
    (
        id INTEGER not null primary key,
        area CHAR(6),
        age INTEGER not null,
        active INTEGER not null
    );
    INSERT INTO user (area, age, active) SELECT 0, 1, 2 FROM generate_series(1, 100000000, 1);
    '
Result:

    16.34user 0.43system 0:16.89elapsed 99%CPU (0avgtext+0avgdata 1477320maxresident)k
    11inputs+0outputs (0major+369851minor)pagefaults 0swaps
Invocation with pragmas:

    command time sqlite3 ':memory:' '
    PRAGMA journal_mode = OFF;
    PRAGMA synchronous = 0;
    PRAGMA cache_size = 1000000;
    PRAGMA locking_mode = EXCLUSIVE;
    PRAGMA temp_store = MEMORY;
    create table IF NOT EXISTS user
    (
        id INTEGER not null primary key,
        area CHAR(6),
        age INTEGER not null,
        active INTEGER not null
    );
    INSERT INTO user (area, age, active) SELECT 0, 1, 2 FROM generate_series(1, 100000000, 1);
    '
Result with pragmas:

    17.31user 0.41system 0:17.85elapsed 99%CPU (0avgtext+0avgdata 1477288maxresident)k
    11inputs+0outputs (0major+369850minor)pagefaults 0swaps
As expected, the pragmas make no difference when using `:memory:` -- 17 seconds, 1.4 GB RAM each on my laptop.

Re: Inserting a billion rows in SQLite under a minute

#159

Post by nh2 »

the8472 wrote: post #73
Twirrim wrote: post #71

Earlier quoted context omitted.

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.

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

Re: Inserting a billion rows in SQLite under a minute

#160

Post by CRConrad »

jtvjan wrote: post #83
motogpjimbo wrote: post #76

Earlier quoted context omitted.

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.

In 1974, the UK government abandoned the long scale (million, milliard, billion, billiard) in favor of the short scale (million, billion, trillion, quadrillion) used in the US. The long scale is still used in languages like Dutch, German, and French.

And in 2001, The 51st State was released...

https://en.wikipedia.org/wiki/The_51st_State

Post reply on HN