Jump to content

MySQL Support


dcrdev
Abobader
Message added by Abobader,

Last warning: Attacking other members & team will cause a ban for your account.

Recommended Posts

16 minutes ago, sfatula said:

I understand but from the Emby responses given, for me personally, feels like it's a hard no. Honestly. Not saying that because I want this. There really is mis-direction and other similar posts throughout this thread. That was my point. It genuinely feels like this was decided long ago. If that was not intended, it has not come across at all. Speaking for just me. 
 

When someone wants to use as a point, things such as two processes on the same machine.... so, my Emby clients doing all the lookups and requests, retrieving the guide, filtering lists, etc, are on same machine as the sqlite database? Don't think so! And to then imply there is an unposted reason that means mysql is absolutely needed but unless someone says it, not going to do it makes zero sense. It all gives a feeling to me of we just don't want to. Which is ok, just say it. 

 

If you look at other feature requests that took a long time, many of them had similar comments where they felt like it was never going to get done and then all of a sudden one day, boom. I for one have never said no to this, so anything is on the table given enough user demand for it.

Link to comment
Share on other sites

Dibbes

Scalability is one reason I'd like it. Various servers accessing at the same time. Yes, I know that this is a separate request and topic as moving to MySQL or any other flavour does not automatically get that implemented.  Anyway all of this is already mentioned in this thread various times. 

Also it would be very nice not to have to rescan every single item when you're doing a clean install, which, in my case is two weeks wasted. Yes, I'm using the backup plugin, still, for reasons not clear to me, it's unavoidable to do the two weeks scan. Not sure if this can be avoided the way Emby is currently written... While I know I have a larger collection than most, having to go through this is a pain. It's the reason I still haven't setup SSL. There are some issues with the OS that make it refuse to install and run IIS, but I just don't want the two weeks downtime.

Link to comment
Share on other sites

6 minutes ago, softworkz said:

For all those reasons, you can not have "your engineer" get it done in a few hours (or days).

Don't incorrectly quote me please. I said "engineers". It's too bad for us that you optimized yourself into a corner. We get it. 

Link to comment
Share on other sites

Happy2Play

This discussion comes and goes over the years and yes, it is an idea but realistically will be for a limited set of users.

Link to comment
Share on other sites

8 minutes ago, rsvg said:

Don't incorrectly quote me please. I said "engineers".

Before working on it, one needs to fully understand all of it. Whether one or five engineers - the time would be the same. And for the actual work, there might be a potential for accelerating by multiple workers, but not that much actually. A little bit with two instead of one, but a 3rd would not make a change and from then on, it just makes it worse 🙂 

Link to comment
Share on other sites

3 minutes ago, softworkz said:

Before working on it, one needs to fully understand all of it. Whether one or five engineers - the time would be the same. And for the actual work, there might be a potential for accelerating by multiple workers, but not that much actually. A little bit with two instead of one, but a 3rd would not make a change and from then on, it just makes it worse 🙂 

I'm literally a Technical Director with multiple teams of engineers. I completely understand that staffing up is not linear. 

  • Agree 1
Link to comment
Share on other sites

9 minutes ago, rsvg said:

I'm literally a Technical Director with multiple teams of engineers. I completely understand that staffing up is not linear. 

I believe that. You said the most reasonable things I've read here for quite a while.

  • Haha 1
Link to comment
Share on other sites

36 minutes ago, softworkz said:

It's an unfortunate truth, but it is a truth that turns this into a very fundamental effort. A significant part of the application logic is at the database level - where it normally shouldn't be. It's an anormal db design with a lot of history and totally unsuitable for outside and generic access. It's not modular, it's not layered, it's an intrinsic part of the application and not following usual conventions.

It's a huge effort. As simple as that.

