• 1
  • 3
  • 4
  • 5(current)
  • 6
  • 7
  • 9
Problem scanning huge source (mem leak)?
#61
Cleaning DB

(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.helper
That sounds like a plan, go for it.
Reply
#62
@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.
Reply
#63
Optimizing DB
(Only 3 topics on the go in this thread!)

(2017-02-27, 22:57)Rusendusen Wrote:
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)
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.

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?
Reply
#64
@trogggy
would be nice, although I can't get the connection to OCD Wink
Reply
#65
(2017-03-02, 12:00)Rusendusen Wrote: @trogggy
would be nice, although I can't get the connection to OCD Wink
Here you go:
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.
Reply
#66
(2017-03-02, 10:45)DaveBlake Wrote: Optimizing DB
(Only 3 topics on the go in this thread!)
We'll keep it sorted Wink
Quote:
(2017-02-27, 22:57)Rusendusen Wrote:
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)
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.

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
logs slow queries only when exceeding 60s, but do log joins without using indexes

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
Reply
#67
(2017-03-01, 15:04)DaveBlake Wrote: Cleaning DB

(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.helper
That sounds like a plan, go for it.
I'll send you a kodi.log of an update and cleaning after moving some albums

My impression is, the process
Code:
delete from artist where idArtist not in (select idArtist from tmp_delartists)
somehow never returns or gets lost or whatever...

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 |
and
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)
Once at this point in cleaning process, Kodi is inaccessible with stating to clean artists. Cancel is not working!

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)
being the last entry in mysql.log with that id.

It seems, the cleanup is doing its job, but the process doen't end in the end.

I'll send you the logs
Reply
#68
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)
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 |
Without any result.

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
Reply
#69
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)
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);
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.
Reply
#70
@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.
Reply
#71
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)
Reply
#72
(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 Cool
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!
Reply
#73
(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
Reply
#74
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 Smile

Here http://forum.kodi.tv/showthread.php?tid=...pid2540104
as it vanished up the page
Reply
#75
Ah nice that's fast now and working! Wink

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
Reply
  • 1
  • 3
  • 4
  • 5(current)
  • 6
  • 7
  • 9

Logout Mark Read Team Forum Stats Members Help
Problem scanning huge source (mem leak)?0