Swingbench

Swingbench is a free load generator (and benchmarks) designed to stress test an Oracle database (12c, 18c, 19c, 21c, 23c). It can be downloaded here. See my blog for new releases and what Im currently working on.

Swingbench 2.7 is the production release.

full

About SwingBench

Swingbench is a load generator and associated set of utilities. The software enables a load to be generated and the transactions/response times to be charted. The tool has both graphical and command line functionality.

Swingbench can be used to demonstrate and test technologies such as Real Application Clusters, Online table rebuilds, Standby databases, Online backup and recovery etc.

The code that ships with SwingBench includes 7 benchmarks, OrderEntry, SalesHistory, TPC-DS Like, TPC-H Like, JSON, Movie Stream and StressTest..

  • OrderEntry is based on the “oe” schema that ships with the Oracle Database. It has been modified so that Spatial, Intermedia schema’s do not need to be installed. It can be run continuously (that is until you run out of space). It introduces heavy contention on a small number of tables and is designed to stress interconnects and memory. It is installed using the “oewizard” located in the bin directory. Both a pure jdbc and PL/SQL (lower network overhead) variant exist of the benchmark.
  • SalesHistory is based on the “sh” schema that ships with Oracle 12c/Oracle 18c/Oracle 19c and is designed to test the performance of complicated queries when run against large tables. It is read only and can be scaled over a number of default sizes from 1GB to 1TB. A custom mode also allows for the creation of smaller and larger schemas
  • StressTest simply fires random inserts,updates,selects and updates against a well know table.
  • JSON Stresstest is based on simple JSON documents modelling people flying between airports. It follow a basic CRUD model
  • TPC-DS Like is a benchmark similar to TPC-DS. It features a complex analytical and transaction workload.
  • TPC-H Like is similar to the well known TPC_H benchmark. The queries used are of moderate query complexity.
  • MovieStream loosely simulates some of the transactions you might see on a moving streaming service.

The entire framework is developed in Java and as a result can be run on wide variety of platforms. It also provides a simple API to allow developers to build their own benchmarks.

Installation

To install Swingbench all you need to do is ensure you have Java 17 (unless otherwise stated) jdk or runtime in your executable path, and then unzip the swingbench zip file. Then change into the bin/winbin directory and run the files from there.

unzip swingbench

On Microsoft Windows use WinZip to perform this operation.

Setting Up

Before you can run a test/benchmark you need to create a schema and install a set of sample data. This is done via the wizards that are installed in the bin directory. They can be run in command line or graphical modes. There are four wizards to choose from.

  • oewizard : Installs a simple order entry schema that is used to create a heavy write workload
  • shwizard : Installs a simple star schema that is used to create an analytics workload
  • jsonwizard : Installs a simple JSON schema that is used to create a JSON CRUD workload
  • tpcdswizard : Installs a TPC-DS like schema that is used to create a complex analytics workload
  • tpchwizard : Installs a TPC-H like schema that is used to create a medium-complexity analytics workload
  • moviewizard : Installs a Movie Stream schema that features primarily OLTP like transactions but also uses AQ Sharded Queues to simulate events used in microservices.

To run any of the wizards either double click on one of the wizards described above if you are running on a GUI with a file manager. Or run it from the command line with

./oewizard

Or on Windows from the winbin directory

oewizard.bat

OEWizard

The wizard will walk you through the various options you can specify for the configuration of the database schema. Enabling you to define its size and what features of the Oracle Database you want to use or even how many threads you want to use in the data generation. On completion, it will create the user, tables, logic and generate the data directly into the database. After it completes generating the data it will then index and analyze the tables. Its final step will be to validate the schema to insure everything has been created correctly. If it hasn’t it might be possible to use the sbutil command line tool to rebuild certain aspects, such as the indexes or stored procedures.

You can also run the wizards from the command in a noninteractive mode where you specify all of the options. For example

