Home-assistant.db 30Gbyte in one day, what to do?

If I take out RECORDER and HISTORY from configuration, will it stop ?

I would add this to your recorder

recorder:
  include:
    domains:
      - switch

then delete your database and recreate and then restart HA.

If your database increase is slowed, you can try adding another domain and test the database increase rate.

This is mine

recorder:
  purge_days: 2
  db_url: mysql://hass:[email protected]/HOMEBASE
  include:
    domains:
      - sensor
      - switch
      - shell_command
      - alarm_control_panel
      - binary_sensor
  exclude:
    entities:
      - sensor.the_sensor_you_want_to_exclude1
      - sensor.the_sensor_you_want_to_exclude2

@anon35356645 I think it may help to post your full configuration.yaml - sanitized for passwords and API keys first of course - so that we may be able to get an idea of what is filling up your events table so quickly.

homeassistant:
  # Name of the location where Home Assistant is running
  name: Home
  # Location required to calculate the time the sun rises and sets
  latitude: xxx
  longitude: xxx
  # Impacts weather/sunrise data (altitude above sea level in meters)
  elevation: 83
  # metric for Metric, imperial for Imperial
  unit_system: metric
  # Pick yours from here: http://en.wikipedia.org/wiki/List_of_tz_database_time_zones
  time_zone: Europe/Rome
#
  customize: !include customize.yaml
# Show links to resources in log and frontend
#introduction:

#
#
#AUTOMATION
automation: !include automation.yaml
#
#
binary_sensor:
  - platform: rest
    name: Living Camera Motion
    sensor_class: motion
    resource: http://192.168.1.52:8080/sensors.json?sense=motion_active
    value_template: '{{ value_json.motion_active.data[0][1][0] | round(0) }}'
#
#binary_sensor alert:
#  - platform: template
#    sensors:
#      temperature_gilma_low:
#        value_template: '{{ states.climate.gilma_thermo.attributes.temperature < 18 }}'
#        friendly_name: "Gilma's temperature is low"
# with mqtt
  - platform: mqtt
    state_topic: "sensor/hallway"
    name: "Hallway motion"
    qos: 0
    payload_on: "ON"
    payload_off: "OFF"
    sensor_class: motion
#
#
#CAMERAS
camera 41:
  - platform: generic
    name: Rear left
    still_image_url: http://[email protected]/PSIA/Streaming/channels/1/picture
camera 42:
  - platform: generic
    name: Rear right
    still_image_url: http://[email protected]:82/PSIA/Streaming/channels/1/picture
camera 44:
  - platform: generic
    name: Entrance
    still_image_url: http://192.168.1.44/snapshot.cgi?user=xxxy
camera 45:
  - platform: generic
    name: Office garden
    still_image_url: http://192.168.1.45/snapshot.cgi?user=xxx
camera 45 video:
  - platform: ffmpeg
    name: Giardino da ufficio
    input: http://[email protected]:80/videostream.asf?user=xxx&resolution=64&rate=0
camera 46:
  - platform: ffmpeg
    name: Garden left
    input: -rtsp_transport tcp -i rtsp://192.168.1.46:554/unicast
    extra_arguments: -pred 1
camera 52:
  - platform: mjpeg
    mjpeg_url: http://192.168.1.52:8080/video
    name: Living camera
camera mi5splus:
  - platform: mqtt
    topic: zanzito/shared_pictures/mi5splus
    name: Zanzito MI5SPLUS Camera
#
#
climate:
  - platform: netatmo
netatmo:
  api_key: !secret netatmo_api_key
  secret_key: !secret netatmo_secret_key
  username: !secret netatmo_username
  password: !secret netatmo_password
  discovery: False

# Enables configuration UI
config:
#
#
device_tracker:
  - platform: mqtt_json
    devices:
      my_device: zanzito/mi5splus/location
#
# Emulated Hue per Alexa`e Google Home
emulated_hue:
  hue1:
    type: alexa
    listen_port: 8301
    target_ip: 192.168.1.92
    exposed_domains:
      - switch
      - script
      - light
  hue2:
    type: alexa
    listen_port: 8302
    target_ip: 192.168.1.92
    exposed_domains:
      - scene
  hue3:
    type: alexa
    listen_port: 8303
    target_ip: 192.168.1.92
    exposed_domains:
      - group
  hue4:
    type: google_home
    listen_port: 80
    target_ip: 192.168.1.91
    exposed_domains:
      - switch
      - script
      - light
