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

@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.

  # 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

automation: !include automation.yaml
  - platform: rest
    name: Living Camera Motion
    sensor_class: motion
    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
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
camera 45:
  - platform: generic
    name: Office garden
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://
    extra_arguments: -pred 1
camera 52:
  - platform: mjpeg
    name: Living camera
camera mi5splus:
  - platform: mqtt
    topic: zanzito/shared_pictures/mi5splus
    name: Zanzito MI5SPLUS Camera
  - platform: 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
  - platform: mqtt_json
      my_device: zanzito/mi5splus/location
# Emulated Hue per Alexa`e Google Home
    type: alexa
    listen_port: 8301
      - switch
      - script
      - light
    type: alexa
    listen_port: 8302
      - scene
    type: alexa
    listen_port: 8303
      - group
    type: google_home
    listen_port: 80
      - switch
      - script
      - light
  ffmpeg_bin: /usr/bin/avconv
# Enables the frontend
  key: dxxa
input_select: !include input_select.yaml
# Discover some devices automatically
    - plex_mediaserver

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

group: !include groups.yaml

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

# View all events in a logbook
  default: warning
    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
  - platform: vlc
    name: VLC
  #- platform: plex
  - platform: yamaha
      - "AV1"
      - "AV2"
      - "AV3"
      - "AV4"
      - "AV5"
      - "AV6"
      - "HDMI2"
      - "Napster"
      - "AirPlay"
      AUDIO2: "Cast audio"
      HDMI1: "XBOX"
      HDMI2: "Pi"
      HDMI3: "Server"
      HDMI4: "BR"
      HDMI5: "Cast TV"
  - platform: cast
  port: 1883
  client_id: home-assistant-2
  username: uxxx
  password: xxx
  publish_topic: master
  publish_topic: "home/states"
  - 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
    title: 'Asus'
    url: ''
    icon: mdi:router-wireless
    title: 'R7000'
    url: ''
    icon: mdi:router-wireless
#  purge_days: 2
# Track the sun

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

# Text to speech
  - platform: google
    cache: true
    cache_dir: /tmp/tts
    time_memory: 300
    language: 'it'
# Checks for available updates
# Xiaomi Gateways
    - sid: xx
      key: xxx
    - sid: xxx
      key: xxx
  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
  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:

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 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

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

I think all you need to do is to add your cameras to the exclude list. You can find you camera entitiy names on the dev state page. I guessed at them below. (I didn’t see your mysql url so you need to change it)

  purge_days: 2
  db_url: mysql://hass:[email protected]/HOMEBASE

I will try that, but I started having problems this week. While I have those cameras since 6 months and more … not sure is that.

I am not 100% sure, but I think I started to have problems when I upgraded from 0.43.2 to 0.44.1 and it failed, so I reverted back to 0.43.2 and started to have problems, then upgraded again and problems persisted (but can’t be 100% sure).
Other things I can think of, is the use of Zanzito (the photo part?), but also does not explain so many GB per day