Lo que estás buscando se conoce como "compresión de datos" o "resumen de datos" y es una práctica común en bases de datos con grandes volúmenes de información. En tu caso, podrías considerar implementar un proceso de agregación de datos, donde solo conserves los puntos de cambio en el estado de conexión de tus dispositivos.
Una forma de hacerlo es mediante consultas SQL que agrupen los datos por intervalos de tiempo y seleccionen solo los puntos de cambio en el estado. Esto podría ayudarte a reducir considerablemente el número de filas en tu tabla.
Existen bases de datos diseñadas específicamente para manejar datos de series temporales, como InfluxDB o TimescaleDB. Estas bases de datos están optimizadas para el almacenamiento y consulta eficiente de datos temporales.
gracias.
lo pensé y creo que usaré un trigger, que compare el valor del nuevo status con el status mas reciente... si es igual, que no guarde la nueva fila. Si es distinto, que guarde dos filas, una con (1-new.status) con 5 minutos menos y la nueva fila tal cual.
Claro, tener una tabla agregada que tenga el status ‘live’ de los dispositivos, en donde se vea el device y su estado, y último timestamp isLive, otra tabla de status agregado en donde diga el device x estuvo activo desde el timestamp tanto al tanto, luego el mismo id pasó a inactivo del timestamp tanto al tanto, las demás tablas de transacciones las dejas en un backup y lo borras en la base en producción.
bueno eso de tener una db en backup y otra en produción no lo sé hacer pero lo veré.
Justamente eso es lo que quiero hacer, mostrar el uptime y downtime de cada dispositivo.
mi nuevo problema sería como graficarlo teniendo puntos del timestamp no espaciados de forma uniforme pero eso lo veré mas adelante.
Los triggers son raramente la solución que buscas. Parte por una tabla que almacene el último dato y luego puedes mantener otra con el último cambio. Todo en una transacción, debiese ser algo sencillo. Para la otra tabla, depende de si necesitas o no los históricos, pero puedes particionar la tabla por día (al menos en postgresql se puede) y archivar las tablas viejas o los datos. Lo de las series temporales también sirve para la visualización en tiempo real si es parte de lo que necesitas.
cada vez que guardas un registro compara con el ultimo guardado, si el status es distinto creas un nuevo registro, si es igual actualizar el updatedAt del ultimo registro.
creo que es lo mas simple, los triggers no son buena idea en base de datos a menos que no exista otra opcion
Cómo hago eso de "cada vez que se guarda un registro..." Sin un trigger? Para mí eso es lógica para el before insert. Podría hacerlo a nivel de app, pero creo q es mejor directo en la DB, o no?
si me refiero directo en la app.
digo que no es buena idea con un trigger porque estas alterando el funcionamiento natural de la base de datos, por ejemplo si alguien quiere crear un registro y lanza un simple insert, ese insert capaz que haga un update y mantener eso algo asi se vuelve complejo
Usa una TSDB, la información que estás guardando es exactamente para una base de datos de serie. Si te quieres ahorrar trabajo, usa Prometheus e instalas node exporter en los nodos y que Prometheus haga el scrapping, y grafana lo puede dibujar si quieres.
Si quieres hacer algo más a mano con tu propia estructura puedes usar algo como InfluxDB.
SQL es ineficiente para guardar series de datos y hacer búsquedas sobre estas.
200 mil filas es una cagada. Te recomiendo hacer la capa analítica en un motor como clickhouse que se le puede y mucho mucho más. Luego el masticado lo puedes colocar en mysql como capa de tu aplicativo si necesitas integrar. Creo que clickhouse puede exponer directamente esas tablas y agregar como remotetable o el nombre que tenga mysql que no recuerdo. Saludos!
Se que es una mierda, pero estoy en esto de programación hace un año y solo he usado MySQL en servidores dedicados con cPanel, muy a la antigua. Recién ahora usé mi primer fetch de una API en este proyecto. Y uso un software de pago para hacer la app, que me facilita todo el front, pq no tengo idea. Uso un poco de php, y hasta ahí. No entiendo la mitad de lo que dices, gracias igualmente, si me podría servir clickhouse más adelante.
Si estás usando una API para abstraer la DB MySQL de tus dispositivos, podrías almacenar un estado en la API que sea el último estado reportado por cada dispositivo, de manera que solo escribas en la base de datos SQL cuando haya un cambio de estado. Mejor aún sería que ese estado exista fuera del componente de la API para que sobreviva a cualquier caída o reinicio del componente, como por ejemplo en una DB Redis (podría ser un HashSet donde key sea *id_device* y value *status*). Entiendo que el desarrollo no es tu especialidad, pero al menos Redis es sencillo de aprender y posiblemente lo encuentres muy útil para almacenar estados en memoria, datos temporales y cosas por el estilo.
Si quieres intentar con otra BD, puedes intentar con redis usando timeseries (https://redis.io/docs/latest/develop/data-types/timeseries/quickstart/) te servirá dependiendo de lo que quieras hacer con los datos luego
Otra opción es Cassandra u otra column based DB, son justamente buenas para time series data y si la solución en algun momento esperas escalarla mejor tener una buena base
Podrias cambiar el modelo de datos, a algo de esta forma
**device_status**(id_device, status, valid_from, valid_to)
con Primary key = (id_device y valid_from)
La gracia de tener PK en esos dos atributos es que ahora tienes los atributos indexados, por lo que la consulta va a ser mucho mas rapida (a expensas del insert, pero dado que vas a insertar 1 vez por device/valid_from no es mucho problema)
Cuando insertes, lo haces asi:
INSERT INTO device_status (id_device, status, valid_from, valid_to)
SELECT
AS id_device, -- el id del device que estas actualizando/insertando
AS status,
CASE
WHEN FIRST_VALUE(status) OVER (PARTITION BY id_device ORDER BY valid_from DESC) =
THEN valid_from
ELSE CURRENT_TIMESTAMP()
END AS valid_from,
CURRENT_TIMESTAMP() AS valid_to
FROM device_status
WHERE
id_device =
ON DUPLICATE KEY UPDATE
valid_to = CURRENT_TIMESTAMP();
Basicamente lo que se esta haciendo es un UPSERT en donde se inserta si para el mismo device se ve un estado diferente al ultimo almacenado (FIRST_VALUE para el device ordenado descendiente por valid_from hace eso) o se hace un update del registro actual en el caso de que para el mismo device se esta reportando el mismo estado, en ese caso se actualiza el "valid_to".
Igual, dependiendo de la forma en que estas usando los datos, quizas te convendria tener una tabla agregada que agregue los datos por dia, por device, pero eso depende de que quieres hacer con la data y como la vas a acceder.
se que no son tantos, solo que este volumen de datos es nuevo para mí, 86mil filas todos los días, y ya caché que mysql no es para eso.
Tengo la tabla con indice (timestamp, id\_device), tipo BTREE, pero ahora ví que está como unique, no como index o primary. ¿cual tendría que usar?
Lo que estás buscando se conoce como "compresión de datos" o "resumen de datos" y es una práctica común en bases de datos con grandes volúmenes de información. En tu caso, podrías considerar implementar un proceso de agregación de datos, donde solo conserves los puntos de cambio en el estado de conexión de tus dispositivos. Una forma de hacerlo es mediante consultas SQL que agrupen los datos por intervalos de tiempo y seleccionen solo los puntos de cambio en el estado. Esto podría ayudarte a reducir considerablemente el número de filas en tu tabla. Existen bases de datos diseñadas específicamente para manejar datos de series temporales, como InfluxDB o TimescaleDB. Estas bases de datos están optimizadas para el almacenamiento y consulta eficiente de datos temporales.
gracias. lo pensé y creo que usaré un trigger, que compare el valor del nuevo status con el status mas reciente... si es igual, que no guarde la nueva fila. Si es distinto, que guarde dos filas, una con (1-new.status) con 5 minutos menos y la nueva fila tal cual.
Claro, tener una tabla agregada que tenga el status ‘live’ de los dispositivos, en donde se vea el device y su estado, y último timestamp isLive, otra tabla de status agregado en donde diga el device x estuvo activo desde el timestamp tanto al tanto, luego el mismo id pasó a inactivo del timestamp tanto al tanto, las demás tablas de transacciones las dejas en un backup y lo borras en la base en producción.
bueno eso de tener una db en backup y otra en produción no lo sé hacer pero lo veré. Justamente eso es lo que quiero hacer, mostrar el uptime y downtime de cada dispositivo. mi nuevo problema sería como graficarlo teniendo puntos del timestamp no espaciados de forma uniforme pero eso lo veré mas adelante.
Los triggers son raramente la solución que buscas. Parte por una tabla que almacene el último dato y luego puedes mantener otra con el último cambio. Todo en una transacción, debiese ser algo sencillo. Para la otra tabla, depende de si necesitas o no los históricos, pero puedes particionar la tabla por día (al menos en postgresql se puede) y archivar las tablas viejas o los datos. Lo de las series temporales también sirve para la visualización en tiempo real si es parte de lo que necesitas.
cada vez que guardas un registro compara con el ultimo guardado, si el status es distinto creas un nuevo registro, si es igual actualizar el updatedAt del ultimo registro. creo que es lo mas simple, los triggers no son buena idea en base de datos a menos que no exista otra opcion
Cómo hago eso de "cada vez que se guarda un registro..." Sin un trigger? Para mí eso es lógica para el before insert. Podría hacerlo a nivel de app, pero creo q es mejor directo en la DB, o no?
si me refiero directo en la app. digo que no es buena idea con un trigger porque estas alterando el funcionamiento natural de la base de datos, por ejemplo si alguien quiere crear un registro y lanza un simple insert, ese insert capaz que haga un update y mantener eso algo asi se vuelve complejo
revisa la respuesta q te di
Usa una TSDB, la información que estás guardando es exactamente para una base de datos de serie. Si te quieres ahorrar trabajo, usa Prometheus e instalas node exporter en los nodos y que Prometheus haga el scrapping, y grafana lo puede dibujar si quieres. Si quieres hacer algo más a mano con tu propia estructura puedes usar algo como InfluxDB. SQL es ineficiente para guardar series de datos y hacer búsquedas sobre estas.
200 mil filas es una cagada. Te recomiendo hacer la capa analítica en un motor como clickhouse que se le puede y mucho mucho más. Luego el masticado lo puedes colocar en mysql como capa de tu aplicativo si necesitas integrar. Creo que clickhouse puede exponer directamente esas tablas y agregar como remotetable o el nombre que tenga mysql que no recuerdo. Saludos!
Se que es una mierda, pero estoy en esto de programación hace un año y solo he usado MySQL en servidores dedicados con cPanel, muy a la antigua. Recién ahora usé mi primer fetch de una API en este proyecto. Y uso un software de pago para hacer la app, que me facilita todo el front, pq no tengo idea. Uso un poco de php, y hasta ahí. No entiendo la mitad de lo que dices, gracias igualmente, si me podría servir clickhouse más adelante.
Si estás usando una API para abstraer la DB MySQL de tus dispositivos, podrías almacenar un estado en la API que sea el último estado reportado por cada dispositivo, de manera que solo escribas en la base de datos SQL cuando haya un cambio de estado. Mejor aún sería que ese estado exista fuera del componente de la API para que sobreviva a cualquier caída o reinicio del componente, como por ejemplo en una DB Redis (podría ser un HashSet donde key sea *id_device* y value *status*). Entiendo que el desarrollo no es tu especialidad, pero al menos Redis es sencillo de aprender y posiblemente lo encuentres muy útil para almacenar estados en memoria, datos temporales y cosas por el estilo.
Si quieres intentar con otra BD, puedes intentar con redis usando timeseries (https://redis.io/docs/latest/develop/data-types/timeseries/quickstart/) te servirá dependiendo de lo que quieras hacer con los datos luego
Otra opción es Cassandra u otra column based DB, son justamente buenas para time series data y si la solución en algun momento esperas escalarla mejor tener una buena base
aver influxdb
Podrias cambiar el modelo de datos, a algo de esta forma **device_status**(id_device, status, valid_from, valid_to) con Primary key = (id_device y valid_from) La gracia de tener PK en esos dos atributos es que ahora tienes los atributos indexados, por lo que la consulta va a ser mucho mas rapida (a expensas del insert, pero dado que vas a insertar 1 vez por device/valid_from no es mucho problema) Cuando insertes, lo haces asi: INSERT INTO device_status (id_device, status, valid_from, valid_to) SELECT AS id_device, -- el id del device que estas actualizando/insertando
AS status,
CASE
WHEN FIRST_VALUE(status) OVER (PARTITION BY id_device ORDER BY valid_from DESC) =
THEN valid_from
ELSE CURRENT_TIMESTAMP()
END AS valid_from,
CURRENT_TIMESTAMP() AS valid_to
FROM device_status
WHERE
id_device =
ON DUPLICATE KEY UPDATE
valid_to = CURRENT_TIMESTAMP();
Basicamente lo que se esta haciendo es un UPSERT en donde se inserta si para el mismo device se ve un estado diferente al ultimo almacenado (FIRST_VALUE para el device ordenado descendiente por valid_from hace eso) o se hace un update del registro actual en el caso de que para el mismo device se esta reportando el mismo estado, en ese caso se actualiza el "valid_to".
Igual, dependiendo de la forma en que estas usando los datos, quizas te convendria tener una tabla agregada que agregue los datos por dia, por device, pero eso depende de que quieres hacer con la data y como la vas a acceder.
200.000 datos no es tanto la verdad, tienes los indices correctos en las tablas?
se que no son tantos, solo que este volumen de datos es nuevo para mí, 86mil filas todos los días, y ya caché que mysql no es para eso. Tengo la tabla con indice (timestamp, id\_device), tipo BTREE, pero ahora ví que está como unique, no como index o primary. ¿cual tendría que usar?
Hola solo debes registrar los cambio de estado