I've completed a new release of the Connector/Arduino! The new release contains some major improvements with memory handling.
- The library has been trimmed to save memory.
- Static strings moved to PROGMEM strings
- Unused structures removed (e.g. ok_packet)
- Moved two more methods to optional compilation
- The WITH_SELECT is turned *OFF* by default. If you want to use select queries, be sure to uncomment this in the mysql.h file.
- Added a CHANGES.txt file to track changes between releases.
Memory, What Memory?
If you have used previous versions of the connector in medium to large sketches or have long query strings or even many variables, chances are you have hit the memory limit for your wee Arduino board.
This can manifest itself in a number of ways. Most notably, the sketch may work for you until you add more code or more sensors in which it can fail to connect to the server. It can also exhibit random reboots or random freezes. If this is happening to you, it is most likely a memory issue.
That is, the old version of the connector consumed nearly 70% of available dynamic memory - the memory used for variables. When the Arduino exceeds its memory limit, sketches will exhibit strange behavior or the board may reboot.
Many people have encountered this so I've worked hard to try and squeeze more memory out of the connector. Which isn't easy considering it must keep a buffer of the data being sent to (or received from) the server.
Let's see an example. The old version of the connector (1.0.1b), when compiled with the hello_mysql example for a Uno, consumes about 68% of available dynamic memory leaving only 637 bytes for your own variables. That's fine for a simple sketch but if you want to do complex queries building INSERT statements from several sensors or including other libraries for additional features (like an LCD), you're not going to be happy.
While you can (and should) limit your memory use and even make use of PROGMEM for your static strings (and calling cmd_query_P()), it still isn't enough free memory for larger sketches. The
following is the compile message generated by the beta release of the Arduino IDE (1.5.4).
Sketch uses 22,376 bytes (69%) of program storage space. Maximum is 32,256 bytes.
Global variables use 1,411 bytes (68%) of dynamic memory, leaving 637 bytes for local variables. Maximum is 2,048 bytes.
Now, with the new version of the library and SELECT turned on, the connector consumes only 58% of dynamic memory as shown below. While that is better, it isn't quite where we need to be.
Sketch uses 22,152 bytes (68%) of program storage space. Maximum is 32,256 bytes.
Global variables use 1,197 bytes (58%) of dynamic memory, leaving 851 bytes for local variables. Maximum is 2,048 bytes.
If we turn off the SELECT feature with the new version, we get a little better.
Sketch uses 20,736 bytes (64%) of program storage space. Maximum is 32,256 bytes.
Global variables use 1,064 bytes (51%) of dynamic memory, leaving 984 bytes for local variables. Maximum is 2,048 bytes.
Ah, now we're cooking. The dynamic memory usage is down to 51%. Much better.
Choosing the Right Board
One of the things users new to the connector have struggled with is choosing the right Arduino board for their project. The connector is a non-trivial library that consumes (relatively) a lot of memory. If you want to write a really big sketch using lots of variables, strings, etc., you will need to use a board with more memory.
This is especially true when you combine the connector with other libraries like those made for some sensors, shields, and more. The combined memory for the connector and the other libraries can consume a lot of dynamic memory leaving you very little to use for your own variables.
While most solve the problem by switching to a Mega board, that has its own issues because some of the pins differ from the smaller (memory-wise, not size-wise) boards. A few Google searches will quickly find solutions to these problems (hint: software serial).
So which board should you choose? I've done some research for you and have compiled a simple MySQL sketch using the new version of the connector on a variety of boards. The following lists the memory usage reports from the Arduino Beta IDE. In this case, the compilations are with SELECT turned off (which is the new default).
- Duemilanove, Uno : 1,064 bytes (51%) of dynamic memory, leaving 984 bytes for local variables
- Leonardo : 1,028 bytes (40%) of dynamic memory, leaving 1,532 bytes for local variables
- Mega 2560 : 1,550 bytes (18%) of dynamic memory, leaving 6,642 bytes for local variables
- Yun : 1,028 bytes (40%) of dynamic memory, leaving 1,532 bytes for local variables
As you can see, using the older Uno-style boards are going to limit your ability to write complex sketches with many variables, logic, etc. However, a Leonardo or even a Yun board may be a better choice especially if you run out of memory on the Uno-style board. Of course, the Mega is the best choice if your sketch is going to be very complex or if you need to store a lot of values in memory or use other, large libraries.
Conclusion
I hope this new version solves many of your problems with memory. I've tried to shrink it down as much as I can without radical changes. I believe the majority of the connection failures and freezes will be solved with this new version. Please feel free to provide feedback on this blog or (better) on the Oracle Connector/Arduino forum.
http://forums.mysql.com/list.php?175
