Suggested SQLite3 DB Optimizations

Plex Media Server
102

DBRepair.sh is located in the container /config directory

So, what am I missing?

fred@omv:~$ docker exec -it plex /bin/bash
root@omv:/# s6-svc -d /var/run/service/plex
root@omv:/# /config/DBRepair.sh
Error: Unknown host. Currently supported hosts are: QNAP, Synology (DSM 6 & DSM 7), Linux Workstation/Server
/config/DBRepair.sh: 16: Error: Unknown host. Currently supported hosts are: QNAP, Synology (DSM 6 & DSM 7), Linux Workstation/Server: not found
103

Iā€™m going to tweak it right now. I rebuilt the container a second time and got a different result.

I understand.

104

@frederick.grayson

Docker container test restructured a bit. It likes LSIO and PlexInc now

root@lsioplex:/# hostname
lsioplex
root@lsioplex:/# ls
app  boot     config  defaults  docker-mods  home  lib    lib64   media  opt      proc  run   srv  tmp        usr
bin  command  data    dev       etc          init  lib32  libx32  mnt    package  root  sbin  sys  transcode  var
root@lsioplex:/# cd
root@lsioplex:~# ls
DBrepair.sh
root@lsioplex:~# ./DBrepair.sh 
 
 
 
      (DEVELOPMENT) Plex Media Server Database Repair Utility (Docker)
 
Select
 
  1. Check database
  2. Vacuum database
  3. Reindex database
  4. Attempt database repair
  5. Replace current database with newest usable backup copy
  6. Undo last successful action (Vacuum, Reindex, Repair, or Replace)
  7. Show logfile
  8. Exit
 
Enter choice:

I can make it such that lsio and plexinc are distinctly reported but doesnā€™t seem to buy anything.

105

Thanks for the fix.

I have the menu now. Letā€™s see how much trouble I can get into now :-0

106

What symptoms are you seeing?

I can guide you

107

Not really having any real symptoms other than what I see as sluggishness in the Plex web interface, probably aggravated by having large libraries.

When I add movies to the directory that holds them all it takes quite a while for Plex to digest that. Meanwhile, while it is chewing on the addition operation and updating things I canā€™t visit other areas of the interface. I have emptied the trash, cleaned bundles, and optimised the database but this doesnā€™t seem to improve things.

108

For sluggishness in Plex/web, presuming you have a fast enough machine -

  • Repair Database
  • Reindex Database

Do this because :

  1. Repair performs a full export, in database logical order and writes a ā€˜sortedā€™ file where the tables are again contiguous. It then imports back and writes perfect sorted order.

  2. Reindex writes fresh indexes after the import is complete

Did this on a small Syno box with a large DB and it ā€œjumped off the tableā€ by comparison to before the export/import

Digesting media will improve but is still dependent on several other factors.

109

Thanks for the tips, Iā€™ll try them.

Seems snappier moving around the interface. Adding new media seems the same - slow to complete.

Processor:

Intel(R) Xeon(R) CPU E3-1230 V2 @ 3.30GHz
16GB of RAM

Movie database is 875247K (no video preview thumbnails), 38410 movies.

111

with

https://www.cpubenchmark.net/cpu.php?cpu=Intel+Xeon+E3-1230+V2+%40+3.30GHz&id=1189

thatā€™s about 3/4 of the speed of an i7-7700.

38,000 movies needs more RAM.

Recommend 64 GB.

Try some searches to confirm

112

Thanks for the input. Iā€™ll see if I can find some more RAM.

113

Youā€™ll still be bound by the thread speed as it digests the file but RAM allows more of the DB to be in the kernel I/O buffers ā€“ which adding media is the hardest on; DB and disk i/o

114

Thanks. Looking into it further, the MB maxes out at 32GB of RAM. Likely not worth adding another 16GB. I can live with it as is :slight_smile:

117

@flow

Can you see this?

2 Likes
119

How do i run it on Unraid? Im new to commands

120

I cloned the repo and tried to run the script but itā€™s failing for me:

root@plex:~/PlexDBRepair# ./DBRepair.sh
./DBRepair.sh: 348: Syntax error: "fi" unexpected (expecting ")")

Iā€™m running it in a proxmox LXC container:

root@plex:~/PlexDBRepair# uname -a
Linux plex 5.15.39-3-pve #2 SMP PVE 5.15.39-3 (Wed, 27 Jul 2022 13:45:39 +0200) x86_64 x86_64 x86_64 GNU/Linux

Any idea how to fix this? Thanks

121

One of these days, Iā€™ll learn to type.

:roll_eyes:

Stand by please.

EDIT: Corrected. v0.3.3 (rerelease)

Cloning the repo is really counter productive.
Using the ā€˜releasesā€™ URL will always get you latest.

5 Likes
122

This tool is awesome!

I have just found a case where it did not work. On my main server (with override.conf) the tool would do nothing when executed - just a blinking cursor - no menu - no nothing. I manually changed the bits inside the tool to point to the appdata/database location and then it worked. Maybe you could take a look at the override recognition logic inside your tool again? :slight_smile:

1 Like
123

Could that be a used as a good thing? Test the changes using plex provided sqlite tool for the pragma change (after backup of course.)
Then implement permanent change using the sed swap?

124

sed is only going to let you change cache_size. The other PRAGMAS are established on the creation of a database connection and a binary edit isnā€™t possible for those.

125

could someone explaine to me how to use this tool with unraid and Docker.
I put the file in the folder of my PMS but what commands do i have to execute?