Slow library performance due to increasingly growing database

Plex Media Server
1

Server Version#: 1.24.1.4931
Player Version#: 4.64.3

Hello,

TL;DR
My com.plexapp.plugins.library.db grew from 900MB to 10GB in 2 weeks and ended up corrupted. Plex reverted to the last backup (unfortunately 2 weeks old), but the DB is now slowly growing in size again. I believe this is the cause of the slow loading & UI I’m experiencing.

I’m sharing this incase others are experience similar issues, and also to get some help from staff to try and resolve this (or file it as a bug).

Problem
In the past few weeks my server has experienced some slowness. For example when I navigate to a TV Show in the Apple TV client it takes 5+ seconds for the screen to load. Similarly when I use the Plex Dash app on iOS it is taking much longer to load everything.

My Apple TV is plugged via ethernet and I’m on a 500Mb connection, my server is remote but has a 40Gb connection and I never experience any playback buffering (just trying to rule out the obvious reasons for you).

I had a server crash recently, I restarted the server and everything was in tact. The next morning I noticed that some already existing movies had been re-added and some viewing history was missing. I went to Library/Application Support/Plex Media Server/Plug-in Support/Databases and that is when I noticed a 10GB com.plexapp.plugins.library.db.broken file. Plex had reverted to a backup from 2 weeks prior (only 900MB). This happened around 5am which is within my server’s scheduled tasks window.

I decided after trying some of the repair steps from the Support page to not bother and just go about my day with the 2 weeks of metadata loss, no biggie. The issue is I think my DB is still tainted somehow, as if you look at the size of the com.plexapp.plugins.library.db file on my server it is growing at a weirdly rapid rate. 28/08/21 it was 909MB, 31/08/21 it was 1.83GB, 1/9/21 it is 3.65GB.

What are my options here? I’d like to avoid the nuke option of just deleting com.plexapp.plugins.library.db and letting the server build everything from scratch. Especially if this is being caused by a software bug and it’ll just reappear again.

Thanks for the help and let me know if I can provide anymore information.

2

There was a known issue a little over a year ago where invalid entries would be added to the statistics_media table, causing the database to balloon in size. It was related to the use of the guest account for playback. However, that issue was resolved as of version 1.22.1.4228. You could check too see if yours is accumulating a bunch of records with a null account_id.

Also, I’ve never heard of Plex Media Server itself restoring a backup database on its own. Are you using a VPS or service which might be doing that? Or maybe it’s just a new PMS feature if which I was unaware.

1 Like
3

Interesting, thanks for the quick reply. This might very well be the same issue! I will investigate when I get time and get back to you.

Oh right yeah could have been something done by the people who manage my server.

4

There was also a recent issue where the activities table wasn’t trimmed appropriately. But in those cases a subsequent version of PMS resolved the issue.

Which recovery steps did you already take?

Do you have a system-installed version of sqlite3 to execute this? (dbstat isn’t available in the Plex-provided Plex SQLite utility.)

sqlite3 com.plexapp.plugins.library.db "select name, sum(pgsize) as size from dbstat group by name order by size desc limit 20;"

That would indicate which tables are the largest and using the most space.

1 Like
5

Just from a quick look it seems like the culprit is the metadata_items table. It has 1 million entries, seeing a ton of duplicates and NULLs for library_section_id & parent_id :confused:

An here is the interesting part after running sqlite3_analyzer

*** Page counts for all tables with their indices *****************************
METADATA_ITEMS.................................... 1197672     33.6% 
MEDIA_STREAMS..................................... 871052      24.4% 
MEDIA_PARTS....................................... 704434      19.8% 
MEDIA_ITEMS....................................... 594848      16.7% 
METADATA_RELATIONS................................ 111651       3.1% 
FTS4_METADATA_TITLES_ICU_SEGMENTS................. 45486        1.3%
6

It’s normal for many rows in that table to have NULL library_section_id or parent_id.

That’s a bunch of rows. Do you have hundreds of thousands of items in Plex, including movies/shows/music/pictures?

Here’s something gross to try -

sqlite3 com.plexapp.plugins.library.db "select count(*), metadata_type, substr(guid,1,instr(guid,'://')+instr(substr(guid, instr(guid,'://')+3),'/')+2) from metadata_items group by metadata_type, substr(guid,1,instr(guid,'://')+instr(substr(guid, instr(guid,'://')+3),'/')+2) order by count(*);"

That will give a sense of how many items you have of each type.

3 Likes
7

Thank you! Here is the result of running that query, what the heck is iva://api.internetvideoarchive.com/ :thinking:

1|1|com.plexapp.agents.none://
1|1|tv.plex.agents.none://
2|1|local://
2|2|local://
2|3|local://
3|10|library://
4|42|com.plexapp.agents.none://
7|4|local://
32|18|plex://collection/
58|2|plex://show/
72|18|collection://
97|3|plex://season/
278|1|plex://movie/
959|4|plex://episode/
1181528|12|iva://api.internetvideoarchive.com/
8

That’s the movie trailers and extras which are included in the Plex Pass.
When setting the number of IVA assets in relation to the number of movies you have, the IVA number looks outrageous to me.

1 Like
9

