[Triage] Sources page times out before loading

[gteltschik]

Describe the bug
Loading the sources page times out. The error log shows:

10:39:06.164 [error] Exqlite.Connection (#PID<0.2343.0>) disconnected: ** (DBConnection.ConnectionError) client #PID<0.6605.0> timed out because it queued and checked out the connection for longer than 15000ms

#PID<0.6605.0> was at location:

(exqlite 0.23.0) lib/exqlite/sqlite3.ex:137: Exqlite.Sqlite3.multi_step/3
(exqlite 0.23.0) lib/exqlite/sqlite3.ex:175: Exqlite.Sqlite3.try_fetch_all/3
(exqlite 0.23.0) lib/exqlite/sqlite3.ex:169: Exqlite.Sqlite3.fetch_all/3
(exqlite 0.23.0) lib/exqlite/connection.ex:675: Exqlite.Connection.get_rows/2
(exqlite 0.23.0) lib/exqlite/connection.ex:621: Exqlite.Connection.execute/4
(db_connection 2.7.0) lib/db_connection/holder.ex:354: DBConnection.Holder.holder_apply/4
(db_connection 2.7.0) lib/db_connection.ex:1558: DBConnection.run_execute/5

To Reproduce
Steps to reproduce the behavior:

1. Navigate to the URL/sources page
2. The page will time out.

Expected behavior
It should display the sources page.

Screenshots
If applicable, add screenshots to help explain your problem.

N/A unless you like to see a page timeout in a browser.

Diagnostic info

• App Version: 2025.1.27
• yt-dlp Version: 2025.01.26
• Apprise Version: v1.9.2
• System Architecture: x86_64-pc-linux-gnu
• Timezone: America/Chicago

Additional context

I think I may have found the limits of the application. I have 1 media profile, 560 sources, 26,747 downloads, 314 pending, and a total of 12.86TB downloaded. The database has over 1M items in it, and consumes 12GB of disk space (and is on a local NVMe). This behavior has been intermittent over the past month and unchanged with newer builds. It's difficult to tell if the error predated the change to the sources page (to paginate versus a single list) or not, but the timing is close. I seem to recall that the page return was slow previously, but usually succeeded whereas now it fails more often than it succeeds. The main home page is slow, but usually returns, other pages seem to return fine.

The server fluctuates between 3-20% CPU, runs stable at 50% memory (about 64GB used), and is not experiencing iowait issues. The container seems to consume 1GB of memory and reports 5-80% CPU. The beam.smp task frequently reports 100-110% CPU and 4GB of memory being used. Ergo, I expect the problems are relegated to the database.

pinchflat-logs-2025-01-29.txt

[kieraneglin]

Hey there! Thanks for the report (:

I think you're right! I have ~200k items locally which is slightly noticeably slower but not the end of the world. But I can imagine that around 1MM + would result in some annoying behaviours.

I should be able to get those numbers back down to earth. I've worked on SQLite projects with 200MM rows that still can perform complex queries in under 20ms, I just need to know where to start. If you'd be willing to share your database through some secure mechanism I'd love to have it as a test case, but otherwise I can try bumping my numbers locally and seeing if I can replicate this

[gteltschik]

Happy to share my database. What's the best mechanism to do so (email)? I can easily send you a dropbox link, google, or whatever works best for you.

I was going to investigate tools to give it a cleaning to see if that might help.

[kieraneglin]

Email would be great! Soft preference for Google Drive but really anything works

[gteltschik]

Invite to google drive sent. The files have finished uploading.

[kieraneglin]

Still working on this one - it's not the quick slam-dunk I had hoped so it's been a bit of a dive into the query planner