[PostgreSQL]: YSCB Benchmarking

July 29, 2015 · by · in PostgresSQL

[ postgres @ dbversity : /YCSB-master ] ls -lhtr
total 2.4M
-rw-r–r– 1 postgres postgres 479 Aug 18 2014 NOTICE.txt
-rw-r–r– 1 postgres postgres 7.9K Aug 18 2014 LICENSE.txt
-rw-r–r– 1 postgres postgres 2.6K Aug 18 2014 CHANGELOG
drwxrwxrwx 2 postgres postgres 4.0K Aug 18 2014 bin
drwxrwxrwx 2 postgres postgres 4.0K Aug 18 2014 workloads
drwxr-xr-x 3 postgres postgres 4.0K Jul 27 07:42 core
drwxr-xr-x 4 postgres postgres 4.0K Jul 27 07:42 jdbc-binding
-rw-r–r– 1 postgres postgres 1.4K Jul 27 07:51 ycsbscript.sh
-rw-r–r– 1 postgres postgres 946K Jul 27 07:55 mysql-connector-java-5.1.35-bin.jar
-rw-r–r– 1 postgres postgres 634K Jul 27 08:19 postgresql-9.4-1201.jdbc41.jar
-rw-r–r– 1 postgres postgres 628K Jul 27 10:07 postgresql-9.4-1201.jdbc4.jar
-rw-r–r– 1 postgres postgres 77K Jul 27 10:38 output.txt
drwxr-xr-x 2 postgres postgres 24K Jul 28 05:15 logs
-rw-r–r– 1 postgres postgres 275 Jul 28 05:26 inittable.sql
[ postgres @ dbversity : /YCSB-master ]
[ postgres @ dbversity : /YCSB-master ]
[ postgres @ dbversity : /YCSB-master ] cat inittable.sql
\connect ycsb
DROP TABLE IF EXISTS usertable;

CREATE TABLE usertable (YCSB_KEY VARCHAR(255) PRIMARY KEY,
FIELD1 TEXT, FIELD2 TEXT,
FIELD3 TEXT, FIELD4 TEXT,
FIELD5 TEXT, FIELD6 TEXT,
FIELD7 TEXT, FIELD8 TEXT,
FIELD9 TEXT, FIELD10 TEXT);

SET AUTOCOMMIT = ON;
\q

[ postgres @ dbversity : /YCSB-master ]
[ postgres @ dbversity : /YCSB-master ]
[ postgres @ dbversity : /YCSB-master ]
[ postgres @ dbversity : /YCSB-master ] cat ycsbscript.sh
export YCSBHOME=/YCSB-master
psql < $YCSBHOME/inittable.sql

cd $YCSBHOME/logs

if [ -z “${host}” ]; then host=`hostname -s`; fi
host=`echo $host | sed ‘s/\-/_/g’`
if [ -z “${tag}” ]; then LOGSUFFIX=`date +”%y%m%d%H%M”`; else LOGSUFFIX=${tag}; fi

psql < $YCSBHOME/inittable.sql

### To Load the workload data#####
${YCSBHOME}/bin/ycsb load jdbc -P ${YCSBHOME}/workloads/workloada -P ${YCSBHOME}/jdbc-binding/conf/db.properties -p recordcount=1000000 -p operationcount=1000000 -p readallfields=true -p hotspotdatafraction=1 -p insertorder=ordered -threads 50

for th in 50 35 25 10
do

for wl in a b c d f
do

echo “Results for $th thread – workload $wl are … ” >> ${YCSBHOME}/logs/$th\_thread_$wl\_workload_logs.txt
echo ” Workload $wl with $th thread started … “

DTSTART=`date +”%y/%m/%d %H:%M:%S”`

${YCSBHOME}/bin/ycsb run jdbc -s -P ${YCSBHOME}/workloads/workload$wl -P ${YCSBHOME}/jdbc-binding/conf/db.properties -p recordcount=1000000 -p operationcount=1000000 -p readallfields=true -p hotspotdatafraction=1 -p insertorder=ordered -threads $th >> ${YCSBHOME}/logs/ycsb-${host}-${wl}-run-${th}-${LOGSUFFIX}.log 2>&1

