100k inserts using `better-sqlite3` (difference: it does use prepared statements), all single-threaded with the same pragmas.

- 8.6k inserts/s with `uuid` and `Date.toUTCString()`

- 12.0k inserts/s with faking uuid and date (generated unique strings)

...So a large amount of time was spend in uuid generation and current date.

EDIT: I mixed up the two numbers above. Simple strings is of course faster than syscall+date format+entropy. Fixed now.

It's fair to say that although Rust is slightly more Web Scale, this benchmark simply measures the excellent performance of SQLite.

https://bun.sh

However, I did it again with this example and Bun is indeed significantly faster

- 15.1k inserts/s with `uuid` and `Date.toUTCString()`

- 20k inserts/s with faking uuid and date (generated unique strings)

Bun is now scientifically at the Rust level of Web Scale, which is pretty crazy. I wonder how they do that, because it's still just v8 under the hood? Mayhaps their sqlite driver is faster.

There are still "obvious" things we need to fix, like our EventEmitter implementation is currently around 3x slower than Node due to C++ <> JS callback overhead and several functions in node:crypto are still using JS-only polyfills intended for web browsers (instead of BoringSSL/OpenSSL).

It was a very similar SQLite bench but I may have done something wrong.

> Happy to take a look.

That’s ok but let me bait and switch you for this dead socket leak issue: https://github.com/oven-sh/bun/issues/1397

In general, I’ve found that tons of projects suffer leaks. People don’t care about leaks :( (or alternative the tooling for discovering them kinda sucks)

Now do it while simultaneously serving 15k selects/second.

Now try it with 15k counts/second. You'll see where this breaks down fast.

    CREATE TABLE IF NOT EXISTS users (
        id BLOB PRIMARY KEY NOT NULL,
        created_at TEXT NOT NULL,
        username TEXT NOT NULL
    );

    let user = User {
        id: uuid::Uuid::new_v4(),
        created_at: chrono::Utc::now(),
        username: String::from("Hello"),
    };

I really like SQLite. But this blog isn't any kind of great performance indicator for it.

    insert into users (id, created_at, username) values ("<id>", "<created_at>", "Hello"), ...15,000 times.

Disk I/O has always been the bottleneck for writes.

That said, it is increasingly rare for disk I/O to be a bottleneck for inserts. When your storage will happily sustain 10+ GB/s of write throughput, you tend to run out of things like network and memory bandwidth first.

For example, if the whole world wanted to create an account, and an account consisted of a UUID and a date, it'd take 5.4 days using this metric as a minimum viable benchmark.

It's all about context, perception. 15,000 seems like a large number, and 1 second seems like a small amount of time. On the other hand, 5.4 days seems like quite a large number. Likewise at small scales, 15,000/second operated at a fidelity only able to capture 0.0005% of a typical processor's clock.

Like a picture in a frame.

This is not to say that the ability to casually write 15,000 things a second to a database is not impressive. Nor that a beowulf cluster of it wouldn't be.

I made a prototype message queue in Rust that processed about 7 million messages/second. That was running from RAM.

I also had a prototype that ran from disk and it very rapidly maxed out the random write performance of the SSD. Can't remember the number but it was in the order of 15K to 30K messages a second, bottlenecked by the SSD.

If the sqlite test is writing to disk it is likely also bottlenecked at the disk.

Can you share the source code?

  Is it possible to improve this micro benchmark? Of course, by bundling all the inserts in a single transaction, for example, or by using another, non-async database driver, but it does not make sense as it's not how a real-world codebase accessing a database looks like. We favor simplicity over theorical numbers.

Nothing theoretical or not-real-world about it. It is how SQLite docs recommend you do it.

https://www.sqlite.org/faq.html#q19

https://rogerbinns.github.io/apsw/tips.html#sqlite-is-differ...

However, it's still kind of weak because you really need to interleave those with SELECTs and some UPDATEs to get a decent view of how it would perform in such a case.

> no use of [...] synchronous and journal mode

They enable both of these when configuring the connection:

    let connection_options = SqliteConnectOptions::from_str(&database_url)?
        .create_if_missing(true)
        .journal_mode(SqliteJournalMode::Wal)
        .synchronous(SqliteSynchronous::Normal)
        .busy_timeout(pool_timeout);

By default, I think it'd sync only on checkpoints, and a checkpoint would happen for every 1000 pages of WAL. 1000 is the default value of the wal_autocheckpoint pragma.

I am very surprised that you cannot reproduce more than 500 inserts/second on an NVMe drive with the same settings. I didn't try running this benchmark, as I'm not familiar with rust, but I've achieved ~15k/sec write transactions with python bindings under similar settings.

    $ cargo run --release -- -c 100 -i 100000
    ...
    Inserting 100000 records. concurrency: 100
    Time elapsed to insert 100000 records: 7.939734809s (12594.88 inserts/s)

Did you do this?

  15 per millisecond sounds horrible.. and it looks like it is actually using 3 threads so its even worse..

And what magical potion Rust adds to this result?

1) This "revelation" has been known to any decent programmer for ages.

2) The example is very contrived and has zero real practical value as the access pattern, amount of tables involved, constraints and ACID requirements in real life are very different.

And I didn't have to do any fancy trick like in this article, just setting `journal_mode=WAL` and `synchronous=normal` was enough.

p/s: yeah transaction helps a lot.

    sqlite> create table users (id blob primary key not null, created_at text not null, username text not null);
    sqlite> create unique index idx_users_on_id on users(id);
    sqlite> pragma journal_mode=wal;
    sqlite> .load '/tmp/uuid.c.so'
    sqlite> .timer on
    sqlite> insert into users(id, created_at, username)
            select uuid(), strftime('%Y-%m-%dT%H:%M:%fZ'), 'hello'
            from generate_series limit 100000;
    Run Time: real 1.159 user 0.572631 sys 0.442133

