Jump to content
dcrdev

MySQL Support

Recommended Posts

dcrdev

I would love the option to store Emby's database in MySQL - I have a setup that serves around 30 people and I feel that there would be real performance gains by using a proper relational backend over SQLite (I'm presuming).

 

On top of this it would make life easier for me when reporting on watched status a cross users.

  • Like 30

Share this post


Link to post
Share on other sites
Angelblue05

Probably not. The emby server is the central point. Not Kodi.

 

Can you give an example of the watched status situation? I don't quite understand what you mean.

 

 

Sent from my iPhone using Tapatalk

Share this post


Link to post
Share on other sites
dcrdev

I think you misunderstand me - I mean having MySQL as a backend for Emby server itself, I'm not talking about kodi.

 

As for watched status I'd like to produce some analytical reports against the status for each user. Having a proper relational backend would enable me to query the database as required.

 

But the main benefit would be performance, you could make use of full text indices and in memory blobs.

Share this post


Link to post
Share on other sites
Angelblue05

Ok, wouldn't that mean you would run 30+ MySQL databases? Each emby users have their own. Right? (Sorry, I haven't setup MySQL since eden so I don't fully recall how it works). Doesn't it also requires to run the same Kodi version across all your devices? How does it work in a remote situation? Let's say a user connects from outside the network?

 

Why not use the server api to query the information about your users (the add-on uses it to populate the kodi database)? You could even push it further and build a server plugin to do everything automatically for you.

 

 

Sent from my iPhone using Tapatalk

Share this post


Link to post
Share on other sites
dcrdev

Haha I'm not talking about Kodi - this has nothing to do with Kodi!

 

Emby would use MySQL itself as a database server and then you could query it without writing an API wrapper to do so. Nearly every analytical tool out there supports SQL.

 

Essentially you could query the database with a simple statement like:

 

select media.*,

watched.*

 

from media

Left join watched on watched.mediaid = media.mediaid

Left join users on watched.userid = users.userid

 

Where users.userid = 1 and media.title like '%show%'

 

But mainly it's about the performance gains you'd get over a flat file database.

 

All of your clients would interact with Emby in exactly the same way - they'd have no knowledge of the MySQL server, it would be invisible behind Emby.

Edited by dcrdev
  • Like 3

Share this post


Link to post
Share on other sites
Angelblue05

Ah ok, I understand what you are talking about now. :)

 

 

Sent from my iPhone using Tapatalk

Share this post


Link to post
Share on other sites
spootdev

As for watched status I'd like to produce some analytical reports against the status for each user. Having a proper relational backend would enable me to query the database as required.

You can make queries with joins/etc now in sqlite3.  You can do the "attach" command to join up the dozen or so databases emby uses.

 

PS: Project in progress for stats/monitoring program in Flask/Python.  I should be starting up the reports in the next few days.

  • Like 1

Share this post


Link to post
Share on other sites
dcrdev

Yeah, but that requires the query tool you're using have a driver for SQLite - not many do. The improvements you'd gain with a proper database engine would be vast particularly by creating full text indices.

 

Cool project though - I'll be interested to see how that pans out.

Share this post


Link to post
Share on other sites
Dibbes

As I said in another thread, I'd be willing to pay for it as extra feature :)

  • Like 3

Share this post


Link to post
Share on other sites
Cerothen

I also support the idea of having a backend like MySQL. However I think it would have to be optional as not every user would want to configure my SQL in addition to emby.

  • Like 2

Share this post


Link to post
Share on other sites
Doozer

I would also love to see a SQL backend.

  • Like 1

Share this post


Link to post
Share on other sites
parasven

I would also like to see this feature in the future.

 

It would also make Loadbalanced setups much more feasible than with the current sqlite db.

 

 

 

 

  • Like 1

Share this post


Link to post
Share on other sites
b0dyr0ck2006

This sounds like something I have been badgering for, for a while now. Ultimately all I want to do is see the cumulative watched status of my media.

 

For example:

 

There are 10 users and 300 files. I wish to be able to produce a report that tells me which users have watched which files, so that once all 10 users have watched file 1 I know that I can delete it from the server.

 

Currently the only option is to use the api and work through each user separately

Share this post


Link to post
Share on other sites
pir8radio

add my vote to this. MySql AS AN OPTION if that's even possible..  Sqlite works well for the majority of emby users. But for the larger databases, with many users I agree MySql might be a better fit.

  • Like 1

Share this post


Link to post
Share on other sites
pir8radio

Any feedback from the DEV's?  Is this even possible? On the table?

Share this post


Link to post
Share on other sites
ebr

It is theoretically possible - that is we designed the interfaces to allow switching the database engine.  However, it is far from simple and introduces another complex dependency.

 

There are a lot of assumptions that MySQL will magically be faster than our current solution and I think those are just that - assumptions.  I'm not sure if they would prove to be correct or not given that we are not in a multi-user environment (from the database perspective).

  • Like 1

Share this post


Link to post
Share on other sites
pir8radio

It is theoretically possible - that is we designed the interfaces to allow switching the database engine.  However, it is far from simple and introduces another complex dependency.

 

There are a lot of assumptions that MySQL will magically be faster than our current solution and I think those are just that - assumptions.  I'm not sure if they would prove to be correct or not given that we are not in a multi-user environment (from the database perspective).

 

