Hitting Oracle with a Hammer

Recently, I had an opportunity to participate in a stress-testing exercise. By stress-testing, I mean simulating an expected peak load on the database and observing how the database performs. The objective is to make sure the particular hardware can handle the expected load and also to test where the physical limit of the machine lies if we keep increasing the number of concurrent users.

I searched for tools available out there. On forums, most people were talking about Hammerora, and although some people recommended high end tools for big bucks, I decided to try this free tool that promised to do exactly what I needed it to do. The open source Hammerora turned out to be excellent tool for the purpose of simulating a typical transactions load in any number of threads. It’s built with the Tcl scripting language and it can simulate a real workload with as many client user sessions as your system can handle.

So if you’re looking for an easy way to measure throughput performance of your database, keep reading.

Shopping list

1. Hammerora 2.2, a stress testing tool for MySQL, Oracle, and Web applications. You can download Hammerora for both Linux and Windows.
2. Activestate Tcl 8.5.5.0. You may find other distributions of Tcl 8.5 for your platform. I found this one easy to install and suitable for OEL/RHEL5. Download ActiveState Tcl.
3. Oratcl 4.4.: The Tcl-Oracle interface. Download oratcl.(Since Hammerora can also work on MySQL databases, you may want to adapt my steps to that. You’ll need mysqltcl, the Tcl-MySQL interface.)

A very nice thing about this setup—the bottom line: $0.

Installation

Start by installing the ActiveState Tcl and oratcl package:

1. After unpacking, install ActiveTcl 8.5.5.0, by running the install.sh script found in the main directory.The interactive text installer will guide you through the rest of installation. You can perform the installation as an oracle user, and choose the install directory.

   You may need to modify your path environment if you have another Tcl installation on the system:

   export PATH=/home/oracle/ActiveTcl-8.5/bin:$PATH

2. Unpack the oratcl package and install with following command:

   $ ./configure --with-tclconfig=/your_install_path/ActiveTcl-8.5/lib --with-tkconfig=/your_install_path/ActiveTcl-8.5/lib
   ...
   $ make install

3. The installation of Hammerora itself is pretty much self-explanatory, just allow execute on downloaded package and execute it. The whole process is best described on Hammerora’s installation web page, so I won’t go into more detail here.Note: The installation itself uses a GUI. It has a silent mode, but since the tool works best in GUI mode, you may need to enable vnc access to your server, or even choose to do the load-testing remotely from your desktop via TNS*Net (the network may be a bottleneck, however). The GUI has the significant advantage of allowing you to run individual sessions as threads, thus saving system resources.

After installation I faced a nasty problem. On my OEL 5.1, everything was working fine; on RHEL5, however, with a minimal installation, I struggled with annoying “Segmentation Fault” error when starting Hammerora.

[oracle@blackbox hammerora-2.2]$ ./hammerora.tcl
Segmentation fault

I tried tracing the issue with strace. Here’s the very tail of the trace:

