9 ms·
15k inserts/s with Rust and SQLite (2021)
- 32gbsd 4y agoYeah but rust has gotten simpler?
- nindalf 4y agoThey're working on it. For example, knowing that you can use async in some contexts (functions) but not in others (traits) is complexity that needs to be learned. By making it work everywhere, the language becomes simpler. 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.
- candiddevmike 4y agoI thought there was contention within the community about async?
- nindalf 4y agoI think async exists and it works well. There were 2 equally popular runtimes earlier but now there's only 1 that everyone uses. I wouldn't characterise that as contention, just people trying different stuff out.
- galangalalgol 4y agoI still think async/await is a bit of a fad outside web services, and one I can't ignore if I want to use some popular crates.
- vlovich123 4y agoTokio is super popular but there are still better runtimes at the moment. The proper design you want is thread per core with some background pool of threads available for work stealing / batch jobs. The reason is that you can completely remove the need for any locking in your critical path. I think tokio is working on making that paradigm available but I don’t know how that’s progressing
- FridgeSeal 4y agoFor what it’s worth, Glommio is thread-per-core async executor library in Rust. Comes with io_uring support too. Not as widely supported as tokio is, obviously, but still viable enough, worth keeping an eye on IMO.
- vlovich123 4y agoYes that’s what I was referring to. I’m using that. ByteDance has a similar one although I haven’t tried it. The annoying thing is how deeply a specific async implementation “infects” all parts of the code. Would be nice for something more general so I could more easily test out individual implementations to compare performance vs having to rewrite a good chunk of the application.
- FridgeSeal 4y agoOh cool. 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.
- timeon 4y agoFor me, everyday.
- deleted 4y ago[deleted]
- jeffbee 4y agoAnyone able to replicate this? With XFS on an NVMe device I only get 500 inserts per second. I wonder if the 15000 figure is attributable to the scaleway virtual server SCSI device ignoring syncs.
- nordsieck 4y ago> pragma temp_store = memory; Did you do this?
- cldellow 4y agoThey're using WAL and synchronous = NORMAL, so there shouldn't be that many syncs to begin with. 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.
- antonvs 4y agoUsing the code from the repo, I got this on a standard Dell XPS laptop, running Ubuntu 20.04.1 with ext4 on an NVMe: $ cargo run --release -- -c 100 -i 100000 ... Inserting 100000 records. concurrency: 100 Time elapsed to insert 100000 records: 7.939734809s (12594.88 inserts/s)
- 4y ago
- fooker 4y agoIs that a lot?
- natas 4y agoDoesn't strike me as a lot honestly.
- TinkersW 4y ago15 per millisecond sounds horrible.. and it looks like it is actually using 3 threads so its even worse..
- gnuvince 4y agoIt's not enormous, but it's good for the simplicity and would probably be sufficient for quite a number of use cases. And I'd guess that it's also probably more than what many programmers get by Lego stacking 14 different abstractions.
- shpx 4y agoThis is running on an AMD EPYC 7543 CPU, which has 32 cores that can each cycle 2,794,750,000 times per second. It's running with 3 threads (if I understand correctly), so as a rough estimate it's doing one insert every 500,000 CPU cycles (2,794,750,000 * 3 / 15000).
- dilyevsky 4y agoPretty sure cpu is not a limiting factor here
- VWWHFSfQ 4y ago15k inserts/second is basically nothing. This is just a batch load of INSERT statements. Now do it while simultaneously serving 15k selects/second. Now try it with 15k counts/second. You'll see where this breaks down fast.
- ijidak 4y agoCan you qualify your statement that this is nothing? How many CPUs and cores are available? Are we talking SSDs? How much data is being inserted? What are the keys and index? 15,000 per second seems like plenty much in some contexts.
- andrewstuart 4y ago15K doesn't seem particularly noteworthy. 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.
- gomezjdaniel 4y ago> I made a prototype message queue in Rust that processed about 7 million messages/second. That was running from RAM. Can you share the source code?
- andrewstuart 4y agoIt was pure garbage - a failed attempt to learn Rust.
- re-thc 4y agoNeed some garbage collection added?
- HopenHeyHi 4y agoIs 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. I don't understand. Using transactions and prepared statements you would get hundreds of thousands of inserts per second even using languages like python/ruby. 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://www.sqlite.org/faq.html#q19 https://rogerbinns.github.io/apsw/tips.html#sqlite-is-different https://rogerbinns.github.io/apsw/tips.html#sqlite-is-differ...
- morelisp 4y agoIt would not be real-world in the sense that most OLTP workloads are inserting (or updating) only a couple rows at a time. In this sense it provides an upper bound on transactions per second, which for many real-world workloads essentially means requests per second (or requests*n per second for small n). 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.
- trollied 4y agoNo idea why this is on the front page. Doesn't seem like anything special. People seem to blindly upvote anything "with Rust", and I've no idea why.
- deleted 4y ago[deleted]
- berkle4455 4y agoIt's also that most devs don't understand reasonable estimates on how many inserts/sec a database should be able to handle or how many requests/sec a website should be able to handle, and they upvote and wax estatic about some of the most unimpressive stats ever.
- 0xfedbee 4y agoIt’s a known tactic to get anything on HN frontpage. Just put Rust in the title, even if it’s about a food recipe.
- web3-is-a-scam 4y agoAnything with Rust or SQLite is basically instant front page.
- re-thc 4y agoAnd anything opposing it is instant downvote :p
- klabb3 4y agoDecided to run this with NodeJS (on Linux) for mandatory Web Scale: 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.
- sroussey 4y agoTry again with bun. It uses a baked-in SQLite silicate to better-sqlite3. https://bun.sh https://bun.sh
- klabb3 4y agoWeird, I just ran some benchmarks with bun a few days ago for my own purposes, and I concluded that their marketing slogan was bs and Node was faster. 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.
- Jarred 4y agoWhat benchmark did you run where Node outperformed Bun? Happy to take a look. 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).
- klabb3 4y ago
- sjc02060 4y agoThe botteneck is on sqlite, you'll get the same performance with any programming language. Rust had nothing to do with this.
- nprescott 4y agoThis reminded me of a prior discussion[0] on bulk data generation in SQLite with Rust (vs Python vs PyPy) which previously led me to trying out two different techniques using just SQLite[1]. The approach here is so similar I tried my prior solution on the slowest VPS I have access to (1 vCPU core 2.4GHz, 512Mi, $2.50/month from vultr): 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 where the UUID extension comes from the SQLite authors[2] and generate_series is compiled into the SQLite CLI. It is possible further pragma-tweaking might eke out further performance but I feel like this representative of the no-optimization scenario I typically find myself in. 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 Clearly UUIDs are not free! [0]: https://news.ycombinator.com/item?id=27872575 https://news.ycombinator.com/item?id=27872575 [1]: https://idle.nprescott.com/2021/bulk-data-generation-in-sqlite.html https://idle.nprescott.com/2021/bulk-data-generation-in-sqli... [2]: https://sqlite.org/src/file/ext/misc/uuid.c https://sqlite.org/src/file/ext/misc/uuid.c
- deleted 4y ago[deleted]
- FpUser 4y agoTranslation - SQLite is capable of 15K batch uniform inserts/s under some configuration. I have no clue what is so impressing here. And what magical potion Rust adds to this result?
- pornel 4y agoThe article is not meant to show off how fast it is, but how easy it is to achieve a good-enough level of performance without anything special. It's meant to show that you don't need specialized "infinitely scalable" services.
- FpUser 4y ago>"It's meant to show that you don't need specialized "infinitely scalable" services." 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.
- antonvs 4y agoYou're correct. Posts like these are made by people who haven't actually tried to scale the approaches they're advocating.
- FpUser 4y agoI think articles like this are written to gain some points for employment purpose. If I was hiring however this type of article would be a net negative as it leaves me with the impression that the author has no clue about real life situations.
- andrewstuart 4y agoTLDR: C++ is 115K inserts/sec to 166K inserts/sec 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; }
- szundi 4y agoI had 30k with PostgreSQL 6.5.3 like in 1999 on a PIII-400 or something like that.
- d1l 4y agoThis is so lazy as to be irritating to me. Magic pragmas with no explanation, no use of the important ones if you're going for max performance (synchronous and journal mode), no discussion of statement compilation and the benefits of plan caching, no discussion of indexes or transactions... Just garbage to make a lazy point that maybe SQLite is fast.
- cldellow 4y agoI agree that there's lots of room for improvement in both the benchmark and the exposition around it, but I disagree with: > 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);
- thagsimmons 4y agoWe chose sqlx for a SQLite based project because it provides a reasonable async interface, and our experience has not been great. Development momentum has been poor, PRs and issue reports are languishing, the developer is unresponsive to ergonomic and technical feedback, sqlite doesn't seem to be a priority, and the fundamental mechanism for compile-time query validation is very, very flawed. As our project has become more important to us, we've become more and more tempted to write our own SQLite library (maybe forking some parts of sqlx).
- maxbond 4y agoHave you checked out rusqlite? No affiliation, just curious.
- kayson 4y agoHow is this not completely I/O bound? What's so cpu intensive that programming language matters?
- zaphar 4y agoThis isn't about language use. This is about what is possible with a simple setup. Single service in language of your choice and just sqlite. The idea being that unless there is a good reason you probably don't need k8s, A high availability database server cluster, and a full on proxy routing layer. Adding more layers of architecture comes with costs and unless you get something essential in return for paying that cost you should probably not bother. You may in fact be able to get by with a single server backed by sqlite and maybe litestream for backups and if that is the case then there isn't any reason not to and a lot of reasons to do so.
- kayson 4y agoAh. Thanks for the context
- npn 4y agoI work with sqlite a lot. Usually with millions rows. The last time I check my script written in crystal which read ten millions entries from the disk, remove the duplication then write the result to sqlite3 database file and it all happen in 70 seconds or so. 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.
- Shorel 4y agoHow many MB per second is this? 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.