Forum: Other

Post: SQL problems when trying to execute TPC-B.

SQL problems when trying to execute TPC-B.
2170p
Jan 30, 2014
Hi everyone,

I'm trying to do TPC-B test(different from original one) on VoltDB by way of experiment via JDBCRunner(load testing tool).
I suppose the sql grammer is correct.
However, some error appears and it doesn't work correctly.


=============TPCB_LOAD.js=============
var jdbcDriver = "org.voltdb.jdbc.Driver";
var jdbcUrl = "jdbc:voltdb://**.**.**.**:21212";
var isLoad = true;
var nAgents = 4;
var isAutoCommit = false;
var logDir = "logs";

// Application settings ----------------------------------------------
var BATCH_SIZE = 100;
var TID_SCALE = 10;
var AID_SCALE = 100000;

// JdbcRunner functions ----------------------------------------------

function init() {
if (getId() == 0) {
var scale = param0;
var taskQueue = new java.util.concurrent.LinkedBlockingQueue();
info("Tiny TPC-B 1.1 - data loader");
info("-param0 : Scale factor (default : 16)");
info("-nAgents : Parallel loading degree (default : 4)");
if (scale == 0) {
scale = 16;
}
info("Scale factor : " + scale);
info("Parallel loading degree : " + nAgents);
for (var branchId = 1; branchId <= scale; branchId++) {
taskQueue.offer(branchId);
}
}
}


function run() {
var branchId = Number(getData("TaskQueue").poll());

if (branchId != 0) {
info("Loading branch id " + branchId + " by agent " + getId() + " ...");
loadBranches(branchId);
loadTellers(branchId);
loadAccounts(branchId);
commit();
} else {
setBreak();
}
}


function fin() {
if (getId() == 0) {
info("Completed.");
}
}





function loadBranches(branchId) {
execute("INSERT INTO branches (bid, bbalance, FILL) "
+ "VALUES ($int, 0, "
+ "'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb"
scale = 16;
}
info("Scale factor : " + scale);
info("Parallel loading degree : " + nAgents);
for (var branchId = 1; branchId <= scale; branchId++) {
taskQueue.offer(branchId);
}
}
}

function run() {
var branchId = Number(getData("TaskQueue").poll());
if (branchId != 0) {
info("Loading branch id " + branchId + " by agent " + getId() + " ...");
loadBranches(branchId);
loadTellers(branchId);
loadAccounts(branchId);
commit();
} else {
setBreak();
}
}

function fin() {
if (getId() == 0) {
info("Completed.");
}
}

=============end of TPCB_LOAD.js=============

===============TPCB.sql=================
CREATE TABLE ACCOUNTS (
AID INT PRIMARY KEY,
BID INT,
ABALANCE INT,
FILL VARCHAR(84)
);

CREATE TABLE BRANCHES (
BID int primary key,
BBALANCE int,
FILL varchar(88),
);

CREATE TABLE HISTORY (
BID int,
TID int,
AID int,
DELTA int,
TIME timestamp default now,
FILL varchar(22),
);

CREATE TABLE TELLERS (
TID int primary key,
BID int,
TBALANCE int,
FILL varchar(84)
);
==========end of TPCB.sql=================


