[jbinkley60]

I've been looking at the performance of the cleaner function and have found the bottleneck.  When a record gets cleaned it writes the information to the cleaner logging facility so you can see what is happening and have a log of it.  However, I am writing each record and committing each one at a time when doing deletes.  Thus it has to open the logging database, write the log record, commit the change and close the logging database on every log write.  On my test system this takes .02 - .03 seconds per log entry thus limiting it to around 33-50 records per second (i.e. deletions).  This type of logging is fine for general logging items like status messages and such which aren't in bulk like cleaning.

I am going to add a second logger to be used only by the cleaner function.  It will open the database at the beginning of the clean, write all the records, commit them when done cleaning (or maybe commit after a larger number of writes maybe something like 500 or 1000) and then close the database.  This should really speed things up and move any bottleneck back to MySQL.    With this approach a progress display may no longer be required.


Stay tuned,

Jeff

[HeresJohnny]

Found a bug when cleaning the files table.

It seems when cleaning the file table, it removes the watched states associated with the files, even though they are still named the same and in the same directory.

I have a ton of files that do not fall under movies or tv shows and are just sitting on shares.

I go to the videos section in files mode and watch them from there. Kodi saves the resume position and the watched status in the files tables and cleaning it seems to remove those.

It has erased watched statuses that date back to at least 2010. 

It's easily reproduceable as all you have to do is play a file that is not in your library and you will see it has been watched if your skin has the indicators enabled and if you return to that file after cleaning the files table, you will see watched indicator is gone.

Aha! I hadn't noticed it because I'm using the watchedlist add-on. That way, the watched status is never lost since it's synced back and forth between clients and a dedicated database.

[jbinkley60]

I have uploaded version 0.0.99l of the Kodi Selective Cleaner.  This version has the following updates:
 

• Adds 2 settings to allow overriding the Default Kodi database name for case sensitive issues and folks not wanting to change ther advancedsettings.xml file.  See the Wiki page for more details. 
  
• Significant improvement in cleaning performance
  
• Added total counts of orphaned / clean records and data integrity mismatches to the video analyzer output
  
• Added option for strict vs. normal field table cleaning.  (option is in the settings but currently runs in strict mode regardless of setting until we agree on normal cleaning)
  

*** Warning:  Cleaning the file table in strict mode will remove file table entries which don't have movie, episode or music table entries.  Please run Analyze Table before cleaning to ensure you want to clean the table.

For the cleaning performance I tested with cleaning 1,100 and 10,500 test records on a MySQL test instance for the actor_link table.  Here are the before and after results:

mysql old 1,100 records     29.72s
mysql new 1,100 records        .6s
mysql new 10,500 records   6.54s

This is an improvement of 37 deletions per second to 1,605 deletions per second.

Please let me know if you see any issues.  Once we agree on normal file cleaning I will post the new code and hopefully it will become the 1.0 production release of the cleaner.


Thanks,

Jeff

[jbinkley60]

Aha! I hadn't noticed it because I'm using the watchedlist add-on. That way, the watched status is never lost since it's synced back and forth between clients and a dedicated database.

Actually it does more than remove the watched status.  It removes the entire file table entry, which might make the files unplayable depending upon  what you are playing them with and whether a file table entry is required.  Kodi creates file table entries on on the fly, even for URL based and other similar content.  You see it as the bookmark removal impact because the Kodi video database has an SQL trigger called delete_file which removes the bookmark when the underlying file is removed. 

What we need to agree on is what we want normal file table cleaning to be vs. what I am calling strict mode, which is how it operates today.

Thanks,

Jeff

[HeresJohnny]

I think some devs should chime in at this point but I don't know who would be the database guy(s).

[HeresJohnny]

I played around with it some more. First, it's very fast now (KSC 0.9i).

Second, something is not right. Cleaned episode, season and tv show tables. No mismatches left. Ran database update. Season and tv show mismatches are back.

[jbinkley60]

I played around with it some more. First, it's very fast now (KSC 0.9i).

Second, something is not right. Cleaned episode, season and tv show tables. No mismatches left. Ran database update. Season and tv show mismatches are back.

What do you mean you ran a database update ?  Can you be more specific ?    Can you provide the mismatch specifics and are they blue or white ?  White is a mismatched data whereas blue are orphaned records.   I looked through the analyzer code for those three tables and everything looks right.  0.0.99l didn't change any analyzer query or cleaning code.  I am glad to hear you are seeing the performance improvements.


Thanks,

Jeff

[HeresJohnny]

I ran "Update library". The mismatches are blue. After cleaning they're gone... but returned after "update library"... unfortunately, thousands...
I could give you teamviewer access for a look if that helps.

[jbinkley60]

I ran "Update library". The mismatches are blue. After cleaning they're gone... but returned after "update library"... unfortunately, thousands...
I could give you teamviewer access for a look if that helps.

How about if you export the logs out of the addon into CSV format and send them to me ?   First clean the tables you want, run the update library and then export the logs and send to me.   Run the analyze table again and let me know a few of the records which reappear.   Did this happen with prior versions of the cleaner ?  This next question is going to sound really dumb but where do I find the Update Library menu item in Kodi ?  I don't see that option anywhere.  I see update on startup.  Is that part of the Library Auto Update addon ?  

If this doesn't work we can consider a Team Viewer option.


Thanks,

Jeff

[HeresJohnny]

I'll prepare some exports tomorrow. "Update library" is in the left-hand blade menu under tv shows or movies.

[jbinkley60]

I'll prepare some exports tomorrow. "Update library" is in the left-hand blade menu under tv shows or movies.

