[Bug] Fetching Subscriptions timeout

[kmccmk9]

Describe the bug
I have an instance that I use for my home. It currently has one user, myself. I am hosting it through the production docker containers as described in the Installation Instructions. About 4 days ago, the subscriptions list (/feed/subscriptions) started experiencing a timeout. All other pages on the website seem to function as normal. I am currently subscribed to 1178 channels but I have not had this issue until now. What's interesting, is if I navigate to the subscriptions page and there are new unseen notifications, the page loads completely fine. However, once those notifications have been seen/acknowledged, the subscriptions feed fails to load. On the server side I was able to capture the log I posted in the relevant section below. And yes I have run the database maintenance sql commands just in case.

Steps to Reproduce

1. Have an account with 900+ subscriptions?
2. Allow the system to find new videos associated with the channels
3. Navigate to /feed/subscriptions
4. See a list of videos
5. Refresh the page, note the browser never completes loading the page

Logs
As seen in the screenshot I have attached, loading the /feed/subscriptions page triggers an API POST call to /auth/notifications which takes 26 seconds to return.

Screenshots

Additional context
Currently, the Invidious version is: Current version: 2024.08.26-4782a67 @ (HEAD detached at v2.20240825.2)

This happens regardless of device or browser that is accessing the subscriptions page. This is all tested locally so upload speed, ISP, tunnel software, etc are not relevant.

[unixfox]

• do you hourly restart invidious?
• do you have pubsub configured?

[kmccmk9]

• do you hourly restart invidious?

  • do you have pubsub configured?

• Yes I have a cron job configured
• it is not configured

[kmccmk9]

Here's a random thought. Hypothetically, if the root cause of the slow response is that there are too many videos in the "notifications feed", could we use a database command to remove videos from the feed older than a certain date?

[unixfox]

There is an option here to disable user notification if the page never completes it loading:

invidious/config/config.example.yml

Line 324 in 4782a67

#enable_user_notifications: true

For the moment I have no idea what would happen really. Maybe try running invidious in debug log level in order to understand more why it is timing out.

[kmccmk9]

invidious.log
Ok I got something on this one. I've attached the log file to this comment since it is really big. From what I gather, it tries to fetch subscriptions (which never completes, despite it saying it took 201000 ms). The relevant part I gathered is that the SELECT statement that runs underneath, is really large. So large that it causes something to shutdown which causes a cascades of process shutdowns.

STATEMENT: SELECT * FROM subscriptions_a75132ba9a7a2e0ca62503ad06ada833436e089eb416e2c96265383aaf4ab29b WHERE NOT id = ANY (VALUES ('cPUbFteFL5Q'),('FCSCq5rGxDI'),('6bODiZ5bP84')...

If this is the true cause, perhaps a solution would be to rethink how that select query is working. Perhaps we can tell PostgreSQL to reference those ids, instead of passing them in? Or perhaps using a not exists clause and reference a table of these ids? Granted I'm not sure if this is the database choking because so much data is being passed in the query or something else?

[unixfox]

There is a pending PR that has been opened for a long time that should solve the subscriptions issues: #2469

[kmccmk9]

Ok great, I'm following that PR and when it gets merged I will update this issue. Your changes look sensible to me and it looks like it should address this problem. Fingers crossed!

[kmccmk9]

There is a pending PR that has been opened for a long time that should solve the subscriptions issues: #2469

One thing I did notice while testing this PR out more, is that the subscription feed page seems to be limited now. I no longer see all the videos that were there or "next page" buttons. I think that may be because you have 1 month hardcoded in the sql queries across a few of those files? Is there any reason for this?

[unixfox]

There is a pending PR that has been opened for a long time that should solve the subscriptions issues: #2469

One thing I did notice while testing this PR out more, is that the subscription feed page seems to be limited now. I no longer see all the videos that were there or "next page" buttons. I think that may be because you have 1 month hardcoded in the sql queries across a few of those files? Is there any reason for this?

At the current state invidious use materialized views to store the subscriptions. This consumes a lot of CPU resources, especially on the public instances where there are many subscriptions to refresh.

This PR switch to a fixed amount of subscriptions retreived. It has to be a fixed amount since there no flexibility anymore thanks to materialized views.

Technically we could allow to extend the time. But we considered that 1 month is already plenty of time. Hardly anybody is going to look back past 1 month. At least in the life time of my instance yewtu.be I never had in 2 years people complaining.
It's great to put some kind of limit because on public instances if you don't set a limit you can easily overload the instance and make it crash.

People that want to find old videos should just go on the page of the channel. And if there are really people that have a niche interest into that then they are free to edit the code!