1

I have a device network where each device has different sensors (temperature, humidity, etc..). Each device uploads raw measured weather data every 10 seconds to a apache/php/mysql REST API. There are a few 100 devices sending data and this will become more over time. Currently the data is added to a mysql table without any processing.

The string format of the device message is something like:

&01AA*020122#

Which means that sensor 01 measured AA and sensor 02 measured 0122.

I want to put the decimal converted hex values in a table so they can be processed further. What I do now is run a script every minute to see what new strings were added to the database. I will then convert these strings to decimal values and put them in a table.

Performance wise I have the feeling that polling new data every minute is pretty silly.

What would be the best way to do this?

  1. have the rest api split the string and convert the hex values to decimal values and insert this immediately into the database

  2. poll the database for new inserted strings and convert the hex values

  3. use a mysql insert trigger to process the string by the database

  4. any other ideas.

migrated from serverfault.com Jul 3 '15 at 17:15

This question came from our site for system and network administrators.

  • Your current format only allows for 256 sensors before it becomes ambiguous as to the data structure. – user40980 Jul 3 '15 at 18:09
  • I am sorry, I made an error in describing the system. Each sensor is made of different measurement sensors; temperature, humidity, etc.I will replace the confusing name sensor by 'device' – Enrico Jul 5 '15 at 8:45
3

The best option is the one with the fewest things that can go wrong. This is the "the web API converts the value and inserts it into the database." There is no significant amount of computation necessary, and changes are you don't even need to convert it into decimal first. You can insert using the unhex function in MySQL (docs).

If you were going for something a bit more enterprisy, this is the model for a message queue where something would be getting the data from the sensors, and sticking into a message queue as a producer, and then something else takes the messages from the queue as a consumer and does something with them (insert them into a database). More on this at a RabbitMQ tutorial.

That said, that message queue is overkill and you can just stick it in the database from the start. Do that.

  • Definitely like the idea of message queue! Very scalable going forward. – Alexus Jul 3 '15 at 18:21
  • @Alexus message queues allow for some very elegant architectures when they are put to use. It allows you to scale up nicely for the publish/subscribe models and also do things with routing. Its one of those architectures that can be very flexible in its use. In this case though, unless one is really pounding on it in such a way that the processing of the message was the bottleneck, the simpler structure would probably be better. – user40980 Jul 3 '15 at 18:27
  • Agreed. Just to add with message queue, you can also add multiple components to process the message pipeline each doing their own thing and re-queing in a different queue. Also error handling and data loss prevention is easier :) But if system is relatively small, it is an overkill indeed. – Alexus Jul 3 '15 at 18:33
  • Thanks so actually the best thing to do is split the message into it hex parts and write the decimal values (based on the sensor number) to the table? I understand that the conversion from hex to decimal is very simple, but splitting the message and depending on the sensor id (01 or 02) writing to a table, seems a bit more logic? – Enrico Jul 5 '15 at 8:51
2

Option 1. The string data should definitely be processed into more appropriate data types before insertion. That measurements are provided in hexadecimal is a detail of data transfer, and isn't important when it comes to storage (hexadecimal being merely a representation of numbers). Note that the script wouldn't be converting the string data to decimal (which is just another representation), but to integers (which is a data type).

Also, processing the data allows the table to be normalized. The raw string isn't even 1st normal form, as it contains a repeating group of compound data (sensor number & measurement).

  • same comment as above: changing the hex to decimal is very lightweight, but splitting the message into its sensor id based values is a little bit more work? – Enrico Jul 5 '15 at 8:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.