You definitely want to check the health of your database file.

  1. activate debug logging (not ‘verbose’!)
  2. quit Plex Server
  3. wait 1 minute
  4. start Plex Server
  5. wait 5 minutes
  6. fetch log files and attach them here

Or inspect them yourself. Take a look at the Plex Media Server.log file and seek for messages about database corrupt or malformed.
If you find these, you may have to repair your database.

as an additional test, disable fetching of IVA assets:
In each library, which already uses the newer metadata agents, disable “Find extras” in the properties of the library, on the “Advanced” tab.
If any of your libraries is using any of the older Python-based metadata agents, go to
Settings - Server - Agents - Movies - Plex Movie (Legacy)
click on the “cog wheel” icon at the end of the line “Plex Movie (Legacy)”
wait 1 – 2 seconds for the popup and disable “Find trailers and extras automatically (Plex Pass required)”

Then “Refresh Metadata” of a few movies and verify that the extras and trailers are vanishing from their preplay pages.
If that works, Refresh Metadata of the whole library(s).

Just to be safe, look at the size of the DB file again, after performing “Optimize Database” under
Settings - Server - Manage - Troubleshooting

1 Like
10

Yeah, exactly what he :arrow_up: said.

Each show & movie can have multiple trailers and extras and such, so it’s normal to have more iva://api.internetvideoarchive.com/ entries. I think there’s a cap at 50 extras, so 1000x is an indicator of a problem.

Let us know if Find extras and Refresh ... cleans up the mess for you.

This would identify the media items with many referenced extras. I’m curious if you have any with a count() significantly over 50. If so, it’s worth starting the Refresh ... cleanup with them.

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;"

And this would identify certain obviously orphaned items:

### Get a count
sqlite3 -header com.plexapp.plugins.library.db "select count(*) from metadata_items where guid like '%api.internetvideoarchive%' and id not in (select related_metadata_item_id from metadata_relations);"
### List the items
# sqlite3 -header com.plexapp.plugins.library.db "select * from metadata_items where guid like '%api.internetvideoarchive%' and id not in (select related_metadata_item_id from metadata_relations);"

I notice that I have a few orphans myself, things where I’ve either fixed the matches or deleted shows. I think there’s a minor data cleanup bug - I’ll poke around to confirm and may open another thread for it.

2 Likes
11

so 15k might be an issue?

12

Edit: Hah, I didn’t notice you were a different person. Let me do some more investigation on my own orphaned items, and I’ll message you about it too.

That’s definitely interesting, but you’ve got a million+ rows in that table. I thought it was possible that you’d have a HUGE number of orphaned iva://api.internetvideoarchive.com entries.

What’s the other query show?

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;"
13

One of the developers has asked for server log files (activate “Debug”, but not “verbose”), covering the server startup and refreshing metadata of a few movies.

1 Like
14

Heh that was not me :stuck_out_tongue: Sorry I’m working on following these steps.

But for now here are the answers to your queries @Volts .

With 50+ extras:

count(metadata_item_id)|metadata_item_id
720918|3079
327690|2391
131076|55

Orphaned entries: 50

1 Like
15

Cool! Let’s look up what those items are, because … that’s insane! It looks like those few items have almost all of the iva://api.internetvideoarchive.com entries associated with them.

sqlite3 -header com.plexapp.plugins.library.db "select id,library_section_id,guid,title from metadata_items where id in (3079,2391,55)"
1 Like
16

:sweat_smile:

id|library_section_id|guid|title
55|1|plex://movie/5d77682754f42c001f8c290c|Easy Rider (4K)
2391|1|plex://movie/5d77704ecfbb56001fb87e34|The Report (4K)
3079|1|plex://movie/5d776b88fb0d55001f56a72c|Little Women (4K)
17

One of the developers has asked for server log files (activate “Debug”, but not “verbose”), covering the server startup and refreshing metadata of a few movies.

So I started up the server, went to “Little Women (4K)” which is the movie with 720k entries, hit “Refreshed Metadata” and it’s been stuck on “Updating Metadata” ever since. After about 10 mins I downloaded the log. This was all with the “Find extras” still enabled. Here is the Plex Media Server.log (I removed some watch events which referenced files on my server.): Plex Media Server.log (264.0 KB)

I also repeated the exact same steps above BUT with “Find extras” disabled. Here is part of the logs: Plex Media Server.log (2.8 MB)

Let me know if you absolutely need the whole .zip of the logs. I can already see in the second example (with Find extras disabled) that the logs are showing a lot of deletion happening, but it will take a while to do 720k :sweat_smile: I have enough info now to try and cleanup & optimise the DB and see if it stays good.

1 Like
18

I always knew those Little Women would be trouble!

19

Developers usually want the whole zip file, without any lines getting deleted or censored. I don’t know how useful these partial logs will be.

20

Developers usually want the whole zip file, without any lines getting deleted or censored. I don’t know how useful these partial logs will be.

Fair enough. Here are the full logs.

“Find extras” enabled. Starting up server + refreshing metadata on the offending film "Little Women (4K): Plex Media Server Logs_2021-09-02_20-39-23.zip (4.6 MB)

“Find extras” disabled. Starting up server + refreshing metadata on the offending film "Little Women (4K): Plex Media Server Logs_2021-09-02_20-59-35.zip (4.4 MB)