Ask HN: How can I test FTS5 engine in SQLite3?
---
For example, there's "threads = N" PRAGMA in SQLite3, it sets an upper bound on the number of auxiliary threads that a prepared statement is allowed to launch to assist with a query. My instinct is that more is better, but:
```
Value, Insert Time (s), StdDev, Rows/Second, Degradation, Query Avg (ms)
1, 2.68, ±0.08, 3732, 1.43x, 0.8
0, 2.72, ±0.18, 3689, 1.51x, 0.8
2, 2.85, ±0.03, 3515, 1.30x, 0.8
```
So it's not always the case.
('Degradation' checks the timings of the first N inserts and compares them with the latest N inserts to determine how much slower they have become (varies by configs, too). I should probably rename Rows/Second to something else and show N rows/s at start and the end of the run)
---
And for the second example, you can set a "synchronous" PRAGMA to OFF, NORMAL, FULL or EXTRA: https://www.sqlite.org/pragma.html#pragma_synchronous
However, sometimes in specific configurations, EXTRA outperforms NORMAL, which makes no sense to me (even after multiple runs; every benchmark is performed 3 times). Though there is some randomness introduced by CPU vs. I/O bound transitions and etc.
---
I feel that if I publish my findings as they are, it might give people the wrong idea. Additionally, my results vary for HDD-only and SSD-only systems, as well as for 86 and ARM differences in random places. So:
- How can I ensure that, if I publish my findings, people won't be misled by the idea that "THIS – pointing at the most unsafe configuration possible – is absolute best configuration and we should use always use it"?
- How can I find out why, for example, synchronous configurations that are doing more work are sometimes faster than the ones that do less?
- Currently, I test 94 configurations three times each. So even though it is plenty, I still feel I need to have more, but at the same time who will read results of 94 benchmarks?
p.s. Oh I also made this image for the article that will be shown on link previews, thoughts? :) https://i.imgur.com/04Mvx5Q.png