Thursday, May 22, 2014

Pressure testing tool for Greenplum -- HAOTPS

HAOTPS is a handy pressure testing tool in JAVA written by me.
It was created for Oracle, but this version is only for Greenplum/PostgreSQL compatible.

Purpose:

Sometimes if you want to generate the same SQL load as customer's production environment with parallel clients running,
Or if you want to see what is the throughout of this box with parallel jobs running, this tool can help.

Input:

  • Connection string to target Greenplum/PostgreSQL;
  • How many threads(JAVA threads on client side, which also means how many parallel sessions on database side);
  • How many queries executed in each thread;
  • How many kinds of SQLs;
  • What is the percent for each SQL;
  • Bind variables(Optional, it is used to reduce SQL hard parse).

Output:

  • Average response time(ms);
  • Average TPS(Transactions executed per second).

Download:

haotps_public$ ls -altr
total 848
-rw-------   1 openkb  openkb    8317 May 22 15:06 config.class
-rw-------   1 openkb  openkb    1162 May 22 15:06 haosql.class
-rw-------   1 openkb  openkb    1376 May 22 15:06 haobind.class
-rw-------   1 openkb  openkb    5005 May 22 15:06 haotps.class
-rw-------   1 openkb  openkb  392094 May 22 15:06 postgresql-8.2-512.jdbc2.jar
-rw-------   1 openkb  openkb     160 May 22 15:07 haotps.profile
-rw-r--r--   1 openkb  openkb    2376 May 22 15:08 test.conf
-rw-r--r--   1 openkb  openkb     646 May 22 15:13 README.txt

Usage:

java haotps test.conf

Example:

  • 2 SQLs, each of them has 50% executions count.
  • Open 10 threads/sessions, each thread executes 100000 SQLs.
  • The bind variables are fetched and stored in memory to feed the 2 SQL.

Sample configuration file -- test.conf:

#########   Author: www.openkb.info
#########   When  : 2011-01
#########   This file is used to feed the HAOTPS java program
#########   beta2 version
#################################################################
#Connection String Part provide the connection string of target database
#################################################################
^^^Connection String Part^^^
#hostname can be the IP of the target host
$hostname = mdw
$port      = 1986
#sid is the database name we want to connect in the GP system
$sid       = test_database
#username and password are used to logon to the target database
$username  = openkb
$password  = openkb
#thread means how many client threads will be started
$thread    = 10
#query_cnt_per_thread means how many queries will be run in one single thread
$query_cnt_per_thread = 1000
#################################################################
#SQL part are the SQLs you want to use to generate the load on target
#################################################################
^^^SQL Part^^^
#Totally how many SQLs will be supplied in the file
$sql_count=2
--------------------------
#the percent of executions among all sqls(please remember that the sum of sql_percent should be 100!!!)
$sql_percent=50
#SQL_TEXT, as you know:)
$sql_text=
select count(*) from public.testbyopenkb
where id=?
#how many bind variables(please count for the "?" in above sql_text:))
$bind_variables_count=1
#bind number in "Bind Variables Sample Part":
$bind_1=1
--------------------------
$sql_percent=50
$sql_text=
select count(*) from public.testbyopenkb2
where name=?
$bind_variables_count=1
$bind_1=2
#################################################################
#Bind variables sample part will feed the bind var for above SQLs
#################################################################
^^^Bind Variables Sample Part^^^
--------------------------
#bind_number is the unique key of all the binds starting with 1 incremental by 1:
$bind_number=1
#bind_type is the data type, currently I only suppport int and string:
$bind_type=int
#bind_sample_sql is the SQL to fetch the sample data and store it in memory when HAOTPS is running:
$bind_sample_sql=
select id from public.testbyopenkb limit 1000
--------------------------
$bind_number=2
$bind_type=string
$bind_sample_sql=
select name from public.testbyopenkb2 limit 1000

Source env:

JAVA_HOME should be set correctly;
CLASSPATH should include the directory where "haotps" exists, and also the PostgreSQL JDBC driver.
In this example, we unzip haotps_public.zip in directory "/home/gpadmin/openkb/haotps".
$ cat haotps.profile
export JAVA_HOME=/usr/java/jdk1.6.0_43
export CLASSPATH=/home/gpadmin/openkb/haotps/postgresql-8.2-512.jdbc2.jar:$JAVA_HOME/jre/lib:/home/gpadmin/openkb/haotps

$ source haotps.profile

Execution:

$ cd /home/gpadmin/openkb/haotps

$ java haotps test.conf
======1.checking the config file test.conf ......
======2.checking constraint of the config ......
======3.collecting all the bind variable data ......
======4.running 10 threads, each thread with 1000 queries......
----------------Result--------------
Average response time(ms) =   965.677
Average TPS               =   9.87

No comments:

Post a Comment