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