[Bug]: Loading items a lot slower than pre-2.4.0 versions #2073
Comments
|
Okay, after some more clicking around, this is not just the covers... After loading my library and then scrolling down about 60% of the list, it took ~140 seconds for the server to load up the book titles and covers. |
|
Same here. |
|
same here |
|
Same here - also it seems to stop matching after ~95 titles on 2.4.1 - didn't have any issues with matching on 2.4.0 |
|
Same here - Also It doesn't follow the rule of not showing a series if it has only one book |
|
Massive slow down on this latest version for me too |
|
The reason the server is so much slower now is due to the entire database no longer being loaded into memory and is being queried to get information. The purpose is to improve performance on large libraries (especially if the database is larger than the available memory), but the queries need to be optimized. Advplyr has already improved the performance of some queries, but is taking some time to work on the apps so it'll be a bit before the next release. It is safe to roll back to 2.3.3 without any additional steps to wait for |
|
Is the problem solved with version 2.4.2? |
No, 2.4.2 ended up being some quick bug fixes that were causing crashes. The performance issues still need to be addressed. |
advplyr
changed the title
|
For whatever its worth as a +1, I am also running into severe slowdowns related to loading cover images. A hard refresh of the library page takes ~40 seconds to finish loading on my local network due to slowdowns. As shown in these Firefox network request logs, each cover image is taking over 30 seconds to finish. Everything else is done long before: 
If I take one of these image URLs and load it on my own, it takes 800ms-1s to load, which is still pretty bad considering that these are just being streamed off disk (in all of my testing, the cover metadata cache is already fully populated). Given that it only gets slower when multiple are requested at once (1s -> 30s), maybe there's a contention bug somewhere?
This is definitely not the case for cover images at least. The files aren't stored in the database and the load times are too slow to just be a "read DB from disk" issue. |
What URL are you using to load it directly? Mine includes a call to the API (and the corresponding library item with a token), so to me it makes sense that the queries being slow within the API are also slow to load the cover images. Granted I deal more with hardware than internet stuff, so I could be misunderstanding the route the data is taking. |
|
Just notifying I am still seeing the cover art load slowly after update 2.4.3. Doesn't appear to have changed things for my instance. |
The last few patches have mostly been crash fixes, so the performance has not been fully addressed yet. The performance fixes will be major enough that they will be called out in the release notes, and I (or somebody else) will comment here as well. Thanks for your patience. You can always revert to 2.3.3 if you want to wait for the performance fixes in a future release. |
No worries appreciate the info! |
|
I did some simple investigation into this, and I found that all of the library item routes (including the one for fetching the cover) use this middleware, which calls audiobookshelf/server/models/LibraryItem.js Lines 422 to 454 in 604f527 That query ends up doing some nested queries to look up authors and book series, which at least for me seems to be a major contributor to the slowness of the query. SELECT `libraryItem`.`id`, `libraryItem`.`ino`, `libraryItem`.`path`, `libraryItem`.`relPath`, ...
FROM `libraryItems` AS `libraryItem`
LEFT OUTER JOIN `books` AS `book` ON `libraryItem`.`mediaId` = `book`.`id`
LEFT OUTER JOIN (
`bookAuthors` AS `book->authors->bookAuthor`
INNER JOIN `authors` AS `book->authors`
ON `book->authors`.`id` = `book->authors->bookAuthor`.`authorId`
) ON `book`.`id` = `book->authors->bookAuthor`.`bookId`
LEFT OUTER JOIN (
`bookSeries` AS `book->series->bookSeries`
INNER JOIN `series` AS `book->series`
ON `book->series`.`id` = `book->series->bookSeries`.`seriesId`
) ON `book`.`id` = `book->series->bookSeries`.`bookId`
LEFT OUTER JOIN `podcasts` AS `podcast` ON `libraryItem`.`mediaId` = `podcast`.`id`
LEFT OUTER JOIN `podcastEpisodes` AS `podcast->podcastEpisodes` ON `podcast`.`id` = `podcast->podcastEpisodes`.`podcastId`
WHERE `libraryItem`.`id` = '8b9c8cb6-7900-46a2-9c75-7f74f0d298ba'
ORDER BY `book->authors->bookAuthor`.`createdAt` ASC, `book->series->bookSeries`.`createdAt` ASC;When I run
I'm not a SQL optimization expert, but I think the unfiltered scans of |
|
I replaced that API endpoint to get the cover so that it doesn't go through the regular middleware that as @selfhost-alt pointed out is loading unnecessary amounts of data. When this is built if anyone wants to test on |
|
I did a quick test, and loading covers is faster, but there are still issues. This URL for example took 25 seconds to load: There were 10 of these requests being done almost at the same time, with just the page-value increasing by 1, all of them taking 25-28 seconds to complete. After that there were two batches of coverart loaded. The first one took about 20 seconds, second one took about 5 seconds.
|
|
@ScuttleSE Do you have the server setting ignore prefixes when sorting enabled? Currently that column in the db is not indexed. I was testing with a library of ~2700 books those queries take anywhere from 1 to 5 seconds to complete. When I disabled that server setting the queries were less than a second.
For testing I scrolled down the library page sorted by title at a fast speed and watched the server logs with debug logs turned on. With ignore prefixes when sorting enabled: With ignore prefixes when sorting disabled: |
|
That does seem to be one of the contributing factors. After I disabled the prefixes-setting, the queries that earlier took 25-30 seconds now took 2-4 seconds. |
|
I do wonder why my system is that much slower though... That is with prefixes disabled |
|
Thanks for checking. Even the faster queries I showed are slower then they should be.
|
|
From my testing big improvements should be in v2.4.4 Collapse series on the library page will still be slow to the point where you might want to avoid that on large libraries. |
|
I'm experiencing this issue in my Podcasts library but primarily only in relation to individual shows that have a very large number of episodes. When attempting to view all episodes of a show in my library with 1-100 episodes, the load time is near instant to maybe half a second. A show with 800+ episodes will take over 10 seconds to load. |
|
I had the hope that a clean installation of version 2.5.0 and building up a fresh database could speed things up again. Unfortunitly that did't help and it still is very slow compared to 2.3.3. Especially the library view with 'Collapse Series' and series view are barely usable. So I have to stick with 2.3.3. and its quirks. |
|
I've started looking into this as well. There seem to be several code paths which are still quite slow. Not unusable, but… annoying. Loading covers is one of the things which are still quite slow. The following query seems to be the problem (taken when loading “Home”): -- Ran the following query in 1117ms:
SELECT `libraryItem`.`id`,
`libraryItem`.`mediatype`,
`libraryItem`.`mediaid`,
`libraryItem`.`libraryid`,
`book`.`id` AS `book.id`,
`book`.`coverpath` AS `book.coverPath`,
`book`.`tags` AS `book.tags`,
`book`.`explicit` AS `book.explicit`,
`podcast`.`id` AS `podcast.id`,
`podcast`.`coverpath` AS `podcast.coverPath`,
`podcast`.`tags` AS `podcast.tags`,
`podcast`.`explicit` AS `podcast.explicit`
FROM `libraryitems` AS `libraryItem`
LEFT OUTER JOIN `books` AS `book`
ON `libraryItem`.`mediaid` = `book`.`id`
LEFT OUTER JOIN `podcasts` AS `podcast`
ON `libraryItem`.`mediaid` = `podcast`.`id`
WHERE `libraryItem`.`id` = '9217841f-400a-490b-a10f-e4529066c07d';One problem here could be the mismatching data types of the joined columns (see #2324). I'm not sure about SQLite UUID and UUIDv4, but in general, a data type mismatch can make a join really expensive. After all, the database might need to perform a type conversion on the fly. That being said, I'm wondering if we need the joins at all. Looking at the data being used, we have If that's not possible for any reason, we could also try splitting up the query and running then as separate queries. This might still cause less data to be searched ovverall and be faster. |
Yeah I plan on doing this but right now the clients are sending a library item id, not a media id. I wanted to update the clients and the API route to use media id. |
|
So you want the clients to request something like That would also mean you don't need a database migration. That's something my idea would have required. |
|
Yeah exactly. Or we could do |
|
It does seem to be slightly faster in v2.6 compared to v2.4/5, but still very noticeably slower than v2.3. |
|
I am running the version: 2.6.0 in Yunohost environment. The peformance (loading the initial library, browsing etc.) is extremely slow as compared to original version a while ago (probably 2.3.3). Additionally, the app crashes consistently on downloading any larger podcasts (500+ espisodes). Is there a way to do a fresh install of version 2.3.3 in Yunohost package while these performance issues/bugs are addressed? The official younohost repository only allows the latest version of be installed. I tested both fixes suggested in this thread (disabling watcher, ignoring pre-fix) with minimal improvements. |
Yes, with docker compose https://www.audiobookshelf.org/docs#docker-compose-install you can define the tag. instead of |
Thanks for the quick response. But I was hoping to find something like the following, supporting install of an earlier version. I believe docker compose method will require separate docker/docker-compose install outside of Yunohost web GUI and I haven't found some good stable guides around that setup |
|
You can, but you have to modify the install manifest https://github.com/YunoHost-Apps/audiobookshelf_ynh/blob/master/manifest.toml#L39-L41 |
Thank you so much. I was able to copy the repository over and modify the file to get to install the required version from within Yunohost web GUI. No more lagging loads and performance issues. Now hoping they all get resolved so that I can upgrade to the latest one. |
|
Another performance update in v2.7.1 for podcasts |
|
At this point in v2.7.1 the performance issues should mostly be resolved. For larger libraries it won't be as fast as v2.3.x and below because the data is being fetched as needed from the database instead of all being stored in memory. For obvious reasons we couldn't keep storing the entire database in memory. There is still more to be done in optimizing the queries and reducing the amount of the data returned from the API but performance improvements never really end. Let me know what other performance issues you are seeing |
|
Just some feedback: 2.7.1 is night and day faster than 2.7.0. I'm finally able to listen to some podcasts I have with near 1000 eps downloaded! I'll still keep an eye out, but it feels like the issue is completely solved for me. Thanks for sticking with this! |
|
Unfortunately version 2.7.1 did not change much on my side. I'm not using podcasts, just audiobooks and radio plays. On my setup the new version is still a lot slower than 2.3.3. |
|
@Eftimin70 Roughly what are the size of your libraries? Anything notable like audiobooks with thousands of audio tracks or anything like that you can share? Are you running the server with Docker desktop on Windows or how are you running the server? Are you using a network file system for the database? |
|
@advplyr Here are my stats for the radio plays: 4165 Items in Library Audiobookshelf is running in docker on a qnap with an Intel Celeron J4125. The Folders are directly hosted on the qnap server which is equipped with 20GB of RAM. |
|
For me the library loading has been much faster with this update. The only problem I am having is that covers are taking up to 22 sec each to load On the home page the load time is up to 2 sec per image Audiobookshelf is running via docker on unraid. The server is running a i9-9900k with 32GB of RAM. |
|
Just updated from 2.3.3. to 2.7.1 and for podcasts, it is fast! Thanks for the effort! The home page takes a bit to start when the android app is initially loaded (although it did in 2.3.3 as well, maybe a touch slower in the newest version). Web version loads right away. The "Latest" page (which I use often) loads very quick for both the app and the web. If I go into the library section (again, android app), a podcast with 750 episodes takes a second or two to load. Not bad at all! The page itself shows up all at once (after everything is loaded in the background). My opinion is that loading a skeleton page, then populating the content (youtube does this) makes it seem faster. For some reason it takes a second when I hit the back arrow too. The library section is blazing fast on the web though, so it might be an android app-related issue. |
|
With the new version, the "re-scan" of a library is much faster than before. Thanks for the effort. |
|
Cover images being slow were addressed in #3580 and are now much faster (currently only available in |
|
I think the biggest improvement will be when non-sqlite database support is added. |
|
sqlite is plenty fast for an application like audiobookshelf though :/. Architectural changes can make way bigger improvements then switching for a networked database. |
|
Exactly. The database system isn't the real problem here. It's more the database structure. |
and others
Describe the issue
When loading for example my Library page, the cover art takes several seconds to loads. The nodejs-process on the server seems to spike on CPU-usage.
I did a profile in Chrome, and as is evident, the browser waits for several seconds to get the cover-images from the server.
(pixelated for privacy)
Steps to reproduce the issue
Audiobookshelf version
2.4.1
How are you running audiobookshelf?
Docker
The text was updated successfully, but these errors were encountered: