mirror of
https://github.com/advplyr/audiobookshelf.git
synced 2026-05-30 23:40:40 +02:00
[Bug]: 'Recent Series' on home taking 25s to load #2148
Assignees
adam (Adam Melkus)
Clear assignees
adam (Adam Melkus)
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: starred/audiobookshelf#2148
Reference in New Issue
Block a user
Delete Branch "%!s()"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Originally created by @alxgarci on GitHub (Aug 5, 2024).
What happened?
Everytime i open or switch between libraries, the home page takes too long to load. I've read another topics addressing similar issues but not this one. I had a 100k book library (epub, mobi, pdf,...) which i split in 20k libraries. Changing between libraries also triggers this.
Problem occurs accessing the server through last Android app release, Chrome and Chrome for Android.
If i change libraries while inside 'Library' tab, it takes 4 seconds, which is okay.
Tested on docker versions 'edge' and 'v2.12.0'
What did you expect to happen?
To load faster, every other home item loads in less than 2 seconds. Or to optionally disable 'recent series' item.
Steps to reproduce the issue
Audiobookshelf version
v2.12.0
How are you running audiobookshelf?
Docker
What OS is your Audiobookshelf server hosted from?
Linux
If the issue is being seen in the UI, what browsers are you seeing the problem on?
Chrome
Logs
Additional Notes
Tried splitting the 100k book library in 20k smaller libraries, thinking 100k books were too many. Also tried android app and searching previous performance issues already solved.
@nichwall commented on GitHub (Aug 6, 2024):
I'm not very familiar with SQL or database queries, but from initial research, it looks like the slowdown for "Recent Series" and "Newest Authors" are both related to needing to verify the author and series have books after removing any permissions (from access control from limiting by tag or whether the item is explicit). Your logs show you as the root user which has access to all library items, so
bookWherewill be empty and just be checking that authors have books.The authors query is simpler than the series query, so putting that here for reference.
https://github.com/advplyr/audiobookshelf/blob/930bacd45d232b44a967c0744c9cfb82dcc592f4/server/utils/queries/libraryFilters.js#L324C1-L358C5
Maybe we can try adding the
separate: trueto the include part of the query because thebookAuthorhasbelongsToManyassociations? I'd also be curious if the query gets faster as more authors and series are no longer "new" after 60 days or if this is specifically related to the book count.https://sequelize.org/docs/v7/querying/select-in-depth/#separate-eager-loading-queries
(customized shelves related to https://github.com/advplyr/audiobookshelf/issues/1241)
@nichwall commented on GitHub (Aug 6, 2024):
Well, I just tried that change locally, and turns out the
HasManyassociation does not exist for this query, soseparate: truedoes not work. I played with changing theattributesthat are used to see if removing the checks fortagsorexplicitwould speed the query up at all and I didn't notice a change in loading time on a test library with 962 new authors and just under 6000 books.@alxgarci commented on GitHub (Aug 6, 2024):
For others having the same issue: Since it's hard for me to understand all the code and queries, i opted to comment every line inside those methods and return an empty array, which disables the section in the 'Home' tab.
So changing the
getNewestAuthors: https://github.com/advplyr/audiobookshelf/blob/930bacd45d232b44a967c0744c9cfb82dcc592f4/server/utils/queries/libraryFilters.js#L324C1-L358C5 commenting from line 325 to line 357, just leaving inside the methodreturn { authors: [], count: 0 }.And changing
getSeriesMostRecentlyAdded:https://github.com/advplyr/audiobookshelf/blob/930bacd45d232b44a967c0744c9cfb82dcc592f4/server/utils/queries/libraryFilters.js#L200C9-L200C35 commenting from line 201 to 313, just leaving inside the method
return { series: [], count: 0 }.This cleanly disables both queries and makes everything load in under 2 seconds.
For me this is a temporary fix, just enter in container's bash shell and install nano/vim.
@github-actions[bot] commented on GitHub (Oct 27, 2024):
Fixed in v2.16.0.
@advplyr commented on GitHub (Oct 28, 2024):
If you update to v2.16.0 I'm curious how much the performance improves
@alxgarci commented on GitHub (Oct 28, 2024):
Sry just gave up on having 100k+ books and started a new library with just 2k, so can't monitor the performance for now. But thanks for the fix! Will try in the future.
@oddstr13 commented on GitHub (Nov 1, 2024):
I can't say I see a noticeable improvement, but the time is still beyond the "stare at it while it loads" threshold, so there could
I just updated to v2.16.2, but the following log is from v2.16.0.
Seeing as there are no mentions of this kind of optimizations in the .1 and .2 changelogs I don't expect any difference.
Once those queries are cached, loading the android app takes about 3s, but the cache lasts for 30m, and I rarely load the app more often than that while listening to audiobooks.
If desirable, I could go look for older logs to compare, but I don't remember when I enabled debug logging, so there may not be too much data to find.
@nichwall commented on GitHub (Nov 1, 2024):
Would you be willing to share your database? I'm curious why the "Continue Listening/Reading" query is 30 seconds long. That seems excessive, especially for only having 170 items in the library.
@oddstr13 commented on GitHub (Nov 1, 2024):
@nichwall I'd rather not dump the whole database, but I can run some queries for you, if you give me some pointers?
@nichwall commented on GitHub (Nov 1, 2024):
If you set the environment variable
QUERY_LOGGINGto bebenchmark, that will tell us exactly what queries are being run and how long each of them are taking. If you can then share the full log with all of the queries related to loading the home page (and maybe navigating around the web client to check if any other queries are being really slow) that will help narrow down where things need to be improved. We have been testing several API endpoints to improve performance recently and have some more things identified, but would be helpful if there is a specific operation with your database which is much slower.Edit to add: it may be helpful to open a new issue with the log file to help keep things distinct between different endpoints
@oddstr13 commented on GitHub (Nov 1, 2024):
I've enabled query logging.
Not seeing the slowness right now, but it tends to only happen the first time loading the app (after a longer pause).
The app connects and starts loading the library, and I'll have to wait for it to finish, even tho the book I want to listen to is downloaded locally.
I'll create a new issue with the verbose logs if and when I next encounter it.
@nichwall commented on GitHub (Nov 1, 2024):
Sounds like a plan. Are you seeing something like "Ran query in X Ms" in the logs? You will need to switch the log level to Debug in the server settings as well (not an environment variable)
@oddstr13 commented on GitHub (Nov 1, 2024):
Yep, the appropriate logs are active.
I wouldn't expect relatively simple queries such as this one to take quite this long, but I suppose my NAS is more over-worked than I thought, especially with backups and other IO heavy tasks running right now.
That wouldn't explain 30s+ response times from a query tho, I think.