Recorder Purge function

Configuration
1

Hello !

I am new to the community and really enjoy setting up Home Assistant. I have a question related to the recorder. My log file is growing and growing and when it grows to 2-3 Gb Home Assistant is struggling and becomes unstable. I am using the the default SQLite database. I do not need all the history so I am fine with purging the log database.

In my configuration.yaml file I have added:

recorder:
  purge_days: 1

So far all well and Home Assistant starts up and no complaints about the recorder in the log file. However the log file does not get purged. My solution now is to delete the database file manually, but I would rather like to use the Purge function.

The permissions for the db log file is rwrwrw. Owner is homeassistant user.

Anyone experienced similar issue and managed to fix it? :slight_smile:

2

I have the same Issue here with 0.43.2.

3

Keep in mind that every time you restart HA, you also restart the timer on the purge_days.

1 Like
4

Thanks! I did not know that. I will keep the server running for a couple days then.

5

This is what i can’t understand!
Why wait 2 days after restart to purge the database?

6

Since I am new to Home Assistant I do some changes everyday and then restart Home assistant. I do understand why you asked the question. I do not know when or how often the recorder will purge the data. I assume it is only once a day, but when? Someone knows?

7

Here`s a topic about it.

8

At the time, you started HA, e.g. you (re)started HA at 5:00 pm, then after 2 days the purge will beginn at 5 pm. If you let HA continuously running, then the purge will be running every day at 5 pm.

1 Like
9

Thanks, that is why it is not working for me then :slight_smile:

10

For the record, it doesn’t happen exactly 48 hours later, but sometime shortly after that. I tested it a few times.

Usually starts the purge sometime in hour 48-49.

11

If you are constantly restarting after config changes you can always delete the file while you are in that directory.

12

Thanks, I am aware of that. Made myself a new sensor now :slight_smile:

Sensor:

- platform: command_line
  name: log_db_file_size
  command: "du -m /home/homeassistant/.homeassistant/home-assistant_v2.db | cut -f1"
  unit_of_measurement: 'MB'
  value_template: '{{ value | int - 1 }}'

Customization:

  sensor.log_db_file_size:
    friendly_name: Home Assistant Log Db
    icon: mdi:database
9 Likes
13

Nice idea. I might have to look into doing the same for my MySQL db.

14

That looks great. Think I will do the same.

Just have to remember what the path was to MySQL db.

15

Couldn´t you use the path from the config?

16

Ya just thought of that. lol
A little confused tho

mysql://hass:mypassword@localhost/homeassistant?charset=utf8

17

You can use this to find out the path :slight_smile:
mysql -p -u [USERNAME] -e 'SHOW VARIABLES WHERE Variable_Name LIKE "datadir"'

18

Hmmm. I get a negative 1 value back.

19

That´s because of an other sort of getting the size. I tried the last hour to get the correct size and this is the result:

mysql -u USER -pPASSWORD -s -N -e 'SELECT SUM( data_length + index_length)/1024/1024 FROM information_schema.TABLES where table_schema = \"hass_db\";'

You have to replace the PASSWORD and the USER, of course. This command opens mysql and execute the query to get the size of the databases. Now i will upgrade to 0.44 :slight_smile:

1 Like
20

I had massive problems with MySQL 5.7 on Debian Stretch, after a bit of tweaking I can get a value to output correctly in a terminal but it won’t transfer into HA for some reason…

  1. Suppress the warnings about command line execution, note not to actually put your password in as it will prompt you for it…

    mysql_config_editor set --login-path=local --host=localhost --user=username --password

  2. Then you can trigger the statement like this…

     mysql --login-path=local -s -N -e 'SELECT SUM( data_length + index_length)/1024/1024 FROM information_schema.TABLES where table_schema = "hass_db";'

This returns a number in the command line but it shows as “unknown” ins HA - this was because I had to set up the mysql config under the homeassistant account, after that everything was fine.