Enormous extremely slow queries that make server hard to use

[MissMakima 2]

One example:

2025-06-07 22:27:52.361 Debug SqliteItemRepository: GetItems query time (slow 6x): 2828ms. Query: select count(*) OVER() AS TotalRecordCount,A.type,A.Id,A.CommunityRating,A.IndexNumber,A.Name,A.Path,A.PremiereDate,A.Overview,A.ParentIndexNumber,A.OfficialRating,A.SortName,A.RunTimeTicks,A.Size,A.Container,A.DateCreated,A.guid,A.ParentId,A.DateLastSaved,A.SeriesName,A.Album,A.AlbumId,A.SeriesId,A.PresentationUniqueKey,A.ProviderIds,A.Images,A.TotalBitrate,A.SortIndexNumber,A.SortParentIndexNumber,A.IndexNumberEnd,UserDatas.IsFavorite,UserDatas.Played,UserDatas.PlaybackPositionTicks,UserDatas.AudioStreamIndex,UserDatas.SubtitleStreamIndex,((Coalesce(A.SortParentIndexNumber,A.ParentIndexNumber, 1) 
* 1000000) + Coalesce(A.SortIndexNumber, A.IndexNumber, 0) + (Select Case When Coalesce(A.ParentIndexNumber,1)=0 Then 0 Else 0.5 End) + (Select Case When Coalesce(A.ParentIndexNumber,1)=0 Then (Cast(Coalesce(A.IndexNumber, 0) as REAL) / 100000) Else 0 End)) EpisodeAbsoluteIndexNumber,(Select ShareLevel from UserItemShares join AncestorIds2 on AncestorIds2.AncestorId=UserItemShares.ItemId where UserItemShares.UserId=6146 and UserItemShares.ShareLevel not null and AncestorIds2.ItemId=A.Id order by Distance limit 1) as ShareLevel from MediaItems A left join (Select N.SeriesPresentationUniqueKey,((Coalesce(N.SortParentIndexNumber,N.ParentIndexNumber, 1) * 1000000) + Coalesce(N.SortIndexNumber, N.IndexNumber, 0) + (Select Case When Coalesce(N.ParentIndexNumber,1)=0 Then 0 Else 0.5 End) + (Select Case When Coalesce(N.ParentIndexNumber,1)=0 Then (Cast(Coalesce(N.IndexNumber, 0) as REAL) / 100000) Else 0 End)) AbsoluteIndexNumber,max(UserDatas_N.LastPlayedDateInt) LastPlayedDateInt,UserDatas_N.playbackPositionTicks from MediaItems N join UserDatas 
UserDatas_N on N.UserDataKeyId=UserDatas_N.UserDataKeyId And UserDatas_N.UserId=6146 where Type=8 and (UserDatas_N.Played=1 or UserDatas_N.playbackPositionTicks > 0) Group By N.SeriesPresentationUniqueKey ORDER BY UserDatas_N.LastPlayedDateInt desc, AbsoluteIndexNumber desc) LastWatchedEpisodes on LastWatchedEpisodes.SeriesPresentationUniqueKey=A.SeriesPresentationUniqueKey left join UserDatas on A.UserDataKeyId=UserDatas.UserDataKeyId And UserDatas.UserId=6146 where ((A.Type in (5,15,18) and UserDatas.playbackPositionTicks > 0) OR (A.Type=8 AND (UserDatas.playbackPositionTicks > 0 or Coalesce(UserDatas.played,0) = 0) AND (select case when LastWatchedEpisodes.playbackPositionTicks > 0 then EpisodeAbsoluteIndexNumber >= Coalesce(LastWatchedEpisodes.AbsoluteIndexNumber,EpisodeAbsoluteIndexNumber) else EpisodeAbsoluteIndexNumber > Coalesce(LastWatchedEpisodes.AbsoluteIndexNumber,EpisodeAbsoluteIndexNumber) end) AND LastWatchedEpisodes.LastPlayedDateInt not null)) AND (A.Type in (5,15,18) OR Coalesce(A.SortParentIndexNumber,A.ParentIndexNumber, -1) <> 0 OR UserDatas.playbackPositionTicks > 0) AND A.Type in (5,8,15,18) AND Coalesce(ShareLevel, 0) > 0 AND A.ExtraType is null AND Coalesce(UserDatas.HideFromResume,0)=0 AND COALESCE((select hidefromresume from userdatas where userdatas.userid=6146 and userdatas.userdatakeyid=(select userdatakeyid from mediaitems where mediaitems.id=Coalesce(A.seriesid,A.albumid))),0)=0 Group by coalesce(A.SeriesPresentationUniqueKey, A.PresentationUniqueKey) ORDER BY COALESCE(lastwatchedepisodes.lastplayeddateint, userdatas.lastplayeddateint, 0) DESC,Min(EpisodeAbsoluteIndexNumber) ASC LIMIT 12

