Welcome to the Directus SQLite Performance hook-extension! This extension is designed to make your Directus SQLite database more efficient and faster. It achieves this by tuning various performance parameters. This document will guide you through what each of these performance tunings means and how you can install and use them effectively.
Remember: all commands executed by this extension will be done for every connection made to the database. This is default behaviour for SQLite databases.
You can either use the marketplace or npm to install this extension. Please read the Marketplace section below on how to get the extension to show up.
The preferred way to install this extension is via a package manager. Assuming a docker or even docker-compose setup, the way to go is to create a custom build of the official Directus Docker image and install the extension in it.
When using npm, this extension can be installed like this:
npm install directus-hook-sqlite-perfTo install the extension in your custom image you need a Dockerfile that installs the extension via pnpm. This is well documented in the official directus documentation.
FROM directus/directus:11.0.2
LABEL authors="Some Name <[email protected]>"
USER root
RUN <<EOF
corepack enable
EOF
USER node
RUN pnpm install [email protected]When using docker compose you can easily point to this dockerfile and build your custom image with ease.
Assuming your docker-compose.yml and Dockerfile are in the same folder, the contents of docker-compose.yml could look like this:
services:
directus:
build:
context: .
restart: unless-stopped
ports:
- '8055:8055'
volumes:
- ./data/database:/directus/database
- ./data/extensions:/directus/extensions
environment:
SECRET: 'some-secret-key-here'
DB_CLIENT: 'sqlite3'To build the image, you "just" have to execute docker compose build to do so.
Note that this project also ships with an example docker-compose and even dockerfile. Check the directus directory for more details on that.
Please also check the directus docs on docs.directus.io for more tips and examples on how to install and manage extensions.
Generally, installing of this extension via Marketplace is not the preferred way
to go. This is because this extension will not be available in the marketplace
unless you have set the marketplace trust mode to all. This could lead to
security issues because the marketplace now shows ALL the non-sandboxed
extensions. You might not want nor need that.
The reason this extension is not able to use the sandbox mode is because it is using the database connection directly. And currently it is not possible for these kinds of extensions to run in sandboxed mode.
If you do want to change the trust-mode you can set the following environment variable to do so.
MARKETPLACE_TRUST=allTo use multiple database connections to your SQLite database file make sure to set the following environment variables:
# Knex.js defaults to a min size of 1 to always keep 1 connection open.
# Set it to 0 to allow 0 connetions.
DB_POOL__MIN: 0
# Set the max pool size. Play arround to see the limit for your system. For me
# setting it to 4 works most of the time.
DB_POOL__MAX: 4Important: make sure to not set PM2_INSTANCES to a value higher then 1
. Currently there is an issue with that.
To tune this extension you can use the instructions below to modify the defaults for the SQLite pragmas that will be set by this extension.
- Busy Timeout
- Journal Mode
- Journal Size
- Cache Size
- Synchronous Commit
- Temporary files location
- Enable memory mapping
- Increase the page size
The Busy Timeout setting configures the duration SQLite will wait before
throwing a SQLITE_BUSY error. By default, this value is set to 0, meaning no
wait time. This default setting can lead to SQLITE_BUSY errors when multiple
applications or threads try to write to the database simultaneously, especially
in WAL mode.
To mitigate this, the extension sets the busy_timeout to 30 seconds (30000
milliseconds) by default, providing ample time for ongoing operations to
complete and reducing the likelihood of encountering SQLITE_BUSY errors.
pragma busy_timeout = 30000;To change the setting of busy_timeout use the environment variable
DHSP_BUSY_TIMEOUT:
DHSP_BUSY_TIMEOUT=30000SQLite includes a feature called WAL mode (Write-Ahead Logging), which allows changes to be written to a separate log file before they're committed to the main database. This method provides more concurrency as readers do not block writers and a writer does not block readers. As a result, reading and writing can proceed concurrently, potentially offering a significant performance increase.
This is the command to enable WAL mode. It will be used for every connection made to the database. Technically it will be enabled for all future connections as well until you change is.
pragma journal_mode = wal;To change the setting of journal_mode use the environment variable
DHSP_JOURNAL_MODE:
DHSP_JOURNAL_MODE=walThe journal_size parameter in SQLite determines the maximum size of the
rollback journal. This is particularly important when the database operates in
modes that involve significant journaling, like WAL mode.
A rollback journal is essential for maintaining database integrity during transactions. However, if the journal grows too large, it may consume significant disk space and impact performance. By setting a maximum journal size, you can control the amount of disk space used by the journal and potentially improve the overall performance and responsiveness of the database.
The default journal size may not be optimal for all applications. Adjusting this value allows you to balance between performance and disk usage based on your specific requirements.
This extension will set the default for journal_size to 5MB.
pragma journal_size = wal;To change the setting of journal_size use the environment variable
DHSP_JOURNAL_SIZE:
DHSP_JOURNAL_SIZE=5242880The cache_size parameter in SQLite determines the maximum number of database
pages the cache can hold in memory at any given time. This setting is crucial
for database performance, as it affects how much of the database can be kept in
memory, thereby reducing the need for disk I/O operations.
When the cache size is larger, more database pages can be stored in memory, leading to faster query responses and improved overall performance, especially for read-heavy and repetitive operations. However, setting the cache size too high can consume a significant amount of memory, which may not be desirable in environments with limited RAM.
By configuring the cache_size, you can optimize memory usage based on the
available resources and workload characteristics of your application.
A negative value for cache_size specifies the size in kilobytes, whereas a
positive value specifies the number of pages. The example above sets the cache
to be 20000 KB (about 20 MB).
pragma cache_size = -20000;To change the setting of cache_size use the environment variable
DHSP_CACHE_SIZE:
DHSP_CACHE_SIZE=-20000The Synchronous setting has at least four possible values: EXTRA, FULL
(default), NORMAL and OFF. Setting the synchronous setting to NORMAL
should be safe when using WAL mode. It will sync less often as EXTRA or FULL
thus it will have some performance benefits. It can also be set to OFF but
that could lead to corruptions. Only use that if you really need the extra
performance.
pragma synchronous = normal;To change the setting of synchronous use the environment variable
DHSP_SYNCHRONOUS:
DHSP_SYNCHRONOUS=normalThe temporary file location in SQLite determines where SQLite puts temporary storage used for query processing. If your system has a faster disk or memory, changing the location of temporary storage can speed up the operations that need temporary storage, like sorting or creating indices. This extension will use the memory as location to store the files.
pragma temp_store = memory;To change the setting of temp_store use the environment variable
DHSP_TEMP_STORE:
DHSP_TEMP_STORE=memoryIn SQLite, mmap_size is a setting that controls the use of memory-mapped I/O. Memory-mapped I/O allows SQLite to access data in its database files as if it were directly in memory, which can provide a significant speed boost for certain workloads.
By default we set the value of mmap_size to 512MB. Make sure to adjust
accordingly. Advised is to look at the database filesize but also take into
account the total size of the memory available on your installation.
pragma mmap_size = 512000000;To change the setting of mmap_size use the environment variable
DHSP_MMAP_SIZE:
DHSP_MMAP_SIZE=512000000SQLite reads and writes one page at a time. Thus, a larger page size means less I/O operations, which can be a performance advantage for large databases. However, a larger page size might also mean more wasted disk space as the space in a partially filled page cannot be used by other pages. Hence, you need to choose a size based on the nature of your database operations.
This extension allows to set the page_size value but it is not included by
default and will only be used when the environment variable DHSP_PAGE_SIZE is
set.
Note: this can only be set before any data is inserted into the database or while restoring a backup.
Setting the value will be done with the PRAGMA instruction:
pragma page_size = 32768;To set the value of page_size use the environment variable DHSP_PAGE_SIZE:
DHSP_PAGE_SIZE=32768There are many SQLite options you can tweak and adjust to gain some improved performance for your Directus installation. The five options provided as is are most common I believe. I am open to add more options if needed, please use an issue in this repository to open up a discussion to do so.