I'm working on a fairly long article about me trying to find a combination of PRAGMAs for fastest INSERT/SELECT queries that specifically target the FTS5 engine. However, the more testing and benchmarking I do, the more unsure I am of the results and how I should word things.

---

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

Submissions

Ask HN: How can I test FTS5 engine in SQLite3?

comments