I think something may be wrong with my Plex database, how can I be sure?

[jayehmsee]

Server Version#: 1.19.5.3035
Player Version#: Samsung TV Tizen 4.0, Plex for Samsung 4.29.6
(I cast it to the TV from my Android phone Plex app)

My Plex server has been slow at different times for months and I kind of got used to it but started wondering if something was wrong with it that I could fix with help. I started watching the console when I was playing a movie and a tv show and when I moved some files around and I see Warnings of slow database queries and Errors.

Is there some way I can assess if something is wrong with my Plex database?
Then after it is assessed, explore options for fixing it?

Thanks for your help,
JMC

[SE56]

Have you tried dumping the cache of browser and PMS and restart.

Also to add it a good think on a Mac to set up a scheduled restart once a week with PMS a login item.

[FordGuy61]

@jayehmsee

Is there some way I can assess if something is wrong with my Plex database?
Then after it is assessed, explore options for fixing it?

Pull the Plex server log files and look in Plex Media Server.log (and .1.log, etc) for lines with ERROR - SQLITE3:..... The line will have text such as database corruption at line.... or database disk image is malformed....

See this post for an example.

If your database is corrupt, you’ve three options: 1) repair; 2) replace from backup; 3) delete & start over.

The support article below has instructions on checking for corruption and how to attempt repairs.

If you replace with a backup, either check it before using or check the log files immediately afterwards to make sure it is not corrupt.

If you choose to delete the database, you will have to re-create your libraries. Your media will not be affected.

https://support.plex.tv/articles/201100678-repair-a-corrupt-database/

https://support.plex.tv/articles/201643703-reporting-issues-with-plex-media-server/

[jayehmsee]

Thank you for that reply and guidance. I appreciate it.

I looked at the logs and did not find ‘malformed’ or ‘corrupt’ in them.

I ran the checks for corruption at the referenced article ‘Repair a Corrupt Database’ and the checks did not detect corruption. I did not run any of the repair commands.

So since my Plex database doesn’t seem to be corrupt, I’ll paste in some of the Warning and Error messages that have me wondering. I removed some files and these are the Warning and Error messages. Does anyone have any guidance on these? I’m not sure if they are serious or minor but the slow query messages make me wonder if my Plex database isn’t as efficient as it could be.

Thank you,
JMC