===========error log======================
# java JR voltdb_tpcb_load.js
17:27:36 [INFO ] > JdbcRunner 1.2
17:27:36 [INFO ] [Config]
Program start time : 20140130-172736
Script filename : voltdb_tpcb_load.js
JDBC driver : org.voltdb.jdbc.Driver
JDBC URL : jdbc:voltdb://**.**.**.**:21212
JDBC user :
Load mode : true
Number of agents : 4
Auto commit : false
Debug mode : false
Trace mode : false
Log directory : logs
Parameter 0 : 0
Parameter 1 : 0
Parameter 2 : 0
Parameter 3 : 0
Parameter 4 : 0
Parameter 5 : 0
Parameter 6 : 0
Parameter 7 : 0
Parameter 8 : 0
Parameter 9 : 0
17:27:36 [ERROR] An exception occurred at agent 3
jdbcrunner.ApplicationException: An exception occurred while connecting to database
at jdbcrunner.Agent.callInit(Agent.java:227)
at jdbcrunner.Agent.run(Agent.java:86)
at java.lang.Thread.run(Thread.java:679)
Caused by: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (null)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
at jdbcrunner.Manager.getConnection(Manager.java:224)
at jdbcrunner.Agent.callInit(Agent.java:222)
... 2 more
Caused by: java.sql.SQLFeatureNotSupportedException
at org.voltdb.jdbc.SQLError.noSupport(SQLError.java:65)
at org.voltdb.jdbc.JDBC4Connection.setAutoCommit(JDBC4Connection.java:391)
at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371)
at org.apache.commons.dbcp.DelegatingConnection.setAutoCommit(DelegatingConnection.java:371)
at org.apache.commons.dbcp.PoolableConnectionFactory.activateObject(PoolableConnectionFactory.java:704)
at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1557)
at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
... 6 more
17:27:36 [INFO ] < JdbcRunner ERROR
===end of error log
jhugg
Jan 30, 2014
VoltDB doesn't support external transaction control, so our setAutoCommit(..) method will throw an exception if you try to turn off auto commit. Operations that need to be in the same transaction need to be specified in a stored procedure, either in Java code or Groovy code (4.0).

I haven't looked at TPC-B in depth, but transactional benchmarks like this often take a little bit of reworking to get running on VoltDB, then perhaps a bit more to work optimally. You can look at our slightly-unnofficial TPC-C code for ideas here: https://github.com/VoltDB/voltdb/tree/master/tests/test_apps/tpcc. If you take this work on, we'll keep answering questions for you here.

To back up a bit, could you give us some context of what you're trying to do with VoltDB and this benchmark? Do yo have a use case in mind, or are you comparing VoltDB to a specific alternative? Thanks.
2170p
Jan 31, 2014
Thank you.

When turned off auto commit, the error disappeared.
And loading records into each DB was successfully done.
Yet, when I executed the test scripts(TPCB.js), some error appears.

Could you help us again?

I'm just testing how easy the VoltDB's operation is and comparing to other in-mem dbs.
We actually try voltDB before making up our mind. Now we have a high opinion of VoltDB.

=======TPCB.js=========
var jdbcDriver = "org.voltdb.jdbc.Driver";
var jdbcUrl = "jdbc:voltdb://16.147.134.216:21212";

var warmupTime = 60;
var measurementTime = 180;
var nAgents = 8;
var isAutoCommit = true;
var logDir = "logs";

// Application settings ----------------------------------------------

var TID_SCALE = 10;
var AID_SCALE = 100000;
var FILLER = "aaaaaaaaaaaaaaaaaaaaaa";

var scale;

// JdbcRunner functions ----------------------------------------------

function init() {
if (getId() == 0) {
info("Tiny TPC-B 1.1");
info("Scale factor : " + Number(getData("ScaleFactor")));
info("Truncating history table...");
execute("TRUNCATE TABLE history");
}
}

