Allocating memory for cache seems very slow

[Hakkin]

Hi, I was recently testing for query performance on a large database compared to the driver I currently use (mattn), and I noticed that performance was very bad until the specified cache amount was completely filled.
Here is a graph showing the issue, both drivers have cache_size=-524288 specified in the connection URI and I am using a 4 connection pool size, so the total memory allocation was 2GiB total:

You can see this driver gets stuck at around 500 queries/second for a bit until suddenly jumping to over 20,000/second, while mattn has no such issue. This jump in performance corresponds exactly to when the cache is entirely filled.

While I'm not 100% certain it's allocating the memory that's slow, it makes the most logical sense.

One thing I tried was running this:

func init() {
	sqlite3.RuntimeConfig = wazero.NewRuntimeConfig()
	sqlite3.RuntimeConfig = sqlite3.RuntimeConfig.WithCoreFeatures(api.CoreFeaturesV2)
	sqlite3.RuntimeConfig = sqlite3.RuntimeConfig.WithMemoryLimitPages(65536)
	sqlite3.RuntimeConfig = sqlite3.RuntimeConfig.WithMemoryCapacityFromMax(true)
	sqlite3.Initialize()
}

WithMemoryCapacityFromMax documentation says it eagerly allocates max memory, unless max is not defined. The default is false, which means minimum memory is allocated and any call to grow memory results in re-allocations.. This seems like it should pre-allocate the memory, but the behavior doesn't seem to change at all, the memory size gradually grows during executing rather than just once at the start.

Apologies for not having anything to reproduce this behavior, but the database I'm testing this on is very large, so it's not really viable to share it. I believe this behavior should be reproducible by any program that gradually fills the cache over many queries, as long as the database is larger than the allocated cache size.

[ncruces]

It's possible that WithMemoryCapacityFromMax is not doing what's expected because we use a custom memory allocator that uses mmap which is required for shared-memory WAL mode to work with the Wasm sandbox.

It's also possible that malloc from wasi-libc is growing memory in constant (rather than linearly increasing) increments, which will cause frequent mprotect syscalls.

I can try to create a patched version of the driver for you to test (i.e. from a branch), but I can't do it earlier than next week at best. Would you be willing to try?

[Hakkin]

It's possible that WithMemoryCapacityFromMax is not doing what's expected because we use a custom memory allocator that uses mmap which is required for shared-memory WAL mode to work with the Wasm sandbox.

It's also possible that malloc from wasi-libc is growing memory in constant (rather than linearly increasing) increments, which will cause frequent mprotect syscalls.

I can try to create a patched version of the driver for you to test (i.e. from a branch), but I can't do it earlier than next week at best. Would you be willing to try?

Sorry, I should have mentioned this in the OP, but this issue was encountered on Windows. I've just now tested this on Linux as well, there does seem to be a slight reduction in performance while the cache is being filled, but nowhere near as bad as on Windows, so I guess this may have to do with the Windows allocator.

I've taken a CPU profile, and the large majority of time is spent in windows.VirtualAlloc, the callstack looks like this:

...
github.com/ncruces/go-sqlite3/driver.(*rows).Next
github.com/ncruces/go-sqlite3.(*Stmt).Step
github.com/ncruces/go-sqlite3.(*sqlite).call
github.com/tetratelabs/wazero/internal/engine/wazevo.(*callEngine).CallWithStack
github.com/tetratelabs/wazero/internal/engine/wazevo.(*callEngine).callWithStack
github.com/tetratelabs/wazero/internal/wasm.(*MemoryInstance).Grow
github.com/ncruces/go-sqlite3/internal/alloc.(*virtualMemory).Reallocate
golang.org/x/sys/windows.VirtualAlloc
...

I would be willing to test any patches you make, though since this doesn't seem to affect Linux very much, maybe it's not much of a priority.

[ncruces]

The theory is the same: too many syscalls, just to VirtualAlloc instead of mprotect; if we fix one we'll fix the other. I'll have a try at this next week.

[ncruces]

Can you test #316? You need branch issue-315. This just grows memory geometrically (1.125× growth factor).

I can also do something about WithMemoryCapacityFromMax, but it'd be nice to improve default behavior.

PS: this was just hacked up on a mobile phone, so take it with a grain of salt.

[Hakkin]

Doesn't have any effect, I think you might have typo'd. The value of new is overwritten on line 54 to the same value it was before the patch, I get a linter error on line 53 of this value of new is never used (SA4006).

If I change line 54 to new = (new + rnd) &^ rnd which I think is maybe what it was suppose to be, the issue does seem fixed, I go from 750 queries/second to 15,000.

[ncruces]

Oh, great!

Yes. Typo. Don't do this on holiday, on a phone, I guess. 🤦

[ncruces]

Since this wastes at most ~12% of memory, passes all tests, and fixes your issue, I'm going to merge it.

I'll keep this open to test a fix for WithMemoryCapacityFromMax, but that's more code, so it's probably wise not to go for it without a computer and auto-complete.