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