SQLite Write Ahead Logging – Why Are Files So Large?

linuxsqlite

I have been doing some operations on SQLite databases recently.

The other day I ran a command to DELETE a bunch of rows from a 1GB database file and the WAL file got over 8GBs at which point it filled my partition.

Why did it do this? How can I stop it?

Here are some of the PRAGMA's that are setup for this database:

PRAGMA wal_autocheckpoint = 1000
PRAGMA page_size          = 1024
PRAGMA max_page_count     = 1073741823
PRAGMA journal_size_limit = -1
PRAGMA journal_mode       = WAL
PRAGMA auto_vacuum        = 0

What can I change to stop WAL files getting so big when I want to make the database smaller because it is starting to eat my disk space? And will such a thing affect performance?

I do know the library was custom compiled, but I am not sure of the compilation time options. Can this be found out somehow?

This is running on and SSD in Debian Linux if that is relevant.

Best Answer

The WAL file records all changes to the database since the last checkpoint.

(Automatic) checkpoints cannot run when the database is locked, so ensure that there is no other connection with an active transaction.

The documentation says:

By default, the auxiliary write ahead log and shared memory files used for transaction control are automatically deleted when the latest connection to the database closes.

So ensure that all database connections are closed at some time.

(If you have disabled deleting WAL files with SQLITE_FCNTL_PERSIST_WAL, you have to set PRAGMA journal_size_limit to a nonnegative value to truncate the WAL file to zero bytes in that situation.)

Related Solutions

Why is SQLite adding unused disk space to “free-list” after deleting a lot of data in a database

As Phil wrote, freeing pages from the free-list would require that pages are moved around (and that all references to those pages are adjusted).

When data is deleted, it is very likely that some other data will be inserted afterwards, and that data can then just use the pages in the free-list. Returning those pages to the OS only to allocate new ones afterwards would be inefficient, and will increase fragmentation (both of the database file on disk, and of the objects in the database file). But if you really want SQLite to do that, you can enable it with PRAGMA auto_vacuum.

In practice, deleting lots of data usually doesn't happen during normal operation, and if you know that you will not insert new data after deleting, you can run the VACUUM command manually.

Import .db files/tables into sqlite database

For each database file (other.db) that must export tables to main.db:

sqlite3 other.db .dump | sqlite3 main.db

Or if you want to check the generated commands:

sqlite3 other.db .dump > log
sqlite3 main.db < log

Another method: (not so good as it does not import the primary key and column types)

First open the main database:

sqlite3 main.db

Then for each database containing tables to be imported:

ATTACH DATABASE 'other.db' AS other;
CREATE TABLE tablename AS SELECT * FROM other.tablename;
DETACH DATABASE other;

Best Answer

Related Solutions

Why is SQLite adding unused disk space to “free-list” after deleting a lot of data in a database

Import .db files/tables into sqlite database

Related Question