Warning:
Jul 18, 2020 10:01:08.488 [0x70000d7d6000] Warning — SLOW QUERY: It took 240.000000 ms to retrieve 2 items.
Jul 18, 2020 10:01:19.286 [0x70000de7d000] Warning — Took too long (2.530000 seconds) to start a transaction on …/Library/MetadataCollection.cpp:175
Jul 18, 2020 10:01:19.286 [0x70000de7d000] Warning — Transaction that was running was started on thread 0x70000d753000 at …/Library/MetadataCollection.cpp:175
Jul 18, 2020 10:01:21.113 [0x70000e5a7000] Warning — SLOW QUERY: It took 230.000000 ms to retrieve 33 items.
Jul 18, 2020 10:01:22.796 [0x70000de7d000] Warning — Held transaction for too long (…/Library/MetadataCollection.cpp:250): 0.160000 seconds
Jul 18, 2020 10:01:24.498 [0x70000e39b000] Warning — Took too long (2.790000 seconds) to start a transaction on …/Library/MetadataCollection.cpp:175
Jul 18, 2020 10:01:24.498 [0x70000e39b000] Warning — Transaction that was running was started on thread 0x70000de7d000 at …/Library/MetadataCollection.cpp:175
Jul 18, 2020 10:01:24.676 [0x70000e41e000] Warning — Took too long (2.710000 seconds) to start a transaction on …/Library/MetadataCollection.cpp:175
Jul 18, 2020 10:01:24.676 [0x70000e41e000] Warning — Transaction that was running was started on thread 0x70000e39b000 at …/Library/MetadataCollection.cpp:175
Jul 18, 2020 10:01:24.677 [0x70000d2b8000] Warning — Took too long (0.580000 seconds) to start a transaction on …/Statistics/StatisticsManager.cpp:245
Jul 18, 2020 10:01:24.677 [0x70000d2b8000] Warning — Transaction that was running was started on thread 0x70000e41e000 at …/Library/MetadataCollection.cpp:175
Jul 18, 2020 10:01:25.569 [0x70000d5ca000] Warning — SLOW QUERY: It took 220.000000 ms to retrieve 1 items.
Jul 18, 2020 10:02:05.555 [0x70000d7d6000] Warning — SLOW QUERY: It took 350.000000 ms to retrieve 37 items.
Jul 18, 2020 10:02:05.686 [0x70000d5ca000] Warning — SLOW QUERY: It took 490.000000 ms to retrieve 7 items.
Jul 18, 2020 10:02:05.689 [0x70000d64d000] Warning — Held transaction for too long (…/Library/MetadataCollection.cpp:250): 0.240000 seconds
Jul 18, 2020 10:02:05.689 [0x70000d8dc000] Warning — Took too long (0.110000 seconds) to start a transaction on …/Library/MetadataCollection.cpp:175
Jul 18, 2020 10:02:05.689 [0x70000d8dc000] Warning — Transaction that was running was started on thread 0x70000d64d000 at …/Library/MetadataCollection.cpp:175
Jul 18, 2020 10:02:13.236 [0x70000d8dc000] Warning — SLOW QUERY: It took 680.000000 ms to retrieve 34 items.
Jul 18, 2020 10:02:13.550 [0x70000d64d000] Warning — SLOW QUERY: It took 520.000000 ms to retrieve 51 items.
Jul 18, 2020 10:02:14.344 [0x70000d5ca000] Warning — SLOW QUERY: It took 210.000000 ms to retrieve 4 items.

