CAN to MySQL logger complete

Code to run on Linux for CAN interfaces.
User avatar
linuxkidd
Site Admin
Posts: 345
Joined: 2005 Jul 22 15:48
Location: Anywhere, USA
Contact:

CAN to MySQL logger complete

Post by linuxkidd » 2005 Aug 02 23:17

Hello everyone!!

I've completed my CAN Logger. It's written in Perl, and requires the Device::SerialPort perl module ( You can find it at http://www.cpan.org ).

Instructions are found in the README file. The logger allows you to mark spots in the data collection for easy distinction of event specific data.

I'm now working on a PHP web front end for the analysis!

Have fun!!


** NEWS FLASH **

Seems that it's not getting the lines the way it should... needs some more work on the serial interface side. You can download it and tweak / fix if you desire, but don't use for production data.

** NEWS FLASH **

can_logger-1.0.tgz
Download it NOW!!
Last edited by linuxkidd on 2005 Aug 03 12:53, edited 2 times in total.
If you can read this, the light is still red.

User avatar
tonyt
Site Admin
Posts: 51
Joined: 2005 Jul 22 21:44

Post by tonyt » 2005 Aug 03 06:45

What no windows version?
Share what you learn....

User avatar
linuxkidd
Site Admin
Posts: 345
Joined: 2005 Jul 22 15:48
Location: Anywhere, USA
Contact:

Post by linuxkidd » 2005 Aug 03 18:48

Ok... well... It seems that an Interpreted language is not going to work for this task. I'm running on a 2Ghz P4 laptop, and it's not running fast enough to keep up w/ the data flow.


SO, on to compiled languages... C anyone?

Just hang tight while I dust off my ancient C skill set. If anyone else wants to chip in on this..

Basically, the Serial port get a stream of data. No flow control at all. Each line is terminated w/ a ^M, and begins with lowercase 't'. I want to have it log to a MySQL database with this schema.

Any help greatly appreciated... Pardon me while I stumble through this one...
If you can read this, the light is still red.

RadarmagneT
CAN? Tin or aluminum?
Posts: 39
Joined: 2005 Jul 26 10:16
Location: Raleigh NC

Post by RadarmagneT » 2005 Aug 04 00:22

linuxkidd wrote:Ok... well... It seems that an Interpreted language is not going to work for this task.
Are you sure it's the Perl interpreter bottle necking? (good assumption)
It could be the MySql transactions.

Have you tried dumping from Perl to null and/or stdio to see if it can keep up alone?
Are there Perl compilers?

I suggest moving the code to Java so it will be x-platform without so much work as C. (it still might be a bottleneck)
I have rusty C and partial Java "hackey" programming skills, I might be able to help, but I don't have the stream generator... no CAN tap yet...

Maybe you could capture 5-10Meg of the stream and just dump it to a file for "testing" the code away from the generator.

RadarmagneT
CAN? Tin or aluminum?
Posts: 39
Joined: 2005 Jul 26 10:16
Location: Raleigh NC

Post by RadarmagneT » 2005 Aug 04 00:39

Another comment...
Preprocessing the CAN packets in the code (breaking down the head/info/tail) may produce a smaller DB transaction stream...

though I don't really think the DB transactions are the issue.
RadarmagneT
2006 Red Charger R/T...

User avatar
TonyB
What's hacking?
Posts: 26
Joined: 2005 Jul 28 14:41
Location: St Louis, MO

Post by TonyB » 2005 Aug 04 10:04

linuxkidd wrote:Ok... well... It seems that an Interpreted language is not going to work for this task. I'm running on a 2Ghz P4 laptop, and it's not running fast enough to keep up w/ the data flow.
I dunno... I've gotten Perl to move a lot of data on less powerful machines. Without actually being able to test anything yet, I'd be inclined to agree that the database is more likely the bottleneck. Inserts are incredibly slow on any SQL database.

How often does a packet come through? Is it constant or bursty? Can you tell the CAN dongle to only pass on certain types of packets, or do you just get everything? After the IDs and data structures are decoded, we'll probably only want to be saving certain packets to the DB, which will help too.

To tell where the slowdown is happening, it would be helpful to split the program out into two or three parts: one that pulls the data out of the CAN dongle and writes the data to a file, one that reads from the file and inserts it into the DB. The parsing of the data could go into either program or split into a third, but I doubt that's the slow part anyway.

User avatar
TonyB
What's hacking?
Posts: 26
Joined: 2005 Jul 28 14:41
Location: St Louis, MO

Post by TonyB » 2005 Aug 04 10:06

RadarmagneT wrote:Maybe you could capture 5-10Meg of the stream and just dump it to a file for "testing" the code away from the generator.
That would be incredibly helpful, if you have the space and bandwidth for it!

User avatar
tonyt
Site Admin
Posts: 51
Joined: 2005 Jul 22 21:44

Post by tonyt » 2005 Aug 04 10:11

From what I saw it's CONSTANTLY streaming data...200-300 hex lines a second...
Share what you learn....

User avatar
linuxkidd
Site Admin
Posts: 345
Joined: 2005 Jul 22 15:48
Location: Anywhere, USA
Contact:

Post by linuxkidd » 2005 Aug 04 10:30

Ya.. I'm in agreement with you guys... it probably is the SQL calls. I'll split it out and see what happens.

Also, I've got one capture of raw data, It's not HUGE, but it's something to see what your dealing with, and if you need a larger file just for stress testing, you may be able to loop it. I'll try and capture a larger sample when I get home tonight.

I've also downloaded canpoll.c from the Prius CAN Project, and reworked it to the standards for this project. (And it compiles w/o problem) I plan on trying this tonight. Basically, I see this as spewing data to a file on disk, then a perl script to hash through it and insert it into the database.

The thing that's cool about this canpoll.c and the perl script I wrote, it allows you to mark spots in the data to easily find specific events. So, this should work well..

If anyone can find specific slowdowns in my perl script that I've missed ( I am watching for keypresses on the keyboard as well... ), please feel free to fix and share!
If you can read this, the light is still red.

RadarmagneT
CAN? Tin or aluminum?
Posts: 39
Joined: 2005 Jul 26 10:16
Location: Raleigh NC

Post by RadarmagneT » 2005 Aug 04 11:15

TonyB wrote:
RadarmagneT wrote:Maybe you could capture 5-10Meg of the stream and just dump it to a file for "testing" the code away from the generator.
That would be incredibly helpful, if you have the space and bandwidth for it!
Yes, I could get that up somewhere.
RadarmagneT
2006 Red Charger R/T...

RadarmagneT
CAN? Tin or aluminum?
Posts: 39
Joined: 2005 Jul 26 10:16
Location: Raleigh NC

Post by RadarmagneT » 2005 Aug 04 11:17

linuxkidd wrote: If anyone can find specific slowdowns in my perl script that I've missed ( I am watching for keypresses on the keyboard as well... ), please feel free to fix and share!
My perl skills are weak but I'll take a look.
RadarmagneT
2006 Red Charger R/T...

User avatar
linuxkidd
Site Admin
Posts: 345
Joined: 2005 Jul 22 15:48
Location: Anywhere, USA
Contact:

Post by linuxkidd » 2005 Aug 04 11:20

RadarmagneT wrote:
TonyB wrote:
RadarmagneT wrote:Maybe you could capture 5-10Meg of the stream and just dump it to a file for "testing" the code away from the generator.
That would be incredibly helpful, if you have the space and bandwidth for it!
Yes, I could get that up somewhere.

This is not going to be a problem to host here. The capture linked above is 24.1 times smaller than the actual text file. So, a 10 meg capture will go down to about 400k.. :) ( even if it were 10 meg, it wouldn't be a problem where we are. )
If you can read this, the light is still red.

05MagnumRT
What's hacking?
Posts: 23
Joined: 2005 Jul 28 16:03
Location: Dallas (Frisco), TX

Post by 05MagnumRT » 2005 Aug 04 11:31

I don't know if Perl can do threading, and I'm assuming that Java can. I'm a C/C++/C# guy, so if you decide to go in that direction, I'll be glad to help. If you're careful C++ is just as transportable as Java, and C# definitely is, as long as the target platform has a CLR (I think there's one for Linux, at least in the works). I've been making my living on Windows for several years now, but I was involved in Linux for quite awhile before that (even contributed code to the ethernet device drivers way back in the 0.95 - 0.96 era kernel).