Humm... This is true...  if emby is considered a single user to the database...        I would love to be a test subject......     

 

Ok, well i'll keep hanging on then...   Hope the guys get a bit more efficient on how emby uses the current database...    

 

Thanks for the response.

Share this post


Link to post
Share on other sites
Luke

It's definitely possible, and is sure to appeal to a certain set of users. You never know. But as Ebr said, obviously the default install is priority number one.

Share this post


Link to post
Share on other sites
Koleckai Silvestri

One thing about switching to MySQL is that you can put the database on a different machine or VM should you have the hardware. This would allow you to allocate resources better if you're running under load.

 

Anyway, I look forward to the potential. Already running a MySQL server for other purposes so it would be a no-brainer if it could be used for the database. Not really worried about performance gains myself. Just being able to query the database directly using other tools would be a benefit for me.

  • Like 2

Share this post


Link to post
Share on other sites
Dibbes

@ebr: it's not so much about performance as it is about scalability. With a proper SQL backend it's possible to set up various servers, or even a cluster, without the current necessary trickery to keep databases in sync. When it comes to that, it will definitely increase performance, as the client plugins are not designed for this.

 

Also this would allow to spread the work load. My current setup is not capable of properly transcoding more than 3 streams at the same time on full HD, unfortunately this creates some friction in my house hold. I have the hardware laying around to setup a second server an increase to 6 streams and I already have a SQL server setup for other purposes.

 

So, while you're right that it's an assumption that a SQL backend would magically increase performance and not something that I would take as a fact before testing, the increase in other possibilities as not just a mere assumption, in my opinion...

Share this post


Link to post
Share on other sites
ebr

@ebr: it's not so much about performance as it is about scalability. With a proper SQL backend it's possible to set up various servers, or even a cluster, without the current necessary trickery to keep databases in sync. When it comes to that, it will definitely increase performance, as the client plugins are not designed for this.

 

Also this would allow to spread the work load. My current setup is not capable of properly transcoding more than 3 streams at the same time on full HD, unfortunately this creates some friction in my house hold. I have the hardware laying around to setup a second server an increase to 6 streams and I already have a SQL server setup for other purposes.

 

So, while you're right that it's an assumption that a SQL backend would magically increase performance and not something that I would take as a fact before testing, the increase in other possibilities as not just a mere assumption, in my opinion...

 

Yes but that makes another assumption - not just the support of MySql but the support of all the components of our server for simultaneous access to the database by other servers.  That may be implied in your mind but it is an entirely different request (and yet another layer of complexity).

Share this post


Link to post
Share on other sites
Dibbes

Yes but that makes another assumption - not just the support of MySql but the support of all the components of our server for simultaneous access to the database by other servers.  That may be implied in your mind but it is an entirely different request (and yet another layer of complexity).

 

Good point... you're right of course. However, though it does add another layer of complexity, it also takes this product to a new level...

Share this post


Link to post
Share on other sites
archangelz

I would love the option to store Emby's database in MySQL - I have a setup that serves around 30 people and I feel that there would be real performance gains by using a proper relational backend over SQLite (I'm presuming).

 

On top of this it would make life easier for me when reporting on watched status a cross users.

 

Might be a bit overkill here but for reporting you could always use a powerful tool like Splunk. Dashboard I set up for this mining the server debug logs. I'm running fifteen users now and trying to keep track of who did what at what time was becoming a bit of a pain. This could be a stop gap for now until Emby sets something native up for this. 

O00Ocrr.png

Edited by archangelz

Share this post


Link to post
Share on other sites
pir8radio

Might be a bit overkill here but for reporting you could always use a powerful tool like Splunk. Dashboard I set up for this mining the server debug logs. I'm running fifteen users now and trying to keep track of who did what at what time was becoming a bit of a pain. This could be a stop gap for now until Emby sets something native up for this. 

 

 

Yea I have been thinking of moving to splunk...   I'm doing something similar but getting normal apache logs using a reverse proxy, gives you a decent amount of data.

http://emby.media/community/index.php?/topic/35555-any-interest-in-a-tutorial-for-statsreverse-proxy/?p=335338

 

You should share your splunk settings! 

Share this post


Link to post
Share on other sites
archangelz

Yea I have been thinking of moving to splunk...   I'm doing something similar but getting normal apache logs using a reverse proxy, gives you a decent amount of data.

http://emby.media/community/index.php?/topic/35555-any-interest-in-a-tutorial-for-statsreverse-proxy/?p=335338

 

You should share your splunk settings! 

 

I'm just ingesting server logs from /MediaBrowser-Server/logs and ignoring all the transcode files transcode-.*\.txt$

I can get streaming statistic from mining log entries like Emby.Kodi.SyncQueue: "USERSYNC" as this contains the user name, series and title of the asset being watched.

 

From there I just regex out the user names using something like "(?=[^E]*(?:Emby.Kodi.SyncQueue|E.*Emby.Kodi.SyncQueue))^[^\(\n]*\((?P<username>[^\)]+)"

 

I get session length from the number of posts. To get actual bandwidth usage I would have to use something like your tutorial and feed nginx into Splunk which I haven't done yet and would take a bit of time. 

Edited by archangelz

Share this post


Link to post
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...