1

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.

3
  • Also interesting, I had a 4GB database, that had had stuff deleted from it. I dumped the whole DB, loaded it into a new DB and it came back as 824MB. WTF?
    – hamstar
    Jun 19 '14 at 0:08
  • That's what VACUUM is for.
    – CL.
    Jun 20 '14 at 9:08
  • I tried doing a VACUUM once but it did nothing (and I think just grew the WAL file until it used 100% of the partition, then failed with disk space full) :(
    – hamstar
    Jun 22 '14 at 23:38
2

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.)

1
  • Ah thank you for this, I have noticed the WAL/SHM files disappearing sometimes but didn't know how that happened. So I guess I need to make the application more idempotent so that I can stop/start it safely to give the WAL a chance to get deleted during large operations.
    – hamstar
    Jun 22 '14 at 23:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.