Looking for something? Try here..

Thursday, February 4, 2016

Swingbench load generator

Swingbench is a free load generator (and benchmarks) designed to stress test an Oracle database (10g,11g,12c). This very useful tool is developed by Dominic Giles and all the details can be obtained from here.
In this post, I'll just show you the basics on how to use Swingbench for DB load generation.
I'm using Windows 10 as client machine and hence the path and file names will be of Windows. If you at any point of time has any clarifications you can always refer to the actual Swingbench site referred above and also comment your queries and I'll try to provide with the necessary information.

Now let's get into action.

Step 1:
Download the Swingbench software from here and extract the contents to a desired directory using unzip (on Unix) or winZIP or winRAR (on Windows).
Note: You should have JVM of version at least Java 6 or higher on the client machine.

Step 2:
Go to the winbin directory (in windows) or bin directory (in Unix) under swingbench root directory and start the swingbench.bat (double click). This will automatically start a command prompt with the following commands.

The Java graphical window for the Swingbench tool will open as similar to the below screenshot.

Swingbench utilizes Sales history (SH), Order Entry (OE) and Calling Circuit (CC) schema and its objects to perform the load tests. One should install these wizards in order to use them. If the sample schemas has been installed in the database, then this installation is not required. The schema can be installed by calling the shwizard.bat, oewizard.bat and ccwizard.bat. Each of the wizards has their own schema and objects with data created. 

So this should be the prerequisite necessary to be satisfied. I'll show how to create Order Entry Schema.

Step 0.a:

Note: I have only invoked the oewizard.bat executable which in turn calls the java executable (4th line) and also brings up the below GUI. 

Click Next --> Next --> Next --> 
The below screen appears.

Edit the details as per your environment. I'm using as below which connects to my ORCL database for schema and objects creation.
Click Next -->
Enter username (of your choice and use the same in testing/load generation), password and tablespace. If tablespace is already present, datafile will be automatically captured or provide the file name.

Click Next --> Change the partitioning and other options if desired
Click Next --> Enter sizing details of tablespace and data as desired
Click Next to arrive at the final configuration screen as below.

Click Finish to begin the installation.

Schema installation begins and log screen appears as below.

This take a few minutes as to build the OE schema. The following screen confirms completion of schema creation.

Now after schema creation, we can start the Swingbench which by default uses OE config xml for the load generation. swingbench.bat will invoke OE load generation as explained in the beginning of the post (step 2).

Step 3:
In case we would like to invoke load tests with SH schema we can call the SH configurations from the command line as below. Note: shwizard.bat has to be run prior to calling SH configuration (similar to Step 0.a) as schema has to be created for successful test (can be ignored if SH is installed already). 

I have only issued the following command which in turn invokes the java command and the CC GUI as below
swingbench.bat -c "E:\Program Files\swingbench\configs\shconfig.xml" -cs //192.168.1.99/orcl



Step 4:
See that I have already made the changes that i require as per my load generation (blue marker). You can change the settings as per your needs. 
Click on the start button (Green button) in the left top corner to start the load generation.
Let the tool do some operations in the database and generate load. Leave it for the amount of time you desire the load be generated. 
The screen while tool is running will look as below.

Once the load is started, the tool automatically shows the events page and the GUI starts giving the number of transactions that happens per minute and per second (blue marker). 
We can select different chart types, the above shown being the overview type and the others being 
1. Transactions per minute
2. Transaction response time
3. DML operations per minute

These charts are dynamically changeable. 

Step 5:
Once you decide to stop the load testing, you can stop the tool by clicking on the stop button (red button) near the grayed out start button at the left top corner.
Now all the sessions logged in will log out and the load generation will stop.

One most important feature of the Swingbench is that the ability to generate load using user defined stored procedures via the spconfig.xml config file. 
In order to achieve this, the storedprocedures.sql script under the <swingbench root>/sql directory should be run against the database which creates a Type, a package and a package body.
The user under which the package is created can be any user that you desire but should be granted execute on dbms_lock.
E:\Program Files\swingbench\sql>sqlplus sh/sh @storedprocedures.sql

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 4 23:47:19 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Type created.


Package created.


Package body created.

SQL> select object_name, object_type from user_objects where object_type in ('TYPE','PACKAGE','PACKAGE BODY');

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
INTEGER_RETURN_ARRAY           TYPE
SWINGBENCH                     PACKAGE
SWINGBENCH                     PACKAGE BODY

SQL>

The main content of the package body is the functions that are as below where the user changes are required to execute against the database objects. The are 6 functions in total that one can use by default.
..
..
  function storedprocedure1(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure1;
  function storedprocedure2(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure2;
  function storedprocedure3(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure3;
  function storedprocedure4(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure4;
  function storedprocedure5(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure5;
  function storedprocedure6(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure6;
..
..
Now I'm making the desired change in one of the function in the package body as below.
..
..
..
      return info_array;
  end storedprocedure2;
  function storedprocedure3(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      insert into demo values (trunc(dbms_random.value(1,10000)), dbms_random.string('u','10'));
      commit;
      increment_inserts(1);
      increment_commits(1);
      return info_array;
  end storedprocedure3;
  function storedprocedure4(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
..
..
..
I've made the function storedprocedure3 to execute the insert statement on to a user table and increment the number of inserts and commits (highlighted in the code). One can have any operation placed in the code to be executed and the load be performed by the tool.

Now to make use of the procedure, we now need to start the Swingbench with spconfig.xml config file as below.

See the command that I used is as below.

swingbench.bat -c "E:\Program Files\swingbench\configs\spconfig.xml" -cs //192.168.1.99/orcl

The following screen is invoked

See that the user name which you intent to run should be changed and the stored procedures that can be activated as per your needs. All the SPs here do nothing but only SP3 here in our test. If you wish not to activate the other functions, you can do so by checking the tick box.
Click on start to run the test and stop when desired (similar to step 4 and step 5). The screen when the tool is running looks as below. We can view the DML that is being performed.
Note that I have selected only the SP3 to be run against the database. 

This fairly ends up on how to use the Swingbench tool to load test your database. 

Some advantages and perks:
  • Swingbench can be run against a single node database and also against a RAC database.
  • Can be used to benchmark the scalability of a cluster database by invoking a coordinator and minibench utility to generate load on both the instances. Then the clusteroverview executable can be invoked to compare the scalability live.
  • With minibench and charbench, user has multiple option to load test according to their requirement. The details of these can be obtained from here
Happy load testing!!

No comments:

Post a Comment