Slowness on large libraries - workarounds and ideas
#1
[EDIT: split into own thread for further discussion - DB]
First of all thanks for all your work on the music part of kodi daveblake, its so much better today then it ever has been.
A little problem I have though is that my entire kodi becomes so slow when a large library is loaded.
Is there anything that could be done about this? I assume too much is held in memory.

This is on arm btw, its ok on a decent x86.

Thanks!
Reply
#2
Depends how you are browsing the music really. If you are listing all albums then yes its probably slow.

I'd personally break the music down into an A-Z artist node, its nice and quick then.
Reply
#3
Split this out to own thread so we can focus discussion on speed/size issues. I suspect that there is more than one problem involved, so perhaps we can usefully break this down and at least come up with some best practices for those with large music collections to get the best from Kodi.

There is a known fundamental design flaw that makes the songs node particularly slow if like @marantz you have a splendid library with 130k of songs. The UI does not show anything until all the song data has been fetched from the db and them manipulated in local memory, fetching every thumbnail etc. first. Everytime you leave and return to the songs node it repeats this time consuning processing. On investigation fetching the data from the DB takes only 5% of the total time (even on MySQL), the rest is memory manipulation inside Kodi.

Those that have read the Devcon blog report will know that there is work in progress to refactor both the video and music databases into one combined db using an ODB interface. In principle ODB supports lazy loading, and so could ultimately help to improve how Kodi handles large volumes of data. However it is lazy loading on the UI side of the processing that is needed to overcome the slow songs node. Simply switching to using ODB will not resolve this issue at all, there is much additional work to be done elsewhere away from the database. I have ideas, but it is more work than I can cope with alone. No doubt it's time will come one day.

All nodes use the same "fetch all and sort locally" design approach and so they can be slow just like the songs node if they contain enough items relative to processor power, and memory access speeds. I don't know how many artists or albums you have in your library @marantz (could you share those counts), but do you find all nodes slow?

I know from working with @Uatschitchun on this thread http://forum.kodi.tv/showthread.php?tid=306766 that there are other things that can make perfromance slow too. Some of these are things that can be avoided perhaps.
  • MySQL is upto 10 times slower than SQLite on some queiries, so it may be worth sacrificing client/server setup for stand alone installations.
  • MariaDB seems to be considerably faster than MySQL (not sure of the versions, so this could be misleading conclusion)
  • Skins that use the Library Data Provider or skin.widget addon invoke inefficient additional data access, so disabling those and using skins like Estuary or Confluence may be quicker.
  • For random playback of large nodes, party mode playlist is the way to go.

The other work around is to try to avoid large nodes using custom nodes and smart playlists as @docwra suggests. It is possibly easier for me to make accessing the library in smaller chunks available as new features than fix the fundamental large node issue. It also seems more useful to me to be able to get to the 20 items you want than have to scroll through hundreds of pages of items.

But is it just large nodes that are slow, or are there other issues?
Reply
#4
First of all is the artist view which is often where you start, I never browse all albums (impossible). Having 3900 artists I checked the reponse of the query kodi uses.

SELECT artistview.* FROM artistview WHERE ((EXISTS (SELECT 1 FROM song_artist WHERE song_artist.idArtist = artistview.idArtist AND song_artist.idRole = 1) OR EXISTS (SELECT 1 FROM album_artist WHERE album_artist.idArtist = artistview.idArtist)) AND (artistview.strArtist != '')) AND (artistview.strArtist <> 'Blandade artister')

The query itself is fast (0.03s) but the data itselt takes over two seconds to transfer using heidisql atleast (dont know how to check kodi).
You can imagine yourself how much text you get with many artists and strBioography. Is this neeed for the artist view?
Cant kodi execute another query on focus or something?

Also the actual time to render everything (cant messure) but it feels like 7-8 seconds on an arm device.

I have changed from text to varchar on all my fields and this does help a bit.
Reply
#5
Ok an artists node with 3900 artists.... I have near that number and don't see slowness (but that is on SQLite on RPi3). But you do, so let's get into it. Smile

While you can use a SQL browser like HeidiSQL to usefully check the releative performance of queries, the times taken by the browser to display the results don't really mean much. To get Kodi times some can come from the log, but I also analyse in VS2017 debug and add extra time checks to investigate, so things you probably can't do.

Because the SQL used is something that users can see and test in SQL browsers it often gets the blame. I will admit that efforts could be made to optimise the query syntax for MySQL. MySQL has some horrible weaknesses (e.g. it does not apply indexes to correlated subqueries), but working around them could easily slow SQLite and will only gain relatively small amounts of time. As you observed in your example the query itself is fast.

Yes only fetching the fields you want to display, rather than all of them as Kodi does, would make the query faster, but again the query speed is not the bottleneck.

The slowness between fetching the data (query execution of 0.03s so fast) and displaying it comes from 3 main areas that you can't easily see -
a) Sorting the results in local memory (rather than at the DB with an ORDER BY statement)
b) Looping through these results and doing processing per record (artist) to get the thumbnail.
c) Displaying a progress bar from the same thread as that doing the looping.

Of course it does take some time to allocate local memory to hold the biogrgraphies etc. too, but again small issue compared to the above.
Quote:Cant kodi execute another query on focus or something?
No, not with the current design and that is the final big flaw. I'm sure the design made sense years ago for the video lib, but for large nodes (and we all have more media these days) it is a problem. But to change it is a fundametal redesign of the way the UI to DB interaction works.

