Generate an unique user id, performance issue
Dec 20, 2010
I'd like to generate an unique userid.
Follow the FAQ (http://www.voltdb.com/community/community-home.php), I crate a table
===========================
CREATE TABLE PKTABLE(
TABLENAME VARCHAR(50) UNIQUE NOT NULL, /*PARTITION*/
CURRENTPK BIGINT NOT NULL,
);
===========================
set "TABLENAME" as the partition column, write a query to read the "CURRENTPK" and another one to increase it by 1, and put them into an single stored procedure called "GetUniqueUserId".
The problem I have is that I tested this procedure and found the voltdb instance on my 4cpu 8G box can only handle about 200 transactions per second. That's way below what I expected. I use the default setup generated by the python generate tools, and below I describe the details of my code. Did I do any wrong?
-------------------
The table that stores the current primary key are showed above, it has only one row: "USERTABLE, 99", where 99 is the current primary key.
Here is my stored procedure code
====================================================
import org.voltdb.*;
@ProcInfo(
partitionInfo = "PKTABLE.TABLENAME:0",
singlePartition = true
)
public class GetUniqueUserId extends VoltProcedure {
public final SQLStmt selectItem =
new SQLStmt("SELECT CURRENTPK " +
"FROM PKTABLE WHERE TABLENAME =?;");
public final SQLStmt updateItem=
new SQLStmt("UPDATE PKTABLE " +
"SET CURRENTPK = CURRENTPK+1 "+
"WHERE TABLENAME = ?;");
public long run(String tableName) throws VoltAbortException {
if(!tableName.equals("USERTABLE"))
throw new RuntimeException("Must be "USERTABLE"");
voltQueueSQL(selectItem, tableName);
VoltTable[] retval = voltExecuteSQL();
long currentPk = retval[0].asScalarLong();
voltQueueSQL(updateItem, tableName);
retval = voltExecuteSQL(true);
return currentPk;
}
}
=======================================================
and there is my test
================================================
import org.voltdb.client.*;
import java.io.IOException;
public class T1{
public static void main(String[] arg) throws Exception {
ClientConfig clientConfig = new ClientConfig();
org.voltdb.client.Client voltclient =
org.voltdb.client.ClientFactory.createClient(clientConfig);
try {
voltclient.createConnection("server ip");
}
catch (IOException e) {
e.printStackTrace();
System.exit(-1);
}
long start=System.currentTimeMillis();
long a=0;
for(int i=0;i<1000;i++){
a=voltclient.callProcedure("GetUniqueUserId", "USERTABLE").getResults()[0].asScalarLong();
}
long end=System.currentTimeMillis();
System.out.println("a="+ a +" takes: "+ (end-start));
voltclient.close();
}
}
====================================
Follow the FAQ (http://www.voltdb.com/community/community-home.php), I crate a table
===========================
CREATE TABLE PKTABLE(
TABLENAME VARCHAR(50) UNIQUE NOT NULL, /*PARTITION*/
CURRENTPK BIGINT NOT NULL,
);
===========================
set "TABLENAME" as the partition column, write a query to read the "CURRENTPK" and another one to increase it by 1, and put them into an single stored procedure called "GetUniqueUserId".
The problem I have is that I tested this procedure and found the voltdb instance on my 4cpu 8G box can only handle about 200 transactions per second. That's way below what I expected. I use the default setup generated by the python generate tools, and below I describe the details of my code. Did I do any wrong?
-------------------
The table that stores the current primary key are showed above, it has only one row: "USERTABLE, 99", where 99 is the current primary key.
Here is my stored procedure code
====================================================
import org.voltdb.*;
@ProcInfo(
partitionInfo = "PKTABLE.TABLENAME:0",
singlePartition = true
)
public class GetUniqueUserId extends VoltProcedure {
public final SQLStmt selectItem =
new SQLStmt("SELECT CURRENTPK " +
"FROM PKTABLE WHERE TABLENAME =?;");
public final SQLStmt updateItem=
new SQLStmt("UPDATE PKTABLE " +
"SET CURRENTPK = CURRENTPK+1 "+
"WHERE TABLENAME = ?;");
public long run(String tableName) throws VoltAbortException {
if(!tableName.equals("USERTABLE"))
throw new RuntimeException("Must be "USERTABLE"");
voltQueueSQL(selectItem, tableName);
VoltTable[] retval = voltExecuteSQL();
long currentPk = retval[0].asScalarLong();
voltQueueSQL(updateItem, tableName);
retval = voltExecuteSQL(true);
return currentPk;
}
}
=======================================================
and there is my test
================================================
import org.voltdb.client.*;
import java.io.IOException;
public class T1{
public static void main(String[] arg) throws Exception {
ClientConfig clientConfig = new ClientConfig();
org.voltdb.client.Client voltclient =
org.voltdb.client.ClientFactory.createClient(clientConfig);
try {
voltclient.createConnection("server ip");
}
catch (IOException e) {
e.printStackTrace();
System.exit(-1);
}
long start=System.currentTimeMillis();
long a=0;
for(int i=0;i<1000;i++){
a=voltclient.callProcedure("GetUniqueUserId", "USERTABLE").getResults()[0].asScalarLong();
}
long end=System.currentTimeMillis();
System.out.println("a="+ a +" takes: "+ (end-start));
voltclient.close();
}
}
====================================