Slow Query time - unable to scan or play media

Plex Media Server
37

To update anyone else having a slow DB issue and/or a large DB filesize, the search + fix was a combination of two items. Most of the info below is above, but this hopefully summarizes it:

  1. Identifying an item in the DB that had over 2 million associated files

  2. Editing the DB to remove all those entries

Step 1 was given by @Volts using this command:

sqlite3 -header com.plexapp.plugins.library.db “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;”
count(metadata_item_id)|metadata_item_id

My output was as follows:

sqlite3 -header com.plexapp.plugins.library.db “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;”
count(metadata_item_id)|metadata_item_id
2097155|519628
50|2862
50|6051
50|8727
.....

ID 519628 had over 2 million items associated with it.

Step 2 was found in this link:
https://forums.plex.tv/t/slow-library-performance-due-to-increasingly-growing-database/740511/28

Based on that link, the super helpful @volts gave me the following details which I put in a file called sql.txt.

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;

I then stopped Plex, backed up my DB file then ran this against it:

/usr/lib/plexmediaserver/Plex\ Media\ Server --sqlite /var/lib/plexmediaserver/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases/com.plexapp.plugins.library.db < sql.txt

About 30 minutes later it was all done and I then had a 590Mb DB vs. 6.6Gb! I haven’t had an issue since and it’s been much zippier scrolling around.

Thanks again to @Volts and @ChuckPa for your time and assistance in this matter, and I hope it helps others who have a similar experience.

1 Like