Speed of INSERT INTO quirries

[fellowbak]

Hi there. I've used this awesome libary a couple of times now with success. This time I'm working on a project where i would like to insert values into a database multiple times per second. However, it seems that I can't execute more than roughly one INSERT INTO query per second. Is this a limitation of the hardware, a hidden delay or what could be the cause.

I'm using the complex_insert example, an Arduino Ethernet Shield and Arduino AT Mega 2560.

I'm hoping to hear from some of you. Thanks

[ChuckBell]

Hi,

Yes, I am afraid the Arduino is notoriously slow. Achieving many inserts per second is beyond the capabilities of most Arduino boards. Mainly due to the slow networking hardware.

However, there are ways around it. One I like best is to buffer the sensor readings either to eeprom or SD card then send the data as a batch. While the best way to do this is with 2 Arduino wired together, you can do it with one. You just need to arrange to send the batch periodically.

One key to do this is to use an insert with multiple rows like this:

Insert into ... values(1,2,3), (2,3,4), (4,3,2)

Just make sure you don’t exceed available memory.

Another trick to using this technique is to capture the time at the Arduino. While this violates the practice of using time stamps in the database, it allows you to record the time with the sensor sample if that is pertinent. However, unless you use a network time server, you’ll need a real-time clock.

Hope this helps and sparks some ideas of your own.

Dr. Bell

On Nov 19, 2018, at 3:34 PM, PcFreaker notifications@github.com wrote:

Hi there. I've used this awesome libary a couple of times now with success. This time I'm working on a project where i would like to insert values into a database multiple times per second. However, it seems that I can't execute more than roughly one INSERT INTO query per second. Is this a limitation of the hardware, a hidden delay or what could be the cause.

I'm using the complex_insert example, an Arduino Ethernet Shield and Arduino AT Mega 2560.

I'm hoping to hear from some of you. Thanks

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread.

[fellowbak]

Hi Dr. Bell

Thank you very much for your response. I’ll look into that.

Den 20. nov. 2018 kl. 04.08 skrev Dr. Charles Bell notifications@github.com:

Hi,

Yes, I am afraid the Arduino is notoriously slow. Achieving many inserts per second is beyond the capabilities of most Arduino boards. Mainly due to the slow networking hardware.

However, there are ways around it. One I like best is to buffer the sensor readings either to eeprom or SD card then send the data as a batch. While the best way to do this is with 2 Arduino wired together, you can do it with one. You just need to arrange to send the batch periodically.

One key to do this is to use an insert with multiple rows like this:

Insert into ... values(1,2,3), (2,3,4), (4,3,2)

Just make sure you don’t exceed available memory.

Another trick to using this technique is to capture the time at the Arduino. While this violates the practice of using time stamps in the database, it allows you to record the time with the sensor sample if that is pertinent. However, unless you use a network time server, you’ll need a real-time clock.

Hope this helps and sparks some ideas of your own.

Dr. Bell

On Nov 19, 2018, at 3:34 PM, PcFreaker notifications@github.com wrote:

Hi there. I've used this awesome libary a couple of times now with success. This time I'm working on a project where i would like to insert values into a database multiple times per second. However, it seems that I can't execute more than roughly one INSERT INTO query per second. Is this a limitation of the hardware, a hidden delay or what could be the cause.

I'm using the complex_insert example, an Arduino Ethernet Shield and Arduino AT Mega 2560.

I'm hoping to hear from some of you. Thanks

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or mute the thread. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/76#issuecomment-440121417, or mute the thread https://github.com/notifications/unsubscribe-auth/Ap5zWd2k7NreaVYynQVNuf10B01sWPf9ks5uw3ITgaJpZM4Yp2Ps.

[emhatek]

Hi Dr Bell, Yesterdew i try with nodemcu esp8266 arduino, its work and still looking for speed up the writing time to server. Esp8266 high capacity memory, clock 80mhz, and high speed wifi networking

[ChuckBell]

Hello,

I would have to see your sketch in order to see if there are any things you can do to improve your code. So, what follows is the general advice I offer to all who encounter this limitation.

