@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/ttyACM0also 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! 
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
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)
recorder:
purge_days: 2
db_url: mysql://hass:[email protected]/HOMEBASE
exclude:
entities:
-camera.camera_41
-camera.camera_42
-camera.camera_43I 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