I know that the wal (write ahead log) of the Sqlite database is a special feature to deal with (very) busy writes to the database and that you can have temporary home-assistant_v2.wal and home-assistant_v2.shm files. But in my configuration they seem to be permanent.
Strange thing: they disappear when I stop HA with ‘ha core stop’ in the console.
But after ‘ha core start’ they appear again and you can see the wal file growing.
The only way to get rid of these files in a correct way according to the sqlite documentation is checkpoint the database with occures automatically when the size of the wal file is 1000 pages or to use the wal checkpoint pragma using the Sqlite3 CLI. But with Home Assistant OS installed that is not part of the installation so far as I know.
I think this is not normal behaviour and I wonder if there is someone in the communtiy who has more knowlledge about this phenomenon and how to deal with it.
After some more reading I must admit that it is not strange that the wal and shm file disappear when you stop HA. In the Sqlite documentation you can read that an automatic checkpoint is done when all database connections are closed, which of course happens when you stop HA. But for me it is still strange that after a start of HA the wal file appears again and is gradually growing to the original size or more.
When making a copy of the database with the wal and shm file to my desktop, after opening and closing the database, the wal and shm files disappear ( better to say: are checkpointed). After opening the database again, I see a wal file of 0 bytes and that doesn’t change.
No SQLite expert here, but my understanding is that those files are used to temporarily store database changes between commit_interval times. This cuts down on writes and improves performance. I would expect them to exist and be open virtually all the time HA is running. And, as you found, be closed when HA closes the database during a clean shutdown.
That said, I’m always interested in learning, so I’d welcome any correction.
As said, when I stop HA the wal and shm file are gone as expected.
When I copy the database to my desktop after the shutdown of HA, I can work locally whith the database without problems. I do only some reads so of course no concurrency problems with writes.
Strange that after restarting HA the wal and shm files are immediately there and the wal file is growing to about 4 Mb.
Then it is logical to assume that processes within HA cause the arise of the wal file. That would be plausible if there were extremely many concurrent writes. But I have a fairly modest Home Assistant installation so that doesn’t make sense.
So I still think something is going wrong somewhere. But where and what ?
Recorder is writing copious amounts of data to the database (and hence, presumably, the wal and shm files) from the moment it starts. My wal file is also around 4Mb right now. I guess I’d have to research more about how SQLite actually uses these files to know for sure, but my assumption has always been that it’s working as designed. I’d only worry if those file sizes continued to grow indefinitely.
A little more studying and I think it’s true that what we see is as designed.
With wal mode enabled ALL writes are done to the wal file and not only when there are a lot of concurrent writes as is often claimed. The wal file is more or less part of the database because when reading also the wal file is inspected. Of course some moment the writes must go into the database. That is called ‘checkpointing’. When that is done is part of the configuration. Default you will have a checkpoint when the last connection to the database is closed. (With HA that will for instance be when you do a ha core stop). Default also when the wal size grows above 1000 pages. So with a page size of 4096 that’s about 4 Mb and so far I have not seen growing my wal file beyond that.