Edit 05-31-2022: PRAGMA synchronous is already NORMAL as confirmed below. Locking_mode exclusive canāt be used due to multiple binaries accessing database.
I donāt think Plex uses TEMPORARY tables directly.
Are there transient objects this would benefit? Complicated joins that use temporary indexes? Do you know how to find out?
SQLite uses cache_size*page_size = memory for caching. And page_size - the next item on your list - is weirdly small. So the result is very small.
I expect moderate increases to help moderately. I donāt expect huge increases to help hugely.
This is easy to benchmark synthetically. Do you have ideas for how to benchmark this in Plex?
Binary hacking is fun:
sed -i.orig \
-e 's/cache_size=2000/cache_size=8192/' \
-e 's/cache_size = 4096/cache_size = 8192/' \
'Plex Media Server'
100% agreed. This is an obvious change.
1024 is anachronistic in 2022, and I canāt think of any reason for the ancient value.
For anybody reading along, hereās how to increase page_size of an existing SQLite db file:
# Show the original page_size
"Plex SQLite" com.plexapp.plugins.library.db "PRAGMA page_size"
# Set the desired page_size,
# disable WAL,
# VACUUM to rewrite the pages
# re-enable WAL (not strictly necessary, Plex will do this)
"Plex SQLite" com.plexapp.plugins.library.db "PRAGMA page_size=4096" "PRAGMA journal_mode=DELETE" "VACUUM" "PRAGMA journal_mode=WAL"
# Show the new page_size
"Plex SQLite" com.plexapp.plugins.library.db "PRAGMA page_size"
4096 is still very conservative and is a significant improvement. Larger values can help too but might be filesystem-dependent.
I donāt think this is viable, Plex uses a connection pool.
Does it help much when in WAL mode? I havenāt tested in forever.
And it would block direct updates, which would be annoying.
This is interesting!
This might help big databases on fast storage. Would it help the āaverageā user much?
mmap would be scary from Plexās perspective. The variety of operating systems and filesystems, the different failure modes from mmap. People complaining about mmap āusing too much memoryā.
I checked all of the values for my db before posting. I showed that Plex is using synchronous=FULL. If the mode is full, there is no reason to not change to normal. As the docs clearly state, there is no risk of corruption when using a write ahead log. The worst case scenario is a few in flight commits are rolled backā¦ so you lose your play status? Anything the WAL rolls back is quickly fixed.
Regarding the locking mode, I āthinkā it can still use a connection pool because all calls to the database are from one binary. It isnāt being accessed by any other user or program. However, as you mentioned, it would block any queries from anything but the Plex binary. This is the least likely to be adopted.
For all of these settings, especially MMAP, they could easily become an advanced hidden user configurable. Make the default MMAP be 0 but allow the preferences.xml (or another new file which can be read by the binary at start-up) to define the value. If you are on an RPi with no memory to spare you are golden but if you have 256GB of memory and want to throw a few gigabytes at Plex you can.
I agree that synchronous=NORMAL seems like the right choice.
But synchronous is a connection-specific parameter. It isnāt saved in the DB file and canāt be queried from it.
My circumstantial evidence is that the Plex Media Server binary contains the string āPRAGMA synchronous=NORMALā. I donāt know if itās used consistently.
I agree with that. It should be true that bigger pages are good on modern journaling filesystems, especially those with compression.
Iām agreeing that 4K would be better than 1K on any system where Plex might run.
But Iām not confident bigger-than-4K is always better than 4K. Itās easy to find older synthetic benchmarks where 4K is the sweet spot.
Iām personally using larger SQLite pages on ZFS.
Thatās interesting. I would have bet you a donut it was per connection. Plex is using FULLMUTEX / serialized mode, but maybe thatās orthogonal to locking.
Considering thereās a connection pool, Iāve been wondering how much SQLiteās shared-cache mode would help.
This seems really stupid but might actually work. It doesnāt immediately explode, anyway. I have a couple ideas how to validate it but they would take effort.
sed -i.orig \
-e 's/SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_FULLMUTEX/SQLITE_OPEN_READWRITE |SQLITE_OPEN_CREATE |SQLITE_OPEN_SHAREDCACHE/g' \
'Plex Media Server'
This (all calls are from one binary) isnāt true. The Plex Media Scanner is run as a separate process and it connects to the same database. There are some other cases where a separate process connects to the same db.
For reference, the setup of all db connections to Plexās database go through code that performs:
The other page size mentioned found in the code is for connecting to the iTunes database so not really applicable to this discussion.
I expect that increasing the cache size can increase performance but it would also mean an increase in RAM usage. With a connection pool of around 20 connections, this can potentially add to a large increase so the question is where is the point of diminishing returns and thus where the two should be balanced?
Using 's/SQLITE_OPEN_FULLMUTEX/0x00010000|0x00020000/' (since I understood FULLMUTEX & SHAREDCACHE as complementary parameters) didnāt make any noticeable difference when I tried it a few weeks.
Excellent information. I (and I am sure others) appreciate the information and insight from the Plex team. Any public or community response, even one users may not like, is welcomed. Thank you!
I didnāt consider the additional binaries.
From the Plex team perspective, I realize you must create a product that is used across thousands (millions?) of installations with vastly different resources, designs, etc. However, unused RAM is wasted RAM. I personally have 256GB DDR4 on each of my Plex installations so I would love to leverage more cache in memory. There is never a one-size-fits-all, so why not make this a tunable for power users? The only true answer for where it should be balanced is dependent on the available memory in the system. Would it be too hard to code the value to pull in from an external config file prior to establishing the database connection? Same with regards to MMAP. The binary already has to read the advanced settings, but I am not sure if that occurs before or after the DB connection is established. If the DB connection is established before the binary reads the advanced settings, that solution wonāt work.
However, changing the default page_size is the biggest (and easiest) change which will see immediate benefits to new installations. The SQLite default changed in 2016. I realize a user can change this on their end (as @Volts and I have done), but I suspect we are in the minority. Is there a reason for the 2003 default of 1024 or simply an oversight of time?
Thatās beautiful. I counted the characters in my head, switched to a monospaced font, smiled.
I agree they are complementary; I believe FULLMUTEX to be a default.
Have you also increased page and cache sizes?
I also havenāt measured a difference. If connections to the pool are mostly static or always used from the top, this wouldnāt help performance much anyway. If connections are shuffled this might help more.
Yep, this is along with page and cache size changes. Both of those together seem to have made a difference in terms of the logs regarding locked DB.
I added SHAREDCACHE in hopes of seeing stable or atleast lower memory usage but the graphs over a few days seemed to be similar to another instance with the same media but without these flags.
I also noticed an occasional locked DB during the backup task in the week or so that I had SHAREDCACHE flag enabled which I hadnāt seen before and havenāt seen since removing it so figured the flag was the cause.
Both of the above reasons led me to remove those flags from any further testing.
I personally havenāt tested the connection changes, but I did change cache_size from 2000 to 8192 and page_size from 1024 to 65536. I havenāt run any benchmarks, but there is a very perceivable improvement on the client front end.
FYI - I know the canned response to āI want to speed up the databaseā is to add faster storage. MY database is already on a cluster of enterprise NVMe.
Cache_size is set at the time of initiating a db connection, in this instance by the Plex binary. You canāt change it directly on your database like the page_size. In order to change the cache_size you have to edit the Plex binary.
Ho, I see, canāt really mess with this then; (Runing Windows version). Thanks for the information! My page size was already at 4096, Iāll see if 65536 is any better.
Plex is using 
