Multi-database support

[drakus72]

Possibility to add mysql/mariadb to replace or as an option to use instead of sqlite?

[brianjmurrell]

What do you see as the benefit?

[NumberedThought]

The ability to use it as a shared data repository for multiple servers. This would allow us to load balance.

[joshuaboniface]

100% on board with this.

[brianjmurrell]

@NumberedThought Fair enough.

But it would take more than just a network-database to properly support load balancing. You wouldn't want somebody deleting something on jellyfin server 1 while somebody else was viewing on jellyfin server 2 for example, so some locking would need to be put into place.

But that said, mysql/mariadb should be an option only. Sqlite should be perfectly fine (and preferable) for simpler installations that don't need the overhead of managing mysql/mariadb.

[drakus72]

I run a mariadb cluster at home and it would allow me & everyone else to keep all settings/libraries, etc in case of a failure and have to reinstall. The DB would never have to be rebuilt with all the shows/movies, etc. It really is a no brainer todo this. Emby 3.5 was slow as hell with sqlite and even with the current updates, it is still slow. Add mysql/maria and watch the speed increase.

Not sure on others., but my sqlite db is over 700MB (has been trimmed, etc), sqlite can handle this, but it gets slow, so why not use a real database.

Even sqlite recommends a RDBMS when doing client/server. https://www.sqlite.org/whentouse.html

Also as others have said, load balancing, shared resources/data by doing it this way.

[drakus72]

some locking would need to be put into place.

Exactly what is needed.

I understand some/maybe a lot won't want to do it, but I am sure that there are several others that will want it. This topic has been posted many & requested many times on the Emby forums.

[joshuaboniface]

@drakus72 Yup, the sqlite DB is slow as hell with my massive media collection. After the LDAP fiasco I never bothered asking for this in the upstream, but we can definitely start to work on it.

I think we should support all the big RDBMS's: MariaDB, MySQL, PostgreSQL, and SQLite, using a featureful connector.

[drakus72]

@joshuaboniface

I think we should support all the big RDBMS's: MariaDB, MySQL, PostgreSQL, and SQLite, using a featureful connector.

There is other software that will install the database right along with it. One such software that I use is SAM Broadcaster, it installs firebird with it as default as long as you don't select it, it will setup one of the other options like mysql/postgres/MSSQL during setup.

[joshuaboniface]

Yes I'd much rather use a 3rd party connector library, saves us the work of having to support every little bug in the various RDBMSes.

[brianjmurrell]

My only concern is requiring people who want to use a simple (i.e. single instance for a household perhaps) instance is the added complexity of having to install and configure the RDBMS when the simplicity of sqlite is sufficient.

[joshuaboniface]

@brianjmurrell agreed, we should keep sqlite as the default, and simply allow the option to use a more complex RDBMS for those who want to.

[BnMcG]

EntityFramework Core is well-supported and pretty database agnostic, it already has providers for SQLite, Postgres, MariaDB, etc.

[EraYaN]

The SQLite backend for EFCore is maintained by Microsoft it seems: https://docs.microsoft.com/en-us/ef/core/providers/sqlite/ So that is nice.

[majorcyto]

Oh good someone already submitted this, I would LOVE to be able to use MySQL/MariaDB as the back end as well. Most people already covered reasons above.

[Ornias1993]

This would be a huge benefid.
Just keep it standard (SQL) and nothing fancy like ScyllaDB, Cassandra etcetc...

Most of the people wanting this feature already have those running and just need something a little more flexible/performant... Going straight Cassandra Scylla would be over doing it imho.

[cameronkollwitz]

Completely agree with this request. It would be great to be able to scale with microservices.

[MrBones757]

This sounds really nice and something that i would use heavily. My use-case for this functionality is to load balance / distribute trans-code load across many hosts.

by distributed transcode, i mean if i had more than one simultaneous stream, each stream could be transcoded on different instances improving performance.

In this case a "non ha" db would be fine (i.e single instance postgres) that both instances could consume, while providing the aforementioned distributed transcode, perhaps across two smaller (less powerful) nodes like say Rpi or in a container environment (kubernetes / swarm)

[brianjmurrell]

@MrBones757 This doesn't sound like a problem that multiple databases is needed to solve. A single database could easily be used and allow distributed transcoding. I would suggest your request is a new ticket for distributed transcoding.

[dkanada]

@MrBones757 you can also look into rffmpeg for now which will kind of meet your criteria.

[cvium]

https://features.jellyfin.org/posts/315/mysql-server-back-end

[Ornias1993]

Just an opinion:
I personally hate how all these projects move issues to different platforms.

Newsflash: I don't have time nor interest to keep track of all the projects I support individually. That was the nice "magic" of github: Having everything in one place.

[cvium]

That is fair, but having feature requests as part of Issues was a burden to us.
We're working on migrating legacy code and fixing bugs, so feature requests are down-prioritized right now. Maybe GH Discussions will replace Fider at some point, but Fider works for us right now.

[MarcosBL]

Another posible improvement, seeing no more:

[2022-05-04 15:51:53.220 +00:00] [DBG] [20] Emby.Server.Implementations.Data.SqliteItemRepository: "GetItemList" query time (slow): 470.8834ms.

on a listing. Better indexes, full text search capabilities, etc...

[g0dless]

What do you see as the benefit?

The first and the main benefit is the SPEED.
Try to add 1k+ videos, 40k+ songs, 100Gb photos with videos, 1k+ books and UI will be stupid like an intel 8086 in 2022.

I'm using jellyfin about a week (minidlna before) and after some basic tuning first thing that i had search is to change database server. And... nothing.

I understand everything, but please, dont forget about this future request.

