Kodi + MySQL slow; The guide for performance tuning and the root cause

Kodi + MySQL slow; The guide for performance tuning and the root cause - Printable Version

+- Kodi Community Forum (https://forum.kodi.tv)
+-- Forum: Support (https://forum.kodi.tv/forumdisplay.php?fid=33)
+--- Forum: General Support (https://forum.kodi.tv/forumdisplay.php?fid=111)
+---- Forum: OS independent / Other (https://forum.kodi.tv/forumdisplay.php?fid=228)
+---- Thread: Kodi + MySQL slow; The guide for performance tuning and the root cause (/showthread.php?tid=222156)

Pages: 1 2 3

Kodi + MySQL slow; The guide for performance tuning and the root cause - Ballistic - 2015-03-23

Hi All,

What is this topic about:
I wanted to make this topic because i am having issues with very slow Kodi + MySQL performance and i think there are more people that run into this issue, maybe without even knowing it could and should perform much better.

What is slow exactly?
Users are reporting slow browsing of the Kodi library since Kodi upgrade. Most noticably; widgets are very slow to populate on Kodi start up.
XBMC13.2 vs Kodi14 example: https://www.youtube.com/watch?v=x6XWqUD7aHc

What can be done?
Lets start by optimizing our MySQL server. (Thanks to forum users for some of the optimizations)

1: Kodi should create your database InnoDB format by default. Check if this is the case as MyISAM is less optimal.

2: add "skip-name-resolve" to my.ini/my.cnf config file.

3: Enable and optimize query caching. Add following to my.ini/my.cnf config file.
query_cache_type = 1
query_cache_size = 15728640
query_cache_limit = 1048576

4: Add table indexes (Possibly useless as its default since XBMC12)
ALTER TABLE movie ADD INDEX ix_idFile(idFile);
ALTER TABLE movie ADD INDEX idMovie(idMovie);
ALTER TABLE movie ADD INDEX idFile(idFile);

5: Clean your Database/Cache. Take a look at this topic: http://forum.kodi.tv/showthread.php?tid=158373

Still slow? Join the club
Even after these optimzations, Kodi is still slow. Let's compare Kodi to XBMC some more. Lets talk about MySQL utilization during XBMC/Kodi startup.
Top = Kodi
Bottom = XBMC

Comparing the two, we see that Kodi is a little bit more agressive on the query side. This might be explained by XBMC grabbing content one at a time and Kodi seems to have a more "grab it all at once" plan of attack.
Kodi seems to be grabbing a little more content but not really seeing any issues here.

Let's continue to disk utilization. I happen to use Apache Webdav as my source content which gives me easy content access logging.
XBMC startup:

Code:
tail -f /var/log/httpd-access.log

Yeah..., Nothing. Everything is grabbed from local cache. This is how we like it.

Kodi startup:

Code:
tail -f /var/log/httpd-access.log

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies/The.Imitation.Game.2014/The.Imitation.Game.2014-banner.png HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies/Birdman.2014/Birdman.2014-banner.jpg HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies/Birdman.2014/Birdman.2014-banner.png HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies/Laggies.2014/Laggies.2014-banner.jpg HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies/Laggies.2014/Laggies.2014-banner.png HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies/Wild.2014/Wild.2014-banner.jpg HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies/Wild.2014/Wild.2014-banner.png HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies/Interstellar.2014/Interstellar.2014-banner.jpg HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies/Interstellar.2014/Interstellar.2014-banner.png HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies_archive/Caligula.1979/Caligula.1979-banner.jpg HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies_archive/Caligula.1979/Caligula.1979-banner.png HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies_archive/A.Beautiful.Mind.2001/A.Beautiful.Mind.2001-banner.jpg HTTP/1.1" 200 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies_archive/The.Girl.With.The.Dragon.Tattoo.2011/The.Girl.With.The.Dragon.Tattoo.2011-banner.jpg HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies_archive/The.Girl.With.The.Dragon.Tattoo.2011/The.Girl.With.The.Dragon.Tattoo.2011-banner.png HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies_archive/Hot.Tub.Time.Machine.2010/Hot.Tub.Time.Machine.2010-banner.jpg HTTP/1.1" 404 -

10.10.50 - Access [23/Mar/2015:12:06:29 +0100] "HEAD /datastore1/Movies_archive/Hot.Tub.Time.Machine.2010/Hot.Tub.Time.Machine.2010-banner.png HTTP/1.1" 404 -

Add 300 more lines

So.. Yeah.. On startup, disk was accessed more then 320 times. Worst of all, these files don't even exist (404) (FYI: All my image/metadata content is local and was scanned local)
I checked if these banners or posters are referenced anywhere (nfo file) but no trace. Something is just guessing these content paths and possibly taking a long time to do it.
Everytime when entering a menu in the library (Movies or TV-Shows), the above disk access is repeated. Causing a major slowdown in library browsing.

My guess is that we have to start looking at the content grabbers. Grab Fanart plugin or the Library Data Provider plugin?
These plugins seem to be doing their job less then optimal since recent updates or they are bieng called in a different way
Grab Fanart Plugin versions:
XBMC: 0.13.2
Kodi: 0.14.0
Libary Data Provider versions:
XBMC: 0.0.8
Kodi: 0.0.8

Jeroen, the ReFocus skin creator was so kind to create a different IncludesHome.xml file for me where he changed the way content it called.
From:

Code:
<content limit="25" target="video">plugin://service.library.data.provider?type=recentmovies&amp;reload=$INFO[Window(10000).Property(

recentmovies)]</content>

To:

Code:
<content target="video">videodb://recentlyaddedmovies/</content>

This has changed the widget population time from 30 to ~10seconds but still way under the XBMC13.2 of barely 2 seconds.

At this moment my knowledge becomes a bit limited as i am not a programmer/skinner. Maybe someone else may be able to give some input?

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - lcapriotti - 2015-03-23

nice, what about optimising tvshows?

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - Ballistic - 2015-03-23

I will look into that later lcapriotti. It shouldn't be hard to translate those lines to make that suitable for TVshows.

Continueing on the issue. I have found the following from a debug log:

Kodi startup debug log:

Code:
Starting Kodi

<cut uninteresting info>

15:13:24 T:5092   DEBUG: CVideoDatabase::RunQuery took 2636 ms for 4422 items query: select * from movieview  WHERE ((movieview.playCount IS NULL OR movieview.playCount < 1))

15:13:24 T:2700   DEBUG: CVideoDatabase::RunQuery took 3027 ms for 4428 items query: select * from movieview

15:13:30 T:5092   DEBUG: CVideoDatabase::RunQuery took 4825 ms for 15839 items query: select * from episodeview  WHERE ((episodeview.playCount IS NULL OR episodeview.playCount < 1))

15:13:34 T:3784   DEBUG: CVideoDatabase::RunQuery took 2178 ms for 4422 items query: select * from movieview  WHERE ((movieview.playCount IS NULL OR movieview.playCount < 1))

15:13:37 T:5092   DEBUG: CVideoDatabase::RunQuery took 2255 ms for 15839 items query: select * from episodeview  WHERE ((episodeview.playCount IS NULL OR episodeview.playCount < 1))

15:13:38 T:4836   DEBUG: CVideoDatabase::RunQuery took 2247 ms for 4422 items query: select * from movieview  WHERE ((movieview.playCount IS NULL OR movieview.playCount = 0))

15:13:40 T:5092   DEBUG: Library Data Provider: Total time needed for all queries: 0:00:18.912000

<Menu is loaded>

XBMC seems to work different:

Code:
Starting XBMC

<Cut uninteresting info>

15:32:11 T:5800   DEBUG: CVideoDatabase::RunQuery took 26 ms for 25 items query: select * from episodeview  ORDER BY dateAdded desc, idEpisode desc LIMIT 25

15:32:11 T:4672   DEBUG: CVideoDatabase::RunQuery took 29 ms for 25 items query: select * from movieview  ORDER BY dateAdded desc, idMovie desc LIMIT 25

15:32:11 T:5392   DEBUG: CVideoDatabase::RunQuery took 16 ms for 10 items query: select * from movieview  ORDER BY dateAdded desc, idMovie desc LIMIT 10

<Menu is loaded>

15:32:14 T:4712   DEBUG: CVideoDatabase::RunQuery took 2799 ms for 4458 items query: select * from movieview

15:32:16 T:5756   DEBUG: CVideoDatabase::RunQuery took 2824 ms for 4451 items query: select * from movieview  WHERE ((movieview.playCount IS NULL OR movieview.playCount < 1))

15:32:19 T:5756   DEBUG: CVideoDatabase::RunQuery took 2200 ms for 14449 items query: select * from episodeview  WHERE ((episodeview.playCount IS NULL OR episodeview.playCount < 1))

It seems Kodi wants all the info, XBMC only loads the widgets required info with max 25 results, continues to load the home menu and does the rest of the stuff in the background.

The old way would be the way to go if you ask me but somewhere it has been decided to change behaviour.

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - Henk84 - 2015-03-24

Same issue here... very annoying problem, browsing and picking a movie or tv shows simple takes to long to be functional...

Code:
17:40:01 T:6720   DEBUG: CVideoDatabase::RunQuery took 2604 ms for 10 items query: select * from episodeview  ORDER BY dateAdded desc, idEpisode desc LIMIT 10

17:43:37 T:6724   DEBUG: CVideoDatabase::RunQuery took 1442 ms for 2 items query: SELECT * FROM seasonview  WHERE seasonview.idShow = 46

17:43:37 T:6724   DEBUG: CVideoDatabase::RunQuery took 193 ms for 0 items query: select * from movieview join movielinktvshow on movielinktvshow.idMovie=movieview.idMovie WHERE movielinktvshow.idShow = 46

[/code

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - twelvebore - 2015-03-24

(2015-03-23, 13:44)Ballistic Wrote: 1: Kodi should create your database InnoDB format by default. Check if this is the case as MyISAM is less optimal.

Really? This runs counter to many years of received wisdom across the Internet. InnoDB has many advantages over MyISAM, but as far as my understanding goes speed is not one of them.

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - Waltman - 2015-03-24

I had the same problems with kodi in december, imo (and at the moment and ass a user) you can monitor and tweak mysql settings as much as you want, you wont really solve the speed problem. Kodi is using resources different than gotham. I use the tool from this post http://forum.kodi.tv/showthread.php?tid=158373
It has done miracles with my mysql acces speed, give it a try.

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - Ballistic - 2015-03-26

Thanks. Thats good information for cache cleanups. However, the problems i am adressing in this topic is about the change Kodi has in getting information from the database (too much at once) which makes it slow if you have a medium to large database.

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - bambi73 - 2015-03-27

I can only speak only about TVShow part of Kodi because it's only one I touched till now.
From my experience problem has 3 parts:

Library size
SQL Views
Kodi code itself

Library size: In my case I have 4000+ TVShows and 70000+ episodes in library. Nothing much what I can't do about it because I want it here Smile

. Unfortunately Kodi isn't optimized for such amounts.
SQL Views: In Kodi they aren't really complex but they aren't simple either. But together with higher amount of data they start to be quite slow. In my case tvshowview is ~750ms and seasonview is ~1500ms, other (episodeview and tvshowcounts) are passable. My solution for this problem was to make them tables (with same structure) filled by triggers instead of views. It returns DB response time into tens ms territory and is completely transparent to Kodi. You can check my triggers at GitHub. Feel free to use them if you know what you are doing, but it's on your own risk as I don't intend to support it Smile

.
Kodi code itself: Even though SQL View optimization helps quite a bit, main problem still remains in application itself. Unfortunately Kodi load TV Show list too often, even when nothing changed it does full roundtrip to DB together with filtering, sorting, looking up current item etc. It works fine with regular library size but response times start to skyrockets with increasing amount of shows. There is file cache implementation but it kick in when response time is over 1s, which is already past usable response. Last year I spent some time to "optimize" code and you can check results at my GitHub repo. But please be warned, these optimizations are quite rough as I'm not regular C/C++ developers and I'm almost sure that I cut some corners more than it's healthy. It works fine for my use-cases, but ... Again feel free to use it at your own risk Smile

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - mkortstiege - 2015-03-27

At least Helix should be a bit more snappy when it comes to mass inserts on non MyISAM backends. @Ballistic, mind checking and posting the "useless" stats for metadata again? I would like to see if we can minimize this ugly behavior to a real minimum.

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - Ballistic - 2015-03-27

Sure ofcourse. I am very glad that you are willing to put some time into optimizing MySQL performance.

Would you like the complete debug log of XBMC and Kodi startup?

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - mkortstiege - 2015-03-27

(2015-03-27, 17:31)Ballistic Wrote: Sure ofcourse. I am very glad that you are willing to put some time into optimizing MySQL performance.

Would you like the complete debug log of XBMC and Kodi startup?

Jep, pretty much the same you posted as snippets. That would help to get a better overview.

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - Ballistic - 2015-03-28

Kodi startup log:
http://pastebin.com/LaAncK4V

XBMC startup log:
http://pastebin.com/Fm1nyZA2

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - mkortstiege - 2015-03-28

Thanks, will check.

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - zerocool_ie - 2015-03-31

fwiw, I have noticed an improvement with Kodi 14.2.

No hard data to provide, but it does seem to be loading lists faster.

(running Bello skin)

RE: Kodi + MySQL slow; The guide for performance tuning and the root cause - popcornmix - 2015-03-31

(2015-03-31, 13:52)zerocool_ie Wrote: fwiw, I have noticed an improvement with Kodi 14.2.

No hard data to provide, but it does seem to be loading lists faster.

I think disabling autocommit was helpful.