In the interest of finding where the bulk of the time is spent and on a hunch I tried swapping the UUID for plain auto-incrementing primary keys as well:

    sqlite> insert into users(created_at, username) select strftime('%Y-%m-%dT%H:%M:%fZ'), 'hello' from generate_series limit 100000;
    Run Time: real 0.142 user 0.068090 sys 0.025507

[0]: https://news.ycombinator.com/item?id=27872575

[1]: https://idle.nprescott.com/2021/bulk-data-generation-in-sqli...

[2]: https://sqlite.org/src/file/ext/misc/uuid.c

Here is a totally unscientific benchmark in C++. Of course there is "lies, damned lies and benchmarks".

AMD Ryzen 9 5900X 12-Core Processor

./sqlitetest

For writing to disk: Inserted 1500000 records in 13 seconds, at a rate of 115385 records per second.

For writing to RAM: change users.db to :memory: Inserted 1500000 records in 9 seconds, at a rate of 166667 records per second.

Credit goes not to me but to everyone's favorite AI programmer.

I've made a tiny attempt to optimise - likely much more can be done.

To compile on Linux:

g++ sqlitetest.cpp -lsqlite3 -luuid -o sqlitetest

    #include <iostream>
    #include <cstring>
    #include <ctime>
    #include <chrono>
    #include <sqlite3.h>
    #include <uuid/uuid.h>
    
    struct User {
        uuid_t id;
        char created_at[25];
        char username[6];
    };
    
    int main() {
        sqlite3 *db;
        sqlite3_open(":memory:", &db);
    
        const char *sql = "CREATE TABLE IF NOT EXISTS users ("
                          "id BLOB PRIMARY KEY NOT NULL,"
                          "created_at TEXT NOT NULL,"
                          "username TEXT NOT NULL"
                          ");";
        sqlite3_exec(db, sql, nullptr, nullptr, nullptr);
    
        sqlite3_exec(db, "BEGIN", nullptr, nullptr, nullptr);
    
        User user;
        char uuid_str[37];
        time_t now = time(nullptr);
    
        int count = 0;
        auto start = std::chrono::steady_clock::now();
    
        for (int i = 0; i < 1500000; i++) {
            uuid_generate(user.id);
            uuid_unparse_lower(user.id, uuid_str);
            strftime(user.created_at, sizeof(user.created_at), "%Y-%m-%d %H:%M:%S", localtime(&now));
            strncpy(user.username, "Hello", sizeof(user.username));
            sqlite3_stmt *stmt;
            sqlite3_prepare_v2(db, "INSERT INTO users (id, created_at, username) VALUES (?, ?, ?);", -1, &stmt, nullptr);
            sqlite3_bind_blob(stmt, 1, user.id, sizeof(user.id), SQLITE_STATIC);
            sqlite3_bind_text(stmt, 2, user.created_at, -1, SQLITE_STATIC);
            sqlite3_bind_text(stmt, 3, user.username, -1, SQLITE_STATIC);
            sqlite3_step(stmt);
            sqlite3_finalize(stmt);
            count++;
        }
    
        sqlite3_exec(db, "COMMIT", nullptr, nullptr, nullptr);
    
        auto end = std::chrono::steady_clock::now();
        auto elapsed = std::chrono::duration_cast<std::chrono::seconds>(end - start).count();
        double rate = static_cast<double>(count) / elapsed;
    
        std::cout << "Inserted " << count << " records in " << elapsed << " seconds, at a rate of " << rate << " records per second." << std::endl;
    
        sqlite3_close(db);
    
        return 0;
    }

A commit on every insert would definitely crush your performance. That's one of the worst ways to do bulk inserts.

Really the whole thing is kind of pointless because there are so many factors. It's not about the language, it's more about sqlite and its configuration.

I was inserting data at 5MB/s using MySQL and C++ some years ago.

MySQL was the bottleneck, the C++ part could potentially run many times faster.

But no, there will always be some inherent complexity in the language because it has new concepts not present elsewhere. Newer tutorials in novel formats are being made all the time and they help with learning, but there's no way around having to learn new things.

Yeah I 100% agree, more executor-neutral API’s would be amazing. Especially for stuff like networking client libs, I wish hyper, Axum, tower, tonic, etc were all executor-neutral, or at least provided executor specialisations via features or something.

Plus the top comment gave useful additional data.

This saves time for other people looking to make an informed decision.

---

People disliking Rust seem to always be on the lookout for the Boogeyman to me. And degrading the quality of HN's comment sections. :(

Just look at your other replies. Seems that several people got lost on their way to 4chan and Reddit.

RE my posts, I'm old. I ask questions, I challenge things I think are wrong. I also praise. This is normal interaction, rather than blind cargo cult following.

What ticks me off is that people single out Rust and that's very visible. Go criticize C/C++ legacy baggage and you'll get a very different engagement. Say something about Python and Golang. Very different responses.

At this point and to me, as a mostly indifferent side observer, people negatively overreact if Rust is praised -- and only cite a bunch of loonies that even the Rust devs and core contributors dislike.

Does not seem like a fair and objective assessment to me.

As techies we should be much better than that. Merit before bias and all that.

Just move on. Rust will pass eventually into the realm of boring and it will be "with Fooboz" or something

I'm waiting for someone to transpile MySQL to PHP to see what the limits of HN upvotes are, however.