Oracle stress testing using Apache JMeter

When looking for an Oracle stress test tool to do performance measurements, one of the first tools to popup is Domic Giles Swingbench tool. Now there is absolutely nothing wrong with Swingbench, it has helped me lots in the past but the last view years i have used Apache JMeter as a stress test tool for non-oracle data platforms. JMeter is extremely configurable stress testing platform that not only supports JDBC calls but a whole variety of other tests too like HTTP(S), Java, TCP, IMAP/POP, OS commands etc. It also has a whole range of build-in functions and variables that makes it easy to script and scale-out. JMeter also supports distributed testing, meaning that you have one command JMeter, controlling multiple other clients that can connect to your application.

JMeter exists of a couple of building blocks that together will make your stress test. All these building blocks can be used in a modulair fashion, making your stress test flexible in setup. At the root level there is the Test Plan, you can add one or more of the following components to this plan:

  • Thread Groups: Basically the beginning of a stress test, this is where you define how many threads (users) your stress will startup, the startup delay between the tests, how often it will loop through the tests etc.
  • Config Elements: This is where you define how samplers are configured. In our Oracle RDBMS example it is the place where we configure JDBC.
  • Samplers: The units that do the actual, in our case this is where we define what call we are going to make over JDBC.
  • Controllers: Allows us to control the flow on a test, for example the onlyonce controller lets us run a test only once instead of the loop number. Controllers also gives us the possibilities to randomness, loops etc.
  • Timers: Does what it appears to be doing, making delays in a loop before we go on to a new element
  • Assertions: Allow you to assert facts about responses received from the server being tested.
  • Pre/post processors: Gives you the possibility to prepare an environment and clean it up afterwards.
  • Listeners: Displays the data that you get back from a test. Listeners can be Graphing listeners, debugging, Summary Reports etc.

Installing JMeter is easy, go to the download page and download the most recent (version 5.1.1 at the time of writing). Unpack it, go to the /bin directory and start JMeter:

 klaasjan@exabook > ~/bin/apache-jmeter-5.1.1/lib > ../bin/jmeter
WARNING: package sun.awt.X11 not in java.desktop
================================================================================
Don't use GUI mode for load testing !, only for Test creation and Test debugging.
For load testing, use CLI Mode (was NON GUI):
   jmeter -n -t [jmx file] -l [results file] -e -o [Path to web report folder]
& increase Java Heap to meet your test requirements:
   Modify current env variable HEAP="-Xms1g -Xmx1g -XX:MaxMetaspaceSize=256m" in the jmeter batch file
Check : https://jmeter.apache.org/usermanual/best-practices.html
================================================================================
Warning: Nashorn engine is planned to be removed from a future JDK release

Now we have JMeter up-and-runnign and we can start building our custom Oracle stress test. In this example in am running JMeter on OSX and have a vagrant box with a single instance oracle database. Vagrant has port 1521 exposed to OSX so i can connect to Oracle at localhost:1521. We also need the Oracle JDBC driver jar file on our machine. I am using the Oracle Instant Client for this example.

First step is to tell our Test Plan where our Oracle JDBC jar file is located, this can be done at the bottom of the Test Plan screen bu extending the class path of JMeter:

Add Oracle JDBC Jar to JMeter class path

Because all tests run under at thread group, this is the first item we need to create.

I have configured mine to use 50 users, with a wait of 10 seconds after each session starts. I am looping through the test a 100 times.

Now we have our threads configured we need to tell JMeter what and how we are going to test. In our case it will be over a JDBC connection, so we first need to a Config Element that configures our JDBC connection

On the JDBC configuration page we need to configure a few things, for new set “Variable Name for created pool” to something like “oracle_db”. This is a variable that we need reference later on so we know what our JDBC configuration is. At the bottom make sure that you fill in your JDBC connection URL to your database and set the JDBC driver class to oracle.jdbc.OracleDriver. Finally set the username/password you want to use.

The last step is add a JDBC sampler by right clicking on the Thread Group and go to add -> Sampler -> JDBC Requests. At the line “Variable Name of Pool declared in JDBC Connection Configuration”, fill in the variable name that you have given it, in my example oracle_db. Leave the query type as it is for now and add a simple query, something like select count(*) from dual. Don’t add a trailing semi-colon add the end of the query, that is not needed and if you do, your tests will fail.

