10 million records VOLTDB table

January 29, 2011  |  cloud, dba, linux

In my previous post I ironically  said that I would resolve the elections voting problems (that new electronic citizen’s card holders had) spending 10 euros with an IT infrastructure.

Tonight I had time to start creating it. I won’t cluster it because I want to make it slower!!! 😛

STEP 1 : Create the table ddl.

CREATE TABLE VOTERS (
CCID VARCHAR(10) NOT NULL,
VOTERID VARCHAR(19),
NAME VARCHAR(100),
CITY VARCHAR(50),
PRIMARY KEY (CCID)
);

STEP 2 : Loading the table with 10 million records

Insert database procedure

import org.voltdb.*;

@ProcInfo(
    partitionInfo = "VOTERS.CCID: 0",
    singlePartition = true
)

public class Insert extends VoltProcedure {

        public final SQLStmt sql = new SQLStmt(
                "INSERT INTO VOTERS VALUES (?, ?, ?, ?);"
        );

        public VoltTable[] run(   String ccid,
                                  String voterid,
                                  String name,
                                  String city)
        throws VoltAbortException {
                voltQueueSQL( sql, ccid, voterid, name, city );
                voltExecuteSQL();
                return null;
        }
}

Bulk Loader

import org.voltdb.*;
import org.voltdb.client.*;
import java.util.*;

public class Bulk {

public static void main(String[] args) throws Exception {
String city = "Lisboa";
String ccid;
String voterid;
String name;

org.voltdb.client.Client myApp;
myApp = ClientFactory.createClient();
myApp.createConnection("voltdbserver");

int x = 1;
while (x <= 9999999) {
ccid  = "C" + x;
voterid = "" + (10000000 - x);
if(1 < x && x < 3000000) {
city = "Lisboa";
}
if(3000000 < x && x < 5000000) {
city = "Guimaraes";
}
if(5000000 < x && x < 10000000) {
city = "Braganca";
}

if ( (x % 2) == 0) {
name = "Manuel Maria";
}
else {
name = "Joana Antonia";
}

myApp.callProcedure(new NullCallback(),
"Insert", ccid,  voterid, name, city );
x++;
}

}
}

STEP 3 : Running the bulk loader. This will insert 10 million records in voters table.

[root@ip-10-227-110-128 com]# time java Bulk
real	4m46.596s
user	0m40.404s
sys	0m18.845s

Well,  it took about 5 minutes. Inserted 34965 records per second. Is it fast enought for you? THIRTY FOUR THOUSAND per second!

STEP 4 : Creating the Exporter procedure to take a snapshot to disk.

import org.voltdb.*;
import org.voltdb.client.*;

public class Exporter {
static final String SAVEDIR = "/tmp/voltdb/backup";
static final String SAVEID = "TestSnapshot";
static final int BLOCKING = 1;

public static void main(String[] args) throws Exception {

org.voltdb.client.Client client;
client = ClientFactory.createClient();
client.createConnection("localhost");

VoltTable[] results = null;
try {
results = client.callProcedure
("@SnapshotSave", SAVEDIR, SAVEID,
BLOCKING).getResults();
}
catch (Exception ex)
{
ex.printStackTrace();
}

for (VoltTable t: results) {
System.out.println(t.toString());
}
}
}

STEP 5 : Run the Exporter procedure to SNAPSHOT the DATABASE (export all data)

[root@ip-10-227-123-67 com]# time java Exporter
real	0m11.797s
user	0m0.220s
sys	0m0.044s

[root@ip-10-227-123-67 com]# ls /tmp/voltdb/backup/ -lht
total 532M
-rw-r--r-- 1 root root 531M Jan 28 19:36 TestSnapshot-VOTERS-host_0.vpt
-rw-r--r-- 1 root root   25 Jan 28 19:36 TestSnapshot.digest

Exported 10 million records, 532MB of data in 11 seconds… Good enough?  May be this sneak preview will help some skeptical to believe…

Next step? Create the app for the people run their searches. Not tonight, going to watch a movie with the Half Of My Heart.


2 Comments


  1. Hi Jiao,

    Love the post. I’m not sure if you are aware of this, but single-sql-statment stored procedures can be created without all the normal boilerplate using a shortcut described in our manual here:

    https://community.voltdb.com/docs/UsingVoltDB/DesignProc#DesignCodeFreeProcs

    Keep up the great work.

    • hi John

      2 days ago i didnt knew it, but after exploring the documention i found it yesterday. Its much simpler and easy that way 🙂 but i already have done it the hard way 😉

      Thanks for you comment 🙂

      Regards
      Joao

Trackbacks

  1. Tweets that mention 10 million records VOLTDB table | Beyond Oracle -- Topsy.com

Leave a Reply