Ok.  I found the Kodi Update Library feature.  It isn't something I've ever used.   I tested cleaning the episode table with the copy of your database and version 0.0.99l.   I cleaned the table (which removed 35 unmatched records), did an analyze table (there were none), ran Update Library against TV Shows and then ran Analyze Table again.  There were still no unmatched records.  This might be something where I get a newer snapshot of your database and the steps to recreate the issue.  One minor enhancement I see I need to do is write a log message with the cleaning summary.  Right now it just provides the popup dialog box and the detail logging of the cleaning but not a cleaning log of the summary (i.e. X records in table Y cleaned)..  I'll add that in the next update.


Thanks,

Jeff

[jbinkley60]

I have uploaded version 0.0.9m of the Kodi Selective Cleaner.  This version has the following updates:
 

• Adds setting to select strict mode vs. normal file table cleaning.  Default is normal.   See the Wiki page for more details. 
  
• Adds summary log entry when cleaning is finished
  

Thanks,

Jeff

[Pr.Sinister]

Found a bug when cleaning the files table.

It seems when cleaning the file table, it removes the watched states associated with the files, even though they are still named the same and in the same directory.

I have a ton of files that do not fall under movies or tv shows and are just sitting on shares.

I go to the videos section in files mode and watch them from there. Kodi saves the resume position and the watched status in the files tables and cleaning it seems to remove those.

It has erased watched statuses that date back to at least 2010. 

It's easily reproduceable as all you have to do is play a file that is not in your library and you will see it has been watched if your skin has the indicators enabled and if you return to that file after cleaning the files table, you will see watched indicator is gone.

Aha! I hadn't noticed it because I'm using the watchedlist add-on. That way, the watched status is never lost since it's synced back and forth between clients and a dedicated database.

I use the watchedlist addon but it doesn't save the watched status of things not in the library. Only for actual Movies and TV Shows

[Pr.Sinister]

Found a bug when cleaning the files table.

It seems when cleaning the file table, it removes the watched states associated with the files, even though they are still named the same and in the same directory.

I have a ton of files that do not fall under movies or tv shows and are just sitting on shares.

I go to the videos section in files mode and watch them from there. Kodi saves the resume position and the watched status in the files tables and cleaning it seems to remove those.

It has erased watched statuses that date back to at least 2010. 

It's easily reproduceable as all you have to do is play a file that is not in your library and you will see it has been watched if your skin has the indicators enabled and if you return to that file after cleaning the files table, you will see watched indicator is gone.

Good catch.  You are correct that right now the cleaner compares the files table to movies, episodes and musicvideos looking for a match.   We have a couple of options:
 

• Switch to only comparing if there is a path table entry that aligns to the idPath value in the files table.  This would eliminate the issue but could leave orphaned file table entries for movies, musicvideos and music.
  
• Add checking the path table to the current query but agree to a path table value to be the key. 
  

For the second option, for the files you have which aren't aligned to the current query (i.e. not a movie, episode or music video) do the corresponding path table fields for strScraper, strContent and idParentPath have a value in them ?   Those seem to be the most likely fields to match on for excluding cleaning. 


Thanks,

Jeff

Honestly, for the files and path tables, the only way to do it properly is to compare the paths with what is defined in the sources.xml.

If they don't exist, then you remove the entry.

I see a ton of paths that no longer exist in the path table so it would be nice to clean that.

For now, i will just refrain from cleaning the files table so i don't lose my watched states and resume points on my home videos and adult media

[jbinkley60]

Found a bug when cleaning the files table.

It seems when cleaning the file table, it removes the watched states associated with the files, even though they are still named the same and in the same directory.

I have a ton of files that do not fall under movies or tv shows and are just sitting on shares.

I go to the videos section in files mode and watch them from there. Kodi saves the resume position and the watched status in the files tables and cleaning it seems to remove those.

It has erased watched statuses that date back to at least 2010. 

It's easily reproduceable as all you have to do is play a file that is not in your library and you will see it has been watched if your skin has the indicators enabled and if you return to that file after cleaning the files table, you will see watched indicator is gone.

Good catch.  You are correct that right now the cleaner compares the files table to movies, episodes and musicvideos looking for a match.   We have a couple of options:
 

• Switch to only comparing if there is a path table entry that aligns to the idPath value in the files table.  This would eliminate the issue but could leave orphaned file table entries for movies, musicvideos and music.
  
• Add checking the path table to the current query but agree to a path table value to be the key. 
  

For the second option, for the files you have which aren't aligned to the current query (i.e. not a movie, episode or music video) do the corresponding path table fields for strScraper, strContent and idParentPath have a value in them ?   Those seem to be the most likely fields to match on for excluding cleaning. 


Thanks,

Jeff

Honestly, for the files and path tables, the only way to do it properly is to compare the paths with what is defined in the sources.xml.

If they don't exist, then you remove the entry.

I see a ton of paths that no longer exist in the path table so it would be nice to clean that.

For now, i will just refrain from cleaning the files table so i don't lose my watched states and resume points on my home videos and adult media

The new option of not using file table strict checking should take care of things for you.  The challenge with Kodi is that is creates a file table entry for anything which gets played, even if an Internet download.  There's no easy matching criteria I can find for normal Kodi usage.  It is easy with Mezzmo because all files, even trailers, are Mezzmo URLs.  I can easily match on them, which is what I do each day with the Kodi database rebuilds.  it also takes care of watch statuses and play counters automatically since they are sync'd from Mezzmo.  . 

If you've downloaded the latest version of the cleaner you should notice that it is much faster now.


Thanks,

Jeff