Show HN: SQLite Transaction Benchmarking Tool
The "sqlite-bench" GitHub project tests SQLite transaction behavior. It provides code, compilation guidelines, and running instructions. To protect SSD, run benchmarks on in-memory filesystem first. Docker image available.
Read original article
The GitHub project "sqlite-bench" is designed for testing SQLite transaction behavior. It offers code for a blog post and guidelines on compiling and executing the benchmark tool. To compile, use `cargo build --release`, and for running, employ `cargo run --release -- --help` to view options. To safeguard your SSD, it's advised to initially run the benchmark on an in-memory filesystem. Instructions for setting up an in-memory filesystem on MacOS and Linux are included. Additionally, a multiplatform Docker image for this project can be accessed at the provided GitHub link.
Related
DB Browser for SQLite (Windows, macOS, and Most Versions of Linux)
DB Browser for SQLite (DB4S) is an open-source tool for SQLite databases, supporting Windows, macOS, Linux, and Unix. It enables table management, data manipulation, SQL queries, and visualization features. Version 3.12.2 is current, with a 3.13.x-rc1 release. Nightly builds are also available for testing.
Storing Scraped Data in an SQLite Database on GitHub
The article explains Git scraping, saving data to a Git repository with GitHub Actions. Benefits include historical tracking and using SQLite for storage. Limitations and Datasette for data visualization are discussed.
First Contact with SQLite
The article explores surprising aspects of SQLite, like limitations in altering columns and data types. It notes the recent jsonb support and handling date/time values, praising SQLite's streaming features but favoring PostgreSQL.
Gotchas with SQLite in Production
SQLite is popular for web apps but has limitations like single-threaded access, network challenges, and constraints in concurrency and transactions. Consider alternatives like MySQL for complex scenarios.
2 commentsSince I don't know where else to ask, maybe this is a good place: How do async wrappers around SQLite (e.g. for node or python) work? SQLite only uses synchronous I/O if I'm not mistaken. Is it just a pretend async function with only synchronous code?
And, as a follow-up: If I have a server with say 100 incoming connections that will all read from the database, I've got 100 readers. No problem in WAL mode. However, I still could get congested by file I/O, right? Because every time a reader is waiting for data from disk, I can't execute the application code of another connection in a different thread since execution is blocked on my current thread. Is there any benefit to having a thread pool with a limit of more than $NUM_CPU readers?
And one more: Would you recommend actually pooling connections or just opening/closing the database for each request as needed? Could keeping a file handle open prevent SQLite from checkpointing under certain conditions?
Related
DB Browser for SQLite (Windows, macOS, and Most Versions of Linux)
DB Browser for SQLite (DB4S) is an open-source tool for SQLite databases, supporting Windows, macOS, Linux, and Unix. It enables table management, data manipulation, SQL queries, and visualization features. Version 3.12.2 is current, with a 3.13.x-rc1 release. Nightly builds are also available for testing.
Storing Scraped Data in an SQLite Database on GitHub
The article explains Git scraping, saving data to a Git repository with GitHub Actions. Benefits include historical tracking and using SQLite for storage. Limitations and Datasette for data visualization are discussed.
First Contact with SQLite
The article explores surprising aspects of SQLite, like limitations in altering columns and data types. It notes the recent jsonb support and handling date/time values, praising SQLite's streaming features but favoring PostgreSQL.
Gotchas with SQLite in Production
SQLite is popular for web apps but has limitations like single-threaded access, network challenges, and constraints in concurrency and transactions. Consider alternatives like MySQL for complex scenarios.