MySQL Support

[dcrdev 254]

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.

[Angelblue05 4131]

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

[dcrdev 254]

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.

[Angelblue05 4131]

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

[dcrdev 254]

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 February 8, 2016 by dcrdev

[Angelblue05 4131]

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

 

 

Sent from my iPhone using Tapatalk

[spootdev 56]

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.

[dcrdev 254]

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.

[Dibbes 458]

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

[Cerothen 95]

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.

[Doozer 2]

I would also love to see a SQL backend.

[parasven 2]

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.

 

 

 

 

[b0dyr0ck2006 57]

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

[pir8radio 1305]

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.

[pir8radio 1305]

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

[ebr 15663]

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).

[pir8radio 1305]

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.

[Luke 40068]

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.

[Koleckai Silvestri 1151]

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.

[Dibbes 458]

@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...

[ebr 15663]

@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).

[Dibbes 458]

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...

[archangelz 13]

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. 

Edited July 2, 2016 by archangelz

[pir8radio 1305]

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! 

[archangelz 13]

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 July 4, 2016 by archangelz