function run() {
if (!scale) {
scale = Number(getData("ScaleFactor"));
}
var tid = random(1, TID_SCALE * scale);
var bid = Math.floor((tid - 1) / TID_SCALE) + 1;
var aid = 0;
if (scale == 1 || random(1, 100) <= 85) {
aid = random(AID_SCALE * (bid - 1) + 1, AID_SCALE * bid);
} else {
aid = random(1, AID_SCALE * (scale - 1));
if (aid > AID_SCALE * (bid - 1)) {
aid += AID_SCALE;
}
}
var delta = random(-999999, 999999);
execute("UPDATE accounts SET abalance = abalance + $int WHERE aid = $int",
delta, aid);
query("SELECT abalance FROM accounts WHERE aid = $int", aid);
execute("UPDATE tellers SET tbalance = tbalance + $int WHERE tid = $int",
delta, tid);
execute("UPDATE branches SET bbalance = bbalance + $int WHERE bid = $int",
delta, bid);
execute("INSERT INTO history (tid, bid, aid, delta, mtime, filler) "
+ "VALUES ($int, $int, $int, $int, $timestamp, $string)",
tid, bid, aid, delta, new Date(), FILLER);


=======error detail=========
# java JR voltdb_tpcb.js
15:09:14 [INFO ] > JdbcRunner 1.2
15:09:14 [INFO ] [Config]
Program start time : 20140131-150913
Script filename : voltdb_tpcb.js
JDBC driver : org.voltdb.jdbc.Driver
JDBC URL : jdbc:voltdb://**.**.**.**:21212
JDBC user :
Warmup time : 60 sec
Measurement time : 180 sec
Number of tx types : 1
Number of agents : 8
Connection pool size : 8
Statement cache size : 10
Auto commit : true
Sleep time : 0 msec
Throttle : - tps
Debug mode : false
Trace mode : false
Log directory : logs
Parameter 0 : 0
Parameter 1 : 0
Parameter 2 : 0
Parameter 3 : 0
Parameter 4 : 0
Parameter 5 : 0
Parameter 6 : 0
Parameter 7 : 0
Parameter 8 : 0
Parameter 9 : 0
15:09:14 [INFO ] Tiny TPC-B 1.1
15:09:14 [INFO ] Scale factor : 16
15:09:14 [INFO ] Truncating history table...
15:09:14 [ERROR] An exception occurred at agent 0
jdbcrunner.ApplicationException: An exception occurred while accessing the database
org.apache.commons.dbcp.SQLNestedException: Borrow prepareStatement from pool failed
at helper.js:53 (execute)
at voltdb_tpcb.js:40 (init)

at jdbcrunner.Script.call(Script.java:290)
at jdbcrunner.Script.callInit(Script.java:205)
at jdbcrunner.Helper.callInit(Helper.java:93)
at jdbcrunner.Agent.callInit(Agent.java:224)
at jdbcrunner.Agent.run(Agent.java:86)
at java.lang.Thread.run(Thread.java:679)
Caused by: org.apache.commons.dbcp.SQLNestedException: Borrow prepareStatement from pool failed
at org.apache.commons.dbcp.PoolingConnection.prepareStatement(PoolingConnection.java:113)
at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:281)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:313)
at jdbcrunner.Helper.execute(Helper.java:362)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:616)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:161)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:247)
at org.mozilla.javascript.optimizer.OptRuntime.call2(OptRuntime.java:76)
at org.mozilla.javascript.gen.helper_js_4._c_execute_13(helper.js:53)
at org.mozilla.javascript.gen.helper_js_4.call(helper.js)
at org.mozilla.javascript.optimizer.OptRuntime.callName(OptRuntime.java:97)
at org.mozilla.javascript.gen.voltdb_tpcb_js_11._c_init_1(voltdb_tpcb.js:40)
at org.mozilla.javascript.gen.voltdb_tpcb_js_11.call(voltdb_tpcb.js)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:426)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3178)
at org.mozilla.javascript.gen.voltdb_tpcb_js_11.call(voltdb_tpcb.js)
at jdbcrunner.Script.call(Script.java:279)
... 5 more
Caused by: java.sql.SQLException: Invalid SQL statement type.
at org.voltdb.jdbc.SQLError.get(SQLError.java:40)
at org.voltdb.jdbc.JDBC4Statement$VoltSQL.parseSQL(JDBC4Statement.java:244)
at org.voltdb.jdbc.JDBC4PreparedStatement.<init>(JDBC4PreparedStatement.java:51)
at org.voltdb.jdbc.JDBC4Connection.prepareStatement(JDBC4Connection.java:313)
at org.apache.commons.dbcp.PoolingConnection.makeObject(PoolingConnection.java:285)
at org.apache.commons.pool.impl.GenericKeyedObjectPool.borrowObject(GenericKeyedObjectPool.java:1212)
at org.apache.commons.dbcp.PoolingConnection.prepareStatement(PoolingConnection.java:107)
... 24 more
15:09:14 [INFO ] < JdbcRunner ERROR



