Greetings

I work in a factory where we have this old PLC-controlled production line that often needs maintenance. It has a number of sensors (connected to PLC) that monitor the status of different parts. We're trying to get some statistics on the production efficiency, and we need a way to collect the signal data in realtime (we're not allowed to tamper with the PLC). Some of us got the idea of using a cheap Raspberry Pi board to do the monitoring, as we can easily route wires from the main controller box to the raspi board (via an I/O shield with level converters).

The goal here is to have the raspi monitor the signals and store the data on a USB key. The board is also going to be connected to the corporate LAN network, and it will be running a web server. We want to make it so that the superiors will be able to connect to the device from their office computers via a web browser. The interface will be displaying real-time sensor data as well as history in the from of graphs/charts. This will let us see what's been going on last week, last month, last year, from day A to day B, from hour C to hour D, etc.

The question here is what is an elegant way to store such data efficiently on a USB flash key? We can monitor up to 8 signals with the I/O shield, but we'll only monitor 4 for now. The signals are in either high or low state - they get scanned about 100 times per second (configurable in raspi, we use a python script for monitoring), but they only change their state once per cca 3 to 8 seconds (or sometimes they don't change at all... production line stopped). We'll probably want to log just the state changes here (with time stamps). What can we use to store this kind of data? It should be easily retrievable by a python/php script. We've thought of using a sqlite3 database, but we also found the RRD graphs to be very visually appealing, but they use a rrd database instead. Any other solutions?

Having been using linux for a while, I'm also worried about the frequency of data writes. As we're using flash memory, it could drastically degrate the lifespan. How can we address this? If we could somehow cache the data in memory and have it written down every hour... perhaps by using appropriate filesystem mount options for the flash key? Or is there a better mechanism? The script should also be able to retrieve cached data when accessing the database, as if the cached data was already written in the db.


Thanks in advance!

Your question is too generic to be answered.

PLC companies usually offer some sort of program to monitor can signals or directly on the plc. If we knew the brand and model we might know how well such a plan might work. Most cheap plc's are pretty dumb. They have connections for input and output but no way to monitor internally what is going on.

Forget the PLC's. I'm asking how to store some specific data on a USB key.

Eight high or low states can be stored in a single byte.
Check the byte after each scan. When the byte changes, write the byte and timestamp.
It is the rewriting of flash memory many times that causes failure. Writing 3 bytes every 3 seconds would take ~130 years to fill a 4GB flash memory.

jefro: I've asked the admin, he said the PLC does not offer any decent logging via software. And the cost of upgrading it is way too high - the CEO won't allow it.

Good point! Out of curiosity, which parameters have to be adjusted so that the system will use write caching for our USB key until at least 512 bytes (1 block) of data have been cached and then trigger the flush?

I cannot say for sure, but it is my understanding that this is all handled by the kernel. The write buffers are only flushed to disk when required or when a sync is done. Have you ever noticed that in Linux, when you disconnect a USB device, there can be a delay before you receive a message that the device is OK to disconnect? During this time, you may see the access light on your device flashing as the buffers are flushed.

You said the board is being connected to the corporate LAN. Can't you store the data off-board? And copy back to your USB each half hour.

Since you are executing some program already to sense the inputs, it should be trivial to keep data in memory for an amount of time. I mean in your program as array or list or structure or whatever. And do the write action to your database each 30 minutes.

I have been using MySQL for this kind of storage. Why MySQL? Because it is accessible thru a TCP connection from everywhere by everything, including Bash. At the time of development of my application I didn't have use for it, but later it prove to be very useful that access was versatile like that.

For displaying I heard about rrd as well, but didn't use it. There are also numerous PHP open source projects. And maybe most promising, Google code base provides also something: https://developers.google.com/chart/.../making_charts

You might google around some more in Google code. I haven't used it yet, but it is a candidate for the project I am working on.

jlinkels

