# 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:** 1\
**Showing post:** 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 30, 2022, 7:36pm UTC](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749/2 "2022-05-30T19:36:00Z")

</div>

> [@](#):
>
> - [PRAGMA synchronous](https://www.sqlite.org/pragma.html#pragma_synchronous) from FULL to NORMAL. Note: The DB is already in WAL mode.

I believe Plex uses `synchronous=NORMAL`.

> [@](#):
>
> - [PRAGMA temp\_store](https://www.sqlite.org/pragma.html#pragma_temp_store) from 0 to 2.

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?

> [@](#):
>
> - [Cache size](https://www.sqlite.org/pragma.html#pragma_cache_size) (not default\_cache\_size) enlarged.

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:

```auto
sed -i.orig \
 -e 's/cache_size=2000/cache_size=8192/' \
 -e 's/cache_size = 4096/cache_size = 8192/' \
 'Plex Media Server'

```

> [@](#):
>
> - [Page\_size](https://www.sqlite.org/pragma.html#pragma_page_size) enlarged from 1024 (which is the old default but the current SQLite3 default is 4096)

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:

```auto
# 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.

> [@](#):
>
> - [PRAGMA locking\_mode](https://www.sqlite.org/pragma.html#pragma_locking_mode) from normal to exclusive.

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. 🙂

> [@](#):
>
> - [PRAGMA mmap\_size](https://www.sqlite.org/pragma.html#pragma_mmap_size) from 0 to anything.

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”.

---

_[View the full topic](https://forums.plex.tv/t/suggested-sqlite3-db-optimizations/794749)._