DTEND=`date +”%y/%m/%d %H:%M:%S”`

echo ” Workload $wl with $th thread completed … “
echo “ycsbrun,${host},${wl},${DTSTART},${DTEND},${LOGSUFFIX},${th}” >> ${YCSBHOME}/logs/ycsbtest.log

done
done
[ postgres @ dbversity : /YCSB-master ]
[ postgres @ dbversity : /YCSB-master ]
[ postgres @ dbversity : /YCSB-master ]

[ postgres @ dbversity : /YCSB-master ] ls -lhtr
total 2.4M
-rw-r–r– 1 postgres postgres 479 Aug 18 2014 NOTICE.txt
-rw-r–r– 1 postgres postgres 7.9K Aug 18 2014 LICENSE.txt
-rw-r–r– 1 postgres postgres 2.6K Aug 18 2014 CHANGELOG
drwxrwxrwx 2 postgres postgres 4.0K Aug 18 2014 bin
drwxrwxrwx 2 postgres postgres 4.0K Aug 18 2014 workloads
drwxr-xr-x 3 postgres postgres 4.0K Jul 27 07:42 core
drwxr-xr-x 4 postgres postgres 4.0K Jul 27 07:42 jdbc-binding
-rw-r–r– 1 postgres postgres 1.4K Jul 27 07:51 ycsbscript.sh
-rw-r–r– 1 postgres postgres 946K Jul 27 07:55 mysql-connector-java-5.1.35-bin.jar
-rw-r–r– 1 postgres postgres 634K Jul 27 08:19 postgresql-9.4-1201.jdbc41.jar
-rw-r–r– 1 postgres postgres 628K Jul 27 10:07 postgresql-9.4-1201.jdbc4.jar
-rw-r–r– 1 postgres postgres 77K Jul 27 10:38 output.txt
drwxr-xr-x 2 postgres postgres 24K Jul 28 05:15 logs
-rw-r–r– 1 postgres postgres 275 Jul 28 05:26 inittable.sql
[ postgres @ dbversity : /YCSB-master ]
[ postgres @ dbversity : /YCSB-master ]
[ postgres @ dbversity : /YCSB-master ]
[ postgres @ dbversity : /YCSB-master ] ls -lhtr logs/
total 4.7M
-rw-r–r– 1 postgres postgres 1018 Jul 27 07:44 ycsb-vm_c8bd_6151-f-run-35-1507270719.log
-rw-r–r– 1 postgres postgres 624 Jul 27 07:44 ycsb-vm_c8bd_6151-d-run-10-1507270733.log
-rw-r–r– 1 postgres postgres 1018 Jul 27 07:44 ycsb-vm_c8bd_6151-c-run-10-1507270719.log
-rw-r–r– 1 postgres postgres 1018 Jul 27 07:44 ycsb-vm_c8bd_6151-b-run-10-1507270719.log
-rw-r–r– 1 postgres postgres 624 Jul 27 07:44 ycsb-vm_c8bd_6151-a-run-50-1507270729.log
-rw-r–r– 1 postgres postgres 383 Jul 27 07:44 ycsb-vm_c8bd_6151-f-run-50-1507270738.log
-rw-r–r– 1 postgres postgres 1018 Jul 27 07:44 ycsb-vm_c8bd_6151-f-run-50-1507270719.log
-rw-r–r– 1 postgres postgres 624 Jul 27 07:44 ycsb-vm_c8bd_6151-f-run-35-1507270733.log
-rw-r–r– 1 postgres postgres 624 Jul 27 07:44 ycsb-vm_c8bd_6151-f-run-25-1507270729.log
-rw-r–r– 1 postgres postgres 1.3K Jul 27 07:44 ycsb-vm_c8bd_6151-f-run-25-1507270720.log
[ postgres @ dbversity : /YCSB-master ] cat ~/.bashrc
export YCSBHOME=/YCSB-master
export PG_HOME=/var/lib/pgsql/9.4/data
export EDITOR=vi

export CLASSPATH=/var/lib/pgsql/9.4/data/mysql-connector-java-5.1.35/mysql-connector-java-5.1.35-bin.jar
export JAVA_HOME=/var/lib/pgsql/9.4/data/jdk1.8.0_05
export PATH=$PATH:$JAVA_HOME/bin
[ postgres @ dbversity : /YCSB-master ]

