Save or not the history of a specific feature

[Terdious]

@pierre-gilles, @AlexTrovato, @VonOx,
I’m following up on this topic as I consider it potentially critical on some rather heavy instances.
As a reminder, the discussion topics:

Rétention uniquement des valeurs aggrégées

Je propose d’ajouter une option pour ne garder qu’uniquement les valeurs aggrégées par période et supprimer les valeurs brut passé 24h (seuil configurable). Actuellement mes prises connectées renvoient une valeur / minute, 24h / 24h depuis plus d’un an. Ma base de données atteint 3Gb, alors qu’elle pourrait être de quelques centaines de Mio.

[DB de 5Go] Lenteurs au premier démarrage

Salut à tous, salut @pierre-gilles, J’en parle sur le forum avant de définir ensemble ce que tu veux voir sur GitHub Ca fait un moment que je le ressent, mais j’ai pu le confirmer maintenant en enregistrant mon écran. Démarrage de Gladys Si le conteneur de Gladys est arrêté puis démarré à nouveau (après une MAJ par exemple), le premier chargement des graphiques prend énormément de temps. Cela est certainement dû en partie à la taille de la DB 4.5Go ! Mais pas que, puisque tu as expli…

Case à cochée "Ne pas enregistrer les nouvelles valeurs identiques" + Choix de durée de backup indépendant pour chaque Device Features

Bonjour, Suite à un gros retour d’expérience concernant l’usage et la taille de DB, je propose cette nouvelle Feature Request et je pense qu’elle rapidement devenir urgente. Je m’explique : J’ai 92 Devices et 143 Devices Features composés de : 4 caméras (avec un poll compris entre 10s et 1 minutes - pour certaines il serait nécessaire d’avoir un temps réel voir toutes les 2 secondes de temps en temps, mais ce n’est pas possible : rend l’interface trop lente), 2 devices / 9 devices features…

If we can define the complete roadmap as well as validate a specification, I’m willing to take care of it as much as possible and produce a first draft quickly.

Also as a reminder, the proposal I made (modified according to Pierre-Gilles’s suggestion in order) to start the discussion:

