[DaveBlake]

Yippee!

It is pretty obvious how inefficient the original SQL was now - trying to do an "IN" on 371k of records rather than just 30k of actual artists with an index. I need to test out on SQLite, make sure I don't fix one issue only to cause a problem elswhere. Also need to test when it has lots to delete, compared to almost nothing. But moving in the right direction.

I can set a jenkins build of a patch ( to 17.1 RC1+ other merged changes) going when I am happy, see then what you can do with that. It is too late in the v17 cycle to start messing with the background thread/cancel side of things, but I will look at that for v18

[Uatschitchun]

yesss ...

Latest Master branch is v18 already so DB migration ;(
What branch should I build against to stay on MyMusic6ß?

I did some more changes to source and your new way of cleanup is fast as hell in comparison!

[DaveBlake]

OK the cleanup SQL fix is in the Millhouse #303 build http://forum.kodi.tv/showthread.php?tid=...pid2540752

Yes this LE build is based on v18, but music db migration is pretty minor so far - no need for forced updates, just copies and adds a few tables. So as long as you have space for another 150MB on your server all will be well. Break out yet another SD card and give it a go. I also want to see what that build does for the memory leak issue when LDP is running.

My attempt at a test build based on Krypton got aborted, but I have restarted, so with luck should have a link for you later today.

Otherwise if you fancy trying to build for yourself then there is a branch in my repo here https://github.com/DaveTBlake/xbmc/tree/CleanSQLKrypton, based on 17.1 master (e.g. almost RC2 )

[Uatschitchun]

OK the cleanup SQL fix is in the Millhouse #303 build http://forum.kodi.tv/showthread.php?tid=...pid2540752

Read it, will test

Yes this LE build is based on v18, but music db migration is pretty minor so far - no need for forced updates, just copies and adds a few tables.

Ok, good to know

Otherwise if you fancy trying to build for yourself then there is a branch in my repo here https://github.com/DaveTBlake/xbmc/tree/CleanSQLKrypton, based on 17.1 master (e.g. almost RC2 )

Checking it out atm, so no link needed

Nice and easy fix for that one, hard to fiddle

[Uatschitchun]

Code:

MariaDB [MyMusic60]> SELECT COUNT(1) as artistcount FROM song_artist;
+-------------+
| artistcount |
+-------------+
|      362664 |
+-------------+
1 row in set (3.94 sec)

MariaDB [MyMusic60]> SELECT COUNT(1) FROM song;
+----------+
| COUNT(1) |
+----------+
|   126187 |
+----------+
1 row in set (0.38 sec)

3 People per song, nice to know... I get to it

[DaveBlake]

Yes artist relationshp to song is many to many, resolved by song_artist.

Well so far my attempts to get a test build from jenkins have been a bust. First it gets aborted, then it has a random fails (that happens), then it succeeds but I forgot to tick to upload, then the servers are busy, then I loose internet in storms....

Shish! If the servers are quiet later today I'll have another go. But if you get your own build to test before then that is great practice

[Uatschitchun]

Build from your branch was successfull and is about to being tested. I even found out how to build deb's alongside

[DaveBlake]

Build from your branch was successfull and is about to being tested. I even found out how to build deb's alongside

Great

I will give up my fight with jenkins, the servers are pretty busy today anyway.

I await your test reports with great interest. I want to be able to say that the cleanup fix has been throughly tested before merge into Krypton, and the release deadline is approaching fast.

[Uatschitchun]

Ah ... your CeanSQLKrypton branch is still on bootstrap/configure... wondering why cmake is complaining

Code:

xbmc/kodi-build$ cmake .. -DCPACK_GENERATOR=DEB
CMake Error: The source directory "/home/uatschitchun/Kodi/xbmc" does not appear to contain CMakeLists.txt.

The succeeded build was against your HEAD, which is cmake already

[DaveBlake]

Meanwhile I have been looking at the join/index optimisation question.

It turns out that MySQL/MariaDB has a fundamental limitation (IMO huge flaw) in that it does not apply indexes on correlated subqueries. The schema has indexes defined, but in many queries MySQL just doesn't use them, hence the MySQLTuner results you got.

MySQL is also much slower than SQLite (on the same size db run on the same machine). I tested MySQL 5.7 on Win10, and I was finding SQLite to be 7 times faster. I guess MariaDB could be different, but it has a lot of ground to make up.

Before vanishing down the rabbit hole of pursuing query optimisation (by redeisgning where possible to avoid MySQL defficiencies) we need to remember that it is not the DB that takes the most time in the slowest Kodi operations. But having said that I have learned things from this examination that I will certainly keep in mind when moving forward with Kodi design.

Thanks for raising this and inspiring me to dig a little deeper. How much I can do about it I am not so sure.

[Uatschitchun]

[26 Mar 2012 19:37] Paul Dubois
Noted in 5.6.5 changelog.

Several subquery performance issues were resolved through the
implementation of semi-join subquery optimization strategies.

from:
http://bugs.mysql.com/bug.php?id=9021

Would it help using joins instead of subqueries?
http://mariadb.com/kb/en/mariadb/non-sem...mizations/

[Uatschitchun]

CleanSQLKrypton branch success

Code:

2003 Query     drop table songpaths
                 2003 Query     CREATE TEMPORARY TABLE tmp_delartists (idArtist integer) CHARACTER SET utf8 COLLATE utf8_general_ci
170305 16:27:40  2003 Query     INSERT INTO tmp_delartists select idArtist from song_artist
170305 16:27:56  2003 Query     INSERT INTO tmp_delartists select idArtist from album_artist
170305 16:27:57  2003 Query     INSERT INTO tmp_delartists VALUES(1)
                 2003 Query     CREATE TEMPORARY TABLE tmp_keep (idArtist INTEGER PRIMARY KEY auto_increment ) CHARACTER SET utf8 COLLATE utf8_general_ci
                 2003 Query     INSERT INTO tmp_keep SELECT DISTINCT idArtist from tmp_delartists
170305 16:28:04  2003 Query     DELETE FROM artist WHERE idArtist NOT IN (SELECT idArtist FROM tmp_keep)
                 2003 Query     DELETE FROM album_artist WHERE album_artist.idArtist = old.idArtist
                 2003 Query     DELETE FROM song_artist WHERE song_artist.idArtist = old.idArtist
                 2003 Query     DELETE FROM discography WHERE discography.idArtist = old.idArtist
                 2003 Query     DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist'
                 2003 Query     DELETE FROM album_artist WHERE album_artist.idArtist = old.idArtist
                 2003 Query     DELETE FROM song_artist WHERE song_artist.idArtist = old.idArtist
                 2003 Query     DELETE FROM discography WHERE discography.idArtist = old.idArtist
                 2003 Query     DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist'
170305 16:28:05  2003 Query     DELETE FROM album_artist WHERE album_artist.idArtist = old.idArtist
                 2003 Query     DELETE FROM song_artist WHERE song_artist.idArtist = old.idArtist
                 2003 Query     DELETE FROM discography WHERE discography.idArtist = old.idArtist
                 2003 Query     DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist'
                 2003 Query     DELETE FROM album_artist WHERE album_artist.idArtist = old.idArtist
                 2003 Query     DELETE FROM song_artist WHERE song_artist.idArtist = old.idArtist
                 2003 Query     DELETE FROM discography WHERE discography.idArtist = old.idArtist
                 2003 Query     DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist'
                 2003 Query     DELETE FROM album_artist WHERE album_artist.idArtist = old.idArtist
                 2003 Query     DELETE FROM song_artist WHERE song_artist.idArtist = old.idArtist
                 2003 Query     DELETE FROM discography WHERE discography.idArtist = old.idArtist
                 2003 Query     DELETE FROM art WHERE media_id=old.idArtist AND media_type='artist'
                 2003 Query     DROP TABLE tmp_delartists
                 2003 Query     DROP TABLE tmp_keep
                 2003 Query     delete from genre where idGenre not in (select idGenre from song_genre) and idGenre not in (select idGenre from album_genre)
                 2003 Query     DELETE FROM role WHERE idRole > 1 AND idRole NOT IN (SELECT idRole FROM song_artist)
                 2003 Query     commit
                 2003 Query     set autocommit=1
                 2003 Query     select count(idSong) as NumSongs from songview
170305 16:28:07  2003 Quit

[DaveBlake]

Would it help using joins instead of subqueries?

I am aware of that suggested work around, but that is not always possible, and not always faster...

At the end of the page you linked someone comments:
The only option you have using MySQL is to redefine your SQL statement using JOIN but this is not always possible or causes a huge modification effort.

Another option (and possibly the best ;-) is to change to a different database provider.

I agree with that sentiment, it really is pants.
As I said I will keep it in mind for future work, but I'm not rushing off chasing my tail to "fix" Kodi for MySQL. It is not just a quick tweak in the way that clean up was.

Instead there may be some buffer settings that could help matters. I'll do some more research on that, but at the moment the wind is gusting over 55mph and my internet connection keeps cutting out (rural location, long cable runs over many poles flapping in the breeze).

[DaveBlake]

CleanSQLKrypton branch success

Wonderful, I will look to getting that fix merged, thanks for all your efforts I do appreciate it.

How about the memory leak with Library Data Provider running?

[Uatschitchun]

How about the memory leak with Library Data Provider running?

Not looking that good ;(

This is Milhouse running since yesterday evening.
LDP & skin.widget together with Xonfluence (tricked it into with adapting xbmc.gui version from required 5.12 to 5.13).
This was running over night and produced 9 crashlogs and at minimum 1 reboot

Code:

# grep -C6 "Thread debugging using libthread_db enabled" kodi_crash*

kodi_crash.log:[Thread debugging using libthread_db enabled]
kodi_crash.log-Using host libthread_db library "/usr/lib/libthread_db.so.1".
kodi_crash.log-Core was generated by `/usr/lib/kodi/kodi.bin --standalone -fs --lircdev /run/lirc/lircd'.
kodi_crash.log-Program terminated with signal SIGSEGV, Segmentation fault.
kodi_crash.log-#0  0x76f98b64 in memcmp () from /usr/lib/libarmmem.so
kodi_crash.log-[Current thread is 1 (Thread 0x74f2a000 (LWP 2304))]
kodi_crash.log-
--

kodi_crashlog_20170305224525.log:[Thread debugging using libthread_db enabled]
kodi_crashlog_20170305224525.log-Using host libthread_db library "/usr/lib/libthread_db.so.1".
kodi_crashlog_20170305224525.log-Core was generated by `/usr/lib/kodi/kodi.bin --standalone -fs --lircdev /run/lirc/lircd'.
kodi_crashlog_20170305224525.log-Program terminated with signal SIGSEGV, Segmentation fault.
kodi_crashlog_20170305224525.log-#0  0x008daf48 in CMusicDatabase::SetMusicTagScanVersion(int) ()
kodi_crashlog_20170305224525.log-[Current thread is 1 (Thread 0x74f10000 (LWP 453))]
kodi_crashlog_20170305224525.log-
--

kodi_crashlog_20170306002511.log:[Thread debugging using libthread_db enabled]
kodi_crashlog_20170306002511.log-Using host libthread_db library "/usr/lib/libthread_db.so.1".
kodi_crashlog_20170306002511.log-Core was generated by `/usr/lib/kodi/kodi.bin --standalone -fs --lircdev /run/lirc/lircd'.
kodi_crashlog_20170306002511.log-Program terminated with signal SIGABRT, Aborted.
kodi_crashlog_20170306002511.log-#0  __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:58
kodi_crashlog_20170306002511.log-[Current thread is 1 (Thread 0x74ecf000 (LWP 629))]
kodi_crashlog_20170306002511.log-
--

kodi_crashlog_20170306004312.log:[Thread debugging using libthread_db enabled]
kodi_crashlog_20170306004312.log-Using host libthread_db library "/usr/lib/libthread_db.so.1".
kodi_crashlog_20170306004312.log-Core was generated by `/usr/lib/kodi/kodi.bin --standalone -fs --lircdev /run/lirc/lircd'.
kodi_crashlog_20170306004312.log-Program terminated with signal SIGSEGV, Segmentation fault.
kodi_crashlog_20170306004312.log-#0  malloc_consolidate (av=av@entry=0x73c00010) at malloc.c:4204
kodi_crashlog_20170306004312.log-[Current thread is 1 (Thread 0x512ff3a0 (LWP 1016))]
kodi_crashlog_20170306004312.log-
--

kodi_crashlog_20170306021431.log:[Thread debugging using libthread_db enabled]
kodi_crashlog_20170306021431.log-Using host libthread_db library "/usr/lib/libthread_db.so.1".
kodi_crashlog_20170306021431.log-Core was generated by `/usr/lib/kodi/kodi.bin --standalone -fs --lircdev /run/lirc/lircd'.
kodi_crashlog_20170306021431.log-Program terminated with signal SIGSEGV, Segmentation fault.
kodi_crashlog_20170306021431.log-#0  0x698ac684 in ?? ()
kodi_crashlog_20170306021431.log-[Current thread is 1 (Thread 0x66dff3a0 (LWP 1122))]
kodi_crashlog_20170306021431.log-
--

kodi_crashlog_20170306022337.log:[Thread debugging using libthread_db enabled]
kodi_crashlog_20170306022337.log-Using host libthread_db library "/usr/lib/libthread_db.so.1".
kodi_crashlog_20170306022337.log-Core was generated by `/usr/lib/kodi/kodi.bin --standalone -fs --lircdev /run/lirc/lircd'.
kodi_crashlog_20170306022337.log-Program terminated with signal SIGSEGV, Segmentation fault.
kodi_crashlog_20170306022337.log-#0  0x76f7cb64 in memcmp () from /usr/lib/libarmmem.so
kodi_crashlog_20170306022337.log-[Current thread is 1 (Thread 0x74f0e000 (LWP 1334))]
kodi_crashlog_20170306022337.log-
--

kodi_crashlog_20170306034739.log:[Thread debugging using libthread_db enabled]
kodi_crashlog_20170306034739.log-Using host libthread_db library "/usr/lib/libthread_db.so.1".
kodi_crashlog_20170306034739.log-Core was generated by `/usr/lib/kodi/kodi.bin --standalone -fs --lircdev /run/lirc/lircd'.
kodi_crashlog_20170306034739.log-Program terminated with signal SIGSEGV, Segmentation fault.
kodi_crashlog_20170306034739.log-#0  0x76ee8b64 in memcmp () from /usr/lib/libarmmem.so
kodi_crashlog_20170306034739.log-[Current thread is 1 (Thread 0x74e7a000 (LWP 1494))]
kodi_crashlog_20170306034739.log-
--

kodi_crashlog_20170306043401.log:[Thread debugging using libthread_db enabled]
kodi_crashlog_20170306043401.log-Using host libthread_db library "/usr/lib/libthread_db.so.1".
kodi_crashlog_20170306043401.log-Core was generated by `/usr/lib/kodi/kodi.bin --standalone -fs --lircdev /run/lirc/lircd'.
kodi_crashlog_20170306043401.log-Program terminated with signal SIGSEGV, Segmentation fault.
kodi_crashlog_20170306043401.log-#0  0x76f4bb64 in memcmp () from /usr/lib/libarmmem.so
kodi_crashlog_20170306043401.log-[Current thread is 1 (Thread 0x74edd000 (LWP 1733))]
kodi_crashlog_20170306043401.log-
--

kodi_crashlog_20170306073316.log:[Thread debugging using libthread_db enabled]
kodi_crashlog_20170306073316.log-Using host libthread_db library "/usr/lib/libthread_db.so.1".
kodi_crashlog_20170306073316.log-Core was generated by `/usr/lib/kodi/kodi.bin --standalone -fs --lircdev /run/lirc/lircd'.
kodi_crashlog_20170306073316.log-Program terminated with signal SIGSEGV, Segmentation fault.
kodi_crashlog_20170306073316.log-#0  0x76f3cb64 in memcmp () from /usr/lib/libarmmem.so
kodi_crashlog_20170306073316.log-[Current thread is 1 (Thread 0x74ece000 (LWP 1931))]
kodi_crashlog_20170306073316.log-
--

kodi_crashlog_20170306073949.log:[Thread debugging using libthread_db enabled]
kodi_crashlog_20170306073949.log-Using host libthread_db library "/usr/lib/libthread_db.so.1".
kodi_crashlog_20170306073949.log-Core was generated by `/usr/lib/kodi/kodi.bin --standalone -fs --lircdev /run/lirc/lircd'.
kodi_crashlog_20170306073949.log-Program terminated with signal SIGSEGV, Segmentation fault.
kodi_crashlog_20170306073949.log-#0  0x76f98b64 in memcmp () from /usr/lib/libarmmem.so
kodi_crashlog_20170306073949.log-[Current thread is 1 (Thread 0x74f2a000 (LWP 2304))]
kodi_crashlog_20170306073949.log-

Only difference seems to be that memory isn't filling up that fast as you can see from the timestamps.