I was having intermittant failures to record history, however when looking at glances I noticed that the system often had >70% iowait time which prompted me to upgrade the write speed on the system SD card. The system now has most <10% wait time and seems to be recording history ok. However when I ask for three days of data on the history display the Python process memory shoots up through the roof eventually consuming all of the swap space and eventually crashes the system.
I have a 64 GB (170mbs/90mbs) card so I should be able to increase the swap size, any thoughts on the best way to do this?
Also will it help or have I simply hit an upper limit on the capability of the sqlite database?
please stop your ha server
copy the db somewhere else
sqlite3 somewhere else/home-assistant_v2.db "delete from events WHERE created <= datetime('now', '-4320 minutes')";'
sqlite3 somewhere else/home-assistant_v2.db "delete from states WHERE created <= datetime('now', '-4320 minutes')";'
du -h somewhere else/home-assistant_v2.db
now you have an idea about the needed space.
if you really need 3 days history maybe you think about do i need all of it like eg. sunset and or maybe you didnt see the rpi4 under the christmas tree?