MySQL: What'd I do wrong?

Help and Support Vero 4K / Vero 4K +
23

I assume you have a rather large TV collection then. Apparently the way that kodi is programed for the database is not terribly efficient so with larger libraries and/or lower power backends performance will suffer. I believe there were some tweaks a few years back that added compatibility for newer SQL versions (or something, my memory is a bit fuzzy on this) but had a performance impact. I think the new tweaks were not supported with MySQL 5.5, but Kodi still works without issue with that version, and it does not seem to take the speed hit. A few months back I tried moving to newer versions of both MySQL and MariaDB and it was both slower (despite my SQL server running on a fast PC) and had the watched status bug on all newer versions.

Long story short you could try running version 5.5 and see how it works for you. You could install it on the Vero and test it on a second profile to see if it works for you without losing your current setup in the meantime (you would have a different advancedsettings file in each profile pointing to the different databases). I can’t help you on how to install 5.5 on Linux, and Kodi says this version is unsupported, but I think it’s worth a shot. I can tell you I have instant access to all my shows currently with 189 different shows and 10k+ episodes. I do have a delay of maybe a second or so to bring up some of the music screens but with over 2400 fully tagged albums this seems like a reasonable delay to me.

Also remember that when you cleared your artwork Kodi is going to have a delay as it goes to retrieve it the first time you go into each folder with it missing. That would not be the cause of the massive delay you are seeing but it might be contributing to it.

24

I’m at a few thousand episodes (if that’s considered “large”) but you mention you have instant access. Are you not using MySQL then?

25

I have a dedicated PC that is on 24/7 that is running MySQL 5.5 and hosting my media files. I run OSMC on four different Pi’s around my house (Four different generations) and have Kodi connected to it on a few PC’s as well. I don’t know where exactly the cutoff for “large” is, but I know I passed that point with my setup as I ran across the watched episode bug (with newer SQL versions). The number of files to get there is dependant on how powerful the machine running the SQL database is. More powerful machines can handle a larger library before performance tanks. My point was that from my testing the current state of Kodi is really bad with newer SQL versions. As such, lower end hardware is performing worse than it should with larger libraries. I’m suggesting that SQL 5.5 might work much better. I have only tested this on my own. I have not seen others suggest the same but I came to this conclusion after spending many hours reading everything I could find in various forums and putting the pieces together.

Hopefully one day Kodi will find someone to optimize MySQL but this has long been an issue and they don’t seem to have a dev that has wanted to pick up that mantle. Running a multi-user database has always been a bit niche for Kodi so it has been lower priority than much of the rest of the program.

26

Ideally I would like to have a HTPC, but I’m concerned with the electricity costs. I have wanted to build a small headless NAS/HTPC, but haven’t pursued it for this reason.

27

The Vero is alot faster than the pi and should have the sql database on it, but even the Vero is slow for that type of work. It takes it a while to do a update of the library from my vero to my windows powered mysqlserver.

if you are going to use smb do consider to add the following to your advancedsettings.xml (it makes browsing smb shares with alot of files/directories faster, which also translates to faster library updates).

<samba>
<statfiles>false</statfiles>
</samba>
28

Will do! What does the setting do?

29

That won’t help with slow SQL queries. And it will only make a difference if using Kodi SMB mounts.

I was playing around with the SQL today, and I think I may have a fix. I got a TV show that would take over 10 seconds to load down to about 2 seconds now. I need to test more and make sure that I didn’t break something unexpected.

30

That won’t help with slow SQL queries. And it will only make a difference if using Kodi SMB mounts.

Yes, thats why I started with “if you are going to use smb”.
Actually it does help, not with the database beeing slow but with the filesystem responding faster which makes both browsing and updating the library faster.

The setting makes kodi display files faster by not running smb stat().
https://kodi.wiki/view/Advancedsettings.xml#samba

