2017-03-01, 15:04
2017-03-01, 15:16
@Rusendusen...
As an aside to this - if you want to run / test with xonfluence I've made 'fake' versions of service.library.data.provider and service.skin.widgets that literally do nothing (other than spew 'I'm not playing' messages into the log when called). Just a modified addon.xml (bumped version number) and a default.py that prints a message to log and closes. If you want to try them I'll chuck them up somewhere.
I use them with xonfluence to assuage my OCD.
As an aside to this - if you want to run / test with xonfluence I've made 'fake' versions of service.library.data.provider and service.skin.widgets that literally do nothing (other than spew 'I'm not playing' messages into the log when called). Just a modified addon.xml (bumped version number) and a default.py that prints a message to log and closes. If you want to try them I'll chuck them up somewhere.
I use them with xonfluence to assuage my OCD.
2017-03-02, 10:45
Optimizing DB
(Only 3 topics on the go in this thread!)
Is there any way to get this tuner to be more precise about what joins it thinks didn't have an index to hit? Assuming 3239 is for a session, perhaps do only set action in Kodi per session and see if we can find the action that upsets the tuner. Was that figure after a clean db attempt?
(Only 3 topics on the go in this thread!)
(2017-02-27, 22:57)Rusendusen Wrote:That is unexpected. From code I know we have indexes defined for the joins (although smart playlists can go beyond that, but that is fine), and I have checked query plans etc. on SQLite.Quote:Optimizing DB access was not because of slowness but because of MySQLTuner always suggested a higher join_buffer_ize or indexed searches ...This is what mysqltuner says:
Code:[!!] Joins performed without indexes: 3239
Adjust your join queries to always utilize indexes
join_buffer_size (> 32.0M, or always use indexes with joins)
Is there any way to get this tuner to be more precise about what joins it thinks didn't have an index to hit? Assuming 3239 is for a session, perhaps do only set action in Kodi per session and see if we can find the action that upsets the tuner. Was that figure after a clean db attempt?
2017-03-02, 13:40
(2017-03-02, 12:00)Rusendusen Wrote: @trogggyHere you go:
would be nice, although I can't get the connection to OCD
Dropbox
Extract the zip and you'll find zips of randomlastitems, skinwidgets and librarydataprovider - none of them do anything useful for me in xonfluence, may not be the case for you. If you want to uninstall them you'll probably have to do it manually (ie delete from the addons folder) as long as xonfluence is installed - alternatively switch skin, uninstall xonfluence, you can then uninstall these (as long as nothing else is depending on them), reinstall xonfluence and the real versions of these will install.
In summary - this is hacky, hacky, hacky - but should let you run xonfluence.
2017-03-02, 18:40
(2017-03-02, 10:45)DaveBlake Wrote: Optimizing DBWe'll keep it sorted
(Only 3 topics on the go in this thread!)
Quote:(2017-02-27, 22:57)Rusendusen Wrote:That is unexpected. From code I know we have indexes defined for the joins (although smart playlists can go beyond that, but that is fine), and I have checked query plans etc. on SQLite.Quote:Optimizing DB access was not because of slowness but because of MySQLTuner always suggested a higher join_buffer_ize or indexed searches ...This is what mysqltuner says:
Code:[!!] Joins performed without indexes: 3239
Adjust your join queries to always utilize indexes
join_buffer_size (> 32.0M, or always use indexes with joins)
Is there any way to get this tuner to be more precise about what joins it thinks didn't have an index to hit? Assuming 3239 is for a session, perhaps do only set action in Kodi per session and see if we can find the action that upsets the tuner. Was that figure after a clean db attempt?
I've tried to get it sorted. Setting these in my.conf
Code:
log_queries_not_using_indexes = 1
long_query_time = 60
This is the output of mysqldumpslow (only joins):
http://sprunge.us/UCGO
And complete:
http://sprunge.us/EBZL
Code:
$ ./tuning-primer.sh
SLOW QUERIES
The slow query log is enabled.
Current long_query_time = 60.000000 sec.
You have 7864 out of 158004 that take longer than 60.000000 sec. to complete
...
JOINS
Current join_buffer_size = 4.00 M
You have had 212 queries where a join could not use an index properly
join_buffer_size >= 4 M
This is not advised
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
...
TABLE SCANS
Current read_buffer_size = 2 M
Current table scan ratio = 7310 : 1
You have a high ratio of sequential access requests to SELECTs
You may benefit from raising read_buffer_size and/or improving your use of indexes
This is from today's normal usage/navigate around, DB Update and Ckeaning tries
2017-03-02, 19:53
(2017-03-01, 15:04)DaveBlake Wrote: Cleaning DBI'll send you a kodi.log of an update and cleaning after moving some albums
(2017-03-01, 14:18)Rusendusen Wrote: As the above result was 0, I could move a folder (or rename it, touch it, whatever), update DB and do a cleanup with something to clean and provide a new kodi.log and mysql.log with Estuary and no ldp & skin.helperThat sounds like a plan, go for it.
My impression is, the process
Code:
delete from artist where idArtist not in (select idArtist from tmp_delartists)
Code:
MariaDB [MyMusic60]> show full processlist;
+------+------+--------------------+-----------+---------+------+--------------+--------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+------+------+--------------------+-----------+---------+------+--------------+--------------------------------------------------------------------------------+----------+
| 4609 | kodi | 192.168.5.5:48622 | MyMusic60 | Query | 2572 | Sending data | delete from artist where idArtist not in (select idArtist from tmp_delartists) | 0.000 |
Code:
MariaDB [MyMusic60]> SELECT COUNT(1) FROM artist WHERE idArtist NOT IN (SELECT idArtist FROM tmp_delartists);
+----------+
| COUNT(1) |
+----------+
| 0 |
+----------+
1 row in set (3.75 sec)
On another client accessing the DB, I can see that my changes (had 2 folders AC/DC, one with albums tagged with picard, one with albums missing on MB, into only one by adding missing albums to MB) have been applied as there's only on artist folder left (before it were 3 within kodi cause of different tagging).
If I kill kodi, the process stays alive on DB. Mysql process is busy but I can't find out, what it is doing. Nothing in log with that process-id, but:
Code:
4609 Query delete from artist where idArtist not in (select idArtist from tmp_delartists)
It seems, the cleanup is doing its job, but the process doen't end in the end.
I'll send you the logs
2017-03-03, 08:46
Cleaning DB
Did some other changes to source und ran an update to the library, which also inititiated a clean (cleanonupdate)
As one can see, the process is still running for about 10h (36142/3600)
Without any result.
Strangely I get:
These are the last lines on this id in mysql.log:
And, the changes are not visible in Kodi. Still as if no clean was done
Did some other changes to source und ran an update to the library, which also inititiated a clean (cleanonupdate)
As one can see, the process is still running for about 10h (36142/3600)
Code:
| 7438 | kodi | 192.168.5.5:56896 | MyMusic60 | Query | 36142 | Sending data | delete from artist where idArtist not in (select idArtist from tmp_delartists) | 0.000 |
Strangely I get:
Code:
MariaDB [MyMusic60]> SELECT COUNT(1) FROM artist WHERE idArtist NOT IN (SELECT idArtist FROM tmp_delartists);
ERROR 1146 (42S02): Table 'MyMusic60.tmp_delartists' doesn't exist
Code:
MariaDB [MyMusic60]> CREATE TEMPORARY TABLE tmp_delartists (idArtist integer) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.18 sec)
MariaDB [MyMusic60]> INSERT INTO tmp_delartists select idArtist from song_artist;
Query OK, 362599 rows affected (18.71 sec)
Records: 362599 Duplicates: 0 Warnings: 0
MariaDB [MyMusic60]> INSERT INTO tmp_delartists select idArtist from album_artist;
Query OK, 8348 rows affected (0.38 sec)
Records: 8348 Duplicates: 0 Warnings: 0
MariaDB [MyMusic60]> INSERT INTO tmp_delartists VALUES(1);
Query OK, 1 row affected (0.04 sec)
MariaDB [MyMusic60]> SELECT COUNT(1) FROM artist WHERE idArtist NOT IN (SELECT idArtist FROM tmp_delartists);
+----------+
| COUNT(1) |
+----------+
| 0 |
+----------+
1 row in set (3.93 sec)
These are the last lines on this id in mysql.log:
Code:
170302 21:21:04 7438 Query select * from album where album.idAlbum not in (select idAlbum from song)
7438 Query delete from album where idAlbum in (1713,8132)
7438 Query DELETE FROM song WHERE song.idAlbum = old.idAlbum
7438 Query DELETE FROM album_artist WHERE album_artist.idAlbum = old.idAlbum
7438 Query DELETE FROM album_genre WHERE album_genre.idAlbum = old.idAlbum
7438 Query DELETE FROM albuminfosong WHERE albuminfosong.idAlbumInfo=old.idAlbum
7438 Query DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album'
7438 Query DELETE FROM song WHERE song.idAlbum = old.idAlbum
7438 Query DELETE FROM album_artist WHERE album_artist.idAlbum = old.idAlbum
7438 Query DELETE FROM album_genre WHERE album_genre.idAlbum = old.idAlbum
7438 Query DELETE FROM albuminfosong WHERE albuminfosong.idAlbumInfo=old.idAlbum
7438 Query DELETE FROM art WHERE media_id=old.idAlbum AND media_type='album'
7438 Query CREATE TEMPORARY TABLE tmp_delartists (idArtist integer) CHARACTER SET utf8 COLLATE utf8_general_ci
7438 Query INSERT INTO tmp_delartists select idArtist from song_artist
170302 21:21:21 7438 Query INSERT INTO tmp_delartists select idArtist from album_artist
170302 21:21:22 7438 Query INSERT INTO tmp_delartists VALUES(1)
7438 Query delete from artist where idArtist not in (select idArtist from tmp_delartists)
And, the changes are not visible in Kodi. Still as if no clean was done
2017-03-03, 11:42
All good stuff @Rusendusen, thank you. I think I have some ideas now about some of it at least, and that is always the first step to a solution.
You have nearly 371k of artists (it includes musicians, producers, etc.) and because after the small change none of them need to be deleted as part of the clean up all of them are in tmp_delartists. While the select query works with
"WHERE idArtist NOT IN (SELECT idArtist FROM tmp_delartists)"
when you test it (and says 0 records), delete with the same clause quietly raises a MySQL 1205 timeout error, or just hangs.
(EDITED AGAIN)
I may have a fix, confim this you could apply this SQL manually (from Workbench or whatever)
That last line should do nothing, there is nothing to delete, but it should not lock.
Both clean db from settings, and the smaller clean up at the end of library update (does not remove songs from missing sources in case they are temp off line), use the same SQL for cleaning artists. So you will hit this issue from either route.
I need to have a closer look at clean on update to see why cancel isn't working for you, but again I have ideas.
Kodi has never had so many artists, and MySQL just does not like the way it tries to clean up with that many.
You have nearly 371k of artists (it includes musicians, producers, etc.) and because after the small change none of them need to be deleted as part of the clean up all of them are in tmp_delartists. While the select query works with
"WHERE idArtist NOT IN (SELECT idArtist FROM tmp_delartists)"
when you test it (and says 0 records), delete with the same clause quietly raises a MySQL 1205 timeout error, or just hangs.
(EDITED AGAIN)
I may have a fix, confim this you could apply this SQL manually (from Workbench or whatever)
Code:
SELECT COUNT(1) as artistcount FROM artist;
CREATE TEMPORARY TABLE tmp_delartists (idArtist integer) CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO tmp_delartists select idArtist from song_artist;
INSERT INTO tmp_delartists select idArtist from album_artist;
INSERT INTO tmp_delartists VALUES(1);
SELECT COUNT(1) as artists_raw FROM tmp_delartists;
CREATE TEMPORARY TABLE tmp_keep (idArtist integer primary key) CHARACTER SET utf8 COLLATE utf8_general_ci;
INSERT INTO tmp_keep SELECT DISTINCT idArtist from tmp_delartists;
SELECT COUNT(1) as artists_keep FROM tmp_keep;
SELECT COUNT(1) as artists_remove FROM artist WHERE idArtist NOT IN (SELECT idArtist FROM tmp_keep);
DELETE FROM artist WHERE idArtist NOT IN (SELECT idArtist FROM tmp_keep);
Both clean db from settings, and the smaller clean up at the end of library update (does not remove songs from missing sources in case they are temp off line), use the same SQL for cleaning artists. So you will hit this issue from either route.
I need to have a closer look at clean on update to see why cancel isn't working for you, but again I have ideas.
Kodi has never had so many artists, and MySQL just does not like the way it tries to clean up with that many.
2017-03-03, 13:04
@Rusendusen just wondering if I could get a patched version of Kodi for you to test. With Kodi on your Linux box, can you get nightly test builds downloaded from the mirrors installed and runnng? I sold out to Windows years ago, always promising I would find time to explore Linux one day but that day has not come yet, so I am sadly ignorant. I know I can create a linux build, just not if you can use it.
EDIT:
My other thought is how big is an export from MySQL of Music60 schema and data?
EDIT2:
No worries, I have manged to get a dummy database locking on that kind of delete on a large table. I can play with that until I find a method that works.
EDIT:
My other thought is how big is an export from MySQL of Music60 schema and data?
EDIT2:
No worries, I have manged to get a dummy database locking on that kind of delete on a large table. I can play with that until I find a method that works.
2017-03-03, 17:11
I'm able to install a nightly from PPA but I also have got a build-environment up, so if you tell me what branch I should checkout, I'll be able to compile. Have also setup a VM to cross-compile latest LE Master.
Code:
select table_schema "Database",round(sum(data_length+index_length)/1024/1024,4) "Size (MB)" from information_schema.tables group by table_schema;
+--------------------+-----------+
| Database | Size (MB) |
+--------------------+-----------+
| badroom-audio60 | 17.0625 |
| fanny-video107 | 10.8750 |
| information_schema | 0.1719 |
| MyMusic60 | 152.8750 |
| mysql | 0.9163 |
| MyVideos107 | 11.3594 |
| performance_schema | 0.0000 |
+--------------------+-----------+
7 rows in set (0.12 sec)
2017-03-03, 18:30
(2017-03-03, 17:11)Rusendusen Wrote: I'm able to install a nightly from PPA but I also have got a build-environment up, so if you tell me what branch I should checkout, I'll be able to compile. Have also setup a VM to cross-compile latest LE Master.I'm impressed
We will make use of that later on.
You may note I have sneaked into my previous post and edited the SQL. No need to do the old form delete, I know it will lock. And of course you have not got 371k of artists, that is just the size of the tmp_delartists table including lots of duplicates, so I am curious how many you have got. I also may have a fix.
Cut and paste that SQL!
2017-03-03, 19:11
(2017-03-03, 18:30)DaveBlake Wrote: You may note I have sneaked into my previous post and edited the SQL. No need to do the old form delete, I know it will lock. And of course you have not got 371k of artists, that is just the size of the tmp_delartists table including lots of duplicates, so I am curious how many you have got. I also may have a fix.
Cut and paste that SQL!
First line has a typo (it is artist, not artists):
Code:
MariaDB [MyMusic60]> SELECT COUNT(1) as artistcount FROM artists;
ERROR 1146 (42S02): Table 'MyMusic60.artists' doesn't exist
And index doesn't work
Code:
MariaDB [MyMusic60]> CREATE INDEX ts_id1 ON tmp_delartists (idSong);
ERROR 1072 (42000): Key column 'idSong' doesn't exist in table
Code:
MariaDB [MyMusic60]> SELECT COUNT(1) as toremove FROM artist WHERE idArtist NOT IN (SELECT idArtist FROM tmp_delartists);
+----------+
| toremove |
+----------+
| 4 |
+----------+
1 row in set (4.03 sec)
MariaDB [MyMusic60]> DELETE FROM artist WHERE idArtist NOT IN (SELECT idArtist FROM tmp_delartists);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
2017-03-03, 19:17
Yes, I know. Editing my experiment, I messed up, sorry about that.
Just edited the post again with new version improved version for you to cut and paste
Here http://forum.kodi.tv/showthread.php?tid=...pid2540104
as it vanished up the page
Just edited the post again with new version improved version for you to cut and paste
Here http://forum.kodi.tv/showthread.php?tid=...pid2540104
as it vanished up the page
2017-03-03, 19:50
Ah nice that's fast now and working!
The lock wait timeout was because of an old delete process still running
Code:
MariaDB [MyMusic60]> SELECT COUNT(1) as artistcount FROM artist;
+-------------+
| artistcount |
+-------------+
| 30522 |
+-------------+
1 row in set (0.09 sec)
MariaDB [MyMusic60]> CREATE TEMPORARY TABLE tmp_delartists (idArtist integer) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.18 sec)
MariaDB [MyMusic60]> INSERT INTO tmp_delartists select idArtist from song_artist;
Query OK, 362537 rows affected (17.76 sec)
Records: 362537 Duplicates: 0 Warnings: 0
MariaDB [MyMusic60]> INSERT INTO tmp_delartists select idArtist from album_artist;
Query OK, 8341 rows affected (0.37 sec)
Records: 8341 Duplicates: 0 Warnings: 0
MariaDB [MyMusic60]> INSERT INTO tmp_delartists VALUES(1);
Query OK, 1 row affected (0.04 sec)
MariaDB [MyMusic60]> SELECT COUNT(1) as artists_raw FROM tmp_delartists;
+-------------+
| artists_raw |
+-------------+
| 370879 |
+-------------+
1 row in set (1.54 sec)
MariaDB [MyMusic60]> CREATE TEMPORARY TABLE tmp_keep (idArtist integer primary key) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.33 sec)
MariaDB [MyMusic60]> INSERT INTO tmp_keep SELECT DISTINCT idArtist from tmp_delartists;
Query OK, 30518 rows affected (6.70 sec)
Records: 30518 Duplicates: 0 Warnings: 0
MariaDB [MyMusic60]> SELECT COUNT(1) as artists_keep FROM tmp_keep;
+--------------+
| artists_keep |
+--------------+
| 30518 |
+--------------+
1 row in set (0.05 sec)
MariaDB [MyMusic60]> SELECT COUNT(1) as artists_remove FROM artist WHERE idArtist NOT IN (SELECT idArtist FROM tmp_keep);
+----------------+
| artists_remove |
+----------------+
| 4 |
+----------------+
1 row in set (0.45 sec)
MariaDB [MyMusic60]> DELETE FROM artist WHERE idArtist NOT IN (SELECT idArtist FROM tmp_keep);
Query OK, 4 rows affected (0.97 sec)
The lock wait timeout was because of an old delete process still running