Custom allocator degrades performance of concurrent reads for complex queries

[jearbear]

I'm porting an application from Postgres which has some non trivial queries that take ~20ms to execute. These queries are run concurrently using Task.async_stream. When using Postgres, this doesn't really affect the runtime of the individual queries so it yields a nice performance benefit. When I used SQLite (via ecto_sqlite3), I noticed that when run concurrently, they would seem to effectively run serially, like each query will just take longer so the total runtime doesn't improve.

The effect is minimal for simple queries, but much more noticeable for the more involved queries in my app. Which is unfortunate because chains of slow queries are the ones I'm more likely to attempt to speed up via concurrency 😅.

I believe this is happening due to the custom allocators used here: https://github.com/elixir-sqlite/exqlite/blob/main/c_src/sqlite3_nif.c#L1428-L1439. When I disable them, throughput starts scaling nicely with the number of workers as expected. (As an aside, after I set SQLITE_DEFAULT_MEMSTATUS=0 which yielded another ~x2 speed up)

My thinking is that these queries are complex enough that they result in a lot of allocations and the custom allocator is becoming a serialization bottleneck.

Would you be open to a PR that provides a flag to disable the custom allocator? If I'm understanding the motivations for its introduction, I'm personally fine trading off visibility into memory usage for the performance benefits.

If you're not quite convinced this could be the problem, I'm happy to work on building a minimal repro tomorrow. Thanks!

[warmwaffles]

I'd be okay with a PR showing a proof of concept yes. What I would like to see is also benchmarks showing the perf penalties of using the custom allocator to be tracked by the VM. My main concern was and still is the tracking of memory consumed by the running process. I did personally have some issues where I had an instance that was limited to 512MB and the process would just OOM but the metrics for BEAM was saying I was fine at around 400MB during heavy load.

Ultimately the solution was to up the memory limit to 2GB and let it run wild, but also to track the memory consumed by the sqlite process itself.

Anyways, go wild. As long as it's optional by some means that'd be fine with me.

[jearbear]

I took some of the queries that are being run in my app to show the range of behavior depending on the complexity of the query.

Here are my results with the default settings (custom allocator left on):

accounts x 1: 906.822ms
accounts x 2: 1134.481ms
accounts x 4: 2033.027ms
accounts x 8: 2673.0ms

allocations x 1: 5689.34ms
allocations x 2: 6830.224ms
allocations x 4: 10827.849ms
allocations x 8: 13962.947ms

categories x 1: 21.091ms
categories x 2: 21.068ms
categories x 4: 26.549ms
categories x 8: 31.511ms

merchants x 1: 406.825ms
merchants x 2: 232.757ms
merchants x 4: 128.77ms
merchants x 8: 76.94ms

And the same results with the custom allocator disabled:

accounts x 1: 771.714ms
accounts x 2: 391.8ms
accounts x 4: 226.668ms
accounts x 8: 143.728ms

allocations x 1: 4659.714ms
allocations x 2: 2237.104ms
allocations x 4: 1326.58ms
allocations x 8: 880.913ms

categories x 1: 18.882ms
categories x 2: 12.92ms
categories x 4: 9.65ms
categories x 8: 10.102ms

merchants x 1: 442.939ms
merchants x 2: 244.249ms
merchants x 4: 129.354ms
merchants x 8: 82.805ms

Most cases see a modest improvement with just 1 process, but you can see that the accounts and allocations queries start to benefit with more processes whereas with the custom allocator, throughput was getting worse.

I've attached the code and sqlite database filled with dummy data. Annoyingly, Github doesn't let you attach sqlite files so I've tarballed both together.

repro.tar.gz

I was looking into how I could implement this and it seems like the most direct option would be to add a disable_custom_allocator option to the exqlite app, read this in load_nif (in sqlite3_nif.ex), and then use that value in on_load (in sqlite3_nif.c) to conditionally set the allocator. Does that seem reasonable to you?

[warmwaffles]

Yea that's about the only thing I can see this being accomplished by without having to force the consumer to manually compile the app. The penalty for that check should only be paid once when the nif is loaded.

[warmwaffles]

When you make this change, can you also add it to the readme in the "Advanced Configuration" section giving the user a good understanding of what they are gaining and losing by disabling the custom allocator.

[warmwaffles]

I do suspect this is just Erlang's allocation system just being slow vs raw malloc