[ postgres @ dbversity : /YCSB-master ] ls -lhtr /var/lib/pgsql/9.4/data/
total 244K
drwxr-xr-x 8 postgres postgres 4.0K Mar 18 2014 jdk1.8.0_05
drwxr-xr-x 4 postgres postgres 4.0K Mar 17 05:21 mysql-connector-java-5.1.35
-rwx—— 1 postgres postgres 4 Jul 24 10:47 PG_VERSION
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_twophase
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_tblspc
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_snapshots
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_serial
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_replslot
drwx—— 4 postgres postgres 4.0K Jul 24 10:47 pg_multixact
drwx—— 4 postgres postgres 4.0K Jul 24 10:47 pg_logical
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_dynshmem
-rwx—— 1 postgres postgres 141 Jul 24 10:50 recovery.done
-rw——- 1 postgres postgres 202 Jul 24 11:18 backup_label.old
-rwx—— 1 postgres postgres 88 Jul 24 15:17 postgresql.auto.conf
-rwx—— 1 postgres postgres 1.6K Jul 24 15:17 pg_ident.conf
drwx—— 6 postgres postgres 4.0K Jul 27 07:31 base
-rwx—— 1 postgres postgres 4.3K Jul 28 01:37 pg_hba.conf
-rwx—— 1 postgres postgres 21K Jul 28 01:51 postgresql.conf
-rw-r–r– 1 postgres postgres 106K Jul 28 01:58 output.txt
-rw——- 1 postgres postgres 73 Jul 28 04:34 postmaster.pid
drwx—— 2 postgres postgres 4.0K Jul 28 04:34 pg_notify
-rwx—— 1 postgres postgres 59 Jul 28 04:34 postmaster.opts
drwx—— 2 postgres postgres 4.0K Jul 28 04:34 pg_stat
drwx—— 2 postgres postgres 4.0K Jul 28 05:20 pg_clog
drwx—— 2 postgres postgres 4.0K Jul 28 05:30 pg_subtrans
drwx—— 3 postgres postgres 4.0K Jul 28 05:39 pg_xlog
drwx—— 2 postgres postgres 4.0K Jul 29 00:00 pg_log
drwx—— 2 postgres postgres 4.0K Jul 29 01:05 global
drwx—— 2 postgres postgres 4.0K Jul 29 01:05 pg_stat_tmp
[ postgres @ dbversity : /YCSB-master ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ] 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 PS1=”[ \u @ \h : \w ] “

####### http proxy settings #####
export http_proxy=http://webproxy.ssmb.com:8080/
export https_proxy=http://webproxy.ssmb.com:8080/
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ postgres @ dbversity : /YCSB-master ] exit
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ] 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 PS1=”[ \u @ \h : \w ] “

