The graph S.00 below shows the result writing 100 rows of 1 MB to the database before committing. One such way for the number of pages in a WAL file to increase beyond the automatic checkpoint size is by executing a large transaction. The SQLite documentation lists cases where the WAL file can grow well beyond 4 MB. Considering the default page size is 4096 B, it shouldn’t be unusual to see the WAL file size of a busy database sitting around 4.1 MB. This process is known as an automatic checkpoint. Large Transactions with a WAL Fileīy default, SQLite will try to let the WAL file grow to 1000 pages in size before moving transactions from the WAL file to the original database. In the titles of the graphs below, the shortened form “S.XX” is used to refer to “Scenario XX”. Each scenario typically starts by populating the database with 100 rows with each added row containing 1 MB of text.Įach profiled scenario has been given a numeric identifier. Various “scenarios” of interest were scripted. Write Ahead Logging mode was of course enabled on the database. The database schema was kept simple, consisting of a single table “Data”: The -shm file was omitted from the graphs below as its behaviour was deemed uninteresting. The temporary directory was specified to be a known location on disk using SQLITE_TMPDIR. The test script creates a database with the highly original name “test.db” and proceeds to monitor the size of the interesting database files namely test.db, test.db-wal, test.db-shm, and the temporary directory. The version of SQLite being used was 3.31.1. Fair warning - it’s not the most elegant code. The code and the generated results are available here at the tag v1.0. Python’s sqlite3 module was used to script various interactions with an SQLite database. Note that performance has intentionally been ignored here, and I don’t doubt that a lot of SQLite’s design decisions have been geared towards improved throughput. In an attempt to better understand this behaviour I’ve profiled how the disk usage of the various SQLite files change in a variety of scenarios. While likely not impactful on a modern desktop or server, this may be of interest if running on an embedded device. Using a database in WAL mode can result in interesting and sometimes surprising disk usage. SQLite advertises that the Write-Ahead Logging (WAL) feature offers better performance than SQLite’s default rollback journal in most scenarios. This makes it a popular choice for a variety of use cases including as an application file format. SQLite is a serverless SQL database engine which can be embedded within a program. ![]() This may be either an automatic checkpoint or a manual wal_checkpoint. A vacuum or incremental_vacuum will not shrink the database until a checkpoint is run.An incremental_vacuum(0) may result in the WAL file growing by approximately the number of free (‽) pages in the database.During the process, a temporary copy of the database will be be created. A vacuum command will result in the WAL file growing by roughly the number of used pages in the database.The pragma wal_checkpoint(truncate) can be used to shrink the WAL file.WAL files can grow to the size of the largest transaction.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |