Writing to database

[BioniC187]

Hi guys,

I want to store some data captured by the Arduino into an Sql DB for later viewing.
I was wondering on the limitations of this.

Looking at the baud rate, if I select 115200, using 8 bits per byte, not even including start and stop bits, you get a rate of 14400.
Which is essentially 14.4KHz.

On the Arduino side of things, I intend to be sampling at a rate of about 500KHz for about 5 seconds.

So is there a way to work around this?

How can i write to Sql at such high rates?

Is there another method I can use for chucking in data to a pc from an Arduino without the high speed restriction?

 

[misterT]

On the Arduino side of things, I intend to be sampling at a rate of about 500KHz for about 5 seconds.

1) What are you sampling? single bits, 10 bit ADC values, 8 bit ADC values..??
2) What kind of Arduino?

BTW: If you plan to use the ATmega ADC you won't get even near 500kHz sampling rate.

Anyway.. what you need is buffer memory. There is probably 8K bytes of internal SRAM in your uC. You write your samples to the buffer and at the same time start sending the data out as fast as you can.. and hope that you don't run out of buffer space.


I did some calculations.

Dout = data rate out (samples/second)
Din = data rate in (samples/second)
B = buffer size (samples)
T = time (seconds)

You must have:

Dout > Din - (B/T)

Example with your numbers:
Data in = 500000
Buffer = 1000 samples
T = 5 seconds

Dout > 500000 - (1000/5)
Dout > 499800

So.. even with 1000 sample buffer, you'll need data out rate of 499.8 kHz.


Another example:
Data in = 500000
Data out = 14400
T = 5 seconds

B > T * (Din - Dout)
B > 5 * (500000 - 14400)
B > 2428000

So.. with 500 kHz in and 14.4 kHz out for 5 seconds, you would need a 2.5 Megasample buffer.


Yet another example:
Data in = 500000
Data out = 14400
Buffer = 1000 samples

T < B / (Din - Dout)
T < 1000 / (500000 - 14400)
T < 0.002059

With 500 kHz in, 14.4 kHz out and 1000 sample buffer, you can sample for 2.059 milliseconds. That is 1029 samples.

All the above is purely theoretical. Even if you can get 500kHz sample rate using arduino, you wont get 14.4kHz out and your SRAM probably cant hold 1000 sample buffer.

 

[BioniC187]

Thank you for your reply

Firstly, it will be off an optical encoder. The encoder can have a top speed of up to 4xxKhz, so I plan to work of a max of 500KHz to be safe.

I have a Mega 2560. BTW, it is only necessary to get from one phase of this encoder, as only one direction of rotation is important.

Maybe i should move to an ARM system that can have a few megabyes of ram, that can hold all of the samples, then after it can send to the pc.

Thank you for the theoretical calculations, it puts things in perspective. Tall task for such a small controller. Or maybe go for a lower resolution encoder. It all comes down to feasibility in the end. Don't want to break the bank for an ARM system, when all i gotta do is use a lower resolution encoder, or gear system

 

[BioniC187]

I just realised that i could use the R pi instead. And it's really cheap lol

 

[misterT]

What kind of encoder? If it is a quadrature encoder then I don't think you'll need to send data at 400kHz even if the encoder itself is spinning at 400kHz. Usually you count the pulses for a constant time period and then send the pulsecount.
Atmels XMega microcontrollers could be perfect for you. It has a quadrature decoder functionality.

 

[BioniC187]

Thanks, but I don't think that would be an option. I still need a high amount of RAM to store the pulses, I need to store the time between the pulses, that's the important information that I require.

But yeah, it is a quadrature encoder, it is optical.

I am really wanting to get the R pi, it has many peripherals that I want. The s-video, and the SD card slot is all a big plus to display and long term storage of the collected data. And yes, I am ignorant about ARM controllers, but the pi seems to have a lot on a nice little board. For what I currently want, I don't need a whole bunch of IO pins, I just need to read the encoder, and data from 3-4 other devices.

I think the pi will suffice for my purpose

 

[misterT]

Thanks, but I don't think that would be an option. I still need a high amount of RAM to store the pulses, I need to store the time between the pulses, that's the important information that I require.

But yeah, it is a quadrature encoder, it is optical.

I am really wanting to get the R pi, it has many peripherals that I want. The s-video, and the SD card slot is all a big plus to display and long term storage of the collected data. And yes, I am ignorant about ARM controllers, but the pi seems to have a lot on a nice little board. For what I currently want, I don't need a whole bunch of IO pins, I just need to read the encoder, and data from 3-4 other devices.

I think the pi will suffice for my purpose

Sounds like you have a good plan.. report here on your progress

 

[3v0]

SQL writes maybe too slow to use in real time. If you get into any trouble on the PC side you may want to initially write the data to a flat file and load it into the database after the run.

 

[misterT]

SQL writes maybe too slow to use in real time. If you get into any trouble on the PC side you may want to initially write the data to a flat file and load it into the database after the run.

You have a point there, but for 5 seconds of data coming from microcontroller through USART.. I think any PC/database can handle it. Real time? No.. Data logging? Yes.
I don't know what you mean by "flat file", but my "old" PC has 4 GB of RAM.. the USART is the bottleneck.

 

