Steps to Run BenchmarkSQL on AntsDB/MySQL

Steps to Run BenchmarkSQL on AntsDB/MySQL

By inProduct Release

Requirements

First of all, the system must meet the following requirements

  • 16GB memory. Could be more or less depending on the test volume.
  • 100GB free storage. SSD is preferred.
  • Centos 7

Install Required Software

sudo yum install wget java-1.8.0-openjdk maven git ant mysql tuned-profiles-oracle

Install AntsDB

cd ; wget http://antsdb.com/download/antsdb-18.05.27-bin.tar.gz; tar xzvf antsdb-18.05.27-bin.tar.gz

Open the AntsDB configuration file at ~/antsdb/conf/conf.properties, specify the hbase-site.xml and cache size. This step is optional, if HBase configuration is missing, all data will be stored locally.

humpback.storage-engine=hbase
hbase_compression_codec=SNAPPY
humpback.hbase-conf=/etc/hbase/conf/hbase-site.xml
cache.size=100g

Install BenchmarkSQL

cd ; git clone git@github.com:waterguo/benchmarksql.git
cd ~/benchmarksql; ant

This is my fork of the official BenchmarkSQL 4.1.1. The only difference from the original are the extra scripts to create test schema on MySQL and Oracle, as well as MySQL JDBC driver in the lib directory.

Create the Schema

Start AntsDB using the command below

~/antsdb/bin/antsdb start

Create schema in AntsDB using the command below

echo 'create database benchmarksql;' | mysql -h 127.0.0.1
cd ~/benchmarksql/run
./runSQL.sh props.mysql sqlTableCreates.mysql
./runSQL.sh props.mysql sqlIndexCreates.mysql

Load the Data

The command below will load 100 warehouses into the database. Each warehouse consists of 500k records roughly.

cd ~/benchmarksql/run
./runLoader.sh props.mysql numWarehouses 100

Run the Benchmark

Open the benchmark profile at /home/xinyi/benchmarksql/run/props.mysql. Change warehouses to the number of warehouses you loaded in above step. Change terminals to the number of concurrent sessions you want to run in the benchmark.

Issue the following command to kick off the benchmark.

cd ~/benchmarksql/run
./runBenchmark.sh props.mysql

Tuning

To achieve the best performance, consider changing the following parameter

  • Change the kernel parameters using command sudo tuned-adm profile oracle
  • Disable SELinux using command sudo setenforce 0
  • Change AntsDB heap size to 4GB. Specify wrapper.java.maxmemory=4096 in configuration file ~/antsdb/conf/wrapper.conf
  • Change JVM to server mode. Specify wrapper.java.additional.1=-server in configuration file ~/antsdb/conf/wrapper.conf
  • Change readahead to 16KB for very large volume echo 16 >  /sys/block/sdb/queue/read_ahead_kb
Xinwei

Leave a Reply