Significant performance loss with Results on 0.20.7

[nwh3365]

Describe the bug
There has been a significant performance loss with the Results page between 0.20.6 and 0.20.7. With 20.6 it took about 8 seconds to change sorts, go to next page, etc.; with 20.7 these tasks take 60-70 seconds.

To Reproduce
For reference, my database has 5323 records.

0.20.6
Launch Speedtest Tracker
Click Results = 7s
Change Per Page to 100; seems to remember this between sessions most of the time
Click Server (sort ascending) = 8s
Click Server (sort descending) = 8s
Click Page 4 = 8s
Click ID (sort by ID) = 8s

0.20.7
Launch Speedtest Tracker
Click Results = 7s
Change Per Page to 100; seems to remember this between sessions most of the time
Click Server (sort ascending) = 60s
Click Server (sort descending) = 68s
Click Page 4 = 66s
Click ID (sort by ID) = 64s
I often get several "page unresponsive" alerts from my browser during these long waits.

Expected behavior
The 8-second waits on 20.6 were a bit annoying, but the 60+ seconds with 20.7 make the Results page pretty unusable.

I have noticed other areas that don't perform very well (including with 20.6 so probably not related to this issue). As an example it typically takes about 8 seconds to open a detailed results page. This seems excessively long to me to display a single page of detailed results, but I don't use it very often so I hadn't reported it.

For now I have rolled back to 20.6.

Environment
Ubuntu 22.04.4 LTS (GNU/Linux 5.15.0-113-generic x86_64)
Docker version 27.0.3, build 7d4bcd8
Speedtest Tracker v0.20.7
Browser = Brave Version 1.67.123 Chromium: 126.0.6478.126 (Official Build) (64-bit)

[alexjustesen]

Have you deleted a large set of data lately and what datbase are you using? (SQLite / MYSQL / PGSQL)

[nwh3365]

SQLite. I'm not sure what qualifies as "large", but I do delete the records associated with a consistently slow speedtest server every few days. I haven't really kept track of how many records each time, and it varies from day to day depending on how often the bad server is selected by speedtest. When I originally did the initial purge of these records several weeks ago they made up about 15% of the total record count.

[alexjustesen]

SQLite performance can be impacted if you have other IO operations happening on the same disk. Start by double checking there first, it's also possible the indexes might need to be rebuilt in your database to help with performance.

I haven't felt any performance issues with my install but I'm running a MySQL DB.

[nwh3365]

Just to clarify, I'm running in Docker (lscr.io/linuxserver/speedtest-tracker:latest) so I can easily switch between 20.6 and 20.7 using the exact same config and database. I don't think the issue is related to the database, disk i/o, or the server as I can switch back and forth between the two versions within a few seconds and 20.6 consistently produces 8-second-ish results while 20.7 consistently produces 60+ second results. The only difference between the two is which docker image is being used.

That said, I did run a vacuum and a reindex on the database which reduced the database size by about 780k, but performance was unchanged (still about 8 seconds for 20.6 vs 60+ seconds for 20.7). I also tried deleting my "latest" (i.e., 20.7) docker image and repulling it, but performance remains unchanged.

[nwh3365]

Perhaps this is an issue with the Docker image?

[nwh3365]

lscr.io/linuxserver/speedtest-tracker:0.20.6

1	12.9 MB	COPY /root-out/ / # buildkit
2	0 B	ARG BUILD_DATE
3	0 B	ARG VERSION
4	0 B	ARG MODS_VERSION=v3
5	0 B	ARG PKG_INST_VERSION=v1
6	0 B	LABEL build_version=Linuxserver.io version:- 6cd44267-ls1 Build-date:- 2024-05-22T19:21:09+00:00
7	0 B	LABEL maintainer=TheLamer
8	23.6 kB	ADD --chmod=744 https://raw.githubusercontent.com/linuxserver/docker-mods/mod-scripts/docker-mods.v3 /docker-mods # buildkit
9	4.1 kB	ADD --chmod=744 https://raw.githubusercontent.com/linuxserver/docker-mods/mod-scripts/package-install.v1 /etc/s6-overlay/s6-rc.... 
10	0 B	ENV PS1=$(whoami)@$(hostname):$(pwd)\$ HOME=/root TERM=xterm S6_CMD_WAIT_FOR_SERVICES_MAXTIME=0 S6_VERBOSITY=1 S6_STAGE2_HOOK=... 
11	13.8 MB	RUN |4 BUILD_DATE=2024-05-22T19:21:09+00:00 VERSION=6cd44267-ls1 MODS_VERSION=v3 PKG_INST_VERSION=v1 RUN echo "**** install run... 
12	6.8 kB	COPY root/ / # buildkit
13	0 B	ENTRYPOINT ["/init"]
14	0 B	ARG BUILD_DATE
15	0 B	ARG VERSION
16	0 B	LABEL build_version=Linuxserver.io version:- 1.26.1-r0_8.3.8-r0-ls5 Build-date:- 2024-06-06T18:32:04+00:00
17	0 B	LABEL maintainer=nemchik
18	42.6 MB	RUN |2 BUILD_DATE=2024-06-06T18:32:04+00:00 VERSION=1.26.1-r0_8.3.8-r0-ls5 RUN echo "**** install build packages ****" && apk... 
19	17.9 kB	COPY root/ / # buildkit
20	0 B	EXPOSE map[443/tcp:{} 80/tcp:{}]
21	0 B	ENV LSIO_FIRST_PARTY=true
22	0 B	ARG BUILD_DATE
23	0 B	ARG VERSION
24	0 B	ARG SPEEDTEST_TRACKER_VERSION
25	0 B	LABEL build_version=Linuxserver.io version:- v0.20.6-ls29 Build-date:- 2024-06-13T01:54:56+00:00
26	0 B	LABEL maintainer=thespad
27	0 B	ENV HOME=/config
28	91.5 MB	RUN |3 BUILD_DATE=2024-06-13T01:54:56+00:00 VERSION=v0.20.6-ls29 SPEEDTEST_TRACKER_VERSION=v0.20.6 RUN apk add --no-cache g... 
29	4.5 kB	COPY root/ / # buildkit
30	0 B	VOLUME [/config]

