Performance - Slow Query on SQLite database cant work out where the bottleneck is

Questions / Help
1

My app used full text search in sqlite. On my local machine the query takes about 1.5 seconds to return.
When I run the same thing on a fly shared x2 machine it takes 9 seconds.
When i connect with ssh and run top I cant see anything jumping out at me, apart from it never seems to use more than about 50% of one of the 2 cores. On my local machine it uses 100% of one core as I don’t believe sqlite is multithreaded.
The IO% is very low too.
I initially thought it was litefs related, but I moved it on to a volume and it didnt make any difference.

The App is written in Go and is in an alpine image.
Does anyone have any ideas?

2

How large is the database and how much RAM do you have on the Fly machine?

3

I never thought about RAM. its 2.5gb and I think the vm had 1gb.

On my local machine the whole dB is probably in the cache.

I’ll up the ram and see what happens.

Thanks

1 Like
4

Tried changing the RAM didnt seem to make any difference. Moved to a performance machine with 1 core and 2Gb and it now runs in 2 seconds. And can use 100% of the CPU.

Contention on the shared cores?

5

Hmm, that’s interesting. I originally suspected the increase in RAM would help because the database would be populated in the OS page cache and that’s significantly faster than fetching over FUSE.

Do you see the same experience if you run your query from the sqlite3 CLI instead of your Go app?

6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.