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.
- 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.
- Drop all of the data for the specified time range into the temp table.
- 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’.
- 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)
- 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.