Plex SQLite && ZFS | Kometa | Page 1

fleet galleon Jan 13, 2024, 3:23 PM

#

Maybe is not the right place to ask, but I have the common "busy db" error running PMM, and I'm trying to find a solution to optimize the database.

I'm trying to follow this guide: https://openzfs.github.io/openzfs-docs/Performance and Tuning/Workload Tuning.html#sqlite-ps

but when I set the page_size it seems to be unchanged:

sqlite3 /config/Library/Application\ Support/Plex\ Media\ Server/Plug-in\ Support/Databases/com.plexapp.plugins.library.db

sqlite> PRAGMA page_size = 65536;
sqlite> PRAGMA page_size;
4096

Someone knows what is wrong?

PS. I have already scheduled PlexDBRepair script to run before running PMM, and I don't have other process running in the same time.
PS. I store Plex Library on a ZFS Raid0 (disk aggregation) with two hight performant NVME disk, the server have good CPUs and a large amount of ram. The library btw is not small.

icy groveBOT Jan 13, 2024, 3:23 PM

#

Welcome @fleet galleon!

It looks like you have not yet completed #938455615741775902, this will allow us to help you quicker.

Someone from <@&938443185347244033> will assist when they're available.

Including the meta.log from the beginning is a huge help, type !logs for more information.

#

You can press the "Close Post" button above or type /close at any time to close this post.

icy groveBOT Jan 13, 2024, 3:23 PM

#

icy grove You can press the "Close Post" button above or type `/close` at any time to clos...

fleet galleon Jan 13, 2024, 4:14 PM

#

Database was malformed, I solved with this:

sqlite3 com.plexapp.plugins.library.db ".recover" | sqlite3 com.plexapp.plugins.library.db.fix
sqlite3 com.plexapp.plugins.library.db.fix "PRAGMA integrity_check"
sqlite3 com.plexapp.plugins.library.db.fix "PRAGMA page_size=65536; VACUUM;"
cp com.plexapp.plugins.library.db.fix com.plexapp.plugins.library.db

#

same for blob db

#

this is the log of DBRepair, I run it before but the integrity error was not solved

Automatic Check,Repair,Index started.

Checking the PMS databases
Check complete.  PMS main database is OK.
Check complete.  PMS blobs database is OK.

Exporting current databases using timestamp: 2024-01-13_17.01.27
Exporting Main DB
Exporting Blobs DB
Successfully exported the main and blobs databases.  Proceeding to import into new databases.
Importing Main DB.
Importing Blobs DB.
Successfully imported databases.
Verifying databases integrity after importing.
Verification complete.  PMS main database is OK.
Verification complete.  PMS blobs database is OK.
Saving current databases with '-BACKUP-2024-01-13_17.01.27'
Making repaired databases active
Repair complete. Please check your library settings and contents for completeness.
Recommend:  Scan Files and Refresh all metadata for each library section.

Backing up of databases
Backup current databases with '-BACKUP-2024-01-13_17.03.35' timestamp.
Reindexing main database
Reindexing main database successful.
Reindexing blobs database
Reindexing blobs database successful.
Reindex complete.
Automatic Check, Repair/optimize, & Index successful.

fleet galleon Jan 13, 2024, 4:42 PM

#

the performance improvement is huge guys!! 1/16 of disk IOPs

thorn fable Jan 13, 2024, 5:32 PM

#

You should not write to the plex database using the standard SQLite tool; they have a custom one built into the server binary that you should use.

fleet galleon Jan 13, 2024, 6:53 PM

#

do you suggest to do it again from a backup?

#

or commands that I ran are safe?

thorn fable Jan 13, 2024, 7:12 PM

#

I personally wouldn't do anything to the Plex DB using anything but the built in sqlite. If yours is working maybe it's fine but ¯_(ツ)_/¯.

fleet galleon Jan 13, 2024, 7:13 PM

#

oki

thorn fable Jan 13, 2024, 7:13 PM

#

I personally would do it again from a backup using the method suggested by Plex.

fleet galleon Jan 13, 2024, 7:13 PM

#

yes is a good idea

#

I will close this

past zodiac Jan 13, 2024, 7:56 PM

#

Possibly also report this on the GitHub of ChuckPA as maybe he could enhance DBRepair and the rest of the community would benefit?

#

That would be appreciated @fleet galleon uparrow

fleet galleon Jan 13, 2024, 7:58 PM

#

let me double check with a corrupted backup and plex sqlite

#

then sure

#

I'm using the official docker image plexinc/pms-docker but I can't find the sqlite executable

past zodiac Jan 13, 2024, 8:00 PM

#

It's inside the container itself

#

There is a process to follow to get access to the SQLite3 within the container you are running

#

We used to have a cakey message to let people know..

silk daggerBOT Jan 13, 2024, 8:03 PM

#

Plex Help

Having trouble with PLEX? Here are some links that may help you.

Repair Corrupt DB: https://support.plex.tv/articles/repair-a-corrupted-database/
Restore PLEX DB: https://support.plex.tv/articles/202485658-restore-a-database-backed-up-via-scheduled-tasks/

