Adjusting SQLite Performance

Backlight uses the SQLite database to manage all data and settings. We have found the performance and scalability of this system to be excellent. There may however be scenarios in which SQLite performance is heavily impacted. In particular, SQLite depends heavily on having direct write access to the database files on disk. Should the files reside on a network drive, performance can be impacted dramatically.

To help alleviate this performance hit we have added two settings to the Backlight Admin settings. These change the way that SQLite saves transaction logs, and changing them may provide a solid performance boost.

We suggest that if you are experiencing significant issues with database performance, that you review the information below and tweak the settings to find the optimal values.

To view and edit the SQLite settings, visit Backlight => Settings, scroll to the end of the page, and click "Show Advanced Settings". The two settings can be found in the "Miscellaneous" section.

SQLite Journal Mode

By default, your installed version of SQLite most likely has the Journal Mode set to DELETE. It is suggested to try setting Journal Mode to PERSIST. The meaning and ramifications of this setting can be discovered here: https://www.sqlite.org/pragma.html#pragma_journal_mode

Note that the 'DEFAULT' setting effectively applies no change to the setting configured with your database. As the above suggests, this is most likely to result in the same setting as 'DELETE'. It's more efficient to leave this as 'DEFAULT' rather than 'DELETE' so as to not unnecessarily set this value for each page load.

SQLite Synchronous

By default, your installed version of SQLite most likely has the Synchronous setting set to FULL. It is suggested to try setting Synchronous to OFF. The meaning and ramifications of this setting can be discovered here: https://www.sqlite.org/pragma.html#pragma_synchronous

Note that the 'DEFAULT' setting effectively applies no change to the setting configured with your database. As the above suggests, this is most likely to result in the same setting as 'FULL'. It's more efficient to leave this as 'DEFAULT' rather than 'FULL' so as to not unnecessarily set this value for each page load.

We do not support the Synchronous OFF mode, as mentioned in the documentation on sqlite.org, as this may break Backlight's ability to maintain database integrity.