Turbocharge your SQLite App with WAL

In current SQLite versions, there is a feature called Write Ahead Logging or WAL.

In a multi-process application such as a Catalyst FastCGI instance, this feature greatly improves performance, at the cost of occasional writes taking slightly longer. See previously linked document for details.

To turn it on, add on_connect_do "PRAGMA journal_mode=WAL;" to your Catalyst::Model::DBIC::Schema configuration, a typical configuration might be:

1
2
3
4
5
6
7
8
9
10
<Model::DB>
    schema_class MyApp::Schema
    traits Caching
    <connect_info>
        dsn dbi:SQLite:dbname=__path_to(db/myapp.db)__
        quote_names 1
        auto_savepoint 1
        on_connect_do "PRAGMA journal_mode=WAL;"
    </connect_info>
</Model::DB>

Make sure that all clients that use this database are version 3.7.0 of SQLite or above, and preferrably, that they execute this PRAGMA when connecting.

AUTHOR

Caelum: Rafael Kitover <rkitover@cpan.org>