[Ballistic]

I have not noticed an improvement with 14.2 unfortunately.

Autocommit:
"By default, MySQL runs with autocommit mode enabled. This means that as soon as you execute a statement that updates (modifies) a table, MySQL stores the update on disk to make it permanent."

It might speed up changes to the database (or maybe not as it needs to wait for disk now)
Anywho, It does not give any improvements to selects.

[mkortstiege]

Correct. Only benefit here is when doing bulk inserts. Adding movies for example is faster now.

[ChipsAhoy]

I've tried all the tweaks for this, but it remains painfully slow. Listings take 2 - 3 times longer to display than under Gotham. A couple of observations:

- The main listing for TV shows and Movies is not too bad, but drilling down into the Season listing for TV shows is extremely slow, regardless of the number of episodes. It feels like it's reading the data for every episode of every show and then filtering, because it takes the same amount of time for a show with hundreds of episodes as it does to display a show with only one or two episodes.
- Once the data has been read once, it appears to be cached, and displays quickly the next time, UNTIL any video is played. That seems to wipe out the cache entirely. It seems odd that there's a single cache for playing videos and for displaying library contents.

I suspect there's an error in the select query, as I can do an external query from my iPad using something like QueryDB and get those results instantly.

[Ballistic]

mkortstiege: Where you able to find some bugs or do performance tuning? Do you have an update for us?

[mkortstiege]

mkortstiege: Where you able to find some bugs or do performance tuning? Do you have an update for us?

Nope, no news. Sorry.

[dlbld]

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 .

This sounds like an idea that i'd like to give a try. I am not totally new to MySQL, but triggers and procedures are not my daily business.
Do I have to do a library rescan to populate the new tables that will replace the views? My Database is called "MyVideos90", I suppose that is the equivalent to your "xbmx_video90" that is mentioned on Github?

Thank you for that solution.

[bambi73]

I renamed original view to TVSHOWVIEW2 and then created table by

Code:

create table TVSHOWVIEW
  select * from TVSHOWVIEW2;

See https://dev.mysql.com/doc/refman/5.0/en/...elect.html
Or you can do insert from select if you already have table. See https://dev.mysql.com/doc/refman/5.1/en/...elect.html

Yes, "xbmc_video90" is my video database name.

Btw, please be aware there is probability next DB update will fail on such updated database.

[Ballistic]

Good information about optimizing MySQL performance. Thank you all.

Unfortunately, the major slowdown is still the dirty (too general) queries in Kodi14.

mkortstiege: Could you tell us if any progress has been made in Isengard regarding this issue?

[mkortstiege]

Not much progress. I think the amount of queries even increased.

[wheemer]

I finally was able to solve my slowness with multi user mysql.

I fixed it by switching to Emby. It's way faster now and multi user is supported out of the box.

[bossanova808]

These emby schill posts are getting tiresome

[bossanova808]

(not per se saying the above is one necessarily but there's been a real rash of hit and run 'emby is great' one liners lately...

[telsin]

mkortstiege: Where you able to find some bugs or do performance tuning? Do you have an update for us?

Nope, no news. Sorry.

So I was seeing 12+ second queries to the views in phpMyAdmin, and my tv and movie lists were very slow. Found this post, and tried checking indexes, etc. Got a little bit of an improvement by adding indexes to lastPlayed and dateAdded in the files tables (both text fields, btw, might be good to make them date fields at some point). Anyway, wasn't a ton of imrovement, with ~5000 shows and ~400 movies, still taking a minute plus to bring up lists, etc.

Then I noticed that while the database was InnoDB, my tables were all still MyISAM, probably because it was an old install that had been upgraded several times. Converted all my tables to InnoDB, and the views now run in milliseconds. Access to the lists in Kodi is quick and sprightly again.

So I recommend double checking your tables to make sure they are InnoDB too, not just the database. I'm running percona's mysql 5.6 atm, about to upgrade to mariadb 10 for what it's worth, but the big issue for we was the table engine.

-Telsin

[Milhouse]

So I recommend double checking your tables to make sure they are InnoDB too, not just the database.

What would be really useful is if you explained what ordinary users are supposed to check, and if necessary, change.

[spcano01]

There was a very long thread about a proposed change to the database schema. Wish it didn't die, as it seemed like a great foundation for lots of neat functionality. Favorites, lists, parental control, speed, etc.
http://forum.kodi.tv/showthread.php?tid=200911

There it is!!Maybe an update!