28 GB com.plexapp.plugins.library.db file - is this normal? Seemingly causing unresponsiveness

[MrAttoAttoAtto]

Server Version#: 1.20.1.3232

Plex was acting up by not playing anything, and when I checked it out with procmon, it was spending huge amounts of CPU and disk reading/writing to com.plexapp.plugins.library.db-wal (which is now 8GB). When that finished (took about 10 mins), I noticed the com.plexapp.plugins.library.db file was 28GB - pretty large! Same for all the backups.

My plex library isn’t that large - I only have about 800 songs and 80 shows. I think that plex stopped working (didn’t work at all as a server) when it started making the latest backup (the times sort of line up) - is this database size normal, or is there any way to fix it?

Thanks.

[TeknoJunky]

if it is truely GB’s in size, that is not normal.

my pretty huge library is only

-rw-rw-r-- 1 plex dvrapps 1.7G Aug 26 21:26 com.plexapp.plugins.library.db
-rw-rw-r-- 1 plex dvrapps  64K Aug 26 21:28 com.plexapp.plugins.library.db-shm
-rw-rw-r-- 1 plex dvrapps 5.9M Aug 26 21:28 com.plexapp.plugins.library.db-wal

[MrAttoAttoAtto]

Indeed - I thought so:

[pshanew]

Have a look at the statistics_media table in your database and see if there are many relatively recent writes with a null account_id column. If so, you may be able to mitigate the issue by deleting those entries. See this thread for (much) more detail:

Plex Database sqlite file has more than doubled in 10 days

I experienced this issue several months ago. I noticed it because database optimization on my system went from completing in 4 - 5 seconds to several minutes. This prompted me to check my database size and it had ballooned by an order of magnitude. I used sqldiff to compare the current DB with a normal-sized backup and found that the table statistics_media was being written to constantly, even with no server usage. Examining the database further, I found that these “rogue” additions were bei…

This tool may be useful:
https://sqlitebrowser.org

Make sure to stop Plex Media Server before making any modifications to your database. And be sure to make a copy first.

[MrAttoAttoAtto]

This sounds right - trying a COUNT(*) on the table is completely stalled - could you give me the SQL you used to delete the rogue entries? I assume it’s something like:

DELETE FROM statistics_media WHERE account_id IS NULL

But I’d rather be sure since I cannot actually check the DB (I have backups, but I estimate this operation will take a long time, so I’d rather not have to redo it).

Thanks!

[pshanew]

That SQL statement will work, it’s what the user in the other thread I linked used (see the OP’s last post). When I had the issue I used DB Browser to sort the data by account_id and deleted all rows where it was NULL. My database hadn’t grown nearly as large as yours has, so I don’t know if that’s feasible in your case.

I’d recommend following a procedure similar to what the user in the thread I linked used. It’s basically the database repair procedure with the DELETE statement inserted before the integrity check.

[JCHH]

excuse my ignorance, but whilst that will reduce the db size, it won’t stop the problem from continuing to happen - or has it already stopped?

[OttoKerner]

According to a developer, a relevant bug was already fixed months ago. So let’s hope this has been growing for quite a while and the affected users only noticed it now, while their server versions already had the bug fix.

There has been another related issue for users who were using the 3rd-party Python Plex API. AFAIR it needed to be updated as well.

[pshanew]

@JCHH

excuse my ignorance, but whilst that will reduce the db size, it won’t stop the problem from continuing to happen - or has it already stopped?

Yes, I probably should have expanded on that a bit more; however, it was discussed in the thread I linked. When this occurred on my system, the issue didn’t persist through restarting the server and signing out all of my known client devices. I also cleaned up my authorized devices list in the process.

[MrAttoAttoAtto]

Great success! That took many hours as I estimated (around 70% of that time was writing to the WAL - I could probably have done without that), but it worked. A real slimming…

[danielg1976]

I’m the user from the other linked thread that posted about something similar. I was lucky because something else unrelated caused me to take a look at my database folder - I was wondering why the optimize took so long when usually it was so fast. I thought it was maybe my 50K photos, but I went and checked anyway. Glad to know I wasn’t alone in this.

Also here’s to hoping the problem was fixed. I have a nightly backup cron that syncs my library to a mirror drive. I added a step to that script that logs my database file size, so I can now go look at the size of it over time in the log file.

It looks like my DB may have added 22 MB in the last 3 days, so I think it might have started up again. I’ll look at it more closely this weekend and continue my thread if I’m seeing more of the same.