Thanks in advance.
xin
Jan 31, 2014
Which version of VoltDB are you using? Can you verify for me which query fails to compile?

I suspect the query "Truncate table history" has problem. It is recently supported in VoltDB 4.0.2. The workaround is to use "Delete from history". This is a guess, but I may be wrong.
pmartel
Jan 31, 2014
To confirm Xin's suspicion, it does appear that the "truncate" command is not supported through our jdbc interface.
"DELETE FROM history" WILL do the same thing. Also, this command became much more performant as of our 4.0.2 release.
2170p
Feb 4, 2014
Hi&#12288;xin, pmartel,

The query "Truncate table history" was the cause as you say!
Instead of that, we used "Delete from history", and it works correct.
I appreciate it.


BTW, there is 1 problem at the very last.
"Caused by: java.sql.SQLException: Connection failure: 'Interrupted while waiting for response'" appears at the last moment.
could you help us?


# java JR voltdb_tpcb.js
20:15:31 [INFO ] > JdbcRunner 1.2
20:15:31 [INFO ] [Config]
Program start time : 20140204-201530
Script filename : voltdb_tpcb.js
JDBC driver : org.voltdb.jdbc.Driver
JDBC URL : jdbc:voltdb://**.***.***.***:21212
JDBC user :
Warmup time : 60 sec
Measurement time : 180 sec
Number of tx types : 1
Number of agents : 8
Connection pool size : 8
Statement cache size : 10
Auto commit : true
Sleep time : 0 msec
Throttle : - tps
Debug mode : false
Trace mode : false
Log directory : logs
Parameter 0 : 0
Parameter 1 : 0
Parameter 2 : 0
Parameter 3 : 0
Parameter 4 : 0
Parameter 5 : 0
Parameter 6 : 0
Parameter 7 : 0
Parameter 8 : 0
Parameter 9 : 0
20:15:31 [INFO ] Tiny TPC-B 1.1
20:15:31 [INFO ] Scale factor : 16
20:15:31 [INFO ] Truncating history table...
20:15:32 [INFO ] [Warmup] -59 sec, 213 tps, (213 tx)
20:15:33 [INFO ] [Warmup] -58 sec, 303 tps, (516 tx)
20:15:34 [INFO ] [Warmup] -57 sec, 344 tps, (860 tx)
20:15:35 [INFO ] [Warmup] -56 sec, 446 tps, (1306 tx)
20:15:36 [INFO ] [Warmup] -55 sec, 578 tps, (1884 tx)
...
20:16:29 [INFO ] [Warmup] -2 sec, 1174 tps, (54450 tx)
20:16:30 [INFO ] [Warmup] -1 sec, 1157 tps, (55607 tx)
20:16:31 [INFO ] [Warmup] 0 sec, 977 tps, (56584 tx)
20:16:32 [INFO ] [Progress] 1 sec, 1085 tps, 1085 tx
20:16:33 [INFO ] [Progress] 2 sec, 1108 tps, 2193 tx
20:16:34 [INFO ] [Progress] 3 sec, 1149 tps, 3342 tx
20:16:35 [INFO ] [Progress] 4 sec, 1072 tps, 4414 tx
20:16:36 [INFO ] [Progress] 5 sec, 1111 tps, 5525 tx
20:16:37 [INFO ] [Progress] 6 sec, 1080 tps, 6605 tx
20:16:38 [INFO ] [Progress] 7 sec, 1105 tps, 7710 tx
20:16:39 [INFO ] [Progress] 8 sec, 1076 tps, 8786 tx
20:16:40 [INFO ] [Progress] 9 sec, 912 tps, 9698 tx
20:16:41 [INFO ] [Progress] 10 sec, 1120 tps, 10818 tx
...
20:19:27 [INFO ] [Progress] 176 sec, 1221 tps, 191079 tx
20:19:28 [INFO ] [Progress] 177 sec, 1048 tps, 192127 tx
20:19:29 [INFO ] [Progress] 178 sec, 1140 tps, 193267 tx
20:19:30 [INFO ] [Progress] 179 sec, 1118 tps, 194385 tx
20:19:31 [INFO ] [Progress] 180 sec, 1105 tps, 195490 tx
20:19:31 [ERROR] An exception occurred at agent 3
jdbcrunner.ApplicationException: An exception occurred while accessing the database
java.sql.SQLException: Connection failure: 'Interrupted while waiting for response'
at helper.js:53 (execute)
at voltdb_tpcb.js:74 (run)

