Watched Episode Status not updated (SQL Setup)

Help and Support
22

Here are some hard numbers - used half my library to make it more reasonable comparison.

SETUP

  • OSMC October 2017 on Vero 4K - Custom Patch to not cache any windows and therefore execute SQL statements everytime even when they take more than 1 second to prevent inconsistencies in displaying episode counts or watched status - see inital post.
  • MariaDB 10.0 on a Synology DS214+
  • WLAN connection - some latency here for sure in general.
  • Library Size for Testing: 253 TV Shows, 13594 Episodes (481 Unwatched) - used only half my library for testing for more comparable numbers. The larger the more extreme the difference will be.
  • Index for played count added to files table because of my library nodes that often execute queries where watchcount is involved.

Only ran one test, but you already get the picture. Normal network activity at home.

REFRESHING RECENTLY ADDED EPISODE WIDGET

OLD: RunQuery took 202 ms for 481 items:
NEW: RunQuery took 129 ms for 481 items

Original episode_view because it fast enough ) as it is just a simple join. Note I have an additional index set on the files table for playcount.

OPENING TV SHOWS

OLD: RunQuery took 1341 ms for 253 items
NEW: RunQuery took 520 ms for 253 items

Original view is significant slower, though still acceptable.

OPENING TV SHOWS WITH UNWATCHED EPISODES

OLD: RunQuery took 945 ms for 28 items
NEW: RunQuery took 85 ms for 28 items

Ouchā€¦ That difference in performance is not acceptable. As soon as some WHERE clauses come in on tvshow_view the problem of the view start to show as values are counted each time.

OPENING IN PROGRESS EPISODES

OLD: RunQuery took 32 ms for 7 items
NEW: RunQuery took 11 ms for 7 items

Original episode_view is fast enough as it is just a simple join.

OPENING RECENTLY ADDED EPISODES

OLD: RunQuery took 200 ms for 481 items
NEW: RunQuery took 149 ms for 481 items

Original episode_view is fast enough as it is just a simple join

OPENING LARGE SEASON (X-FILES 10 SEASONS WITH 210 EPISODES)

OLD: RunQuery took 3716 ms for 11 items
NEW: RunQuery took 6 ms for 11 items

Muwahahaha. :sunglasses:The season_view is just bad. Unacceptable bad.

OPENING EPSIODES (X-FILES 1ST SEASON)

OLD: RunQuery took 29 ms for 24 items
NEW: RunQuery took 15 ms for 24 items

Original epsiode_view is fast enough as it is just a simple join

So what is the result:

  • season_view has to go. It is absolutely unacceptable
  • tv_show_view has to go as well depending on the query it can slow down things a lot, also season_view uses it for some data sigh
  • tvshowcounts seems ok, has not much impact, but will shave of a bit on the season view as well. Debatable
  • episode_view seems fine as it is a simple join with not much performance impact.

Anyway, big improvement everywhere overall especially with changing seasons_view and tv_show to a table. And that is important as you easily drill down from Show ā†’ Season ā†’ Episode and waiting for the seasons for several seconds is just painful.

The rest just icing on the cake to shave of a few more ms. I guess I leave then as tables now.

Doing the movie_view will not result in much improvement, as it is once more a select * and it is more or less a simple join similar to episode_view. Not much I can do here. But itā€™s not like movies increase every week by a few. So putting more into sets will probably help to cut the list down.

Music? I bet there will be an improvement, But not really interested in it right now.

EDIT:
I repeated the rests a bit like running the queries Kodi did 100 times in a row. Well first, Kodi itself seams to create some overhead here as well when receiving and does some processing with it before it opens the window. Too lazy to check the code what is done there. What is logged basically matches until I see something on screen.

Also most of the longer queries seems to be network related delays as well, meaning mysql client talking to the server. Well with a bigger data set no wonder, even though the data itself is still quite small.

The queries are executed now lightning fast on the server itself, so throwing more RAM or tweaking some settings does nothing to it. We already talking a few ms, just takes time until it reaches the client and is processed by Kodi.