#
#
#
ffmpeg:
  ffmpeg_bin: /usr/bin/avconv
#
#
# Enables the frontend
frontend:
#
#
#
#
#IFTTT#
ifttt:
  key: dxxa
#
input_select: !include input_select.yaml
#
#
#
#
#
# Discover some devices automatically
discovery:
  ignore:
    - plex_mediaserver

# Allows you to issue voice commands from the frontend in enabled browsers
conversation:

#
group: !include groups.yaml

# Enables support for tracking state changes over time.
# Recorder with the few entities I want included
#recorder:
#  purge_days: 1
#  db_url: mysql://[email protected]/hass_db?charset=utf8
# History setting is empty now
#history:
#
http:
  api_password: !secret api_password
  ssl_certificate: !secret ssl_certificate
  ssl_key: !secret ssl_key
  base_url: !secret base_url
#
# LIGHTS
light:
  - platform: flux_led
    devices:
      192.168.1.146:
        name: foyer_strip
      192.168.1.147:
          name: ceiling_right
#
  - platform: hue
    host: 192.168.1.80
    allow_unreachable:
#
    - platform: yeelight
    devices:
      #WHITE Yeelight
      192.168.1.203:
        name: Hallway left
      #RGB Yeelight
      192.168.1.204:
        name: Living table lamp
      #RGB Yeelight
      #192.168.1.200:
       # name: Kitchen one
      #192.168.1.201:
       # name: Kitchen two
      #192.168.1.202:
       # name: Kitchen three

# View all events in a logbook
logbook:
#
#
logger:
  default: warning
  logs:
    homeassistant.components.switch.broadlink: info
#    homeassistant.components.automation: info
#    homeassistant.components.script: info
#    homeassistant.components.device_tracker: info
#    homeassistant.components.http: info
#    homeassistant.components.rfxtrx: info
#    homeassistant.components.sensor.miflora: debug
    custom_components.xiaomi: debug
#    custom_components.sensor.xiaomi: debug
#    custom_components.switch.xiaomi: debug
#    custom_components.binary_sensor: debug
#    custom_components.light.xiaomi: debug
#    miflora.miflora_poller: debug
#    homeassistant.components.sensor.miflora: debug
#    yeelight: debug
#
#
#
# Media Players
media_player:
  - platform: vlc
    name: VLC
  #- platform: plex
  - platform: yamaha
    host: 192.168.1.32
    source_ignore:
      - "AV1"
      - "AV2"
      - "AV3"
      - "AV4"
      - "AV5"
      - "AV6"
      - "HDMI2"
      - "Napster"
      - "AirPlay"
    source_names:
      AUDIO2: "Cast audio"
      HDMI1: "XBOX"
      HDMI2: "Pi"
      HDMI3: "Server"
      HDMI4: "BR"
      HDMI5: "Cast TV"
  - platform: cast
    host: 192.168.1.35
#
#
#MQTT
mqtt:
  broker: 192.168.1.238
  port: 1883
  client_id: home-assistant-2
  username: uxxx
  password: xxx
#
mqtt_eventstream:
  publish_topic: master
#
mqtt_export:
  publish_topic: "home/states"
#
#NOTIFY
notify:
  - name: claudio_pushbullet
    platform: pushbullet
    api_key: !secret pushbullet_api_key
  - name: Push_HTML5
    platform: html5
    gcm_api_key: 'AAxmDY4q7JcWLl7ff1lFUgdIU2tnBrPtNixr6-YjwfOFR'
    gcm_sender_id: 'AIzaxxozemgxkJ_kL8'
  - name: claudio_zanzito
    platform: zanzito
    device-name: mi5splus
#
#
#Panel Iframe
panel_iframe:
  router_ufficio:
    title: 'Asus'
    url: 'http://192.168.1.253'
    icon: mdi:router-wireless
  router_tv:
    title: 'R7000'
    url: 'http://192.168.1.247'
    icon: mdi:router-wireless
#
#
#
#
#
#recorder:
#  purge_days: 2
#
# Track the sun
sun:

# Sensor Script Scenes Switch
script: !include script.yaml
scene: !include scene.yaml
sensor: !include sensor.yaml
switch: !include switch.yaml
#
#
#
#
#
shell_command:
  sleep_nuc: '/usr/bin/curl -X GET "http://192.168.1.12:7760/hibernate"'

# Text to speech
tts:
  - platform: google
    cache: true
    cache_dir: /tmp/tts
    time_memory: 300
    language: 'it'
