Extremely slow Sqlite queries with Docker

[wbste]

Hey, just wanted to document my issues here, looking for ways to help troubleshoot. Running docker desktop via wsl2 on a windows host. I'm the only user. I am using bind mounts to the host, and I know there could be some slight deltas in performance, but the many other containers I run all do the same binding and I've had no issues.

compose file:

services:
    workout-tracker:
        ports:
            - 3065:8080
        environment:
            - WT_JWT_ENCRYPTION_KEY=my-secret-key
        volumes:
            - ./data:/data
            - ./imports:/imports
        image: ghcr.io/jovandeginste/workout-tracker:master
        restart: unless-stopped

Every sqlite interaction is incredibly slow. Below are some snippets from it just restarting. I use watchtower to auto-update, so I'm always on the latest version.

    time=2025-01-11T05:54:59.835735518Zlevel=INFOmsg=Connecting to the database 'sqlite': ./database.dbapp=workout-trackerversion=mastersha=299b7bd058c33a219c3510e53532c584ffa9bd11module=app

    time=2025-01-11T05:56:33.246399831Zlevel=WARNmsg=slow sql query [1m33.230235416s >= 100ms]app=workout-trackerversion=mastersha=299b7bd058c33a219c3510e53532c584ffa9bd11module=databaseslow_query=truequery=DELETE FROM `map_data_details` WHERE map_data_id IN (SELECT map_data_id FROM map_data_details as mdd where map_data_details.created_at < mdd.created_at)duration=93230235416rows=0file=/app/pkg/database/gorm.go:97

    time=2025-01-11T05:57:06.107303007Zlevel=WARNmsg=slow sql query [32.362159138s >= 100ms]app=workout-trackerversion=mastersha=299b7bd058c33a219c3510e53532c584ffa9bd11module=databaseslow_query=truequery=SELECT * FROM `map_data_details` WHERE `map_data_details`.`map_data_id` IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18...)duration=32362159138rows=787file=/app/pkg/database/workouts.go:407

Any thoughts on where to start?

[jovandeginste]

Do you have access to a "real" Linux host? How many workouts are there in your database?

[wbste]

Do you have access to a "real" Linux host? How many workouts are there in your database?

I do not. 787 workouts. Sqlite file size is 330 MB. Do the binaries in the release get auto-built as well? Maybe I can try it without docker?

EDIT:

• I downloaded the windows v2.0.1 binary, and used my sqlite file. Same issue.
• I deleted that db, uploaded 10 files, and tried. Nice and snappy.
• Currently I'm reuploading all the files to a new db. Will see how that goes...

EDIT 2:

• Reimported all files to new db with binary. Queries were much faster.
• Copied the sqlite file to the container's volume. Still slow.
• I noticed for the docker image you just place the binary in the /app folder. First time I've seen that...maybe something weird going on by interacting with it via a container?

For now I'll just use the binary. Let me know if there's anything I can do on my end to troubleshoot. May just be a "me" problem 😄

[jovandeginste]

It's no secret wsl2 io is not optimal...

[wbste]

It's no secret wsl2 io is not optimal...

True enough. With the other 30 containers I run written in Go, Python, etc...this is just the first slowness I've ran into for SQLite queries.

[jovandeginste]

Are any of those other containers using a SQLite database of similar size?

[wbste]

Are any of those other containers using a SQLite database of similar size?

Nothing quite as large it looks like. The two largest are around 60 MB. How large of a db do you have? Or do you use Postgres or something else? I would think ~800 activities is on the small side, but maybe I'm wrong?

[jovandeginste]

I use SQLite but on Linux; I also don't have that many activities. I did hear of people with multiple thousands, however.

I will take a look at query caching at some point. I assume you have enough memory in your system?

[jovandeginste]

I merged a simple query cacher; if you have time, please upgrade and provide feedback!

[wbste]

I merged a simple query cacher; if you have time, please upgrade and provide feedback!

Seems snappier! The map loading is the only slow thing, but I'll test some more to see where the slowness may be (leaflet? heatmap? etc...)

[jovandeginste]

Did you find anything useful?

[wbste]

Nothing obvious, but honestly I'm happy with it as is. Thanks so much for the hard work!