mirror of
https://github.com/advplyr/audiobookshelf.git
synced 2026-05-30 23:40:40 +02:00
[Bug]: Podcast library latency/timeouts due to cache thrashing and query contention (v2.33.0+) #3273
Open
opened 2026-04-25 00:14:40 +02:00 by adam
·
3 comments
No Branch/Tag Specified
master
book_tags_genres_dedupe
episode_download_fallback
Issue-4540-SortBy-StartedDate-and-FinishedDate
episode_meta_tagging
fix_authorize_race_condition
redirect_transcode_requests
progress_updated_sort
fix_ereader_socket_event
fix_change_empty_root_password
fix_podcast_session_track_index
fix_set_token
session_modal_user
localize_durations
fix_oidc_create_user
jwt_auth_refactor
fix_scanner_deleting_single_file_books
fix_mediaprogress_updatedat_2
experimental_next_client
podcast_episode_duration
episode-timestamps-clickable
book_author_secondary_sort_title
podcast_useragents
pathexists_user_access
fix_pathexists_join
book_author_secondary_sort
clean_duplicate_mediaprogress
sanitize_html_description
trix_prevent_attachments
check_path_api_fix
fix_mediaprogress_updatedat
increase_express_json_limit
fix_dockerfile_nunicode
search_episodes
audiobook_tools_update
episode_secondary_sorts
hls_stream_url_update
new_session_track_endpoint
audiobook_tools_enhancements
watcher_rescans_update
player_track_tooltip
fix_exclude_prefixes_crash
socket_item_events
fix_podcast_episode_scanner_promise
new_stats_controller
count_cache_for_userpermissions
parsing-opf-v3
validate_migration_files
fix-quick-match-all-crash
fix-chapter-end-sleep-timer
stringify_sequelize_query
remove-col-ambiguity
fix_next_prev_edit_description
details_trim_whitespace
fix_content_url_basepath
fix_logger_fatal
progress_bar_visibility
batch-edit-populate-map-details
feed_generator_updates
bookmark-modal-updates
migrate-library-item-in-scanner
migrate-new-library-items
migrate-podcasts-new-library-item-2
migrate-podcasts-new-library-item
fix-remove-episode-from-playlist
playback-session-use-new-library-item
refactor-library-item
fix-heatmap-caption
feed-episodes-upsert
share-media-player-media-session-api
remove-old-playlist
remove_old_collection_object
plugin-implementation-demo
feed_migration
refactor-feeds-from-item
fix_remove_authors_no_books
v2.17.3-fk-constraints-migration
migrations-first-upgrade
sqlite_2
feature/nuxt-target-server
waveform
sqlite
playlists
video
v2.35.1
v2.35.0
v2.34.0
v2.33.2
v2.33.1
v2.33.0
v2.32.1
v2.32.0
v2.31.0
v2.30.0
v2.29.0
v2.28.0
v2.27.0
v2.26.3
v2.26.2
v2.26.1
v2.26.0
v2.25.1
v2.25.0
v2.24.0
v2.23.0
v2.22.0
v2.21.0
v2.20.0
v2.19.5
v2.19.4
v2.19.3
v2.19.2
v2.19.1
v2.19.0
v2.18.1
v2.18.0
v2.17.7
v2.17.6
v2.17.5
v2.17.4
v2.17.3
v2.17.2
v2.17.1
v2.17.0
v2.16.2
v2.16.1
v2.16.0
v2.15.1
v2.15.0
v2.14.0
v2.13.4
v2.13.3
v2.13.2
v2.13.1
v2.13.0
v2.12.3
v2.12.2
v2.12.1
v2.12.0
v2.11.0
v2.10.1
v2.10.0
v2.9.0
v2.8.1
v2.8.0
v2.7.2
v2.7.1
v2.7.0
v2.6.0
v2.5.0
v2.4.4
v2.4.3
v2.4.2
v2.4.1
v2.4.0
v2.3.5
v2.3.4
v2.3.3
v2.3.2
v2.3.1
v2.3.0
v2.2.23
v2.2.22
v2.2.21
v2.2.20
v2.2.19
v2.2.18
v2.2.17
v2.2.16
v2.2.15
v2.2.14
v2.2.13
v2.2.12
v2.2.11
v2.2.10
v2.2.9
v2.2.8
v2.2.7
v2.2.6
v2.2.5
v2.2.4
v2.2.3
v2.2.2
v2.2.1
v2.2.0
v2.1.5
v2.1.4
v2.1.3
v2.1.2
v2.1.1
v2.1.0
v2.0.24
v2.0.23
v2.0.22
v2.0.21
v2.0.20
v2.0.19
v2.0.18
v2.0.17
v2.0.16
v2.0.15
v2.0.14
v2.0.13
v2.0.12
v2.0.11
v2.0.10
v2.0.9
v2.0.8
v2.0.7
v2.0.6
v2.0.5
v2.0.4
v2.0.3
v2.0.2
v2.0.1
v1.7.2
v1.7.1
v1.7.0
v1.6.0
v1.5.5
v1.5.0
v1.4.11
v1.4.9
v1.4.7
v1.4.6
v1.4.4
v1.4.2
v1.4.0
v1.4.1
v1.3.4
v1.3.3
v1.3.1
v1.2.8
v1.2.6
v1.2.5
v1.2.4
v1.2.1
v1.1.15
v1.1.14
v1.1.13
v1.1.12
v1.1.11
v1.1.10
v1.1.9
v1.1.8
v1.0.0
0.9.61-beta.0
0.9.61-beta
Labels
Clear labels
authentication
backlog
bug
chapter editor
config-issue
ebooks
encoding/embedding
enhancement
help wanted
listening sessions & progress
planned
possible plugin
progress sync
pull-request
sorting/filtering/searching
unable to reproduce
upload
users & permissions
waiting
Mirrored from GitHub Pull Request
No Label
bug
Milestone
No items
No Milestone
Projects
Clear projects
No project
Assignees
adam (Adam Melkus)
Clear assignees
No Assignees
Notifications
Due Date
No due date set.
Dependencies
No dependencies set.
Reference: starred/audiobookshelf#3273
Reference in New Issue
Block a user
Blocking a user prevents them from interacting with repositories, such as opening or commenting on pull requests or issues. Learn more about blocking 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 @drwggm on GitHub (Mar 20, 2026).
What happened?
Describe the bug
Large podcast libraries experience severe latency and socket timeouts when loading the home page and recent episodes page. The root causes are:
Podcast cron clears entire API cache every minute —
PodcastManager.runEpisodeCheck()saveslastEpisodeCheckandupdatedAton every check, even when no new episodes are found. Each save triggersafterUpdatehooks that callApiCacheManager.clear(), wiping the entire cache. With podcasts staggered across each minute, the cache is effectively useless.Promise.all()causes SQLite lock contention — PR #5073 parallelized personalized shelf queries. SQLite usestransactionType: 'IMMEDIATE', so parallel queries fight over the write lock rather than running concurrently. This adds overhead without improving throughput.getRecentEpisodesquery is too heavy — The/api/libraries/:id/recent-episodesendpoint runs a single query joiningpodcastEpisodes → podcasts → libraryItemswith a LEFT JOIN onmediaProgresses, filtering on$mediaProgresses.isFinished$withsubQuery: false. SQLite must materialize the entire joined result set (~2500+ episodes) before applying LIMIT, causing timeouts.What did you expect to happen?
Expected behavior
lastEpisodeChecktimestamp updates should not invalidate the entire API cacheSteps to reproduce the issue
To Reproduce
Audiobookshelf version
v2.33.0 / v2.33.1
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?
None
Logs
Additional Notes
It might be viewed as a performance issue, but in my setup, the recent change made the app and webapp unusable. Basically won't load anything.
The following fixes my issue:
Suggested fix
1. Selective cache invalidation for podcast/libraryItem
afterUpdate(server/managers/ApiCacheManager.js)Add
'podcast'and'libraryItem'tohighChurnModels. For these models, onlyafterUpdatehooks should use selective clearing (e.g., personalized shelf slices).afterCreate/afterDestroy(actual content changes) should still clear the full cache.2. Revert
Promise.all()to sequential queries (server/models/LibraryItem.js)In
getPersonalizedShelves(), revert both the book and podcast paths fromPromise.all()back to sequentialawaitcalls. SQLite's file-level locking means parallelism adds contention overhead without throughput gains.3. Split
getRecentEpisodesinto a two-step query (server/utils/queries/libraryItemsPodcastFilters.js)findAllwithattributes: ['id']and minimal join attributes, applying filter/sort/limit to get only the needed episode IDsfindAllwithid IN (...)to fetch full metadata for only the selected episodes@davidwilemski commented on GitHub (Mar 29, 2026):
I have ~2500 podcast episodes over ~30 shows on my install, some of which do have custom cron schedules. Though probably not that many recent episodes. I'm not seeing the performance problem you're seeing so I don't think a large library alone is sufficient to trigger this. I looked into this a bit (I have limited knowledge of audiobookshelf internals) and think this may be in part accidental due to the
mediaProgressesindex added in #5073.In particular, I noticed on an explain plan for the
getMediaFinishedquery ("Listen Again" shelf) seems like it probably got worse because sqlite is using the new index in this case for a condition ofThe query plan with the new
media_progresses_user_item_finished_timeindex is:This indicates to me we're using that new index which means the db is finding all potentially matching items and then sorting them by
updatedAttime afterwards in order to select the most recent 10.Whereas if I modify the query manually to force using the older
media_progresses_updated_aton the join to the media progresses, I see this query plan:I believe this one indicates we're instead traversing media progresses by updated at time so we only need to read as many progresses until find 10 that satisfy the
isFinishedcondition. (This could still have pathlological query behavior if we had many progresses that were not finished).I think a fix for that particular query problem would be to either modify the query logic in
getFilteredPodcastEpisodesto force the old index (not sure if that's possible here) or to add another new index tomediaProgressesfor this case.The Newest Episodes query didn't have anything that stood out to me when I looked at the query / ran an explain so I'm less sure why that would be taking longer, it seems to have a good index
I'm also not quite sure you'd be seeing your query cache reset every minute? Or are you saying you have a cron item occuring every minute, thus invalidating your cache? If so, that seems like potentially aggressive feed refreshing behavior, even if all 50+ podcasts published new episodes daily.
@drwggm commented on GitHub (Mar 30, 2026):
Thanks for digging into this — the query plan analysis is really insightful and I think you're onto something important that I missed.
On the index issue: You're right that the
media_progresses_user_item_finished_timeindex from #5073 could be causing SQLite's query planner to pick a worse plan forgetMediaFinished. TheEXPLAINoutput is compelling — with the new index, SQLite searches by userId then has to sort byupdatedAtvia a temp B-tree, whereas the old media_progresses_updated_at index lets it scan inupdatedAtorder and short-circuit once it finds 10 finished items. That's a big difference for the "Listen Again" shelf, especially with lots of progress rows.I didn't look at
EXPLAINplans at all — I was mostly going off observed latency and the log output. This is probably the more correct root cause for at least that shelf's slowness.On the cache invalidation: My podcasts have staggered cron schedules, so different groups fire every minute throughout the hour. In
PodcastManager.runEpisodeCheck(), every podcast getslastEpisodeCheckandupdatedAtsaved regardless of whether new episodes were found. Each save firesafterUpdatehooks. In the upstream code,podcastandlibraryItemaren't inhighChurnModels, so eachafterUpdatecallscache.clear(), wiping the entire API cache. With staggered schedules, there's essentially always a cron group firing, which means the cache is being cleared nearly every minute. It never lives long enough to serve a hit.You're right that a large library alone isn't sufficient. The severity depends on how many podcasts have auto-download enabled and how their cron schedules are staggered. With 50+ podcasts on staggered schedules the cache is effectively always invalidated, which compounds any underlying query performance issues.
On the "Newest Episodes" query: It's possible that was more of a compounding effect from cache invalidation + the index issue rather than the query itself being inherently slow. I'd be curious to see if the index fix alone resolves it for others.
Would a composite index like
(userId, isFinished, updatedAt)be the right shape to give SQLite a plan that can both filter onisFinishedand scan inupdatedAtorder without a temp B-tree? That seems like a more targeted fix than my two-step query split for thegetMediaFinishedcase at least.@davidwilemski commented on GitHub (Mar 30, 2026):
Ah I see what you mean on the cache invalidation thing. That makes sense now, I haven't looked into how the cache works here but yes it does seem like invalidating only if something changes could help you some there, if that's not already what is happening, like you mentioned in the original report. The query performance you reported isn't great but sub one second should be fine if the query result is able to be cached for longer than it is now...
On the Listen Again query I had been thinking an index on
(userId, mediaItemId, isFinished, updatedAt)(perhaps with updatedAt in specifically DESC order) would help here but I tried that on a copy of my database and the explain plan isn't looking better.For reference, here's the query for the listen again section, with the fields being selected removed. I found this via logs with the
QUERY_PROFILINGenv var set.It turns out when applying an index like this:
That I still see a plan that involves the temporary table sort:
I think to fully address this we'd need to add my index and restructure the query, perhaps in a way similar to what you mentioned in your first post. I think we would want to select the podcasts that have mediaProgresses with
isFinished = 1ordered byupdatedAtas a sub-query/CTE that we then used to build the rest of the query that joins all the tables together and selects the rows we found from the CTE. I can't currently think of a way to do this fully in the join but maybe it's possible.I'm not entirely sure how to rewrite the query to be in this format with the Sequelize library, I'm not experienced with it. I may give it a go.
I hesitate to suggest this, only do it if you're fully confident in your ability to manage this, but one thought to validate whether any further work in this area would help is to consider dropping the new
media_progresses_user_item_finished_timeindex. I'm not a maintainer of this project so definitely take what I'm saying here with a heavy grain of salt but if I were to try it I would: Make a copy of my sqlite database file, drop the table on the original sqlite file, and see whether performance got better on my instance (possibly stopping ABS before dropping the index and restarting it after that was done). I think it should be possible to drop and re-add the index with no other harmful effects but that is what the backup copy of the DB would be for, just in case.If performance did get better with the new index removed, then that at least that's a signal that we're on the right track. (though obviously that index was added for a different issue so it can't just be totally removed)