With the inclusion of the server setting Database Cache Size (MB) will plex be supplying a support doc on how best to tune this for admins and their specific system ?
I have my own opinions for my system however, I’d like to see what plex recommends for any given system and its resources.
BTW… thanks for adding this 
Oh, that’s neat to add as well.
I just adjusted mine to 2GB to see how it works as the default was 40MB.
I think 2 GB is way overboard.
It’s not that you have to set it equivalent to the size of your DB file.
Try to raise it in – let’s say – 200 MB steps up from the default. Compare performance before and after.
You won’t see much difference in daily use. Only certain actions might profit from it.
I assume some complex filters in smart collections might be a good test case.
But in general, your precious RAM is better invested in file read cache.
To your point, I would not have a good way to benchmark it as generally, my DB is on NVME and with 32GB, 25GB is already being used for file system cache so losing 2GB for this really won’t impact anything in that realm.
My DB is:
696M com.plexapp.plugins.library.db
I’ll leave it at 1G and see if I can notice any difference…
Before everyone gets on the craze to mess with sizes again.
(With PMS stopped, running as root)
Run options: 1 - 4 - 3
Give this a shot.
The best way to test the impact is has is to run a search after completing this optimization sequence.
PMS gets slow because of the internal links / chains SQLite creates.
It takes time to walk it. When you compact the DB (option 4 above) and place the records adjacent each other, it gets profoundly quicker
Chuck, if it has such an impact, why is something like this not integrated into PMS and run as part of database optimization? Everyone should be able to benefit, not just those on Linux and comfortable with the command line.
Word to @animosity022 
It can’t be built into PMS because this level of optimization can only be performed when there is no other activity. There’s no way to NOT have PMS in the DB.
The entire database is exported to SQL source.
A new database is then created, in perfected sorted order, and put in place of the old one.
Vacuum is the only optimization which PMS can perform while it’s open and that isn’t a “real” vacuum.
I was under the impression that the current built-in optimization stops any activity and locks the database until it is done. Would that not be enough?
Nope… Not even close.
Makes sense. I’m really just asking out of curiosity. I don’t have any database issues (that I’m aware of), even though my library size is quite substantial compared to the average user (not @jaquestati )
I wrote this because it was always a PITA and error prone to help folks really optimize AND fix their databases.
The tool is nothing more than that sequence of commands , with host detection built in, as well as a few other things to improve PMS operation on the host.
Two recent cases are:
PMS would randomly crash – no reasons anywhere – just crash but always in the database.
– We did the repair and reindex
– Hasn’t crashed since
PMS would take 45-60+ seconds to load the posters when opening the library section.
– Did the same repair and reindex operation (aka. EXPORT/IMPORT + reindex)
– Start PMS
– Open section – Posters loaded in about 5 seconds … it was profound.
If you read the other things I’ve done in there, you’ll get a feel why it exists.
Yeah, I’ve read them all. Great support!
Give it a try. I think you’ll like what it does for you.
Did you see the README ?
Thanks @ChuckPa @OttoKerner both useful pieces of information and I’ll go playing around. I’ve noticed since I started to add a fairly large music library (currently just shy of 60k tracks in plex) that search has slowed and the overall db has slowed as its grown (and yes I expect this to some extent). Once I finally finish getting everything into plex it will be more like 200k tracks
Anyhow, I guess I was more thinking plex might give some guidelines on this cache. Finger in the air I was hoping something along the lines of …
I picked metadata_items as the example as its the table which contains the list of playable items in plex, be it audio, video or image.
From what I remember in the past this cache_size was 20mb now has a default of 40mb. 2GB seemed high to jump to initially and was thinking myself 256mb and then increases of 256mb up to 1024. This of course is assuming no negative impact on the db.
EDIT: Doing 1 - 4 - 3 from the tool above certainly has helped a wee bit so thanks for that. However, while related its not exactly what I was hoping for from this thread but lets see over time what folks find with adjusting this cache.
2022-12-13 19.22.15 - Repair - Verify main database - PASS (Size: 514MB/476MB).
2022-12-13 19.22.15 - Repair - Verify blobs database - PASS (Size: 1MB/1MB).
So you got an almost 20% reduction in DB size beyond what PMS can give you natively.
When you have 60K tracks of music, that’s just a lot of **** to Shovel
LOL LOL
What did you observe when trying a search?
Can’t. I’m on Windows, remember?
Anyway, like @animosity022, I changed the cache size to 1024 earlier, then restarted PMS. Do you have any insight as to how this change is reflected? I want to say the RAM usage of the PMS executable went up, but it seems dynamic, since it doesn’t go to a gig immediately. Does that sound right?
Search appears fine from the limited testing I’ve had time to do so far. It feels a little snappier but to be honest that could be a placebo effect.
What is noticeable is running optimise database from troubleshooting. That now completes in 25 seconds whereas before it was taking over 60.
Going to bump db cache to 128mb and will see if there is any noticeable effects. If not I’ll just roll back to the default.
While suspicious the search is ‘snappier’ as a placebo effect, we measured it.
There was one where search needed 11 seconds to complete.
After repacking and reindexing, the search was spitting answers as we were typing.
“Optimize”, which is “vacuum” in SQLite terms, is showing what I expect.
Oh well, that’s what you get for using a MickeyMSDOS operating system 
You can run the commands it does by hand if you want to hand script it into a bat file.
I’ll help you with that.
For us, that are currently on the cursed Win1x - my ears went slightly up, when you mentioned some assistance to a little handscripting!
BAT or powershell - i’m all ears for this one 