For example the ā€œselect * from tvshow_viewā€ query takes 0.4ms on the server, well itā€™s a ā€œselect *ā€ so nothing much to do and no view involved here. Then it takes about 250ms to get it to Kodi. And there it takes about 250ms to be processed (then time is logged) and next Window is opened.

So all in all full success. I canā€™t get Kodi tables here any faster on the SQL side. Optimization must be done on the Kodi side in processing it (whatever takes additional 250ms) and working with smaller data sets (now we enter architectural problems of Kodi itself).

1 Like
23

FWIW I have a HP N54L Microserver running Openmediavault with 4 x 3TB drives. I have ~2500 movies, ~90000 music tracks and ~1000 tv episodes currently running at around 7TB in all. I donā€™t know whether this is considered a ā€˜largeā€™ library but using the default (ie. local to each device) database settings running queries is pretty much instantaneous on my Vero4K or Macbook. It takes a couple of seconds changing screens on an RPI.

I recently experimented with using a central database on the OMV server but I found it completely unusable. It was orders of magnitude slower. I didnā€™t have the Vero4K at the time, but both my Wetek Hub and Odroid C2 took minutes rather than seconds just to boot and I could make - and drink - a cup of coffee changing movies views.

Actually streaming a movie from the server was as fast as ever.

So, yes, the database needs some work ā€¦ :wink:

24

That is something several of us have known for some time. The best thing to do is to highlight the issues you are having on the Kodi Forum as ultimately this is something for the Kodi devs to resolve.

Maybe @TwentyWasHere would be kind enough to share his improvements there also as it seems he has made some dramatic improvements. It would be a shame if the appropriate team couldnā€™t benefit from his wisdom.

25

The idea is not new, there was an old post where someone did what I did, I just upgraded and tweaked it a bit to Krypton.

Also there have been DB discussions by the Kodi Team several times, but they just died.

AFAIK their plan is to ODB. Basically trowing a middle ware as the magic pill into the mix so Kodi itself does not have to deal with tables columns and SQL at all.

Doing that basically means: We give up, our stuff is so badly written we cannot fix it. So we rewrite it using a middle ware and hope the middle ware does a better job then we did. IMHO likely for Kodi. But still sub par.

You have the data structures defined by ODB and it puts them into a DB based on your C++ classes. Basically think of making C++ structures persistent.

The problem: ORM approaches usually cause performance problems. Well Kodi might benefit here, but not naturally in a good way as it takes a bad design and basically serialize it into the database in simple terms. The idea is that programmers do not need to worry about databases.

But as said for Kodi it might provide a benefit as they can ignore their bad database design and queries. Also ORM approaches do their own caching as needed. It is a good idea to use such a middle ware if you do not have the resources or skills to improve your DB design and queries.

Performance improvement and fixing long standing architectural issues and problems were never in the focus of the the Kodi team. It is like a ton of open source projects. Pet projects by the developers have the highest priority. Was it is boring is rarely touched. And users usually kill the problems by better and faster hardware. Luckily todayā€™s SOCs are really good.

And even with an ORM approach it might not help at all if you have horrible design data structures and how you use them. A middle ware is no magic pill. It always adds overhead.

Weā€™ll see which direction they are going in the end.

But the right approach would be just to re-design the DB then update the Database classes to do proper queries as well as make changes to some architectural stuff. For example: queries can run in their own thread and information can be fetched on demandā€¦

More small queries at run time that just take a few milliseconds usually feel faster for the user compared to big queries and then waiting.

Anyway, off doing the movie_view just for funsies as wellā€¦Will not do much as it is basically a SELECT * when opening it. The better approach would be: analyze what data fields a skin actually uses and only query them and get additional details on demand (why have plot loaded for all movies when the user displays only posters? You can load the plot when the user presses ā€œiā€. It is as simple as that). Makes the result of the query way smaller.

Only query what you need when you need it. It is as simple as that.