./oewizard -cl -create -cs //localhost/PDB1 -u soe -p soepassword -scale 50 -tc 32 -dba "sys as sysdba" -dbap welcome1 -ts +DATA

The command above will run a wizard in command line mode setting username, password, connectstring, a scale of 50 (50GB), using 32 threads also setting the dba username, password and tablespace. For command line generation you will be expected to specify all of the parameters needed on the command line.

For a complete list of all of the many possible command line parameters for the wizards run the following command

./oewizard -h

Running a workload

After building a schema you should be in a position to run a workload against it. This is done by running the workload generator with one of three front ends.

Swingbench

  • swingbench : Is a graphically rich frontend that enables you to modify all of the parameters as well as run and monitor the workloads.
  • minibench : Is a simpler frontend that enables you to run workloads and monitor but parameters must be set in the command line or by editing the config file.
  • charbench : Is a command line only tool where all of the parameters are set on the command line and metrics like latency and throughput are written to standard out or a file.

To start one of the variants of the load generator simply run a command like the following

./swingbench -c ../configs/SOE_Server_Side_V2.xml

This will launch swingbench in graphical mode using the config file for the Order Entry workload. Unless additional command line pararmeters are specified swingbench will use the parameters held in the configuration file. If you don’t specify a configuration file from the command line swingbench will ask you for one. Note for minibench and charbench you must supply a configuration file or you’ll receive an error. Swingbench allows you to specify more complex command line options. For instance the command line below will launch swingbench overriding the connectstring, username, password, user connections, think time and the location and size of the window.

./swingbench -c ../configs/SOE_Server_Side_V2.xml -u soe -p soe -cs //localhost/orderentry -min 0 -max 10 -intermin 200 -intermax 500 -dim 1024,768 -pos 100,100 

Similarly if you are using charbench you use the command line parameters to configure a run. The command line below will run charbench and out useful metrics similar to the way vmstat runs

./charbench -c ../configs/SOE_Server_Side_V2.xml -u soe -p soe -uc 4 -cs //localhost/orderentry -min 0 -max 10 -intermin 200 -intermax 500 -v users,tpm,tps,errs,vresp
Author  :	 Dominic Giles
Version :	 2.6.0.1137

Results will be written to results.xml.
Hit Return to Terminate Run...

Time     Users       TPM      TPS     Errors   NCR   UCD   BP    OP    PO    BO    SQ    WQ    WA    
10:58:35 [0/4]       0        0       0        0     0     0     0     0     0     0     0     0     
10:58:37 [4/4]       0        0       0        0     0     248   414   0     0     0     0     0     
10:58:38 [4/4]       8        8       0        0     0     32    213   0     19    209   0     0     
10:58:39 [4/4]       17       9       0        0     19    39    33    0     10    198   0     0     
10:58:40 [4/4]       26       9       0        0     7     12    150   0     10    198   0     0     
10:58:41 [4/4]       37       11      0        93    7     10    109   0     10    198   0     0     
10:58:42 [4/4]       46       9       0        22    7     30    89    0     28    198   0     0

charbench has similar parameters to swingbench but with a few additions. Like swingbench to get a comprehensive list use the following command.

./charbench -h

By default swingbench outputs the results of a run in XML to the “Output” tab in the UI whilst charbench by default will output the results to a file called results.xml. You can change the location of the results file in charbench with the -r c0mmand line parameter.

NOTE: In the latest build of charbench now uses colour output to make it easier to highlight errors and parse the output. IT can be disabled with the -nc command line option.

Formatting the Results File

Whilst XML is simple to parse by a computer it is not necessarily ideal for humans. I supply a utility called results2pdf in the same bin/winbin directory. This allows you to convert the XML of a run into a pdf file. For example suppose you generated a XML files called results0001.xml to convert it into a pdf file called resultsrun1.pdf you would use a command similar to the own below

./results2pdf -c results0001.xml -o resultsrun1.pdf

