About
I've been working as a performance engineer for a number of years. I started my PE career with JMeter, spent lots of time with HP LoadRunner and culminated with k6. The most annoying of the three was HP LR. Mostly, because it is very rigid (at least from a user's perspective), only runs on Windows and can't be talked into doing anything the OSS way. Our team was lean, fast and innovative, so it was only a matter of time before LR had to either budge and do it my way, or hit the highway.
I won.
Struggles
HP LR is a massive, very powerful tool with proper customer support and quite a large community. However, it's married to MS Windows. If you need anything more than the defaults it can provide, you have to open results' files with the HP LR tool, creatively (/s) called "Analysis". And it only works on MS Windows. Want to integrate performance testing in your CI? Not happening! Unless you are willing to run a dedicated Windows server with Analysis installed in it, acting as a SPOF, chomping on your resources on free time.
I said NO
Running a Windows machine 24/7, or even worse - spinning it up on demand and hoping it will start up correctly before the CI times out - was not an option for me. Not to mention licencing nuances I'd have to address/request in the corp. I worked for. I needed a lean, simple and easily maintainable approach. I searched for hints in the HPLR community forums, asked others for assistance, but the answers I got either were along the lines of "forget it" or "use a windows server for that - Analysis has a CLI and <... I stopped reading at that point>".
Discovery
I decided to have a darn good look at the resources I have. My main question was: is the HPLR results' file proprietary or can I perhaps open it somehow and extract what I need from it myself? I downloaded all the run files and analyzed them closely. One of the zip archives contained a particularly big file with an extension: .mdb. A quick google search told me that's an Access DB file. Now, how do I access it with Java....? Then I found the ucanaccess JDBC driver.
I added 2 + 2 and a potential solution was born in my head.
Struggles, again
The .mdb file structure is not well docummented, so it took quite some time to understand what's where. The "meat" is in the table event_meter, but its structure is not immediatelly obvious. I struggled with the acount concept (to be fair, I no longer remember what it means, but I know it's something about data aggregation - perhaps how many such events happened in that single second? IDK). A few weeks later, the dragon was tamed and the queries were born.
Queries
https://gitlab.com/-/snippets/2575521
run_details
This query returns a run summary:
+--------+---------------+-------------+-----------------+---------------+----------+----------+-------------+-----+-----------+
|TOTAL_VU|VU_RAMPUP_START|VU_RAMPUP_END|VU_RAMPDOWN_START|VU_RAMPDOWN_END|test_start|test_end |test_duration|tz |result_name|
+--------+---------------+-------------+-----------------+---------------+----------+----------+-------------+-----+-----------+
|25000 |82 |294 |948 |1327 |1690377335|1690378663|1328 |28800|res9694.lrr|
+--------+---------------+-------------+-----------------+---------------+----------+----------+-------------+-----+-----------+
with run_details(test_start, test_end, test_duration, tz, result_name)
as (select (result.[start time]) as test_start,
(result.[result end time]) as test_end,
(result.[result end time] - result.[start time]) as test_duration,
(result.[time zone]) as tz,
(result.[result name]) as result_name
from result),
vuser_events(inout_flag, status_name, time)
as (SELECT (events.[inout flag]) as inout_flag,
(status.[vuser status name]) as status_name,
(events.[end time]) as time
FROM vuserevent_meter events
JOIN vuserstatus status ON (events.[vuser status id]) = (status.[vuser status id])
order by time asc)
select (select sum(inout_flag) from vuser_events where status_name = 'RUN' and inout_flag > 0) as total_vu,
(select min(time) from vuser_events where status_name = 'RUN' and inout_flag > 0) as vu_rampup_start,
(select max(time) from vuser_events where status_name = 'RUN' and inout_flag > 0) as vu_rampup_end,
(select min(time) from vuser_events where status_name = 'RUN' and inout_flag < 0) as vu_rampdown_start,
(select max(time) from vuser_events where status_name = 'RUN' and inout_flag < 0) as vu_rampdown_end,
details.test_start as test_start,
details.test_end as test_end,
details.test_duration as test_duration,
details.tz as tz,
details.result_name as result_name
from run_details details;
run_errors
This query returns test run errors linked to scripts, actions, VU ID and with a test run window offset (sort of a timestamp)
+--------+------------+-----+-------+---------+-------------+-----------------+------------+---------------------------------------------------------------------------------+
|END_TIME|EVENT_NAME |VU_ID|LINE_NO|ITERATION|SCRIPT_NAME |ACTION_NAME |LG_NAME |MESSAGE |
+--------+------------+-----+-------+---------+-------------+-----------------+------------+---------------------------------------------------------------------------------+
|84.365 |Error -26366|47 |23 |1 |01_MyScript_1|Create_Cart_Items|srvhplrlg001|Create_Cart_Items.c(23) Error -26366 "Text="cart_id1"" not found for web_reg_find|
+--------+------------+-----+-------+---------+-------------+-----------------+------------+---------------------------------------------------------------------------------+
<........>
with test_script(id, name) as (select
[script id] as id,
[script name] as name
from Script),
event(id, type, name) as (select
[event id] as id,
[event type] as type,
[event name] as name
from event_map),
err(event_id, end_time, error_id, vu_id, host_id, script_id, line_no, action_id, iteration) as (select
[event id] as event_id,
[end time] as end_time,
[error id] as error_id,
[vuser id] as vu_id,
[host id] as host_id,
[script id] as script_id,
[line number] as line_no,
[action id] as action_id,
[iteration number] as iteration
from Error_meter),
error(id, message) as (select
[error id] as id,
[error message] as message
from errormessage),
action(id, name) as (select
[action id] as id,
[action name] as name
from ScriptActions),
lg(id, name) as (select
[host id] as id,
[host name] as name
from host),
sorted_errors(end_time, event_name, vu_id, line_no, iteration, script_name, action_name, lg_name, message)
as (select err.end_time,
event.name as event_name,
err.vu_id,
err.line_no,
err.iteration,
test_script.name as script_name,
action.name as action_name,
lg.name as lg_name,
error.message
from err
join error on err.error_id = error.id
left join test_script on err.script_id = test_script.id
left join action on err.action_id = action.id
left join lg on err.host_id = lg.id
join event on err.event_id = event.id
order by end_time, vu_id asc)
select *
from sorted_errors;
run_results
This is a big one. It's the core query my automation revolves around. It summarizes test results for each Action (transaction) and makes it rather easy to pluck those numbers out of the database and use them in my report. It also becomes quite easy to compare runs with each other.
NOTE BENE this query is memory-hungry and I ended up alocating 8GB to the alpine containers running it. I guess your memory requirements will depend on how long your tests run for and how many scripts/actions you have.
+-------------------------------+------------+------------+-----------+------------+------------+-----------+-------+----------+----------+----------+----------------+----------------+
|TRANSACTION_NAME |PASSED_COUNT|FIRST_PASSED|LAST_PASSED|FAILED_COUNT|FIRST_FAILED|LAST_FAILED|AVG_TPS|MAXIMUM_RT|MINIMUM_RT|AVERAGE_RT|RT_PERCENTILE_90|RT_PERCENTILE_50|
+-------------------------------+------------+------------+-----------+------------+------------+-----------+-------+----------+----------+----------+----------------+----------------+
|#TOTAL |616917 |81 |198 |2963 |84 |198 |3427 |29.829 |0.026 |1.059 |0 |0 |
|01_MyScript_1_Create_Cart_Items|3403 |81 |198 |283 |84 |198 |18 |23.546 |0.337 |4.082 |10.5 |2.559 |
+-------------------------------+------------+------------+-----------+------------+------------+-----------+-------+----------+----------+----------+----------------+----------------+
<........>
NOTE in the lines 3 and 4 set the correct timeframe to generate the report for. Values are seconds from the start of the test.
with tally as (select rownum() as num from event_meter),
test_configuration(timeframe_start, timeframe_end)
as (select %d as timeframe_start,
%d as timeframe_end
from dual),
run_details(test_start, test_end, test_duration, tz, result_name)
as (select (result.[start time]) as test_start,
(result.[result end time]) as test_end,
(result.[time zone]) as tz,
(result.[result name]) as result_name,
(result.[result end time] - result.[start time]) as test_duration
from result),
all_events (event_id, value, acount, end_time, event_name, event_type, status1, describe_id)
as (select (data_raw.[event instance id]) as event_id,
data_raw.value as value,
data_raw.acount as acount,
(data_raw.[end time]) as end_time,
(event.[event name]) as event_name,
(event.[event type]) as event_type,
data_raw.status1 as status1,
(event.[describe id]) as describe_id
from event_meter data_raw
join event_map event
on (event.[event id]) = (data_raw.[event id])
order by value asc),
all_events_steady (event_id, value, acount, end_time, event_name, event_type, status1, describe_id)
as (select event_id as event_id,
value as value,
acount as acount,
end_time as end_time,
event_name as event_name,
event_type as event_type,
status1 as status1,
describe_id as describe_id
from all_events
where end_time >= (select timeframe_start from test_configuration)
and end_time <= (select timeframe_end from test_configuration)),
passed_events (event_id, value, acount, end_time, event_name, event_type, status1, describe_id, tx_status)
as (select data_all_passed.event_id,
data_all_passed.value,
data_all_passed.acount,
data_all_passed.end_time,
data_all_passed.event_name,
data_all_passed.event_type,
data_all_passed.status1,
data_all_passed.describe_id,
(tx_status.[transaction end status]) as tx_status
from all_events_steady data_all_passed
JOIN transactionendstatus tx_status
ON data_all_passed.status1 = tx_status.status1
left join tally on tally.num <= data_all_passed.acount
where data_all_passed.event_type = 'Transaction'
AND (tx_status.[transaction end status]) = 'Pass'
ORDER BY data_all_passed.value ASC),
failed_events(event_id, value, acount, end_time, event_name, event_type, status1, describe_id, tx_status)
as (select data_all_failed.event_id,
data_all_failed.value,
data_all_failed.acount,
data_all_failed.end_time,
data_all_failed.event_name,
data_all_failed.event_type,
data_all_failed.status1,
data_all_failed.describe_id,
(tx_status.[transaction end status]) as tx_status
from all_events_steady data_all_failed
JOIN transactionendstatus tx_status
ON data_all_failed.status1 = tx_status.status1
left join tally on tally.num <= data_all_failed.acount
where data_all_failed.event_type = 'Transaction'
AND (tx_status.[transaction end status]) = 'Fail'),
grouped_events (event_name,
event_count, first_event, last_event,
value_max, value_min, value_avg,
pctl_id_98, pctl_id_95, pctl_id_90, pctl_id_50) as (select data_passed.event_name as event_name,
count(data_passed.value) as event_count,
min(data_passed.end_time) as first_event,
max(data_passed.end_time) as last_event,
max(data_passed.value) as value_max,
min(data_passed.value) as value_min,
avg(data_passed.value) as value_avg,
(count(data_passed.value) * 98 / 100) as pctl_id_98,
(count(data_passed.value) * 95 / 100) as pctl_id_95,
(count(data_passed.value) * 90 / 100) as pctl_id_90,
(count(data_passed.value) * 50 / 100) as pctl_id_50
from passed_events data_passed
GROUP BY data_passed.describe_id,
data_passed.event_name),
summary_table(transaction_name,
passed_count, first_passed, last_passed,
failed_count, first_failed, last_failed,
avg_tps,
maximum_rt, minimum_rt, average_rt,
rt_percentile_90_id, rt_percentile_90,
rt_percentile_50_id, rt_percentile_50) as (select grouped.event_name as transaction_name,
grouped.event_count as passed_count,
grouped.first_event as first_passed,
grouped.last_event as last_passed,
(select count(failed.acount)
from failed_events failed
where failed.event_name = grouped.event_name) as failed_count,
(select min(failed.end_time)
from failed_events failed
where failed.event_name = grouped.event_name) as first_failed,
(select max(failed.end_time)
from failed_events failed
where failed.event_name = grouped.event_name) as last_failed,
(grouped.event_count /
(select timeframe_end - timeframe_start
from test_configuration)
) as avg_tps,
grouped.value_max as maximum_rt,
grouped.value_min as minimum_rt,
grouped.value_avg as average_rt,
grouped.pctl_id_90 as rt_percentile_90_id,
(select rows_numbered.value
from (select sorted_by_value.value, rownum() as rownum
from passed_events sorted_by_value
where sorted_by_value.event_name = grouped.event_name) as rows_numbered
where rows_numbered.rownum = grouped.pctl_id_90 + 1) as rt_percentile_90,
grouped.pctl_id_50 as rt_percentile_50_id,
(select rows_numbered.value
from (select sorted_by_value.value, rownum() as rownum
from passed_events sorted_by_value
where sorted_by_value.event_name = grouped.event_name) as rows_numbered
where rows_numbered.rownum = grouped.pctl_id_50 + 1) as rt_percentile_50
from grouped_events grouped)
select transaction_name transaction_name,
passed_count passed_count,
first_passed first_passed,
last_passed last_passed,
failed_count failed_count,
first_failed first_failed,
last_failed last_failed,
round(avg_tps, 3) avg_tps,
round(maximum_rt, 3) maximum_rt,
round(minimum_rt, 3) minimum_rt,
round(average_rt, 3) average_rt,
round(rt_percentile_90, 3) rt_percentile_90,
round(rt_percentile_50, 3) rt_percentile_50
from summary_table
UNION
select '#TOTAL' as transaction_name,
count(all_passed.value) as passed_count,
min(all_passed.end_time) as first_passed,
max(all_passed.end_time) as last_passed,
(select count(failed.acount) from failed_events failed) as failed_count,
(select min(failed.end_time) from failed_events failed) as first_failed,
(select max(failed.end_time) from failed_events failed) as last_failed,
round(count(all_passed.acount) / (select timeframe_end - timeframe_start
from test_configuration), 3) as avg_tps,
round(max(all_passed.value), 3) as maximum_rt,
round(min(all_passed.value), 3) as minimum_rt,
round(avg(all_passed.value), 3) as average_rt,
0.0 as rt_percentile_90,
0.0 as rt_percentile_50
from passed_events all_passed
ORDER BY transaction_name
;
There. Have a blast!
One caveat: MS Access doesn't seem to like CTEs in my queries, but ucanaccess deals with them just fine.
Top comments (0)