####### http proxy settings #####
export http_proxy=http://webproxy.ssmb.com:8080/
export https_proxy=http://webproxy.ssmb.com:8080/
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ]
[ root @ dbversity : ~ ] cd /var/lib/pgsql/9.4/data/
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ] ll -lhtr
total 244K
drwxr-xr-x 8 postgres postgres 4.0K Mar 18 2014 jdk1.8.0_05
drwxr-xr-x 4 postgres postgres 4.0K Mar 17 05:21 mysql-connector-java-5.1.35
-rwx—— 1 postgres postgres 4 Jul 24 10:47 PG_VERSION
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_twophase
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_tblspc
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_snapshots
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_serial
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_replslot
drwx—— 4 postgres postgres 4.0K Jul 24 10:47 pg_multixact
drwx—— 4 postgres postgres 4.0K Jul 24 10:47 pg_logical
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_dynshmem
-rwx—— 1 postgres postgres 141 Jul 24 10:50 recovery.done
-rw——- 1 postgres postgres 202 Jul 24 11:18 backup_label.old
-rwx—— 1 postgres postgres 88 Jul 24 15:17 postgresql.auto.conf
-rwx—— 1 postgres postgres 1.6K Jul 24 15:17 pg_ident.conf
drwx—— 6 postgres postgres 4.0K Jul 27 07:31 base
-rwx—— 1 postgres postgres 4.3K Jul 28 01:37 pg_hba.conf
-rwx—— 1 postgres postgres 21K Jul 28 01:51 postgresql.conf
-rw-r–r– 1 postgres postgres 106K Jul 28 01:58 output.txt
-rw——- 1 postgres postgres 73 Jul 28 04:34 postmaster.pid
drwx—— 2 postgres postgres 4.0K Jul 28 04:34 pg_notify
-rwx—— 1 postgres postgres 59 Jul 28 04:34 postmaster.opts
drwx—— 2 postgres postgres 4.0K Jul 28 04:34 pg_stat
drwx—— 2 postgres postgres 4.0K Jul 28 05:20 pg_clog
drwx—— 2 postgres postgres 4.0K Jul 28 05:30 pg_subtrans
drwx—— 3 postgres postgres 4.0K Jul 28 05:39 pg_xlog
drwx—— 2 postgres postgres 4.0K Jul 29 00:00 pg_log
drwx—— 2 postgres postgres 4.0K Jul 29 01:06 global
drwx—— 2 postgres postgres 4.0K Jul 29 01:06 pg_stat_tmp
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ] grep -v “#” pg_ident.conf
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ] grep -v “#” pg_hba.conf

host all all 0.0.0.0/0 md5

host replication rep xx.xxx.175.107/32 md5

host all all 127.0.0.1/32 ident
host all all ::1/128 ident
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ] grep -v “#” postgresql.conf

log_timezone = ‘US/Eastern’
datestyle = ‘iso, mdy’
timezone = ‘US/Eastern’
default_text_search_config = ‘pg_catalog.english’