lscr.io/linuxserver/speedtest-tracker:latest

1	12.9 MB	COPY /root-out/ / # buildkit
2	0 B	ARG BUILD_DATE=2024-07-02T14:32:49+00:00
3	0 B	ARG VERSION=5f4013a7-ls6
4	0 B	ARG MODS_VERSION=v3
5	0 B	ARG PKG_INST_VERSION=v1
6	0 B	ARG LSIOWN_VERSION=v1
7	0 B	LABEL build_version=Linuxserver.io version:- 5f4013a7-ls6 Build-date:- 2024-07-02T14:32:49+00:00
8	0 B	LABEL maintainer=TheLamer
9	25.3 kB	ADD --chmod=755 https://raw.githubusercontent.com/linuxserver/docker-mods/mod-scripts/docker-mods.v3 /docker-mods # buildkit
10	4.2 kB	ADD --chmod=755 https://raw.githubusercontent.com/linuxserver/docker-mods/mod-scripts/package-install.v1 /etc/s6-overlay/s6-rc.... 
11	945 B	ADD --chmod=755 https://raw.githubusercontent.com/linuxserver/docker-mods/mod-scripts/lsiown.v1 /usr/bin/lsiown # buildkit
12	0 B	ENV PS1=$(whoami)@$(hostname):$(pwd)\$ HOME=/root TERM=xterm S6_CMD_WAIT_FOR_SERVICES_MAXTIME=0 S6_VERBOSITY=1 S6_STAGE2_HOOK=... 
13	13.9 MB	RUN |5 BUILD_DATE=2024-07-02T14:32:49+00:00 VERSION=5f4013a7-ls6 MODS_VERSION=v3 PKG_INST_VERSION=v1 LSIOWN_VERSION=v1 RUN echo... 
14	7.5 kB	COPY root/ / # buildkit
15	0 B	ENTRYPOINT ["/init"]
16	0 B	ARG BUILD_DATE=2024-07-11T18:49:50+00:00
17	0 B	ARG VERSION=1.26.1-r0_8.3.9-r0-ls10
18	0 B	LABEL build_version=Linuxserver.io version:- 1.26.1-r0_8.3.9-r0-ls10 Build-date:- 2024-07-11T18:49:50+00:00
19	0 B	LABEL maintainer=nemchik
20	48.3 MB	RUN |2 BUILD_DATE=2024-07-11T18:49:50+00:00 VERSION=1.26.1-r0_8.3.9-r0-ls10 RUN echo "**** install build packages ****" && ap... 
21	17.9 kB	COPY root/ / # buildkit
22	0 B	EXPOSE map[443/tcp:{} 80/tcp:{}]
23	0 B	ENV LSIO_FIRST_PARTY=true
24	0 B	ARG BUILD_DATE=2024-07-13T21:24:55+00:00
25	0 B	ARG VERSION=v0.20.7-ls34
26	0 B	ARG SPEEDTEST_TRACKER_VERSION=v0.20.7
27	0 B	LABEL build_version=Linuxserver.io version:- v0.20.7-ls34 Build-date:- 2024-07-13T21:24:55+00:00
28	0 B	LABEL maintainer=thespad
29	0 B	ENV HOME=/config
30	91.8 MB	RUN |3 BUILD_DATE=2024-07-13T21:24:55+00:00 VERSION=v0.20.7-ls34 SPEEDTEST_TRACKER_VERSION=v0.20.7 RUN apk add --no-cache g... 
31	4.5 kB	COPY root/ / # buildkit
32	0 B	VOLUME [/config]

[alexjustesen]

I'll do a little digging on this later in the week.

[alexjustesen]

So, I "think" I narrowed this down to referencing json data in sqlite. Can you give me some details on the server specs your Docker instance is running on? My servers might be too fast which is why I'm not seeing much of a slow down.

[nwh3365]

Hardware is dual Xeon X5550 @ 2.67GHz (8 cores, 16 threads), 48GB RAM
Docker is running in an Ubuntu 22.04 VM with 8 cores, 6GB RAM (about 53% in use)
Neither are heavily loaded (load avg typically <1)

I wondered if the json structures within sqlite might be contributing to some of the general performance I mentioned (e.g., taking 8 seconds to open a detail record). That said, were any changes made to json-related code between 20.6 and 20.7? The reason I ask is that the significant performance hit that my original post referred to seems to be very specific to 20.7, and possibly specific to the docker image. As mentioned, if I simply switch back to the 20.6 docker image (using the same config/data), the significant performance hit goes away, although opening detail records still takes 8 seconds.

[alexjustesen]

Between 0.20.6 and 0.20.7 nothing I did, but that being said it could of been dependency related.

[nwh3365]

Oh, OK. Didn't think about that. I did see some differences in the docker images as shown above, but I don't understand that side of docker enough to know if they could be related.