0002844: Enable WAL mode on databases

Chat on our Discord Server Get the latest version: 3.1
Source Code

View Issue Details [ Jump to Notes ]

[ Issue History ] [ Print ]

Project

Category

View Status

Date Submitted

Last Update

0002844

Zandronum

[All Projects] Suggestion

public

2016-09-28 03:10

2018-09-30 21:41

Reporter

AlexMax

Assigned To

Torr Samaho

Priority

normal

Severity

feature

Reproducibility

N/A

Status

closed

Resolution

fixed

Platform

OS Version

Product Version

2.1

Target Version

3.0

Fixed in Version

3.0

Summary

0002844: Enable WAL mode on databases

Description

Apparently, recent versions of SQLite3 have a feature called Write-Ahead Logging which appears to confer significant advantages over the default rollback journaling.

'https://www.sqlite.org/wal.html [^]'

It is significantly faster and allows concurrent reading and writing, which is good - I occasionally get messages from people saying that database system slows down with the amount of data they're putting into it, and this might be able to combat latency.

Enabling it is as simple as sending a PRAGMA to the database in question. It is persistent, but it's probably best to always send the PRAGMA upon opening the file just in case. Since it is persistent across opens, you can technically set any database file to WAL mode simply by opening it with the command line client and setting the pragma that way, but obviously not all server administrators can be expected to know this.

The most significant downside with WAL appears to be that the database cannot be located on a network filesystem. However, SQLite already recommends against being used over NFS, and although I haven't tested it myself, I'm willing to bet that safe use of sqlite over NFS might block the entire server process with additional latency, which would be unacceptable.

Attached Files

Relationships

Notes
(0017098) Torr Samaho (administrator) 2017-04-08 13:02	The SQLite3 database can now be used in WAL mode. This mode is enabled / disabled with the new CCMDs 'db_enable_wal' / 'db_disable_wal'

(0017101) Edward-san (developer) 2017-04-08 13:21	Is it okay for these CCMDs to not be protected against ConsoleCommand?

(0017102) Torr Samaho (administrator) 2017-04-08 13:31	No, they should be protected. Thanks for pointing this out. Fixed.

**Issue Community Support**
This issue is already marked as resolved. If you feel that is not the case, please reopen it and explain why.
Supporters:	ZzZombo FascistCat Korshun
Opponents:	No one explicitly opposes this issue yet.

Issue History
Date Modified	Username	Field	Change
2016-09-28 03:10	AlexMax	New Issue
2017-04-08 13:02	Torr Samaho	Note Added: 0017098
2017-04-08 13:02	Torr Samaho	Status	new => needs testing
2017-04-08 13:02	Torr Samaho	Product Version	=> 2.1
2017-04-08 13:02	Torr Samaho	Target Version	=> 3.0
2017-04-08 13:02	Torr Samaho	Assigned To	=> Torr Samaho
2017-04-08 13:02	Torr Samaho	Status	needs testing => assigned
2017-04-08 13:03	Torr Samaho	Status	assigned => needs testing
2017-04-08 13:21	Edward-san	Note Added: 0017101
2017-04-08 13:31	Torr Samaho	Note Added: 0017102
2017-04-08 16:42	Edward-san	Note View State: 0017101: public
2017-09-07 20:34	Dusk	Status	needs testing => resolved
2017-09-07 20:34	Dusk	Fixed in Version	=> 3.0
2017-09-07 20:34	Dusk	Resolution	open => fixed
2018-09-30 21:41	Blzut3	Status	resolved => closed

Questions or other issues? Contact Us.

Links

Doom and Doom II are the property of id Software.