mirror of
https://github.com/advplyr/audiobookshelf.git
synced 2026-05-30 23:40:40 +02:00
[Bug]: Loading items a lot slower than pre-2.4.0 versions #1364
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#1364
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 @ScuttleSE on GitHub (Sep 10, 2023).
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
@ScuttleSE commented on GitHub (Sep 10, 2023):
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.
@DocDrydenn commented on GitHub (Sep 10, 2023):
Same here.
@skyzuma commented on GitHub (Sep 10, 2023):
same here
@Spinonduality commented on GitHub (Sep 11, 2023):
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
@stefanop1 commented on GitHub (Sep 11, 2023):
Same here - Also It doesn't follow the rule of not showing a series if it has only one book
@barelylit commented on GitHub (Sep 12, 2023):
Massive slow down on this latest version for me too
@nichwall commented on GitHub (Sep 12, 2023):
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
2.4.22.4.32.4.4a future release if this makes things unusable.It is always a good idea to make a backup just in case.
@Hallo951 commented on GitHub (Sep 14, 2023):
Is the problem solved with version 2.4.2?
@nichwall commented on GitHub (Sep 14, 2023):
No, 2.4.2 ended up being some quick bug fixes that were causing crashes. The performance issues still need to be addressed.
@BlackHoleFox commented on GitHub (Sep 18, 2023):
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.
@nichwall commented on GitHub (Sep 18, 2023):
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.
@haptiqblack commented on GitHub (Sep 18, 2023):
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.
@nichwall commented on GitHub (Sep 18, 2023):
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.
@haptiqblack commented on GitHub (Sep 18, 2023):
No worries appreciate the info!
@selfhost-alt commented on GitHub (Sep 19, 2023):
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
LibraryItem.getOldByIdwhich runs this slow query: https://github.com/advplyr/audiobookshelf/blob/604f52762b14e5f64eedb56c136a8211be36cb2c/server/models/LibraryItem.js#L422-L454That 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.
When I run
explain query planI get the following:I'm not a SQL optimization expert, but I think the unfiltered scans of
bookAuthors<->authorsandbookSeries<->seriesare contributing to the query slowness.@advplyr commented on GitHub (Sep 22, 2023):
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
edgedocker version we can determine if the covers were the main problem with the performance. I can see it being part of the problem but I think the main issue is the performance of the/libraries/:id/itemsand the/libraries/:id/personalizedendpoints.@ScuttleSE commented on GitHub (Sep 22, 2023):
I did a quick test, and loading covers is faster, but there are still issues.
I opened up my library, and the first 10 or so books visible on the page popped up fine, I then scrolled about 2/3 down the list (~1400 books), and it took around 20-30 seconds for the titles of the books to load and then another few seconds of the cover art to load.
This URL for example took 25 seconds to load:
https://site.example.org/api/libraries/dac312d8-6b0e-4b3c-ad6e-8e50a9d8d8b1/items?sort=media.metadata.title&desc=0&limit=100&page=1&minified=1&include=rssfeed,numEpisodesIncompleteThere 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.
@advplyr commented on GitHub (Sep 22, 2023):
@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:
I'm only sharing the first half of the requests
With ignore prefixes when sorting disabled:
@ScuttleSE commented on GitHub (Sep 22, 2023):
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.
@ScuttleSE commented on GitHub (Sep 22, 2023):
I do wonder why my system is that much slower though...
That is with prefixes disabled
Btw, no idea why my logs says "libary" and yours say "library"...
@advplyr commented on GitHub (Sep 22, 2023):
Thanks for checking. Even the faster queries I showed are slower then they should be.
Here are some things that need to be done to speed things up:
audioFilescolumn in the books table@advplyr commented on GitHub (Sep 30, 2023):
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.
Let me know if you see improvement
@BlackHoleFox commented on GitHub (Oct 1, 2023):
Updated with my library and saw a night to day improvement. The library screen loads in just a few seconds now instead of 30-70s. Great work so far :)
@ScuttleSE commented on GitHub (Oct 1, 2023):
Hrm...I must say, I haven't noticed that much of an improvement.
If I open my library and then scroll down about 70%, it takes 30-40 seconds for both the book titles and covers to appear
This link for example took about 39 seconds to load:
https://abs.example.org/api/libraries/dac312d8-6b0e-4b3c-ad6e-8e50a9d8d8b1/items?sort=media.metadata.title&desc=0&limit=100&page=8&minified=1&include=rssfeed,numEpisodesIncomplete@ScuttleSE commented on GitHub (Oct 1, 2023):
If I disable Prefix-handling, it cuts down from around 30 seconds down to 10 doing the same.
Covers are still pretty slow, 10-12 seconds each
@Dragonatorul commented on GitHub (Oct 2, 2023):
I'm seeing a similar issue in podcasts. Disabling or enabling the prefix option does not seem to make any noticeable difference.
The following logs are from switching between home, latest and library tabs.
@kaldigo commented on GitHub (Nov 13, 2023):
I am still having these issues including the slow loading covers. I did test a new install with the same library and it was much faster. Is there any way to migrate the data across without copying the database? Or maybe something that can be done to clean up the old database
@djolker commented on GitHub (Nov 15, 2023):
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.
@Eftimin70 commented on GitHub (Nov 18, 2023):
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.
@lkiesow commented on GitHub (Nov 19, 2023):
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”):
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
libraryIdfrom the tablelibraryItemsandexplicit,tagsandcoverPathfrom eitherbooksorpodcasts. This means that all the latter three fields exist in both media type tables. If we move the common columns to thelibraryItemstable instead, we would need no join at all. That would probably speed up the whole process quite a bit.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.
@advplyr commented on GitHub (Nov 19, 2023):
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.
@lkiesow commented on GitHub (Nov 19, 2023):
So you want the clients to request something like
/api/books/{mediaId}/coverand/api/podcasts/{mediaId}/coverand then just access one of the tables? That would also definitely work and be faster.That would also mean you don't need a database migration. That's something my idea would have required.
@advplyr commented on GitHub (Nov 19, 2023):
Yeah exactly. Or we could do
/api/media/:mediaType/:mediaId/cover.The
mediaTypestored on library items is singular so it would be easier on the clients and allow for adding new types without creating new cover routes.@advplyr commented on GitHub (Dec 2, 2023):
Another performance improvement was made in v2.6.0 with some api caching thanks to @mikiher here https://github.com/advplyr/audiobookshelf/pull/2343
@DDriggs00 commented on GitHub (Dec 6, 2023):
It does seem to be slightly faster in v2.6 compared to v2.4/5, but still very noticeably slower than v2.3.
@AppUserOne commented on GitHub (Dec 13, 2023):
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.
@JBlond commented on GitHub (Dec 13, 2023):
Yes, with docker compose https://www.audiobookshelf.org/docs#docker-compose-install you can define the tag. instead of
:latestyou can use:2.3.3@AppUserOne commented on GitHub (Dec 14, 2023):
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
https://github.com/YunoHost-Apps/audiobookshelf_ynh
@JBlond commented on GitHub (Dec 14, 2023):
You can, but you have to modify the install manifest https://github.com/YunoHost-Apps/audiobookshelf_ynh/blob/master/manifest.toml#L39-L41
@AppUserOne commented on GitHub (Dec 14, 2023):
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.
@advplyr commented on GitHub (Dec 31, 2023):
Another performance update in v2.7.1 for podcasts
@advplyr commented on GitHub (Jan 1, 2024):
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
@ejstacey commented on GitHub (Jan 1, 2024):
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!
@Eftimin70 commented on GitHub (Jan 1, 2024):
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.
@advplyr commented on GitHub (Jan 1, 2024):
@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?
@Eftimin70 commented on GitHub (Jan 1, 2024):
@advplyr Here are my stats for the radio plays:
4165 Items in Library
3816 Overall Hours
361 Authors
305.7 Size (GB)
43901 Audio Tracks
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.
@kaldigo commented on GitHub (Jan 3, 2024):
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
Thats with a library of 1319 audiobooks.
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.
@hoggatt commented on GitHub (Jan 4, 2024):
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.
@JBlond commented on GitHub (Jan 4, 2024):
With the new version, the "re-scan" of a library is much faster than before. Thanks for the effort.
@nichwall commented on GitHub (Nov 9, 2024):
Cover images being slow were addressed in https://github.com/advplyr/audiobookshelf/pull/3580 and are now much faster (currently only available in
edge). There has been some more cleanup in the API and database queries, but there is still more to do.@DDriggs00 commented on GitHub (Nov 9, 2024):
I think the biggest improvement will be when non-sqlite database support is added.
@BlackHoleFox commented on GitHub (Nov 9, 2024):
sqlite is plenty fast for an application like audiobookshelf though :/. Architectural changes can make way bigger improvements then switching for a networked database.
@lkiesow commented on GitHub (Nov 10, 2024):
Exactly. The database system isn't the real problem here. It's more the database structure.
@stelle007 commented on GitHub (Jan 20, 2025):
Any update on this?
It is still very slow for me, I am using mainly for podcasts, and I do have the datahorder disease :)
@r1aven commented on GitHub (Jan 22, 2025):
@stelle007
Curious question - are you using the root account when experiencing the slow loading times? I continued to have slow loading times while using the root account, even after all of the updates/improvements, that I used to manage the library/podcasts/etc. I noticed that the user account I used as my main listening account wasn't experiencing the slow load problem so I decided to create yet another account and gave it all of the administrative permissions. Using this "new admin" account, my install is just as speedy as it once was and I no longer use the root account. It's worth a shot if you haven't tried.
@stelle007 commented on GitHub (Jan 22, 2025):
@r1aven Thank you for that input.
Yes I have tried with other users one with admin rights and one with non admin rights, But it is the same result for both.
I do have 18072 Audio Tracks though, and It is only happening when I get a lot of Audio Tracks. I have also read somewhere that the amount of items is the issue.
I am running on docker, and I have tried fresh install with same result.
@r1aven commented on GitHub (Jan 22, 2025):
@stelle007
Sorry to hear that didn't help. You do have about 10x the audio files than I. I too am running on docker and just remembered that I also made this change in my environmental variables which is also worth a shot. If it doesn't work, I'm out of my helpful ideas. Best of luck!
Found in this thread
@stelle007 commented on GitHub (Jan 22, 2025):
Again, thank you.But unfortunately I already have those options set.I did see from the thread you linked that advplyr wrote "Most of the OOM issues have been resolved and the remaining one I'm aware of is with podcast libraries where you have thousands of episodes downloaded."
I don't see a OOM issue, or any errors in the log, It just takes about 11 sec per image to load on the Library page.
@nichwall commented on GitHub (Jan 22, 2025):
What server version are you seeing 11 seconds to load per image?
@stelle007 commented on GitHub (Jan 22, 2025):
Any version since 2.4.0. I am on 2.18.1 now.
Well I can't say it's 11 per image since 2.4.0, but it have been incredibly slow since.
At one point I did try to rollback and run with v2.3.3 image, and that the ran fine.
@mikiher commented on GitHub (Jan 26, 2025):
@stelle007 Can I ask you to please share a few more details about your setup and system?
Networktab (in the top line)Networktab, click on one of the slow cover images (you will likely see many of them namedcover&ts=<some-long-number>)Timingtab.Timingtab contents and post it here.@ScuttleSE commented on GitHub (Jan 26, 2025):
Original poster here. For me, the problem is still there, and without a way to know exactly what queries are being made, and how long they take, it is hard to pinpoint exactly what causes it. My guess is still the database.
Either queries are being done that are unnecessary, or they are being done in an extremely inefficient way. The problem isn't that it takes time to load an image, the problem is rather that it takes time to wait for the server to tell the browser what image to load.
When I look at the profile, the actual loading of the coverimages doesn't take long at all. The problem is that the aren't being loaded until after 35-40 seconds.
@mikiher commented on GitHub (Jan 26, 2025):
Thanks. I appreciate the response.
It is hard to understand from the profile image you sent where the image requests actually spend their time.
If you're willing to answer the same questions I sent @stelle007 , I promise I will try to help with this until we resolve this.
Also, have you upgraded to the most recent version? In v2.17.0 we made some significant improvements in cover image loading, so I want to make sure these are included in the code you're running.
@nichwall commented on GitHub (Jan 26, 2025):
@ScuttleSE is the slow loading time every time you access something in the web client or only the first time? That almost sounds like the slow loading for
filterDatawhich is not very efficient to generate with the current data model, so it is generated once and cached until a change for media is detected (due to the cache being invalidated)@ScuttleSE commented on GitHub (Jan 26, 2025):
It's when blowsing the audiobook library for example. I have just under 2000 titles. If I open the library and then scroll down about 80% of the page, it takes 40-45 seconds for anything to display
@ScuttleSE commented on GitHub (Jan 26, 2025):
When looking at the actual query-logging, it seems to be a huge number of small queries being done actually.
If I scroll past ~1800 titles, it looks like ABS actually queries all those 1800 titles before loading the ones I have scrolled to.
@advplyr commented on GitHub (Jan 26, 2025):
Can you confirm what version of the server you are running?
@mikiher commented on GitHub (Jan 26, 2025):
I see.
I will ask you again to still provide the rest of the details I asked for above, except for the Chrome timing, since based on what you said I now think the issue is indeed not with cover image loading, but rather with concurrent page fetch requests.
Can you please let me know:
@ScuttleSE commented on GitHub (Jan 26, 2025):
2.18.1
@mikiher commented on GitHub (Jan 26, 2025):
While I'm waiting for your response, I'll say a couple of things, which I'm quite sure are related to this issue, given what you described:
So again, I'm quite sure the issue revolves around one or more of the above. If you can answer my questions, I'll be able to know which one pertains more to your specific setup.
@JBlond commented on GitHub (Jan 26, 2025):
I have the same issue with a library with 300 items, even though it is not 45 seconds. more like 15.
Title DESC
The visible part is there after like 3 to 4 seconds.
18 (view set to size 120)
I use the mouse wheel default windows settings for the length
@mikiher commented on GitHub (Jan 26, 2025):
@JBlond
Is it serving on Docker? On which system?
@JBlond commented on GitHub (Jan 26, 2025):
Docker on Debian 12 all updates.
ABS 2.18.1
I may add the info that it isn't the only library in the database, only the largest.
Also #3767 did improve the speed. Yet it is not like before 2.3
@mikiher commented on GitHub (Jan 26, 2025):
And on which hardware? Also, this is through localhost or local network? Just trying to make sure no network latency issues are involved
@PhantomPhoton commented on GitHub (Jan 26, 2025):
@mikiher
I'm ABS 2.18.1 (modified with pragma values) on Docker.
I'm certainly in the boat for issue 1. If I scroll down too fast, even with the pragma changes, It'll crash the frontend. The backend reports
Socket disconnected from client after 133624ms (Reason: transport close). If I let it sit long enough, the frontend partially comes back to life. None of the cover images load, but the data loads in for all the entries. However, trying to open an entry leads to a blank screen until I refresh the page.I started to try to look into a way to force the initial load to be everything rather than a paginated view, but the frontend calculations are a bit beyond me to do so.
I really appreciate all the hard work you are doing to help us all out. Thank you very much.
@mikiher commented on GitHub (Jan 26, 2025):
I am inclined to think that this may be a good mitigation for not-so-large libraries (i.e up to about 500 books).
I will play with this a bit to see what's the impact on initial page load time.
@JBlond commented on GitHub (Jan 26, 2025):
Intel i5 4300U 1,9 GHz (c2/t4)
2 GB RAM
Samsung SSD 860 EVO 500G
Network the same switch. All devices connected via 1 Gbit LAN.
Tested browsers Firefox 128, Chromium 133
@JBlond commented on GitHub (Jan 26, 2025):
Does that improve the speed? It is still a single database file. Just wondering.
@PhantomPhoton commented on GitHub (Jan 26, 2025):
Depends on what you are going for. The first page of entities will be slower, but if you're going for later entries, it's faster.
So when you query the database, you say, I want the entries sorted by this criteria. Generally the criteria to find the entries is the slow bit. Then to paginate it, you say, give me (for example) 12 entries from the query. That gives you the first page. To get the next page, you say, I want entries 13 to 24 from the query. There's nothing that holds the query results from the first query, so for the engine to figure out what entries 13 to 24 is, it needs to figure out what entries 1-12 is and discard them.
So for each page, you repeat the work it took to get the previous pages and discard it. So the final page query is the cost to find all the rows. By just doing a complete query on load, you save a lot of query time for the between pages, but at the cost that the first page takes as long as the last page would in the paginated version.
Does that make sense?
For me, I generally scroll somewhere in the middle of the list, so I rather just pay the full cost once. It's possible that other people tend to the first entries in the list where the pagination would be faster.
@mikiher commented on GitHub (Jan 26, 2025):
All what @PhantomPhoton said, plus a larger fetch size (even if not covering the whole library), may reduce the probablity for concurrent fetches due to fast scrolling, which leads to bad server behavior.
As mentioned, there's certainly a tradeoff here between initial page load time and faster loading of later entries.
@JBlond commented on GitHub (Jan 26, 2025):
Yes. A single large query in the first place vs a lot of small queries.
I tried your method scrolling down to 50% and that make the images load a lot faster.
I tried to split the libs into sizes of 100 items and there is no more delay when I scroll down to the end.
@advplyr commented on GitHub (Jan 26, 2025):
The sorting and limiting is done in the db query so if it is properly indexed it doesn't need to know what items 1-12 are and discard them
@PhantomPhoton commented on GitHub (Jan 27, 2025):
Well, it does, it's just very fast to find them. The index isn't an array that has a fixed offset position to jump to, it's a b-tree and so you have to transverse the nodes to find how many counts each branch has to find out which branch to start processing from.
It is very fast, but it still does figure out what the first n would be before it can give you what offset you want is.
Though this is deep into the technical weeds.
@ScuttleSE commented on GitHub (Jan 27, 2025):
Out of curiosity, I logged the queries that occurred after the library was loaded and I used the scrollbar to go all the way to the bottom.
342 queries, almost all of them taking more and more time.
`[2025-01-27 05:41:50.925] DEBUG: Ran the following query in 4ms:
[2025-01-27 05:41:50.956] DEBUG: Ran the following query in 28ms:
[2025-01-27 05:41:50.975] DEBUG: Ran the following query in 15ms:
[2025-01-27 05:41:50.981] DEBUG: Ran the following query in 2ms:
[2025-01-27 05:41:50.988] DEBUG: Ran the following query in 5ms:
[2025-01-27 05:41:51.000] DEBUG: Ran the following query in 9ms:
[2025-01-27 05:41:51.011] DEBUG: Ran the following query in 8ms:
[2025-01-27 05:41:51.058] DEBUG: Ran the following query in 6ms:
[2025-01-27 05:41:51.061] DEBUG: Ran the following query in 1ms:
[2025-01-27 05:41:51.069] DEBUG: Ran the following query in 6ms:
[2025-01-27 05:41:51.070] DEBUG: Ran the following query in 4ms:
[2025-01-27 05:41:51.075] DEBUG: Ran the following query in 2ms:
[2025-01-27 05:41:51.128] DEBUG: Ran the following query in 42ms:
[2025-01-27 05:42:04.351] DEBUG: Ran the following query in 31ms:
[2025-01-27 05:42:04.414] DEBUG: Ran the following query in 1ms:
[2025-01-27 05:42:04.478] DEBUG: Ran the following query in 2ms:
[2025-01-27 05:42:04.489] DEBUG: Ran the following query in 7ms:
[2025-01-27 05:42:04.492] DEBUG: Ran the following query in 1ms:
[2025-01-27 05:42:04.495] DEBUG: Ran the following query in 1ms:
[2025-01-27 05:42:04.625] DEBUG: Ran the following query in 121ms:
[2025-01-27 05:42:04.627] DEBUG: Ran the following query in 1ms:
[2025-01-27 05:42:04.629] DEBUG: Ran the following query in 1ms:
[2025-01-27 05:42:04.713] DEBUG: Ran the following query in 82ms:
[2025-01-27 05:42:04.908] DEBUG: Ran the following query in 4ms:
[2025-01-27 05:42:04.919] DEBUG: Ran the following query in 4ms:
[2025-01-27 05:42:04.939] DEBUG: Ran the following query in 2ms:
[2025-01-27 05:42:04.943] DEBUG: Ran the following query in 2ms:
[2025-01-27 05:42:05.072] DEBUG: Ran the following query in 27ms:
[2025-01-27 05:42:05.074] DEBUG: Ran the following query in 52ms:
[2025-01-27 05:42:05.088] DEBUG: Ran the following query in 64ms:
[2025-01-27 05:42:05.127] DEBUG: Ran the following query in 42ms:
[2025-01-27 05:42:05.130] DEBUG: Ran the following query in 42ms:
[2025-01-27 05:42:05.143] DEBUG: Ran the following query in 2ms:
[2025-01-27 05:42:05.146] DEBUG: Ran the following query in 5ms:
[2025-01-27 05:42:05.193] DEBUG: Ran the following query in 34ms:
[2025-01-27 05:42:05.251] DEBUG: Ran the following query in 90ms:
[2025-01-27 05:42:05.316] DEBUG: Ran the following query in 57ms:
[2025-01-27 05:42:05.325] DEBUG: Ran the following query in 69ms:
[2025-01-27 05:42:05.327] DEBUG: Ran the following query in 2ms:
[2025-01-27 05:42:05.329] DEBUG: Ran the following query in 4ms:
[2025-01-27 05:42:05.343] DEBUG: Ran the following query in 5ms:
[2025-01-27 05:42:05.344] DEBUG: Ran the following query in 4ms:
[2025-01-27 05:42:05.355] DEBUG: Ran the following query in 6ms:
[2025-01-27 05:42:05.457] DEBUG: Ran the following query in 81ms:
[2025-01-27 05:42:05.466] DEBUG: Ran the following query in 4ms:
[2025-01-27 05:42:05.487] DEBUG: Ran the following query in 11ms:
[2025-01-27 05:42:05.519] DEBUG: Ran the following query in 41ms:
[2025-01-27 05:42:05.538] DEBUG: Ran the following query in 3ms:
[2025-01-27 05:42:05.540] DEBUG: Ran the following query in 4ms:
[2025-01-27 05:42:05.562] DEBUG: Ran the following query in 16ms:
[2025-01-27 05:42:05.564] DEBUG: Ran the following query in 15ms:
[2025-01-27 05:42:05.570] DEBUG: Ran the following query in 2ms:
[2025-01-27 05:42:05.571] DEBUG: Ran the following query in 3ms:
[2025-01-27 05:42:05.581] DEBUG: Ran the following query in 5ms:
[2025-01-27 05:42:05.582] DEBUG: Ran the following query in 4ms:
[2025-01-27 05:42:24.263] DEBUG: Ran the following query in 3ms:
[2025-01-27 05:42:24.330] DEBUG: Ran the following query in 52ms:
[2025-01-27 05:42:24.507] DEBUG: Ran the following query in 225ms:
[2025-01-27 05:42:24.568] DEBUG: Ran the following query in 11ms:
[2025-01-27 05:42:24.577] DEBUG: Ran the following query in 20ms:
[2025-01-27 05:42:35.448] DEBUG: Ran the following query in 4ms:
[2025-01-27 05:42:35.525] DEBUG: Ran the following query in 51ms:
[2025-01-27 05:42:35.527] DEBUG: Ran the following query in 38ms:
[2025-01-27 05:42:35.737] DEBUG: Ran the following query in 258ms:
[2025-01-27 05:42:35.934] DEBUG: Ran the following query in 423ms:
[2025-01-27 05:42:35.939] DEBUG: Ran the following query in 135ms:
[2025-01-27 05:42:35.945] DEBUG: Ran the following query in 424ms:
[2025-01-27 05:42:35.949] DEBUG: Ran the following query in 106ms:
[2025-01-27 05:42:35.978] DEBUG: Ran the following query in 248ms:
[2025-01-27 05:42:35.978] DEBUG: Ran the following query in 128ms:
[2025-01-27 05:42:36.152] DEBUG: Ran the following query in 421ms:
[2025-01-27 05:42:36.176] DEBUG: Ran the following query in 277ms:
[2025-01-27 05:42:36.192] DEBUG: Ran the following query in 422ms:
[2025-01-27 05:42:36.195] DEBUG: Ran the following query in 286ms:
[2025-01-27 05:42:36.202] DEBUG: Ran the following query in 283ms:
[2025-01-27 05:42:36.208] DEBUG: Ran the following query in 438ms:
[2025-01-27 05:42:36.221] DEBUG: Ran the following query in 436ms:
[2025-01-27 05:42:36.226] DEBUG: Ran the following query in 298ms:
[2025-01-27 05:42:36.232] DEBUG: Ran the following query in 444ms:
[2025-01-27 05:42:36.236] DEBUG: Ran the following query in 443ms:
[2025-01-27 05:42:36.240] DEBUG: Ran the following query in 438ms:
[2025-01-27 05:42:36.244] DEBUG: Ran the following query in 313ms:
[2025-01-27 05:42:36.247] DEBUG: Ran the following query in 457ms:
[2025-01-27 05:42:36.251] DEBUG: Ran the following query in 453ms:
[2025-01-27 05:42:36.255] DEBUG: Ran the following query in 464ms:
[2025-01-27 05:42:36.260] DEBUG: Ran the following query in 472ms:
[2025-01-27 05:42:36.270] DEBUG: Ran the following query in 456ms:
[2025-01-27 05:42:36.279] DEBUG: Ran the following query in 471ms:
[2025-01-27 05:42:36.283] DEBUG: Ran the following query in 472ms:
[2025-01-27 05:42:36.949] DEBUG: Ran the following query in 1007ms:
[2025-01-27 05:42:36.977] DEBUG: Ran the following query in 1130ms:
[2025-01-27 05:42:36.981] DEBUG: Ran the following query in 1027ms:
[2025-01-27 05:42:36.989] DEBUG: Ran the following query in 1135ms:
[2025-01-27 05:42:36.994] DEBUG: Ran the following query in 1131ms:
[2025-01-27 05:42:36.997] DEBUG: Ran the following query in 1137ms:
[2025-01-27 05:42:37.001] DEBUG: Ran the following query in 1134ms:
[2025-01-27 05:42:37.005] DEBUG: Ran the following query in 1149ms:
[2025-01-27 05:42:37.008] DEBUG: Ran the following query in 1120ms:
[2025-01-27 05:42:37.012] DEBUG: Ran the following query in 1121ms:
[2025-01-27 05:42:37.015] DEBUG: Ran the following query in 1122ms:
[2025-01-27 05:42:37.019] DEBUG: Ran the following query in 1124ms:
[2025-01-27 05:42:37.023] DEBUG: Ran the following query in 1126ms:
[2025-01-27 05:42:37.031] DEBUG: Ran the following query in 1145ms:
[2025-01-27 05:42:37.876] DEBUG: Ran the following query in 1894ms:
[2025-01-27 05:42:37.903] DEBUG: Ran the following query in 2023ms:
[2025-01-27 05:42:37.908] DEBUG: Ran the following query in 2024ms:
[2025-01-27 05:42:37.917] DEBUG: Ran the following query in 2016ms:
[2025-01-27 05:42:37.928] DEBUG: Ran the following query in 1739ms:
[2025-01-27 05:42:37.934] DEBUG: Ran the following query in 2026ms:
[2025-01-27 05:42:37.940] DEBUG: Ran the following query in 2036ms:
[2025-01-27 05:42:37.951] DEBUG: Ran the following query in 2040ms:
[2025-01-27 05:42:37.958] DEBUG: Ran the following query in 2044ms:
[2025-01-27 05:42:37.965] DEBUG: Ran the following query in 2049ms:
[2025-01-27 05:42:39.118] DEBUG: Ran the following query in 2919ms:
[2025-01-27 05:42:39.149] DEBUG: Ran the following query in 3223ms:
[2025-01-27 05:42:39.153] DEBUG: Ran the following query in 3231ms:
[2025-01-27 05:42:39.156] DEBUG: Ran the following query in 3232ms:
[2025-01-27 05:42:39.160] DEBUG: Ran the following query in 2940ms:
[2025-01-27 05:42:39.193] DEBUG: Ran the following query in 3255ms:
[2025-01-27 05:42:39.846] DEBUG: Ran the following query in 3907ms:
[2025-01-27 05:42:39.863] DEBUG: Ran the following query in 3922ms:
[2025-01-27 05:42:39.877] DEBUG: Ran the following query in 3613ms:
[2025-01-27 05:42:39.932] DEBUG: Ran the following query in 3985ms:
[2025-01-27 05:42:40.745] DEBUG: Ran the following query in 4793ms:
[2025-01-27 05:42:41.015] DEBUG: Ran the following query in 5067ms:
[2025-01-27 05:42:41.069] DEBUG: Ran the following query in 4099ms:
[2025-01-27 05:42:41.073] DEBUG: Ran the following query in 5122ms:
[2025-01-27 05:42:41.081] DEBUG: Ran the following query in 5103ms:
[2025-01-27 05:42:42.306] DEBUG: Ran the following query in 5322ms:
[2025-01-27 05:42:42.329] DEBUG: Ran the following query in 6348ms:
[2025-01-27 05:42:42.353] DEBUG: Ran the following query in 6347ms:
[2025-01-27 05:42:44.313] DEBUG: Ran the following query in 8266ms:
[2025-01-27 05:42:44.317] DEBUG: Ran the following query in 8141ms:
[2025-01-27 05:42:44.319] DEBUG: Ran the following query in 8188ms:
[2025-01-27 05:42:44.326] DEBUG: Ran the following query in 8146ms:
[2025-01-27 05:42:44.343] DEBUG: Ran the following query in 8160ms:
[2025-01-27 05:42:44.369] DEBUG: Ran the following query in 6457ms:
[2025-01-27 05:42:44.410] DEBUG: Ran the following query in 8322ms:
[2025-01-27 05:42:44.414] DEBUG: Ran the following query in 8235ms:
[2025-01-27 05:42:44.414] DEBUG: Ran the following query in 8238ms:
[2025-01-27 05:42:44.435] DEBUG: Ran the following query in 8237ms:
[2025-01-27 05:42:45.544] DEBUG: Ran the following query in 7599ms:
[2025-01-27 05:42:45.665] DEBUG: Ran the following query in 9459ms:
[2025-01-27 05:42:46.840] DEBUG: Ran the following query in 10633ms:
[2025-01-27 05:42:46.857] DEBUG: Ran the following query in 7711ms:
[2025-01-27 05:42:46.883] DEBUG: Ran the following query in 10659ms:
[2025-01-27 05:42:52.887] DEBUG: Ran the following query in 16662ms:
[2025-01-27 05:42:53.265] DEBUG: Ran the following query in 17035ms:
[2025-01-27 05:42:53.303] DEBUG: Ran the following query in 17064ms:
[2025-01-27 05:42:53.336] DEBUG: Ran the following query in 17094ms:
[2025-01-27 05:42:53.341] DEBUG: Ran the following query in 17098ms:
[2025-01-27 05:42:53.364] DEBUG: Ran the following query in 17118ms:
[2025-01-27 05:42:53.373] DEBUG: Ran the following query in 17126ms:
[2025-01-27 05:42:53.401] DEBUG: Ran the following query in 17151ms:
[2025-01-27 05:42:53.405] DEBUG: Ran the following query in 17154ms:
[2025-01-27 05:42:53.425] DEBUG: Ran the following query in 17171ms:
[2025-01-27 05:42:53.432] DEBUG: Ran the following query in 17177ms:
[2025-01-27 05:42:53.459] DEBUG: Ran the following query in 17201ms:
[2025-01-27 05:42:53.463] DEBUG: Ran the following query in 17204ms:
[2025-01-27 05:42:53.486] DEBUG: Ran the following query in 17223ms:
[2025-01-27 05:42:53.496] DEBUG: Ran the following query in 14333ms:
[2025-01-27 05:42:53.528] DEBUG: Ran the following query in 17293ms:
[2025-01-27 05:42:53.563] DEBUG: Ran the following query in 17324ms:
[2025-01-27 05:42:53.617] DEBUG: Ran the following query in 17382ms:
[2025-01-27 05:42:53.618] DEBUG: Ran the following query in 17389ms:
[2025-01-27 05:42:53.632] DEBUG: Ran the following query in 17359ms:
[2025-01-27 05:42:54.374] DEBUG: Ran the following query in 18089ms:
[2025-01-27 05:42:54.380] DEBUG: Ran the following query in 18093ms:
[2025-01-27 05:42:54.416] DEBUG: Ran the following query in 18122ms:
[2025-01-27 05:42:54.420] DEBUG: Ran the following query in 17450ms:
[2025-01-27 05:42:54.427] DEBUG: Ran the following query in 14526ms:
[2025-01-27 05:42:54.437] DEBUG: Ran the following query in 18155ms:
[2025-01-27 05:42:55.936] DEBUG: Ran the following query in 19654ms:
[2025-01-27 05:42:55.978] DEBUG: Ran the following query in 19704ms:
[2025-01-27 05:42:56.021] DEBUG: Ran the following query in 19038ms:
[2025-01-27 05:42:56.837] DEBUG: Ran the following query in 19858ms:
[2025-01-27 05:42:56.841] DEBUG: Ran the following query in 19861ms:
[2025-01-27 05:42:56.868] DEBUG: Ran the following query in 15799ms:
[2025-01-27 05:42:56.885] DEBUG: Ran the following query in 19893ms:
[2025-01-27 05:43:06.144] DEBUG: Ran the following query in 29147ms:
[2025-01-27 05:43:06.157] DEBUG: Ran the following query in 29153ms:
[2025-01-27 05:43:06.161] DEBUG: Ran the following query in 29157ms:
[2025-01-27 05:43:06.184] DEBUG: Ran the following query in 29183ms:
[2025-01-27 05:43:06.212] DEBUG: Ran the following query in 29204ms:
[2025-01-27 05:43:06.228] DEBUG: Ran the following query in 29218ms:
[2025-01-27 05:43:06.235] DEBUG: Ran the following query in 29224ms:
[2025-01-27 05:43:06.265] DEBUG: Ran the following query in 29250ms:
[2025-01-27 05:43:06.282] DEBUG: Ran the following query in 29267ms:
[2025-01-27 05:43:06.312] DEBUG: Ran the following query in 29305ms:
[2025-01-27 05:43:06.318] DEBUG: Ran the following query in 29299ms:
[2025-01-27 05:43:06.346] DEBUG: Ran the following query in 29324ms:
[2025-01-27 05:43:06.357] DEBUG: Ran the following query in 29334ms:
[2025-01-27 05:43:06.399] DEBUG: Ran the following query in 29369ms:
[2025-01-27 05:43:06.419] DEBUG: Ran the following query in 29388ms:
[2025-01-27 05:43:06.469] DEBUG: Ran the following query in 29451ms:
[2025-01-27 05:43:06.476] DEBUG: Ran the following query in 29441ms:
[2025-01-27 05:43:06.527] DEBUG: Ran the following query in 28624ms:
[2025-01-27 05:43:06.531] DEBUG: Ran the following query in 28628ms:
[2025-01-27 05:43:06.545] DEBUG: Ran the following query in 28639ms:
[2025-01-27 05:43:06.554] DEBUG: Ran the following query in 28647ms:
[2025-01-27 05:43:06.599] DEBUG: Ran the following query in 29565ms:
[2025-01-27 05:43:06.600] DEBUG: Ran the following query in 24272ms:
[2025-01-27 05:43:06.623] DEBUG: Ran the following query in 29623ms:
[2025-01-27 05:43:08.556] DEBUG: Ran the following query in 30646ms:
[2025-01-27 05:43:08.572] DEBUG: Ran the following query in 31575ms:
[2025-01-27 05:43:08.615] DEBUG: Ran the following query in 31622ms:
[2025-01-27 05:43:08.669] DEBUG: Ran the following query in 30730ms:
[2025-01-27 05:43:11.030] DEBUG: Ran the following query in 33086ms:
[2025-01-27 05:43:11.040] DEBUG: Ran the following query in 26627ms:
[2025-01-27 05:43:12.107] DEBUG: Ran the following query in 34168ms:
[2025-01-27 05:43:12.154] DEBUG: Ran the following query in 34234ms:
[2025-01-27 05:43:12.160] DEBUG: Ran the following query in 34239ms:
[2025-01-27 05:43:13.380] DEBUG: Ran the following query in 35449ms:
[2025-01-27 05:43:13.390] DEBUG: Ran the following query in 35458ms:
[2025-01-27 05:43:13.423] DEBUG: Ran the following query in 35467ms:
[2025-01-27 05:43:14.584] DEBUG: Ran the following query in 36622ms:
[2025-01-27 05:43:15.728] DEBUG: Ran the following query in 36582ms:
[2025-01-27 05:43:15.743] DEBUG: Ran the following query in 36632ms:
[2025-01-27 05:43:15.777] DEBUG: Ran the following query in 36667ms:
[2025-01-27 05:43:15.782] DEBUG: Ran the following query in 37818ms:
[2025-01-27 05:43:16.970] DEBUG: Ran the following query in 31404ms:
[2025-01-27 05:43:16.976] DEBUG: Ran the following query in 39019ms:
[2025-01-27 05:43:17.011] DEBUG: Ran the following query in 37859ms:
[2025-01-27 05:43:18.223] DEBUG: Ran the following query in 39070ms:
[2025-01-27 05:43:19.382] DEBUG: Ran the following query in 40223ms:
[2025-01-27 05:43:19.382] DEBUG: Ran the following query in 32525ms:
[2025-01-27 05:43:19.394] DEBUG: Ran the following query in 40238ms:
[2025-01-27 05:43:20.581] DEBUG: Ran the following query in 41419ms:
[2025-01-27 05:43:20.588] DEBUG: Ran the following query in 41433ms:
[2025-01-27 05:43:20.591] DEBUG: Ran the following query in 41432ms:
[2025-01-27 05:43:20.616] DEBUG: Ran the following query in 40753ms:
[2025-01-27 05:43:20.619] DEBUG: Ran the following query in 40755ms:
[2025-01-27 05:43:20.625] DEBUG: Ran the following query in 27062ms:
[2025-01-27 05:43:20.628] DEBUG: Ran the following query in 40727ms:
[2025-01-27 05:43:20.643] DEBUG: Ran the following query in 39637ms:
[2025-01-27 05:43:20.647] DEBUG: Ran the following query in 39641ms:
[2025-01-27 05:43:20.655] DEBUG: Ran the following query in 39586ms:
[2025-01-27 05:43:20.662] DEBUG: Ran the following query in 24651ms:
[2025-01-27 05:43:21.868] DEBUG: Ran the following query in 39570ms:
[2025-01-27 05:43:21.902] DEBUG: Ran the following query in 39605ms:
[2025-01-27 05:43:21.904] DEBUG: Ran the following query in 25042ms:
[2025-01-27 05:43:21.912] DEBUG: Ran the following query in 39584ms:
[2025-01-27 05:43:21.967] DEBUG: Ran the following query in 37654ms:
[2025-01-27 05:43:25.512] DEBUG: Ran the following query in 41196ms:
[2025-01-27 05:43:25.556] DEBUG: Ran the following query in 41234ms:
[2025-01-27 05:43:25.603] DEBUG: Ran the following query in 41260ms:
[2025-01-27 05:43:25.605] DEBUG: Ran the following query in 41262ms:
[2025-01-27 05:43:25.617] DEBUG: Ran the following query in 41272ms:
[2025-01-27 05:43:25.622] DEBUG: Ran the following query in 41276ms:
[2025-01-27 05:43:25.645] DEBUG: Ran the following query in 41245ms:
[2025-01-27 05:43:25.648] DEBUG: Ran the following query in 41248ms:
[2025-01-27 05:43:25.658] DEBUG: Ran the following query in 41245ms:
[2025-01-27 05:43:25.658] DEBUG: Ran the following query in 17007ms:
[2025-01-27 05:43:25.670] DEBUG: Ran the following query in 41354ms:
[2025-01-27 05:43:26.890] DEBUG: Ran the following query in 42577ms:
[2025-01-27 05:43:26.909] DEBUG: Ran the following query in 42588ms:
[2025-01-27 05:43:26.915] DEBUG: Ran the following query in 41349ms:
[2025-01-27 05:43:26.922] DEBUG: Ran the following query in 13508ms:
[2025-01-27 05:43:26.929] DEBUG: Ran the following query in 40072ms:
[2025-01-27 05:43:26.936] DEBUG: Ran the following query in 9939ms:
[2025-01-27 05:43:26.945] DEBUG: Ran the following query in 33700ms:
[2025-01-27 05:43:28.151] DEBUG: Ran the following query in 34905ms:
[2025-01-27 05:43:28.161] DEBUG: Ran the following query in 34825ms:
[2025-01-27 05:43:28.164] DEBUG: Ran the following query in 34800ms:
[2025-01-27 05:43:28.166] DEBUG: Ran the following query in 34873ms:
[2025-01-27 05:43:28.169] DEBUG: Ran the following query in 34875ms:
[2025-01-27 05:43:28.176] DEBUG: Ran the following query in 34812ms:
[2025-01-27 05:43:28.188] DEBUG: Ran the following query in 34763ms:
[2025-01-27 05:43:28.191] DEBUG: Ran the following query in 34766ms:
[2025-01-27 05:43:28.198] DEBUG: Ran the following query in 34739ms:
[2025-01-27 05:43:28.201] DEBUG: Ran the following query in 34742ms:
[2025-01-27 05:43:28.206] DEBUG: Ran the following query in 34720ms:
[2025-01-27 05:43:28.209] DEBUG: Ran the following query in 34723ms:
[2025-01-27 05:43:28.214] DEBUG: Ran the following query in 34694ms:
[2025-01-27 05:43:28.216] DEBUG: Ran the following query in 34696ms:
[2025-01-27 05:43:28.222] DEBUG: Ran the following query in 34659ms:
[2025-01-27 05:43:28.228] DEBUG: Ran the following query in 33812ms:
[2025-01-27 05:43:28.230] DEBUG: Ran the following query in 33814ms:
[2025-01-27 05:43:28.236] DEBUG: Ran the following query in 33817ms:
[2025-01-27 05:43:28.241] DEBUG: Ran the following query in 33822ms:
[2025-01-27 05:43:28.256] DEBUG: Ran the following query in 32284ms:
[2025-01-27 05:43:28.259] DEBUG: Ran the following query in 32287ms:
[2025-01-27 05:43:28.280] DEBUG: Ran the following query in 32269ms:
[2025-01-27 05:43:28.286] DEBUG: Ran the following query in 31425ms:
[2025-01-27 05:43:28.292] DEBUG: Ran the following query in 7683ms:
[2025-01-27 05:43:28.297] DEBUG: Ran the following query in 34961ms:
[2025-01-27 05:43:28.303] DEBUG: Ran the following query in 34902ms:
[2025-01-27 05:43:28.306] DEBUG: Ran the following query in 34905ms:
[2025-01-27 05:43:28.316] DEBUG: Ran the following query in 22136ms:
[2025-01-27 05:43:28.322] DEBUG: Ran the following query in 22095ms:
[2025-01-27 05:43:28.324] DEBUG: Ran the following query in 22096ms:
[2025-01-27 05:43:28.329] DEBUG: Ran the following query in 22065ms:
[2025-01-27 05:43:28.331] DEBUG: Ran the following query in 22067ms:
[2025-01-27 05:43:28.336] DEBUG: Ran the following query in 22024ms:
[2025-01-27 05:43:28.338] DEBUG: Ran the following query in 22026ms:
[2025-01-27 05:43:28.349] DEBUG: Ran the following query in 22003ms:
[2025-01-27 05:43:28.352] DEBUG: Ran the following query in 22173ms:
[2025-01-27 05:43:28.358] DEBUG: Ran the following query in 21958ms:
[2025-01-27 05:43:28.360] DEBUG: Ran the following query in 22014ms:
[2025-01-27 05:43:28.365] DEBUG: Ran the following query in 21896ms:
[2025-01-27 05:43:28.367] DEBUG: Ran the following query in 21898ms:
[2025-01-27 05:43:28.373] DEBUG: Ran the following query in 21847ms:
[2025-01-27 05:43:28.375] DEBUG: Ran the following query in 21849ms:
[2025-01-27 05:43:28.381] DEBUG: Ran the following query in 21783ms:
[2025-01-27 05:43:28.383] DEBUG: Ran the following query in 22179ms:
[2025-01-27 05:43:28.385] DEBUG: Ran the following query in 19782ms:
[2025-01-27 05:43:28.388] DEBUG: Ran the following query in 21789ms:
[2025-01-27 05:43:28.393] DEBUG: Ran the following query in 19742ms:
[2025-01-27 05:43:28.399] DEBUG: Ran the following query in 6503ms:
[2025-01-27 05:43:28.416] DEBUG: Ran the following query in 19813ms:
[2025-01-27 05:43:28.422] DEBUG: Ran the following query in 22023ms:
[2025-01-27 05:43:28.428] DEBUG: Ran the following query in 22223ms:
[2025-01-27 05:43:28.441] DEBUG: Ran the following query in 16339ms:
[2025-01-27 05:43:28.446] DEBUG: Ran the following query in 16292ms:
[2025-01-27 05:43:28.448] DEBUG: Ran the following query in 15034ms:
[2025-01-27 05:43:28.453] DEBUG: Ran the following query in 12682ms:
[2025-01-27 05:43:28.456] DEBUG: Ran the following query in 15076ms:
[2025-01-27 05:43:28.458] DEBUG: Ran the following query in 11489ms:
[2025-01-27 05:43:28.460] DEBUG: Ran the following query in 15080ms:
[2025-01-27 05:43:28.466] DEBUG: Ran the following query in 11469ms:
[2025-01-27 05:43:28.471] DEBUG: Ran the following query in 9090ms:
[2025-01-27 05:43:28.473] DEBUG: Ran the following query in 11503ms:
[2025-01-27 05:43:28.478] DEBUG: Ran the following query in 12707ms:
[2025-01-27 05:43:28.484] DEBUG: Ran the following query in 7903ms:
[2025-01-27 05:43:28.487] DEBUG: Ran the following query in 7878ms:
[2025-01-27 05:43:28.492] DEBUG: Ran the following query in 1583ms:
[2025-01-27 05:43:28.495] DEBUG: Ran the following query in 16392ms:
[2025-01-27 05:43:28.537] DEBUG: Ran the following query in 9156ms:
[2025-01-27 05:43:28.547] DEBUG: Ran the following query in 7966ms:
[2025-01-27 05:43:28.556] DEBUG: Ran the following query in 16402ms:
[2025-01-27 05:43:28.626] DEBUG: Ran the following query in 6730ms:
[2025-01-27 05:43:28.634] DEBUG: Ran the following query in 1725ms:
[2025-01-27 05:43:28.638] DEBUG: Ran the following query in 382ms:
[2025-01-27 05:43:28.639] DEBUG: Ran the following query in 383ms:
[2025-01-27 05:43:28.642] DEBUG: Ran the following query in 3098ms:
[2025-01-27 05:43:28.644] DEBUG: Ran the following query in 2999ms:
[2025-01-27 05:43:28.648] DEBUG: Ran the following query in 3046ms:
[2025-01-27 05:43:28.651] DEBUG: Ran the following query in 3049ms:
[2025-01-27 05:43:28.656] DEBUG: Ran the following query in 3112ms:
[2025-01-27 05:43:28.661] DEBUG: Ran the following query in 3015ms:
[2025-01-27 05:43:30.442] DEBUG: Ran the following query in 45ms:
[2025-01-27 05:43:30.444] DEBUG: Ran the following query in 45ms:
`
@ScuttleSE commented on GitHub (Jan 27, 2025):
Almost all of the queries at similar to this one:
SELECTbookAuthor.id,bookAuthor.authorId,bookAuthor.createdAt,bookAuthor.bookId,author.idASauthor.id,author.nameASauthor.nameFROMbookAuthorsASbookAuthorLEFT OUTER JOINauthorsASauthorONbookAuthor.authorId=author.idWHEREbookAuthor.bookIdIN ('cce1cccd-16a9-4526-a8c6-e351720dea05', '70a19088-5291-40ef-99f9-099355ab1c55', '2ca1c76b-6166-405b-9fb9-fcd91a31a3f1', '3f5708b1-c1bd-4ddd-9ce1-a22a51168952', '5fc117d1-5a68-43de-9cbb-edb9c3380664', '6714cfe4-3ccb-4540-97ca-0a80987b676f', 'f0dc204c-5e3e-470d-9a6e-0f97db69bf7a', 'a2a6a223-558e-4efd-bbcf-0f319377978d', '01cc19d2-e855-4046-83a1-a9adf4350e1a', '826c106a-17af-40f2-b72d-37f78104303c', 'a270e6ba-69b9-4ac6-9b03-afd22c99b35f', '5e184ebd-71bc-417b-833a-b435b2ba0dea', 'd21992d2-1ddb-4dcd-b028-264bd27948db', 'dfc1bc55-defa-4be2-ad44-30f6625784c6', '1f25cd61-f3b4-4b8c-8001-3ba8f3cc59dd', '45d35118-313e-4b38-a057-2e1076c5b1a3', '008314ae-ac23-450a-934d-2a6464987da5', '17f9ae17-abe4-4c73-ad9e-2c5c8d3018c4', '15720dd1-a67d-4ba0-b84e-d8c69e093d02', '48fa1bf3-4ccd-47d1-a5e2-eda5ffd58629', '1c5b3e53-9d52-48f8-93a6-7374dbd55de4', '08af8dbf-89dd-4864-88f1-64cc3a120d24', 'aff1ebfc-5f4b-46f9-866d-09c98263f8d8', 'deb7d449-ff7c-4abd-9a61-48466d1eaa80', 'ae0eb3c5-d6ac-4936-8aa3-a7d290f38016', 'd73dfde1-086e-4b73-92b4-a9ccd9de7422', '1d944758-37f4-458a-b8eb-eb1dbf00815b', '4faba6ea-e1dc-48b6-9585-a023eb5237c6', '8db683d2-98ef-4c85-9fa6-bd8fed8474e7', '35def283-fa45-48f7-8c8e-005eb0b3aba9', '919f5175-c789-4e36-88c9-023814caf063', '70ba7717-6506-4914-bcb6-999a93f9a626', '2c2f9f97-3171-45d3-bdd8-ffba2ac04f3a', '0a6c7b10-a687-45b0-9a30-308a23514e31', '410505ce-0e15-4b31-bf77-587d35bb15fc') ORDER BYbookAuthor.createdAtASC;@mikiher commented on GitHub (Jan 27, 2025):
Thanks for the data.
Your issue does seem similar to the one described by others.
Around which piece of code did you put your measurement?
Regarding the query you show - this is actually just the last part of a multi-query sequence (this once brings the authors for the books that were returned by main query). If you're really into analyzing query performance on your setup and are able to modify the code, you can add the following lines here
So the sequelize query looks like this:
You will see in the logs that the sequelize query is translated into 4 SQL queries:
I suspect that the queries which seem to take a lot of time are ones where there are multiple concurrent queries running together (due to fast scrolling), Issue no. 1 in my comment above. Concurrent queries seems to seriously mess up Sequelize/SQLite performance.
As I said above, I'm trying to play with increasing the fetch size to decrease the probability of concurrency. I will also try playing with disallowing concurrent queries altogether (i.e. put a mutex on query execution). Stay tuned...
@stelle007 commented on GitHub (Jan 27, 2025):
WOW this threat took off with a lot of posts :)
I have also tried running it (ABS) in docker on a Debian server, AMD Ryzen 7 5700G, 128 Gb Ram and NVME storage. And a Proxmox LXC container, (Don't remember hardware)
I did fresh install and podcast fetch on all, but It showed the same symptoms.
@mikiher commented on GitHub (Jan 27, 2025):
@stelle007 Thank you for the data.
I think your case is somewhat different from the others (podcast and books library queries are different queries).
Just a couple to make sure I get it correct:
Libraryto load the page) also very slow? or is it only when you scroll down?@PhantomPhoton commented on GitHub (Jan 27, 2025):
I started looking into why it appeared as all the concurrent queries appeared to be serialized and discovered that sequelize currently only supports Single-thread or Serialized sqlite threading models. The Multi-thread model is completely safe to use and would allow concurrent read query execution (it still serializes write queries). I added a feature request to sequelize to add support for the Multi-thread mode which should really improve the responsiveness for these concurrent queries.
@stelle007 commented on GitHub (Jan 27, 2025):
At one point I had my Audio books on the same container, and they were Just as slow to load as the Podcasts, that's why I made a new container for the Audio books.
@mikiher commented on GitHub (Jan 28, 2025):
What I'm struggling with this explanation is that when concurrent queries are running, the cummulative run time seems to go much beyond the sum of expected run times of the individual queries (for example, non-concurrent requests take 50 ms each, but 5 concurrent requests take 5 seconds). Something really fishy is happening, either inside Sequelize or in SQLite (I suspect Sequelize).
Also, note that we're currently using Sequelize 6, not 7.
@PhantomPhoton commented on GitHub (Jan 28, 2025):
I think you are absolutely right. There does seem to be something else going on. Getting concurrent read queries is just one potential improvement, presuming there's not an architectural reason why Sequelize doesn't support multi-threaded queries, however, figuring out faster query performance would be a huge win everywhere.
Thank you for that clarification. I checked the docs and 6 has the same restriction as 7 does.
@mikiher commented on GitHub (Jan 28, 2025):
@ScuttleSE
Ah, stupid me... you didn't need to change the code, you just ran the server with
QUERY_LOGGING=benchmark.The code I showed above logs the underlying SQL queries and the timing for this specific Sequelize query, rather than using the
QUERY_LOGGINGenv variable, which shows it for all Sequelize queries. But obviously you need to modify the code to do this, whereas withQUERY_LOGGING, you don't.@mikiher commented on GitHub (Feb 1, 2025):
@ScuttleSE and others who are sorting their book library by title (ascending or descending) - I've made some progress investigating this, and I have a couple of additional questions regarding your specific case:
Ignore prefixes when sortingenabled?Collapse Seriesin the library page (the option that shows all books in a series as a single entry)?@ScuttleSE commented on GitHub (Feb 1, 2025):
Yes on the first one, with just "the" as an ignored prefix
No to the second one.
@JBlond commented on GitHub (Feb 2, 2025):
no
no
@mikiher commented on GitHub (Feb 2, 2025):
Thank you, I'm working on some changes that are likely to improve your loading and scrolling experience. Stay tuned.
@mikiher commented on GitHub (Feb 6, 2025):
@stelle007
While I'm working on this, v2.19.0 has been released, which allows setting some SQLite pragma values through environment variables, requested by @PhantomPhoton who has a setup similar to yours (a very large podcast database).
@PhantomPhoton reports in #3750 that setting up some of those values improved his page load time siginificantly. I'm not yet directly working on podcast library query performance, but in the meantime you may want to experiment with setting up these values on your setup as well.
The environment variables and values used by @PhantomPhoton were:
You can set these up in your docker container, restart the container, and see if you see any changes in latency.
To verify that the server is using the environment variables you set, look for the following lines in the server log:
@stelle007 commented on GitHub (Feb 6, 2025):
Thank you, that is such a huge improvement, thank you both so much, I think it is 2-3 seconds per page now.
This is great !
Thank you !
@mikiher commented on GitHub (Feb 6, 2025):
I'm happy this works for you. Although it doesn't solve the core issue, it's good that it's improving the situaiton significantly.
2-3 seconds per page is still way too high, and I'm still intending to work on improving this by optimizing database queries, though it might be some time until I get to it, and the solution might not be simple.
@mikiher commented on GitHub (Feb 12, 2025):
@stelle007 @PhantomPhoton
I'd like to separate your issue (podcast library page loading performance) from the book library page loading performance which was address here. I opened a separate issue #3965 to track that.
Let's continue discussing there.
@mikiher commented on GitHub (Feb 13, 2025):
@ScuttleSE @JBlond
I'm interested to know if v2.19.2 improved book page loading time for you (please skip v2.19.1, it was a buggy release).
@JBlond commented on GitHub (Feb 13, 2025):
it is a lot faster with my lib 266 books. Once the images are in the browser cache each slow scrolling like 5 rows. Each row 8 books (aka 40 books at once) it takes now only 25 to 40 ms for the query.
Thank you for your awesome work.
@ScuttleSE commented on GitHub (Feb 13, 2025):
It's a lot faster. My library is just under 2000 books. Opening the library and scrolling to the bottom now takes about 6 seconds
Still looks like it is loading all 2000 coverimages, but it is doing it a lot faster
@mikiher commented on GitHub (Feb 13, 2025):
Whether it loads all images or not depends on how fast you scroll - if scrolling is fast enough, it may skip some of them.
You can check how many actual image requests are done through the developer console network tab - click on
Imgto show only image requests. I will look if there's some more optimization to squeeze there, but there's a tradeoff here - if you wait too late loading the items (which in turns triggers the cover image fetch), you're more likely to see a visible delay in loading the page when you stop scrolling.@darthShadow commented on GitHub (Feb 13, 2025):
This was closed earlier but may be worth a revisit: https://github.com/advplyr/audiobookshelf/issues/3090
That way, you could delay loading the actual images till they enter the viewport.
@ScuttleSE commented on GitHub (Feb 13, 2025):
Just tested that. Loaded up the library. I have 25 thumbnails visible at 180 pixels(?). Quickly pulled the scrollbar all the way to the bottom
608 images loaded. Out of 1986.
On a local connection I guess it's not an issue, but remotely it would really slow things down I assume.
@mikiher commented on GitHub (Feb 13, 2025):
Interesting. I will look into that.
@PhantomPhoton commented on GitHub (Feb 13, 2025):
I've been meaning to look into why my browser doesn't cache the cover images (least for podcasts). Every cover is requested from the server, as if it's not getting cached at all, and if you scroll down too fast, it seems to hit a point where everything just sits until it times out. If the cover images are cached by the browser, first load would be rough, but future loads would be a lot better. I presume it's some sort of issue with the response headers preventing the cache, but haven't made the time to get them sorted out yet.
@mikiher commented on GitHub (Feb 13, 2025):
That sounds about right. I will see if I can get the number down a bit, and the blur hash feature mentioned by @darthShadow might help here as well.
Yeah, of course, depending on bandwith and connection type - but that is unavoidable right now. Note that once the browser has them, the images are locally cached for about a day, so scrolling up and down after the initial scroll will load images much faster. Of course if you have a lot of books, they might be thrown out of the cache, but generally the browser cache can hold a lot images.
@mikiher commented on GitHub (Feb 13, 2025):
No, that should not happen. The images are supposed to have response headers that allow browser caching for 24 hours. If that does not work, we should fix it. After you scroll up and down the library, do you still see image requests going out in the Network tab (Note that the requests might still appear, but the size column should show them as served from the browser's disk cache).
Update: yeah I just checked, the cover image response headers are correctly set (I remembered since I wrote it):
Which means that the browser should cache the image for 86400 seconds==24 hours. The
privatejust means that caching by any proxies in the middle is not allowed. We could certainly increase the TTL to more than 24 hours. I think I did that to be on the safe side, but we could essentially cache forever - there's a cache busting mechanism which changes the cover image url if the image changed.@PhantomPhoton commented on GitHub (Feb 13, 2025):
Oh my, I am a bonehead! So my cover requests included
Cache-Control: no-cache, which is why they weren't being cached, I had toggled disable caches in dev tools when I was benchmarking the sql pragma changes and forgot to toggle it back...Apologies for the incorrect issue! Everything is working just fine
@mikiher commented on GitHub (Feb 13, 2025):
Yeah, I also have disable-cache enabled in my browser dev tools Network tab by default for testing purposes...
@Eftimin70 commented on GitHub (Feb 13, 2025):
The normal library view is now really fast, thank you! But with "collapse series" selected it still is a lot slower than pre-2.4.0
@mikiher commented on GitHub (Feb 13, 2025):
Yes, the "collapse series" setting really complicates the database queries, and that code path has not been optimized yet.
I tried to focus first on the the some of the queries that are relatively easier to optimize - some of them require more effort.