Performance/Avoid SQLite In Your Next Firefox Feature - Revision history

Yoric: +old document warning

2020-06-14T18:28:08Z

+old document warning

← Older revision		Revision as of 18:28, 14 June 2020
Line 1:		Line 1:
			== VERY OLD DOCUMENT ==

			This document dates back to 2014. You have been warned :)

	== Introduction ==		== Introduction ==
	Many Firefox developers see SQLite as a default choice for storing any non-trivial amount of data, and this wiki explains why that view is incorrect.		Many Firefox developers see SQLite as a default choice for storing any non-trivial amount of data, and this wiki explains why that view is incorrect.

PeterMortensen: /* Introduction */ Some copy editing.

2017-02-02T21:02:08Z

Introduction: Some copy editing.

← Older revision		Revision as of 21:02, 2 February 2017
Line 1:		Line 1:
	== Introduction ==		== Introduction ==
	Many Firefox developers see SQLite as a default choice for storing any non-trivial amount of data, this wiki explains why that view is incorrect.		Many Firefox developers see SQLite as a default choice for storing any non-trivial amount of data, and this wiki explains why that view is incorrect.

	SQLite may seem like a very appealing option when first designing a feature. It offers the familiar and powerful SQL language, our codebase has nice C++ and JavaScript APIs for it, and it's already used in many places in the codebase. However, when choosing storage formats, we have to keep in mind that SQLite is a powerful and reliable database and that it comes with a lot of '''hidden complexity'''. It's very tempting to take the abstractions it offers at face value and to ignore the complexity happening under the hood. As a result, we have repeatedly seen SQLite become a source of performance problems. This isn't an indictment of SQLite itself -- any other relational embedded DB would pose the same challenges.		SQLite may seem like a very appealing option when first designing a feature. It offers the familiar and powerful SQL language, our codebase has nice C++ and JavaScript APIs for it, and it's already used in many places in the codebase. However, when choosing storage formats, we have to keep in mind that SQLite is a powerful and reliable database and that it comes with a lot of '''hidden complexity'''. It's very tempting to take the abstractions it offers at face value and to ignore the complexity happening under the hood. As a result, we have repeatedly seen SQLite become a source of performance problems. This isn't an indictment of SQLite itself -- any other relational embedded DB would pose the same challenges.

Mak77: /* More Tips */

2014-04-08T08:13:09Z

More Tips

← Older revision		Revision as of 08:13, 8 April 2014
Line 114:		Line 114:
	* Maintain your DB with [https://sqlite.org/lang_vacuum.html VACUUMs] during Firefox version upgrades or during idle times. Run [http://www.sqlite.org/lang_analyze.html ANALYZE] periodically if necessary		* Maintain your DB with [https://sqlite.org/lang_vacuum.html VACUUMs] during Firefox version upgrades or during idle times. Run [http://www.sqlite.org/lang_analyze.html ANALYZE] periodically if necessary
	** You should have a vacuum plan from the get-go. '''This is a requirement for all new code'''.		** You should have a vacuum plan from the get-go. '''This is a requirement for all new code'''.
	* If working on the same data from multiple connections, consider using an unshared memory cache, to avoid memory contention. This will improve concurrency.		* If working on the same data from multiple connections, consider using an unshared [http://www.sqlite.org/sharedcache.html memory cache], to avoid memory contention. This will improve concurrency.
	** Though, it will also multiply the memory cache per the number of connection, so it's a memory/performance choice you should do carefully		** Though, it will also multiply the memory cache per the number of connection, so it's a memory/performance choice you should do carefully

Mak77: /* More Tips */

2014-04-08T08:11:18Z

More Tips

← Older revision		Revision as of 08:11, 8 April 2014
Line 114:		Line 114:
	* Maintain your DB with [https://sqlite.org/lang_vacuum.html VACUUMs] during Firefox version upgrades or during idle times. Run [http://www.sqlite.org/lang_analyze.html ANALYZE] periodically if necessary		* Maintain your DB with [https://sqlite.org/lang_vacuum.html VACUUMs] during Firefox version upgrades or during idle times. Run [http://www.sqlite.org/lang_analyze.html ANALYZE] periodically if necessary
	** You should have a vacuum plan from the get-go. '''This is a requirement for all new code'''.		** You should have a vacuum plan from the get-go. '''This is a requirement for all new code'''.
			* If working on the same data from multiple connections, consider using an unshared memory cache, to avoid memory contention. This will improve concurrency.
			** Though, it will also multiply the memory cache per the number of connection, so it's a memory/performance choice you should do carefully

Mak77: /* Important Pragmas */

2014-04-08T08:06:37Z

Important Pragmas

← Older revision		Revision as of 08:06, 8 April 2014
Line 92:		Line 92:
	* Use a [https://www.sqlite.org/wal.html write-ahead log] for fewer fsyncs		* Use a [https://www.sqlite.org/wal.html write-ahead log] for fewer fsyncs
	** <code>PRAGMA journal_mode = WAL;</code>		** <code>PRAGMA journal_mode = WAL;</code>
			** Though limit the size of the journal, by using a small autocheckpoint and a journal_size_limit about thrice that size
			*** <code>PRAGMA wal_autocheckpoint = 16; /* number of 32KiB pages in a 512KiB journal */</code>
			*** <code>PRAGMA journal_size_limit = 1536; /* 512KiB * 3 */</code>
			** If it's not possible to use WAL journaling, consider using a TRUNCATE journal
	* Set the DB cache size based on the device specs, and consider adjusting it dynamically based on memory pressure on mobile devices		* Set the DB cache size based on the device specs, and consider adjusting it dynamically based on memory pressure on mobile devices
	** <code>PRAGMA cache_size = X;</code>		** <code>PRAGMA cache_size = X;</code>
	* For large DBs prone to fragmentation, pre-allocate a large empty DB initially by increasing the DB's growth increment		* For large DBs prone to fragmentation, pre-allocate a large empty DB initially by increasing the DB's growth increment
	** This can be done by calling Connection::SetGrowthIncrement() which changes the value of SQLite's <code>SQLITE_FCNTL_CHUNK_SIZE</code>. See {{bug\|581606}} for reference.		** This can be done by calling Connection::SetGrowthIncrement() which changes the value of SQLite's <code>SQLITE_FCNTL_CHUNK_SIZE</code>. See {{bug\|581606}} for reference.
			* Use a memory temp store if the amount of data in memory entities is acceptable, otherwise temp data will be written to disk, causing unexpected I/O
			** <code>PRAGMA temp_store = MEMORY</code>

	=== More Tips ===		=== More Tips ===

Mak77: /* How to Store Your Data */

2014-04-08T07:57:20Z

How to Store Your Data

← Older revision		Revision as of 07:57, 8 April 2014
Line 73:		Line 73:

	* If you need to store a small amount of data (less than 1MB), you should use JSON files, and do all your I/O off the main thread. Simple solutions with linear worst-case performance are ideal.		* If you need to store a small amount of data (less than 1MB), you should use JSON files, and do all your I/O off the main thread. Simple solutions with linear worst-case performance are ideal.
	** If you're working with larger amounts of data (roughly on the order of 1MB), you should compress the JSON data with Snappy before writing it to disk		** If you're working with larger amounts of data (roughly on the order of 1MB), you should compress the JSON data with Snappy or lz4 before writing it to disk
	** You can use OS.File's [https://developer.mozilla.org/en-US/docs/JavaScript_OS.File/OS.File_for_the_main_thread#OS.File.writeAtomic%28%29 writeAtomic()] method. More information on writeAtomic [http://dutherenverseauborddelatable.wordpress.com/2014/02/05/is-my-data-on-the-disk-safety-properties-of-os-file-writeatomic/ in this blog post]		** You can use OS.File's [https://developer.mozilla.org/en-US/docs/JavaScript_OS.File/OS.File_for_the_main_thread#OS.File.writeAtomic%28%29 writeAtomic()] method. More information on writeAtomic [http://dutherenverseauborddelatable.wordpress.com/2014/02/05/is-my-data-on-the-disk-safety-properties-of-os-file-writeatomic/ in this blog post]
	** If your workload involves a lot of strings, don't use SQLite. Store your data in external files.		** If your workload involves a lot of strings, don't use SQLite. Store your data in external files.
			** If your workload involves a lot of blobs, don't use SQLite. Store your data in external files.
			* If you have a large dataset, but you don't need to run complex queries over it, evaluate a JSON files structure on disk
			* If you must read and write most of your data every time, evaluate using a JSON file
	* For larger datasets or when SQL is absolutely necessary, use SQLite.		* For larger datasets or when SQL is absolutely necessary, use SQLite.
	** Make sure you understand [https://sqlite.org/docs.html how SQLite works], carefully design your schemas and then profile your implementation.		** Make sure you understand [https://sqlite.org/docs.html how SQLite works], carefully design your schemas and then profile your implementation.

Mak77: /* I/O Patterns */

2014-04-08T07:49:14Z

I/O Patterns

← Older revision		Revision as of 07:49, 8 April 2014
Line 29:		Line 29:
	* 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, not the external [filesystem] fragmentation		** 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.		** VACUUM empties the free pages space of the database file, that means after a vacuum, until there's new free space, inserts will be a little bit more expensive cause they will have to grow the file
			* 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, especially if the important data can be rebuilt at any 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		* 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

Mak77: /* Memory Usage */

2014-04-08T07:45:02Z

Memory Usage

← Older revision		Revision as of 07:45, 8 April 2014
Line 36:		Line 36:
	* By default, our SQLite wrapper uses a [http://hg.mozilla.org/mozilla-central/file/444714c3820a/storage/src/mozStorageConnection.cpp#l48 max of 2MB memory cache per connection].		* By default, our SQLite wrapper uses a [http://hg.mozilla.org/mozilla-central/file/444714c3820a/storage/src/mozStorageConnection.cpp#l48 max of 2MB memory cache per connection].
	** This cache size may be too large for Fennec and B2G, especially if there are multiple connections. The cache size should be adjusted with [[#Important Pragmas\|PRAGMAs]]		** This cache size may be too large for Fennec and B2G, especially if there are multiple connections. The cache size should be adjusted with [[#Important Pragmas\|PRAGMAs]]
			** Queries unable to use an index will use SCAN TABLE to go through all of the rows, this will quickly grow the page memory cache to the maximum value, this space is never called back until PRAGMA shrink_memory is used, or the connection is closed.

	=== CPU Usage ===		=== CPU Usage ===

Mak77: /* Storage Footprint */

2014-04-08T07:44:23Z

Storage Footprint

← Older revision		Revision as of 07:44, 8 April 2014
Line 11:		Line 11:

	* Features that regularly add data to the DB (e.g. visited URIs), but have no expiration policy for the data, can easily cause the DB to balloon into tens or hundreds of MBs. This is particularly undesirable on mobile devices		* Features that regularly add data to the DB (e.g. visited URIs), but have no expiration policy for the data, can easily cause the DB to balloon into tens or hundreds of MBs. This is particularly undesirable on mobile devices
	* WAL journals have been known to grow very large, e.g. {{bug\|609122}}, {{bug\|608422}}, their size must be limited at a performance cost~~. More in general large~~ transactions ~~always~~ cause large journals, so transactions should be kept as small as possible.		* WAL journals have been known to grow very large, e.g. {{bug\|609122}}, {{bug\|608422}}, their size must be limited at a performance cost
			* Large transactions cause large journals, so transactions should be kept as small as possible
	* Every index contains a complete copy of the indexed data. Creating indexes on lengthy text fields (like URIs) will cause very large indexes to be stored on disk		* Every index contains a complete copy of the indexed data. Creating indexes on lengthy text fields (like URIs) will cause very large indexes to be stored on disk
	* Using the default microsecond precision for timestamps causes unnecessary bloat		* Using the default microsecond precision for timestamps causes unnecessary bloat

Mak77: /* Storage Footprint */

2014-04-08T07:41:24Z

Storage Footprint

← Older revision		Revision as of 07:41, 8 April 2014
Line 11:		Line 11:

	* Features that regularly add data to the DB (e.g. visited URIs), but have no expiration policy for the data, can easily cause the DB to balloon into tens or hundreds of MBs. This is particularly undesirable on mobile devices		* Features that regularly add data to the DB (e.g. visited URIs), but have no expiration policy for the data, can easily cause the DB to balloon into tens or hundreds of MBs. This is particularly undesirable on mobile devices
	* WAL journals have been known to grow very large, e.g. {{bug\|609122}}, {{bug\|608422}}		* WAL journals have been known to grow very large, e.g. {{bug\|609122}}, {{bug\|608422}}, their size must be limited at a performance cost. More in general large transactions always cause large journals, so transactions should be kept as small as possible.
	* Every index contains a complete copy of the indexed data. Creating indexes on lengthy text fields will cause very large indexes to be stored on disk		* Every index contains a complete copy of the indexed data. Creating indexes on lengthy text fields (like URIs) will cause very large indexes to be stored on disk
	* Using the default microsecond precision for timestamps causes unnecessary bloat		* Using the default microsecond precision for timestamps causes unnecessary bloat