Confirm
381
edits
Changes
→I/O Patterns
** Deleting data does not shrink the database, just marks pages as unused.
* Physical/external fragmentation can also happen due to SQLite using inappropriate defaults
** eSQLite does not preallocate files by default.gAppending data to an SQLite in multiple sessions often means the OS has to start a new data block that's not adjacent to the previous one. ** By default sqlite will grow/shrink the factorydatabase file by |page_size|. This behavior causes new data blocks to be allocated too. This is especially problematic on OSX, Linux XFS.** SQLite [https://www.sqlite.org/c3ref/c_fcntl_chunk_size.html#sqlitefcntlchunksize CHUNK_SIZE] feature is a way to minimize this problemFactory-default SQLite [http://www.sqlite.org/pragma.html#pragma_page_size page size] is 1024 bytes. When the Mozilla default page size was changed to 32KB in {{bug|416330}}, there was a [http://taras.glek.net/blog/2013/06/28/new-performance-people/ 4x reduction in SQLite IO waits] according to Telemetry. This is likely due to a reduction in syscalls and OS readahead.
* Performance can [http://sqlite.1065341.n5.nabble.com/Coping-with-database-growth-fragmentation-td44781.html#a44782 significantly degenerate over time]; scheduling periodic [https://sqlite.org/lang_vacuum.html rebuilds of the DB] is necessary.
** Note that this "vacuuming" only deals with internal fragmentation, not the external [filesystem has its own ] fragmentation
* SQLite uses fsync's to guarantee transaction durability and enable recovery from crashes. fsyncs can be very expensive, and happen relatively frequently in the default rollback-journal mode. This performance/reliability trade-off might not be necessary for simple storage needs.
* JSON files or log files will show better I/O patterns almost every time, especially if they're compressed and read/written in entirety each time
