In one of our recent projects, we worked on developing a modern reactive web app, where we had to present IoT device data aggregated and updated in real time. We faced several challenges involving a large volume of data being generated from devices and mandate from the client to use their existing infrastructure. Let’s understand the use case first.
We get a continuous feed from IoT device, every second a device sends data to the Server, We have about 200 such devices sending data to the server every second. Here is how we get the data from Device.
DeviceID | Datetime | Rotations | CummulativeRotations |
df3ffs355fgg3ss3 | 2018-07-01 15:30:01 | 23.66 | 650.36 |
We had a need to aggregate the data at the minute interval and report it to the users via a live dashboard page. The graphs had to auto-update with live data in real-time. Multiple views presented today’s data, along with historical data for weekly, monthly and custom data ranges with multiple Y-axis variables, something similar to the graph below
Source: https://www.chartjs.org
Our client had prior investments in SQL Server and had mandated that use the existing database and infrastructure Here’s the stack at the end of the development cycle:
- React using Material-UI using MVVM – UI
- .NET Core 2 – Web API
- SQL Server 2012 – Database
- Signal R – Web Sockets for real-time connectivity
- Redis Cache –
We selected open-source MVVM framework as it baked in reactive backend using SignalR and for two way data binding. The UI widgets were updated in real-time with aggregated and cached data.
During the development phase, we tested with a single IoT device and data volume was manageable using stored procedures and queries, However, we later realized the volume of data would reach around 6+ billion rows in the primary table every year (200 device data per sec x 86400 seconds x 365 days)
Initial Solution
We went the traditional approach of using LINQ and Stored procedure to fetch data from DB. Linq aggregated the data at a minute level. The responses seemed fine when we tested with 1 month of data and a single IoT device.
We used SignalR to push the data from back-end to the UI at specified intervals, the initial responses with our testing data set was quick, but as the data kept growing we started facing latency issues. We tried indexing and DB tuning but it didn’t help. We had to address the issues head on so we ventured on step by step optimization journey.
Step by Step Optimization
We realized that we wanted the reporting on the frontend at minute level, seconds level reporting was not planned in the near future, so the first step we took was aggregated the data at minute level for each category and device into a new table, this brought down the number of rows to 92 million per year from 6+ billion.
This made some difference in response times, but it wasn’t enough, so we decided to use a Caching engine to store the aggregated data. We narrowed on Redis cache. Since there was no change in data till yesterday, decided to create 2 Redis viz DataTillYesterday and TodaysData, and scheduled 2 events to rebuild the cache:
- DataTillYesterday cache is being built once every night with data for last year
- TodaysData cache is being rebuilt with last-minute data every few seconds with updated data from devices.
Now there is no need to go to SQL Server and fetch new data or old data. The above 2 Caches have all the data that is required to build Today, Weekly, Monthly and Custom range graphs on the frontend. Response from Redis Cache is in milliseconds and there is no Lag and data is being pushed in realtime to React.
Here’s the code to build DataTillYesterday cache in Redis
IList RotationssData;
using (DataContext db = new DataContext())
{
DateTime firstDateOfYear = new DateTime(2017, 01, 01, 0, 0, 0);
DateTime lastNightDate = new DateTime(2017, 10, 21, 23, 59, 59);
RotationssData = db.RotationsData.FromSql(“sp_GetRotationsData @p0, @p1 “, firstDateOfYear, lastNightDate).ToList();
}
IDatabase cache = RedisConnectorHelper.Connection.GetDatabase();
await cache.KeyDeleteAsync(“DataTillYesterday”); //Delete cache
List lst = new List();
foreach (GetRotationsDataView o in RotationssData)
{
lst.Add(JsonConvert.SerializeObject(o));
}
await RedisConnectorHelper.RedisCache.ListLeftPushAsync(“DataTillYesterday”, lst.ToArray());
Code to fetch data from Redis
var redisData = RedisConnectorHelper.RedisCache.ListRange(“DataTillYesterday”);
if (redisData.Length > 0)
{
List objs = Array.ConvertAll(redisData, value => JsonConvert.DeserializeObject(value)).ToList();
}
