Speed tips

PRAGMA journal_mode = OFF;

This will turn off the journal. If the process is interrupted, then your database will become corrupt with a high enough probability to care about. I set this to OFF when I do initial import of bulk data (and it gets forgotten when the connection close). Then, in daily work, you have normal database protections.

PRAGMA  synchronous = OFF;

Same as above. This provides protection against sudden power outages, or OS-level failures. Only provides benefit in write-heavy transactions. Can be turned off for initial import. Default is FULL, but the manual says NORMAL is safe enough for some work. (But if you are just querying data, changing this isn't needed.)

PRAGMA page_size = 4096;

This can provide slight performance advantages, since filesystem blocks tend to be 4096 these days. Larger could be even better, but it heavily depends on your database load. Try 32k or up to 256k ?

PRAGMA mmap_size = 1073741824;

Memory-mapped I/O is faster than normal. This gives a slightly greater risk of corruption, but not so much if you are using Python. Value is maximum number of bytes mapped. Set to large if dealing with lots of data.

PRAGMA cache_size = -2000000;

Set cache size to something large if you are using lots of data. Preferable as much as possible. If negative value, this many KiB will be allocated. Positive value is absolute number of pages. If this is greater than DB size, then you will be almost working directly on in-memory data.

VACUUM;

Recreate the DB from scratch, but with all data. This serves to defragment and pack it. Good to do if you have done a lot of updating of the main data.

DebianNotes/sqlite (last edited 2015-10-25 12:38:28 by RichardDarst)