Blog/SQLite Performance on Btrfs

2020-10-25: SQLite Performance on Btrfs

Btrfs has come a very long way as a performant filesystem. One of the lingering myths that still permeates on the Internet is that you need to turn off CoW (Copy on Write) by using chattr +C mydatabase.sqlite. There is a much better way to increase the performance of your SQLite database on your Btrfs filesystem.

By using Write-Ahead Logging we can gain a 300% performance boost, compared to about 25-30% increase using the nocow attribute!.

Write-Ahead Logging

SQLite has since version 3.7.0 (2010-07-21) supported a new journaling mode called Write-Ahead Logging, or simply WAL. Instead of updating the database in-place, it uses separate log-file to store changes to the database. These changes are append-only, which means it is much much better for CoW filesystems like Btrfs, since Btrfs always write new data in new locations, and never overwrites in-place.

Benchmarks using Phoronix Test Suite

I did some quick benchmarks using the popular Phoronix Test Suite.

Setup:

• AMD Athlon 3000G 2-core/4-thread CPU @3.9GHz
• 8GB RAM
• Samsung 830 240GB SSD,
• Btrfs mount options: rw,noatime,compress-force=zstd:2,ssd,space_cache=v2,subvolid=92538,subvol=/volume/root
• PTS test: SQLite-2.1.0, 1 thread.

Test 1: default options

First round I am using default options:

• SQLite journal mode: delete
• Btrfs compression: zstd:2
• Normal COW mode.

Result: 183.88 seconds

Test 2: Using nocow option

• SQLite journal mode: delete
• Btrfs compression: Not available with nocow
• Nocow filemode.

Result: 142.14 seconds

Test 3: default options with WAL

• SQLite journal mode: WAL
• Btrfs compression: zstd:2
• Normal COW mode.

Result: 59.73 seconds

Test 4: Using nocow with WAL

• SQLite journal mode: WAL
• Btrfs compression: Not available with nocow
• Nocow filemode.

Result: 46.40 seconds

Consequences of using nocow

I often see that nocow is the first stop-gap to improve performance. But few talk about the consequences of using the nocow attribute (or the nodatacow mount option).

When you enable nocow on your files, Btrfs cannot compute checksums, meaning the integrity against bitrot and other corruptions cannot be guaranteed (i.e. in nocow mode, Btrfs drops to similar data consistency guarantees as other popular filesystems, like ext4, XFS, ...).

In RAID modes, Btrfs cannot determine which mirror has the good copy if there is corruption on one of them.

Btrfs does support reflinking nocow files. Deduplication works, but only against other nocow files.

Btrfs temporarily disables nocow during the snapshot.

How to enable SQLite WAL

It is very easy to enable WAL on any SQLite database. It works on any application that uses SQLite 3.7.0 or newer, which should be everything since that version is more than 10 years old.

1. Close your application
2. Make a copy of your database cp --reflink database.sqlite database.sqlite.bak
3. Open the db: sqlite3 somedb.sqlite
4. Check current journal mode: PRAGMA journal_mode;
5. Enable WAL: PRAGMA journal_mode=WAL;
6. Compact db for good measure (not strictly needed): VACUUM;
7. Close the db: .exit or press ctrl-d.

Now open your application and see that all works. You should see a database.sqlite-wal file if WAL journal is used.

# ls -lh /var/lib/quassel/quassel-storage.*

-rw-r--r-- 1 quassel quassel 126M Oct 25 14:10 /var/lib/quassel/quassel-storage.sqlite
-rw-r--r-- 1 quassel quassel  32K Oct 25 14:32 /var/lib/quassel/quassel-storage.sqlite-shm
-rw-r--r-- 1 quassel quassel  32M Oct 25 14:32 /var/lib/quassel/quassel-storage.sqlite-wal

WAL mode is persistent. It means the setting is stored inside the database file and will be re-used when applications restart. If you want to restore the old journal mode simply do PRAGMA journal_mode=delete;.