How can I optimize my code especially on its memory usage to ensure there will no be problem like memory leaks

Projects General Guidance
1

This code is fetching data from a database and displaying it to a large LED. My concern is that as this code run infinitely, maybe there will come a time that an issue will arise especially with its memory usage. For an overview, I am using ESP WROOM-02D and this is where this code is uploaded and the ESP is connected to a transceiver.

#include <MySQL_Connection.h>
#include <MySQL_Cursor.h>
#include <ESP8266WiFi.h>

//Network credentials
const char* ssid = "wifi";
const char* pass = "password";

//Database credentials
IPAddress server_addr (192,167,2,100); // MySQL server IP
char user[] = "name";
char password[] = "pass";
char database[] = "table";

// Default values
uint32_t data_1 = 0;
uint32_t data_2 = 0;

WiFiClient client;
MySQL_Connection conn((Client *)&client);
//Create an instance of the cursor passing in the connection
MySQL_Cursor cur = MySQL_Cursor(&conn);

//Declaration of byte format
uint8_t header = 0xFF; // byte 0 as header
uint8_t address1 = 0xFA; // 1st layer address
uint8_t address2 = 0xFB; // 2nd layer address
uint8_t byte2 = 0x90;
uint8_t byte6 = 0x10;

//Wi-Fi connection set up
void setup_wifi() {
  delay(500);
  //Connecting to a WiFi network
  Serial.println();
  Serial.print("Connecting to ");
  Serial.println(ssid);
  WiFi.begin(ssid, pass);
  while (WiFi.status() != WL_CONNECTED) {
    delay(500);
    Serial.print(".");
  }
  Serial.println("");
  Serial.println("WiFi connected");
  Serial.println("IP address: ");
  Serial.println(WiFi.localIP());
}

void setup() {
  Serial.begin(115200);
  Serial1.begin(9600, SERIAL_8N1);
  setup_wifi();
 
// Connect to MySQL server
  if (conn.connect(server_addr, 3306, user, password, database)) {
    Serial.println("Connected to MySQL server"); }
  else {
    Serial.println("Connection to MySQL server failed");
    return;
  }
}

void loop () {
// Execute query to get a single row
  char query[] = "SELECT SUM(data1), SUM(data2) FROM data ORDER BY time DESC LIMIT 1 ";
  
  MySQL_Cursor* cursor = new MySQL_Cursor(&conn);
  if (conn.connected())
  {
  cursor->execute(query);

  // Fetch the columns and print them
  column_names *cols = cursor->get_columns();
  for (int f = 0; f < cols->num_fields; f++) {
    Serial.print(cols->fields[f]->name);
    if (f < cols->num_fields-1) {
      Serial.print(',');
    }
  }

  Serial.println();
  // Fetch the first row of data
  row_values* row = NULL;
  int data1 = 0;
  int data2 = 0;

  do {
    row = cursor->get_next_row();
    if (row != NULL) {
    data1 = atoi(row->values[1]);
    data2 = atoi(row->values[2]);

    }
  } while (row != NULL);

  //Assign the converted values to data_1 - data_4
  data_1 = data1;
  data_2 = data2;

  Serial.println(data_1);
  Serial.println(data_2);

  // Clean up cursor memory to avoid memory leak
  delete cursor;
  } else {
    conn.close();
    Serial.println("Connection...");
    delay(200);

    if (conn.connect(server_addr, 3306, user, password, database)) {
      delay(500);
      Serial.println("Successful reconnect!");
    } else {
      Serial.println("Cannot reconnect!");
    }
  }
  
// Process the values of data_1, data_2
  for (int i = 1; i <= 2; i++) {
    uint32_t data_i;
    uint8_t address;

    if (i == 1) {
      data_i = data_1;
      address = address1;
    } else if (i == 2) {
      data_i = data_2;
      address = address2;
    }

    uint8_t byte5 = (data_i >> 16) & 0xFF;
    uint8_t byte4 = (data_i >> 8) & 0xFF;
    uint8_t byte3 = data_i & 0xFF;

    uint8_t checksum = (address + byte2 + byte3 + byte4 + byte5 + byte6);
    uint8_t data_packet[8] = {header, address, byte2, byte3, byte4, byte5, byte6, checksum};
    
  Serial1.write(data_packet, sizeof(data_packet));
  Serial1.flush();}
  }
2

You can use the F() macro for the serial port prints, eg

Serial.println(F("WiFi connected"));

to save the strings in flash instead of RAM.

1 Like
3

Code should be able to normally run indefinitely. If you keep saving data you will run out of memory. If you update values the memory usage stays the same. It is my understanding that using strings is a problem with the Arduino(s), avoid whenever you can.

4

Can you avoid allocating it on the heap? Even though you are reallocating the same memory it might still become an issue depending on how connection object is implemented.. you could have a global connection object rather than recreating it in the loop

5

thanks for this reply but can you expound on this one? It still seems vague to me especially the "have a global connection object rather than recreating it in the loop"

6

move the delete, it currently only cleans up if connected, but creates a new one each loop..

 
  MySQL_Cursor* cursor = new MySQL_Cursor(&conn);
  if (conn.connected())
  {
  cursor->execute(query);

  // Fetch the columns and print them
  column_names *cols = cursor->get_columns();
  for (int f = 0; f < cols->num_fields; f++) {
    Serial.print(cols->fields[f]->name);
    if (f < cols->num_fields-1) {
      Serial.print(',');
    }
  }

  Serial.println();
  // Fetch the first row of data
  row_values* row = NULL;
  int data1 = 0;
  int data2 = 0;

  do {
    row = cursor->get_next_row();
    if (row != NULL) {
    data1 = atoi(row->values[1]);
    data2 = atoi(row->values[2]);

    }
  } while (row != NULL);

  //Assign the converted values to data_1 - data_4
  data_1 = data1;
  data_2 = data2;

  Serial.println(data_1);
  Serial.println(data_2);

  } else {
    conn.close();
    Serial.println("Connection...");
    delay(200);

    if (conn.connect(server_addr, 3306, user, password, database)) {
      delay(500);
      Serial.println("Successful reconnect!");
    } else {
      Serial.println("Cannot reconnect!");
    }
  }
  // Clean up cursor memory to avoid memory leak
  delete cursor;

good luck.. ~q

7

You are allocating dynamic memory using new every loop iteration. If you need to know what a heap is please look it up. How do you expect to write an optimized robust code without knowing much about different memory types and object scope? A global object is an object that lives for the duration of your sketch.. you have a few already

8

This topic was automatically closed 180 days after the last reply. New replies are no longer allowed.