Qants – Tips in the field of Java Performance & Software Testing

Most of you performance testers, newbies or experienced, you all came across this problem … how to export the load test results, and how to analyse them.

While working for a project in Viena, several years ago, Excel looked to be the best option, because of the relative powerful charts, functions, and code (VBA) were offering.  Back then, i was still using OpenSTA, which was at the time, the best open source loading tool on the market. (still is a very good option, depending on the needs) As usual, each Performance Tool has (or not) its integrated Graphs for reporting the test results, yet most of the time unsuited for the needs of the performance tester. So exporting to excel was the best option back then. And i used it.

Exporting was quite easy, because of the CSV export functionality OpenSTA was offering, and importing…well there is no point in continuing the discussion, as Excel has really powerful functions for importing and exporting all kinds of formats (starting with csv, ending with xml)

Remembering that project, and being in quite a “time crisis” for the current project, i took the decision to turn once more to the old Excel, this time using JMeter sample records.

Now, a few words about how JMeter exports it’s samples. By default, JMeter will save between other samplers:

• timestamp of the http request
• response time
• size
• response code
• response message
• request name

This is actually pretty much what you should be interested in in a very basic scenario. You want to measure some averages, and see some graphs.

Now, basically, the concept behind measuring the response times, is grouping the request names with their response times, and measuring the averate response times. Suppose you have four requests, two of them doing book, the other ones doing login and logout

Using one thread, one user, and generating the following samples(request name, response time in ms):

• login: 240
• book:450
• book:650
• logout:200

you would have an average of (450+650)/2=550 ms for the “booking” request.

Using this concept, we can group requests in Excel by using Pivot Tables. Let’s go through the steps for doing that:

1. Import JMeter results into EXCEL

   JMeter (version 2.3.4) can export in multiple formats, with xml being the native one, and csv being optional. I chose to export into XML (it offers more configurable options when exporting to xml and not csv). As i previously said, EXCEL can handle XML Import pretty well, so we will use that
   Using Excel 2007, go to “Data”. You Have a “Connections” icon in the toolbar. Click on it. Add a new connection from the window that just opened. Don’t get annoyed by the window that just opened, just select “search for other elements” from the buttons below, and then navigate to your xml file.
   Import everything, and then keep the following columns only (delete other columns, or hide them)

   • t (response time)
   • ts (timestamp)
   • request name

2. Prepare your data for pivot tables

   The problem with JMeter is that it exports its timestamp in UNIX Format. You will need therefore to create an extra column, called “Timeline”, and use the following formula for converting data into date format (we will only use hours, minutes and seconds. The grouping of all samples will be made with the second being the smallest unit. It really does not make sense granulating it all over to milliseconds):
   =DATE(1970;1;1)+LINKS(B2;10)/86400
   where B2 is the column containing the JMeter timestamps.
   After this step, select the new column (timeline), and define its properties so that it returns only the hours, minutes and seconds. Click on the column, select the properties, and go to the last option in the first tab: “custom”. Select the following format: “hh:mm:ss”
   Ok, now you have your requests, your response times, and your timelines in readable format. On we go to the next step: Pivot Tables

3. Generate Pivot Tables from JMeter Results

   Select all the columns in the worksheet. Go to “Table Tools” and select “Group with Pivot Tables”. Use a new worksheet for the Pivot Table. After Excel generates the new worksheet and the Pivot Table, you will have the columns available to select in the right side of the screen. (pivot table field list) Now do the following:

   • Drag and drop field “Request Name” to the legends (there are four boxes down there, use the upper right one)
   • Drag and drop field “Timeline” to the “Axis fields” (lower left)
   • Drag and drop “t” (or “Response Time” column) to the “values” field (lower right)
   • Select the “t” field in the lower right area, click on it, and modify the properties, so that the “Field Settings” point to “Average” instead of “Sum”
   • Now you can generate a “Chart” from the “Pivot Table Tools” > “Pivot chart”
   • In the pivot table, go to the  end of the rows. You have a row called “Totals”. Modify the field properties for each of the columns so that it calculates the average, and not the sum. Generating a simple chart out of this values is not the purpose of this article and should be REAAALY dumb simple

   There you have it. All you need to generate some graphs in EXCEL using JMETER Samples. The final results look pretty much like this:

4. Pivot Charts from JMETER Results 

   

   JMeter Average Response Times for 4 Samplers

   

   Average Response Times Totals

5. Final words

   I strongly recommend using Jasper Reports for Reporting. Jasper Reports is incredibly strong, and in combination with a Jasper Report Server, it will do wonders. This is going to be one of my next topics.
   JMeter Reporting by using Pentaho Data Integration, IReport, JasperReports, MySQL and Jasper Report Server.

Cheers and enjoy,

Alex