Database Performance

[Guest]

Hi Everyone,

I’m looking for some advice regarding database performance for my Emby server. I’m currently running version 4.8.10.0 natively on Ubuntu 22.04.4, but I’ve been experiencing some sluggishness that I’d love some help with.

When searching for content, it takes anywhere from 5 to 10 seconds to get results through the web client, and it can take even longer on the Apple TV client. Loading new TV series also seems to have some noticeable delays.

For reference, my setup is on an Intel NUC i7 with 32GB RAM and a 500GB NVMe drive. I used to run Emby in an LXC container on a Proxmox cluster, and then tried Docker on Ubuntu, but I’ve seen the best performance so far running natively on Ubuntu. That said, the response times are still a bit slower than I’d like.

Here’s a bit more detail about my database:

• The entire /data folder is around 1.2GB.
• My DB configuration is as follows:

  Cache: 15258

  Row limit: 40000

  Optimise on shutdown: enabled

  Vacuum on start-up: enabled

For context, I have a fairly large media library:

• Movies: 5000+
• TV Shows: 450+
• Albums: 200,000+
• Pictures: 50,000+
• Plus, a mix of audiobooks, concerts, radio stations, live TV, etc.

The server gets used by around five family members almost throughout the day.

I’m wondering if anyone has any advice on how I could improve the database performance, whether through adjusting my current configuration or possibly moving the database to a dedicated instance like PostgreSQL?

I’d greatly appreciate any insights or recommendations!

Thanks so much in advance!

[visproduction 315]

I wonder if having large libraries contribute to the delay.  Do you have multiple libraries, each having say 1/3 or 1/2 of your collection for Movies, TV shows, etc.?  Would dividing up content by decade or some other choice, then only show less content per library selection... if that would speed up your load time?

You would have more library collection rows on your home page.  Genre and tags would only search within each library, but if you clicked on the top genre links or bottom tag links on a media page, the results would include all libraries.  Also search by text results spans all libraries, so the functionality of finding things is still pretty good.

This is just a guess.

Hope that helps.

[Luke 42077]

Hi there, please attach the emby server log from when the problem occurred:

How to Report a Problem

Thanks.

[Carlo 4560]

@dpslabber

Hi,

You can't move the database to PostgreSQLor any other type of database as Emby doesn't support this (wish it did).
Emby uses an integrated database called sqlite which requires it to run on the same server as the application using it.

The Vacuum on start-up is a one-time option, meaning that if enabled it would vacuum the database on startup resetting the value back to not being enabled.  So anytime you set this will only be used once.  You need to enable and then restart the server every time you want to vacuum the database.

A drawback to sqlite is that it can't be scaled in any manner and only has a couple of config setting that can be used to help its performance. The Database cache size (MB) setting is one of them.  By default, Emby server uses a setting of 128. I would suggest, changing this value to 4096 which is 4GB of memory available for caching.  The upper limit used to be 4096 and anything entered higher than this would be ignored resulting in use of the default instead.

So based on that I would try setting the database cache value to 4096, then do a vacuum to change the cache value and optimize the database.

Something to keep in mind is that the cache value is the upper limit of memory that Sqlite can use but doesn't mean it will get used.  Since it's in memory, the cache is started fresh on every Emby Server restart. The cache is not used for temporary database operations that often get used in Emby running queries. It's also not as useful as it would seem for tables using blob entries or variable string lengths which Emby uses.

You can get a rough idea of the memory being used for this cache by checking the memory usage on Emby started, then checking again later to see how much the memory footprint has increased.

Sqlite is great for typical systems, but I've always found that performance does slow down incrementally the more media files you have loaded in Emby. I myself do not use Emby for music as I stopped doing this 3+ years ago.  I found that loading lots of music really hurt my performance a lot.  I may be wrong, but I believe each song added to Emby uses more total table rows per entry than a movie or show would require. That means the database performance will suffer more from music than for a movie. IE performance is worse for me when testing 500K songs vs 500K movies. It's been 3+ years since I tested this so thing could have improved or gotten worse since then. I originally setup a second Emby server just for music to offload this from my main server but found that hardly any of my users even used my music library but instead used things like Spotify, Amazon music, etc so I eventually removed the Emby server I was running just for music.

Radio stations and Live TV EPG data can add a lot of rows to Emby's database as well that can hurt performance long term as well if not managed with a vacuum as this data isn't permanent and is constantly being removed and added to the database on each refresh which tends to fragment the database as well as affecting the management and use of the database cache being used in memory.

The hardware running Emby cab make a huge difference. I'm not advocating the need for high-end hardware but just giving you an idea of extremes.  My media is roughly 95% (at least) movies and TV show/episodes.  I have Emby running on a Synology 920+ NAS with about 500K media files and on a Dell R740xd server running 2 XEON Gold CPUs, 256GB memory and over a million media files spread out on a 100+ disk ZFS pool using 4 disk encloses each with two SAS connections. The Synology is using an NVMe HDD for Emby (database).  I also have another NVMe installed for metadata caching, Live TV, DVR, transcoding, etc.  On the 740 Emby runs off a RAID 0 drive using 4 fast NVMe drives.  The OS system has its own NVMe drives.

An Emby search done on the 740 will normally take about 2 to 3 seconds while the same on my Synology with less than half the media is 8 or 9 seconds. With only 500K media items the 740 would probably be 1 to 1.5 seconds. Thats about 6 times faster, but not as linear as the overall speed difference of the two setups.

[Happy2Play 9780]

3 hours ago, Carlo said:

The Vacuum on start-up is a one-time option, meaning that if enabled it would vacuum the database on startup resetting the value back to not being enabled.  So anytime you set this will only be used once.  You need to enable and then restart the server every time you want to vacuum the database.

It is triggered weekly per scheduled task Now unless you have messed with the default trigger and how often you actually restart.