{"id":14864,"date":"2020-10-12T18:23:30","date_gmt":"2020-10-12T16:23:30","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/ycsb-nosql-benchmark-on-oracle-database\/"},"modified":"2023-07-17T08:16:59","modified_gmt":"2023-07-17T06:16:59","slug":"ycsb-nosql-benchmark-on-oracle-database","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/ycsb-nosql-benchmark-on-oracle-database\/","title":{"rendered":"YCSB (NoSQL benchmark) on Oracle Database"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nThe 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.<\/p>\n

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<\/a>).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:<\/p>\n

\n[opc@al YCSB]$ grep -r \"ycsb.DB\" . | awk -F\/ '{print $2}' | sort -u | paste -sd,\naccumulo1.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\n<\/code><\/pre>\n
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.<\/p>\n
Here is how to download the latest relase of YCSB:<\/p>\n
\ncd \/var\/tmp\nrelease=\"$(curl https:\/\/github.com\/brianfrankcooper\/YCSB | awk '\/[\/]download[\/]\/{print $NF}' )\"\ncurl --location \"$release\" | tar -zxvf -\n<\/code><\/pre>\n
However, my Pull Request to support the FETCH FIRST n ROWS ONLY<\/a> 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.<\/p>\n
\nsudo yum-config-manager --add-repo http:\/\/repos.fedorapeople.org\/repos\/dchen\/apache-maven\/epel-apache-maven.repo\nsudo yum-config-manager --enable epel-apache-maven\nsudo yum install -y git apache-maven\n<\/code><\/pre>\n
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.<\/p>\n
I compile YCSB:<\/p>\n
\n(\ngit clone https:\/\/github.com\/brianfrankcooper\/YCSB.git\ncd YCSB\nmvn clean package\nmvn -pl site.ycsb:jdbc-binding -am package -DskipTests dependency:build-classpath -DincludeScope=compile -Dmdep.outputFilterFile=true\n) > mvn.log\n<\/code><\/pre>\n
I keep the log in “mvn.log” and I’ll use it to get the classpath<\/p>\n
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:<\/p>\n
\ncd \/var\/tmp\nwget https:\/\/download.oracle.com\/otn_software\/linux\/instantclient\/oracle-instantclient-basic-linuxx64.rpm\nwget https:\/\/download.oracle.com\/otn_software\/linux\/instantclient\/oracle-instantclient-sqlplus-linuxx64.rpm\nsudo yum localinstall -y oracle-instantclient-basic-linuxx64.rpm oracle-instantclient-sqlplus-linuxx64.rpm\n<\/code><\/pre>\n
In this example, I’ll connect to my free Oracle Autonomous Database for which I’ve downloaded the wallet into my TNS_ADMIN directory.<\/p>\n
Here is my quick and dirty way to set the ORACLE_HOME from an installed Instant Client with sqlplus:<\/span>
\nUpdated (see comments)<\/b> I’get important directories for future use:<\/p>\n
\nMY_ORACLE_HOME=$(sqlplus \/nolog <<<'get ?\"' | awk -F'\"' '\/SP2-0160\/{print $(NF-2)}')\nMY_TNS_ADMIN=$MY_ORACLE_HOME\/network\/admin\nMY_LD_LIBRARY_PATH=$MY_ORACLE_HOME\nMY_CLASSPATH=\"$MY_ORACLE_HOME\/ojdbc8.jar\"\n<\/code><\/pre>\n
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<\/p>\n
If you are too lazy to create a database, I let you use the one I’ve shared for the SQL101<\/a> presentation. Here is my wallet.<\/p>\n
\nwget https:\/\/objectstorage.us-ashburn-1.oraclecloud.com\/n\/idhoprxq7wun\/b\/pub\/o\/Wallet_sql101.zip\nsudo unzip -od $MY_TNS_ADMIN Wallet_sql101.zip\n<\/code><\/pre>\n
Please be smart if you use my database, you are not alone there. It is easy to open an Oracle Cloud trial<\/a> that will leave you with a free Oracle Database for life.<\/p>\n
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.<\/p>\n
\ncat > \/tmp\/ycsb.database.properties <<CAT\ndb.driver=oracle.jdbc.driver.OracleDriver\ndb.url=jdbc:oracle:oci:@sql101_tp\ndb.user=SQL101DEMO\ndb.passwd=**P455w0rd**\njdbc.batchupdateapi=true\ndb.batchsize=1000\nCAT\n<\/code><\/pre>\n
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.<\/p>\n
YCSB workload is defined by a few properties:<\/p>\n
\ncat > \/tmp\/ycsb.workload.properties <<'CAT'\nthreadcount=20\nfieldcount=1\nfieldlength=42\nrecordcount=100000\noperationcount=10000\nworkload=site.ycsb.workloads.CoreWorkload\nreadallfields=true\nreadproportion=0.3\nupdateproportion=0.2\nscanproportion=0.1\ninsertproportion=0.4\nrequestdistribution=zipfian\nminscanlength=1\nmaxscanlength=1000\nCAT\n<\/code><\/pre>\n
All this is documented in https:\/\/github.com\/brianfrankcooper\/YCSB\/wiki\/Core-Properties<\/a><\/p>\n
Here is my sqlplus script to create the table used by YCSB<\/p>\n
\ncat > \/tmp\/ycsb.create.sql <<'SQL'\nset pagesize 1000 long 1000000 echo on\nwhenever sqlerror exit failure\nexec for i in (select * from user_tables where table_name='USERTABLE') loop execute immediate 'drop table USERTABLE'; end loop;\npurge recyclebin;\ncreate table USERTABLE (\n  YCSB_KEY varchar2(25) primary key,\n  FIELD0 &&1\n)\n&&2\n\/\nselect dbms_metadata.get_ddl('TABLE','USERTABLE') from dual;\nexit\nSQL\n<\/code><\/pre>\n
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.<\/p>\n
When running on my SQL101 you are limited in space, so let’s keep it simple:<\/p>\n
\nsqlplus SQL101DEMO\/\"**P455w0rd**\"@sql101_tp @ \/tmp\/ycsb.create.sql \"varchar2(42)\" \"organization index nologging --partition by hash(YCSB_KEY) partitions 8\"\n<\/code><\/pre>\n
Now I have a USERTABLE in the DEMO schema.<\/p>\n
I put all environment variables in setenv.sh which is called by ycsb.sh<\/p>\n
\ncat > \/var\/tmp\/YCSB\/bin\/setenv.sh <<CAT\nTNS_ADMIN=$MY_TNS_ADMIN\nCLASSPATH=$MY_CLASSPATH:$(awk -F= '\/^classpath=\/{cp=$2}END{print cp}' \/var\/tmp\/mvn.log)\nexport LD_LIBRARY_PATH=$MY_LD_LIBRARY_PATH\nCAT\n<\/code><\/pre>\n
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<\/p>\n
I am now ready to load some data:<\/p>\n
\n\/var\/tmp\/YCSB\/bin\/ycsb.sh load jdbc -P \/tmp\/ycsb.workload.properties -P \/tmp\/ycsb.database.properties\n<\/code><\/pre>\n
This reads \/tmp\/ycsb.workload.properties for the number of records to create.<\/p>\n
Here is how to run the workload defined in \/tmp\/ycsb.workload.properties<\/p>\n
\n\/var\/tmp\/YCSB\/bin\/ycsb.sh run jdbc -P \/tmp\/ycsb.workload.properties -P \/tmp\/ycsb.database.properties | tee run.log\n<\/code><\/pre>\n
I’m showing below the Performance Hub when I’ve run it on my SQL101 database with 20 client threads.
\n\"\"
\nI’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.<\/p>\n
Here is how to quickly parse the log to see the result:<\/p>\n
awk -F, '\/^\\[(READ|SCAN|INSERT|UPDATE|DELETE)\\].*95thPercentile\/{printf \"%10.3f %-10s \",$3\/1e6,$1}' run.log | sort | paste -s\n\n     0.355 [SCAN]          0.000 [INSERT]        0.009 [UPDATE]        0.003 [READ]\n\n<\/code><\/pre>\n
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.<\/p>\n
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.<\/p>\n
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:<\/p>\n