search

alexjustesen / speedtest-tracker

Speedtest Tracker is a self-hosted internet performance tracking application that runs speedtest checks against Ookla's Speedtest service.
https://speedtest-tracker.dev/
MIT License
2.62k stars 98 forks source link

Significant performance loss with Results on 0.20.7 #1603

Closed nwh3365 closed 4 weeks ago

nwh3365 commented 1 month ago

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 commented 1 month ago

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

nwh3365 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

Perhaps this is an issue with the Docker image?

nwh3365 commented 1 month ago 
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 commented 1 month ago

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

alexjustesen commented 1 month ago

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 commented 1 month ago

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 commented 1 month ago

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

nwh3365 commented 1 month ago

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.

nwh3365 commented 1 month ago

Just a quick update...I pulled the new 0.20.8 docker image this morning. I'm experiencing the same performance issues with it as with 0.20.7. As soon as I revert to the 0.20.6 image, performance returns to normal:

Task                             0.20.8     0.20.6
--------------------------------------------------
Open Results page (100 items)     7.30s      7.57s
Sort Server Ascending            63.22s      7.41s
Sort Server Descending           68.12s      7.53s

A few days ago you mentioned the referencing of json data in sqlite, so I did some timing comparisons between 0.20.6 and 0.20.8. In both scenarios, my Per Page is set to 100.

Results Table (0.20.6)
Sort by ID       7.71s
Sort by Server ID    7.90s
Sort by Server       7.73s
Sort by Download     7.59s
Sort by Upload       7.85s
Sort by Ping         7.78s
Sort by Status       7.74s
Sort by Created at   7.98s

Results Table (0.20.8)*
Sort by ID      62.55s
Sort by Server ID   73.26s
Sort by Server      70.22s
Sort by Download    70.23s
Sort by Upload      73.50s
Sort by Ping        71.89s
Sort by Status      74.14s
Sort by Created at  Initial page load 7.93s / Subsequent sort 72.51s

*The URL in the address bar updates at about 8 seconds, but the results table isn't updated until the times indicated above.

*Of interest is that when I initially click Results, which appears to be a descending Created At sort, the table displays in about 7.93s; for all sort selections after that the times are as indicated above (i.e., the performance of the initial display of the Results page does not seem to be affected; only if I select a different sort order and then it doesn't matter which sort order I select). Is the code that initially displays the Results page perhaps different from the code that displays the Results page post-sort-selection?

alexjustesen commented 1 month ago

If it's related to the database or database indexes regardless of version I'd expect the results to be the same(ish). This is leading me down the Docker and/or code path now. Looks like there is a new Filament release and it references performance improvement for table checkboxes so this could be related.

I'm going to tag and release v0.20.9 shortly and I'll include only dependency updates to see if it fixes it.

nwh3365 commented 4 weeks ago

Both v0.20.9 and v0.21.0 appear to have returned to the old (v0.20.6) performance levels.

It takes about 8 seconds to switch sort orders with these two versions rather than the 60-75 seconds I was seeing with v0.20.7 and v0.20.8.

Thank you!

alexjustesen commented 4 weeks ago

Not a clue what it was but sounds like a win!