listen_addresses = ‘*’
wal_level = ‘hot_standby’
max_wal_senders = 1
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ] ll -lhtr
total 244K
drwxr-xr-x 8 postgres postgres 4.0K Mar 18 2014 jdk1.8.0_05
drwxr-xr-x 4 postgres postgres 4.0K Mar 17 05:21 mysql-connector-java-5.1.35
-rwx—— 1 postgres postgres 4 Jul 24 10:47 PG_VERSION
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_twophase
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_tblspc
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_snapshots
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_serial
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_replslot
drwx—— 4 postgres postgres 4.0K Jul 24 10:47 pg_multixact
drwx—— 4 postgres postgres 4.0K Jul 24 10:47 pg_logical
drwx—— 2 postgres postgres 4.0K Jul 24 10:47 pg_dynshmem
-rwx—— 1 postgres postgres 141 Jul 24 10:50 recovery.done
-rw——- 1 postgres postgres 202 Jul 24 11:18 backup_label.old
-rwx—— 1 postgres postgres 88 Jul 24 15:17 postgresql.auto.conf
-rwx—— 1 postgres postgres 1.6K Jul 24 15:17 pg_ident.conf
drwx—— 6 postgres postgres 4.0K Jul 27 07:31 base
-rwx—— 1 postgres postgres 4.3K Jul 28 01:37 pg_hba.conf
-rwx—— 1 postgres postgres 21K Jul 28 01:51 postgresql.conf
-rw-r–r– 1 postgres postgres 106K Jul 28 01:58 output.txt
-rw——- 1 postgres postgres 73 Jul 28 04:34 postmaster.pid
drwx—— 2 postgres postgres 4.0K Jul 28 04:34 pg_notify
-rwx—— 1 postgres postgres 59 Jul 28 04:34 postmaster.opts
drwx—— 2 postgres postgres 4.0K Jul 28 04:34 pg_stat
drwx—— 2 postgres postgres 4.0K Jul 28 05:20 pg_clog
drwx—— 2 postgres postgres 4.0K Jul 28 05:30 pg_subtrans
drwx—— 3 postgres postgres 4.0K Jul 28 05:39 pg_xlog
drwx—— 2 postgres postgres 4.0K Jul 29 00:00 pg_log
drwx—— 2 postgres postgres 4.0K Jul 29 01:07 global
drwx—— 2 postgres postgres 4.0K Jul 29 01:07 pg_stat_tmp
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ] grep -v “#” recovery.done
standby_mode = ‘on’
primary_conninfo = ‘host=10.42.105.44 port=5432 user=rep password=test123’
trigger_file = ‘/tmp/postgresql.trigger.5432’
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ] cat PG_VERSION
9.4
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ] which java
/usr/bin/which: no java in (/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin)
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ] java
-bash: java: command not found
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ]
[ root @ dbversity : /var/lib/pgsql/9.4/data ] su postgres
[ postgres @ dbversity : ~/9.4/data ]
[ postgres @ dbversity : ~/9.4/data ]
[ postgres @ dbversity : ~/9.4/data ]
[ postgres @ dbversity : ~/9.4/data ]
[ postgres @ dbversity : ~/9.4/data ] /YCSB-master/bin/ycsb load jdbc -P /YCSB-master/workloads/workloada -P /YCSB-master/jdbc-binding/conf/db.properties -p recordcount=10 -p operationcount=10 -p readallfields=true -p hotspotdatafraction=1 -p insertorder=ordered -threads 1
java -cp /YCSB-master/mysql-connector-java-5.1.35-bin.jar:/YCSB-master/postgresql-9.4-1201.jdbc4.jar:/YCSB-master/postgresql-9.4-1201.jdbc41.jar:/YCSB-master/core/lib/core-0.1.4.jar:/YCSB-master/jdbc-binding/lib/jdbc-binding-0.1.4.jar:/YCSB-master/jdbc-binding/conf com.yahoo.ycsb.Client -db com.yahoo.ycsb.db.JdbcDBClient -P /YCSB-master/workloads/workloada -P /YCSB-master/jdbc-binding/conf/db.properties -p recordcount=10 -p operationcount=10 -p readallfields=true -p hotspotdatafraction=1 -p insertorder=ordered -threads 1 -load
YCSB Client 0.1
Command line: -db com.yahoo.ycsb.db.JdbcDBClient -P /YCSB-master/workloads/workloada -P /YCSB-master/jdbc-binding/conf/db.properties -p recordcount=10 -p operationcount=10 -p readallfields=true -p hotspotdatafraction=1 -p insertorder=ordered -threads 1 -load
Loading workload…
Starting test.
Adding shard node URL: jdbc:postgresql://10.42.105.44:5432/postgres
Using 1 shards
Error in processing insert to table: usertableorg.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “usertable_pkey”
Detail: Key (ycsb_key)=(user0) already exists.
[OVERALL], RunTime(ms), 196.0
[OVERALL], Throughput(ops/sec), 0.0
[CLEANUP], Operations, 1
[CLEANUP], AverageLatency(us), 202.0
[CLEANUP], MinLatency(us), 202
[CLEANUP], MaxLatency(us), 202
[CLEANUP], 95thPercentileLatency(ms), 0
[CLEANUP], 99thPercentileLatency(ms), 0

CREATE USER dbversity WITH PASSWORD ‘yourpassword’;
GRANT ALL PRIVILEGES ON DATABASE ycsb to dbversity;
postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO dbversity;
GRANT
postgres=# GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO dbversity;
GRANT
postgres=#

\connect ycsb
DROP TABLE IF EXISTS usertable;

CREATE TABLE usertable (YCSB_KEY VARCHAR(255) PRIMARY KEY,
FIELD1 TEXT, FIELD2 TEXT,
FIELD3 TEXT, FIELD4 TEXT,
FIELD5 TEXT, FIELD6 TEXT,
FIELD7 TEXT, FIELD8 TEXT,
FIELD9 TEXT, FIELD10 TEXT);

SET AUTOCOMMIT = ON;
\q
script :

#!/bin/bash
{% if postgresql.host is defined %}

{{directory}}/script/postgresql-event-generator

createdb -U postgres -h {{ postgresql.host.name }} -p {{postgresql.port}} {{table}}
psql -d {{table}} -U postgres -h {{ postgresql.host.name }} -p {{postgresql.port}} -f {{directory}}/YCSB/jdbc/src/main/resources/sql/create_table.sql

