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:12, 20 March 2014
, 20 March 2014→I/O Patterns
(First draft) |
|||
| Line 17: | Line 17: | ||
=== I/O Patterns === | === I/O Patterns === | ||
* Your database file can suffer from logical/internal fragmentation due to | * Your database file can suffer from logical/internal fragmentation due to common SQL patterns | ||
** | ** Eg appending data to columns with indexes causes interleaved index and table data. This means both table and index scans are non-sequential until a VACUUM is performed. | ||
** Appending data to multiple tables interleaves tables too. | |||
** 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 | ||
** e.g. the factory-default SQLite [http://www.sqlite.org/pragma.html#pragma_page_size page size] is 1024 bytes. When the 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 | ** e.g. the factory-default SQLite [http://www.sqlite.org/pragma.html#pragma_page_size page size] is 1024 bytes. When the 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 | ||