• 1st PR :

  • Front-end side :
    • a selection field on each feature “Conserver l’historique des états de la feature” offering the same choices as in the Settings/System tab,
    • The field is selected by default on Gladys’s general configuration (except for cameras
  • Server side :
    • The column « keep_history » is already present so nothing to do on that side
    • Either :
      • we add a “delay_keep_history” column to the t_device_feature table defaulting to Gladys’s general config ?
      • or we modify the “keep_history” column to be able to put the value directly ?
      • or we use a device parameter for example:

      {
        "name": "[FEATURE_SELECTOR]_HISTORY_IN_DAYS",
        "value": [Delay en jours]
      }
      

      if present then we take this parameter into account for the feature values, otherwise we take Gladys’s general parameter as today.
    • review destroy_states according to this parameter.

• 2nd PR :

  • Front-end side :
    • a checkbox on each feature “Enregistrer uniquement les changements d’états”,
    • this checkbox is unchecked by default to remain as it is now,
  • Server side :

    • if the checkbox “Enregistrer uniquement les changements d’états” is checked, either :

      • we add an “only_keep_value_changes” column in the t_device_feature table, this is 0 by default,
      • or when checked we add a parameter in device_param :

      {
        "name": "[FEATURE_SELECTOR]:only_keep_value_changes",
        "value": true
      }
      

    • if the value of this parameter is true and the new value is the same as the previous one, we go through a new file server/lib/device/device.saveLastStateChanged.js to record in the table t_device_feature the saveLastStateChanged as you asked me at the time of the Netatmo development :

Code Proposal

const db = require('../../models');
const logger = require('../../utils/logger');
const { EVENTS, WEBSOCKET_MESSAGE_TYPES } = require('../../utils/constants');

/**
 * @description Save new device feature state in DB.
 * @param {Object} deviceFeature - A DeviceFeature object.
 * @example
 * saveLastValueChanged({
 *   id: 'fc235c88-b10d-4706-8b59-fef92a7119b2',
 *   selector: 'my-light'
 * });
 */
async function saveLastStateChanged(deviceFeature) {
  // logger.debug(`device.saveLastStateChanged of deviceFeature ${deviceFeature.selector}`);
  const now = new Date();
  // save local state in RAM
  this.stateManager.setState('deviceFeature', deviceFeature.selector, {
    last_value_changed: now,
  });
  await db.sequelize.transaction(async (t) => {
    // update deviceFeature lastValue in DB
    await db.DeviceFeature.update(
      {
        last_value_changed: now,
      },
      {
        where: {
          id: deviceFeature.id,
        },
      },
      {
        transaction: t,
      },
    );
  });

  // send websocket event
  this.eventManager.emit(EVENTS.WEBSOCKET.SEND_ALL, {
    type: WEBSOCKET_MESSAGE_TYPES.DEVICE.NEW_STATE_NO_CHANGED,
    payload: {
      device_feature_selector: deviceFeature.selector,
      last_value_changed: now,
    },
  });
}

module.exports = {
  saveLastStateChanged,
};

• When the value changes again, we go back through the function in server/lib/device/device.saveState.js which we must modify to re-save, before the new value, the previous value already in the database, to have an adjusted curve :

Code Proposal

const db = require('../../models');
const logger = require('../../utils/logger');
const { EVENTS, WEBSOCKET_MESSAGE_TYPES } = require('../../utils/constants');
const { BadParameters } = require('../../utils/coreErrors');

const DEFAULT_OPTIONS = {
  skip: 0,
  order_dir: 'DESC',
  order_by: 'created_at',
};

/**
 * @description Save new device feature state in DB.
 * @param {Object} deviceFeature - A DeviceFeature object.
 * @param {number} newValue - The new value of the deviceFeature to save.
 * @example
 * saveState({
 *   id: 'fc235c88-b10d-4706-8b59-fef92a7119b2',
 *   selector: 'my-light'
 * }, 12);
 */
async function saveState(deviceFeature, newValue) {
  if (Number.isNaN(newValue)) {
    throw new BadParameters(`device.saveState of NaN value on ${deviceFeature.selector}`);
  }
  const optionsWithDefault = Object.assign({}, DEFAULT_OPTIONS);

  // logger.debug(`device.saveState of deviceFeature ${deviceFeature.selector}`);
  const now = new Date();
  const previousDeviceFeature = this.stateManager.get('deviceFeature', deviceFeature.selector);
  const previousDeviceFeatureValue = previousDeviceFeature ? previousDeviceFeature.last_value : null;

  const previousDeviceFeatureLastValueChanged = previousDeviceFeature ? previousDeviceFeature.last_value_changed : null;

  const deviceFeaturesState = await db.DeviceFeatureState.findOne({
    attributes: ['device_feature_id', 'value', 'created_at'],
    order: [[optionsWithDefault.order_by, optionsWithDefault.order_dir]],
    where: {
      device_feature_id: deviceFeature.id,
    },
  });
  const previousDeviceFeatureStateLastValueChanged = deviceFeaturesState ? deviceFeaturesState.created_at : 0;

  // save local state in RAM
  this.stateManager.setState('deviceFeature', deviceFeature.selector, {
    last_value: newValue,
    last_value_changed: now,
  });
  // update deviceFeature lastValue in DB
  await db.DeviceFeature.update(
    {
      last_value: newValue,
      last_value_changed: now,
    },
    {
      where: {
        id: deviceFeature.id,
      },
    },
  );
  // if the deviceFeature should keep history, we save a new deviceFeatureState
  if (deviceFeature.keep_history) {
    // if the previous created deviceFeatureState is different of deviceFeature
    // last value changed, we save a new deviceFeatureState of old state
    if (previousDeviceFeatureLastValueChanged - previousDeviceFeatureStateLastValueChanged > 0) {
      await db.DeviceFeatureState.create({
        device_feature_id: deviceFeature.id,
        value: previousDeviceFeatureValue,
        created_at: previousDeviceFeatureLastValueChanged,
      });
    }
    await db.DeviceFeatureState.create({
      device_feature_id: deviceFeature.id,
      value: newValue,
    });
  }
  // });

  // send websocket event
  this.eventManager.emit(EVENTS.WEBSOCKET.SEND_ALL, {
    type: WEBSOCKET_MESSAGE_TYPES.DEVICE.NEW_STATE,
    payload: {
      device_feature_selector: deviceFeature.selector,
      last_value: newValue,
      last_value_changed: now,
    },
  });

  // check if there is a trigger matching
  this.eventManager.emit(EVENTS.TRIGGERS.CHECK, {
    type: EVENTS.DEVICE.NEW_STATE,
    device_feature: deviceFeature.selector,
    previous_value: previousDeviceFeatureValue,
    last_value: newValue,
    last_value_changed: now,
  });
}

module.exports = {
  saveState,
};

Example :

if we receive data for a consumed power

{
  value: 1014.7,
  created_at: 2022-04-19 04:20:13.277 +00:00
},
{
  value: 1014.9,
  created_at: 2022-04-19 04:30:13.277 +00:00
},
{
  value: 1014.9,
  created_at: 2022-04-19 04:40:13.277 +00:00
},
{
  value: 1014.9,
  created_at: 2022-04-19 04:50:13.277 +00:00
},
{
  value: 1014.9,
  created_at: 2022-04-19 05:00:13.277 +00:00
},
{
  value: 1014.9,
  created_at: 2022-04-19 05:10:13.277 +00:00
},
{
  value: 78.0,
  created_at: 2022-04-19 05:20:13.277 +00:00
}

In the DB we will find only

{
  value: 1014.7,
  created_at: 2022-04-19 04:20:13.277 +00:00
},
{
  value: 1014.9,
  created_at: 2022-04-19 04:30:13.277 +00:00
},
// here we did not save the identical values
{
  value: 1014.9,
  created_at: 2022-04-19 05:10:13.277 +00:00
},
// After writing the value below, we sent again
{
  value: 78.0,
  created_at: 2022-04-19 05:20:13.277 +00:00
}

Question: Is it useful or necessary to see anything for aggregated data? Let me explain: following lmilcent’s post (previously cited regarding retention), during the 1st PR we must take this into account so as not to break data aggregation if the feature’s keep_history is set to 1 day. The 1-year aggregation should still keep all its data unless keep_history is set to « OFF », right??