[Uatschitchun]

Code:

NOTICE: My Music: Scanning for music info using worker thread, operation took 18:57:21

[DaveBlake]

Code:

NOTICE: My Music: Scanning for music info using worker thread, operation took 18:57:21

Is this you saying that your music collection has scanned at last?
I expected more excitement

Some users are experiencing being taken straight to the songs node when then enter the music library by default. I am hoping that you are not one of them. Maybe click on Genres or Artists on the home screen to avoid it?

[Uatschitchun]

Yes, I'm through :-)

Yippieh!

Now we only need to find out what's that issue with not releasing memory... I'll first try to find out what was issuing these queries every 10 minutes. As you can see from the logs, there was nothing special...

Anyway I can help to find out about that leakage?

[DaveBlake]

Beers all round.

I am pretty sure that the unplayed song queries were being triggered by the Library Data Provider despite your efforts to disable it. But if you find something else then do let me know.

EDIT: Just be told that service.skin.widgets does a similar thing. You may have had that from older installs, and the addon db (kept on MySQL) gets migrated to new installation

I am unsure if the memory issue was because of the unplayed song queries grabbing huge amounts of memory and then releasing it (and some kind of fragmentation was happening), or because of some leakage during scanning itself. It would be nice to know this.

To test:
1) one way would be to enable the LDP again, and just let it poll (evey 10 mins it triggers the query). Do nothing else. What happens to memory?

2) The next would be to start with a clean SD card (you have a supply of those, right?), install LE, point it at a different MySQL server (no Kodi db files), add source, and scan again. What happens to memory with just scanning to populate an empty db, and no unplayed songs query popping up.

I don't know what you need to do to give Kodi a clean db start on MySQL. I don't want to delete the databases that you have created, but I would like them out of the way while a clean test is done. Rename the schemas? Export off somewhere safe? Another instance of MySQL?

3) Try the same things but with a Windows platform (not RPi) onto clean MySQL

4) Try same with Windows and local SQLite db

Your vast music collection is a great test resources as long as you have the patience too

BTW I have PR up to make the random unplayed songs query less memory hungry, hopefully with get into 17.1. Meanwhile working so inefficiently might at least show us something about something else.

I hope you didn't have any other plans for the weekend

[Uatschitchun]

I am unsure if the memory issue was because of the unplayed song queries grabbing huge amounts of memory and then releasing it (and some kind of fragmentation was happening), or because of some leakage during scanning itself. It would be nice to know this.

As far as I can see, it's maybe both. During scan, the memory gets filled up slowly but without a negative drawback. Only together with the query it seems to become a problem.

Ad 2.) I'm doing that right now. As scanning the source did work and I had lots of changes to the source still in pipeline, I fixed all(!) tags that MB didn't recognize and am scanning right now. Clean 7.95.3 LE on fresh SD (like before, when scanning did work)

Ad 1.) I'll check that once source is read

Ad 3. & 4.) I have no Windows, only in my walls

Your vast music collection is a great test resources as long as you have the patience too

Sure

[DaveBlake]

Yes it could be a bit of both, we need to try and isolate it.

Fresh SD card, but did you also find a way to make it a new db on the server, not a migrated one?

Not sure where I got the idea of you having Windows platform from to test with as well (but surely most households have Windows on something?), I would have liked to see if we have RPi/LE specific issue of any kind.

I also wonder how much of your lib we could fit on the SD card in a SQLite db rather than using MySQL. It should be faster locally.

As an aside I have just discovered that a smart playlist that includes all songs is 3 times slower to display than the already slow songs node - do not try such a playlist on your huge library!! Something I had never timed, it was a shock. Having fetched and sorted the data (15% of time, 1% is actual DB) the UI waits (interruptably on a different thread) while Kodi does lord knows what - I am investigating. Music playback is uninterrupted..

[Uatschitchun]

What I did:

After the above attempt to scan the source did succeed, I dropped the DB again and started anew, once I had all files tagged completly.

