Base de datos frente a archivos planos para datos a los que rara vez se accede

7

Estamos recibiendo datos en tiempo real de más de 1000 sensores, cada uno de los cuales envía un punto de datos cada 10 segundos en promedio, lo que equivale a aproximadamente 1 millón de filas de datos cada día. Nuestro sistema ofrece a los usuarios la posibilidad de seleccionar cualquier sensor y un intervalo de fechas y descargar los puntos de datos como un archivo de Excel.

Hemos visto que nuestros usuarios están interesados principalmente en datos que tienen menos de 30 días. Es probable que los datos que tienen más de 30 días de antigüedad ya se hayan descargado. Solo alrededor del 1% de nuestras solicitudes de recuperación de datos provienen de datos que tienen más de 30 días de antigüedad. Sin embargo, no podemos decir que estos datos son totalmente inútiles, porque nuestros usuarios a veces desean descargar datos que tienen incluso más de un año. Eliminar datos antiguos no es una posibilidad.

Actualmente estamos usando la base de datos MySQL para almacenar los datos, y todos los datos se almacenan en una sola tabla. La tabla ahora tiene más de 60 millones de filas. Usamos SSD y tenemos los índices correctos debido a los cuales la recuperación de datos sigue siendo considerablemente más rápida.

Un ejemplo de consulta de base de datos que utilizamos para seleccionar cada minuto de datos es:

SELECT
    data_value AS value, param_id AS param_id,
    data_timestamp AS ts,
FROM tbl_data_log
WHERE param_id in (?)
    AND data_timestamp >= ? AND data_timestamp <= ?
GROUP BY DATE(data_timestamp), HOUR(data_timestamp),
    MINUTE(data_timestamp),
    param_id
ORDER BY data_timestamp ASC

Actualmente, esta consulta tarda menos de 5 segundos en recuperar datos que tienen más de 30 días para un sensor específico.

A medida que se almacenen más datos en esta tabla, aumentará de tamaño, tal vez hasta 2 mil millones de filas en el próximo año (también agregaremos más sensores todos los días). No sé cómo sería el rendimiento de la consulta en esa etapa. Para mí, el almacenamiento de todos estos datos en una base de datos MySQL no parece ser correcto, ya que se accede muy raramente, y tener datos indexados hace más de 4 meses parece innecesario.

Un enfoque en el que pensé es tener solo los últimos 30 días de datos en MySQL y mover los datos antiguos a archivos planos con una estructura de carpetas como /old_data/%YEAR%/%MONTH%/%DATE%/%PARAM_ID%.dat . De esta manera, el tamaño de nuestros datos no será inmanejable, pero al mismo tiempo los datos se indexarán en forma de archivos planos en el disco.

¿El enfoque actual es bueno para escalar? ¿Mover datos antiguos a archivos planos ayuda o no? ¿Es correcto almacenar todos los datos en una sola tabla? ¿Necesitamos cambiar nuestro motor de base de datos? Por favor, da tu opinión sobre esta arquitectura. ¡Muchas gracias de antemano!

    
pregunta Ananth 19.11.2017 - 06:11

5 respuestas

8

Los motores de base de datos, en principio, están diseñados para hacer frente a enormes cantidades de datos mucho más rápido que con archivos de datos sin procesar, cuando tiene que acceder a los datos de manera no secuencial.

Dice que tiene todos los índices correctos en su tabla para obtener un acceso optimizado, por lo que data_timestamp ciertamente está indexado. Sin embargo, veo en su ejemplo de consulta que utiliza:

GROUP BY DATE(data_timestamp), HOUR(data_timestamp),
    MINUTE(data_timestamp),
    param_id

Esto obliga a su motor de base de datos a convertir la marca de tiempo de cada fila que coincida con el lugar de la fecha de la consulta, que sospecho que es muy tiempo consumiendo .

Como supongo que la fecha y la hora se usan comúnmente en su aplicación, sugeriría considerar un poco de desnormalización aquí para facilitar el trabajo de la base de datos precomputando la fecha ( DATE type) y el tiempo redondeado al minuto (ya sea tipo TIME o eventualmente un SMALLINT entre 0000 y 2359). Eso es una sobrecarga de 5 bytes por fila. Cree un índice en ellos para acelerar la cláusula GROUP BY .

Si esto no es suficiente, asegúrese de que el servidor esté dimensionado correctamente para su desafío de big data y observe si su DBMS está lo suficientemente bien ubicado en los puntos de referencia con otros DBMS.

