add a benchmark for SQLite storage
- ID
fc136b6- date
2026-11-05 21:42:27+00:00- author
Alex Chan <alex@alexwlchan.net>- parent
53e3da3- message
add a benchmark for SQLite storage- changed files
3 files, 183 additions
Changed files
pyproject.toml (1403 → 1504)
diff --git a/pyproject.toml b/pyproject.toml
index 33ae166..07242c9 100644
--- a/pyproject.toml
+++ b/pyproject.toml
@@ -23,6 +23,9 @@ dependencies = []
dynamic = ["version"]
license = "MIT"
+[project.optional-dependencies]
+xxhash = ["xxhash"]
+
[project.urls]
"Homepage" = "https://alexwlchan.net/projects/funkycache/"
"Changelog" = "https://alexwlchan.net/projects/funkycache/releases/"
@@ -39,6 +42,9 @@ nitrate = ["py.typed"]
[tool.coverage.run]
branch = true
source = ["funkycache", "tests"]
+omit = [
+ "tests/bench_sqlite_storage.py",
+]
[tool.coverage.report]
show_missing = true
tests/bench_sqlite_storage.csv (0 → 2572)
diff --git a/tests/bench_sqlite_storage.csv b/tests/bench_sqlite_storage.csv
new file mode 100644
index 0000000..539543e
--- /dev/null
+++ b/tests/bench_sqlite_storage.csv
@@ -0,0 +1,12 @@
+size,identity (insert),identity (lookup),uncached_md5 (insert),uncached_md5 (lookup),cached_md5 (insert),cached_md5 (lookup),uncached_xxh64 (insert),uncached_xxh64 (lookup),cached_xxh64 (insert),cached_xxh64 (lookup)
+16,0.0038624166627414525,0.00485716665086026,0.005556194829599311,0.006441742666841795,0.006859785004053265,0.0052007776297007995,0.004092097515240312,0.005120131827425212,0.0053156736539676785,0.005245882164066036
+32,0.003951777510034542,0.004894576345880826,0.005495701334439218,0.006497819306484113,0.007224242979039748,0.005240895998819421,0.004107569499562184,0.005131909662547211,0.005992506805341691,0.005247278022579849
+64,0.004214624990709126,0.005011125021458914,0.005838840501382947,0.006989409681409597,0.007004062334696452,0.00558196532074362,0.0041123263266248005,0.005105013803889354,0.006183118132563929,0.0051294931715043885
+128,0.004661117816188683,0.005136756653276582,0.006187687686178833,0.0071968194873382645,0.008093347190879285,0.005237958491003762,0.004141048664071907,0.005216729167538385,0.00531172218810146,0.005182729199683915
+256,0.005215507194710274,0.005411124827029805,0.007067569667318215,0.008022812311537564,0.008449749982294938,0.005350138681630294,0.004216985815825562,0.005270958490048845,0.0058110486522006495,0.005208895813363294
+512,0.006894416699651629,0.006411500178122272,0.008901784662157297,0.009872840309981257,0.010354131828838339,0.00548740280404066,0.004577423639905949,0.005607034855832656,0.005999208020512015,0.005221687334900101
+1024,0.015626360992124926,0.014198617854466042,0.012006506808878234,0.013119458182093998,0.01374636796147873,0.005391902833556135,0.004580388660542667,0.005841757035038124,0.006131728858842204,0.005093499998717259
+2048,0.021630986166807514,0.01857389583407591,0.018352062674239278,0.019831055503649015,0.019978361340084422,0.005327763637372603,0.004936430836096406,0.006511222163680941,0.006215444668972244,0.005078110863299419
+4096,0.026166020853755374,0.024834576334493857,0.031164930512507755,0.03266890279094999,0.032470284844748676,0.005261333116019766,0.00543506247534727,0.007364555320236832,0.007129479005622367,0.005333597306162119
+8192,0.04628963900419573,0.0373413609728838,0.05650255565221111,0.060085117836327605,0.057509062656511865,0.005226507045639058,0.006792271005300184,0.009294895668669293,0.00879796501249075,0.005148694501258433
+16384,0.0792942083789967,0.05508438895534103,0.1058573403279297,0.10741161816986278,0.10783092369092628,0.005599375193317731,0.009193923692995062,0.011757215465574214,0.01109850719027842,0.005246986014147599
tests/bench_sqlite_storage.py (0 → 5107)
diff --git a/tests/bench_sqlite_storage.py b/tests/bench_sqlite_storage.py
new file mode 100644
index 0000000..471f19b
--- /dev/null
+++ b/tests/bench_sqlite_storage.py
@@ -0,0 +1,165 @@
+"""
+Run a benchmark to compare different key strategies in SQLite.
+
+In particular, does it make sense to store text as raw strings in SQLite, or
+to hash the strings as MD5/xxHash before insertion?
+"""
+
+from collections.abc import Callable
+from collections import defaultdict, namedtuple
+from contextlib import closing
+import csv
+from functools import cache
+import hashlib
+from pathlib import Path
+import random
+import sqlite3
+import statistics
+import string
+import time
+
+import xxhash
+
+
+def random_string(length: int) -> str:
+ """
+ Generate a random alphanumeric string of a specific length.
+ """
+ return "".join(random.choices(string.ascii_letters + string.digits, k=length))
+
+
+type KeyStrategy = Callable[[str], str]
+
+
+def identity(key: str) -> str:
+ """Return a key unmodified."""
+ return key
+
+
+def uncached_md5(s: str) -> str:
+ """Return the MD5 hex digest of a string, computed fresh each time."""
+ h = hashlib.md5()
+ h.update(s.encode("utf8"))
+ return h.hexdigest()
+
+
+@cache
+def cached_md5(s: str) -> str:
+ """Return the MD5 hex digest of a string, cached after first hash."""
+ return uncached_md5(s)
+
+
+def uncached_xxh64(s: str) -> str:
+ """Return the XXH64 hex digest of a string, computed fresh each time."""
+ return xxhash.xxh64_hexdigest(s.encode("utf8"))
+
+
+@cache
+def cached_xxh64(s: str) -> str:
+ """Return the XXH64 hex digest of a string, cached after first hash."""
+ return uncached_xxh64(s)
+
+
+Result = namedtuple("Result", ["insert_duration", "lookup_duration"])
+
+
+def run_benchmark(
+ key_size: int, key_strategy: KeyStrategy, *, num_operations: int
+) -> Result:
+ """
+ Run the benchmark for a single key size.
+ """
+ # Use an in-memory SQLite database to isolate CPU/B-tree performance
+ # from disk I/O.
+ #
+ # Disk performance would likely also have an effect if a key spans
+ # multiple pages, but it would only exaggerate the result.
+ with closing(sqlite3.connect(":memory:")) as con:
+ # Add the same settings and table as I'm using in SQLiteStorage.
+ con.execute("PRAGMA journal_mode = WAL;")
+ con.execute("PRAGMA synchronous = NORMAL;")
+ con.execute("CREATE TABLE cache(key TEXT PRIMARY KEY, value TEXT)")
+ con.commit()
+
+ # Generate a batch of unique keys and values upfront
+ keys = [random_string(key_size) for _ in range(num_operations)]
+ values = [random_string(10) for _ in range(num_operations)]
+
+ expected = {k: v for k, v in zip(keys, values)}
+
+ # 1. Benchmark insertions
+ start_time = time.perf_counter()
+ cur = con.cursor()
+ for k, v in zip(keys, values):
+ insert_key = key_strategy(k)
+ cur.execute("INSERT INTO cache VALUES (?, ?)", (insert_key, v))
+ con.commit()
+ insert_duration = time.perf_counter() - start_time
+
+ # 2. Benchmark lookups
+ # Shuffle keys to simulate random cache access patterns
+ shuffled_keys = list(keys)
+ random.shuffle(shuffled_keys)
+
+ start_time = time.perf_counter()
+ cur = con.cursor()
+ for k in shuffled_keys:
+ lookup_key = key_strategy(k)
+ cur.execute("SELECT value FROM cache WHERE key = ?", (lookup_key,))
+ assert cur.fetchone() == (expected[k],), (cur.fetchone(), expected[k])
+ lookup_duration = time.perf_counter() - start_time
+
+ return Result(insert_duration, lookup_duration)
+
+
+if __name__ == "__main__":
+ sizes = [16, 32, 64, 128, 256, 512, 1024, 2048, 4096, 8192, 16384]
+ all_results = defaultdict(lambda: {sz: [] for sz in sizes})
+
+ key_strategies = [
+ identity,
+ uncached_md5,
+ cached_md5,
+ uncached_xxh64,
+ cached_xxh64,
+ ]
+
+ # Run six interleaved samples and average the results to reduce
+ # the noise of local CPU/memory contention.
+ for i in range(1, 7):
+ print(f"Getting sample {i}...")
+ for sz in sizes:
+ for ks in key_strategies:
+ all_results[ks][sz].append(run_benchmark(sz, ks, num_operations=5000))
+
+ results = defaultdict(dict)
+ for sz in sizes:
+ for ks in key_strategies:
+ results[ks][sz] = Result(
+ statistics.mean(ins for ins, _ in all_results[ks][sz]),
+ statistics.mean(lok for _, lok in all_results[ks][sz]),
+ )
+
+ from pprint import pprint
+
+ pprint(results)
+
+ results_path = Path(__file__).parent / (Path(__file__).stem + ".csv")
+
+ with open(results_path, "w") as f:
+ fieldnames = ["size"]
+ for ks in key_strategies:
+ fieldnames.append(f"{ks.__name__} (insert)")
+ fieldnames.append(f"{ks.__name__} (lookup)")
+
+ writer = csv.DictWriter(f, fieldnames=fieldnames)
+ writer.writeheader()
+
+ for sz in sizes:
+ row = {
+ "size": sz,
+ }
+ for ks in key_strategies:
+ row[f"{ks.__name__} (insert)"] = results[ks][sz].insert_duration
+ row[f"{ks.__name__} (lookup)"] = results[ks][sz].lookup_duration
+ writer.writerow(row)