mkdir -p {{ directory }}/results/postgresql
rm -f -r {{ directory }}/results/postgresql/*

touch {{ directory }}/results/postgresql/timings.out

echo “Start $(date)” >> {{ directory }}/results/postgresql/timings.out
{{ directory }}/script/postgresql-delete

{{directory}}/YCSB/bin/ycsb load jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-load -s > {{ directory }}/results/postgresql/load-unlimited.dat;
sleep 50s
{{directory}}/YCSB/bin/ycsb run jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-stress-load -s -threads 30 > {{ directory }}/results/postgresql/unlimited.dat
sleep 20s

{% if threads %}
sleep 100s
for t in 1 2 3 4 5 7 10 15 20 30 40 50 75 100; do
for i in 2;do
echo “Threads $t $i $(date)” >> {{ directory }}/results/postgresql/timings.out
{{ directory }}/script/postgresql-delete
{{directory}}/YCSB/bin/ycsb load jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-load -s > {{ directory }}/results/postgresql/load-threads-$t-$i.dat;
sleep 50s
{{directory}}/YCSB/bin/ycsb run jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-stress-load -s -threads $t > {{ directory }}/results/postgresql/threads-$t-$i.dat;
done
done
{% endif %}

{% if maxLoad %}
sleep 100s
for t in 20 50 100 150 200 250 300 400 500 600 700 800 900 1000; do
for i in 2;do
echo “Hockey $t $i $(date)” >> {{ directory }}/results/postgresql/timings.out

{{ directory }}/script/postgresql-delete
{{directory}}/YCSB/bin/ycsb load jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-load -s > {{ directory }}/results/postgresql/load-target-$t-$i.dat;
sleep 50s
{{directory}}/YCSB/bin/ycsb run jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-stress-load -s -threads 30 -target $t > {{ directory }}/results/postgresql/target-$t-$i.dat;

done;
done;
{%endif %}

{% if availabilityStop %}
sleep 100s
{%for node in postgresql.pgpoolNodes %}

#postgresql – Continuous run: 900s!
for run in 1 2 ;do
echo “Continiuum {{node.host.name}} $run $(date)” >> {{ directory }}/results/postgresql/timings.out

{{ directory }}/script/postgresql-delete;

{{directory}}/YCSB/bin/ycsb load jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-load -s > {{directory}}/results/postgresql/load-continuous-{{ node.host.name }}-$run.dat;
sleep 50s
{{directory}}/YCSB/bin/ycsb run jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-continuous -s -threads 30 -target 100 -p eventFile={{directory}}/config/postgresql/postgresql-event-{{ node.host.name }}.xml > {{directory}}/results/postgresql/continuous-{{ node.host.name }}-$run.dat;
sleep 50s;
{{ directory }}/script/postgresql-remote-recover {{ loop.index0}} {{ node.host.name }};
done;
{% endfor %}
{%endif %}

{% if availabilityKill %}
sleep 100s
{%for node in postgresql.pgpoolNodes %}

#postgresql – Continuous run: 900s!
for run in 1 2 ;do
echo “Continiuum {{node.host.name}} KILL $run $(date)” >> {{ directory }}/results/postgresql/timings.out

{{ directory }}/script/postgresql-delete;

{{directory}}/YCSB/bin/ycsb load jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-load -s {{directory}}/results/postgresql/load-continuous-{{ node.host.name }}-kill-$run.dat;
sleep 50s
{{directory}}/YCSB/bin/ycsb run jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-continuous -s -threads 30 -target 100 -p eventFile={{directory}}/config/postgresql/postgresql-event-{{ node.host.name }}-kill.xml > {{directory}}/results/postgresql/continuous-{{ node.host.name }}-kill-$run.dat;
sleep 50s;
{{ directory }}/script/postgresql-remote-recover {{ loop.index0}} {{ node.host.name }};
done;
{% endfor %}
{%endif %}

{% if availabilityDrop %}
sleep 100s
{%for node in postgresql.pgpoolNodes %}

#postgresql – Continuous run: 900s!
for run in 1 2 ;do
echo “Continiuum {{node.host.name}} DROP $run $(date)” >> {{ directory }}/results/postgresql/timings.out

{{ directory }}/script/postgresql-delete;

{{directory}}/YCSB/bin/ycsb load jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-load -s {{directory}}/results/postgresql/load-continuous-{{ node.host.name }}-drop-$run.dat;
sleep 50s
{{directory}}/YCSB/bin/ycsb run jdbc -P {{directory}}/config/postgresql.conf -P {{directory}}/config/workload-ycsb-thomas -P {{directory}}/config/workload-ycsb-thomas-continuous -s -threads 30 -target 100 -p eventFile={{directory}}/config/postgresql/postgresql-event-{{ node.host.name }}-drop.xml > {{directory}}/results/postgresql/continuous-{{ node.host.name }}-drop-$run.dat;
sleep 50s;
{{ directory }}/script/postgresql-remote-recover {{ loop.index0}} {{ node.host.name }};
done;
{% endfor %}
{% endif %}
{% endif %}

=====================
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://{{dbversity}}:{{5432}}/{{usertable}}
db.user=
db.passwd=
export YCSBHOME=/YCSB-master
export PG_HOME=/var/lib/pgsql/9.4/data
export EDITOR=vi

export CLASSPATH=/var/lib/pgsql/9.4/data/mysql-connector-java-5.1.35/mysql-connector-java-5.1.35-bin.jar
export JAVA_HOME=/var/lib/pgsql/9.4/data/jdk1.8.0_05
export PATH=$PATH:$JAVA_HOME/bin

create database ycsb;

\connect ycsb

drop table usertable;

CREATE TABLE usertable (YCSB_KEY varchar(255) PRIMARY KEY,
FIELD1 varchar(255), FIELD2 varchar(255),
FIELD3 varchar(255), FIELD4 varchar(255),
FIELD5 varchar(255), FIELD6 varchar(255),
FIELD7 varchar(255), FIELD8 varchar(255),
FIELD9 varchar(255), FIELD10 varchar(255));

set autocommit = on;

\q

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:221)

host replication rep xx.xxx.175.107/32 md5
host ycsb root 10.42.105.44/32 md5
host ycsb root xx.xxx.175.107/32 md5
host postgres rep 10.42.105.44/32 md5
host postgres root xx.xxx.175.107/32 md5
lockfile=”/var/lock/subsys/postgres/${NAME}”
pidfile=”/var/run/postgres/${NAME}.pid”
export YCSBHOME=/YCSB-master
psql < $YCSBHOME/inittable.sql

cd $YCSBHOME/logs

if [ -z “${host}” ]; then host=`hostname -s`; fi
host=`echo $host | sed ‘s/\-/_/g’`
if [ -z “${tag}” ]; then LOGSUFFIX=`date +”%y%m%d%H%M”`; else LOGSUFFIX=${tag}; fi

psql < $YCSBHOME/inittable.sql

### To Load the workload data#####
${YCSBHOME}/bin/ycsb load jdbc -P ${YCSBHOME}/workloads/workloada -P ${YCSBHOME}/jdbc/src/main/conf/db.properties -p recordcount=1000000 -p operationcount=1000000 -p readallfields=true -p hotspotdatafraction=1 -p insertorder=ordered -threads 50

for th in 50 35 25 10
do

for wl in a b c d f
do

echo “Results for $th thread – workload $wl are … ” >> ${YCSBHOME}/logs/$th\_thread_$wl\_workload_logs.txt
echo ” Workload $wl with $th thread started … “

DTSTART=`date +”%y/%m/%d %H:%M:%S”`

${YCSBHOME}/bin/ycsb run jdbc -s -P ${YCSBHOME}/workloads/workload$wl -P ${YCSBHOME}/jdbc/src/main/conf/db.properties -p recordcount=1000000 -p operationcount=1000000 -p readallfields=true -p hotspotdatafraction=1 -p insertorder=ordered -threads $th >> ${YCSBHOME}/logs/ycsb-${host}-${wl}-run-${th}-${LOGSUFFIX}.log 2>&1

DTEND=`date +”%y/%m/%d %H:%M:%S”`

echo ” Workload $wl with $th thread completed … “
echo “ycsbrun,${host},${wl},${DTSTART},${DTEND},${LOGSUFFIX},${th}” >> ${YCSBHOME}/logs/ycsbtest.log

done
done

  • Ask Question
    • Tags: