Database is growing since switched to Zigbee2MQTT

[madface (Mike)]

Hello Community,

i have switched from ZHA to MQTT for about 2 weeks. Before i had cleaned up my recorder settings, made a fresh start (deleted DB and began from beginning, purge Settings 7 days, auto_purge:true, mariadb) which leaded to a database size for about ~350MB.
Since then my DB is growing day by day to now about ~650MB.

I had a look in the entities table, there are only entities i want to record. PHP My admin says about 230MB for events, 270MB for states and 120MB for state attributes for the big space eating tables.

Does Z2M generate more often state changes that are recorded, more (or other) events or something like this?

Thanks Mike

[NODeeJay (Alexander Weber)]

I was already on Z2M and did not have a major grow for weeks, but since afternoon of May 31st.

In my case it seems that I have a lot of “duplicate” entries, even though the state did not change.

and these are non Zigbee devices, so in my case it is not caused by zigbee. Do you see similar?

[madface (Mike)]

I think i can answer my own question, as it looks my problem is by design. I have made a test setup with 2 motion sensors, one of them connected to ZHA and second to Z2M.
Each time movement is recognized and cleared (one on and off action) uses 4 database entries for Z2M and only 2 for ZHA. Here are 2 screenshots:

This happens because of the attributes, since there is a change in the attributes the db links to the old and new value. For my sensor the stored attributes look like this:

{"battery":100,"detection_interval":5,"device_temperature":26,"illuminance":54,"linkquality":54,"motion_sensitivity":"high","occupancy":true,"temperature":25,"trigger_indicator":true,"update":{"state":null},"update_available":null,"voltage":3025,"device_class":"motion","friendly_name":"Presence Arbeitszimmer"}
{"battery":100,"detection_interval":5,"device_temperature":26,"illuminance":54,"linkquality":54,"motion_sensitivity":"high","occupancy":false,"temperature":25,"trigger_indicator":true,"update":{"state":null},"update_available":null,"voltage":3025,"device_class":"motion","friendly_name":"Presence Arbeitszimmer"}

So because of the change of occupancy in the attributes there is written a new line in the attributes table and a new line in the states table linking to the new attributes_id.
But if that’s not enough, since every sensor in Z2M has all the attributes of the entity (yes, the battery sensor of this entity also has the occupancy attribute), every time motion is detected the db writes lines for the illuminace, battery and temperature sensor (this is an Aqara P1 Sensor which has 4 entities in Z2M), even nothing of them has changed. So i can be happy my DB size only doubles
ZHA only writes one line per change and only for the binary_sensor, the battery sensor for example is not written since the battery not changed.
If something i wrote is bullshit please correct me, but this is how it looks for me atm.

@NODeeJay, this does not explain why your database is growing so much. Your tradfri gateway sensor has this many entries because of the linked attributes. You should look in the attribute table what changes, every line stands for the sensor with a change in the attributes.

A last question, is it possible to have the entities without the / some attributes? I have autodiscovery enabled so once connected to Z2M the sensors automatically appear but with all the attributes in every sensor…

EDIT: Setting advanced options in Z2M disables the attributes in each sensor, so my problem should be solved. I will report in a week when i see it works or not …

  legacy_entity_attributes: false

From the docs from Z2M

[NODeeJay (Alexander Weber)]

You may be better off limiting the recording of these attributes, battery values e.g. are still quite interesting to have (just not record) - at least in my case.

I did some tuning to the recorder and also a purge with pack and now it looks better:

here is my revised recorder.yaml, the - sensor.last_seen and automations seemed to help a lot:

db_url: mysql://[USER]:[PASS]@core-mariadb/homeassistant?charset=utf8mb4
commit_interval: 10
# auto_purge: true
auto_repack: true
purge_keep_days: 60
exclude:
  domains:
    - automation
    - camera
    - device_tracker
    - geo_location
    - input_datetime
    - input_number
    - input_select
    - input_text
    - media_player
    - proximity
    - sun
    - time_date
    - timer
    - updater
    - update
    - uptime
    - worldclock
    - weblink
  entity_globs:
    - sensor.clock*
    - sensor.date*
    - sensor.glances*
    - sensor.load_*m
    - sensor.time*
    - sensor.uptime*
    - sensor.*_uptime_sensor
    - sensor.*_linkquality
    - sensor.*_connect_count
    - number.kitchen_es_alarm_*
    - sensor.*last_seen
  entities:
    #    - sensor.home_assistant_v2_db
    - sensor.memory_free
    - sensor.memory_use
    #    - sensor.memory_use_percent
    - sensor.processor_use
    - sensor.internet_time
    - weather.openweathermap
  event_types:
    - service_removed
    - service_executed
    - platform_discovered
    - homeassistant_start
    - homeassistant_stop
    - feedreader
    - service_registered
    - call_service
    - component_loaded
    - logbook_entry
    - system_log_event
    - automation_triggered
    - script_started
    - timer_out_of_sync