The full scan did succeed with a fresh SD install (as before). Here's the mysql-slow.log:
http://sprunge.us/ZfLB
(In the end there's update from Yatse running)

So fresh SD, fresh and complete sources, fresh DB - check

I will now switch skin to Xonfluence to get LDP in game again and let it run with just ideling around and see if memory fills over time

I would have liked to see if we have RPi/LE specific issue of any kind.

Yes, a cross test would be good, but honestly no Windows here, only Linux ;(

I can check with my laptop and a single Kodi install on Ubuntu (no LE!)....

I've got 3 clients using the same DB, so Mysql is a must have. For testing I can check with my laptop and a single Kodi install on Ubuntu (no LE!)....

As an aside I have just discovered that a smart playlist that includes all songs is 3 times slower to display than the already slow songs node - do not try such a playlist on your huge library!! Something I had never timed, it was a shock. Having fetched and sorted the data (15% of time, 1% is actual DB) the UI waits (interruptably on a different thread) while Kodi does lord knows what - I am investigating. Music playback is uninterrupted..

Hmm... I'll see how the DB will perform...

[Uatschitchun]

Ok, here are some news...

As I finally got my source read in, I stumbled over some tagging issues (mostly genre), which was wrong. Together with changing the genres I also did some moving of folders, as Picard isn't that consistent when it comes to artists (example: some Smashing Pumpkins are 'Smashing Pumpkins' as artist and some have 'The Smashing Pumpkins') so I had 2 folders (renaming and moving done with a script in Picard) for some artists...

After the changes being done, I did update the DB and did a clean afterwards. Here the problem occurs:
Cleaning the DB takes ages and throughout the process kodi simply doesn't react anymore to any kind of input. Did check on RPi3 and laptop (both v17). I can see that kodi is still alive from the logs, but I can't cancel the cleaning nor anything else but kill.
The SQL query for cleaning is still running after that (show processlist. The first parts of the cleaning do well (hell slow, but working). During the process a temporary table tmp_delartists gets created and from this point on it gets stuck.

26089 Query CREATE TEMPORARY TABLE tmp_delartists (idArtist integer) CHARACTER SET utf8 COLLATE utf
_general_ci
26089 Query INSERT INTO tmp_delartists select idArtist from song_artist
170221 11:11:10 26089 Query INSERT INTO tmp_delartists select idArtist from album_artist
170221 11:11:11 26089 Query INSERT INTO tmp_delartists VALUES(1)
26089 Query delete from artist where idArtist not in (select idArtist from tmp_delartists)
170221 11:11:14 26089 Query DELETE FROM album_artist WHERE album_artist.idArtist = old.idArtist
26089 Query DELETE FROM song_artist WHERE song_artist.idArtist = old.idArtist
26089 Query DELETE FROM discography WHERE discography.idArtist = old.idArtist
26089 Query DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist'

The bold line indicates the, from my view, beginning of the problem. After that query there are 4 queries following for each artist, I guess: DELETE FROM album_artist, song_artist, discography, and art. These individual sets of 4 queries for each artist takes from a minute up to multiple hours each :-(
I can see the process in the process list and I can see Mysql running with 100% on only one core. Forcing some other DB access from other clients, I can see Mysql go up to 400% (100% each core - Intel Atom).
I'm sadly not able to get to see tmp_delartists or count the artists, as my Mysql knowledge is less then useless - I guess once able to use kodi, I'm an expert :-)

As said, as the RPi3 only has 100Mbit I tried on my laptop (more powerful and GBit) to no avail.
What I don't understand is why Mysql is only running at ¼ of its speed when cleaning the DB.. But that's with all processes coming from kodi... it's ¼ CPU for each process... cleaning DB = 100%, cleaning + update another clients DB = 200%, cleaning +update + maybe texturecache = 300%...!?

I'll paste my my.conf here:
http://sprunge.us/NVYj

Is your last PR improving the cleanup?

[DaveBlake]

Is your last PR improving the cleanup?

Sorry no. :p

Cleaning up a huge db is a whole new can of worms. It is not something I get to test first hand, have to wait for a willing volunteer. I'll take a look tomorrow at what you sent.

Meanwhile just drop the db and start again if clean isn't. You must be well practiced at that!!

[Uatschitchun]

Cleaning up a huge db is a whole new can of worms. It is not something I get to test first hand, have to wait for a willing volunteer. I'll take a look tomorrow at what you sent.

Any other than me with 130k?

Meanwhile just drop the db and start again if clean isn't. You must be well practiced at that!!

Did drop already...

[Uatschitchun]

Just as an update, I switched to MariaDB instead of Mysql and the scanning is fast as hell (or somewhere on the way to it :-)

With Mysql I had an max average queries per second of about 80 continously slowing down to about 40
With MariaDB I have an avg of 260 and it is still getting faster...

Together with that I tried using mysqltuner.pl and did some small changes to my.cnf.

[DaveBlake]

I have a test db file from another long standing user with 140k songs, but some testing only works when you have the actual music files behind it which obviously I don't. You collection is certainly large, but you are not alone. However you are the only person I have on a RPi with that size of lib ready to do some testing :p

So MariaDb faster than MySQL 5.5, interesting
We did have some issues reported with MariaDB some versions back, I believe they are fixed but interested to see what happens for you.

Still eager to know about the unplayed songs query and memory issues. Maybe it all goes away with MariaDB, and not having LDP running during scanning? As I said v17.1 will have a fix for the inefficient unplayed songs query - no longer fetching the entire songs table into local memory just to give 10 random ones!

[Uatschitchun]

I did try to switch to Xonfluence again on the RPi3 as its running smoothly with same source on my Amlogic S905x box. As soon as Xonfluence was loaded on the RPi3, I get the same issue like before (fastly filling up ram until RPi3 hangs with oom).
I'll provide logs!

It's ldp or skin.widget addon coming along with Xonfluence.

Yes, Maria is definitely faster than My (advantage of the second child I guess :-). It's factor 4. Complete scan took 5h. Prior it was 19h. Maybe my Mysql was screwed up somehow?

[DaveBlake]

I know we have not finished yet, but would you like to write a user best practice guide for others with huge music collections on server dbs. Things to avoid, work around etc. while the team try to fix Kodi to do better.

v17.1 will have a couple of things that could improve the memory issues you experience. I know there will be a delay while it gets into LE, but I hope you will be up for yet more retesting when it does

[Uatschitchun]

Sure