How can I use YCSB to evaluate Oracle's performance? #128

Closed
KaimingWan opened this Issue May 9, 2013 · 7 comments

7 participants

@KaimingWan

I have set the classpath included with 'ojdbc14' and I also move it to the directory ~/ycsb-0.1.4/jdbc-binding/lib.

I have create a table called 'usertable' in oracle.When use the command
./ycsb load jdbc -P ../workloads/workloada -p threads=1 -p columnfamily=f1 -p recordcount=10000 -s > load.dat

issues appeared as below:

Loading workload...
Starting test.
0 sec: 0 operations;
Error in database operation: java.sql.SQLException: No suitable driver found for
com.yahoo.ycsb.DBException: java.sql.SQLException: No suitable driver found for
at com.yahoo.ycsb.db.JdbcDBClient.init(JdbcDBClient.java:203)
at com.yahoo.ycsb.DBWrapper.init(DBWrapper.java:63)
at com.yahoo.ycsb.ClientThread.run(Client.java:189)
Caused by: java.sql.SQLException: No suitable driver found for
at java.sql.DriverManager.getConnection(DriverManager.java:602)
at java.sql.DriverManager.getConnection(DriverManager.java:185)
at com.yahoo.ycsb.db.JdbcDBClient.init(JdbcDBClient.java:187)
... 2 more
0 sec: 0 operations;

my conf file under /jdbc-binding:
[hadoopcdh@mastersrv conf]$ cat db.properties
#Properties file that contains database connection information.

jdbc.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@192.168.1.65:1521:kakou
db.user=kakou
db.passwd=kakou
[hadoopcdh@mastersrv conf]$ cat h2.properties
#Properties file that contains database connection information.

jdbc.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@192.168.1.65:1521:kakou
db.user=kakou
db.passwd=kakou

@SlyRax

Windows or linux? Take a look in the ycsb files in the bin dir. You can see how they set the classpath, might be worth printing it out.

The jar is either not on the path or is the wrong version.

@pumaranikar

I am also having problem while running workload for JDBC. I have included mysql-connector-java-5.1.27-bin.jar in classpath and also in ycsb/jdbc-binding/lib.
Then I run a following command, it throws exception,
pushkar@pushkar-VirtualBox:~/ycsb-0.1.4$ ./bin/ycsb load jdbc -P workloads/workloada
java -cp /home/pushkar/ycsb-0.1.4/core/lib/core-0.1.4.jar:/home/pushkar/ycsb-0.1.4/core/lib/mysql-connector-java-5.1.27-bin.jar:/home/pushkar/ycsb-0.1.4/hbase-binding/conf:/home/pushkar/ycsb-0.1.4/workloads/mysql-connector-java-5.1.27-bin.jar:/home/pushkar/ycsb-0.1.4/nosqldb-binding/conf:/home/pushkar/ycsb-0.1.4/infinispan-binding/conf:/home/pushkar/ycsb-0.1.4/voldemort-binding/conf:/home/pushkar/ycsb-0.1.4/jdbc-binding/conf:/home/pushkar/ycsb-0.1.4/jdbc-binding/lib/jdbc-binding-0.1.4.jar:/home/pushkar/ycsb-0.1.4/jdbc-binding/lib/mysql-connector-java-5.1.27-bin.jar:/home/pushkar/ycsb-0.1.4/gemfire-binding/conf com.yahoo.ycsb.Client -db com.yahoo.ycsb.db.JdbcDBClient -P workloads/workloada -load
YCSB Client 0.1
Command line: -db com.yahoo.ycsb.db.JdbcDBClient -P workloads/workloada -load
Loading workload...
Starting test.
Adding shard node URL:
Error in database operation: java.sql.SQLException: No suitable driver found for
com.yahoo.ycsb.DBException: java.sql.SQLException: No suitable driver found for
at com.yahoo.ycsb.db.JdbcDBClient.init(JdbcDBClient.java:203)
at com.yahoo.ycsb.DBWrapper.init(DBWrapper.java:63)
at com.yahoo.ycsb.ClientThread.run(Client.java:189)
Caused by: java.sql.SQLException: No suitable driver found for
at java.sql.DriverManager.getConnection(DriverManager.java:596)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at com.yahoo.ycsb.db.JdbcDBClient.init(JdbcDBClient.java:187)
... 2 more
com.yahoo.ycsb.DBException: java.sql.SQLException: No suitable driver found for
at com.yahoo.ycsb.db.JdbcDBClient.init(JdbcDBClient.java:203)
at com.yahoo.ycsb.DBWrapper.init(DBWrapper.java:63)
at com.yahoo.ycsb.ClientThread.run(Client.java:189)
Caused by: java.sql.SQLException: No suitable driver found for
at java.sql.DriverManager.getConnection(DriverManager.java:596)
at java.sql.DriverManager.getConnection(DriverManager.java:215)
at com.yahoo.ycsb.db.JdbcDBClient.init(JdbcDBClient.java:187)
... 2 more
[OVERALL], RunTime(ms), 132.0
[OVERALL], Throughput(ops/sec), 0.0