at jdbcrunner.Script.call(Script.java:290)
at jdbcrunner.Script.callRun(Script.java:218)
at jdbcrunner.Helper.callRun(Helper.java:107)
at jdbcrunner.Agent.callRun(Agent.java:246)
at jdbcrunner.Agent.run(Agent.java:108)
at java.lang.Thread.run(Thread.java:744)
Caused by: java.sql.SQLException: Connection failure: 'Interrupted while waiting for response'
at org.voltdb.jdbc.SQLError.get(SQLError.java:60)
at org.voltdb.jdbc.JDBC4Statement$VoltSQL.execute(JDBC4Statement.java:148)
at org.voltdb.jdbc.JDBC4Statement.executeUpdate(JDBC4Statement.java:502)
at org.voltdb.jdbc.JDBC4PreparedStatement.executeUpdate(JDBC4PreparedStatement.java:131)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
at jdbcrunner.Helper.execute(Helper.java:364)
at sun.reflect.GeneratedMethodAccessor6.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.mozilla.javascript.MemberBox.invoke(MemberBox.java:161)
at org.mozilla.javascript.NativeJavaMethod.call(NativeJavaMethod.java:247)
at org.mozilla.javascript.optimizer.OptRuntime.call2(OptRuntime.java:76)
at org.mozilla.javascript.gen.helper_js_4._c_execute_13(helper.js:53)
at org.mozilla.javascript.gen.helper_js_4.call(helper.js)
at org.mozilla.javascript.optimizer.OptRuntime.callName(OptRuntime.java:97)
at org.mozilla.javascript.gen.voltdb_tpcb_js_11._c_run_2(voltdb_tpcb.js:74)
at org.mozilla.javascript.gen.voltdb_tpcb_js_11.call(voltdb_tpcb.js)
at org.mozilla.javascript.ContextFactory.doTopCall(ContextFactory.java:426)
at org.mozilla.javascript.ScriptRuntime.doTopCall(ScriptRuntime.java:3178)
at org.mozilla.javascript.gen.voltdb_tpcb_js_11.call(voltdb_tpcb.js)
at jdbcrunner.Script.call(Script.java:279)
... 5 more
Caused by: java.io.InterruptedIOException: Interrupted while waiting for response
at org.voltdb.client.ClientImpl.callProcedure(ClientImpl.java:249)
at org.voltdb.client.ClientImpl.callProcedureWithTimeout(ClientImpl.java:206)
at org.voltdb.jdbc.JDBC4ClientConnection.execute(JDBC4ClientConnection.java:201)
at org.voltdb.jdbc.JDBC4Statement$VoltSQL.execute(JDBC4Statement.java:116)
... 26 more
xin
Feb 4, 2014
Hi,

It seems that the client is waiting responses from the server, but got timed out. We can not figure out the problem by the exception message. Can you tell me which version of VoltDB are you using?

If possible, can you share the ddl and client code and we can reproduce it in our lab?

Email me: xjia@voltdb.com
--
Xin