"SLOW QUERY" and "Held transaction for too long" lately

General Discussions
1
  • PMS Version: 1.13.2.5154
  • Platform: Official Docker (latest) on UnRAID (6.5.0)
  • CPU: 2x E5-2680v2
  • RAM: 96GB DDR3 ECC
  • WAN: CenturyLink Fiber 1Gbps/1Gbps

Hey all,

Having some SQLite DB issues lately. The entire /config folder for Plex resides on a mirrored 120GB SSD (using btrfs in UnRAID), yet I’m seeing all sort of database errors and slowness in recent months. It’s to the point where I sometimes require a container reboot to get plex “unstuck”. I’ve seen instances where new media added couldn’t be scanned, as the database was busy, or scenarios where Plex just becomes unresponsive. Looking for any input on how I can proceed.

Other things I’ve tried:

  • I’ve tried optimizing the DB (it’s done weekly via Scheduled Tasks, but still tried just to be sure)

  • I’ve tried the Repair a Corrupt Database documentation to check for SQLite errors, and I even did the repair anyways just in case

Relevant log examples:

Jun 11, 2018 14:31:30.103 [0x14b28dbff700] WARN - SLOW QUERY: It took 600.000000 ms to retrieve 6 items.
Jun 11, 2018 14:31:30.106 [0x14b2773ff700] WARN - SLOW QUERY: It took 510.000000 ms to retrieve 3 items.
Jun 11, 2018 14:31:30.114 [0x14b28b3fd700] WARN - SLOW QUERY: It took 710.000000 ms to retrieve 6 items.
Jun 11, 2018 14:31:30.135 [0x14b2953ff700] WARN - SLOW QUERY: It took 790.000000 ms to retrieve 6 items.
Jun 11, 2018 14:31:51.600 [0x14b28b3fd700] WARN - SLOW QUERY: It took 250.000000 ms to retrieve 6 items.
Jun 11, 2018 14:31:51.610 [0x14b29cbff700] WARN - SLOW QUERY: It took 340.000000 ms to retrieve 6 items.

Jun 11, 2018 14:17:28.320 [0x14b298fff700] WARN - Held transaction for too long (../Sync/SyncItemGenerator.cpp:142): 0.160000 seconds
Jun 11, 2018 14:17:28.637 [0x14b298fff700] WARN - Held transaction for too long (../Sync/SyncItemGenerator.cpp:142): 0.130000 seconds
Jun 11, 2018 14:17:28.944 [0x14b298fff700] WARN - Held transaction for too long (../Sync/SyncItemGenerator.cpp:142): 0.150000 seconds
Jun 11, 2018 14:18:21.986 [0x14b28fbff700] WARN - Held transaction for too long (../Library/DatabaseFixups.cpp:207): 0.330000 seconds
Jun 11, 2018 14:18:23.351 [0x14b28fbff700] WARN - Held transaction for too long (../Library/FullTextSearch.cpp:17): 1.150000 seconds
Jun 11, 2018 14:18:24.583 [0x14b28fbff700] WARN - Held transaction for too long (../Library/FullTextSearch.cpp:27): 0.870000 seconds
Jun 11, 2018 14:18:26.573 [0x14b28fbff700] WARN - Held transaction for too long (../Library/FullTextSearch.cpp:38): 1.910000 seconds
Jun 11, 2018 14:18:27.866 [0x14b28fbff700] WARN - Held transaction for too long (../Library/FullTextSearch.cpp:48): 1.120000 seconds

Sqlite3: Sleeping for 200ms to retry busy DB.
Sqlite3: Sleeping for 200ms to retry busy DB.
Sqlite3: Sleeping for 200ms to retry busy DB.
Sqlite3: Sleeping for 200ms to retry busy DB.
Sqlite3: Sleeping for 200ms to retry busy DB.
Sqlite3: Sleeping for 200ms to retry busy DB.
Sqlite3: Sleeping for 200ms to retry busy DB.
Sqlite3: Sleeping for 200ms to retry busy DB.
Sqlite3: Sleeping for 200ms to retry busy DB.

Disk read performance seems to be just fine on both the active and secondary SSDs:

root@NAS01:~# hdparm -Tt /dev/sde

/dev/sde:
 Timing cached reads:   19680 MB in  1.99 seconds = 9869.70 MB/sec
 Timing buffered disk reads: 1298 MB in  3.00 seconds = 432.02 MB/sec
    
root@NAS01:~# hdparm -Tt /dev/sdd

/dev/sdd:
 Timing cached reads:   20390 MB in  1.99 seconds = 10226.91 MB/sec
 Timing buffered disk reads: 1294 MB in  3.00 seconds = 430.85 MB/sec