Jump to content


Photo

MySQL Support


  • Please log in to reply
80 replies to this topic

#1 dcrdev OFFLINE  

dcrdev

    Advanced Member

  • Members
  • 843 posts
  • Local time: 08:29 PM
  • LocationUK

Posted 08 February 2016 - 03:57 PM

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.
  • MrWebsmith, pir8radio, Beardyname and 19 others like this

#2 Angelblue05 OFFLINE  

Angelblue05

    Advanced Member

  • Developers
  • 12738 posts
  • Local time: 01:29 PM

Posted 08 February 2016 - 04:19 PM

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

#3 dcrdev OFFLINE  

dcrdev

    Advanced Member

  • Members
  • 843 posts
  • Local time: 08:29 PM
  • LocationUK

Posted 08 February 2016 - 04:25 PM

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.

#4 Angelblue05 OFFLINE  

Angelblue05

    Advanced Member

  • Developers
  • 12738 posts
  • Local time: 01:29 PM

Posted 08 February 2016 - 04:49 PM

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

#5 dcrdev OFFLINE  

dcrdev

    Advanced Member

  • Members
  • 843 posts
  • Local time: 08:29 PM
  • LocationUK

Posted 08 February 2016 - 05:08 PM

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, 08 February 2016 - 05:12 PM.

  • Angelblue05, muhfugen and Mklitgaard like this

#6 Angelblue05 OFFLINE  

Angelblue05

    Advanced Member

  • Developers
  • 12738 posts
  • Local time: 01:29 PM

Posted 08 February 2016 - 05:19 PM

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


Sent from my iPhone using Tapatalk

#7 spootdev OFFLINE  

spootdev

    Advanced Member

  • Members
  • 166 posts
  • Local time: 01:29 PM

Posted 08 February 2016 - 06:22 PM

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.


  • Oxide likes this

#8 dcrdev OFFLINE  

dcrdev

    Advanced Member

  • Members
  • 843 posts
  • Local time: 08:29 PM
  • LocationUK

Posted 08 February 2016 - 06:51 PM

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.

#9 Dibbes ONLINE  

Dibbes

    Advanced Member

  • Members
  • 743 posts
  • Local time: 08:29 PM
  • LocationMadrid, Spain

Posted 24 April 2016 - 08:00 AM

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


  • pir8radio, Koleckai Silvestri and Mklitgaard like this

#10 Cerothen OFFLINE  

Cerothen

    Advanced Member

  • Members
  • 213 posts
  • Local time: 03:29 PM

Posted 25 April 2016 - 08:25 AM

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.
  • pir8radio and Dibbes like this

#11 Doozer OFFLINE  

Doozer

    Advanced Member

  • Members
  • 69 posts
  • Local time: 11:29 AM

Posted 30 April 2016 - 12:18 AM

I would also love to see a SQL backend.
  • pir8radio likes this

#12 parasven OFFLINE  

parasven

    Member

  • Members
  • 12 posts
  • Local time: 08:29 PM

Posted 19 May 2016 - 10:39 AM

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.

 

 

 

 


  • pir8radio likes this

#13 b0dyr0ck2006 OFFLINE  

b0dyr0ck2006

    Advanced Member

  • Members
  • 400 posts
  • Local time: 08:29 PM
  • LocationUK

Posted 24 May 2016 - 01:31 PM

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

#14 pir8radio OFFLINE  

pir8radio

    NGINX

  • Members
  • 3058 posts
  • Local time: 01:29 PM
  • LocationChicago

Posted 12 June 2016 - 10:18 AM

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.


  • MrWebsmith likes this

#15 pir8radio OFFLINE  

pir8radio

    NGINX

  • Members
  • 3058 posts
  • Local time: 01:29 PM
  • LocationChicago

Posted 23 June 2016 - 10:35 PM

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



#16 ebr ONLINE  

ebr

    Chief Bottle Washer

  • Administrators
  • 49166 posts
  • Local time: 02:29 PM

Posted 24 June 2016 - 10:13 AM

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


  • drashna likes this

#17 pir8radio OFFLINE  

pir8radio

    NGINX

  • Members
  • 3058 posts
  • Local time: 01:29 PM
  • LocationChicago

Posted 24 June 2016 - 06:42 PM

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.



#18 Luke OFFLINE  

Luke

    System Architect

  • Administrators
  • 146625 posts
  • Local time: 02:29 PM

Posted 24 June 2016 - 06:47 PM

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.



#19 Koleckai Silvestri OFFLINE  

Koleckai Silvestri

    Advanced Member

  • Alpha Testers
  • 3735 posts
  • Local time: 11:29 AM

Posted 24 June 2016 - 10:57 PM

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.


  • cayars and CChris like this

#20 Dibbes ONLINE  

Dibbes

    Advanced Member

  • Members
  • 743 posts
  • Local time: 08:29 PM
  • LocationMadrid, Spain

Posted 26 June 2016 - 06:09 AM

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




1 user(s) are reading this topic

1 members, 0 guests, 0 anonymous users


    byakuya32