fast-sqlite3-inserts
napkin-math
Our great sponsors
fast-sqlite3-inserts | napkin-math | |
---|---|---|
11 | 13 | |
363 | 2,990 | |
- | - | |
0.0 | 6.3 | |
about 1 year ago | 5 days ago | |
Rust | Rust | |
MIT License | MIT License |
Stars - the number of stars that a project has on GitHub. Growth - month over month growth in stars.
Activity is a relative number indicating how actively a project is being developed. Recent commits have higher weight than older ones.
For example, an activity of 9.0 indicates that a project is amongst the top 10% of the most actively developed projects that we are tracking.
fast-sqlite3-inserts
-
SQLite performance tuning: concurrent reads, multiple GBs and 100k SELECTs/s
I am experimenting with SQLite, where I try inserting 1B rows in under a minute. The current best is inserting 100M rows at 23s. I cut many corners to get performance, but the tweaks might suit your workload.
I have explained my rationale and approach here - https://avi.im/blag/2021/fast-sqlite-inserts/
the repo link - https://github.com/avinassh/fast-sqlite3-inserts
-
I/O is no longer the bottleneck
I am working on a project [0] to generate 1 billion rows in SQLite under a minute and inserted 100M rows inserts in 33 seconds. First, I generate the rows and insert them in an in-memory database, then flush them to the disk at the end. To flush it to disk it takes only 2 seconds, so 99% of the time is being spent generating and adding rows to the in-memory B Tree.
For Python optimisation, have you tried PyPy? I ran my same code (zero changes) using PyPy, and I got 3.5x better speed.
I published my findings here [1].
[0] - https://github.com/avinassh/fast-sqlite3-inserts
[1] - https://avi.im/blag/2021/fast-sqlite-inserts/
- Ask HN: Which personal projects got you hired?
-
Is there any language that is as similar as possible to Python in syntax, readability, and features, but is statically typed?
I have a side project where I tried to insert one billion rows in SQLite. I was able to insert 100 million rows using Python under 210 seconds. The same thing with PyPy took 120 seconds. I am wondering what kind of speed boost I would get with Cython
- Ask for benchmark. The owner can’t verify a 18% perf gain, could you?
-
Inserting One Billion Rows in SQLite Under A Minute
Measure, measure, measure! There is a PR which made really minor changes, but it got 2x speed boost with CPython version
- Inserting One Billion Rows in SQLite Under a Minute
- Weekly Coders, Hackers & All Tech related thread - 17/07/2021
-
How we achieved write speeds of 1.4 million rows per second
[somewhat related] Recently, I was benchmarking SQLite inserts and I managed to insert 3.3M records per second (100M in 33 ish seconds) on my local machine - https://github.com/avinassh/fast-sqlite3-inserts Ofcourse the comparison is not apples to apples, but sharing here if anyone finds it interesting
napkin-math
- capacity planning in system design interviews
- Napkin Math
-
S3 Express Is All You Need
Most production storage systems/databases built on top of S3 spend a significant amount of effort building an SSD/memory caching tier to make them performant enough for production (e.g. on top of RocksDB). But it's not easy to keep it in sync with blob...
Even with the cache, the cold query latency lower-bound to S3 is subject to ~50ms roundtrips [0]. To build a performant system, you have to tightly control roundtrips. S3 Express changes that equation dramatically, as S3 Express approaches HDD random read speeds (single-digit ms), so we can build production systems that don't need an SSD cache—just the zero-copy, deserialized in-memory cache.
Many systems will probably continue to have an SSD cache (~100 us random reads), but now MVPs can be built without it, and cold query latency goes down dramatically. That's a big deal
We're currently building a vector database on top of object storage, so this is extremely timely for us... I hope GCS ships this ASAP. [1]
[0]: https://github.com/sirupsen/napkin-math
-
Random Read or Sequential Read
Trying to estimate performance using some napkin math based on this: https://github.com/sirupsen/napkin-math
-
A CVE has been issued for hyper. Denial of Service possible
So napkin maths time. Typical cross-world bog-standard network speeds for a single TCP channel of ~25MiBps. A single HEADERS+RST pair is likely < 128 bytes (40 for the HEADERS + whatever payload, and 32 for the RST). So 8 pairs per K, 8K pairs per MiB, 200K pairs per 25MiB...
- Index Merges vs Composite Indexes in Postgres and MySQL
-
I/O is no longer the bottleneck
Yes, sequential I/O bandwidth is closing the gap to memory. [1] The I/O pattern to watch out for, and the biggest reason why e.g. databases do careful caching to memory, is that _random_ I/O is still dreadfully slow. I/O bandwidth is brilliant, but latency is still disappointing compared to memory.
[1]: https://github.com/sirupsen/napkin-math
- Monthly cost to host server for 1M DAUs?
- Napkin-math: Techniques and numbers for estimating system's performance
-
System Design prep?
https://github.com/sirupsen/napkin-math (memorize these)
What are some alternatives?
tsbs - Time Series Benchmark Suite, a tool for comparing and evaluating databases for time series data
huniq - Filter out duplicates on the command line. Replacement for `sort | uniq` optimized for speed (10x faster) when sorting is not needed.
julia - The Julia Programming Language
advisory-database - Security vulnerability database inclusive of CVEs and GitHub originated security advisories from the world of open source software.
plum - Multiple dispatch in Python
adix - An Adaptive Index Library for Nim
sqlite_micro_logger_arduino - Fast and Lean Sqlite database logger for Arduino UNO and above
h2 - HTTP 2.0 client & server implementation for Rust.
remixdb - RemixDB: A read- and write-optimized concurrent KV store. Fast point and range queries. Extremely low write-amplification.
RAMCloud - **No Longer Maintained** Official RAMCloud repo
dynamic-dns - An automated dynamic DNS solution for Docker and DigitalOcean
simdjson - Parsing gigabytes of JSON per second : used by Facebook/Meta Velox, the Node.js runtime, ClickHouse, WatermelonDB, Apache Doris, Milvus, StarRocks