Arduino MEGA + WIFI shield sending data frequency

Hi Guys, first of all its a great community. Second, I am trying to send data over wifi client to my web page that is connected to online database and inserting values in it.
My main purpose is to send 10,000 samples per second in real time which is alot, I know. Here is the result to my current connection at max baud rate to check the sending frequency of samples.

12:12:28.430 -> MAC: 
12:12:28.430 -> Scanning available networks...
12:12:28.430 -> ** Scan Networks **
12:12:30.429 -> number of available networks:10
12:12:30.429 -> 0) network1	Signal: -41 dBm	Encryption: WPA2
12:12:30.429 -> 1) network1	Signal: -41 dBm	Encryption: None
12:12:30.429 -> 2) network1	Signal: -64 dBm	Encryption: WPA2
12:12:30.429 -> 3) network1	Signal: -64 dBm	Encryption: None
12:12:30.429 -> 4) network1	Signal: -67 dBm	Encryption: WPA2
12:12:30.429 -> 5) network1	Signal: -67 dBm	Encryption: None
12:12:30.429 -> 6) network1	Signal: -71 dBm	Encryption: WPA2
12:12:30.429 -> 7) network1	Signal: -72 dBm	Encryption: None
12:12:30.429 -> 8) network1	Signal: -72 dBo open SSID: TalTech
12:12:30.429 ->  HUAWEI5	Signal: -72 dBm	Encryption: WPA2
12:12:30.429 -> ** Connecting to Network **
12:12:30.429 -> Attempting to connect to open SSID: network1	
12:12:45.422 -> You're connected to the networkSSID: network1	
12:12:45.422 -> BSSID: 
12:12:45.422 -> signal strength (RSSI):-41
12:12:45.422 -> Encryption Type:7
12:12:45.422 -> SSID: network1	
12:12:45.456 -> IP Address: 
12:12:45.456 -> signal strength (RSSI):-41 dBm
12:12:45.456 -> 
12:12:45.456 -> Starting connection to server...
12:12:49.668 -> 
12:12:49.668 -> Connected to Server...
12:12:49.668 -> Inserted
12:12:49.702 -> Inserted
12:12:49.702 -> Inserted
12:12:50.687 -> Inserted
12:12:50.722 -> Inserted
12:12:50.722 -> Inserted
12:12:50.722 -> Inserted
12:12:51.127 -> Inserted
12:12:51.127 -> Inserted
12:12:51.127 -> Inserted
12:12:51.364 -> Inserted
12:12:51.364 -> Inserted
12:12:51.364 -> Inserted
12:12:51.364 -> Inserted
12:13:06.414 -> Inserted
12:13:28.981 -> Inserted
12:13:51.542 -> Inserted
12:14:14.106 -> Inserted

Here is the code I am using for sending data using wifi client :

void setup() {
  //Initialize serial and wait for port to open:
  Serial.begin(2000000);
  while (!Serial) {
    ; // wait for serial port to connect. Needed for native USB port only
  }

  

  // check for the presence of the shield:
  if (WiFi.status() == WL_NO_SHIELD) {
    Serial.println("WiFi shield not present");
    // don't continue:
    while (true);
  }

  String fv = WiFi.firmwareVersion();
  if (fv != "1.1.0") {
    Serial.println("Please upgrade the firmware");
  }

  // Print WiFi MAC address:
  printMacAddress();
  Serial.println("Scanning available networks...");
  listNetworks();
  connectNetwork();

  printCurrentNet();
  printWifiStatus();


  Serial.println("\nStarting connection to server...");
  // if you get a connection, report back via serial:
 
   if (client.connect(server, 80)) {
    
    Serial.println("\nConnected to Server...");
    client.print("GET /test.php?t=");
    client.print(35);
    client.println(" HTTP/1.1");
    client.println("Host:myhost.com");
    client.println("Connection: Keep-Alive");
    client.println();
    Serial.println("Inserted");
   

    client.print("GET /test.php?t=");
    client.print(35);
    client.println(" HTTP/1.1");
    client.println("Host:myhost.com");
    client.println("Connection: Keep-Alive");
    client.println();
    Serial.println("Inserted");
    //client.println("Connection: close");
   }

}

void loop() {
  // scan for existing networks:
    
    
     /*while (client.available()) {
    char c = client.read();
    Serial.write(c);
  }*/

  
  // if the server's disconnected, stop the client:
  if (!client.connected()) {
    Serial.println();
    Serial.println("disconnecting from server.");
    client.stop();

    client.connect(server, 80);
    // do nothing forevermore:
    //while(true);
  }
  else
  {
    client.print("GET /test.php?t=");
    client.print(35);
    client.println(" HTTP/1.1");
    client.println("Host:myhost.com");
    client.println("Connection: Keep-Alive");
    client.println();
    Serial.println("Inserted");
   // client.println("Connection: Keep-Alive");
  
  }
    
   
  
}

As it can be seen that the connection is never disconnected but it takes too much time in between because of setting up the connection. Can anyone help me out with it ? like direct me towards a better approach and if it is possible to send that high of data samples per second to an online database using arduino or not.
It will be really great if we can have a nice discussion on it and I am still new to the field of arduino so please guide me, thank you.

okay, I changed GET to client.print("POST /test.php?t=");

Now, I am getting like 5 - 6 normal queries in one second at 9600 baud rate but after like around 12 queries the connection start taking a lot of time and even if the query runs the value is not inserted in database. It usually arrives after 12 queries, it is because of taking too much memory ? can i clear up space in memory on the run after every POST request is send ?

It insane to try to get a high throughput this way, like you recognized it takes too much time to establish a connection. You should think about buffering data say for a whole second and batch upload it, connecting to the server only once a second and sending a whole second of data in one shot.

