That’s right, it’s a lookup/normalization table. Normalization is a basic strategy in database design. Metadata_type is used by other tables throughout the database. Other tables just store “1” for metadata_type=video because it can be looked up in that table. Saves space, makes updates and translations easier, etc.
I am considering trying to remove all the entries from STATISTICS_MEDIA table with account_id null. I did a test delete on a copy of the DB and it reduced it down to a few thousand rows (from 17M). Total DB size after VACUUM was then 200 MB instead of 1.8TB.
If I do that, do I need to delete some of the indices? Or maybe just do the database repair procedure (sql dump/restore)?
1 Like
Shouldn’t have to do any of that. Indexes should update automatically as you remove entries.
It might have been faster to dump/restore than to VACUUM, but probably not “better”.
1 Like
When I went through the process myself, I didn’t need to delete indexes. I’d recommend performing a manual database optimization through the interface afterward as well.
Thanks for all the advice. Great working with you all.
So here’s what I did:
#> systemctl stop plexmediaserver
#> cp com.plexapp.plugins.library.db com.plexapp.plugins.library.db.backup
#> sqlite3 com.plexapp.plugins.library.db
SQLite version 3.7.17 2013-05-20 00:56:22
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> DROP index 'INDEX_TITLE_SORT_NATURALSORT';
sqlite> DELETE from schema_migrations where version='20180501000000';
sqlite> DELETE FROM STATISTICS_MEDIA WHERE account_id IS NULL;
sqlite> PRAGMA integrity_check;
ok
sqlite> .quit
#> sqlite3 com.plexapp.plugins.library.db .dump > dump.sql
#> rm com.plexapp.plugins.library.db
#> sqlite3 com.plexapp.plugins.library.db < dump.sql
#> ls -l com.plexapp.plugins.library.db
-rw-r--r--. 1 root root 114881536 Aug 22 17:59 com.plexapp.plugins.library.db
#> chown plex:plex com.plexapp.plugins.library.db
#> systemctl start plexmediaserver
<Waited a few minutes>
#> systemctl status plexmediaserver
<Also browsed around media collection to ensure stuff looked good>
#> rm dump.sql
<Ran an optimize from Manage --> Troubleshooting>
Final DB Size: 116 MB
I will keep watching for feedback from the developer Otto. Quite curious to see what is found. I’ll also keep watching the DB size and see if it starts ballooning again.
So just to be clear, if it DOESN’T start ballooning again - What I have changed is
- deleted all those rows from the statistics_media table
- rebuild the DB
- upgrade to the latest server version
- turn off some of the scheduled tasks (unchecked ‘perform extensive media analysis…’ and ‘perform refresh of program guide data’).
I haven’t changed my clients or turned off DLNA. I’ll see where this goes first.
1 Like
Looks like my DB is growing again. It’s added 22 MB in the last 3 days. I’ll look at it more this weekend and post what I find.
I’m going to hold off on doing what @pshanew did (cleanup his clients) so I can be a guinea pig to help find any other lingering bugs, so I’m trying to keep my usage patterns the same for now in case there’s still a server issue and I can help get to the root of it.
Yet another reason why an alternative to SQLite is needed!
Why is that, @bryanpetkus? This wasn’t a SQLite issue. Plex was (incorrectly) inserting additional rows into the database.
I think you’ve reached a conclusion about Plex and Databases and SQLite and you are looking through that lens.
1 Like
@danielg1976, any changes in the last week? Have you needed to intervene & delete null rows?
Yes, I have needed to intervene and delete the null account rows again. I’ve been just performing some forensics again. I’m going to make another post with the details shortly.
Is there a reason sqlite is not desired? This doesn’t seem to be a database problem but a problem with what is being put in the database. Any DB implementation would still take a lot of space if millions of unnecessary entries were put in a table, I’d assume?
I don’t want to hijack this thread with a debate on using sqlite, but if you want to point me to another discussion on this I am curious why you’d say this.
Okay it appears the issue is still happening for me.
I have some ideas as to what’s happening, which I’ll mention at the end but let’s let the experts decide for sure 
DB Size Aug 23 after last cleanup: 116MB
DB Size today (9/6): 237MB
Version of server running during this period: 1.19.5.3112
I did a cleanup. Exactly the same as in this post..
Result was a file that was 116MB again (back to Aug 23 size).
I did make a copy and analyze it. Some notes:
- statistics_media was largest table again:
*** Page counts for all tables with their indices *****************************
STATISTICS_MEDIA.................................. 117907 51.0%
METADATA_ITEMS.................................... 47818 20.7%
MEDIA_PARTS....................................... 25460 11.0%
TAGGINGS.......................................... 15073 6.5%
MEDIA_ITEMS....................................... 11347 4.9%
MEDIA_STREAMS..................................... 3254 1.4%
METADATA_ITEM_CLUSTERINGS......................... 2548 1.1%
SPELLFIX_METADATA_TITLES_VOCAB.................... 1356 0.59%
-
It was filled with seemingly bogus entries with account_id=NULL, like before.
-
The entries were for two devices in our house that get usage and are not currently signed in to Plex.
a. NOTE: They were signed out when we realized that was the only way to get them to work during an internet outage… they weren’t signed back in. I haven’t signed them in yet because frankly I want to help figure this issue out) -
The devices are a Sony Android TV with latest firmware, and an iPad running latest iOS. Should have latest version of the Plex Clients on them.
4a. The vast majority of the entries in statistics_media table (1,151,027 rows) were from the iPad (device_id 8), metadata types ‘1’ mostly, but lots of ‘4’ also, and the table entries looked like this:
id, account_id, device_id, timespan, at, metadata_type, count, duration
17500140 NULL 8 1 2020-08-01 00:00:00 1 0 0
17500141 NULL 8 2 2020-08-24 00:00:00 1 0 0
17500142 NULL 8 0 2020-01-01 00:00:00 1 0 3
17500143 NULL 8 1 2020-08-01 00:00:00 1 0 3
17500144 NULL 8 2 2020-08-24 00:00:00 1 0 3
4b. There were also a significant number of entries from our Android TV [device_id 10] (42717 rows), metadata type was all ‘1’ even though we watch many TV episodes on it too. Here’s how it looks:
id, account_id, device_id, timespan, at, metadata_type, count, duration
17744087 NULL 10 0 2020-01-01 00:00:00 1 0 10
17744088 NULL 10 1 2020-08-01 00:00:00 1 0 10
17744089 NULL 10 2 2020-08-24 00:00:00 1 0 10
17744138 NULL 10 0 2020-01-01 00:00:00 1 0 10
17744139 NULL 10 1 2020-08-01 00:00:00 1 0 10
17744140 NULL 10 2 2020-08-24 00:00:00 1 0 10
17744189 NULL 10 0 2020-01-01 00:00:00 1 0 10
The ‘real’ statistics rows just don’t look the same as these bogus ones. There are WAY fewer of them, for one, and the dates vary much more and the durations also vary much more.
My suspicion is that there is some statistics generation bug related to these clients that aren’t signed in.
I’m happy to upload my DB and logs again for more forensics.
I’m going to now upgrade to the latest version of the server (1.20.1.3252). I suspect the problem to continue as I didn’t see anything in the release notes about this. But I’ll report back!
2 Likes
There are a few feature requests where folks go in to more detail as to why they’d like to see alternative database options for Plex:
Some of it is interesting reading. Some folks desire it for a perceived deficiency in SQLite3’s performance. Others want it because they’d like to be able to share a single database among multiple servers. Yet others just don’t consider SQLite3 to be a “proper” database.
I’m of the opinion that SQLite is perfectly suited to the task of being Plex’s database engine.
2 Likes
Plex Media Server Logs_2020-09-06_17-38-42.zip (3.3 MB) I’ll just be proactive and attach my logs to this post :-). I get an upload error with the DB file though (35MB ZIP).
So… it can be found here: Plex_Library_DB_20200906_1600.zip
Sorry I only have the .db file in the ZIP, I didn’t use the web server to download the database because I cleaned up the DB first and didn’t think of it. So I only have the copy of the .db I made.
I’m on board with that theory.
I considered brazenly adding a “not null” constraint to that column, but mine has been (fingers crossed) quiet for a while.
Otto mentioned a bug; perhaps it was addressed! I don’t think every internal fix is mentioned in the release notes.
Yep. I upgraded to the latest version and will watch it for another couple weeks. Keeping everything else the same. Since pretty much only a minor annoyance for me, I can still enjoy Plex while investigating this issue.
At some point, if this doesn’t go away, I’ll workaround it., logging the other clients in. I’ll then join the “please make Plex clients work better when the internet goes out” bandwagon.
1 Like
Ugh, yes, that. It seemed like everything was “OK” last time until it was partially working, and then my PMS and clients all got completely tangled up in their own shoelaces.
The coming server version 1.20.2 will have a fix related to this.
Make sure “Remove old cache files every week” is activated under Scheduled Tasks.
1 Like
Thanks for the update Otto! I’ll look for that update and of course post back on how things go.
Problem still occurs in version 1.20.1.3252. From 9/6 until 10/11, DB grew from 114 MB to 506MB. (A few MB was new photos…) - but most was the now almost 4 million entries in statistics_media. Removed the null account entries and it’s now at 120MB.
But Otto said it would be fixed in 1.20.2, so I’m not surprised this happened in 1.20.1.3252. I won’t bother uploading anything for debug. I’ll clean out, update to 1.20.2.3402, and I’ll report back in another few weeks!
