Steps to Run BenchmarkSQL on AntsDB/MySQL
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
Leave a Reply