DB locks and wait times

Plex Media Server Desktops & Laptops
,
44

@Ryushin

I might have something about resolving this.

This is a sqlite3 query on a DB to see if it has the multiple-IVA record problem.

  1. Inspect
[chuck@lizum plexdb.2007]$ sqlite3 com.plexapp.plugins.library.db 
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> select count(metadata_item_id),metadata_item_id from metadata_relations group by metadata_item_id order by count(metadata_item_id) desc limit 20
   ...> ;
761863|57236
524292|56545
54|58237
51|56604
51|57096
47|59056
34|55911
33|57576
31|56926
31|57314
30|22739
30|57175
30|57590
30|57890
30|58039
28|56713
28|57832
27|318
27|56934
26|57660
sqlite>

Notice items 57236 and 56545 have the crazy counts. These are the problem movies.

  1. For each item which has the high count, query it directly
sqlite> select metadata_item_id, related_metadata_item_id, created_at from metadata_relations Where (metadata_item_id = '57236' ) order by created_at desc limit 20;
57236|1083694|2021-11-14 03:14:01
57236|1083695|2021-11-14 03:14:01
57236|1083696|2021-11-14 03:14:01
57236|1083697|2021-11-14 03:14:01
57236|1083698|2021-11-14 03:14:01
57236|1083699|2021-11-14 03:14:01
57236|1083700|2021-11-14 03:14:01
57236|1083701|2021-11-14 03:14:01
57236|1083702|2021-11-14 03:14:01
57236|1083703|2021-11-14 03:14:01
57236|1083704|2021-11-14 03:14:01
57236|1083705|2021-11-14 03:14:01
57236|1083706|2021-11-14 03:14:01
57236|1083707|2021-11-14 03:14:01
57236|1083708|2021-11-14 03:14:01
57236|1083709|2021-11-14 03:14:01
57236|1083710|2021-11-14 03:14:01
57236|1083711|2021-11-14 03:14:01
57236|1083712|2021-11-14 03:14:01
57236|1083713|2021-11-14 03:14:01
sqlite> select metadata_item_id, related_metadata_item_id, created_at from metadata_relations Where (metadata_item_id = '56545' ) order by created_at desc limit 20;
56545|1345875|2021-11-23 02:45:04
56545|1345876|2021-11-23 02:45:04
56545|1345877|2021-11-23 02:45:04
56545|1345878|2021-11-23 02:45:04
56545|1345879|2021-11-23 02:45:04
56545|1345880|2021-11-23 02:45:04
56545|1345881|2021-11-23 02:45:04
56545|1345882|2021-11-23 02:45:04
56545|1345883|2021-11-23 02:45:04
56545|1345884|2021-11-23 02:45:04
56545|1345885|2021-11-23 02:45:04
56545|1345886|2021-11-23 02:45:04
56545|1345887|2021-11-23 02:45:04
56545|1345888|2021-11-23 02:45:04
56545|1345889|2021-11-23 02:45:04
56545|1345890|2021-11-23 02:45:04
56545|1345891|2021-11-23 02:45:04
56545|1345892|2021-11-23 02:45:04
56545|1345893|2021-11-23 02:45:04
56545|1345894|2021-11-23 02:45:04
sqlite>

This confirms the records were added multiple times at during the same butler run.

If possible , try to identify which PMS version was installed at the time. We’re still working our way backwards looking for the individual change which caused it.

Step 3. Cleanup

Put the following into a file and run it using Plex SQLite (must use Plex’s SQLite tool and not stock sqlite3)

BEGIN TRANSACTION;

DELETE FROM metadata_items
WHERE guid LIKE '%api.internetvideoarchive%'
    AND id NOT IN (
        SELECT related_metadata_item_id 
        FROM metadata_relations
    )
;

DELETE FROM media_streams
WHERE media_item_id IN (
    SELECT media_streams.media_item_id 
    FROM media_streams
    INNER JOIN media_items ON media_streams.media_item_id=media_items.id
    WHERE media_items.metadata_item_id NOT IN (
        SELECT id 
        FROM metadata_items
    )
)
;

DELETE FROM media_parts 
WHERE media_item_id in (
    SELECT media_parts.media_item_id 
    FROM media_parts INNER JOIN media_items ON media_parts.media_item_id=media_items.id
    WHERE media_items.metadata_item_id NOT IN (
        SELECT id
        FROM metadata_items
    )
)
;

DELETE FROM media_items
WHERE metadata_item_id NOT IN (
    SELECT id
    FROM metadata_items
)
;

COMMIT;

VACUUM;

If you want to inject a select changes(*) to see how many records were deleted after COMMIT but prior to the VACUUM, that’s fine.

1 Like