These slow queries always have a length of 2000-5000ms and when lots of them happens together, the server will return 502 to all requests.

I have incresed my database cache size to 128GB and analyze limit to 10000 lines but not working.

Is my database totally corrupted?

embyserver.txt

[Neminem 1518]

Where is you media located ?

To me it look like bad network or slow storage.

Log Level    Time Stamp    Log Level    Source    Message
Info    2025-06-07 22:28:06    Info    Server    http/1.1 Response 206 to xx.yy.zz.rr Time: 28080ms. GET http://‌‍‍blabal.xyz‌/emby/videos/356627/original.mp4?DeviceId=LAPTOP-Q8GLJRDI&MediaSourceId=5509775584b1df63fda3e88248cc8c53&PlaySessionId=7ca3a4af9d1a47de9c27423e07b7f418&api_key=‌375bcac3c83d43219a87fa5a0d6bfa5e‌

[MissMakima 2]

3 hours ago, Neminem said:

Where is you media located ?

To me it look like bad network or slow storage.

Log Level    Time Stamp    Log Level    Source    Message
Info    2025-06-07 22:28:06    Info    Server    http/1.1 Response 206 to xx.yy.zz.rr Time: 28080ms. GET http://‌‍‍blabal.xyz‌/emby/videos/356627/original.mp4?DeviceId=LAPTOP-Q8GLJRDI&MediaSourceId=5509775584b1df63fda3e88248cc8c53&PlaySessionId=7ca3a4af9d1a47de9c27423e07b7f418&api_key=‌375bcac3c83d43219a87fa5a0d6bfa5e‌

Well my media is a mix of Cloud Drives like Google or Dropbox, and remote physical storages, which have a 250ms latency to my application server. They are combined together via Rclone.

[kikinjo 281]

Post rclone mount config

[Lessaj 467]

7 hours ago, Neminem said:

Where is you media located ?

To me it look like bad network or slow storage.

Log Level    Time Stamp    Log Level    Source    Message
Info    2025-06-07 22:28:06    Info    Server    http/1.1 Response 206 to xx.yy.zz.rr Time: 28080ms. GET http://‌‍‍blabal.xyz‌/emby/videos/356627/original.mp4?DeviceId=LAPTOP-Q8GLJRDI&MediaSourceId=5509775584b1df63fda3e88248cc8c53&PlaySessionId=7ca3a4af9d1a47de9c27423e07b7f418&api_key=‌375bcac3c83d43219a87fa5a0d6bfa5e‌

This type of request is normal to have a long duration, because the MP4 (also applies to mkv) is being streamed directly, so it's reporting the entire duration of the request, this would have been played for ~28 seconds.

[MissMakima 2]

10 hours ago, kikinjo said:

Post rclone mount config

I don't think the problem is related to rclone or any storage properties. The log was clear that the slow query is inside the SQLite itself. Maybe we should focus on that.

[Q-Droid 989]

I cringe when I see queries that look like your example. Surely there has to be a better approach to what that query is doing. This would be in the dev's hands.

How many media items do you have? How big are your DB files? You could try a vacuum cycle to optimize as much as it can.

Unless something has changed recently the analyze row limit is 1000. Your setting of 10000 will be ignored and capped to the limit.

You have two paths which could affect UI performance and response time. Cache and metadata should be on your fastest/lowest latency storage when you're storing metadata separate from the media. The type of storage behind /mnt/data/emby is unknown to us in this case. This is assuming the Emby server config and data are also on your fastest storage.

Quote

2025-06-07 22:27:21.197 Info Main: Cache path: /mnt/data/emby/cache/cache
2025-06-07 22:27:21.197 Info Main: Internal metadata path: /mnt/data/emby/metadata/metadata

 

[Luke 42077]

Hi, I believe some of these queries should be improved in the upcoming 4.9 server release. Thanks.

[MissMakima 2]

On 6/8/2025 at 11:55 PM, Q-Droid said:

I cringe when I see queries that look like your example. Surely there has to be a better approach to what that query is doing. This would be in the dev's hands.

How many media items do you have? How big are your DB files? You could try a vacuum cycle to optimize as much as it can.

Unless something has changed recently the analyze row limit is 1000. Your setting of 10000 will be ignored and capped to the limit.

You have two paths which could affect UI performance and response time. Cache and metadata should be on your fastest/lowest latency storage when you're storing metadata separate from the media. The type of storage behind /mnt/data/emby is unknown to us in this case. This is assuming the Emby server config and data are also on your fastest storage.

 

Thank you! You pointed out a significant varible.

I do think that these two paths are in another continent physically, but meantime I think the Rclone will cache all these datas locally after first time it read it. It shouldn't cause so much slow queries after the short initiation time of the server.

Edited June 9, 2025 by MissMakima