# 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:** 2

<div class="post-metadata">

**Author:** ![Volts](https://avatars.discourse-cdn.com/v4/letter/v/a5b964/32.png) [@Volts](https://forums.plex.tv/u/Volts)\
**Post date:** [May 31, 2022, 10:35pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/21 "2022-05-31T22:35:45Z")

</div>

> [@JDA88](#):
>
> My page size was already at 4096, I’ll see if 65536 is any better.

Ohhhh? That’s interesting.

The same binary hack can be made to the Windows exe. `Sed` exists for windows, and there are lots of other binary-friendly editors.

Note that a larger page\_size will itself increase the amount of memory used for cache. Page size is the single biggest help anyway. Plus it doesn’t need to be performed for each upgrade.

---

<div class="post-metadata">

**Author:** ![jasonsansone](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/jasonsansone/32/291100_2.png) [@jasonsansone](https://forums.plex.tv/u/jasonsansone)\
**Post date:** [June 1, 2022, 12:30am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/22 "2022-06-01T00:30:24Z")

</div>

> [@JDA88](#):
>
> My page size was already at 4096, I’ll see if 65536 is any better.

Plex offers server downloads for Windows, macOS, FreeBSD, and Linux. I decided to test them all with fresh installs of 1.27.0.5849-99e933842 (Update: I skipped FreeBSD after finding consistent results on the other operating systems). All values below are for “PRAGMA page\_size”.

**macOS:**  
DB = 1024  
BLOBS.DB = 1024

**Windows:**  
DB = 1024  
BLOBS.DB = 1024

**Linux (Ubuntu 22.04):**  
DB = 1024  
BLOBS.DB = 1024

---

<div class="post-metadata">

**Author:** ![Volts](https://avatars.discourse-cdn.com/v4/letter/v/a5b964/32.png) [@Volts](https://forums.plex.tv/u/Volts)\
**Post date:** [June 1, 2022, 1:09am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/23 "2022-06-01T01:09:04Z")

</div>

FreeBSD is **1024** as well.

And unless Plex is doing something really surprising, every other platform too.

If they don’t exist when Plex starts, the active database files are cloned from the distribution template file `Resources/com.plexapp.plugins.library.db`, which is **1024**.

---

<div class="post-metadata">

**Author:** ![JDA88](https://avatars.discourse-cdn.com/v4/letter/j/94ad74/32.png) [@JDA88](https://forums.plex.tv/u/JDA88)\
**Post date:** [June 1, 2022, 4:07am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/24 "2022-06-01T04:07:26Z")

</div>

> [@Volts](#):
>
> Ohhhh? That’s interesting.

Sorry for the confusion, it was 4096 because I changed it a few year ago after a reddit post made the same discovery and sugjested changing the value.

> [@Volts](#):
>
> The same binary hack can be made to the Windows exe. `Sed` exists for windows, and there are lots of other binary-friendly editors.

I’m confortable enough to edit the database, but not to alter the binaries, Plex have enough crash cases already, if i do change something in the binaries everytime it will have a problem I will be wundering if it was my fault 🙂

---

<div class="post-metadata">

**Author:** ![Volts](https://avatars.discourse-cdn.com/v4/letter/v/a5b964/32.png) [@Volts](https://forums.plex.tv/u/Volts)\
**Post date:** [June 1, 2022, 4:35am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/25 "2022-06-01T04:35:51Z")

</div>

> [@JDA88](#):
>
> I’m confortable enough to edit the database, but not to alter the binaries

That is a reasonable and prudent position. 🙂

---

<div class="post-metadata">

**Author:** ![exuberantShadow](https://avatars.discourse-cdn.com/v4/letter/e/bbce88/32.png) [@exuberantShadow](https://forums.plex.tv/u/exuberantShadow)\
**Post date:** [June 1, 2022, 7:24am UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/26 "2022-06-01T07:24:20Z")

</div>

I have never actually gone beyond page sizes of 4k or 8k (with the block size of the filesystem at 4k), since at that point it starts slowing down writes enough to not be worth it anymore.

I would think the ideal value of `page_size` should be the same (or at most 2x of the value) as the block size of the filesystem without unduly effecting writes.

From [https://phiresky.github.io/blog/2020/sqlite-performance-tuning/:](https://phiresky.github.io/blog/2020/sqlite-performance-tuning/:)

```auto
useful if you are storing somewhat large blobs in your database and might not be good for other 
projects where rows are small. For writing queries SQLite will always only replace whole pages, so
this increases the overhead of write queries.

```

Increasing `cache_size` to `512M` or even `1024M`, on the other hand, should give a significant improvement.

* * *

Thread from Emby when they made these options configurable last year and the corresponding tests with various options & their results: [https://emby.media/community/index.php?/topic/96065-performance-improvements-for-large-databases/](https://emby.media/community/index.php?/topic/96065-performance-improvements-for-large-databases/)

---

<div class="post-metadata">

**Author:** ![jasonsansone](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/jasonsansone/32/291100_2.png) [@jasonsansone](https://forums.plex.tv/u/jasonsansone)\
**Post date:** [June 1, 2022, 1:02pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/27 "2022-06-01T13:02:57Z")

</div>

> [@exuberantShadow](#):
>
> [https://emby.media/community/index.php?/topic/96065-performance-improvements-for-large-databases/](https://emby.media/community/index.php?/topic/96065-performance-improvements-for-large-databases/)

Link requires user sign-in.

> [@exuberantShadow](#):
>
> Increasing `cache_size` to `512M` or even `1024M` , on the other hand, should give a significant improvement.

As a reminder for anyone else that decides to try this, the value for [cache\_size](https://www.sqlite.org/pragma.html#pragma_cache_size) is in kibibytes.

512MB = 500000 KiB

EDIT: As mentioned, a positive value defines total page count. A negative value defines a target size of cache. The old default was 2000 pages which it appears Plex has simply never updated.

`*Backwards compatibility note:* The behavior of cache_size with a negative N was different prior to [version 3.7.10](https://www.sqlite.org/releaselog/3_7_10.html) (2012-01-16). In earlier versions, the number of pages in the cache was set to the absolute value of N.`

The default changed in 2012 to -2000 to define cache\_size as 2000KiB instead of 2000 pages. However, since Plex is still using a positive integer, the cache size will be pages (2000) \* page\_size (default of 1024). This has been stated several times by @Volts. If you crank up cache\_size to 9999 and increase the page\_size to the SQLite recommended default of 4096, you have increased your memory footprint to a **whopping** 40M. A max setting of 9999 \* 65536 will still only consume ~655MB.

---

<div class="post-metadata">

**Author:** ![kazz3r24](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/kazz3r24/32/86318_2.png) [@kazz3r24](https://forums.plex.tv/u/kazz3r24)\
**Post date:** [June 1, 2022, 1:22pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/28 "2022-06-01T13:22:05Z")

</div>

Not sure if this is a “if you don’t know, don’t do it” type of thing, but I’ve managed to change my page size, just unsure of where the cache\_size portion needs to be executed from? /usr/lib/plexmediaserver/ ?

Thank you in advance, this thread has been a great read!

---

<div class="post-metadata">

**Author:** ![exuberantShadow](https://avatars.discourse-cdn.com/v4/letter/e/bbce88/32.png) [@exuberantShadow](https://forums.plex.tv/u/exuberantShadow)\
**Post date:** [June 1, 2022, 1:23pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/29 "2022-06-01T13:23:47Z")

</div>

> [@jasonsansone](#):
>
> decides to try this

It won’t be possible to try this right now with binary editing since the number of characters need to be same while replacing so the most you can try is `9999` (positive value means pages) or `-999` (negative meaning kibibytes).

---

<div class="post-metadata">

**Author:** ![exuberantShadow](https://avatars.discourse-cdn.com/v4/letter/e/bbce88/32.png) [@exuberantShadow](https://forums.plex.tv/u/exuberantShadow)\
**Post date:** [June 1, 2022, 1:30pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/30 "2022-06-01T13:30:51Z")

</div>

The above mentioned command should work when executed from `/usr/lib/plexmediaserver/`:

> [@Volts](#):
>
> ```auto
> sed -i.orig \
> -e 's/cache_size=2000/cache_size=9999/' \
> 'Plex Media Server'
> 
> ```

If executed from elsewhere, you need to specify the complete path of the executable like `/usr/lib/plexmediaserver/Plex Media Server`

---

<div class="post-metadata">

**Author:** ![kazz3r24](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/kazz3r24/32/86318_2.png) [@kazz3r24](https://forums.plex.tv/u/kazz3r24)\
**Post date:** [June 1, 2022, 1:31pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/31 "2022-06-01T13:31:50Z")

</div>

Great, thank you very much!

---

<div class="post-metadata">

**Author:** ![jasonsansone](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/jasonsansone/32/291100_2.png) [@jasonsansone](https://forums.plex.tv/u/jasonsansone)\
**Post date:** [June 1, 2022, 1:35pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/32 "2022-06-01T13:35:05Z")

</div>

> [@Volts](#):
>
> `-e 's/cache_size = 4096/cache_size = 9999/'`

As mentioned by @gbooker02, that value is for iTunes and doesn’t really need to be changed.

> [@gbooker02](#):
>
> The other page size mentioned found in the code is for connecting to the iTunes database so not really applicable to this discussion.

---

<div class="post-metadata">

**Author:** ![jasonsansone](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/jasonsansone/32/291100_2.png) [@jasonsansone](https://forums.plex.tv/u/jasonsansone)\
**Post date:** [June 1, 2022, 1:37pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/33 "2022-06-01T13:37:58Z")

</div>

It is also worth mentioning the obvious:

Any binary changes won’t survive Plex updates unless you script for sed to run after each update.

---

<div class="post-metadata">

**Author:** ![kazz3r24](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/kazz3r24/32/86318_2.png) [@kazz3r24](https://forums.plex.tv/u/kazz3r24)\
**Post date:** [August 5, 2022, 9:51pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/34 "2022-08-05T21:51:52Z")

</div>

So far it looks like the changes have been surviving updates. I run a pragma check after every update and the page size has stayed at 65536 each time. I’ve only ever had to run the tweaks once when I first did it.

Overall client performance has been great, items on pages load almost instantly!

---

<div class="post-metadata">

**Author:** ![DeBoet](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/deboet/32/160675_2.png) [@DeBoet](https://forums.plex.tv/u/DeBoet)\
**Post date:** [September 29, 2022, 3:33pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/35 "2022-09-29T15:33:40Z")

</div>

Following this with great interest, anything that boosts performance is great in my book… At least my 64 GB will get some usage out of them!

Although, I jumped in rather too quick, and found out I can’t edit the settings from the .db in windows, and this sed tool isn’t exactly self explanatory, is there a way to use this on Windows (Server) and to set those values there?

edit: managed to edit in the new cache\_size, although most PRAGMA values can’t be adapted/changes/refuse to save :\

---

<div class="post-metadata">

**Author:** ![silenceheaven](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/silenceheaven/32/107596_2.png) [@silenceheaven](https://forums.plex.tv/u/silenceheaven)\
**Post date:** [September 29, 2022, 3:59pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/36 "2022-09-29T15:59:25Z")

</div>

Low effort bash script I threw together for the binary modifications:

```auto
#!/bin/bash

# Parameters are for linuxserver/plex containers
# Adjust for other container distributions
# https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/2

CONTAINER="plex"
SHELL="/bin/bash"
PLEX_DATA="/config/Library/Application Support/Plex Media Server"
STOP_PLEX="s6-svc -d /run/service/service-plex"
START_PLEX="s6-svc -u /run/service/service-plex"

####################################

# Check if PMS binary has original cache_size value
CACHESIZE=$(docker exec $CONTAINER $SHELL -c "tr -d '\0' < /usr/lib/plexmediaserver/Plex\ Media\ Server | grep -a 'cache_size=2000'")

if [! -z "$CACHESIZE"]; then
        echo "Modifications needed"
        docker exec $CONTAINER $SHELL -c "sed -i.orig -e 's/cache_size=2000/cache_size=8192/' '/usr/lib/plexmediaserver/Plex Media Server'"
fi

# Stop Plex instance within container
docker exec $CONTAINER $SHELL -c "$STOP_PLEX"

# Wait for Plex service to stop
sleep 6

# Update SQLite DB
docker exec $CONTAINER $SHELL -c "/usr/lib/plexmediaserver/Plex\ SQLite $PLEX_DATA/Plug-in\ Support/Databases/com.plexapp.plugins.library.db 'PRAGMA page_size=65536' 'PRAGMA journal_mode=DELETE' 'VACUUM' 'PRAGMA journal_mode=WAL' 'PRAGMA optimize'"
docker exec $CONTAINER $SHELL -c "/usr/lib/plexmediaserver/Plex\ SQLite $PLEX_DATA/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"

# Start Plex instance within container
docker exec $CONTAINER $SHELL -c "$START_PLEX"

```

---

<div class="post-metadata">

**Author:** ![silenceheaven](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/silenceheaven/32/107596_2.png) [@silenceheaven](https://forums.plex.tv/u/silenceheaven)\
**Post date:** [September 29, 2022, 4:03pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/37 "2022-09-29T16:03:29Z")

</div>

You could use [https://sqlitebrowser.org/](https://sqlitebrowser.org/) to modify the database in Windows without using the command line.

You might be able to use Set-Content in PowerShell [Set-Content (Microsoft.PowerShell.Management) - PowerShell | Microsoft Learn](https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.management/set-content?view=powershell-7).

---

<div class="post-metadata">

**Author:** ![DeBoet](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/deboet/32/160675_2.png) [@DeBoet](https://forums.plex.tv/u/DeBoet)\
**Post date:** [September 29, 2022, 4:05pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/39 "2022-09-29T16:05:23Z")

</div>

I’ve tried the sqlitebrowser, changing the PRAGMA values mentioned above is impossible, it refuses to save and returns to default values when I re-open the program.

---

<div class="post-metadata">

**Author:** ![anon5074910](https://avatars.discourse-cdn.com/v4/letter/a/db5fbb/32.png) [@anon5074910](https://forums.plex.tv/u/anon5074910)\
**Post date:** [September 29, 2022, 4:07pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/40 "2022-09-29T16:07:02Z")

</div>

> [@silenceheaven](#):
>
> You could use [https://sqlitebrowser.org/](https://sqlitebrowser.org/) to modify the database in Windows without using the command line.

That depends on the table your editing. Example, metadata\_items won’t edit correctly so always best to use sqlite from plex itself.

---

<div class="post-metadata">

**Author:** ![silenceheaven](https://sea1.discourse-cdn.com/plex/user_avatar/forums.plex.tv/silenceheaven/32/107596_2.png) [@silenceheaven](https://forums.plex.tv/u/silenceheaven)\
**Post date:** [September 29, 2022, 4:07pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/41 "2022-09-29T16:07:26Z")

</div>

Did you turn off the Plex server first?

[Previous page](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749.md?page=1)

[Next page](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749.md?page=3)