The current design logic is loosely this:

UI wants to show artist name (no other info) for the 2500th to 2520th artist when ordered by name.
All fields of all artists are queried from the db and loaded into a local list of objects.
This list is sorted by name.
For each entry the thumbnail is queried and loaded into memory too.
The UI can now display that page for artists.

There are several alternative approaches with features like:
Don't lock the UI thread while doing all this.
Only fetch what you display (20 records at a time), or fetch in the background (lazy load UI side as well as db).

Redesign will happen eventually, if the team had more devs it would happen sooner.

Quote:Also the actual time to render everything (cant messure) but it feels like 7-8 seconds on an arm device.
What you said in your op about x86 being OK in comparison does point to it being a memory allocation/processor speed issue, and that changes db side (to the server, query syntax etc.) are not going to help much. It makes an interesting test platform for any solution claiming to fix the slow large node issue.
Reply
#6
On my laptop (kaby lake i7) pressing artists gives

Time to retrieve artists from dataset = 1949

I will test on my arm(cubox) soon (need to reinstall, its broke).

But yes I agree on the query part, those querys that was really slow has been fixed and its no longer such a big issue.

Seems your rpi3 must be faster then my cubox becouse I feel my entire kodi is slowed down when the music library is loaded compare to when its not, my cubox has 1gb ram.
Reply
#7
Don't get me wrong there is still possibilty for improvement in optimising queries especially on MySQL, but it is just not where the really big improvements lie. I would happily get into changing the SQL, and there are things I want to change, but generally the team are moving in the opposite direction. The idea is to let ODB sort it all out (I'm an old dog with lots of db experience so yet to be convinced about "magic middle tier tools" that hide the SQL) and populate the local memory in the background so process efficiency becomes less of a worry anyway.

Will that help your Cubox? I really don't know, hence my testing comment.

Nothing special about my RPi (also 1GB ram), although I think it makes a cracking media player for £30. What skin do you use? Not blaming skins, just it does sometimes seem to make more difference than I expect. Do you leave it on continuously? There are potential memory leak issues that could be a factor. Or maybe network issues, could try a local db on it rather than client server and see what happens .
Reply
#8
I will get back with cubox results once it's up.

On windows (i7)

CMusicDatabase::GetAlbumsByWhere - query took 5851 ms


so those extra 4 seconds is kodi internal sorting?
same query is 2 sec with heidi(including network lag)

edit: i use estuary, everything default
Reply
#9
My 2 cents: If queries and performance are already highly optimised, move on to intelligent caching. For example, if each thumbnail on a network resource generates a query, copy it or at least a thumbnail locally and load it from there. Leave all costly queries for scraping and load as much as possible from cache for sorting and such. Maybe this could be done through a dedicated add-on, like simplecache does.
Reply
#10
I feel exactly the opposite, would be faster the query less and query other stuff once needed. (which is not yet possible as dave said)
Reply
#11
So you favour the impossible over the practical? [WINKING FACE]
Reply
#12
Impossible right now...
Reply
#13
(2017-05-09, 12:32)marantz Wrote: CMusicDatabase::GetAlbumsByWhere - query took 5851 ms

so those extra 4 seconds is kodi internal sorting?
same query is 2 sec with heidi(including network lag)
I did my detailed analysis on songs not albums, but I'm pretty sure the same issues apply to both.

First thing is to know that the 6s reported in the log against CMusicDatabase::GetAlbumsByWhere is not only spent by that routine but an overall time, and most of it is taken outside the CMusicDatabase unit. The 2s taken by Heidi of course includes doing something to display the results but as it includes the network delays it is reasonable to ask where does the extra 4s go since Kodi takes 6s.

Yes, some of the time goes on internal sorting. You can get a feel for how much by swapping sort order, what happens then does not involve the db in any way, but I would say it is roughtly 1s of the 4s you are wondering about.

I believe (I need to actually test to be sure) that of the remaining 3s half goes checking and fetching the art work (if there is any), and the other on the progress bar.

I guess it could be interesting to make a build that didn't show a progress bar or fetch art and see just how fast it is even on large libraries. It would be ugly of course, no art, but I think it would be much faster.
Reply
#14
(2017-05-09, 12:48)HeresJohnny Wrote: My 2 cents: If queries and performance are already highly optimised, move on to intelligent caching. For example, if each thumbnail on a network resource generates a query, copy it or at least a thumbnail locally and load it from there. Leave all costly queries for scraping and load as much as possible from cache for sorting and such. Maybe this could be done through a dedicated add-on, like simplecache does.
This is actually the current design. The issue is that putting things in internal memory (effectively cache) takes time and while this happens the GUI waits, so while it is happening Kodi does nothing but show a progress bar (on the same thread as the caching thus slowing down the process even more).

There are obvious ways this could be improved, but that does not mean that I or any other dev have the time or skill to make the fundamental changes required at this time.

Getting just the stuff we need only when it is needed would be my prefered approach. That would take a fundamental redesign too. One reason I am persoanlly not trying to rework the load the lot into local memory approach is that I would choose to do it differently. And by time I went off on my own and fixed it, Kodi would have moved on in some other direction and my work would be wasted.

The best I can do here is try to make the design issues and options clear, then at least we can lean the ladder against the correct wall.
Reply
#15
I really hope that a dev will be able to add lazy loading to the KODI core one day.
Reply

Logout Mark Read Team Forum Stats Members Help
Slowness on large libraries - workarounds and ideas0