There should never be a SELECT * FROM TABLE; in your code. Either you do not need all columns or you can reduce the rows by a WHERE clause.

1 Like
26

Oh I know. Iā€™ve followed several of the discussions over time. Iā€™ve done a fair bit of database work over the years and whilst I understand why what youā€™ve done works I donā€™t have sufficient experience of MYSQL to come up with the solutions.

Agree it is always the boring bits that get neglected and yet often they make the most significant differences. I think maybe the Kodi project could benefit from having an enthusiastic DBA on board to push for these improvements.

Improved native access is always going to be faster and more reliable than adding any form of middleware.

27

Iā€™d be really interested in giving this a shot when you are ready to share.

I have a mysql database shared between multiple physical connections linked by OpenVPN tunnel (10Mbps max) and experience the poor performance and cached watched status way too often.

Also willing to be a tester if it will help.

28

Sure, when I am finished (doing Movie view this weekend) Iā€™ll post the SQL on GitHub for testing and Feedback.

29

ā€¦so now that TV shows and movies do not use views anymore I can change the tables easily.

Indexes: well I put the obvious ones in plus the ones I saw constantly in the kodi.log. Initially I just used the ones from the underlying tables. Many of them were just set to support the joins for the views. So cleaned up a bit on my new tables. I leave the original ones untouched to easy upgrades in the future.

Now the SELECT * queries are still very slow to begin with.

I checked if temporary tables are involved because as TEXT and BLOB cannot be used for in memory temp tables. Seems not, at least EXPLAIN does not indicate any. Must be just some disk IO in the end and transferring the data over the network.

Before my change it was really bad with temp. tables on the diskā€¦

There is a some more speed gain if I switch TEXT to VARCHAR of an appropriate length. And then I could move my new table into memory on startup. Nah, donā€™t have the RAM to spent on my NAS. I got the current speed improvement basically for freeā€¦

Guess this is as far as I can go without changing the DB design and the code. Already spent too much time on this one.

I set out to fix the watched status indicator for me, then fixed the extremely annoying slow season view and even put icing on the cake by making episodes and movies a table instead of a view even though th speed gain is not really significant.

Guess thatā€™s it then. The rest would involve rewriting some of the queries and changing the DB. But I donā€™t wanna do that.

Anyway, going over my SQL script later and share it on github in the coming days for those who are interested.

30

In case you wanna give it a try:

https://gist.github.com/Scythe42/ec08732c33c25db1f44532207bbf0f99

Replaces all the views in the Movie DB with tables updated by triggers.

Should contain all the changes I made. Hopefully did not make any copy and paste errors when taking out of the DB into the script.

Changing the Episode View, TV Show Counts and Movie View does only give a small speed boost. On TV Show View and especially Season View there is a significant improvement.

Have not done the Music Video View as I do not have any stored. If wished I can add it as well.

Let me know if it works out for you or what bugs you find.

For fallback: rename the _orig view back to their original name and remove the added "call update" procedures from the triggers. Should be straight forward.

1 Like
31

A small typo on line 1347 :wink: Extra ) at the end of the line.

And I do seem to see an improvement! Iā€™ll have to do more testing to be sure.

Thanks for all your work on this!

Episode view for me: Original about .05, new .0014
Season view: Original: about .7, new .0016!

32

Thx for trying it out. Fixed the typo in the GIST. Let me know if you run across any other issues/bugs in your testing. Pretty sure there might be a few with the amount of copy and pasting. Also if you see any missing indices, better SQL or anything like that. I am really rusty at this stuff, havenā€™t done SQL in ages.

Will do probably the music views as well next and just for good measure throw in the musicvideo_view in.

Naturally the triggers generate a small overhead when updating, but scraping already takes way longer so when adding stuff there is no perceived difference. And updating watched status, well it is basically as slow as it ever has been (need to check what takes Kodi so long there in the code).

And great to see there is a speed increase on your end as well, though it is most noteable in the season view with larger libraries.