#
#
# Checks for available updates
updater:
#
# Xiaomi Gateways
xiaomi:
  gateways:
    - sid: xx
      key: xxx
    - sid: xxx
      key: xxx
#
#
#ZONES
zone:
  name: Home
  latitude: xx
  longitude: xx6709
  radius: 100
  icon: mdi:home-variant
zone 2:
  name: School
  latitude: 4xxx412
  longitude: 1xxx147
  radius: 100
  icon: mdi:school
#
#
zwave:
  usb_path: /dev/ttyACM0

also all automation, script, sensor, switches?

Slow down, buddy! LOL

Seriously, I love your enthusiasm, but I think it’s part of your problem. You dive into things headfirst and simultaneously. While you’re working with us on this thread, I see you in other threads trying to implement even more stuff. You need to take a step back. These things tend to interact with one another, and because of that it’s that much harder to track down the cause of a problem.

Let us look through this first and then we’ll see if you need to post the others. And I think I may have an answer to your problem with WinSCP. Check your settings for your connection to the Pi in WinSCP’s site manager. Find your connection and click on Edit, then Advanced. Go to SCP/Shell settings and make sure yours looks like mine:

yes I am in that way

And that’s not necessarily a bad thing, but when it comes to HA, slow and steady wins the race. Not a criticism though; know that if I didn’t like you, I wouldn’t work so hard to help you! :smile:

Wow you have a lot of stuff going on there. I’d be willing to bet that the motion sensor in the living room is contributing at least somewhat.

I think @RobDYI’s suggestion, while it may be tedious, may be the best idea for figuring out what the chatty item is. This way you can add the domains back in one at a time and you should be able to tell instantly what is at fault. Then we can figure out if this is a bug or something else.

The other thing I was thinking of with your db connection issue is that I found there was a big difference between:

localhost
127.0.0.1
Your Pi's IP
Your Pi's hostname

when it comes to MySQL and HA. @philhawthorne helped me out with this one; check out this post and then read the 3-4 that follow it. It will tell you how to determine which of the above you should be using for your db_url in configuration.yaml. In my case I was trying to use 127.0.0.1 and it was actually localhost.

My first guess from the configuration.yaml would be the netatmo sensor as I don’t have that one.

I noticed from your database post that you only have 27k rows for the 500MB. My database has 1.7M rows with 700MB. This might help you determine the issue.

Great catch! It would be worth your time digging into that and seeing if you’re storing blob data like images. Could be that I was right about the motion; if ffmpeg stores motion frame data (I don’t know as I don’t use it for that) I could see it causing a problem.

1 Like

It would take a WHOLE LOT of text to equal 30GB in one day. I’m not sure a Pi could write 30GB in a day. The blobs would be my guess too.

Do you know, you can just delete the .DB file anything and hass will create a fresh one without making a fuss.

Unless you are using the database contents explicitly for any reason.

True, but that won’t lead to finding out what is creating such large records.

But I don’t want to delete the DB every day.
And have to reboot a crashed HA or pi3

I see.

But how to see if is storing images?

I do have my pi3 in my network, and have letsencrypt and duckdns.

When I do
mysql -u root - p

I get prompted a password, once entered, I am in, no problem on that.

Also when I do

http://192.168.1.238/phpmyadmin/

I get into the mysql DB (now is empty I deleted the recorder: function)

I don’t think switching from sqlite to MySQL really solves the problem. That way only the target that gets filled up changes, with MySQL being more performant. Still, so much IO on a SD card could easily kill it within a couple weeks.

The real issue here is that some part of your configuration is spamming the database, and that could actually be a bug which is still unnoticed by others.
My suggestion:

  • Make sure you’re running the latest version of HASS
  • Try out what’s described in this blogpost and see if you can at least pinpoint the table that’s containing all that data, along with some other metrics about your database. The tools documentation is here.

Turning off the recorder may fix the growing database. But there probably is something going wrong in the background that you still want to fix to ensure your HASS will be running stable. It might be interesting to get some averages of the CPU load and memory usage as well, just to get an overall feeling of the health of your pi.

absolutely, HA is crashing also without the recorder function. Will HAVE TO dig into it

Just for the record, moving to MySQL was never intended to solve the issue. It’s pretty clear that something is storing large binary info in the event table. MySQL and phpMyAdmin gives him the tools to probe the events table and figure out just what that is.

yep, at least now I can look into the DB easily … still need to understand what to look for, since is sooo big

1 Like