[Enhancement]: Allow adjustment of SQLITE PRAGMA values #2437

New Issue

2026-04-25T00:07:08+02:00

adam commented

2026-04-25 00:07:08 +02:00

Originally created by @PhantomPhoton on GitHub (Dec 27, 2024).

Type of Enhancement

None

Describe the Feature/Enhancement

I would like to adjust some PRAGMA values for the sqlite database.

Why would this be helpful?

My podcast library is fairly large and it takes approximately 20 seconds to load each "page" on the library view. Adjusting mmap_size to 268435456 (256 megabytes) reduces the latency of the query to approximately 3 seconds per "page". My absdatabase.sqlite file is approximately 150 megabytes.

I've tested this via using the sqlite3 command line which running the query before the change matches pretty closely the latency being reported by AudioBookShelf.

Future Implementation (Screenshot)

Optionally a list of values that would be able to be adjusted or a freeform text box allowing a list of values to be set that is run on every newly created connection. The majority of the PRAGMA values are specific to the connection and won't persist once the connection is closed.

Audiobookshelf Server Version

v2.17.5

Current Implementation (Screenshot)

No response

Originally created by @PhantomPhoton on GitHub (Dec 27, 2024). ### Type of Enhancement None ### Describe the Feature/Enhancement I would like to adjust some [PRAGMA](https://www.sqlite.org/pragma.html) values for the sqlite database. ### Why would this be helpful? My podcast library is fairly large and it takes approximately 20 seconds to load each "page" on the library view. Adjusting [mmap_size](https://www.sqlite.org/pragma.html#pragma_mmap_size) to `268435456` (256 megabytes) reduces the latency of the query to approximately 3 seconds per "page". My `absdatabase.sqlite` file is approximately 150 megabytes. I've tested this via using the `sqlite3` command line which running the query before the change matches pretty closely the latency being reported by AudioBookShelf. ### Future Implementation (Screenshot) Optionally a list of values that would be able to be adjusted or a freeform text box allowing a list of values to be set that is run on every newly created connection. The majority of the PRAGMA values are specific to the connection and won't persist once the connection is closed. ### Audiobookshelf Server Version v2.17.5 ### Current Implementation (Screenshot) _No response_

adam added the enhancement label 2026-04-25 00:07:08 +02:00

adam closed this issue

2026-04-25 00:07:08 +02:00

adam commented

2026-04-25 00:07:09 +02:00

@nichwall commented on GitHub (Dec 27, 2024):

Out of curiosity, what was the mmap size before you changed it?

@nichwall commented on GitHub (Dec 27, 2024): Out of curiosity, what was the mmap size before you changed it?

adam commented

2026-04-25 00:07:09 +02:00

@PhantomPhoton commented on GitHub (Dec 27, 2024):

It's zero, which has mmap disabled.

@PhantomPhoton commented on GitHub (Dec 27, 2024): It's zero, which has mmap disabled.

adam commented

2026-04-25 00:07:09 +02:00

@mikiher commented on GitHub (Dec 29, 2024):

Would it make sense to make some of these PRAGMA values default for everyone? Do you have any suggestions?

@mikiher commented on GitHub (Dec 29, 2024): Would it make sense to make some of these PRAGMA values default for everyone? Do you have any suggestions?

adam commented

2026-04-25 00:07:09 +02:00

@PhantomPhoton commented on GitHub (Dec 29, 2024):

I think ideally a few could make sense.

PRAGMA temp_store = 2

Use memory backed temporary tables rather than writing them to disk. This is less impacting for people with their database on a fast SSD, but depending on the queries, could be impactful for people with a database on a traditional hard drive

PRAGMA cache_size = -some_value

By default, the cache_size is set to -2000, which the cache size is limited to 2048000 bytes of memory. ~2 megabytes isn't that much, my podcastEpisode table is over 80 megabytes, per SELECT name, SUM("pgsize") FROM "dbstat" GROUP BY name I'm using -262144 in my testing (256 megabytes) to match the size of the mmap value I'm using.

PRAGMA mmap_size = 268435456

