Forum: Building VoltDB Applications

Post: Generate an unique user id, performance issue

Generate an unique user id, performance issue
paul
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();
}
}
====================================
re: Generate unique user id, performance issue
tcallaghan
Dec 20, 2010
Paul,
The performance you are seeing is not a limitation of the server, it is because you are generating the requests synchronously from a single client application. The server you describe is capable of significantly more than 200 transactions per second. Modify your client to be asynchronous or run several synchronous clients and you will see what I mean (please post your findings as well).
Also, your stored procedure could be a little more efficient by only calling VoltExecuteSQL() once, as in:
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);
voltQueueSQL(updateItem, tableName);
VoltTable[] retval = voltExecuteSQL(true);
long currentPk = retval[0].asScalarLong();
return currentPk;
}
}
-Tim
Tim- Thanks for the answer.
paul
Dec 20, 2010
Tim- Thanks for the answer. It works. I tested 1000 threads, each one with 1000 synchronous calls, and it took about 27 seconds to complete, so it is about 37,000 transactions per second. That's wonderful!!