Now finally we need to add a listener so we can actually see some results. Do this by right clicking on Test Plan, then add -> listener -> Summary Report.

We can now run this very basic JMeter test. Now click on the play button and wait for the results to come in.

Now doing a very simple test as this might not be very interesting but as soon as you start expanding your tests it becomes more interesting. When making a bigger JMeter test like this, which is a bit more complex you can see the features and power of JMeter. You can download the linked Gist file and open it in JMeter to watch it. This test will do the following steps:

  • Create a tables jmeter_1 until jmeter_n, where n is the amount of threads configured.
  • Each thread will then loop through its own table doing
    • an insert append of 1000 records into that table
    • a select count(*) on that table
  • Jmeter then will delete all the jmeter_n tables.
  • Create graphs and reports of your test

For setting up, removing my test tables i am using the setup and teardown Thread groups in this example. I also want to have the number of threads the same in all thread groups so i have all the tables for my test and are also removing them. I have created a variable in JMeter so i can set the number of threads in all Thread groups. Under TestPlan.user_defined_variables you can see all configured variables for this test.

  <elementProp name="threads" elementType="Argument">
    <stringProp name="Argument.name">threads</stringProp>
    <stringProp name="Argument.value">${__P(threads,1)}</stringProp>
    <stringProp name="Argument.metadata">=</stringProp>
  </elementProp>

At line 2 there is a variable defined called threads, i am going to use this to reference this in the configuration of all thread groups. You can see on line 3 that this variable has a value called ${__P(threads,1)}, this means that the thread variable is defined by a build in function __P. With this function i can define the variable of threads through the command line. It has 2 parameters, the first one being the name referenced from the command line. In this example we can set it with -Jthreads=N. The 2nd variable is the default value, if it is not being called from the command line, use 1 as a default. I can now set the number of threads with this variable in every thread group by giving it the value ${threads}

<stringProp name="ThreadGroup.num_threads">${threads}</stringProp>

We have now set the number of threads for creating and removing the tables as well as running the tests the same over all thread groups. Next step is to make sure that every thread is using its own table. JMeter has a build-in variable that keeps track of running thread, we can use that in our create table statement:

<stringProp name="ThreadGroup.num_threads">${threads}</stringProp>

By referencing ${__threadNum} in the create table statement, JMeter will create a table called jmeter_n where n=threadNum. If we set ${threads} to 50 then every thread will create its own table. The teardown thread will do the same after the test is done but then with a drop table statement.

We can then use the same ${__threadNum} variable in our test code, for example in a PL/SQL block

<JDBCSampler guiclass="TestBeanGUI" testclass="JDBCSampler" testname="JDBC: Fill table" enabled="true">
  <stringProp name="dataSource">oracle_db</stringProp>
  <stringProp name="query">declare
sql_stmt    varchar(200);
begin
for i in 1..1000 loop
sql_stmt := &apos;insert /*+ append_values */ into jmeter_${__threadNum} select lpad( &apos;&apos;x&apos;&apos;,&apos;||i||&apos;,&apos;&apos;x&apos;&apos;) from dual&apos;;
execute immediate sql_stmt;
commit;
end loop;
end;</stringProp>
  <stringProp name="queryArguments"></stringProp>
  <stringProp name="queryArgumentsTypes"></stringProp>
  <stringProp name="queryTimeout"></stringProp>
  <stringProp name="queryType">Callable Statement</stringProp>
  <stringProp name="resultSetHandler">Store as String</stringProp>
  <stringProp name="resultVariable"></stringProp>
  <stringProp name="variableNames"></stringProp>
</JDBCSampler>

Every thread will stay in its own table for the inserts as well as the selects. Also, at line 15 in the fragment above, we must set the queryType to “Callable Statement” for PL/SQL to function properly.

We can now run this test from the command line:

../bin/apache-jmeter-5.1.1/bin/jmeter -n -t db_test.jmx -o reports -l results.txt -Jthreads=50 -Jloop=10

We have now started a test that will span 50 threads, looping through the test 10 times, write the results of the tests to results.txt and create a HTML based report in the reports directory for further analysis.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s