Confirmed users
381
edits
Performance/Avoid SQLite In Your Next Firefox Feature: Difference between revisions
Jump to navigation
Jump to search
Performance/Avoid SQLite In Your Next Firefox Feature (view source)
Revision as of 22:18, 20 March 2014
, 20 March 2014→I/O Patterns
| Line 22: | Line 22: | ||
** Deleting data does not shrink the database, just marks pages as unused. | ** Deleting data does not shrink the database, just marks pages as unused. | ||
* Physical/external fragmentation can also happen due to SQLite using inappropriate defaults | * Physical/external fragmentation can also happen due to SQLite using inappropriate defaults | ||
** | ** SQLite does not preallocate files by default. Appending 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 database 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 problem | |||
Factory-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. | * 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, the filesystem | ** Note that this "vacuuming" only deals with internal fragmentation, not the external [filesystem] 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. | * 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 | * 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 | ||