Como alternativa, también puede considerar el uso de dos tablas: una tabla activa durante los últimos 30 días y una segunda tabla con todos los datos históricos con más de 30 días. Algunos trabajos por lotes luego moverían los datos que expiran de una tabla a la otra.

    
respondido por el Christophe 19.11.2017 - 14:59
4

Cuando inserta datos en grandes tablas indexadas a una alta velocidad de datos, el mantenimiento del índice puede convertirse fácilmente en un factor limitante. Los valores de cada fila individual deben insertarse en sus índices.

Por lo tanto, limitar el tamaño de la tabla es una buena idea. Pero no teniendo una tabla y eliminando entradas antiguas de la tabla porque eso significa otra actualización del índice, esta vez eliminando las entradas del índice, lo que lleva aproximadamente la misma cantidad de tiempo que la inserción.

Elimine datos de la base de datos eliminando / truncando tablas completas en lugar de eliminar filas.

No sé si mySQL tiene algo similar a las Tablas particionadas de Oracle (físicamente de varias tablas, a las que se accede lógicamente como una sola / similar a una vista que muestra una UNIÓN de las particiones). Si mySQL no lo tiene, no es necesario hacer magia negra con un montón de tablas de bases de datos normales, una vista y un poco de lógica.

Con este esquema, creo que puede mantener los datos antiguos en la base de datos sin comprometer el rendimiento de las consultas para sus consultas típicas del mes en curso.

    
respondido por el Ralf Kleberhoff 19.11.2017 - 15:45
2

Aquí hay una idea. Iniciar una nueva base de datos (archivo) cada mes. El único problema que veo con esto es que puede obtener una consulta que abarca dos meses diferentes. Puede simplemente decirles a sus usuarios que ejecuten dos consultas diferentes si necesitan datos de diferentes meses, o hacer un código proxy que pueda dividir una consulta y unir los datos antes de devolverlos (puede hacer esto en VBA para que los usuarios puedan ejecutarlos) de su libro de trabajo).

La marca de tiempo debe estar en el nombre de su base de datos para que la base de datos a tratar se pueda construir a partir de los parámetros de consulta. Es posible que necesite un catálogo que asigne un nombre de base de datos a un servidor para que pueda escalar entre diferentes servidores. Podría crear las bases de datos futuras por adelantado, ingresar algunos datos ficticios y probarlos.

Es posible que desee comprimir las bases de datos (más antiguas) y descomprimirlas justo antes de montarlas, y eliminar las bases de datos descomprimidas cada noche o antes de abordar una base de datos antigua diferente. Eso daría a sus usuarios un impacto considerable en el rendimiento al abordar datos antiguos, pero aún así sería posible y podría ahorrar mucho espacio.

    
respondido por el Martin Maat 19.11.2017 - 10:09
1

Aquí hay otra idea. Ya que su estructura de datos es tan simple que podría escribir su propio programa, defina una estructura / registro para lo que ahora es su registro de base de datos y use una secuencia de archivos para acceder a los datos. Podría tener un archivo por día o mes.

Para cualquier consulta que salte a su flujo, vea qué marca de tiempo ha golpeado, si está demasiado lejos a la mitad en la parte en que sabe que sus datos están en etcétera. Debes encontrar el registro de inicio en unos pocos intentos. Luego haga lo mismo para el registro final y reúna el conjunto de resultados de todos los registros intermedios.

Usted ahorraría en la sobrecarga del RDBMS. Esta podría ser la solución más eficiente (también podría combinar esto con descomprimir sobre la marcha) pero sería menos flexible. Si los datos de su "punto de datos" cambiarían un poco, probablemente tendría más trabajo del que tendría con la solución MySql.

    
respondido por el Martin Maat 19.11.2017 - 10:34
1

Puedes mirar:

  • Conjunto ordenado REDIS ZRANGEBYSCORE
    • enlace con la tecla como identificación del sensor
    • enlace para el script lua en este contexto
    • encuentre cómo agruparlo en los datos de tiempo
  • scylladb con eventualmente kairosdb
  • Una base de datos de series de tiempo escalables como InfluxDB (agrupación en clúster no es de código abierto aquí)

Creo que en tu caso usaría un scylladb agrupado correctamente.

    
respondido por el Vince 21.11.2017 - 23:37

Lea otras preguntas en las etiquetas