This will convert some of the data held in the XML file into graphs and nicely formatted tables.

It’s also possible that you might want to compare a number of files and then render the results either as human readable tables or CSV files. For this reason I’ve included a python script in the utils directory. The choice of python was simply to simplify the modification of the script by a user to control what is is rendered. The script that is shipped will pull out the the key metrics as well as the displaying the average,10th,50th and 90th percentile… If multiple result files are supplied the script will display them next to one another. If you use the -c or --csv option it will output in comma seperated format making it easier to load into excel or Google Sheets.

python parse_results.py -r ../bin/results.xml ../bin/results00001.xml 
+-------------------------------------------+--------------------------+--------------------------+
| Attribute                                 |       results.xml        |     results00001.xml     |
+-------------------------------------------+--------------------------+--------------------------+
| Benchmark Name                            | "Order Entry (PLSQL) V2" | "Order Entry (PLSQL) V2" |
| Connect String                            |     //localhost/soe      |     //localhost/soe      |
| Time of run                               |   03-Oct-2020 16:49:11   |   03-Oct-2020 18:09:28   |
| Minimum Inter TX Think Time               |            0             |            0             |
| Maximum Inter TX Think Time               |            0             |            0             |
| Maximum Intra TX Think Time               |            0             |            0             |
| Maximum Intra TX Think Time               |            0             |            0             |
| No of Users                               |            4             |            4             |
| Total Run Time                            |         0:10:00          |         0:10:00          |
| Average Tx/Sec                            |          394.55          |          502.04          |
| Maximum Tx/Min                            |          30302           |          35015           |
| Total Completed Transactions              |          236727          |          301222          |
|                                           |                          |                          |
| Average Transaction Response Time         |                          |                          |
| Order Products                            |          16.78           |          12.82           |
| Warehouse Activity Query                  |           6.99           |           7.24           |
| Customer Registration                     |           9.39           |           7.16           |
| Update Customer Details                   |           3.83           |           3.21           |
| Sales Rep Query                           |           4.11           |           3.12           |
| Warehouse Query                           |           3.94           |           3.48           |
| Browse Products                           |           8.89           |           7.12           |
| Browse Orders                             |           6.32           |           5.29           |
| Process Orders                            |           7.37           |           5.87           |
|                                           |                          |                          |
| 10th Percentile Transaction Response Time |                          |                          |
| Order Products                            |           8.00           |           7.00           |
| Warehouse Activity Query                  |           3.00           |           4.00           |
| Customer Registration                     |           5.00           |           4.00           |
| Update Customer Details                   |           2.00           |           2.00           |
| Sales Rep Query                           |           1.00           |           1.00           |
| Warehouse Query                           |           2.00           |           2.00           |
| Browse Products                           |           3.00           |           3.00           |
| Browse Orders                             |           2.00           |           2.00           |
| Process Orders                            |           4.00           |           3.00           |
|                                           |                          |                          |
| 50th Percentile Transaction Response Time |                          |                          |
| Order Products                            |          13.00           |          11.00           |
| Warehouse Activity Query                  |           5.00           |           6.00           |
| Customer Registration                     |           7.00           |           5.00           |
| Update Customer Details                   |           2.00           |           2.00           |
| Sales Rep Query                           |           2.00           |           2.00           |
| Warehouse Query                           |           2.00           |           2.00           |
| Browse Products                           |           7.00           |           5.00           |
| Browse Orders                             |           4.00           |           4.00           |
| Process Orders                            |           5.00           |           4.00           |
|                                           |                          |                          |
| 90th Percentile Transaction Response Time |                          |                          |
| Order Products                            |          23.00           |          17.00           |
| Warehouse Activity Query                  |           9.00           |           9.00           |
| Customer Registration                     |          11.00           |           8.00           |
| Update Customer Details                   |           5.00           |           3.00           |
| Sales Rep Query                           |           6.00           |           4.00           |
| Warehouse Query                           |           5.00           |           4.00           |
| Browse Products                           |          13.00           |          10.00           |
| Browse Orders                             |           9.00           |           7.00           |
| Process Orders                            |           9.00           |           6.00           |
+-------------------------------------------+--------------------------+--------------------------+

