SQLite In-Memory database

[sscheib 11]

Hello,

I've watched my log file for the last few hours and noticed over and over slow queries from the SQLite database (I guess its from the library.db).

 

Example:

 Debug SqliteItemRepository: GetItemList query time (slow): 7443ms. Query: select type,data,StartDate,EndDate,IsOffline,ChannelId,IsMovie,IsSports,IsKids,IsSeries,IsLive,IsNews,IsPremiere,EpisodeTitle,IsRepeat,CommunityRating,IndexNumber,IsHD,ExternalEtag,DateLastRefreshed,Name,Path,PremiereDate,Overview,ParentIndexNumber,ProductionYear,OfficialRating,ForcedSortName,RunTimeTicks,VoteCount,DateCreated,DateModified,guid,Genres,ParentId,Audio,ExternalServiceId,IsInMixedFolder,DateLastSaved,Studios,Tags,SourceType,TrailerTypes,PrimaryVersionId,Album,CriticRating,CriticRatingSummary,IsVirtualItem,SeriesName,SeasonName,SeasonId,SeriesId,SeriesSortName,PresentationUniqueKey,InheritedParentalRatingValue,InheritedTags,ExternalSeriesId,ShortOverview,Tagline,ProviderIds,Images,ProductionLocations from TypedBaseItems A where Guid = @IncludeId0 OR Guid = @IncludeId1 OR Guid = @IncludeId2 OR Guid = @IncludeId3 OR Guid = @IncludeId4 OR Guid = @IncludeId5 OR Guid = @IncludeId6 OR Guid = @IncludeId7 OR Guid = @IncludeId8 OR Guid = @IncludeId9 OR Guid = @IncludeId10 OR Guid = @IncludeId11 OR Guid = @IncludeId12 OR Guid = @IncludeId13 OR Guid = @IncludeId14 OR Guid = @IncludeId15 OR Guid = @IncludeId16 OR Guid = @IncludeId17 OR Guid = @IncludeId18 OR Guid = @IncludeId19 Group by PresentationUniqueKey

So i was wondering, if it is possible to move the whole database to the memory, to make things faster. I know, for the most users its perfectly fine this way, but i have around 30GB of unused memory and putting this db file there would speed up things heavily I assume.

 

My library.db file is ~342MB "big" and would fit in the memory with ease

[sscheib 11]

ok, quick update:

I've mounted two ramdrives for /var/lib/emby-server (where those .db files are stored) and for the cache folder (for me it is /opt/emby/cache) and that really increases the performance a lot

 

Still it would be nice to have the option to move the sqlite db itself to the ram (in-memory db)

[Luke 42077]

Well done ! But we would still like to see your server log to help diagnose that. Also make sure your installed sqlite version isn't too old. 3.15.1 is the latest.

[sscheib 11]

ii  libmono-sqlite4.0-cil                                            4.6.1.5-0xamarin1                 all          Mono Sqlite library (for CLI 4.0)

ii  libsqlite3-0:amd64                                               3.8.7.1-1+deb8u2                  amd64        SQLite 3 shared library

 

is what i have installed

[Luke 42077]

If that means sqlite 3.8.7 that is very old. Sqlite has made numerous performance improvements between that version and the latest which is 3.15.1.

 

In the future we are going to bundle our own sqlite so you'll always have the latest, but until then you might want to get it updated.