[3v0]

I am not saying you are wrong but it depends on what he has for a PC and what OS he is running and how bogged down it is by addware, malware, etc. The guys running CNC from PCs are either running Linux or XP because anything past XP is not responsive enough.

This is a reasonable definition of flat file (WebOPedia)

In databases a flat file refers to data files that contain records with no structured relationships. Flat files may contain only basic formatting, have a small fixed number of fields, and it may or may not have a file format.

My thinking was that it is faster to write to a file rather then executing SQL statements to store it.

 

[BioniC187]

Writing to anything is first limited by how fast data is received. So, usart will be slow for my data rate. He did the math ^^

My plan is to write all data to the RAM, at the fastest rate possible - hopefully. Then after i "record" the 5 seconds, i will empty that out to the sql database.
I think this will work

 

[3v0]

Sorry I bothered you. Next time I will not.

 

[BioniC187]

Sorry I bothered you. Next time I will not.

Have I offended you in some way?

 

[BioniC187]

Update :
I have found out that it is possible to send data at higher that 115200 bps. I can get up to 1mbps on the arduino. So, I am currently looking into that, also, maybe even new usart hardware that transmits at even higher speeds. Also, you don't have to send 8 bits, you can send 5 bits. So that would mean that you could essentially send more "bytes" per second, increasing the amount of pulses sent to the database.

I also got a database configured, and it accepts data up to microsecond accuracy

 

[BioniC187]

I wrote a little program for the arduino which sends out the letter "a" to the pc at 1000 ms intervals.

I've been fooling around with c#, and i made a little program that reads the serial port and displays the time interval between pulses, coz i wanted to check the accuracy.

the results aren't good. the interval is constantly changing.

Ignore all the nonsense and only bother to look at the line that says "INTERVAL:"



This could be due to a the pc timer generating the microsecond timestamp not being accurate, and then it could the arduino coz i used delay(1000) in the program. Tomorrow i will write the program with OVF timer or COMP timer and see how much better my accuracy becomes and will help me to deduce what is causing the non-constant time interval

 

[misterT]

The "PC timer" is really not that good for accurate timing. Are you using the "System.Diagnostics.StopWatch" or "System.Timers.Timer"? You should be using the diagnostics StopWatch.

For very accurate timing you can use Win32 multimedia timer. But, for this purpose I don't think it is worth the effort. I think the rs232 communication also introduces jitter when the received byte passes through all the drivers, operating system stuff and .Net runtime environment.

If you are interested to do very accurate timing, then take a look at links below:
https://www.codeproject.com/Articles/17474/Timer-surprises-and-how-to-avoid-them
https://www.codeproject.com/Articles/98346/Microsecond-and-Millisecond-NET-Timer
https://www.codeproject.com/Articles/5501/The-Multimedia-Timer-for-the-NET-Framework
https://www.codeproject.com/Articles/2635/High-Performance-Timer-in-C

 

[BioniC187]

Actually, I'm just using "string dt = DateTime.Now.ToString("HH:mm:ss.ffffff");" . But you're right, I was doing some reading, and the stopwatch has good accuracy. I'm gonna write the arduino code to help reduce my error in timing from that side, and give stopwatch a go. Hopefully it'll yield good results.

Thanks mate

 

[misterT]

Actually, I'm just using "string dt = DateTime.Now.ToString("HH:mm:ss.ffffff");" . But you're right, I was doing some reading, and the stopwatch has good accuracy. I'm gonna write the arduino code to help reduce my error in timing from that side, and give stopwatch a go. Hopefully it'll yield good results.

Thanks mate

Ok, yeah. You picked probably the worst method for timing in C#.

I have not used much Arduino IDE. Bad thing about it is that it hides much of the details. For accurate timing (in microcontroller) you should use Timer interrupts.

You know that you can write your code in pure C using the free Atmel Studio and upload the compiled hex file into the arduino board (with uploader program like this: **broken link removed**).
If you want to seriously learn/program microcontrollers I would recommend you ditch the Arduino IDE and learn proper C.

 

[BioniC187]

Ah yes, I've seen that Atmel studio around. Didn't think about trying it though.

Luckily, I'm a quick learner with programming.

But even though, what will be the advantages of ditching the Arduino software, and coding in pure C?

If the advantages are significant I will definitely move on to program in pure C

 

[misterT]

Using Arduino sketch functions like "analogRead()" hides all the details and does not let you fully control the ADC conversion. You might want to change the conversion time for more stable readings etc. Or you might want to run the ADC in "free running" mode.

Coding with pure C gives you full control of the microcontroller.. and you will learn how the microcontroller actually works. And then you can do relly impressive stuff like write your own multitasking kernel or generate TV signal with an 8bit uC. Like this: https://www.youtube.com/watch?v=z41MToCukc0

Arduino IDE hides everything from you and you won't learn anything. Have you even ever read the atmega datasheet? I bet you don't have to if you use Arduino IDE.

EDIT: In your UART communication the Arduino sketching is a huge bottleneck. For max speed you need to use UART interrupts with software buffer to send data.