And of course the mmap value. Strictly speaking, it's best to have a larger mmap value than the size of the database. It basically allows the kernel to bypass a huge amount of layers and speeds up reads and writes to the file dramatically, however, you should have enough free memory to fix it all, as if it starts to swap, you've in a worst performance path then just using the standard file operations.

I ran my library query as a test for different sizes of mmap

mmap_size seconds
        0      22 
 16777216      13 
 33554432      12
 67108864      10
134217728       7
268435456       3

I don't know if the project has a target amount of ram to fit into. If I had to pick, I'd pick a mmap value that is larger then the database first, followed by the memory temp_store and then finally increasing the cache size.

@PhantomPhoton commented on GitHub (Dec 29, 2024): I think ideally a few could make sense. `PRAGMA temp_store = 2` Use memory backed temporary tables rather than writing them to disk. This is less impacting for people with their database on a fast SSD, but depending on the queries, could be impactful for people with a database on a traditional hard drive `PRAGMA cache_size = -some_value` By default, the cache_size is set to `-2000`, which the cache size is limited to 2048000 bytes of memory. ~2 megabytes isn't that much, my podcastEpisode table is over 80 megabytes, per `SELECT name, SUM("pgsize") FROM "dbstat" GROUP BY name` I'm using `-262144` in my testing (256 megabytes) to match the size of the mmap value I'm using. `PRAGMA mmap_size = 268435456` And of course the mmap value. Strictly speaking, it's best to have a larger mmap value than the size of the database. It basically allows the kernel to bypass a huge amount of layers and speeds up reads and writes to the file dramatically, however, you should have enough free memory to fix it all, as if it starts to swap, you've in a worst performance path then just using the standard file operations. I ran my library query as a test for different sizes of mmap ``` mmap_size seconds 0 22 16777216 13 33554432 12 67108864 10 134217728 7 268435456 3 ``` I don't know if the project has a target amount of ram to fit into. If I had to pick, I'd pick a mmap value that is larger then the database first, followed by the memory temp_store and then finally increasing the cache size.

adam commented

2026-04-25 00:07:09 +02:00

@mikiher commented on GitHub (Dec 29, 2024):

Thanks for the suggestions, I will look into these.

In the meantime, will a setting through an environment variable be OK for you? That is easier than adding a full UI (which will also likely not be used by many).

Audiobookshelf opens a single Sequelize instance (which opens connections to SQLite under the hood) that lives throughout the server's lifetime, so PRAGMA settings would be done at initialization and would not change until the server is shutdown.

@mikiher commented on GitHub (Dec 29, 2024): Thanks for the suggestions, I will look into these. In the meantime, will a setting through an environment variable be OK for you? That is easier than adding a full UI (which will also likely not be used by many). Audiobookshelf opens a single Sequelize instance (which opens connections to SQLite under the hood) that lives throughout the server's lifetime, so PRAGMA settings would be done at initialization and would not change until the server is shutdown.

adam commented

2026-04-25 00:07:10 +02:00

@PhantomPhoton commented on GitHub (Dec 29, 2024):

I think environment variables would be perfect. I agree that adding a full UI seems overkill for the feature. Most people won't be changing the values, doubly so if there's some reasonable defaults being set.

@PhantomPhoton commented on GitHub (Dec 29, 2024): I think environment variables would be perfect. I agree that adding a full UI seems overkill for the feature. Most people won't be changing the values, doubly so if there's some reasonable defaults being set.

adam commented

2026-04-25 00:07:10 +02:00

@mikiher commented on GitHub (Jan 3, 2025):

I'm working on some query optimizations. Since you seem to know your way around querying the db, can you please also share the sizes (number oh entries) of your Podcasts and PodcastEpisodes tables?

@mikiher commented on GitHub (Jan 3, 2025): I'm working on some query optimizations. Since you seem to know your way around querying the db, can you please also share the sizes (number oh entries) of your Podcasts and PodcastEpisodes tables?

adam commented

2026-04-25 00:07:10 +02:00

@PhantomPhoton commented on GitHub (Jan 5, 2025):

717 Podcasts with 35204 Episodes.

@PhantomPhoton commented on GitHub (Jan 5, 2025): 717 Podcasts with 35204 Episodes.

adam commented

2026-04-25 00:07:10 +02:00

