Library.db size more than doubled in latest version

Plex Media Server
,
357

Folks,

Here is a script for you all.

You can run it outside a docker container if you give it the real paths.

PMS must be stopped to use this

You MUST CONFIGURE the PATH variables at the top

This script is for Linux. Mac / Win can be adapted from this.

#!/bin/bash

#  1.  Configure PSQL and DBDIR to match your system
#       PSQL - Path to "Plex SQLite"   (Do not use  SQLite3)
#       DBDIR - Directory containing the database files
#
#  2.  Run this as 'root' user (UID 0) or 'sudo' root.


# Where is Plex SQLite really stored (native or external to container)
PSQL="/usr/lib/plexmediaserver/Plex SQLite"

# Where are databases really stored (native or external to container)
DBDIR="/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases"
CPPL="com.plexapp.plugins.library.db"

# Sanity checks
[ ! -d "$DBDIR" ] && echo "ERROR:  No such directory '$DBDIR'" && exit 1
[ ! -f "$PSQL" ] && echo "ERROR:  Cannot find 'Plex SQLite' at '$PSQL'" && exit 2
[ ! -w "$DBDIR/$CPPL" ] && echo "ERROR:  Cannot find  / no write permission for '$CPPL'" && exit 3

echo ==== Current listing
ls -lah

## Run in actual databases directory
cd "$DBDIR"

# Remove output if exists
rm -f "./new.com.plexapp.plugins.library.db"

echo ==== Deflating into new DB
"$PSQL" com.plexapp.plugins.library.db << EOT
  CREATE TABLE temp_bandwidth as select * from statistics_bandwidth where account_id not null;
  DROP TABLE statistics_bandwidth;
  ALTER TABLE temp_bandwidth RENAME to statistics_bandwidth;
  CREATE INDEX 'index_statistics_bandwidth_on_at' ON statistics_bandwidth ('at');
  CREATE INDEX 'index_statistics_bandwidth_on_account_id_and_timespan_and_at' ON 'statistics_bandwidth' ('account_id', 'timespan', 'at');
  VACUUM main into './new.com.plexapp.plugins.library.db';
EOT
Result=$?

# Make sure no errors and non-null DB
[ $Result -ne 0 ] && echo "ERROR: Error code $Result from deflate.  Seek assistance" && exit 4

# Confirm we have a DB
OK=1
[ ! -f "new.com.plexapp.plugins.library.db" ] && OK=0

# Confirm not zero
NewSize="$(stat -c '%s' "new.com.plexapp.plugins.library.db")"
[ "$NewSize" -le 300000 ] && OK=0

# Abort if not OK
[ $OK -ne 1 ] && echo "ERROR:  Something went wrong.  Exiting."  && exit 31

# Get the owning UID/GID before we proceed so we can restore
Owner="$(stat -c '%u:%g' $CPPL)"
Perms="$(stat -c '%a' $CPPL)"


echo ========== DEBUG ================
echo Owner: $Owner
echo Perms: $Perms
echo New: $NewSize

# Move existing DB's
echo "Moving existing DB to '-bloat'"
mv "$CPPL" com.plexapp.plugins.library.db-bloat
[ -e "$CPPL" ]     && mv "$CPPL" "$CPPL-bloated"

echo "Removing WAL and SHM if exist"
[ -e "$CPPL-wal" ] && rm -f "$CPPL-wal"
[ -e "$CPPL-shm" ] && rm -f "$CPPL-shm"

# Move new DB into position
echo "Making new DB active"
mv "new.com.plexapp.plugins.library.db"  "com.plexapp.plugins.library.db"

# Set owner and permissions
chown "$Owner" "$CPPL"
chmod "$Perms" "$CPPL"

echo New databases directory contents
ls -lah

echo Done.
exit 0

At the bottom is where the file moving is done.
You can insert a exit 0 before this to “dry run” it.

HOW THIS WORKS:

  1. It creates a new statistics_bandwidth table where the bloat is removed
  2. It then deletes the old table
  3. It renames the new (temp) to take its place
  4. It VACUUMs the DB into a new file (safeguard)
  5. When all looks OK, it renames the existing DB and moves the new into place.

Space required — the size of your DB before bloating.

5 Likes