[CentralCommand (Mike)]

In the Z2M’s HA settings, make sure to set legacy_entity_attributes to false. This option defaults to true and it causes Z2M to replicate everything in all sensors.

By default if you look at any of the entities for a device you’ll see the attributes of that entity contain the state of all other entities of that device. For example if you have a motion sensor with a primary sensor of occupancy but that also reports illuminance and temperature you’ll see you not only have sensors for illuminance, temperature and occupancy but each also has illuminance, temperature and occupancy in their attributes.

Replicating the same information to the attributes of the others is hugely wasteful with DB resources. One of the main optimizations made in recently releases was to strip down the number of attributes since they require so much more space. This change should make a huge difference if you currently have legacy_entity_attributes unspecified or set to true

[madface (Mike)]

Thanks for clearing, that’s how it looked for me and i could not have explained it better.

[CentralCommand (Mike)]

Oh yea sorry I see you said that in there, my bad. Had just solved this relatively recently so was replying quick.

While you’re at it I’d also recommend setting legacy_triggers to false. This isn’t really a space saving measure, not like the other one. But better to start off without legacy options rather then build around them and then find them deprecated at some point.

[madface (Mike)]

@CentralCommand

While you’re at it I’d also recommend setting legacy_triggers to false. This isn’t really a space saving measure, not like the other one. But better to start off without legacy options rather then build around them and then find them deprecated at some point.

Thanks, that’s a good hint. Just changed my automations, the actual and modern way also looks a bit more as i was used to from zha or deconz. And i think my move from ZHA to Z2M is solved and done!

Greetz Mike

[NODeeJay (Alexander Weber)]

so in the end Z2M does not produce more logs than ZHA if legacy_entity_attributes is set to FALSE.
I tried it and it makes half of the entries, thanks Mike:

[madface (Mike)]

you mean legacy_entity_attributes should be set to false, else you have minimum the double amount of logs being written.
I think as most of us do not dive that deep into the materia this should be the default setting, all attributes in every sensor that is created isn’t really needed.
I started the database fresh yesterday evening and it uses 37MB in 22 hours, so it will reach the same size as before when i used ZHA (~ 350MB in a week).

[NODeeJay (Alexander Weber)]

Thanks, obviously wrong, corrected

[teeeeee]

Hi @madface and @NODeeJay .

I am trying to set the legacy_entity_attributes to false as you suggested, but it doesn’t seem to make a difference for me. Would you mind seeing if there’s anything in my config that you think looks to be responsible? Thank you!

Here is the InfluxDB part of the main configuration.yaml file:

influxdb:
  host: 192.168.0.48
  port: 8086
  database: homeassistant
  username: homeassistant
  password: **password**
  include:
    entities:
      - sensor.snzb_02_sensor_1_temperature

and here is the Zigbee2MQTT configuration file:

data_path: /config/zigbee2mqtt
socat:
  enabled: false
  master: pty,raw,echo=0,link=/tmp/ttyZ2M,mode=777
  slave: tcp-listen:8485,keepalive,nodelay,reuseaddr,keepidle=1,keepintvl=1,keepcnt=5
  options: "-d -d"
  log: false
mqtt:
  server: mqtt://core-mosquitto:1883
  base_topic: zigbee2mqtt
  user: mqtt_user
  password: **password**
serial:
  port: /dev/ttyUSB0
homeassistant:
  legacy_entity_attributes: false

As you can see, there are many attributes being pushed into InfluxDB:

[madface (Mike)]

Sorry for the late answer, i missed your post.
The legacy attribute switch in z2m does not mean that you don’t have attributes in a sensor. When enabled, you have an own sensor for every attribute (and all attributes of the ‘mother’ sensor in it). Without it you have only the one sensor but sure, it have attributes.
If you don’t want them (and this can save really space in your db), you can exclude them from writing into influxdb. You have to add the ignore_attributes to your config:

influxdb:
  api_version: 1
  host: !secret influxdb_host
  port: 8428
  max_retries: 3
  measurement_attr: entity_id
  tags_attributes:
    - friendly_name
    - unit_of_measurement
  ignore_attributes:
    - icon
    - source
    - options
    - editable
    - min
    - max
    - step
    - mode
    - marker_type
    - preset_modes
    - supported_features
    - supported_color_modes
    - effect_list
    - attribution
    - assumed_state
    - state_open
    - state_closed
    - writable
    - stateExtra
    - event
    - friendly_name
    - device_class
    - state_class
    - ip_address
    - device_file
    - unit_of_measurement
    - unitOfMeasure

Just add the attributes you don’t want to be written and they wouldn’t be transferred to influxdb.