i’ve got one tv show with over 500 episodes, browsing it is almost instant with smb stat set to false on the vero, if set to true its alot slower. (with smb stat set to false kodi on the vero is about as fast as kodi on my core i7-6500u laptop). I do however have my mysql database on another computer where it’s configured for performance.

31

Did you have any update on this? I’m curious on what it was that you tweaked.

32

If you know much about MySQL, the problem is in the season_view. It has un-needed group by clauses that were slowing it down. If you have MySQL Workbench or phpMyAdmin and are comfortable with using them, you just need to modify the season_view. Look at the end of the query and remove all but the first clause in the GROUP BY so it looks like this:

Before:

group by `seasons`.`idSeason`,`seasons`.`idShow`,`seasons`.`season`,`seasons`.`name`,`seasons`.`userrating`,`tvshow_view`.`strPath`,`tvshow_view`.`c00`,`tvshow_view`.`c01`,`tvshow_view`.`c05`,`tvshow_view`.`c08`,`tvshow_view`.`c14`,`tvshow_view`.`c13`

After:

group by `seasons`.`idSeason`

Before doing that I’d suggest just to be safe that you make a copy of the original season_view

33

Thanks for the reply. I don’t, but I muddle my way through. I gave it a try but I could see no difference on my particular setup. I am running my db on some hefty hardware and don’t have a performance issue so if there was a difference I don’t know that I would have been able to notice. I am also running MySQL 5.5 and I think that the extra sorting is actually unsupported and therefore ignored. I ran across some information about this issue before and saw that the change in the view was made to make Kodi compliant with SQL v5.7+. I will revisit this again if I decide to upgrade my database. When I tried newer SQL versions a few months back my performance dropped and I reverted as I couldn’t find a solution to the watched status bug.

34

I’m using MariaDB. I don’t know why this didn’t fix it for you. Makes a huge difference for me.

I have not tested it yet, but this has now been updated to work with V18 if you want to try it:

I know the V17 version worked great, so if you are wanting to experiment give it a try.

35

Hello, also using more then one instance of SQL database kodi client and have always see slow quering to the base resulting in freezing in movies view. I use mariaDB, the newest version. You’ve recommended to modify the season_view, but as I understand the modification shiuld be done on the sql client which makes queries but not in table of database. Could you explain it, please ?

36

No, you make the change to season_view on the MariaDB server, not the individual clients. After making the change you will not need to restart Kodi, it will just pick up the change since it will use the new season_view.

37

I think I may have run across that thread after I gave up and reverted back to SQL 5.5 as it did not have the issues that I saw with newer versions. I have no background in databases so much of that thread was over my head. I could have fixed the watched status bug it would seem with his modified file but downgrading seemed to have no downsides.

As for your database tweak I can only guess why it has no impact on my particular install.

(original query)
RunQuery took 451 ms for 27 items query: SELECT * FROM season_view WHERE season_view.idShow = 707

(with the extra bits removed from the query)
RunQuery took 484 ms for 27 items query: SELECT * FROM season_view WHERE season_view.idShow = 707

That half of a second is mostly buried in the menu transition so it does not feel like a delay.

38

You are only getting a .5s delay, so that should not even be noticeable. It’s interesting that with my fix it slows the query down by .02s Not that you would ever be able to see that!

39

I’m running mysql 5.7,
tweaking the view makes the query 0.05s faster (i just made a new view and ran both i mysql workbench).

adding an index to files.playCount makes tv-show listing a bit faster in kodi.
adding an index to episode.c05 makes tv-shows even faster. (or does it, hesitating on this one)

looks to me like kodi is a bit strange, instead of running one query with alot of results i runs the same query over and over with different idShow for each tv-show in the database
SELECT * FROM season_view WHERE season_view.idShow = 171
it takes about the same time to get one row as it does getting all the rows, when displaying all the tv-shows titles (the list in kodi), which makes it slower for each show you add since that will add yet another query…