Riceviamo dati in tempo reale da oltre 1000 sensori, ognuno dei quali invia un punto dati ogni 10 secondi in media, il che equivale a circa 1 milione di righe di dati ogni giorno. Il nostro sistema offre agli utenti la possibilità di selezionare qualsiasi sensore e un intervallo di date e di scaricare i punti dati come file Excel.
Abbiamo visto che i nostri utenti sono per lo più interessati a dati che hanno meno di 30 giorni. I dati di più di 30 giorni sono probabilmente già stati scaricati. Solo circa l'1% delle nostre richieste di recupero dei dati proviene da dati vecchi di oltre 30 giorni. Tuttavia, non possiamo dire che questi dati siano totalmente inutili, perché i nostri utenti a volte vogliono scaricare dati che hanno ancora più di un anno. Eliminare i vecchi dati non è una possibilità.
Attualmente stiamo utilizzando il database MySQL per archiviare i dati e tutti i dati vengono archiviati in una singola tabella. La tabella ora ha oltre 60 milioni di righe. Usiamo SSD e abbiamo gli indici giusti grazie ai quali il recupero dei dati avviene ancora molto più velocemente.
Un esempio di query del database che usiamo per selezionare ogni minuto è:
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
Attualmente, questa query impiega meno di 5 secondi per recuperare dati di oltre 30 giorni per un sensore specifico.
Man mano che più dati vengono archiviati in questa tabella, diventeranno più grandi, forse fino a 2 miliardi di righe nel prossimo anno (aggiungiamo anche più sensori ogni giorno). Non so come la prestazione della query sarebbe in quella fase. Per me, archiviare tutti questi dati in un database MySQL non sembra essere giusto, perché l'accesso è molto raro e il fatto che i dati siano indicizzati a più di 4 mesi non sembra necessario.
Un approccio a cui ho pensato è di avere solo 30 giorni di dati in MySQL e spostare i vecchi dati in file flat con una struttura di cartelle come /old_data/%YEAR%/%MONTH%/%DATE%/%PARAM_ID%.dat . In questo modo la dimensione dei nostri dati non diventerà ingestibile ma allo stesso tempo i dati sono ancora indicizzati sotto forma di file flat su disco.
L'approccio attuale è buono in scala? Lo spostamento di vecchi dati in file flat aiuta o no? La memorizzazione di tutti i dati in una singola tabella è corretta? Abbiamo bisogno di cambiare il nostro motore di database stesso? Si prega di dare i tuoi pensieri su questa architettura. Grazie mille in anticipo!