DatabaseVerse: Journey into the World of Databases

Swingbench is a free load generator (and benchmarks) designed to stress test an Oracle database (12c, 18c, 19c, 21c, 23c). 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.

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

You can find more information here: swingbench

For the purpose of this demo I will use Linux.

1. Install JAVA
2. Download Swingbench
3. Swingbench – setting up
4. sbutil – Validation of schema creation
5. Running a workload
6. What about the formatting?
7. Conclusion

Install JAVA

The main requirement to install/use swingbench is to have Java 17 or later

As you can see above, I don’t have any specific java installation in my VM. Let’s install the latest one.

You can find the complete information about how to install JAVA here: Installing the JDK on Linux from Archive Files, Debian Packages, RPM Packages, and Oracle Linux Repositories

I have done the following:

sudo dnf install jdk-21-headless

java -version

My java is installed and looks good, let’s proceed with next step.

Download Swingbench

You can download swingbench from the official web: https://www.dominicgiles.com/downloads.html is simple as just downlead the zip file and unzip into the server you want to setup the tool.

Unzip the downloaded file:

Swingbench – setting up

Before running a test or benchmark, it’s necessary to create a schema and install a set of sample data using the wizards in the bin directory. These wizards can be run in command line or graphical modes, and there are four options 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 a 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

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

I’m not fan of using GUI to be honest, simple because most of the time many clients have a lot of restriction and you can’t simple setup VCN or any other tool to be able to start the GUI. Anyway you can find tons of info about using the GUI, in this post I will use the command line.

BTW, you can read all the options using -h flag:

For the purpose of this demo I will use the following parameters:

• -v: run in verbose mode when running from command line
• -cl: run in character mode
• -create: create benchmarks schema
• -u (username): for benchmark schema
• -p (password): for benchmark schema
• -scale (mulitiplier): for default config
• -tc: the number of threads(parallelism) used to generate data. Defaults to cpus*2
• -dba (username): dba username for schema creation
• -dbap (password): password for schema creation
• -ts: tablespace to create schema in
• -df: datafile name used to create schema in

I have to use -df since my demo database is running on filesystem and it is not using OMF.

./oewizard -v -cl -create -cs localhost:1521/NONCDB19C -u soe -p userPASS -scale 1 -tc 2 -dba "sys as sysdba" -dbap sysPASS -ts ts_swingbench -df xxx/swingbench01.dbf

sbutil – Validation of schema creation

If you got any issue (space issue, table/index creation) there is a tool called sbutil that you can use if you want to avoid doing the whole process again as I can take some time.

sbutil can help you 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:1521/NONCDB19C -soe -u soe -p userPASS -val

Running a workload

I have my schema build, now I can run a workload against it.

So, there are multiples ways, as I mentioned early I’m not going to use the GUI, so I need to use charbench

• 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 the following

When i tried to use swingbench:

Anyway, let’s use charbench:

In this scenario I will tell charbench to simulate 100 users

• -uc: override user count in configuration file.
• -max (milliseconds): override maximum intra transaction think time in configuration file
• -min (milliseconds): override minimum intra transaction think time in configuration file
• -intermax (milliseconds): override minimum inter transaction sleep time (default = 0)
• -intermin (milliseconds): override minimum inter transaction sleep time (default = 0)
• -mt (maxtrans): maximum tasks to be executed before terminating run
• -mr: produce a mini report of the results of a run
• -v: display run statistics (vmstat/sar like output), options include (comma separated no spaces).
  • trans|cpu|disk|dml|errs|tpm|tps|users|resp|
  • vresp|tottx|trem|pool

./charbench -c ../configs/SOE_Server_Side_V2.xml -u soe -p userPASS -uc 100 -cs localhost:1521/NONCDB19C -min 0 -max 10 -intermin 200 -intermax 500 -mt 5000 -mr -v users,tpm,tps,errs,vresp

Sample output:

What about the formatting?

Well, the information was generated in a XML file: results00001.xml

And maybe the output is structured but difficult to read without a proper tool, what can I do?

Fortunately, our dear friend Dominic has supplied another tool called results2pdf in the same bin/winbin directory. This allows you to convert the XML of a run into a pdf file.

To convert our file results00001.xml is simple as executing the following command:

./results2pdf -c results00001.xml -o resultstest1.pdf

and you can see a beautiful graph in the pdf:

Conclusion

We have successfully installed the required Java version for swingbench, downloaded and set up swingbench, created a schema for testing, and ran workloads against it using charbench. Additionally, we explored a tool called sbutil for schema validation and modification and converted the generated results into a PDF for clear visualization.

Swingbench is specially useful when you need to generate some stress in your database, then you can capture the information using Real Application Testing (RAT). Others scenarios are to test how your infrastructure is performing (CPU, I/O etc)

Stay tuned, as I have been using swingbench for others demo I’m working on it and you will find really useful.