MySQL Support

[adminExitium 355]

Plex also uses SQLite and there is a similar thread for MySQL support in their forums (closed now due to attacks on the plex team like here) and they have since added a "HubCache" which caches the latest rows, any collections or playlists selected for display on the dashboard and this helps a lot with the timings.

The same library loaded in Plex takes <2 seconds to load the dashboard, if cached, and <5 seconds, if uncached, because ideally not everything will get changed at once and some library or the other will be cached anyway.

[softworkz 5066]

15 minutes ago, adminExitium said:

It's probably one of the largest DBs on this forum atm, so I expect it to be quite slow 

 

1 hour ago, adminExitium said:

of item lists like the dashboard latest items (which take 10+ seconds on my setup)

Here we get to the same crossroads like a few times before already: Are you putting this up as an argument in favor of MySQL or are you primarily interested in improvement.
In the latter case, if you can share your database privately with @Luke I think it will be possible to improve this substantially.
It's just that (afaik) we have never done optimization with/for large data bases like that.

[Dibbes 514]

24 minutes ago, adminExitium said:

It's probably one of the largest DBs on this forum atm, so I expect it to be quite slow 

The count is 2169299.

There are ways to speed up the loading. In my case I've been adding a bunch of NVMe drives and split things out. For example, do not have the pagefile/swapfile, the database and the cache on the same drive and make sure that the pagefile/swapfile and the cache NVMe drives do not use the same PCIe lanes.

This is a snapshot from September last year:

I'm not currently at home, but over the weekend I'll see what's the count is now.

Edited March 5, 2024 by Dibbes

[softworkz 5066]

2 minutes ago, Dibbes said:

There are ways to speed up the loading. In my case I've been adding a bunch of NVMe drives and split things out. For example, do not have the pagefile/swapfile, the database and the cache on the same drive and make sure that the pagefile/swapfile drive and the cache NVMe drives do not use the same PCIe lanes.

This is a snapshot from September last year:

I'm not currently at home, but over the weekend I'll what's the count is now.

Wasn't it an 8-figure? I probably misremembered.

[Dibbes 514]

Just now, softworkz said:

Wasn't it an 8-figure? I probably misremembered.

Nope... I copied this image from an earlier chat you and I had

Edited March 5, 2024 by Dibbes

[adminExitium 355]

14 minutes ago, softworkz said:

in favor of MySQL or are you primarily interested in improvement

100% in improvement of the current timings with SQLite. I am not really a fan of external databases unless you need horizontally scalable apps and Emby doesn't support that anyway. In fact, I just moved a lot of my databases at work the other way (from MySQL to SQLite) to massive improvements in performance.

14 minutes ago, softworkz said:

if you can share your database privately

I did do that for the 4.8 beta cycle and the speeds did go down to 6-8 seconds on average briefly for a few beta releases but they have increased back to 10+ second timings (still a performance improvement over the ~15 seconds of the 4.7 release). Will hand over another copy in a few days now that the 4.9 beta cycle has started properly.

[adminExitium 355]

16 minutes ago, Dibbes said:

ways to speed up the loading

This is currently on an Intel Optane drive in Linux. I have also tested it out on a RAM-backed filesystem, to rule out any I/O issues, to not much improvement so I don't think I can do much hardware-wise to improve the timings.

[softworkz 5066]

4 minutes ago, adminExitium said:

I did do that for the 4.8 beta cycle