First, please understand that the Arduino and it's ESP cousins are not the fastest processors in the world and that networking in general on microcontrollers in this price point is notoriously slower than more expensive boards (and PCs). Sadly, in my experience, WiFi is about 50% slower than Ethernet (in general) on microcontrollers. Lots of reasons why, but we get what we pay for like anything else.

That said, in my experience, the best you can do is take and store samples reliably every 3-5 seconds and that is only if everything is optimized in your sketch and your network is not overburdened. Some have achieved faster, but I would err on the side of caution for stability over time.

So, what can we do? There are several techniques, but the following are those I stress most. It all begins with your application.

1) Many of the hobby and enthusiast grade sensors are not very fast. Check the refresh and sampling rate of your sensors. For example, I have several sensors that can reliably take samples every 5 seconds, but doing so more frequently often leads to the same sample read multiple times or, worse, spurious values that appear when the sensor electronics are resetting. Make sure you're not over driving your sensors.

2) Consider your sampling rate and the data you are collecting. Many people make the mistake thinking more samples means better data. That is often incorrect. As an extreme, perhaps absurd example, consider temperature or barometric pressure. If you're measuring ambient environmental factors, taking samples at 10, 100, or even 1000 times a second will yield no additional knowledge. Indeed, it may be that a sampling rate many times slower would yield the same knowledge. The bottom line is, don't over sample! Set your sampling rate so that you can collect enough data points to capture knowledge but not so much that you end up with a lot of "useless" data.

3) If you must collect samples at a higher rate than your hardware can safely and accurately store, you should consider using either an INSERT statement that saves two or more samples at the same time, or buffer the data and store it periodically.

For example, the following inserts 3 rows of data using a single INSERT statement that isn't any (practically) slower.

INSERT INTO mydb.mytable VALUES (1,'a'), (13,'b'), (9,'c');

There are several ways to buffer data. The most common is to write the data to an SD card or memory device (EEPROM) and then periodically read the data and save it to the database.

Another method, which I use in these situations, is to use a second Arduino board slaved to the master whereby the slave does the period data store triggered by the master or by a set schedule. While this is an advanced solution (I talk about it in one of my books), it works quite well.

Beyond that, I would have to see your sketch to see if there is anything obvious that can be optimized.

For example, some people attempt to calculate the sample datetime, do string manipulation, augment or aggregate the data, interpret the data (change to a set unit), etc. -- that which should be done on the database side. That is, you should offload as much processing as possible to the database. I like to store the raw data and use timestamp and calculated fields (or views with such) thus freeing processing power on the Arduino.

Finally, it is possible to tweak the connector code to run a bit faster, but for most this is a fool's errand. More specifically, messing with the timing in the code may help but it may also make things fail more spectacularly. And, if you do get it to work faster, it may only work so for that specific board and solution. The bottom line is: don't mess with the connector code unless you know, precisely, what you're doing.

Hope this helps,

Dr. Bell

On 11/23/18 5:28 PM, emhatek wrote:

Hi Dr Bell, Yesterdew i try with nodemcu esp8266 arduino, its work and still looking for speed up the writing tine to server. Esp8266 high capacity memory, clock 80mhz, and high speed wifi netwirking

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/76#issuecomment-441325136, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4HGT2yOdhwYjfwcFGeGJnzqsTdqZks5uyHaFgaJpZM4Yp2Ps.

[Muller59]

All,

I had a similar issue on a NodeMCU V3 board, writes (inserts) to a MySQL database would take around 1 second for a single insert. Then I updated my version of the library from V1.1.1 to V1.2.0a, and inserts are now 90 ms.

try upgrading the library

Regards,

Rob

[ChuckBell]

And placing a delay between your queries. Also, connect/disconnect for each query seems to help too.

On 4/3/19 2:58 PM, Muller59 wrote:

All,

I had a similar issue on a NodeMCU V3 board, writes (inserts) to a MySQL database would take around 1 second for a single insert. Then I updated my version of the library from V1.1.1 to V1.2.0a, and inserts are now 90 ms.

try upgrading the library

Regards,

Rob

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/ChuckBell/MySQL_Connector_Arduino/issues/76#issuecomment-479616822, or mute the thread https://github.com/notifications/unsubscribe-auth/AH0j4DV5WvDacCbdGQp4cKOLRrBEA4N7ks5vdPnJgaJpZM4Yp2Ps.