# Suggested SQLite3 DB Optimizations
**URL:** https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749
**Category:** Plex Media Server
**Tags:** server-linux
**Created:** [May 29, 2022, 3:20am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749 "2022-05-29T03:20:42Z")
**Posts on this page:** 20
**Page:** 4
### Author:  [@jasonsansone](https://forums.plex.tv/u/jasonsansone)
#### Post date: [October 11, 2022, 3:10pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/62 "2022-10-11T15:10:31Z")
If you are doing this on a broader scope than personal use, such as providing a packaged solution for others as you mention, you might want to receive written permission prior to doing so.
The other obvious problem is that if Plex makes code changes in the binary it could cause these solutions to cease to work, or worse cause corruption to a database, at any time. These tweaks incur a certain maintenance cost and risk.
---
### Author:  [@Gloppie](https://forums.plex.tv/u/Gloppie)
#### Post date: [October 11, 2022, 3:11pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/63 "2022-10-11T15:11:59Z")
Noted. The overlay isn’t in reapology or accessible from eselect, so it’s not that noticed.
---
### Author:  [@ChuckPa](https://forums.plex.tv/u/ChuckPa)
#### Post date: [October 11, 2022, 3:43pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/64 "2022-10-11T15:43:51Z")
> [@Tangs](#):
>
> Should i use this script in a powershell?
>
> 1. service plexmediaserver stop
> 2. “/usr/lib/plexmediaserver/Plex SQLite” “/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.db” “PRAGMA page\_size=65536” “PRAGMA journal\_mode=DELETE” “VACUUM” “PRAGMA journal\_mode=WAL” “PRAGMA optimize”
> 3. “/usr/lib/plexmediaserver/Plex SQLite” “/var/lib/plexmediaserver/Library/Application Support/Plex Media Server/Plug-in Support/Databases/com.plexapp.plugins.library.blobs.db” “PRAGMA page\_size=65536” “PRAGMA journal\_mode=DELETE” “VACUUM” “PRAGMA journal\_mode=WAL” “PRAGMA optimize”
> 4. sed -i.bak -e ‘s/cache\_size=2000/cache\_size=9999/’ ‘/usr/lib/plexmediaserver/Plex Media Server’
> 5. service plexmediaserver start
>
> Then i will increase the cache allowed for plex posters?
This is a violation of Plex’s licensing .
License to modify was not granted.
---
### Author:  [@ChuckPa](https://forums.plex.tv/u/ChuckPa)
#### Post date: [October 11, 2022, 3:45pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/65 "2022-10-11T15:45:14Z")
If you want a tool for the database (which is currently Linux-based hosts only), I have one.
Playing with cache size is a bandaid for another problem. The problem should be fixed instead of applying more bandaids.
The tool I have corrected several problems on a machine with only 1GB of RAM.
If PMS runs fine there, all of the above is moot.
---
### Author:  [@jasonsansone](https://forums.plex.tv/u/jasonsansone)
#### Post date: [October 11, 2022, 4:05pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/66 "2022-10-11T16:05:48Z")
I’m sure we would all appreciate reviewing your script. Thank you.
---
### Author:  [@Volts](https://forums.plex.tv/u/Volts)
#### Post date: [October 11, 2022, 4:27pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/67 "2022-10-11T16:27:32Z")
Chuck, can you poke somebody about modifying the default database page size?
It’s currently `1024` which is SO OLD. Like dawn-of-SQLite old, back when block devices had such small blocks, dinosaurs roamed the earth, and you and I were learning about computers. Even wildly-conservative SQLite has defaulted to `4096` forever, and they target literal embedded systems.
I’ve been using bigger values forever, but `4096` (at least) is a no-brainer.
(The template database file that ships with Plex is `1024`. That’s also the right place to modify it on a brand new system. Before first run, the other files don’t exist.)
I’d be happy to grab some links and resources and even some generic benchmarks if that would be helpful for a discussion.
---
### Author:  [@ChuckPa](https://forums.plex.tv/u/ChuckPa)
#### Post date: [October 11, 2022, 4:32pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/68 "2022-10-11T16:32:45Z")
@Volts
1. I **ABSOLUTELY** will poke folks about updating the page size.
2. We need a very well-defined and objective demonstration which includes PMS operating in both large and small (1 GB total system RAM) environments).
3. We further need to show how the different tables and indexes react to those changes.
With that in hand, I seriously doubt they will flinch at making changes.
---
### Author:  [@ChuckPa](https://forums.plex.tv/u/ChuckPa)
#### Post date: [October 11, 2022, 4:38pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/69 "2022-10-11T16:38:05Z")
Here is my tool.
1. Stop PMS
2. Run as root
3. If you want the most expedient, I recommend actions:
- Check
- Repair
- Reindex
1. If you want to examine the individual facets,
- Check
– `ls -la` the database sizes before starting
- Vacuum
– Now `ls -la` of the databases again
- Reindex
– One last check
Feel free to check the DB sizes between each step.
The only conflict possible is if PMS is running and you attempt to use the tool AFTER PMS starts.
This tool only checks for PMS at first startup so be careful.
[DBRepair.tar](https://forums.plex.tv/uploads/short-url/kmxxkBt6r62woKQCm7hEhPUSPK4.tar) (40 KB)
I typically see problems with PMS when:
1. There is small, but non-fatal, database damage
2. The WAL & SHM files do not fold back into the main DB at PMS exit (which it ALWAYS should)
NOTE:
I’ve added an extra couple checks based on feedback received.
Please let me know if you get errors before getting to the main menu.
15-Oct-2022
Restructured Docker test.
---
### Author:  [@Volts](https://forums.plex.tv/u/Volts)
#### Post date: [October 11, 2022, 4:43pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/70 "2022-10-11T16:43:48Z")
Thanks! I’ll gather some references.
And I understand that you target relatively small systems. It’s funny how these days 1 GB is considered small-ish.
(The fact the template database’s page\_size is `1024` is probably a really neat indication that the SAME file has been used by Plex for a very, very long time. That’s kinda cool history.)
---
### Author:  [@ChuckPa](https://forums.plex.tv/u/ChuckPa)
#### Post date: [October 11, 2022, 4:45pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/71 "2022-10-11T16:45:18Z")
> [@Volts](#):
>
> It’s funny how these days 1 GB is considered small-ish.
ABSOLUTELY!
1. My first machine had 4K RAM
2. The first multi-user machine I used (and help build) had 128KB RAM.
DA\*&@#$&@#%$ PROGRAMMERS! BLOAT BLOAT BLOAT!!!
🤣
---
### Author:  [@Tangs](https://forums.plex.tv/u/Tangs)
#### Post date: [October 11, 2022, 5:07pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/72 "2022-10-11T17:07:52Z")
Then i’ll not use this script, but I reaally hope that Plex will give more space for cache, because obviously this has become ridiculsly little in 2022 🙂
---
### Author:  [@ChuckPa](https://forums.plex.tv/u/ChuckPa)
#### Post date: [October 11, 2022, 5:25pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/73 "2022-10-11T17:25:53Z")
@Tangs
I would like to suggest:
1. Use my tool (script)
2. REPAIR the DB (which does an export/import
3. REINDEX
4. Now start PMS and let me know how much quicker it is
(You speak of cache size , yes that’s one issue, locality of reference in a flat-file database is the bigger issue here. Export/Import makes the tables contiguous again )
If you have a ‘larger’ DB, I’m 100% certain the internal links are splattered all over the place. This action fixes that.
---
### Author:  [@jasonsansone](https://forums.plex.tv/u/jasonsansone)
#### Post date: [October 11, 2022, 5:37pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/74 "2022-10-11T17:37:42Z")
For my education, how is import/export different from performing a vacuum? I was under the impression that VACCUM [rebuilt the database](https://www.sqlite.org/lang_vacuum.html) and [aligns table data to be contiguous](https://www.tutorialspoint.com/sqlite/sqlite_vacuum.htm).
---
### Author:  [@ChuckPa](https://forums.plex.tv/u/ChuckPa)
#### Post date: [October 11, 2022, 6:40pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/75 "2022-10-11T18:40:40Z")
From what I understand, you’re correct for default SQLite.
Engineering took SQLite3 source and added extra modules to it to create “Plex SQLite”
In all I’ve tested, VACUUM never does as well as export/import.
(My largest test was a 340K TV episode database)
---
### Author:  [@jasonsansone](https://forums.plex.tv/u/jasonsansone)
#### Post date: [October 11, 2022, 6:42pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/76 "2022-10-11T18:42:30Z")
Thank you for the expalanation!
---
### Author:  [@shark2k](https://forums.plex.tv/u/shark2k)
#### Post date: [October 12, 2022, 1:32am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/77 "2022-10-12T01:32:21Z")
Hey @ChuckPa,
I realize there are always risks associated with doing stuff like your script (big or small).
Just curious, what are the potential risks with running your script?
Also, for Docker installs, would the script be run on the host where the data is persisted too since you say to stop PMS?
I’m interested in trying to run this but want to make sure I do it properly for my system. I quickly looked through the script and even though bash scripting isn’t my forte, I am able to understand enough programming/logic to figure out what is going on.
Thanks!
-Shark2k
---
### Author:  [@ChuckPa](https://forums.plex.tv/u/ChuckPa)
#### Post date: [October 12, 2022, 5:59am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/78 "2022-10-12T05:59:34Z")
I make backups of the database at each step along the way.
Each backup copy is placed in a subdirectory named `dbtmp`
Each backup uses a time stamp so you can see the chronology.
The Logfile (you can display while still in the tool as well) will let you see the steps you took
The Logfile persists after tool exit.
When you exit, you have the option of purging the intermediate backup databases you’ve accumulated along the way.
If you keep them , you can recover to any point in time you wish without data loss.
I put in all the precautions because I know how tired I get and want my \*@#$@# covered 🙂
---
### Author:  [@ChuckPa](https://forums.plex.tv/u/ChuckPa)
#### Post date: [October 12, 2022, 6:13am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/79 "2022-10-12T06:13:35Z")
ALL:
Based on feedback, I added an extra precautionary test in the startup.
If permissions aren’t adequate, it’ll report so and exit.
This should prevent it from spitting errors when trying to initialize.
Please let me know if any other annoyances are detected.
Presumption is:
1. UID/GID of the invoking user matches the PMS user
-or-
2. UID/GID of the invoking user is ‘root’ (0:0)
Please remember, this tool exists solely to assist and save all the typing which reduces the risk of error which really can destroy databases.
---
### Author:  [@shark2k](https://forums.plex.tv/u/shark2k)
#### Post date: [October 13, 2022, 1:28am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/80 "2022-10-13T01:28:08Z")
Thanks for reassurance on running the script.
Regarding your last post, is there a new link to download the latest version with the extra precautionary test you added or is the same link earlier up in the thread updated?
Thanks,
-Shark2k
---
### Author:  [@ChuckPa](https://forums.plex.tv/u/ChuckPa)
#### Post date: [October 13, 2022, 1:52am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/81 "2022-10-13T01:52:45Z")
No new link. I updated the post.
[Previous page](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749.md?page=3)
[Next page](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749.md?page=5)