I have a strange problem with Core Data in an iOS app where sometimes the WAL file becomes huge (~1GB). It appears there are other people with the problem (e.g. Core Data sqlite-wal file gets MASSIVE (>7GB) when inserting ~5000 rows).
My initial thought is to delete the WAL file at app launch. It seems from reading the sqlite documentation on the matter that this will be fine. But does anyone know of any downsides to doing this?
I'd of course like to get to the bottom of why the WAL file is growing so big, but I can't get to the bottom of it right now and want to put in a workaround while I dig deeper into the problem.
It's worth pointing out that my Core Data database is more of a cache. So it doesn't matter if I lose data that's in the WAL. What I really need to know is, will the database be completely corrupted if I delete the WAL? My suspicion is no, otherwise the WAL doesn't serve one of its purposes.
WAL files are a form of cache whereby data that is written to an SQLite db is first written to the WAL file (when this is enabled) and then at a later time (known as a checkpoint) the SQLite data is written into the main database.
SQLite Drop DatabaseYou just have to delete the file manually. Here filename is always unique i.e. database name is always unique and it is case-sensitive. SQLite stores the whole database in a single disk file so if you want to delete the database then manually go that file location and delete it.
The write-ahead log or "wal" file is a roll-forward journal that records transactions that have been committed but not yet applied to the main database. Details on the format of the wal file are describe in the WAL format subsection of the main file format document.
This file is a rollback journal for an SQLite database, similar to a transaction log on Microsoft SQL. In the SQLite documentation db-wal files are defined: A write-ahead log or WAL file is used in place of a rollback journal when SQLite is operating in WAL mode.
Couple of things:
You can certainly delete the WAL file. You will lose any committed transactions that haven't been checkpointed back to the main file. (Thus violating the "durability" part of ACID, but perhaps you don't care.)
You can control the size of the WAL file on disk with the journal_size_limit pragma (if it bothers you). You may want to manually checkpoint more often too. See "Avoiding Excessively Large WAL files" here: https://www.sqlite.org/wal.html
I dislike all the superstitious bashing of WAL mode. WAL mode is faster, more concurrent, and much simpler since it dispenses with the all the locking level shenanigans (and most "database is busy" problems) that go with rollback journals. WAL mode is the right choice in almost every situation. (The only place it is problematic is on flash filesystems that don't support shared memory-mapped access to files. In that case, the "unofficial" SQLITE_SHM_DIRECTORY compile directive can be used to move the .shm file to a different kind of filesystem -- e.g. tmpfs -- but this should not be a concern on iOS.)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With