SQLite Database Issues

Plex Media Server Desktops & Laptops
,
1

Server Version: 1.13.5.5332
Player Version: Any/All Clients
Logs: https://hastebin.com/warulareno.cs

I have been experiencing SQLite DB issues more and more frequently lately. Whether it’s SLOW QUERY, “database is locked” “Waited X number of seconds for transaction”, etc. This goes on for some time before Plex becomes completely unresponsive, clients disconnect, and I am forced to restart the server entirely.

What I’ve done:

  1. Confirmed the DB is on its own dedicated SSD. I’ve also tried a different SSD to ensure no IOPS issues. (media is on HDDs)
  2. Moved transcode directory to RAMDisk to move it off the SSD to ensure literally nothing else is using IOPS.
  3. Repaired/rebuilt the DB per https://support.plex.tv/articles/201100678-repair-a-corrupt-database/
  4. Optimized the database (and set a CRON job to do it every ~12hrs)
  5. Set the PRAGMA default_cache_size to 100k per a few example threads around here and Reddit.

What fixes it:

  1. Reboot the Plex service/container
  2. (if I catch this happening before server becomes unresponsive) optimize the database will generally delay the inevitable.

What can I do here? I’m at the point where the server sometimes goes down several times per day, and I can’t continue in my current state. I’ve got ~60TB of media in Plex at this point, and I’m willing to do whatever is needed to get this sorted. Thanks!

1 Like
2

Bumping this original post as my SLOW QUERY woes continue. I’ve made a few changes since this post:

  1. Bumped Plex to Version 1.14.0.5468
  2. Moved databases to a RAMDisk to maximize performance
  3. I optimize my DB via cron every 6 hours with: curl -XPUT http://10.1.1.4:32400/library/optimize

Did some dd testing to confirm performance of the RAMDisk during usual load:

1MB blocks (sequential throughput):

root@NAS01:~# dd if=/dev/zero of=/tmp/testdd bs=1M count=10240 && rm -rf /tmp/testdd
10240+0 records in
10240+0 records out
10737418240 bytes (11 GB, 10 GiB) copied, 4.11106 s, 2.6 GB/s

512 byte blocks (IOPS)

root@NAS01:~# dd if=/dev/zero of=/tmp/testdd bs=512 count=102400 && rm -rf /tmp/testdd
102400+0 records in
102400+0 records out
52428800 bytes (52 MB, 50 MiB) copied, 0.132844 s, 395 MB/s

@sa2000 has recent PMS logs that show the issue, but I can provide them to others as needed. Thanks!

3

Several points on this

  • I have already explained that any issue to be investigated needs to have debug logging enabled beforehand. All logs provided had debug logging disabled. See my response to you here What do these logs mean exactly? Trying to debug general instability - #6 by sa2000

  • To investigate this, logs with debug logging enabled would need to be provided covering the time the Database Optimize was last triggered and all the time up to its completion. Also the logs need to cover the period between that time and the time when you notice an abnormal time for response time. You may need to increase the number of log files through LogNumFiles - see https://support.plex.tv/articles/201105343-advanced-hidden-server-settings/

  • There is no personal service provided here. Support is through the forums. If there are sensitive elements - look into redacting them provided they do not impede the investigation. For some specific diagnostics you may be requested to send the files privately or share link privately - but the normal process for support is through the forums

  • More frequent optimization may be needed if there is high level of changes to the media and adding/removing and this may also get triggered by having multiple instances of same media file in the libraries as the hashes would match and Plex may switch from one to another. Logs would show if this is happening

4

Thanks for the feedback, debug logging is now enabled, will wait for the issue to reproduce and reply here.

There is no personal service provided here. Support is through the forums. If there are sensitive elements - look into redacting them provided they do not impede the investigation. For some specific diagnostics you may be requested to send the files privately or share link privately - but the normal process for support is through the forums

I apologize if you took my request for assistance as a personal request for your help - I was simply noting to any Plex devs who happen to come across this post that you had the logs, in case it was easier to get them from you versus re-uploading. I completely understand that this is a forum and Plex doesn’t do 1:1 support.

More frequent optimization may be needed if there is high level of changes to the media and adding/removing and this may also get triggered by having multiple instances of same media file in the libraries as the hashes would match and Plex may switch from one to another. Logs would show if this is happening

I’m currently optimizing every 6 hours via cron job, so should be good there. Also I tend to see the SLOW QUERY log when there isn’t much being added/removed from my library. I never do any big changes (more than a few movies or episodes at a time), so not sure what’d cause it. Hopefully the debug logs clear it up.

Thanks again, will reply here and wait for assistance once the issue reproduces.

5

I think we all see that - but unless it is showing long delays - i tend to ignore. I normally only investigate cases where it is more 1000 ms

6

Ok had some good action over the holiday weekend. Logs are attached, and here are some stand-out (>1000ms) database SLOW QUERY errors:

Nov 23, 2018 09:22:36.078 [0x148093bfd700] WARN - SLOW QUERY: It took 1210.000000 ms to retrieve 8 items.
Nov 23, 2018 09:22:36.517 [0x1480a07ff700] WARN - SLOW QUERY: It took 2460.000000 ms to retrieve 30 items.
Nov 22, 2018 23:01:48.348 [0x1490a05fa700] WARN - SLOW QUERY: It took 239460.000000 ms to retrieve 50 items.
Nov 22, 2018 22:42:39.534 [0x1490d45fe700] WARN - SLOW QUERY: It took 182770.000000 ms to retrieve 50 items.
Nov 22, 2018 20:47:13.951 [0x1490ddfff700] WARN - SLOW QUERY: It took 68960.000000 ms to retrieve 1 items.

Thanks!

Plex Media (17.7 MB)

7

Thanks for the logs

I can see an unauthorized attempt to optimize the database - failing as it was unauthenticated

Nov 23, 2018 08:00:01.807 [0x1480ae1fe700] DEBUG - Request: [10.1.1.4:45972 (WAN)] PUT /library/optimize (7 live) Signed-in
Nov 23, 2018 08:00:01.807 [0x1480ae3ff700] DEBUG - Completed: [10.1.1.4:45972] 401 PUT /library/optimize (7 live) 0ms 249 bytes

and another 8 hours before - failing the same way

Nov 23, 2018 00:00:01.799 [0x1490debff700] DEBUG - Request: [10.1.1.4:39246 (WAN)] PUT /library/optimize (14 live) Signed-in
Nov 23, 2018 00:00:01.799 [0x1490de9fe700] DEBUG - Completed: [10.1.1.4:39246] 401 PUT /library/optimize (14 live) 0ms 249 bytes

If you are invoking yourself using api, you would need to add ?X-Plex-Token=xxxxxxxxxx

Aim to run it out of hours as the server is locked out during database optimization and plex client apps may fail attempting to talk to the server. Database optimization may taken a while - more than any timeout period set in clients

8

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.