My db. properties and h2.properties contains:
db.driver=com.mysql.jdbc.driver
db.url=jdbc:mysql://server-name.rds.amazonaws.com:3306/db-name
db.user=admin
db.passwd=password

I have not created usertable as i have added core property table=table_name in workloada

Can you please suggest how i can resolve this ?
Please reply at your earliest.

@wangchang

YCSB defaultly will not read the db.properties file ,so u need put these in your configure file,just like this:
[xxxx@xxxx ycsb-0.1.4]$ cat para.dat
recordcount=10000
operationcount=100000000
columnfamily=family
jdbc.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:thin:@xxxx:xxxxx
db.user=xxxx
db.passwd=xxx

then u can do:
bin/ycsb load jdbc -P workloads/workloada -P para.dat -s

or u just give a "-P" to use db.properties,like this:
bin/ycsb load jdbc -P workloads/workloada -P jdbc-bingding/conf/db.properties -s

@wangchang

and for different version of Oracle ,u need correspond jdbc,for 11g i use ojdbc6.jar

@srinivas35153

There’s a minor change required to the below YCSB Java program for the Oracle database.

https://github.com/brianfrankcooper/YCSB/blob/master/jdbc/src/main/java/com/yahoo/ycsb/db/JdbcDBClient.java

semicolon (;) for the INSERT/UPDATE statements in JDBCClient.java program cause below errors.
We need to change the line from insert.append(");"); to insert.append(")");

However, this will work fine with MySQL/PostgreSQL .. etc.
Hence, we need to change the code as below and take the latest compiled jar file to fix the problem.

insert.append(");"); ------ > insert.append(")");

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option

Table dropped.
Table created.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Automatic Storage Management option
Loading records --------
Loading workload...
Starting test.
0 sec: 0 operations;
Error in processing insert to table: usertablejava.sql.SQLSyntaxErrorException: ORA-00911: invalid character

[Lab root @ myvmbox /home/guest/ycsb-0.1.4] # cat ~/.bashrc

.bashrc

User specific aliases and functions

alias rm='rm -i'
alias cp='cp -i'
alias mv='mv -i'

Source global definitions

if [ -f /etc/bashrc ]; then
. /etc/bashrc
fi

export YCSBHOME=/home/guest/ycsb-0.1.4

export ORACLE_HOME=/optware/oracle/11.2.0.4/db_1
export ORACLE_SID=TESTAP

#export ORACLE_LD_LIBRARY_PATH=/optware/oracle/11.2.0.4/db_1/lib
#export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_LD_LIBRARY_PATH

export PATH=/optware/oracle/11.2.0.4/db_1/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:.
export EDITOR=vi

export CLASSPATH=/optware/oracle/11.2.0.4/db_1/jdbc/lib/ojdbc7.jar
export ORA_CLASSPATH=/optware/oracle/11.2.0.4/db_1/jlib/orai18n.jar
export CLASSPATH=$CLASSPATH:$ORA_CLASSPATH

export JAVA_HOME=/optware/oracle/11.2.0.4/db_1/jdk1.7.0_60
export PATH=$PATH:$JAVA_HOME/bin

@srinivas35153

But for Updates, I'm still receiving errors ... please suggest anywhere you still using ; except from JDBCClient.java

@busbey
Collaborator

restating the problems above:

  • Need to verify the README of the JDBC driver explains how to get appropriate jdbc drivers on the classpath
  • Need to update the JDBC driver to not rely on semi-colons since that breaks some oracle bindings.
@kruthar kruthar added a commit to kruthar/YCSB that referenced this issue Nov 4, 2015
@kruthar kruthar [jdbc] removed semicolons from JDBC operations
This was a necessary fix for a few JDBC flavors including the derby
database used for testing.

Fixes #128
e71a696
@risdenk risdenk added a commit to risdenk/YCSB that referenced this issue Nov 9, 2015
@kruthar kruthar [jdbc] removed semicolons from JDBC operations
This was a necessary fix for a few JDBC flavors including the derby
database used for testing.

Fixes #128
e24d85a
@cmccoy cmccoy closed this in #497 Nov 13, 2015
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment