MariaDB SkySQL allows Certified Power to collect and record data coming in from the IoT sensors on its snowplowing trucks, which lets cities, counties, and state governments reduce water and salt use, saving them millions of dollars while also being more environmentally friendly.
Snow plowing operations are increasingly more complex. Winter weather is becoming more variable and more extreme in some cases (e.g., last December’s bomb cyclone storm). Users, local municipalities, and other agencies are used to real-time updates on everything from their Uber rides to package deliveries to traffic congestion. Departments of Public Works, Transportation, and other agencies need to know which roads have been plowed and when other roads will be cleared. Fleet operators are under pressure to rein in costs. And if all of that is not enough, there is more pressure to address sustainability concerns by reducing the amount of salt applied to the roads.
Certified Power (CP) is trying to help in all these areas with its offerings. Powered by MariaDB SkySQL, a second-generation cloud database running on Amazon Web Services (AWS), CP integrates vehicle sensors, an automated electronic spreader control system, and wireless data transfer into its snowplowing trucks. Fleet managers are able to leverage the Internet of Things (IoT) telematics to build maps and reports from the data collected, resulting in reduced salt used on roads. Additionally, the data is automatically compiled and downloaded into the snowplowing truck’s spreader control system and then streamed to MariaDB SkySQL Cloud Data Services once the snowplowing truck returns to headquarters.
RTInsights recently sat down with Jeremy Miller, R&D manager and senior principal electrical engineer, and Adrien Holtzman, a senior software engineer, both at CP, to discuss their work in this area, how MariaDB SkySQL helps those efforts, and the benefits the work provides to the company’s customers.
Here is a summary of that conversation.
RTInsights: In general, how do your customers leverage IoT telematics?
Miller: Our customers use our winter road maintenance system to know where trucks are, what roads have been plowed, the material that they put on the roads, how much was dispensed, and in some cases, how the road’s grip improved as they put the material down. They can do this in real time if the proper sensor is on the truck. And also, back in the office, if they have in-road sensors, they can look at the sensors after the truck passes and see how the grip is improving.
RTInsights: The winter season is upon us. Are there any early predictions on what to expect?
Miller: We’re seeing a lot more usage of trucks that can lead to more breakdowns. One thing our customers depend on us for is to spot things that break, supporting predictive maintenance. If a sensor fails or trucks are not calibrated, that is reported to our system, so they have their mechanics update the trucks and get them ready for the next event.
RTInsights: How has MariaDB, and particularly MariaDB SkySQL, been a good fit for your business needs?
Holtzman: SkySQL has been an extremely good fit for our business needs because, as a small development group, we don’t have a lot of dedicated resources to configure, deploy, and maintain servers.
In addition, using MariaDB has worked out well because there is no need for any exotic skills. This is all stuff that integrates extremely well into the existing tool sets that we have. And virtually anybody who comes aboard can be brought up to speed very quickly to work on the system.
RTInsights: Are there any results you can share about using MariaDB?
Holtzman: MariaDB SkySQL allows us to collect and record more data coming in from the IoT sensors on our snowplowing trucks, which lets cities, counties, and state governments reduce water and salt use, saving them millions of dollars while also being more environmentally friendly. We also saved over 60% in total database cost by migrating to SkySQL and leveraging their database-adminstrators-as-a-service, SkyDBAs.
The MariaDB SkyDBA team and the support they have given us has been really helpful. The SkyDBA team helped us through the initial setup and questions, and when we do see problems or have issues that come up, they’re there to help us immediately. They’ve been really great that way.
Furthermore, our experience with SkySQL compared to our old on-premise database is night and day. Our old one was frequently down, and it was our responsibility to stand it back up. This one’s been up the entire winter season without an issue.
RTInsights: Let’s focus on the snowplow application. How much data and what kind of data is collected? What issues and challenges did you face before going on SkySQL?
Miller: The data that we collect from the truck is speed, direction, plow down, temperatures, on-route vehicle data, and more. In a month’s time, that’s probably about 500 megabytes of data on average, sometimes a little less depending upon how many storm events they have.
We do breadcrumbs, so every five seconds, we take all those readings. And so, the operator can see on a map in great detail everything that they’ve done and what the truck is doing. And if something’s failed, they’ll get an error report from in-cab. And then, we’ll also get one for management through the cloud service.
In our old system, we didn’t run a database, and we ran flat files. Our processing power required was a lot more. And our downtime was a lot higher. I think our downtime was approaching 50% in some cases during the winter seasons, whereas with SkySQL, we’ve experienced zero downtime to date; – 100% uptime makes our life a lot easier and, more importantly, our customer’s life a lot easier.
Additionally, when we were working with flat files in our old system, anytime we wanted to do a different type of reporting, a different type of summation for the customer, we would have to write custom software to do it instead of being able to just string together pretty trivial SQL queries to get the information the customer is looking for.
Holtzman: With respect to how much data, just looking at the total size of recorded sensor data, there are about 180 gigabytes of data for 18 months for about 500 trucks.
RTInsights: How do MariaDB SkySQL and other technologies help with your work?
Holtzman: As a developer, I am able to develop an entire application without having to go through a systems team and without having to have any knowledge of servers. That was extremely beneficial to get things up and going quickly.
Miller: In our old system, 90% of our customers were Wi-Fi based. And with the flat files, a lot of pre-processing power was needed, and generating reports was difficult. Our new era here started in 2022. And going into 2023, we are going to use cellular activity from the truck to the cloud. We have a real-time response from the truck to the cloud. A very minimal delay allows us to do reports a lot quicker and make changes for our customers in a much shorter timeline than our old system would allow us.
RTInsights: Could you talk a little bit about the cloud aspect of the solution and why that is so important?
Holtzman: As far as simplicity goes, it couldn’t get any better than this. Being able to develop on the cloud instead of either on a local server or something that’s managed by the IT department has made it infinitely simpler to get the application running. As well as considering that we have the rest of the application deployed on the cloud already, being able to integrate that directly into our AWS cloud has made sure that the whole thing is a lot faster than if it was two separate clouds. That makes it extremely easy to integrate as well.
RTInsights: What are the benefits that you and your customers derive from using MariaDB SkySQL?
Miller: For our customers, it’s the uptime. Our uptime has been a hundred percent this season. And they have the ability to look at the data, and if they see something that they want to look into further or deeper, they can.
There is the speed at which we can do things. A good example is low battery warnings that we record in the system. It wasn’t in the report we generated last year when we launched the system, but it was a request. And three days later, we had the solution on the webpage. So, our turnaround time to meet our customer needs is a lot better, which makes them a lot happier.
Holtzman: And as far as the development side, the cloud is so important because I can get other developers on my team working on the system without having to go through IT. It’s always on and always available, and I never have to manage anything or worry about backing up the server because that’s all taken care of.
RTInsights: Are there any things that we missed?
Holtzman: One thing that’s been really important to our team is the ability to scale up, scale down, add read replicas, or anything like that. Also, our business, as far as usage, is very seasonal. So, during the winter months, our usage is easily three or four times what it will be during the year round. The cloud nature of this solution gives us a lot of flexibility as far as meeting that demand and reducing our cost of operations to support that demand.