[oracle@blackbox hammerora-2.2]$ strace -aefd -o strace_hammerora.log ./hammerora.tcl
[oracle@blackbox hammerora-2.2]$ tail strace_hammerora.log 
uname({sys="Linux", node="blackbox.pe.com", ...}) = 0
open("/home/oracle/.Xdefaults-blackbox.pe.com", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/oracle/.Xdefaults", O_RDONLY) = -1 ENOENT (No such file or directory)
uname({sys="Linux", node="blackbox.pe.com", ...}) = 0
open("/home/oracle/.Xdefaults-blackbox.pe.com", O_RDONLY) = -1 ENOENT (No such file or directory)
open("/home/oracle/.Xdefaults", O_RDONLY) = -1 ENOENT (No such file or directory)
uname({sys="Linux", node="blackbox.pe.com", ...}) = 0
open("/home/oracle/.Xdefaults-blackbox.pe.com", O_RDONLY) = -1 ENOENT (No such file or directory)
--- SIGSEGV (Segmentation fault) @ 0 (0) ---
+++ killed by SIGSEGV +++

That didn’t offer many clues, so I compared the trace files from the OEL system (where it worked fine) and the same trace from the RHEL system where the segmentation fault occurred.

Eventually, I found that it was caused by a missing X11 Type1 fonts package. The GUI tool simply expects some extra fonts, so make sure that you have installed following fonts packages.

[oracle@blackbox hammerora-2.2]$ rpm -qa | grep fonts
xorg-x11-fonts-base-7.1-2.1.el5
xorg-x11-fonts-Type1-7.1-2.1.el5

Alright, now we are able to start Hammerora:

[oracle@176801-db1 hammerora-2.2]$ ./hammerora.tcl

Creating a script to simulate your transaction

Before I started to work with Hammerora, I was afraid I’ll have to learn Tcl to be able to use it. Fortunately this hasn’t been the case, and I was able to find my way through using “common scripting sense”.

What you actually need is a raw SQL trace file with binds, which the tool can then convert into a Tcl script and “replay” it on the database.

To obtain the tracefile, I created a logon trigger which set event 10046 for any session that interested me. For example:

create or replace trigger logon_trigger
after logon on database
begin
if ( user = 'MY_USER' ) then
execute immediate
'alter session set events ''10046 trace name context forever, level 4''';
end if;
end;
/

Be careful—if there’s a load on the instance, at the moment you create the trigger it will immediately start generating trace files into USER_DUMP_DEST. If you want to better distinguish your files, you can also set a trace file identifier.

I enable the trigger and periodically checked the UDUMP tracefiles to see whether I had the desired sample. I searched all the generated files for interesting INSERT statements.

If you control your application, you can simply enable a 10046 event on your database session and perform just those steps you want to simulate. For setting trace in particular session you can use:

execute dbms_system.set_sql_trace_in_session(SID,SERIAL#,false);

Once I identified the right trace file, I loaded it into Hammerora and let the tool covert it to Tcl. To do that, just take the raw trace file and put it somewhere visible to Hammerora:

1. Click on FILE > Open > locate your *.trc file and open it.
2. The tracefile will get loaded into the main window. Now just click on the pencil-and-notepad icon that reads “Convert trace to TCL”.
3. The Tcl script is generated.

The next steps depend on what kind of load you want to generate. SELECT queries will likely get converted without any further work. On the other hand, UPDATE and INSERT queries might be a bit tricky.

I needed to simulate a complicated INSERT statement containing the DATE datatype. Having no prior knowledge of Tcl, I had to get creative.

The problem with the DATE datatype is that SQL trace does not record the DATE datatype bind variable value in readable form because it would lose its precision. Therefore, we’ll need to take care of all DATE columns by hand. You can probably define a date variable in Tcl and substitute it into right place. I did a workaround—I simply substituted SYSDATE for all the DATE bind variable values. This was fine for this case, as I didn’t have too care much about the actual value of the date.

If you see in your SQL trace file things like memory dumps instead of the bind variable value, it is most likely a DATE datatype and you’ll have to deal with it manually.

 Bind#0
  oacdty=01 mxl=128(63) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000010 frm=01 csi=871 siz=320 off=0
  kxsbbbfp=2af21da2a500  bln=128  avl=21  flg=05
  value="Conversion"
 Bind#1
  oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=871 siz=0 off=128
  kxsbbbfp=2af21da2a580  bln=11  avl=11  flg=01
  value=
Dump of memory from 0x00002AF21DA2A580 to 0x00002AF21DA2A58B
2AF21DA2A580 0A0C6C78 0E011D0C 004070D7           [xl.......p@.]
 Bind#2
  oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
  oacflg=03 fl2=1000000 frm=01 csi=871 siz=0 off=144
  kxsbbbfp=2af21da2a590  bln=22  avl=03  flg=01
  value=239

Once the Tcl script has been generated, you need to modify the connect string at the beginning of your script:

!/usr/local/bin/tclsh8.4
package require Oratcl
####UPDATE THE CONNECT STRING BELOW###
set connect scotch/vigor@DEV
...

If you think your script is ready, you can test it by clicking the Computer icon to select “Test Tcl code”.

This will create a single virtual user and attempt to execute the Tcl script you have created. If you have an INSERT statement, it will actually insert a row, so make sure you are not running this in a production database.

Unfortunately, the errors Hammerora gives you during the script troubleshooting are not too informative. For example, the one below tells you that thread with the specified identifier returned an ORA error.

Error in Thread tid00001270 - ORA-24338: statement handle not executed

It does’t tell you at which line of your script the error occurred. So I found it useful to enable output in Virtual Users definition -> Check the “Show output” check box. That way, you can at least see where your script stopped, or put some debugging comments into your script.

The goal is to get a green “check” next to your virtual user.

If your test was successful, you’re ready to configure your virtual users.

Configuring Hammerora for your database

There are several use cases for Hammerora. The first is benchmarking your database with TPC-C and TPC-H industry standard benchmarks. Hammerora can actually create the official schema and populate it with data. Then you can benchmark your database and compare it with industry data.

In my case, I will be using a custom schema, so I will completely ignore the TPC benchmarking options in this post. Hammerora can be configured from the GUI or from its configuration file, called config.xml, which is located in the top your Hammerora install directory.

The configuration is fairly easy, requiring you to know only the database connect string and some credentials. Basically, there are two things you need to configure:

1. Database credentials. We have already updated the script with the connect string in the previous section.
2. Create virtual users. There are a few options for your virtual users. Click on Virtual Users > Vuser Options, and add values for:
   • Virtual users -> in other words, the number of sessions
   • User Delay -> how long to wait before next session is created
   • Repeat Delay -> how long to wait before one session runs another iteration of the script
   • Iterations -> how many times each session should execute the script

   

If you want to have a visual display of transaction counts, you will also need a system password.

Under TX Counter > TX Counter Options, you can set your system credentials and refresh rate. Hammerora will then inform you about actual transaction throughput-per-second.

You can also run multiple instances of hammerora in master/slave mode, and modify scripts on slaves to diversify the load. Everything is nicely described in Hammerora’s documentation.

Hammer time

Now we’re ready for the simulation. I strongly suggest that you start incrementally. For example, if you want to test 100 users with 1000 iterations, start with 1 user and 1 iteration. If that works, try 100 users with single iteration, and if that proves to be working go with your target goal.

A quick run through the prerequisites:

1. You have set your credentials in the Tcl script (main window).
2. You have tested the Tcl script and it completed with a green check for your single user.
3. You have setup Virtual Users options.
4. You have set the system password for TX counter.
5. You have disabled the logon trigger (if you created one) on your target database (because this could put your system under a lot of stress).
6. You have set your statspack/awr to the desired sampling period.
7. You have enough free space to handle INSERTs.

Now you’re three clicks away from running your test.

1. click on create Virtual Users – persons icon.
2. click on test Run Hammerora loadtest – the tools icon
   
3. Enable your TX throughput display by clicking the red pen button.
   

And now, watch your database go crazy:

I hope you have enjoyed this. I’d love to hear what other method of stress-testing you may have used, and if the test results turned out to be relevant to a real life environment.