https://github.com/chrisdavies/dbench Skip to content Sign up * Why GitHub? + Features + Mobile + Actions + Codespaces + Packages + Security + Code review + Issues + Integrations + GitHub Sponsors + Customer stories * Team * Enterprise * Explore + Explore GitHub + Learn and contribute + Topics + Collections + Trending + Learning Lab + Open source guides + Connect with others + The ReadME Project + Events + Community forum + GitHub Education + GitHub Stars program * Marketplace * Pricing + Plans + Compare plans + Contact Sales + Education [ ] * # In this repository All GitHub | Jump to | * No suggested jump to results * # In this repository All GitHub | Jump to | * # In this user All GitHub | Jump to | * # In this repository All GitHub | Jump to | Sign in Sign up {{ message }} chrisdavies / dbench Public * Notifications * Fork 0 * Star 14 * An unscientific benchmark of SQLite vs the file system (btrfs) 14 stars 0 forks Star Notifications * Code * Issues 0 * Pull requests 0 * Actions * Projects 0 * Wiki * Security * Insights More * Code * Issues * Pull requests * Actions * Projects * Wiki * Security * Insights This commit does not belong to any branch on this repository, and may belong to a fork outside of the repository. main Switch branches/tags [ ] Branches Tags Could not load branches Nothing to show {{ refName }} default View all branches Could not load tags Nothing to show {{ refName }} default View all tags 1 branch 0 tags Code Latest commit Christopher Davies Add note about EXT4. ... f1b3dfd Feb 12, 2022 Add note about EXT4. f1b3dfd Git stats * 6 commits Files Permalink Failed to load latest commit information. Type Name Latest commit message Commit time .gitignore Linear writes Feb 12, 2022 dbench Test w/ ext4 Feb 12, 2022 go.mod Linear writes Feb 12, 2022 go.sum Linear writes Feb 12, 2022 main.go Test w/ ext4 Feb 12, 2022 readme.md Add note about EXT4. Feb 12, 2022 View code [ ] DBENCH Linear writes Concurrent writes A more realistic test More resilient file writes Conclusion EXT4 Footnotes readme.md DBENCH Basic benchmarks for SQLite vs file system (btrfs on a 2020 Dell XPS SSD). Linear writes 10k inserts, written in a tight loop. SQLite 647ms, 809ms, 708ms BTRFS 393ms, 371ms, 382ms For sequential writes, the file system is 1.8x faster. Concurrent writes Here, we're going to crank up the concurrency and have 100 concurrent writers, each writing 1K records. SQLite will serialize these under the hood. The file system will do whatever it does. SQLite 8.2s, 8.1s, 8.5s BTRFS 3.1s, 3.4s, 3.4s For concurrent writes, the file system is 2.4x faster. This is not terribly surprising, as SQLite does not handle concurrent writes. To be honest the performance gap here is smaller than I'd have guessed. * SQLite can write around 12K inserts per second * BTRFS can write around 30K files per second A more realistic test The real-world application I'll be writing would have a bit more structure and more indices. So, I think the next test will be to run N tasks from start to finish: insert, update status, update progress, delete. For this, we'll want to index by status, and in the real world, I'd probably also index by scheduled_at so that we can handle job scheduling efficiently. If I were to use the file system for this, I'd keep the queues and schedules in memory, and rebuild it when the application starts. I've tested the in-memory approach, and it's blisteringly fast (millions of ops per second, given proper care). Concurrency: 100, each running a simulation of 100 tasks (create, change status, update "output" 10 times, delete): SQLite 11.5s, 10.3s, 7.8 BTRFS 3.3s, 3.2s, 2.4s In this test, the file system was 3.3x faster than SQLite. This surprises me, since we're writing the entire file each time vs SQLite presumably being able to do more optimal, in-place updates (though a variety of things may mean that's not actually happening). Let's try again. This time, we'll do proper file writes (write to a tmp file, rename to overwrite the current file). This should be a bit more crash-resistant, though for my use case, it probably doesn't matter a whole lot if one or two tasks fail due to crashes once or twice a year. More resilient file writes Interesting. This time, I modified my file simulation to write to a tmp file first, then rename it to overwrite the existing file. This tweak caused the file simulation to be a bit slower than SQLite: SQLite 9.6s, 9.6s, 7.9s BTRFS 13.5s, 13.3s, 10.2s This makes me think that probably my initial file tests weren't waiting for fsync, but the rename forces the application to wait. I'm not sure. Another interesting thing I've noticed is that SQLite seems to speed up a bit as it goes along. Here's another run, just with SQLite: SQLite ran 10k tasks in 10.83936393s SQLite ran 10k tasks in 10.27817409s SQLite ran 10k tasks in 8.891015857s SQLite ran 10k tasks in 6.528546715s SQLite ran 10k tasks in 6.738008705s SQLite ran 10k tasks in 6.917476809s It seems to have a warm up phase or something. Eeeenterestink. Conclusion For a my real(ish) world scenario, SQLite-- once warmed up-- is roughly twice as fast as the file system. I'm not sure which I'll end up going with, but I think it'll be SQLite. The devops part of me has a slight preference for using the file system, as I can use basic tools (grep, ls, etc) to check on things. The dev part of me definitely pefers SQLite, as I can let it take care of loads of things for me that I'd otherwise have to do myself, and I can trivially query for stats, etc. EXT4 Someone on Hacker News suggested I try a faster file system. Here's what a $5 Linode running Ubuntu Server and EXT4 got me (running 10K simulations, which is 1 insert, 11 updates, and 1 delete): SQLite 8.7s, 8.0s, 8.2s, 8.3s, 8.0s, 8.5s EXT4 18.3s, 17.3s, 16.6s, 15.7s, 16.1s So. Same conclusion. Footnotes * I used mattn/go-sqlite3 + These were the settings: ./tmp.db?_timeout=5000&_journal=WAL& _sync=1 * The project is here * In a separate project, I ran tests vs Postgres and found the performance was roughly the same as SQLite's worst performance when Postgres is hosted on the same machine. About An unscientific benchmark of SQLite vs the file system (btrfs) Resources Readme Stars 14 stars Watchers 1 watching Forks 0 forks Releases No releases published Packages 0 No packages published Languages * Go 100.0% * (c) 2022 GitHub, Inc. * Terms * Privacy * Security * Status * Docs * Contact GitHub * Pricing * API * Training * Blog * About You can't perform that action at this time. You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.