Size of influxdb much bigger than MariaDb

[agak79]

Hello

I configure both databases 2 days ago, MariaDb for shorterm data storage and InfluxDb for long term storage.

Mariadb is configured without any exclusion or inclusion, so it’s basically registering everything in my HA for 30 days:

db_url: !secret mariadb_url

purge_keep_days: 30

commit_interval: 1

and influxdb is configured to store mainly temperature and energy sensor:

host: 192.168.0.12
port: 8086
database: HA_db
username: !secret influxdb_user
password: !secret influxdb_pass
max_retries: 3
default_measurement: state
include:
  entities:
    #Setpoints temperatura
    - sensor.salon_target_temperature
    #temperaturas habitaciones
    - sensor.salon_temperatura
    - sensor.casa_temperatura_exterior
    - sensor.trv_salon_temperature
    - sensor.termometro_bano_temperature
    - sensor.termometro_irati_andion_temperature
    - sensor.termometro_gurasoak_temperature
    - sensor.termometro_aseo_temperature
    - sensor.termometro_cuarto_juegos_temperature
    - sensor.termometro_cocina_temperature
    - sensor.termometro_salon_2_temperature

    #Tª Rasperry Pi
    - sensor.system_monitor_processor_temperature

    #% calefaccion
    - sensor.valvula_radiador_salon_position
    - sensor.salon_calefaccion

    #climates
    - climate.valvula_radiador_salon
    - climate.salon

    #modos
    - sensor.salon_modo_tado

    #humedad
    - sensor.salon_humedad
    - sensor.termometro_salon_2_humidity
    - sensor.termometro_aseo_humidity
    - sensor.termometro_irati_andion_humidity
    - sensor.termometro_bano_humidity
    - sensor.termometro_cocina_humidity
    - sensor.termometro_cuarto_juegos_humidity
    - sensor.termometro_gurasoak_humidity

    #energia
    - sensor.sensor_energia_1_energy_a
    - sensor.sensor_energia_1_energy_b

    #voltage
    - sensor.sensor_energia_1_voltage

    #corriente
    - sensor.sensor_energia_1_current_a
    - sensor.sensor_energia_1_current_b

    #power factor
    - sensor.sensor_energia_1_power_factor_a
    - sensor.sensor_energia_1_power_factor_b

    #potencia
    - sensor.sensor_energia_1_power_a
    - sensor.sensor_energia_1_power_b

    #contadores historicos
    - sensor.radiador_salon_heating_today
    - sensor.salon_heating_today

The influxDb db is configures to store those variables for 7 days and later a long term db with continuous queris every 15 minutes for each unit.

I would expect my Mariadb to be bigger than influxdb, as the later is only storing 26 variables, but the reality is different:

Maria Db is increasing at 15MB/day and influxdb at 120 MB /day. Does it make any sense? Shouldn’t influxDB to be smaller than MariaDb? How can I optimize my InfluxDb?

[_dev_null]

I’m interested in knowing how your sensor is measuring the database sizes

[tom_l]

I’m interested in this bit:

@agak79

The influxDb db is configures to store those variables for 7 days and later a long term db with continuous queris every 15 minutes for each unit.

[agak79]

yes, I forgot to include this part.

I configured the database size sensors using this guide

MariaDB sensor is configured as a SQL query:

SELECT table_schema "homeassistant", Round(Sum(data_length + index_length)/Power(1024,2), 1) "value" FROM information_schema.tables WHERE table_schema='homeassistant' GROUP BY table_schema;

The influxdb sensor is configured in the configure.yaml as

#Database File Size
#InfluxDB Database Sensor
- platform: influxdb
  host: 192.168.0.12 #Internal IP of Home Assistant
  port: 8086 #Access Port
  username: !secret influxdb_user
  password: !secret influxdb_pass
  scan_interval: 3600
  queries:
    - name: InfluxDB Database Size
      unit_of_measurement: MB
      value_template: "{{ (value | float(0) / 1024 /1024) | round(1) }}"
      group_function: sum
      measurement: '"monitor"."shard"'
      database: _internal
      where: '"database"=''HA_db'' AND time > now() - 5m'
      field: diskBytes

[agak79]

Yes , I configured it using this guide (in spanish):

There are 2 differente retention policies configured in my database:

autogen → 7days
infinite → 720 days

Then i have configured several continous queries to store data from autogen to infitite, with the following query (or similar for each unit):

cq_celsius_15m:

CREATE CONTINUOUS QUERY cq_celsius_15m ON HA_db BEGIN SELECT median(value) AS value INTO HA_db.infinite."°C" FROM HA_db.autogen."°C" GROUP BY time(15m), entity_id fill(previous) END

[agak79]

Ok, update about my influxdb database

Yesterday, at 2:00 A.M. the data base size decreased to 80 MB, without doing anything as I was sleeping and there was nothing automatized in HA to delete the database. the 7d configured of retention policy haven’t passed so it houldn’t be that.

After that it continued increasing at the same rate as previously

MariaDB database continues increasing at the same rate.

Is it normal?

[tom_l]

Yes.