Error:
Jul 18, 2020 10:02:46.023 [0x70000d7d6000] Error — SQLITE3:0x1109c32c0, 1, near “,”: syntax error in "select upper(substr((select, 1, 1)) as c,count(distinct metadata_items.id) from metadata_items left join metadata_item_settings on metadata_item_settings.guid=metadata_items.guid an
Jul 18, 2020 10:03:59.335 [0x70000dd77000] Error — SQLITE3:0x1109c32c0, 1, near “,”: syntax error in "select upper(substr((select, 1, 1)) as c,count(distinct metadata_items.id) from metadata_items left join metadata_item_settings on metadata_item_settings.guid=metadata_items.guid an
Jul 18, 2020 10:03:59.336 [0x70000dd77000] Error — Soci Exception handled: sqlite3_statement_backend::prepare: near “,”: syntax error for SQL: select upper(substr((select, 1, 1)) as c,count(distinct metadata_items.id) from metadata_items left join metadata_item_settings on metadata_item_settings.guid=metadata_items.guid and metadata_item_settings.account_id= 1 where metadata_items.library_section_id=? and metadata_items.library_section_id in (6) and (metadata_items.metadata_type=2 and (select count(*) from metadata_items as leaves join metadata_items as parents on leaves.parent_id=parents.id left join metadata_item_settings on metadata_item_settings.guid = leaves.guid and metadata_item_settings.account_id=1 where (parents.parent_id=metadata_items.id or parents.id=metadata_items.id) and (metadata_item_settings.view_count=0 or metadata_item_settings.view_count is null) and leaves.metadata_type in (1, 4, 10))>=1) group by c order by c
Jul 18, 2020 10:04:04.982 [0x70000dd77000] Error — Unknown metadata type: folder

[FordGuy61]

The slow query entries sometimes happen, especially if the system is busy with other tasks. Not sure about the error messages. I have not seen those on my system.

In Scheduled Tasks, make sure you have database optimization and removal of old bundles and cache files scheduled.

Then in Troubleshooting, manually Clean Bundles then Optimize Database.

And it wouldn’t hurt to restart the Mac as suggested by SE56.

[jayehmsee]

Hi @FordGuy61,

Thanks for your reply. I’ve checked ‘top’ and it looks like the slow queries log entries happen even when nothing is using the processors much on the mac mini. Just an observation there.

I checked Scheduled Tasks and those were already turned on.

I did go ahead and manually Clean Bundles and Optimize Database.
I also quit the Plex Server and started it again.
Then I added a new file and I saw similar log entries.

I had restarted the mac mini 15 days ago. Previously it was up for 300+ days and 15 days ago I upgraded the Mac OS to current.

As I’ve read articles and done these steps I remembered that my Plex database has been around for a long time. I think I’ve been running it for 10+ years.
I have also seen a couple log messages saying the schema had changed. I can’t remember if they were Warning or Error.

I’m wondering if I should back up the current database and then let it create a new database from scratch and then see how the Warnings and Errors and general performance is?
Or, I’ve read that sometimes even with no corruption found the repair steps can fix some things, so maybe running the repair steps on my current database?

Thanks for your help,
JMC

[FordGuy61]

@jayehmsee

couple log messages saying the schema had changed

I have a few in my log files. They’re info level, so I’m not worrying about it.

@jayehmsee

I’m wondering if I should back up the current database and then let it create a new database from scratch and then see how the Warnings and Errors and general performance is?
Or, I’ve read that sometimes even with no corruption found the repair steps can fix some things, so maybe running the repair steps on my current database?

The Repair a Corrupt Database support document mentions a repair can help sometimes.

As long as you’ve a backup copy it won’t hurt to try the repair or starting over with a new database.

[jayehmsee]

I decided to run the Repair Database steps this morning. I followed each step.

After it was done I observed a size difference between the old and new database:
-rw-r–r-- 1 staff 211M Jul 19 08:26 com.plexapp.plugins.library.db.original
-rw-r–r-- 1 staff 184M Jul 19 08:37 com.plexapp.plugins.library.db

Then I added 33 files and watched the logs. I’m still seeing similar Warning and Error messages as before.

Warning:
Jul 19, 2020 09:19:35.513 [0x700004a2d000] Warning — Held transaction for too long (…/Statistics/StatisticsManager.cpp:245): 3.710000 seconds
Jul 19, 2020 09:19:35.513 [0x700004a2d000] Warning — Held transaction for too long (…/Statistics/StatisticsManager.cpp:245): 3.710000 seconds
Jul 19, 2020 09:19:39.302 [0x700003eeb000] Warning — MDE: unable to find a working transcode profile for video stream
Jul 19, 2020 09:19:39.302 [0x700003eeb000] Warning — MDE: unable to find a working transcode profile for video stream
Jul 19, 2020 09:19:39.302 [0x700003eeb000] Warning — MDE: unable to find a working transcode profile for video stream
Jul 19, 2020 09:19:39.302 [0x700003eeb000] Warning — MDE: unable to find a working transcode profile for video stream
Jul 19, 2020 09:20:14.309 [0x7000048a4000] Warning — SLOW QUERY: It took 510.000000 ms to retrieve 1 items.
Jul 19, 2020 09:20:32.543 [0x700003cdf000] Warning — SLOW QUERY: It took 440.000000 ms to retrieve 6 items.
Jul 19, 2020 09:20:44.178 [0x700003844000] Warning — SLOW QUERY: It took 1500.000000 ms to retrieve 30 items.
Jul 19, 2020 09:20:44.178 [0x700003844000] Warning — SLOW QUERY: It took 1500.000000 ms to retrieve 30 items.
Jul 19, 2020 09:20:48.151 [0x7000034af000] Warning — QueryParser: Invalid field ‘contentDirectoryID’ found, ignoring.
Jul 19, 2020 09:20:48.151 [0x7000034af000] Warning — QueryParser: Invalid field ‘libraryHubsOnly’ found, ignoring.

Error:
Jul 19, 2020 08:54:33.100 [0x700003eeb000] Error — Error parsing content.
Jul 19, 2020 08:54:33.234 [0x700004409000] Error — Unknown metadata type: folder
Jul 19, 2020 08:54:33.242 [0x7000041fd000] Error — SQLITE3:0x10ecff2c0, 1, near “,”: syntax error in "select upper(substr((select, 1, 1)) as c,count(distinct metadata_items.id) from metadata_items left join metadata_item_settings on metadata_item_settings.guid=metadata_items.guid an
Jul 19, 2020 08:54:33.242 [0x7000041fd000] Error — Soci Exception handled: sqlite3_statement_backend::prepare: near “,”: syntax error for SQL: select upper(substr((select, 1, 1)) as c,count(distinct metadata_items.id) from metadata_items left join metadata_item_settings on metadata_item_settings.guid=metadata_items.guid and metadata_item_settings.account_id= 1 where metadata_items.library_section_id=? and metadata_items.library_section_id in (6) and (metadata_items.metadata_type=2 and (select count(*) from metadata_items as leaves join metadata_items as parents on leaves.parent_id=parents.id left join metadata_item_settings on metadata_item_settings.guid = leaves.guid and metadata_item_settings.account_id=1 where (parents.parent_id=metadata_items.id or parents.id=metadata_items.id) and (metadata_item_settings.view_count=0 or metadata_item_settings.view_count is null) and leaves.metadata_type in (1, 4, 10))>=1) group by c order by c
Jul 19, 2020 08:54:34.082 [0x700003eeb000] Error — Error parsing XML response for update.
Jul 19, 2020 08:54:34.208 [0x700004409000] Error — Error resizing an image, we don’t trust what we cached [/Users/jclark/Library/Caches/PlexMediaServer/PhotoTranscoder/09/09e571f75239bfeabbad7e0fa75c79d05e76abc2.jpg]
Jul 19, 2020 08:54:38.688 [0x7000040f7000] Error — Error resizing an image, we don’t trust what we cached [/Users/jclark/Library/Caches/PlexMediaServer/PhotoTranscoder/09/09e571f75239bfeabbad7e0fa75c79d05e76abc2.jpg]
Jul 19, 2020 08:54:38.688 [0x7000040f7000] Error — Error resizing an image, we don’t trust what we cached [/Users/jclark/Library/Caches/PlexMediaServer/PhotoTranscoder/09/09e571f75239bfeabbad7e0fa75c79d05e76abc2.jpg]
Jul 19, 2020 08:54:38.901 [0x70000450f000] Error — Caught exception trying to stream file: /Users/jclark/Library/Caches/PlexMediaServer/PhotoTranscoder/bc/bca03ff57c7a967bf1e318a1fa09b12feaada207.jpg: write: Broken pipe
Jul 19, 2020 08:54:38.901 [0x70000450f000] Error — Caught exception trying to stream file: /Users/jclark/Library/Caches/PlexMediaServer/PhotoTranscoder/bc/bca03ff57c7a967bf1e318a1fa09b12feaada207.jpg: write: Broken pipe

Its responsiveness does seem to be a little better since I did the original check for corruption in this thread. For some reason the PMS seemed to be more responsive after that.

I think I’ll leave it alone for the week and watch shows like normal and see what I think. I’d just hate to start over with a new database, even with being able to keep the viewed state, because I imagine it would take so long for it to populate the 27,710 episodes and 1,147 movies. But, I do wonder if it might make it even more responsive.

[jayehmsee]

Over this week I was using the Plex Media Server like normal and also searching and found this posting:

Database Slow Query, Unable to Load Episode, all Star Trek Series

Frankly, SLOW QUERY is directly related to filesystem performance. PMS marks the time when the query begins. If, upon return, the ratio of time required to items returned exceeds a few ms, it will raise the warning. The logs show this. The root cause may be the CPU, may be other tasks monopolizing the DB (unlikely but can happen), sheer size of the DB, or poor performance. If the DB has been compacted at the SQLITE3 level, and the CPU more than fast enough, only two things remain…

That led me to think about the harddrive in the mac mini and I realized that there were a few other programs that would be using the disk that the Plex databases reside on. I turned them off and used the Plex Media Server like normal and the logs showed fewer Errors and Warnings though still produced the slow query warnings, but fewer of them.

I tested with a couple people around the country that I share my Plex Media Server with and they had good performance with loading of pages and images as well as with streaming content.

So I think the databases are fine and it’s a matter of the disk usage. I specifically think it’s the blobs.db that is slow because posters are large(?).

I still wonder if a huge content move I did over a year ago while Plex was running may have complicated the databases or broken some indexing, plus I upgraded the OS on the mac mini a month and a half ago, and I migrated the Plex Media Server install from a laptop I had been running it on 7 years ago instead of starting with a fresh install.

That all makes me wonder if doing a backup of viewed state:
https://support.plex.tv/articles/201154527-move-viewstate-ratings-from-one-install-to-another/

Setting aside the current db and blobs.db.

Then a complete uninstall:
https://support.plex.tv/articles/201941078-uninstall-plex-media-server/

Then reinstalling Plex Media Server and letting it build a database from scratch might yield improvements. I’d check the file sizes of the new db and blobs.db files and watch the logs while I search through the web interface. I could load the previous watched states.

I think this is what I’m going to do because if it didn’t work out I could just drop the previous db’s in and restart Plex Media Server.

[jayehmsee]

It’s been about 3 weeks and I did completely uninstall Plex and started over with a fresh database. I decided not to import the previously viewed states. I decided just keep all the old database information to itself and just let the new database be.

I suspect that some, but not all, of the slow queries were from other programs using the mac mini disk, but at the same time I just added a couple of files and got plenty of slow query warnings with plex being the only program running on the mac mini. I am not seeing the same Error messages of failed sql commands, so I am happy about that.

Here’s an example of what I see for Warnings and Errors now:

Warnings:
Aug 16, 2020 16:25:04.808 [0x7000082ab000] Warning — SLOW QUERY: It took 340.000000 ms to retrieve 1 items.
Aug 16, 2020 16:25:04.810 [0x70000907c000] Warning — SLOW QUERY: It took 390.000000 ms to retrieve 1 items.
Aug 16, 2020 16:25:05.353 [0x700008ef3000] Warning — Held transaction for too long (…/Library/MetadataItem.cpp:1107): 0.210000 seconds
Aug 16, 2020 16:25:05.466 [0x700008ef3000] Warning — Held transaction for too long (…/Library/MetadataItem.cpp:3648): 0.220000 seconds
Aug 16, 2020 16:25:06.419 [0x7000078f2000] Warning — Held transaction for too long (…/Library/MetadataCollection.cpp:404): 0.850000 seconds
Aug 16, 2020 16:25:06.585 [0x7000088cf000] Warning — SLOW QUERY: It took 790.000000 ms to retrieve 1 items.
Aug 16, 2020 16:25:06.585 [0x700009182000] Warning — SLOW QUERY: It took 460.000000 ms to retrieve 1 items.
Aug 16, 2020 16:25:06.589 [0x70000809f000] Warning — SLOW QUERY: It took 500.000000 ms to retrieve 1 items.
Aug 16, 2020 16:25:08.649 [0x7000082ab000] Warning — SLOW QUERY: It took 240.000000 ms to retrieve 1 items.
Aug 16, 2020 16:25:09.180 [0x70000938e000] Warning — Held transaction for too long (…/Library/MetadataItem.cpp:6790): 0.110000 seconds
Aug 16, 2020 16:25:09.284 [0x70000938e000] Warning — Held transaction for too long (…/Library/MetadataItem.cpp:1055): 0.320000 seconds
Aug 16, 2020 16:33:38.531 [0x700007a7b000] Warning — QueryParser: Invalid field ‘libraryHubsOnly’ found, ignoring.
Aug 16, 2020 16:33:38.535 [0x700007a7b000] Warning — QueryParser: Invalid field ‘libraryHubsOnly’ found, ignoring.
Aug 16, 2020 16:34:09.708 [0x7000081a5000] Warning — QueryParser: Invalid field ‘libraryHubsOnly’ found, ignoring.
Aug 16, 2020 16:34:09.853 [0x7000081a5000] Warning — QueryParser: Invalid field ‘libraryHubsOnly’ found, ignoring.
Aug 16, 2020 16:34:18.647 [0x7000081a5000] Warning — [Chromecast] Did not move header “accept” to query string. This can result in an unnecessary OPTIONS preflight request.
Aug 16, 2020 16:34:18.840 [0x7000079f8000] Warning — [Chromecast] [MDE] Invalid profile property; channels: 6 > 2
Aug 16, 2020 16:34:18.840 [0x7000079f8000] Warning — [Chromecast] [MDE] Invalid profile property; channels: 6 > 2
Aug 16, 2020 16:34:19.310 [0x7000079f8000] Warning — [Chromecast] [MDE] Invalid profile property; channels: 6 > 2
Aug 16, 2020 16:34:19.310 [0x7000079f8000] Warning — [Chromecast] [MDE] Invalid profile property; channels: 6 > 2
Aug 16, 2020 16:34:35.720 [0x70000809f000] Warning — [Chromecast] Did not move header “accept” to query string. This can result in an unnecessary OPTIONS preflight request.

Errors:
Aug 16, 2020 15:38:40.426 [0x700007b81000] Error — Error issuing curl_easy_perform(handle): 28
Aug 16, 2020 16:22:23.809 [0x700007663000] Error — EventSource: Retrying in 15 seconds.
Aug 16, 2020 16:22:29.592 [0x700007663000] Error — EventSource: Retrying in 15 seconds.
Aug 16, 2020 16:22:39.567 [0x700007e10000] Error — [PlexRelay] client_loop: send disconnect: Broken pipe
Aug 16, 2020 16:22:47.281 [0x7000076e6000] Error — MyPlex: mapping failed due to potential double NAT configuration
Aug 16, 2020 16:24:01.647 [0x700008be1000] Error — Error parsing content.
Aug 16, 2020 16:24:01.647 [0x700008be1000] Error — Error parsing XML: Error parsing file.
Aug 16, 2020 16:24:01.647 [0x700008be1000] Error — Scanner [Plex Movie]: error reading match results

The main reason it’s been 3 weeks from my last message is because I paused in investigating Plex to address my Drobo Gen2 4-bay that was unmounting from my mac mini when Plex would scan it and also when I would try to watch content from it. I had been tolerating it for months and decided to deal with it. I checked online and it seems that all drive slots green but with unmounting may be a disk failing in a way Drobo can’t report yellow/red. So, I took out drive 2 (different serial numbers, it was a guess), let Drobo rebalance the data and noticed wildly changing estimates to completion. Then I removed drive 1, and was impatient so put drive 2 back in, and it rebalanced the data but the estimate remained steady. After the data was rebalanced I did an extended copy out of the Drobo and it didn’t unmount and the estimate to completion was consistent. Then I did an extended copy onto the Drobo and it didn’t unmount and the estimate to completion was consistent. Then I turned Plex on and scanned the Drobo and it didn’t unmount. I’ve also watched several shows that reside on the Drobo and not had trouble. So, I think the Drobo is resolved. Drive 1 seems to have been the culprit.

That had nothing to do with Plex but the Plex scan is what would lead to the unmounting so I addressed the Drobo.

So, I feel like I’m done with this activity. I still have slow query Warnings but they don’t seem to affect using Plex, whereas before maybe they were? The sql Errors are gone. I learned that I can make Plex more usable by turning off other programs on the mac mini while I’m going to actively be using Plex. The mac mini is from 2013 so I guess it’s not super robust.
Mac mini:
Processor: 1.4 GHz Dual-Core Intel Core i5
Memory: 4 GB 1600 MHz DDR3

Thanks to @FordGuy61 and @SE56 for your help and discussion!

JMC