Also update to Leia will be straight forward so far. Only one column was added so far. And given no changes where made to the real tables Leia will update fine. So once final is release an update to the SQL should be easy.

33

Iā€™d be interested in the Music updates, as the current version really seems slow. I havenā€™t looked that close at the SQL yet, but I will (Iā€™m no SQL expert, but Iā€™ve done my shareā€¦)

34

The music should benefit as well I guess. But probably not that much on larger collections given the views are relatively simple. Well, I will know once I started with it. Maybe next weekend.

35

Iā€™ve used sqlalchemy (Python ORM) in the part, and it does work well, and doesnā€™t seem to add much overhead. You had mentioned that the Kodi team was considering an ORM.

At least then there would not be ugly things like C00ā€¦

36

Made some minor adjustments on the SQL (two copy and paste errors). If you using it or trying it out, just run the thing againā€¦

37

@TwentyWasHere Foremost, thanks for the script. works like a charm for my collection as well!

I was wondering if you are planning to update your script now that OSMC with Kodi v18 was released?

/edit: oh wowā€¦ just notice that this is over one year oldā€¦ so that probably answers the question.

38

Not much changes just two more columns more in two views, so an easy update. Once I updated to v18 Iā€™ll post an update here as well. Just waiting out 1 or 2 releases until most issues are iron outā€¦

39

I just glanced at the script, but I take it what you are doing is using triggers to copy all changes from the real tables (movie, tvshow, etc.) into the new tables that mimic the old views, correct?

This really shouldnā€™t gain much performance if the original tables have the same indexes that you apply to the new ā€œmimicā€ tables. With a even a half-decent external system running MariaDB, you can keep the entire Kodi database in RAM (via cache).

How big is your kodivideo### database? Even with all those TV shows, it shouldnā€™t be much more than about 200MB.

47

Yeah, quick and dirty solution to the horrible DB design, the even more uglier queries and the basically non existent caching in Kodi. Look at the code yourself and youā€™ll see. Itā€™s not a matter of total size or indexes.

Kodi shoudlnā€™t require more an Pi for running a DB with no DB tweaks at. But well, it requires way more once you total number of entries get somewhat larger, itā€™s most obvious in the season view. Also Kodi doesnā€™t properly cache at all. It has a nasty habit: it just remembers how long a query took the last time, if that query repeats while the data set is still in memory (in simple terms Kodi usually dumps information from the BD constantly) it re-uses the data and doesnā€™t query again until you completely leave a window - and it never updates that information when it writes new stuff to the DB. Hence you see inconsistencies with watched status on a library with quite a lot of TV episodes for example even if you only use one system - once you hit the magic threshold.The DB design is just really bad, they never changed it but kept adding to it.

This is a quick and dirty solution I made way over a year ago that didnā€™t require any changes to the code or 3rd party tools or any changes to SQL configuration files. And shared with with others who might be interested. Sure, niche usage caseā€¦

Yeah, team Kodi knows this. DB stuff has been discussed over and over again. No one really wants to touch it, is interested in changing it. They properly opt for an ORM solution at some point. Not much to discuss here, even Team Kodi admits that the design and the queries are long overdue for a rewrite.

Try it for yourself on a Pi with letā€™s say 250 TV shows/15K episodes in small and large shows and enter some larger shows in a season view and see how long the queries took - no SQL Server optimization just out of the box setup. Yeah, itā€™s bad, really badā€¦

Anyway, not much to discuss here, nothing new to see. Someone asked for a Kodi 18 update and Iā€™ll provide it once I updated my systems. Still niche usage case and still a quick and dirty hack.

48

I saw this inconsistent watched status behavior when I switched to newer SQLā€™s a few months back. They all seemed noticeably slower as well. IIRC I tried MySQL 5.7, Maria 10, and an older Maria I canā€™t remember. I tried it with both my existing db and starting over from scratch which did not make any noticeable difference. After a few days of trying to figure it out I ended up going back to MySQL 5.5 which has zero issues that I have found.