past zodiac Jan 13, 2024, 8:03 PM

#

There you go... uparrow @fleet galleon

fleet galleon Jan 13, 2024, 8:04 PM

#

there isn't anything about the docker image :/

#

Windows (32-bit): "C:\Program Files (x86)\Plex\Plex Media Server\Plex SQLite.exe"
macOS: "/Applications/Plex Media Server.app/Contents/MacOS/Plex SQLite"
Linux (desktop): "/usr/lib/plexmediaserver/Plex SQLite"
QNAP: "/share/CACHEDEV1_DATA/.qpkg/PlexMediaServer/Plex SQLite"
Synology (DSM 6): "/var/packages/Plex Media Server/target/Plex SQLite"
Synology (DSM 7): "/var/packages/PlexMediaServer/target/Plex SQLite"

#

"Plex SQLite" test
bash: Plex SQLite: command not found

thorn fable Jan 13, 2024, 8:06 PM

#

you need to copy it out of the docker image.

#

give me a sec..

fleet galleon Jan 13, 2024, 8:06 PM

#

https://www.reddit.com/r/PleX/comments/onwnbe/trying_to_repair_database_in_official_pms_docker/

#

here we go

ls /usr/lib/plexmediaserver/
 CrashUploader           'Plex Media Fingerprinter'  'Plex Relay'       'Plex Transcoder'      etc
'Plex Commercial Skipper'  'Plex Media Scanner'        'Plex SQLite'       'Plex Tuner Service'   lib
'Plex DLNA Server'       'Plex Media Server'           'Plex Script Host'   Resources

thorn fable Jan 13, 2024, 8:09 PM

#

Assuming you are in the Databases directory, and you adjust those first two to suit your system:

plexdocker="plex"
sqplex="/opt/plexsql/Plex Media Server"

docker stop "${plexdocker}"
docker cp "${plexdocker}":/usr/lib/plexmediaserver/ /opt/plexsql

cp com.plexapp.plugins.library.db com.plexapp.plugins.library.db.original
"${sqplex}" --sqlite com.plexapp.plugins.library.db "SQL GOES HERE"

fleet galleon Jan 13, 2024, 8:30 PM

#

no corrupted log with plex sqlite

#

at least starting from the backup

#

the strange thing is that the page size is defined as 1024 with this command, let me check with a common sqlite3

Plex\ SQLite com.plexapp.plugins.library.db "PRAGMA page_size;"
1024

#

the same

#

sqlite3  com.plexapp.plugins.library.db "PRAGMA page_size;"
1024

#

this backup (from yesteday have a different configuration, maybe was changed by DBRepair

#

well... I will run it again after upgrading the page size to 64k

#

acting directly on db doesn't works:

# Plex\ SQLite com.plexapp.plugins.library.db
sqlite> PRAGMA integrity_check; PRAGMA page_size=65536; VACUUM; PRAGMA page_size;
ok
1024

#

on a recover file it works:

# Plex\ SQLite com.plexapp.plugins.library.db ".recover" | Plex\ SQLite com.plexapp.plugins.library.db.fix
Plex\ SQLite com.plexapp.plugins.library.db.fix "PRAGMA integrity_check; PRAGMA page_size=65536; VACUUM; PRAGMA page_size;"
ok
65536

#

I will run a DBRepair check the page_size again, start PMS and run its local activities than PMM again and check corruption with Plex SQLite and than DBRepair

#

ok is definitely DBRepair that changes my page size to 4k, I will look into the script

#

and is not the script but the default configuration on sqlite maybe, btw I will pr this small fix

fleet galleon Jan 13, 2024, 9:10 PM

#

too hard for me 😢

#

i can set it but if it goes in error I don't know how to fix

#

:/

fleet galleon Jan 13, 2024, 9:57 PM

#

I've wrote an issue on GitHub

past zodiac Jan 14, 2024, 1:51 AM

#

Pretty sure that ChuckPA will have some answers for you soon enough... and when he is back at work at Plex on Monday. 😉

#

But maybe he will answer on the weekend?

fleet galleon Jan 15, 2024, 7:39 PM

#

a fix is in beta and will be released soon, I have to close this?

tropic needle Jan 15, 2024, 11:20 PM

#

@fleet galleon Do you think this provides benefit for a traditional unraid system?

#

My plex install is unassigned with btrfs

fleet galleon Jan 16, 2024, 4:17 AM

#

no. btrfs block size is 4k so the default configuration of DBRepair (https://github.com/ChuckPa/PlexDBRepair ) works great out of the box

GitHub

GitHub - ChuckPa/PlexDBRepair: Database repair utility for Plex Med...

Database repair utility for Plex Media Server databases - GitHub - ChuckPa/PlexDBRepair: Database repair utility for Plex Media Server databases

tropic needle Jan 17, 2024, 2:30 AM

#

Ok thanks!

thorn fable Jan 19, 2024, 4:14 PM

#

Anything further needed here? @fleet galleon

fleet galleon Jan 19, 2024, 6:50 PM

#

not on my side

#

I can close it if you want

#Plex SQLite && ZFS