[SOLVED] Why does the execution speed of the while loop change over time?

Issue

We have a Time issue in a C while loop. in one part of the program, the while loop receives information from a sensor via the SPI protocol and stores it in a Mysql database, The problem appears when we are checking the stored data.
For example, 600 data per second is stored in the database, but in some seconds the number of stored data is reduced to 400, and after a few seconds it returns to 600 per second, and so on.
I know that the data transfer rate from the sensor works on a fixed clock and cannot be changed.
But I do not know where this problem arises.

here is while loop code in c:

 while(1)
        {

            tx_buff[0] = 0x08 ;
            tx_buff[0] = (tx_buff[0] << 1) | 1;
            tx_buff[1] = 0xFF;
            tx_buff[2] = 0xFF;
            tx_buff[3] = 0xFF;
            tx_buff[4] = 0xFF;
            tx_buff[5] = 0xFF;
            tx_buff[6] = 0xFF;
            tx_buff[7] = 0xFF;
            tx_buff[8] = 0xFF;
            tx_buff[9] = 0xFF;
            tinkerboard_set_gpio_state(24, LOW);
            tinkerboard_spi_transfer(SPI2, tx_buff, rx_buff,10, mode);
            tinkerboard_set_gpio_state(24, HIGH);
            X_unsigned = (rx_buff[1]<<16) | (rx_buff[2]<<8)| (rx_buff[3]);
            X_unsigned = X_unsigned >> 4;
            X_signed = adc24to32(X_unsigned);
            Y_unsigned = (rx_buff[4]<<16) | (rx_buff[5]<<8)| (rx_buff[6]);
            Y_unsigned = Y_unsigned >> 4;
            Y_signed = adc24to32(Y_unsigned);
            Z_unsigned = (rx_buff[7]<<16) | (rx_buff[8]<<8)| (rx_buff[9]);
            Z_unsigned = Z_unsigned >> 4;
            Z_signed = adc24to32(Z_unsigned);

printf(" X=%5.4f Y=%5.4f Z=%5.4f \n",
X_signed*0.0039,Y_signed*0.0039,Z_signed*0.0039);
sprintf(query, "INSERT INTO PO(x,y,z) VALUES(%5.4f,%5.4f,%5.4f)",
X_signed*0.0039,Y_signed*0.0039,Z_signed*0.0039);

//conect to sql
     mysql_query(conn, query);
    
        }

Solution

Two things.

First, open a connection to your MySql server and reuse it. Don’t open a new connection for every insert: it’s too slow. And keep in mind that MySql connection objects are not thread-safe.

Second, MySql handles bulk insert operations most efficiently if you bundle many of them into a single database transaction. And your project does bulk insertions.

A suitable number of insert operations to bundle together is 100 or so. This helps the MySql server’s efficiency because most of the insertion work happens upon transaction commit. If you don’t wrap your inserts in START TRANSACTION; and COMMIT; MySql does autocommit. That means every insertion is also a transaction, and the overhead gets out of hand.

This is fairly easy to organize. When your connection is first opened, set a countdown variable to 100, and issue START TRANSACTION; to MySql.

As you do each insert, decrement your countdown variable. When it hits zero, reset it and do a COMMIT; and then another START TRANSACTION;.

This won’t turn your project into a hard real-time project, but it will help a lot with MySql overhead.

And, consider running MySql on a different host machine.

Answered By – O. Jones

Answer Checked By – Terry (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *