More GrabPERF Enhancements

The newest addition to GrabPERF is the dynamic calculation of Success Rate values for the Top/Bottom 30 (up from 20) tables. This has also been extended to the site pages.

Although this may seem relatively simple, you must remember that I am neither a coder nor a SQL whiz. But last week, a colleague demonstrated how to use CASE statements in SQL queries and all of the pieces snapped together.

The sheer volume of information analyzed in the Top/Bottom 30 tables leads to the following process flow for this page.

  1. Create a temp table to hold the test_id, a success flag and the measurement total. The success flag is based on the HTTP/cURL return codes, where a ‘200’ is the only code written to the database that indicates a success.
  2. Drop all of the data for the specified time range into the temp table.
  3. Generate sorted lists (one ASC, the other DESC) based on the Average measurement for each test_id. The Average calculation uses the actual measurement total when the success flag is ‘1’ and 0 when the success flag is ‘0’.
  4. As part of Step 3, the Success Rate is also calculated using this fantastic little bit of SQL magic:
    ((sum(case when success = '1' then 1 else 0 end)/count(*))*100)
  5. Spew the results out using PHP to colour-code the table cells based on the generated average response time (<1 — no colour; >=1 and <3 — yellow; >=3 — red).

And the general public wonder why coding Web-apps is hard. This is a simple page.

It’s been a year and I am still scratching my head trying to figure out how to allow people to generate graphs based on their own defined start and end dates. I may start with the simple part and allow people to pull the data in HTML/Raw Text, then save the graphing problem until I have some time (like between Christmas and New Years).

Still waiting to hear about other possible enhancements to GrabPERF that people would like to see.

Leave a Reply

Your email address will not be published. Required fields are marked *