liuzengqiang:
It insane to try to get a high throughput this way, like you recognized it takes too much time to establish a connection. You should think about buffering data say for a whole second and batch upload it, connecting to the server only once a second and sending a whole second of data in one shot.

Yes, thank you. Can you please guide me a little ?
The current setup I am thinking of is using a system in between as a local database setup and filling it up using serial connection from arduino card. Will it be able to keep the speed up on serial ?
I will be batch uploading it later online.

What database are you using, SQLite, Postgres, MySQL, SQL Server or something else?
Is it running on your local computer or somewhere on the web?

For really high speed stuff people use a datastore system such as Redis. It doesn’t support SQL queries, but there’s nothing stopping you from writing code that subsequently copies the data into a SQL database.

mikb55:
What database are you using, SQLite, Postgres, MySQL, SQL Server or something else?
Is it running on your local computer or somewhere on the web?

For really high speed stuff people use a datastore system such as Redis. It doesn't support SQL queries, but there's nothing stopping you from writing code that subsequently copies the data into a SQL database.

I am using MySQL and it is running on web somewhere.
other than redis it is possible to store data on a local system maybe of a low processing speed using serial port over arduino card at 10 kHz frequency ?

If you need local storage, just write the data to a file.

If you really need to do 10k transactions per second to a remote database in real-time then you can buy dedicated bandwidth and create your own private network like the banks and other organisations do.

I tried with a simplified version of GitHub - ocrdu/arduino-webinterface-websocket: Example web interface for the Arduino Nano 33 IoT using a websocket , and found that I could get about 250 separate short messages per second through a websocket on an Arduino Nano 33 IoT, so that is nowhere near fast enough for what you want without buffering measurements and sending several in one go. Maybe a raw socket would work.

The bits per second rate of the serial port(s) can be quite high; feasability would depend on he size of your measurements, update frequency, and overhead in the transmission.

Roughly and off the top of my head: 10KHz * 32bits * 1.05 = 336000bps, assuming 4-byte measurements and 5% overhead, and assuming you can read your sensor at 10KHz.

336000bps is probably doable on your Arduino, but I don't have one to experiment with. I do know the Arduino Nano 33 IoT can easily do much more than 336000bps, if you can get the data to the UART fast enough.

Processing them on the receiving computer at that speed, and sending them on to a database, is another matter, of course.

mikb55:
If you need local storage, just write the data to a file.

If you really need to do 10k transactions per second to a remote database in real-time then you can buy dedicated bandwidth and create your own private network like the banks and other organisations do.

Okay, so if i do make my own private network lets suppose with a system or raspberry pi board using arduino and sensor as upfront. Can arduino and board be able to send 10k transactions per second ?

I tried with arduino mega and it is like not able to send much per second. But on private network based on serial will it be able to send over amass of requests ?

Sorry still a newbie, I am testing with blank requests but want to discuss it out a little.

ocrdu:
I tried with a simplified version of GitHub - ocrdu/arduino-webinterface-websocket: Example web interface for the Arduino Nano 33 IoT using a websocket , and found that I could get about 250 separate short messages per second through a websocket on an Arduino Nano 33 IoT, so that is nowhere near fast enough for what you want without buffering measurements and sending several in one go. Maybe a raw socket would work.

The bits per second rate of the serial port(s) can be quite high; feasability would depend on he size of your measurements, update frequency, and overhead in the transmission.

Roughly and off the top of my head: 10KHz * 32bits * 1.05 = 336000bps, assuming 4-byte measurements and 5% overhead, and assuming you can read your sensor at 10KHz.

336000bps is probably doable on your Arduino, but I don't have one to experiment with. I do know the Arduino Nano 33 IoT can easily do much more than 336000bps, if you can get the data to the UART fast enough.

Processing them on the receiving computer at that speed, and sending them on to a database, is another matter, of course.

Thank you ocrdu for this awesome insight, the sensors are capable of reading at 10 KHz.

The main problem is processing them from arduino to a system. I can store them locally on the database if serial port can process them at high speed. I will test it out with an arduino board and hopefully might be able to save 10k transactions per second over on serial.

I tried with a bigger bps but it started missing out on packets over internet, but gonna run it for a serial port.

Is this a student project or a commercial project?

mikb55:
Is this a student project or a commercial project?

This is for my thesis currently but i have research fund allocated to buy things and stuff needed.

nothan:
This is for my thesis currently but i have research fund allocated to buy things and stuff needed.

Have a meeting with your supervisor together with your local network administrator. Explain the problem and ask for guidance.

mikb55:
Have a meeting with your supervisor together with your local network administrator. Explain the problem and ask for guidance.

lol I did, and he said find a solution for it. Research and discuss on forums and get a rough idea and than setup the system here in lab and test.

check your communication on TCP/IP Level with wireshark or similar.

do not generate so many client.prints, as every print will increase your TCP overhead.

    client.print("GET /test.php?t=");
    client.print(35);
    client.println(" HTTP/1.1");
    client.println("Host:myhost.com");
    client.println("Connection: Keep-Alive");
    client.println();
[code]

Fill a buffer and send the buffer with as less print/println as possible (ideally with one client.print)

noiasca:
check your communication on TCP/IP Level with wireshark or similar.

do not generate so many client.prints, as every print will increase your TCP overhead.

    client.print("GET /test.php?t=");

client.print(35);
    client.println(" HTTP/1.1");
    client.println("Host:myhost.com");
    client.println("Connection: Keep-Alive");
    client.println();

[code]

Fill a buffer and send the buffer with as less print/println as possible (ideally with one client.print)

Thanks, will try using that way too. Hopefully it will increase the rate of sending data to database.