Faster mode for sqlite wordpress backend

Sqlite finally gets faster on writes with WAL — write-ahead logging.

Why WAL is faster?

WAL is faster because in this mode sqlite writes commits to separate sequential file, and merges these commits to database in batches. The last operation, check-pointing is the only synced operation in WALs synchronous = NORMAL mode. The separation between main db and new commits and the structure of WAL file makes it possible to do concurrent reads while writing and the check-pointing makes actual db updates (and thus locking) much less frequent.

Is is safe?

As far as i understand in synchronous = FULL mode it is as safe as default mode, because every commit is synced. In synchronous = NORMAL mode checkpoints are synced only.

How to use

Any application can use WAL:

PRAGMA journal_mode = WAL
PRAGMA synchronous = NORMAL

In case of SQLite Integration these lines can be added to pdoengine.class.php at line 261:

$this->pdo->query('PRAGMA journal_mode = WAL');
$this->pdo->query('PRAGMA synchronous = NORMAL');

Subjectively writes are faster at least in several times.

See also

Performance/Avoid SQLite In Your Next Firefox Feature

 

shitpoet@gmail.com