Infrastructure at your Service

Franck Pachot

YCSB (NoSQL benchmark) on Oracle Database

By Franck Pachot

.
The NoSQL technologies emerged for Big Data workloads where eventual consistency is acceptable and scaling out to multiple server nodes is an easy answer to increase throughput. With cloud services rising, those key-value document datastores started to be used by the web-scale companies for some transactional processing workloads as well. The solutions can provide high performance and easy partitioning capabilities thanks to the very simple API of NoSQL. Each table or collection, has only one key, the primary key, which is used for hash partitioning, sharding, and indexing. The API is a simple get/put by primary key, or scan on a range. There’s no referential integrity and no join, so that each query access only one shard, which makes each call it fast and predictable. There’s limited consistency provided so that each call do not have to wait for cross-node latency. There’s no row set operations link in SQL: you scale by adding more threads rather than having more work done by each thread.

Even if the API is very simple, there no standard API, and each technology provides a different interface for the application. In order to compare the performance, a benchmark application has been developed by the research division of Yahoo in 2010 (all explained in this paper: https://www2.cs.duke.edu/courses/fall13/cps296.4/838-CloudPapers/ycsb.pdf).This open source YCSB (Yahoo Cloud Serving Benchmark), in addition to providing a core workload generator, includes extensible clients for any NoSQL database. Look at the current clients:


[[email protected] YCSB]$ grep -r "ycsb.DB" . | awk -F/ '{print $2}' | sort -u | paste -sd,
accumulo1.9,aerospike,arangodb,asynchbase,azurecosmos,azuretablestorage,cassandra,cloudspanner,couchbase,couchbase2,crail,elasticsearch,elasticsearch5,geode,googlebigtable,googledatastore,griddb,hbase1,hbase2,hypertable,ignite,infinispan,jdbc,kudu,maprjsondb,memcached,mongodb,nosqldb,orientdb,rados,redis,rest,s3,solr,solr6,solr7,voldemort,voltdb

In the list of clients, you can see that it goes beyond the usual NoSQL datastores. There’s a JDBC client storing the attibutes in a relational table. I have added the support for the FETCH FIRST n ROWS ONLY so that you can run it on Oracle Database and any SQL:2008 compatible RDBMS.

Here is how to download the latest relase of YCSB:


cd /var/tmp
release="$(curl https://github.com/brianfrankcooper/YCSB | awk '/[/]download[/]/{print $NF}' )"
curl --location "$release" | tar -zxvf -

However, my Pull Request to support the FETCH FIRST n ROWS ONLY has been merged but is not yet included in the release, so better compile from source if you want to use YCSB on Oracle Database.


sudo yum-config-manager --add-repo http://repos.fedorapeople.org/repos/dchen/apache-maven/epel-apache-maven.repo
sudo yum-config-manager --enable epel-apache-maven
sudo yum install -y git apache-maven

This installs Git and Maven if you don’t have it already (I’m running an Autonomous Linux on the Oracle Free Tier as it gives me an always free environment with an Oracle Database). Of course you can run it anywhere but remember that, by the nature of the NoSQL API, the network latency between the application and the database is quickly a bottleneck.

I compile YCSB:


(
git clone https://github.com/brianfrankcooper/YCSB.git
cd YCSB
mvn clean package
mvn -pl site.ycsb:jdbc-binding -am package -DskipTests dependency:build-classpath -DincludeScope=compile -Dmdep.outputFilterFile=true
) > mvn.log

I keep the log in “mvn.log” and I’ll use it to get the classpath

YCSB will connect with JDBC but I’m using the OCI client here (OCI is the Oracle Call Interface). I’ll also use sqlplus to create the tables. Here is how to download them:


cd /var/tmp
wget https://download.oracle.com/otn_software/linux/instantclient/oracle-instantclient-basic-linuxx64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/oracle-instantclient-sqlplus-linuxx64.rpm
sudo yum localinstall -y oracle-instantclient-basic-linuxx64.rpm oracle-instantclient-sqlplus-linuxx64.rpm

In this example, I’ll connect to my free Oracle Autonomous Database for which I’ve downloaded the wallet into my TNS_ADMIN directory.

Here is my quick and dirty way to set the ORACLE_HOME from an installed Instant Client with sqlplus:
Updated (see comments) I’get important directories for future use:


MY_ORACLE_HOME=$(sqlplus /nolog <<<'get ?"' | awk -F'"' '/SP2-0160/{print $(NF-2)}')
MY_TNS_ADMIN=$MY_ORACLE_HOME/network/admin
MY_LD_LIBRARY_PATH=$MY_ORACLE_HOME
MY_CLASSPATH="$MY_ORACLE_HOME/ojdbc8.jar"

You may choose to use the JDBC thin driver, and even run this from the Cloud Shell. For the OCI thick JDBC I need to define the CLASSPATH for the .jar and LD_LIBRARY_PATH for the .so

If you are too lazy to create a database, I let you use the one I’ve shared for the SQL101 presentation. Here is my wallet.


wget https://objectstorage.us-ashburn-1.oraclecloud.com/n/idhoprxq7wun/b/pub/o/Wallet_sql101.zip
sudo unzip -od $MY_TNS_ADMIN Wallet_sql101.zip

Please be smart if you use my database, you are not alone there. It is easy to open an Oracle Cloud trial that will leave you with a free Oracle Database for life.

Here is my configuration file providing the connection settings. If you used my SQL101 wallet this will connect to my database with the DEMO user. I’ll remove it only if someone does nasty things. It is limited to very small storage quota so this is only to check how it works – not real scale benchmark.


cat > /tmp/ycsb.database.properties <<CAT
db.driver=oracle.jdbc.driver.OracleDriver
db.url=jdbc:oracle:oci:@sql101_tp
db.user=SQL101DEMO
db.passwd=**P455w0rd**
jdbc.batchupdateapi=true
db.batchsize=1000
CAT

I’m using the OCI driver here, as I have the Instant Client installed. Of course you can use the thin driver, and even add it as a maven dependency. If you do this, please document it in comments.

YCSB workload is defined by a few properties:


cat > /tmp/ycsb.workload.properties <<'CAT'
threadcount=20
fieldcount=1
fieldlength=42
recordcount=100000
operationcount=10000
workload=site.ycsb.workloads.CoreWorkload
readallfields=true
readproportion=0.3
updateproportion=0.2
scanproportion=0.1
insertproportion=0.4
requestdistribution=zipfian
minscanlength=1
maxscanlength=1000
CAT

All this is documented in https://github.com/brianfrankcooper/YCSB/wiki/Core-Properties

Here is my sqlplus script to create the table used by YCSB


cat > /tmp/ycsb.create.sql <<'SQL'
set pagesize 1000 long 1000000 echo on
whenever sqlerror exit failure
exec for i in (select * from user_tables where table_name='USERTABLE') loop execute immediate 'drop table USERTABLE'; end loop;
purge recyclebin;
create table USERTABLE (
  YCSB_KEY varchar2(25) primary key,
  FIELD0 &&1
)
&&2
/
select dbms_metadata.get_ddl('TABLE','USERTABLE') from dual;
exit
SQL

It takes the datatype as first parameter, and table attributes as second parameters. I use this to compare various physical data model alternatives. For a scalable key-value document store you will probably partition by hash on the key, with the primary key index being local, maybe even all stored as an Index Organized Table. Any RDBMS has specific features to scale a key-value workload. NoSQL databases usually restrict their implementation to those physical representations, but they didn’t invent new algorithms for access by primary key as it was there in RDBMS for a long time. But, as some RDBMS like Oracle Database are also optimized to handle complex queries, the default table attributes may not be the best suited for NoSQL-like access.

When running on my SQL101 you are limited in space, so let’s keep it simple:


sqlplus SQL101DEMO/"**P455w0rd**"@sql101_tp @ /tmp/ycsb.create.sql "varchar2(42)" "organization index nologging --partition by hash(YCSB_KEY) partitions 8"

Now I have a USERTABLE in the DEMO schema.

I put all environment variables in setenv.sh which is called by ycsb.sh


cat > /var/tmp/YCSB/bin/setenv.sh <<CAT
TNS_ADMIN=$MY_TNS_ADMIN
CLASSPATH=$MY_CLASSPATH:$(awk -F= '/^classpath=/{cp=$2}END{print cp}' /var/tmp/mvn.log)
export LD_LIBRARY_PATH=$MY_LD_LIBRARY_PATH
CAT

The LD_LIBRARY_PATH is required to find libocijdbc19 as I’m using the OCI driver. You can also put it in etc/ld.so.conf.d/oic.conf if you prefer. The CLASSPATH includes the one from maven output, plus the ojdbc8.jar

I am now ready to load some data:


/var/tmp/YCSB/bin/ycsb.sh load jdbc -P /tmp/ycsb.workload.properties -P /tmp/ycsb.database.properties

This reads /tmp/ycsb.workload.properties for the number of records to create.

Here is how to run the workload defined in /tmp/ycsb.workload.properties


/var/tmp/YCSB/bin/ycsb.sh run jdbc -P /tmp/ycsb.workload.properties -P /tmp/ycsb.database.properties | tee run.log

I’m showing below the Performance Hub when I’ve run it on my SQL101 database with 20 client threads.

I’m running a very low workload here as it is a small test database. But the important message is that you cannot run a benchmark without looking at the database metrics to be sure that the workload, and the bottleneck, is what you expect to push to the limits.

Here is how to quickly parse the log to see the result:

awk -F, '/^\[(READ|SCAN|INSERT|UPDATE|DELETE)\].*95thPercentile/{printf "%10.3f %-10s ",$3/1e6,$1}' run.log | sort | paste -s

     0.355 [SCAN]          0.000 [INSERT]        0.009 [UPDATE]        0.003 [READ]

Measuring the percentiles, 95th Percentile here, is interresting for NoSQL-like workloads. The goal is to scale to million of users and get acceptable response time for most of the interactions. And NoSQL databases are usually used to “web-scale” into nodes distributed over the internet where some latency outliers may happen. Looking at percentiles helps to focus on the SLA while accepting those outliers.

This “jdbc” client declares all attributes as columns in the table. Of course, even if RDBMS works on structured data in order to ease the application code that can then rely on a schema, SQL includes DDL with all agility to add columns later. But in addition to the traditional relational tables, all the major RDBMS can also be used to store documents. And some have even an optimized datatype to store a JSON document in binary format (JSONB in PostgreSQL, OSON in Oracle). Oracle even provides a NoSQL-like API for it: SODA. I hope that Oracle will contribute to the YCSB project adding a client for SODA.

The NoSQL APIs are similar but may have some implementation differences and, for sure, different calls. YCSB implements read, scan, update, insert, delete and here is how it maps to some popular NoSQL databases:

  • YCSB read is equivalent to
    MongoDB: find().first() with “_id”, DynamoDB: getItem(), Cassandra: SELECT without WHERE, HBase: get(), SODA: getOne()
  • YCSB scan is equivalent to
    MongoDB: find().sort().limit(), DynamoDB: scan(), Cassandra: SELECT with WHERE and LIMIT, HBase: scan(), SODA: find()
  • YCSB update is equivalent to
    MongoDB: updateOne(), DynamoDB: updateItem(), Cassandra: UPDATE, HBase: put(), SODA: replaceOne()
  • YCSB insert is equivalent to
    MongoDB: insertOne(), DynamoDB: putItem(), Cassandra: INSERT, HBase: put(), SODA: insert()
  • YCSB delete is equivalent to
    MongoDB: deleteOne(), DynamoDB: deleteItem(), Cassandra: DELETE, HBase: delete(), SODA: remove()

With the evolution of RDBMS to converged databases, it is useful to compare the performance of key-value workloads with JDBC SQL API and NoSQL put/get APIs. YCSB can be used and extended for that. Among the most advanced converged databases:

  • On PostgreSQL you can use the JDBC client or the “postgrenosql” that are already there in the master branch
  • On YugaByteDB you can do the same because it is compatible with PostgreSQL with the YSQL API. YB also provides a Cassandra-like NoSQL API: CQL and here is how to run YCSB on it: https://docs.yugabyte.com/latest/benchmark/ycsb-ycql/
  • On Oracle Database, now that I have added the support for FETCH FIRST, the jdbc client can be used on a relational table. I hope to see a SODA one soon, as SODA (Simple Oracle Document Access) is a very advanced NoSQL API that combines key-value document store with RDBMS consistency and reliability. Oracle has published the result but not the code.

7 Comments

  • Side comments

    – I really don’t like seeing ORACLE_HOME set for Instant Client, since it can have side effects if it points somewhere else.

    – In general, there is no need to set TNS_ADMIN to $ORACLE_HOME/network/admin (or Instant Client’s network/admin) since network/admin is a default location already. I know you were using it to aid the unzip.

  • Hi Christopher, yes that’s right. I’ve updated the post to use different variables names (MY_*) as the goal was just to keep them for later and give a meaningful name. Thanks a lot for the heads-up. Franck.

  • Alex Vin says:

    Hi Franck.
    oracle in maximum protection (STB1 and STB2).
    Data Guard Broker enabled.
    when one of standby databases (STB1) crashes, primary database continues committing.
    we don’t need it.
    how to get primary database to hang after losing one of the two standby databases?

  • Hi Alex, this is very far from the subject of this blog post! And I would discuss the need for that (hand the primary even when one standby is ready for no data loss failover) before finding a solution.

  • Thanks for the update. BTW, that’s a nice trick for finding the appropriate network/admin directory.

  • Alex Vin says:

    I understand it is far away from the topic. but there is no response to comments on old blog posts)

    https://blog.dbi-services.com/dataguard-oracle-12-2-fast-start-failover-with-maximum-protecion/#comment-13572

    We have a primary database PRIMARYDC1 in the first datacenter and two standby database
    STB1DC1 in the fisrt datacenter and STB2DC2 in the second datacenter.

    In case of loss primary database host we want failover to STB1DC1 in the first data center.
    In case of loss first data center we want zero data loss on STB2DC2 in the second data center.
    In case of loss second data center we will suspend the application.
    We want zero data loss.

    In future, we going to organize three data centers and remove the “local” standby database (STB1DC1) from the first data center.
    And we want make databases without data loss in the second and in the third data center in both.

  • Hi Alex Vin, you are posting a question to another author’s post on a completely unrelated subject. This is not a forum. And anyway, a feasibility study about HA and DR may deserve some consulting as it requires time to understand the problems and documentation about the solutions. We are doing consulting. We share some free stuff on the blogs, and open comments to get feedback and questions on the topic. But this is not a good place for free consulting or software support 😉 That said, you may have a look at Binding and LogArchiveMinSucceedDest broker properties and contact ORacle support if they don’t work as documented.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Franck Pachot
Franck Pachot

Principal Consultant / Database Evangelist
Oracle ACE Director
Oracle Database OCM 12c certified
AWS Database Specialty certified
Oak Table member

RSS for this blog: feed
Twitter: @FranckPachot
LinkedIn : www.linkedin.com/in/franckpachot
Podcast en français: DBPod