Thanks for this statement!
it is - as far as I got (but I didn't follow the topic for several years) - the first time that someone really explained the reason why external database support has not yet been implemented as of now.

So far - my point of view - I'm still looking forward to get this done at some point.
Why?
Because I always prefer application and data seperated from each other.

This is the reason, why my applications are running on different lxc's, why applications are using seperated databases (if possible) - and why user-data will be stored on seperated drives.

I can somehow understand, that the majority of emby users are "non professionals" - or at least, just regular users that are running "some kind" of HomeServer maybe on their NAS storage...
But on the other hand... emby is a server application, and it is pretty common for server software to have their data storage and user interfaces seperated.

In terms of Performance:
If you are running emby on your own Database host - performance optimization is pretty much something you have to maintain... just as every other DBA has to do [except the application is using some really bad queries]

  • Like 1
  • Agree 1
Link to comment
Share on other sites

17 minutes ago, CChris said:

Thanks for this statement!
it is - as far as I got (but I didn't follow the topic for several years) - the first time that someone really explained the reason why external database support has not yet been implemented as of now.

The other reason is user demand for it compared to other requests. I think we all understand there is an audience for this feature, but there are other things that have even larger audiences that have to be prioritized ahead of this. That doesn't mean it won't happen, but it is a reason why years have gone by and this hasn't been done. But the same can be said for other things that eventually got their turn.

Link to comment
Share on other sites

tomlawesome
1 hour ago, softworkz said:

 

You hit the nail! It's an unfortunate truth, but it is a truth that turns this into a very fundamental effort. A significant part of the application logic is at the database level - where it normally shouldn't be. It's an anormal db design with a lot of history and totally unsuitable for outside and generic access. It's not modular, it's not layered, it's an intrinsic part of the application and not following usual conventions.

For all those reasons, you can not have "your engineer" get it done in a few hours (or days).

Despite all the things I mentioned, there's one important aspect that needs to be understood as well: it's done like it's done, but it's extremely optimized for this very purpose of Emby in every little detail. It will be not only tough, but probably even impossible to achieve the same level of performance with an external database (within the constraints of typical Emby use).

It's a huge effort. As simple as that.

This is great to see posted — it’s an honest and clear rationale as to why Emby don’t want to implement external DB without investment and I can accept that. 

  • Like 2
Link to comment
Share on other sites

2 minutes ago, tomlawesome said:

This is great to see posted — it’s an honest and clear rationale as to why Emby don’t want to implement external DB without investment and I can accept that. 

Just to clarify, he did not say that we don't want to, rather he just hinted at some of the work that would be required.

Link to comment
Share on other sites

sfatula
1 hour ago, softworkz said:

Despite all the things I mentioned, there's one important aspect that needs to be understood as well: it's done like it's done, but it's extremely optimized for this very purpose of Emby in every little detail. It will be not only tough, but probably even impossible to achieve the same level of performance with an external database (within the constraints of typical Emby use).

It's a huge effort. As simple as that.

This is the best argument against doing it (the way it's designed/coded), and is simply a fact I am sure. If what you post here is true, and I am pretty sure it is, then in fact barring a redesign this request will never get done. Thus my suggestion to just close the request. Just because it's closed, to Lukes point, doesn't mean it still couldn't get done one day. Seems like a lot of wasted time in this thread. Most of the ideas are already posted. 
 

I am just going to add, without having written Emby, that while you may not be able to achieve the same performance as currently designed, there is no question you can meet and exceed the performance. Unfortunately, that would likely require a redesign and code. Worked on many systems for huge companies with many  orders of magnitude greater requirements. Other similar products (mythtv, kodi, etc) perform very well using mysql and do scale. It's certainly possible but if the code doesn't support this as is, it won't get done as that would mean many many other requests never would get done. Given the huge effort, I agree it's not the best use of resources. But do I want it for my own purposes? Yes. There's so much extra stuff I could do. 

  • Like 1
Link to comment
Share on other sites

Just now, Luke said:

Just to clarify, he did not say that we don't want to, rather he just hinted at some of the work that would be required.

Exactly right, in fact really do want to do it, I have a strong background in enterprise database development and client-server business applications before I came to Emby. But I also know the status quo and how long and tough the way will be to get there. It will be an extremely disruptive task, tearing everything apart and putting it back together. And it's not only the DB access code - it goes through the whole application. With an in-memory database, you can do things that you cannot (or should not) do with an external DBS - for example single-item queries. Those are used all around the code base. It's something you can easily do with an in-memory database, because you are already sitting right inside the database (same process, same memory). But when you do that with an external DBS, you have the roundtrip times summing up quickly, so you need to do it in a different way and such changes are often going viral through call paths. And there are many more things that need to be done differently. Probably you want to introduce some ORM middleware to achieve independence from the DBS, but this would change the game even further. It brings benefits but also a bunch of new problems. And disallows doing certain kinds of optimizations that were done before.
Though - in the end it still needs to be able to run with SQLite as default DB, and when it would then run only half as fast on a Raspberry than before (or even worse), the whole endeavor would be a fail. The idea that Emby Server should run on any better table calculator is bringing quite a twist to the requirements and narrows the range of possible options. 😉 

Link to comment
Share on other sites

I thought it was a bit early for the traditional Airing of Grievances.

  • Haha 4
Link to comment
Share on other sites

sfatula

Redis?

Regarding the Pi, my Pi runs pretty good with >100,000 videos/photos (Digikam) and mariadb backed. 

Edited by sfatula
Link to comment
Share on other sites

enjoyed reading this, thanks for the forthcoming honestly, i love emby, the application is great, i just wish i could use it for photos too, but with a million items in emby it becomes unusable, search takes minutes and opening folders takes minutes, running it with just 5-10k items (which is a lot to most users) everything comes instantly so i fully understand their design choices here.

Link to comment
Share on other sites

1 hour ago, cd01 said:

enjoyed reading this, thanks for the forthcoming honestly, i love emby, the application is great, i just wish i could use it for photos too, but with a million items in emby it becomes unusable, search takes minutes and opening folders takes minutes, running it with just 5-10k items (which is a lot to most users) everything comes instantly so i fully understand their design choices here.

And we have a winner!!!!

🎶🎆🧨🎇

This is the #1 most valid case I was talking about above!

Everybody who is really knowledgeable in databases and experienced over the whole range of DBS implementations would have pointed this out instead of all the other reasons that have been stated, some of them reasonable but not forcing, some of them understandable but just nice-to-have and unfortunately many of them invalid or untrue.

It's the number of rows per table where file-based in-process databases like SQLite or the MS Jet Engine are getting to their limits.
Rule of thumb:

  • 100k: No Problem
  • 1M: No Joy
  • 10M: No Fly

After so many unjustified reasons that were given (like "I have 100 users"), this is finally a case where I can say: Yes, this is really something that could be improved by using an external DBS.

PS: This doesn't change the situation right now. But we continue to evaluate demand and necessity vs. required effort for feature requests, as @Luke has pointed out above. And as I have pointed out above, that evaluation is difficult due to the high effort required to change this.

  • Like 1
Link to comment
Share on other sites

2 hours ago, cd01 said:

enjoyed reading this, thanks for the forthcoming honestly, i love emby, the application is great, i just wish i could use it for photos too, but with a million items in emby it becomes unusable, search takes minutes and opening folders takes minutes, running it with just 5-10k items (which is a lot to most users) everything comes instantly so i fully understand their design choices here.

@cd01 - We have recently introduced settings to control the RAM usage for SQLite. If you haven't done so, please try to set this to something like 1.5x the size of your library.db. This may still greatly improve the experience with a high number of rows, even when it won't be the same like for 100k items.

Link to comment
Share on other sites

sfatula

That's why I pointed out my Digikam example, lol. My >100k photos there with all the metadata, facial recognition, etc. just doesn't perform in SQLite on the Pi. Thus the move to mariadb where now the Pi handles it with ease. The general recommendation for Digikam app is 20-30k and then it slooooows. Then you combine that with Photoprism to give a web gallery to said photos and it's mariadb also. 

The same happens to Home Assistant, when you keep a lot of history. Millions of rows is easy to get to but provides a lot of analysis opportunities. One can purge them and only keep a little but many people good at mariadb are able to get some good data out of the history. 

That's just 3 of my databases, have many more. That's why mariadb would be great for me, already have a DB server and it flies, be great to take advantage of. I can do analysis on millions of rows in almost no time.

But, for Emby, that's not a great benefit to me as I don't have huge number of rows. I can get much more out of it using triggers, etc. As I mentioned long ago in this thread, that of course I am sure is a downside for Emby trying to provide support. I would not ask it, but I am sure many could mess things up big time. 

Edited by sfatula
Link to comment
Share on other sites

17 minutes ago, sfatula said:

That's why I pointed out my Digikam example, lol. My >100k photos there with all the metadata, facial recognition, etc. just doesn't perform in SQLite on the Pi.

100k shouldn't really be a problem, though. You need to make sure to do a fair comparison. For this, you'll need to set the allowed RAM usage for SQLite in a way that it matches the RAM used by the other DBS. The SQLite defaults are very low.

Link to comment
Share on other sites

sfatula
9 hours ago, softworkz said:

100k shouldn't really be a problem, though. You need to make sure to do a fair comparison. For this, you'll need to set the allowed RAM usage for SQLite in a way that it matches the RAM used by the other DBS. The SQLite defaults are very low.

100k Emby != 100k Digikam, depends on indices, row size, etc.  Digikam recommends above 20k. I got a little more out of it. It seems to be common to offer embedded sqlite but also mariadb.

Link to comment
Share on other sites

13 minutes ago, sfatula said:

100k Emby != 100k Digikam, depends on indices, row size, etc.  Digikam recommends above 20k. I got a little more out of it. It seems to be common to offer embedded sqlite but also mariadb.

The numbers I gave were not specific to Emby but rather - as I said - a common rule of thumb, but I should have mentioned that this is about cases when queries are around a single large table and relations targeting smaller entities.  With multiple large tables and/or complex relations, you can hit limits way earlier, so that's surely plausible.
Yet, it's always worth trying to throw more RAM at it (just in case you haven't tried).

Link to comment
Share on other sites

sfatula

Yep, I know but for me that's a massive step backwards. There is so much more I can do when it's in a database like mariadb. I would never want to spend any time trying to keep data in sqlite if it can be moved to db server. Anyway, getting off topic somewhat. Maybe some year for Emby.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...