Fixing problems with sbutil

It is possible that you may run into problems during the creation of the schema for any number of reasons i.e. run out of space, not enough temp space to create indexes etc. Since the creation of a schema using the waizards can take along time I’ve create a utility sbutil to solve many of the common issues as well as provide a tool to change the shape of the created schema by increasing it’s size, enabling compression or even partitioning the data. SBUtil is located in the bin directory. The following command validates a newly created schema

./sbutil -soe -cs //localhost/soe -soe -u soe -p soe -val
There appears to be an issue with the current Order Entry Schema. Please see below.
--------------------------------------------------
|Object Type    |     Valid|   Invalid|   Missing|
--------------------------------------------------
|Table          |        10|         0|         0|
|Index          |        26|         0|         0|
|Sequence       |         5|         0|         0|
|View           |         2|         0|         0|
|Code           |         0|         1|         0|
--------------------------------------------------
List of missing or invalid objects. 
Invalid Code : ORDERENTRY, 

This shows that there is a problem with the ORDERENTRY package. Instead of running a whole build again we could simply ask sbutil to reload the package.

$> ./sbutil -soe -cs //localhost/soe -soe -u soe -p soe -code
Reloading PL/SQL Packages
Reloaded PL/SQL Package. Completed in : 0:00:00.273
$> ./sbutil -soe -cs //localhost/soe -soe -u soe -p soe -val
The Order Entry Schema appears to be valid.
--------------------------------------------------
|Object Type    |     Valid|   Invalid|   Missing|
--------------------------------------------------
|Table          |        10|         0|         0|
|Index          |        26|         0|         0|
|Sequence       |         5|         0|         0|
|View           |         2|         0|         0|
|Code           |         1|         0|         0|
--------------------------------------------------

You can also use the sbutil script to show the contents of a schema i.e.

./sbutil -soe -cs //localhost/soe -soe -u soe -p soe -tables
Order Entry Schemas Tables
----------------------------------------------------------------------------------------------------------------------
|Table Name                  |                Rows|              Blocks|           Size|   Compressed?|  Partitioned?|
----------------------------------------------------------------------------------------------------------------------
|INVENTORIES                 |             899,129|              22,343|        176.0MB|      Disabled|            No|
|JUST_A_TABLE                |           1,000,000|              18,901|        149.0MB|      Disabled|            No|
|ORDER_ITEMS                 |             806,135|               6,424|         70.0MB|      Disabled|            No|
|ORDERS                      |             173,095|               2,392|         32.0MB|      Disabled|            No|
|CUSTOMERS                   |             106,470|               1,762|         21.0MB|      Disabled|            No|
|ADDRESSES                   |             150,000|               1,762|         19.0MB|      Disabled|            No|
|LOGON                       |             278,392|               1,006|         12.0MB|      Disabled|            No|
|CARD_DETAILS                |             150,000|               1,006|         11.0MB|      Disabled|            No|
|PRODUCT_DESCRIPTIONS        |               1,000|                 124|         1024KB|      Disabled|            No|
|PRODUCT_INFORMATION         |               1,000|                 124|         1024KB|      Disabled|            No|
|ORDERENTRY_METADATA         |                   4|                 124|         1024KB|      Disabled|            No|
|WAREHOUSES                  |               1,000|                 124|         1024KB|      Disabled|            No|
|JUST_A_SMALL_TABLE          |              20,000|                 124|         1024KB|      Disabled|            No|
----------------------------------------------------------------------------------------------------------------------
                                                            Total Space         495.0MB

For a full break down of the possible commands use the -h command line option.