PS: I have ordered a number of raspi's myself as well for these kind of projects. But delivery time seems to be quite long. Where did you get them?

It was considered, but in the end we decided against using an off-site logging server. The goal was to store everything on a USB key that is always attached to the raspi board. This is to prevent the degradation of the system SD card (and filling it up completely).

This is what I'm having trouble with. I can't decide how to store the data. Should I use sqlite? RRD? Some other way? If I keep the data in memory like that then I can't retrieve the last 30 minutes via database query, can I? I'm sorry, but this is where I'm lost. I'd like to be able to retrieve data on demand in real-time (can be done by reading signals directly) and the relevant history up to the current point (need to query the database and the data in memory not yet written to the DB). So... how to do that with rrs, sqlite, etc? Perhaps I can trigger a flush each time a database is accessed?

We thought of using mysql, but decided against it because it's too heavyweight for a project like this.

Thanks, I'll check those out. RRD graphs are the nicest I've seen, but they seem to be firmly attached to the RRD databases - if we go for rrd and some other database, we'll need to find a way to convert that data to a rrd-compatible format for graph drawing.

We got them here: http://newit.co.uk/shop/products.php?cat=26

Best regards!

If that is the requirement you must write to disk immediately. Or have something really complicated, which I would not recommend. That "really" complicated would be that your application is a server, TCP protocol, on top your proprietary protocol. Your application would keep data in memory and older data on disk. For data retrieval your application would send the most recent data from memory, the older data from disk. Not recommended.

About MySQL: I don't know yet what the Raspi can do, but I had mysql running on a 1 GHZ Pentium 3 with a few hundreds of read/writes per second. It took up 20% processor time. And I had 128 MB (MB, not GB) of memory in that machine.

Next option: create a RAMFS on your raspi. Put the database in the RAMFS file system. Periodically dump the database to disk to avoid data loss when power is lost. It would mean you have a few days or weeks of data in the RAMFS, would that we sufficient?

Another option: run MySQL on anything else but the raspi. It will run on anything > 1 GHz > 128 MB (see above, no GUI of course). Let the raspi communicate thru a MySQL client with the other machine. MySQL is multi-user, concurrent access, so you can have multiple raspi's hooked up and all talking to that DB.

I think I would prefer the last option. It is unthinkable (really ) that you can't find something to run a database on. In that case you don't store anything on the raspi but only use it as DAS.

What PLC do you have BTW? Can't you access it thru ModBus? Nice modbus drivers exist for Linux. Saves you the hardware sensing.

Last thing: I am talking about MySQL because I know it best. Other databases might suffice as well. But I think SQLite is only for local non-concurrent access.

Thanks for the link. I see they have it in stock. Mine has a few weeks delivery time. Let's see what happens, I might order a few additional ones, they are cheap.

jlinkels

Thanks for the update.

Not sure you really need a database. Why not store it as raw data in some file or collection of files? Decode it later. Could be deliminator based if you wish. Could be line by line return or such.

In all this you could end up with a lot of data if you have a few thousand plc's and a few thousand input/output devices. I have seen multi-factory installs of this order where a process is too large for single buildings. Product goes from building to building and the darn plc's are all over the place. Up in the roof, down in the basement, usually too hard to reach.

We don't have thousands of PLC's.

I've checked today there's only two of them. One is a Honeywell HC-900, the other is a simens S-200 I think. We have no decent scada, theres barely any logging going on, and the command PC only displays some process sketches with sensor lights blinking. A poor investment, if you ask me. The admin interface is password-protected. An upgrade would likely cost us a lot, so the idea was we could cheaply obtain the statistics by reading the signals directly with a raspi + expansion board.

I've studied RRD a bit and I found it to be a satisfying solution. There's still an option to use mysql or sqlite though. It all comes down to what we need. Looks like every-minute sampling is going to be sufficient, so we'll likely go with rrd.

Regards,
~displace