Processing, MySQL and RAM tables…

I’m currently working on a simulation model in Processing, which creates in every cycle, 350,000 lines of temporary data in chunks of 1,000 lines.

I decided to save the temporary data in a MySQL table, as this allows me to easily add an extra query at the end that summarises everything and saves the final results on a different table.

As the 350,000 lines need only to be stored temporarily, I’m using the Memory storage engine, instead of the default (MyISAM). The advantage of the Memory storage engine for temporary storage is that it saves the data on the RAM rather than the hard drive, is (or should be) faster and when you reboot the machine, the temp data is gone. The structure of the table is stored on the hard drive, so you can use the table again and again.

Here is a test sketch I wrote to get an estimate of how long it takes to create 350,000 records.

If you want to try this, you need to download and install the SQLibrary (http://bezier.de/processing/libs/sql/). You also need to install a mySQL database locally. If you don’t have one installed already, I suggest you go to apachefriends.org and download the latest version of XAMPP.

First, to create a simple table in RAM, run the following mysql snippet:

1
2
3
4
5
6

CREATE TABLE IF NOT EXISTS `arkadian_test` (
  `myID` int(11) NOT NULL AUTO_INCREMENT,
  `myRecord` int(15) NOT NULL,
  `myTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`myID`)
) ENGINE=MEMORY  DEFAULT CHARSET=latin1;

This is a simple table, that is stored in ram (ENGINE=MEMORY).

The following sketch creates 350,000 records in chunks of 1,000 records.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50

 
/* //////////////////////////////////////////////////////////////////////
Date: 1 Aug 2009
From: Arkadian.eu
Desc: A quick sketch that creates 350K records in MySQL
*///////////////////////////////////////////////////////////////////////
 
import de.bezier.data.sql.*;
MySQL mx;
 
public int maxRecords = 350000;
public int currentRecord = 0;
 
// adjust these for your configuration
public String user     = "root";
public String pass     = ""; //blank
public String database = "test";
public String dbhost = "localhost";
/////////////////////////////////////////////////////////////////////////
void setup() {
  size(100, 100);
  mx = new MySQL( this, dbhost, database, user, pass );
  while(mx.connect()==false){delay(1000);}
  mx.execute("TRUNCATE TABLE  `arkadian_test`"); 
}
/////////////////////////////////////////////////////////////////////////
void draw() {
 if(currentRecord<maxRecords){
    // let's check to see if the connection is still up
    while(mx.connect()==false){
      mx = new MySQL( this, "localhost", database, user, pass );
    }
 
    if ( mx.connect()==true ){  
 
      for(int i=0; i<1000; i++){
        currentRecord++;
        mx.execute("INSERT INTO arkadian_test(myRecord) VALUES ('" + currentRecord+ "')"); 
      }
 
    }
 
  }
 
  else{
    mx.close();
    exit();
  }
}
/////////////////////////////////////////////////////////////////////////

The above sketch takes about 45-50 seconds to run on my 2GHz Intel Core2 Duo laptop with 3GB RAM.

To see how long it takes on your machine, just look at the timestamps of the first and last record on your database.

For more information on MySQL tables on RAM, visit this site.