To follow up, I made a new release with performance fixes, and here are the new results: https://github.com/ncruces/go-sqlite-bench/blob/98553a08199b...

The performance difference was larger than I had expected. But this is good.

To fix a recent crash [1] that was happening due to a particular case of reentrancy, which only showed up when I implemented virtual tables and queried other tables to implement one (e.g.: Go calls sqlite3_step to execute a query, which calls Go because it's a query on virtual table, which calls sqlite3_step to scan another table) I introduced a performance regression.

The fix [2] was not to reuse some objects I was allocating once per connection. A mitigation for the regression was (very naive) caching [3].

TLDR: my caching is just not good enough. Simply caching more will go a long way (confirmed already by doubling cache size), but now that I have a good benchmark, I'll do better.

I expect to cut numbers for CPU bound tests in half due to this mishap.

So, thanks cvilsmeier!

[1]: https://github.com/ncruces/go-sqlite3/commit/a9e32fd3f0b9f39... [2]: https://github.com/ncruces/go-sqlite3/commit/d862f47d95d522f... [3]: https://github.com/ncruces/go-sqlite3/commit/9c562f5d8bf7436...

OK, so if I'm looking at this right, a smarter, wider cache goes a great length to fixing the issue.

In [1] I implemented a simple PLRU bit cache, and I'm seeing an 8x performance improvement in some of the tests I was doing worse in:

Before:

  bench-ncruces    -               simple       insert        query       dbsize
  bench-ncruces    -               simple        21224        16495     58687488
  bench-ncruces    -   complex/200/100/20       insert        query       dbsize
  bench-ncruces    -   complex/200/100/20        14993        15228     25354240
  bench-ncruces    -            many/N=10        query       dbsize
  bench-ncruces    -            many/N=10          483        36864
  bench-ncruces    -           many/N=100        query       dbsize
  bench-ncruces    -           many/N=100         3129        36864
  bench-ncruces    -          many/N=1000        query       dbsize
  bench-ncruces    -          many/N=1000        28034        94208
  bench-ncruces    -        large/N=50000        query       dbsize
  bench-ncruces    -        large/N=50000          428    501981184
  bench-ncruces    -       large/N=100000        query       dbsize
  bench-ncruces    -       large/N=100000          779   1003761664
  bench-ncruces    -       large/N=200000        query       dbsize
  bench-ncruces    -       large/N=200000         1475   2007330816
  bench-ncruces    -       concurrent/N=2        query       dbsize
  bench-ncruces    -       concurrent/N=2        13091     56573952
  bench-ncruces    -       concurrent/N=4        query       dbsize
  bench-ncruces    -       concurrent/N=4        14731     56573952
  bench-ncruces    -       concurrent/N=8        query       dbsize
  bench-ncruces    -       concurrent/N=8        24730     56573952

After:

  bench-ncruces    -               simple       insert        query       dbsize
  bench-ncruces    -               simple         5128         3026     58687488
  bench-ncruces    -   complex/200/100/20       insert        query       dbsize
  bench-ncruces    -   complex/200/100/20         3127         3730     25354240
  bench-ncruces    -            many/N=10        query       dbsize
  bench-ncruces    -            many/N=10           93        36864
  bench-ncruces    -           many/N=100        query       dbsize
  bench-ncruces    -           many/N=100          403        36864
  bench-ncruces    -          many/N=1000        query       dbsize
  bench-ncruces    -          many/N=1000         3470        94208
  bench-ncruces    -        large/N=50000        query       dbsize
  bench-ncruces    -        large/N=50000          444    501981184
  bench-ncruces    -       large/N=100000        query       dbsize
  bench-ncruces    -       large/N=100000          717   1003761664
  bench-ncruces    -       large/N=200000        query       dbsize
  bench-ncruces    -       large/N=200000         1401   2007330816
  bench-ncruces    -       concurrent/N=2        query       dbsize
  bench-ncruces    -       concurrent/N=2         3275     56573952
  bench-ncruces    -       concurrent/N=4        query       dbsize
  bench-ncruces    -       concurrent/N=4         3404     56573952
  bench-ncruces    -       concurrent/N=8        query       dbsize
  bench-ncruces    -       concurrent/N=8         4918     56573952

There's still work to do. I could use ints rather than strings for function identifiers. I'll evaluate that later.

[1]: https://github.com/ncruces/go-sqlite3/commit/964a42c76deb9c7...