Anyway, if it is that SQL bottlenecking, we'll need to separate the listening from the processing. I'd recommend a thread for listening which just gets a complete message and puts it into a thread-safe queue. A second thread would take the message from the queue, do any processing, and put it into the DB.
That way, the listener can keep up with the live stream, and whenever there is processing time to process and save, it does.
For what it's worth, I'd say use C++ or C#, you can encapsulate the threads and queue easily - keeps the code nice and clean.

User avatar
TonyB
What's hacking?
Posts: 26
Joined: 2005 Jul 28 14:41
Location: St Louis, MO

Post by TonyB » 2005 Aug 04 11:41

Another thing I just noticed is that you're calling 'stty' as an external program twice for every CAN message you receive. It would probably speed things up a lot if you just set the terminal to cbreak mode at the start of the program (either with stty or Term::Readkey::Readmode()), or better yet, just use Readkey() from Term::Readkey in non-blocking mode.

I'm going to try to write a new version of the program that splits the reading and saving to separate processes. Should I try to keep to as few non-default modules as possible, or would it be OK to use some more complicated ones like Event and Expect?
1964 Ddoge 440
1965 Dart GT
2005 Dodge Magnum

RadarmagneT
CAN? Tin or aluminum?
Posts: 39
Joined: 2005 Jul 26 10:16
Location: Raleigh NC

Post by RadarmagneT » 2005 Aug 04 11:52

I was thinking treading would be something that HAD to be implemented eventually.

If 05' can help with the C/C++ code that would be great...
Since this is basically serial processing and (hopefully) an open DB backend, then x-platform shouldn't be hard.
RadarmagneT
2006 Red Charger R/T...

Post Reply