phiresky/tune.md

## tune.md

      
    Raw
  

              tune.md
            
          
    You can scale a SQLite database to multiple GByte in size and many concurrent readers by applying the below optimizations.
Run these every time you connect to the db

(some are applied permanently, but others are reset on new connection)
pragma journal_mode = WAL;

Instead of writing directly to the db file, write to a write-ahead-log instead and regularily commit the changes. Allows multiple concurrent readers, and can significantly improve performance.
pragma synchronous = normal;

or even off. normal is still completely corruption safe in WAL mode, and means not every insert/update has to wait for FSYNC. off can cause db corruption though I've never had problems. See here: https://www.sqlite.org/pragma.html#pragma_synchronous
pragma temp_store = memory;

stores temporary indices / tables in memory. sqlite automatically creates temporary indices for some queries. Not sure how much this one helps.
pragma mmap_size = 30000000000;

Uses memory mapping instead of read/write calls when db is < mmap_size. Less syscalls, and pages and caches will be managed by the OS, so the performance of this depends on your operating system. Note that it will not use this amount of physical memory, just virtual memory. Should be much faster on at least Linux.
pragma page_size = 32768;

this improved performance and db size a lot for me in one project, but that might only be true because i was storing somewhat large blobs in my database and might not be good for other projects where rows are small.
More things that must be run manually

pragma vacuum;

Run once to completely rewrite the db. Very expensive.
pragma optimize;


To achieve the best long-term query performance without the need to do a detailed engineering analysis of the application schema and SQL, it is recommended that applications run "PRAGMA optimize" (with no arguments) just before closing each database connection. Long-running applications might also benefit from setting a timer to run "PRAGMA optimize" every few hours. https://www.sqlite.org/pragma.html#pragma_optimize

pragma auto_vacuum = incremental; -- once on first DB create
pragma incremental_vacuum; -- regularily

Probably not useful unless you expect your DB to shrink significantly regularily.

The freelist pages are moved to the end of the database file and the database file is truncated to remove the freelist pages [...]. Note, however, that auto-vacuum only truncates the freelist pages from the file. Auto-vacuum does not defragment the database nor repack individual database pages the way that the VACUUM command does. In fact, because it moves pages around within the file, auto-vacuum can actually make fragmentation worse.

Regarding WAL mode

WAL mode has some issues where depending on the write pattern, the WAL size can grow to infinity, slowing down performance a lot. I think this usually happens when you have lots of writes that lock the table so sqlite never gets to doing wal_autocheckpoint. There's a few ways to mitigate this:

Reduce wal_autocheckpoint interval. No guarantees since all autocheckpoints are passive.
Run pragma wal_checkpoint(full) or pragma wal_checkpoint(truncate) sometimes. With full, the WAL file won't change size if other processes have the file open but still commit everything so new data will not cause the WAL file to grow. If you run truncate it will block other processes and reset the WAL file to zero bytes. Note that you can run these from a separate process.