@PhantomPhoton commented on GitHub (Jan 21, 2025):

So I patched in some hardcoded pragma values in Database.js

      Logger.info(`[Database] Setting PRAGMA values`)
      await this.sequelize.query('PRAGMA temp_store   = 2');
      await this.sequelize.query('PRAGMA cache_size   = -262144');
      await this.sequelize.query('PRAGMA mmap_size    = 268435456');
      Logger.info(`[Database] Db connection was successful`)

And can confirm that I'm seeing the same speed up inside of Audiobookshelf that I saw from the sqlite command line interface. It's a really nice speed improvement.

Just wanted to share that I've confirmed that on my setup it helps a lot :)

@PhantomPhoton commented on GitHub (Jan 21, 2025): So I patched in some hardcoded pragma values in [Database.js](https://github.com/advplyr/audiobookshelf/blob/master/server/Database.js#L235) ``` Logger.info(`[Database] Setting PRAGMA values`) await this.sequelize.query('PRAGMA temp_store = 2'); await this.sequelize.query('PRAGMA cache_size = -262144'); await this.sequelize.query('PRAGMA mmap_size = 268435456'); Logger.info(`[Database] Db connection was successful`) ``` And can confirm that I'm seeing the same speed up inside of Audiobookshelf that I saw from the sqlite command line interface. It's a really nice speed improvement. Just wanted to share that I've confirmed that on my setup it helps a lot :)

adam commented

2026-04-25 00:07:10 +02:00

@github-actions[bot] commented on GitHub (Feb 2, 2025):

Fixed in v2.19.0.

@github-actions[bot] commented on GitHub (Feb 2, 2025): Fixed in [v2.19.0](https://github.com/advplyr/audiobookshelf/releases/tag/v2.19.0).

Sign in to join this conversation.

Branches Tags

master

book_tags_genres_dedupe

episode_download_fallback

Issue-4540-SortBy-StartedDate-and-FinishedDate

episode_meta_tagging

fix_authorize_race_condition

redirect_transcode_requests

progress_updated_sort

fix_ereader_socket_event

fix_change_empty_root_password

fix_podcast_session_track_index

fix_set_token

session_modal_user

localize_durations

fix_oidc_create_user

jwt_auth_refactor

fix_scanner_deleting_single_file_books

fix_mediaprogress_updatedat_2

experimental_next_client

podcast_episode_duration

episode-timestamps-clickable

book_author_secondary_sort_title

podcast_useragents

pathexists_user_access

fix_pathexists_join

book_author_secondary_sort

clean_duplicate_mediaprogress

sanitize_html_description

trix_prevent_attachments

check_path_api_fix

fix_mediaprogress_updatedat

increase_express_json_limit

fix_dockerfile_nunicode

search_episodes

audiobook_tools_update

episode_secondary_sorts

hls_stream_url_update

new_session_track_endpoint

audiobook_tools_enhancements

watcher_rescans_update

player_track_tooltip

fix_exclude_prefixes_crash

socket_item_events

fix_podcast_episode_scanner_promise

new_stats_controller

count_cache_for_userpermissions

parsing-opf-v3

validate_migration_files

fix-quick-match-all-crash

fix-chapter-end-sleep-timer

stringify_sequelize_query

remove-col-ambiguity

fix_next_prev_edit_description

details_trim_whitespace

fix_content_url_basepath

fix_logger_fatal

progress_bar_visibility

batch-edit-populate-map-details

feed_generator_updates

bookmark-modal-updates

migrate-library-item-in-scanner

migrate-new-library-items

migrate-podcasts-new-library-item-2

migrate-podcasts-new-library-item

fix-remove-episode-from-playlist

playback-session-use-new-library-item

refactor-library-item

fix-heatmap-caption

feed-episodes-upsert

share-media-player-media-session-api

remove-old-playlist

remove_old_collection_object

plugin-implementation-demo

feed_migration

refactor-feeds-from-item

fix_remove_authors_no_books

v2.17.3-fk-constraints-migration

migrations-first-upgrade

sqlite_2

feature/nuxt-target-server

waveform

sqlite

playlists

video

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: starred/audiobookshelf#2437