The Wal mode of SQLite

Link

Overview

After 3.7.0, the WAL (write-ahead Log) pattern can be used, another way of implementing transactional atomicity.

Advantages of Wal

In most cases faster

Higher parallelism. Because read and write operations can be parallel.

File IO is more orderly and serialized (more sequential)

Use Fsync () less often, and fsync () is not a good time to call on a bad machine.

Disadvantages

In general, VFS is required to support shared memory mode. (Shared-memory Primitives)

The process that operates the database file must be on the same host and cannot be used on the network operating system.

A database connection that holds multiple database files is atomic for a single database, not atomic for all databases.

You cannot modify the size of the page after entering the Wal mode.

You cannot open a read-only Wal database (Read-only Databases), which must have write access to the "-shm" file.

For a database that is read-only and seldom writes, it is 1%-2% slower.

There will be extra "-wal" and "-shm" files.

Need developer attention checkpointing

Principle

The way to roll back the log is to write the contents of the changed database file to the log, and then write the changed content directly to the database file. In the event of system crash or power-down, the contents of the log are re-written to the database file. The log file is deleted, and the flag commits the end of a commit.

This is the opposite of the Wal model. The original changed database content in the database file, the modification of the database file is appended to the separate Wal file. When a record is appended to the Wal file, it marks the end of a commit. Therefore, a commit does not have to operate on the database file, and while the write operation is in progress, it can be read concurrently. The contents of multiple transactions can be appended to the end of a Wal file.

Checkpoint

The contents of the last Wal file must be updated into the database file. The process of updating the contents of a Wal file to a database file is called a checkpoint.

There are two ways to roll back a log: Read and write. Wal has three kinds of operations, reading, writing and checkpoint.

By default, SQL will checkpoint once when the Wal file reaches 1000page. The timing of the Wal can also be determined by the application itself.

concurrency

When a read occurs on a database in the Wal schema, the last commit, called "End Mark", is first found in the Wal file. Each transaction can have its own "end point", but for a fixed transaction, end mark is fixed.

When reading the page in the database, SQLite will first look for the page from the Wal file, from finding the last record that is closest to end mark, and if not, look for the page from the database file. To avoid scanning the Wal file every time, SQLite maintains a "wal-index" data structure in shared memory, helping to quickly locate the page. The

Write database simply adds new content to the end of the Wal file, and does not have a relationship with the read operation. Because there is only one Wal file, there can be only one write operation at a time. The

checkpoint operation can be parallel to the read operation. However, if checkpoint writes a page to the database file and the page exceeds the end mark of the current read operation, checkpoint must stop. Otherwise, the part that is currently being read is overwritten. The next time you checkpoint, you will start copying data from this page to the database.

When a write operation checks the progress of the Wal file being copied to the database. If it has been completely copied into the database file, has been synchronized, and no read operation is using the Wal file, then the Wal file will be emptied, starting from the fact that the data append. Ensure that the Wal file does not grow indefinitely.

Performance

writes are fast because only one write is required and is sequential (not random, written to the end of each). Also, it is not necessary to brush the data onto the disk. (if pragma synchronous is full, each commit should be brushed once, otherwise it will not be brushed.) The performance of the

read operation has decreased because it needs to find content from the Wal file, and the time spent is related to the size of the Wal file. Wal-index can shorten this time, but it cannot be completely avoided. It is therefore necessary to ensure that the Wal file is not too large.

in order to protect the database from corruption, the Wal file needs to be brushed into the disk before the Wal file is written to the database, and the database contents will be brushed into the database file before the Wal file is reset. Also checkpoint requires a find operation. These factors make checkpoint slower than write operations. The

Default policy is that many threads can grow the Wal file. It is the responsibility of the checkpoint that the Wal file size becomes larger than 1000page. will cause most read and write operations to be fast, and a random write operation is very slow. You can also disable automatic checkpoint policies and periodically perform checkpoint operations on a thread or process.

Efficient write operations expect the Wal file to be as large as possible; efficient read operations expect the smaller Wal file to be as good as possible. There is a tradeoff between the two.

Activating and configuring the Wal mode

PRAGMA journal_mode=WAL;, if successful, returns "Wal".

Automatic checkpoint

Can be manually checkpoint

sqlite3_wal_checkpoint(sqlite3 *db, const char *zDb)

Configure Checkpoint

sqlite3_wal_autocheckpoint(sqlite3 *db, int N);

application-initiated checkpoints

Can be called or in any database connection that can be writtensqlite3_wal_checkpoint_v2()sqlite3_wal_checkpoint().

The persistence of the Wal mode

When a process sets up the Wal mode, closes the process, re-opens the database, and still is the Wal mode.

If you set the WAL mode in a database connection, all connections to the database will be set to the Wal mode.

Read-only database

If the database needs to be restored, and you have Read permission and no write permission, then you cannot read the database because the first step in the read operation is to recover the database.

Similarly, because the database in the Wal mode requires operations similar to database recovery, it is not possible to open the database if it has Read permission.

The implementation of Wal needs to have a hash table based on the Wal file in shared memory. In the VFS implementation of UNIX and Windows, it is based on Mmap . The shared memory is mapped to the "-shm" file in the same directory. Therefore, even if the database file in the Wal mode is read, write permissions are required.

In order to convert the database file into a read-only file, the log mode of the database needs to be changed to "delete".

Avoid too big a Wal file

Wal-index Shared Memory Implementation

Before Wal was released, it tried to map Wal-index to a temporary directory, such as/DEV/SHM or/tmp. But different users see the directory is different, so blocked.

Later attempts to map the Wal-index to an anonymous block of virtual memory, but cannot be consistent in the unused UNIX version.

The final decision is to map the Wal-index to the same directory. This will result in unnecessary disk IO. The problem, however, is that wal-index rarely exceeds 32k and never calls the sync operation. Additionally, after the last database connection is closed, the file is deleted.

If the database is only used by a single process, you can use heap memory instead of sharing it.

Implement Wal without shared memory

After the 3.7.4 version, the WAL mode can be used even if the shared memory is not supported, as long as SQLite's lock mode is set to exclusive.

In other words, if only one process is using SQLite, you can use Wal without shared memory.

At this point, changing the lock mode to normal is not valid and needs to be implemented to cancel the Wal mode.

The Wal mode of SQLite