Ah, great! There are always ways for improvement, it just depends on how far one would want to go (given that it's not the avearage case).
But i'm not really familiar with the subject in detail, it's something for @Luketo explain why and what is taking som much time.

[Dibbes 514]

4 hours ago, adminExitium said:

This is currently on an Intel Optane drive in Linux. I have also tested it out on a RAM-backed filesystem, to rule out any I/O issues, to not much improvement so I don't think I can do much hardware-wise to improve the timings.

You put the cache and database on separate drives or are these still on the same stick?

[adminExitium 355]

18 minutes ago, Dibbes said:

the cache

What do you mean by the cache here exactly? The cache folder for Emby? That's also on a RAM-backed temporary filesystem.

[sfatula 207]

10 hours ago, softworkz said:

Yes, foreign key constraints, sometimes coluimn data constraints, indexes, also partial indexes. Triggers only for full-text index updating IIRC.

Also curious more than anything, if you have the time. For a given screen or task, Is Emby primarily using lots of small queries to populate a screen, or, is Emby primarily doing a multi-table query to obtain all the results needed? The former would be bad when using external database. 

[softworkz 5066]

6 hours ago, sfatula said:

Also curious more than anything, if you have the time. For a given screen or task, Is Emby primarily using lots of small queries to populate a screen, or, is Emby primarily doing a multi-table query to obtain all the results needed? The former would be bad when using external database. 

It primarily uses complex queries to get all information at once. But the more complex queries get, the less predictable will the execution plan generation get and optimizing becomes hard. Also, certain joins cannot be done economically, or the acquisition of certain data depends on business logic. So, the answer is: both cases exist and when small queries take negligible time with SqLite, there was never a reason to care or worry about those. 

Another example is image loading: When the client displays a list ot items with images, it retrieves the data from the server first and the client requests images from the server's image service. For each image request, the server queries the database again to where the image paths are stored. Let's assume that the view displays 30 images. In this case, there's not only the db request(s) for the data but possibly also 30 requests for the images. With SqLite, this is nothing to care about, because there is zero latency, but with an external DB, that's whole different story. When you develop for an architecture with an external DB, then you would probably do something like caching of the data from the initial data request, knowing that it's likely that the data will be needed shortly after when the image requests are coming.

Countless of such cases exist everywhere which would need to be done differently, and that's what makes this an even more toxic task. 

[Dibbes 514]

On 3/5/2024 at 9:44 AM, softworkz said:

Wasn't it an 8-figure? I probably misremembered.

Here's one from today:

 

I'll be cleaning up over the next few weeks though, splitting this into 2 servers... I'm running out of space on my main server and backups are a nightmare

Edited March 9, 2024 by Dibbes

[bakes82 167]

On 3/5/2024 at 3:37 AM, adminExitium said:

Plex also uses SQLite and there is a similar thread for MySQL support in their forums (closed now due to attacks on the plex team like here) and they have since added a "HubCache" which caches the latest rows, any collections or playlists selected for display on the dashboard and this helps a lot with the timings.

The same library loaded in Plex takes <2 seconds to load the dashboard, if cached, and <5 seconds, if uncached, because ideally not everything will get changed at once and some library or the other will be cached anyway.

Yeah plex does a lot of things better and if you run the plex db optimization script on GitHub  that reorders/indexes the db it’s even better.

[bakes82 167]

On 3/6/2024 at 9:50 PM, softworkz said:

data from the server first and the client requests images from the server's image service. For each image request, the server queries the database again to where the image paths are stored.

Seems like a poor design and should be returned with the base payload. No reason to be calling the db for every image path when you just called it to get the details of all the items to display. Images should be part of that.

[TMCsw 247]

So much better...

[softworkz 5066]

1 hour ago, TMCsw said:

So much better...

[ebr 16169]

10 hours ago, bakes82 said:

Seems like a poor design and should be returned with the base payload. No reason to be calling the db for every image path when you just called it to get the details of all the items to display. Images should be part of that.

On the contrary, it is exactly the proper design.

[Neminem 1518]

12 hours ago, bakes82 said:

Yeah plex does a lot of things better and if you run the plex db optimization script on GitHub  that reorders/indexes the db it’s even better.

 

12 hours ago, bakes82 said:

Seems like a poor design and should be returned with the base payload. No reason to be calling the db for every image path when you just called it to get the details of all the items to display. Images should be part of that.

Just go back to that guy, you sorely miss him.

Ohh boy, now i'm feeding the amimal in the cage.

[Dibbes 514]

On 3/10/2024 at 3:49 PM, jaycedk said:

 

Just go back to that guy, you sorely miss him.

Ohh boy, now i'm feeding the amimal in the cage.

I see an ice bucket challenge coming up for this specific gremlin... what's the worst that could happen

[adminExitium 355]

I may be understanding the logs wrong, but, it seems like Emby is **not** using a connection pool for the database, which by itself should add a ton of responsiveness to all queries by allowing faster queries to bypass the slower ones rather than waiting on them to finish.

I have been able to reproduce this by opening the dashboard in multiple browser tabs simultaneously and seeing the response times increase accordingly as a factor of the number of open tabs.

Jellyfin did the move to a connection pool recently in their latest release, so it's possible that Emby may still be running without one, unless it was added sometime after the fork.

[axinnice 2]

Spoiler

 

After nearly 9 years, emby still does not support databases such as MySQL

[softworkz 5066]

On 6/16/2025 at 8:55 AM, axinnice said:

After nearly 9 years, emby still does not support databases such as MySQL

Correct. And......?

It has never been promised or called out to be a goal.

[nunuxx 7]

@softworkzMaybe kind be good to be a goal

[brothom 177]

Maybe flipping around the argument works better; what if Emby could be configured to use different kinds of adapters with different connection strings.

It would still use the sqlite installation initially, but could (for advanced users) be configured to use with database adapters.

The impact of this is probably enourmous because all queries need to be put through a DAL of some sorts so that the each appriopriate adapter receives a standardised query. Personally I like the adapter-based DAL approach because certain models can be offloaded to other storage mechanisms so that the entire structure can be optimized more. For example storing media items in sqlite but storing search indices in a different engine like redis or mysql.

A (PHP) example of this within doctrine would be the expression builder: https://www.doctrine-project.org/projects/doctrine-collections/en/2.3/expression-builder.html which showcases the general direction pretty well. But I say again, any changes to the query methods probably have a massive impact on this project as a whole.