Optimize Database takes almost an hour. every single time. If its actually was optimized after the first run, the second run (and used to in the past) right after the first run should be almost instant. This is not the case. I have checked my database and there is no corruption. Whats yalls thoughts on these really long times?
Not that I’m an expert on this but I don’t think there is any logical checks when optimizing the database. Meaning PLEX will optimize the database in the same fashion every time going through each table and each row.
But that is a real long time. Mine takes about 10 minutes.
I have about 49TB of media. When my library was much smaller, the first pass would take 10 or so minutes, and the second pass would take seconds. Thats the only reason it confuses me
@dirtycajunrice said:
I have about 49TB of media. When my library was much smaller, the first pass would take 10 or so minutes, and the second pass would take seconds. Thats the only reason it confuses me
Could be a time-stamp thing. Could also be “new” to the latest versions of PMS
@dirtycajunrice said:
Optimize Database takes almost an hour. every single time. If its actually was optimized after the first run, the second run (and used to in the past) right after the first run should be almost instant. This is not the case. I have checked my database and there is no corruption. Whats yalls thoughts on these really long times?
How big is the DB file?
What are you running on? (OS & CPU)
@ChuckPA (8.6G com.plexapp.plugins.library.db) its running Ubuntu 16.04.3 and it has 2x Intel Xeon E5-2670s
That’s a really big file. Loaded with photos? (each indexed item takes a base amount of space whether it be a photo, a photo album entry, a music track, an album referencing the songs, a TV episode, the parent series, or a movie. Add them all up and you can get a rough idea of the DB size. That said:
Did you empty the trash (to remove the deleted items from the database?
After performing that, the best next step is to export and reimport the DB manually via Sqlite3 . This will remove all the unused space in the physical DB as well as make the data tables contiguous again
Yes I have dumped and recreated the sqlite database. That is just movies and tv shows. I empty the trash (although I don’t delete anything so there is nothing to delete) and clean bundles.
This was all first steps before ever resorting to the forums
This also causes crazy unresponsive libraries during the optimization due to the nature of SQLite. The only thing I assume that would cause the “larger than you expected” database would be that I have thumbnail and preview on for every movie (2000+) and tv episode (16000+). I don’t use the music or pictures library features of plex. If it cannot handle these options… why are they available?
I have 28000 music tracks, 900 movies, and 7500 episodes weighing in at 75MB
-rw-r--r-- 1 plex users 74878976 Feb 23 14:28 com.plexapp.plugins.library.db
How your DB got so big is what needs to be resolved.
Edit: FWIW: Sqlite3 is good for 140 TB
Ok, interesting. So, first, I have created a new Server and only pulled watch stats from the other db. Then re-queried all of the shows in the new instance. Im sitting at under 50Mb. Is there a way to tell what could have possibly happened? And i was unaware of sqls 140TB, (although if it can hit that 8Gb shouldnt be doing what its doing)
If the trash was never emptied, every change would still be floating around in the DB. This includes every time media was moved, every poster change, everything related to metadata because Plex has to keep track of it.
If maintenance isn’t performed, the tables fragment and performance goes in the toilet as Sqlite has to run through all the table extensions and secondary pointers to find where the data is actually stored in the file. Not even an i7-7700 on an SSD can make up for that. That can occur at 8GB without any difficulty. Scramble it enough and it will not respond in time. PMS operations will “time out” and communication between cooperative tasks fails. It’s that simple
Well, I have “Empty trash automatically after every scan”, “Optimize database every week”, “Remove old bundles every week”, and “Remove old cache files every week” checked as per usual. Although as an IT guy I really hate this answer I am pretty sure its gonna have to be “Something poo’d the bed a long time ago and it just needed to be clean installed”.
Thanks for your time Chuck.
If you’re been a ‘good little IT guy’ 
(I’m an EE/CE myself so know the drill)
Something must have gone astray long ago.
I am glad you’re back on the right track.
fwiw: I did a test of sqlite3 not too long ago. I loaded a test DB with 32M records. I then did a query which would hit on 10% of them. From query start until all results retrieved in my program was about 600 ms. (3rd gen i7 at 2.7 Ghz) which isn’t half bad.
Hey chuck, Its only been 2 weeks since we last spoke and the DB is already 1.4G And the jump was within 5 days. This verifies it didnt just crap the bed a while back. What the heck could possibly be going on? Below is the logs during a database optimization, a bundle clean and a trash removal:
du -h com.plexapp.plugins.library.db*
1.4G com.plexapp.plugins.library.db
153M com.plexapp.plugins.library.db-2018-02-27
1.4G com.plexapp.plugins.library.db-2018-03-02
1.4G com.plexapp.plugins.library.db-2018-03-05
Mar 07, 2018 11:48:08.610 [0x7f7707bff700] WARN - Held transaction for too long (../Library/DatabaseFixups.cpp:191): 1.090000 seconds
Mar 07, 2018 11:48:19.587 [0x7f7707bff700] WARN - Held transaction for too long (../Library/FullTextSearch.cpp:17): 1.340000 seconds
Mar 07, 2018 11:48:20.670 [0x7f7707bff700] WARN - Held transaction for too long (../Library/FullTextSearch.cpp:27): 1.050000 seconds
Mar 07, 2018 11:48:23.403 [0x7f7707bff700] WARN - Held transaction for too long (../Library/FullTextSearch.cpp:38): 2.720000 seconds
Mar 07, 2018 11:48:24.688 [0x7f7707bff700] WARN - Held transaction for too long (../Library/FullTextSearch.cpp:48): 1.270000 seconds
Mar 07, 2018 11:50:20.541 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.567 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.585 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.606 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.618 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.636 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.647 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.659 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.670 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.682 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.693 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.704 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.716 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.726 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.737 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.748 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.767 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.792 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.809 [0x7f7707bff700] INFO - SQLITE3:0x10, 17, statement aborts at 57: [select * from metadata_items limit 1] database schema has changed
Mar 07, 2018 11:50:20.837 [0x7f77337ff700] ERROR - EventSource: Retrying in 15 seconds.
Mar 07, 2018 11:50:20.850 [0x7f76e83fd700] WARN - Held transaction for too long (../Statistics/StatisticsManager.h:216): 0.150000 seconds
Not much for helpful info in that… but it does show that the optimization only took ~ 2:18
Opening this in a new thread as technically this one closed: New Post