PS: my server has 40 cores 2.5GGhz and sata 6Gb/s drives and 64Gb DDR3 memory. Also system has SSD drive for system files (by default your SQLite DB placed there).

[brianjmurrell]

@g0dless Isn't this that the problem that sharding solves?

Sharding should be done in the database client/server backend, not by every app that wants to benefit from load sharing across databases. IOW, every app should not have to re-invent that wheel. It should have a consistent (i.e. SQL) API to the database and the database handles the details of balancing load horizontally.

[davispuh]

If Jellyfin supported PostgreSQL then you could replicate DB (across many servers) and shard it aswell (all handled by PostgreSQL itself, that's the point of RDBMS), the issue is currently Jellyfin doesn't have any support.
And I also have noticed that Jellyfin is unbearably slow but I'm not sure if it's because of current SQLite DB or just in general something else.
Sharding is useful if your single server can't handle it, but we're not there as it doesn't even utilize current server.

[dmgolembiowski]

Would it be feasible to split away all calls that would normally go to Sqlite to a dummy library whose only purpose is to guarantee the contracts of a database, then copy/paste the existing sqlite-calling code into the dummy library?

It seems like it could be a waste of time on the surface, but unless some kind of code-splitting like this happens, it'll be impossible for the community to work in parallel and build database backends for Jellyfin.

Moreover, I think this kind of approach makes it possible to enable dynamic linking across FFI boundaries (in the long run) as an implementation option — without ever having to fully stop using sqlite as the out-of-the-box DB provider.

[maurocolella]

SQLLite inherently lacks robustness. See list of potential causes of corruption:
https://www.sqlite.org/howtocorrupt.html

With Jellyfin on Docker, this amounts so far to three instances of database corruption for me on a BTRFS bind mount and in less than ten days.

I believe there are a number of issues open right now with similar requests, to help with a wide range of scenarios:

• Load balancing, where locking, dispatch and state management are all back-end concerns.
• Overall robustness or performance improvements.
• Statistics, reporting and monitoring.
• Etc.

https://features.jellyfin.org/posts/315/mysql-server-back-end

Someone suggested that the plan at some point was to integrate with a back-end-agnostic framework such as EF. I think that a lot of people will find this critical.

[Wildcarde]

Would love to see postgresql as an available backend, it handles faults gracefully, is just comically fast comparably (especially with the right filesystem under it), and backups are so easy it feels like cheating.

[Ornias1993]

Would love to see postgresql as an available backend, it handles faults gracefully, is just comically fast comparably (especially with the right filesystem under it), and backups are so easy it feels like cheating.

On top of that, the horizontal scaling of postgresql is just decades ahead of stuff like MariaDB.

[maurocolella]

That having been said, I verified the issue I was experiencing, and it doesn't seem due to sqllite corruption. Rather, it appears to be a bug where jellyfin databases when it runs in a container (with ext4 as the underlying filesystem) don't resist a reboot. They may still show movies for a time, but those can't be streamed. It's as if filesystem references had become inaccessible.

…

On Sun, 20 Nov 2022 at 18:23, Kjeld Schouten-Lebbing < ***@***.***> wrote: Would love to see postgresql as an available backend, it handles faults gracefully, is just comically fast comparably (especially with the right filesystem under it), and backups are so easy it feels like cheating. On top of that, the horizontal scaling of postgresql is just decades ahead of stuff like MariaDB. — Reply to this email directly, view it on GitHub <#42 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAFZ7NGN3N62SM3H37SSMKLWJH3Z3ANCNFSM4GJWFUKQ> . You are receiving this because you commented.Message ID: ***@***.***>

[graealex]

I fully agree. While I don't necessarily see the need for replication and clusters for a movie database, SQLite simply does not scale when talking about tens of thousands of entries. It performs well in some places, for example it is very fast when inserting, as it foregoes lots of overhead. However, the query planner is lacking when a database grows beyond a few hundred MBs.

The general direction should be EF. The only real reasons for not using it would be if loads of views and stored procedures are used - the latter which SQLite doesn't even offer. In all other cases, EF creating reasonably good SQL directly from LINQ is more than enough reason for using it. It'd also mean that plugging in a different database backend would be trivial.

[aleksasiriski]

Wouldn't it be easier to switch to something like this to achieve replication? https://github.com/rqlite/rqlite

[Ornias1993]

Wouldn't it be easier to switch to something like this to achieve replication? https://github.com/rqlite/rqlite

The point isn't "just" replication. Actually replication is a minor note in the whole thing.
If there are gigantic projects with lots of trouble with good replications, you can be pretty sure you shouldn't use minor tools like rqlite ;-)

[aleksasiriski]

Is there any place where we can see the progress of external DB support, other than git commits?

[onedr0p]

I am sure the devs would post an update on this issue here, so you are already subscribed to the best place to get updates!

And also here https://features.jellyfin.org/posts/315/mysql-server-back-end

[nielsvanvelzen]

This issue was closed over a year ago so it's unlikely that status updates will be provided here. Work is ongoing to migrate the database over to Entity Framework and when that's finished support for other database engines will be possible.

[maurocolella]

@nielsvanvelzen this is great news. Seemingly not related, but the frequent corruption I have noted with docker (anytime my physical server is restarted) is potentially related to a file system issue.

Something to do with file systems not being mounted when Jellyfin starts; I am investigating this.

Though not directly related to the database system in use, it appears to bear some kind of relation to database sync/maintenance logic: as if Jellyfin decided that the references are obsolete and discarded them during some routine task.

I don't like to cross post, but there is a link. It could simply be worth documenting (if Docker indeed accepts to start with an invalid mount).