Once a load test is completed user have the option of Downloading the SQL Database as a .sql file or a user can Run Queries in WPM. Here are some common SQL queries to analyze the test data in further detail.
Analyze the avg, max, and min time for each step in a transaction:
SELECT step, COUNT(*), AVG(time_active), MAX(time_active), MIN(time_active) FROM step GROUP BY step
View the average load time of objects in the test that were requested x times, where x is the number followed by HAVING COUNT(*) > x. Depending on the size of this test the number may need to be increased (for larger tests) or decreased (for smaller tests):
SELECT path, COUNT(*), AVG(time_active), MIN(time_active), MAX(time_active) FROM object GROUP BY path HAVING COUNT(*) > 100 ORDER BY AVG(time_active) DESC
Retrieve error messages grouped by error, host, status:
SELECT err_msg, host, status_code, COUNT(*), AVG(time_active) FROM object GROUP BY err_msg, host, status_code
Get a breakdown of a specific path over the duration of the test. The path = '/' will need to be updated with a valid path for the site. To use a full URL replace path with url:
SELECT DATE_FORMAT(start_time, %H:%i'), COUNT(*), AVG(time_active), MIN(time_active), MAX(time_active), AVG(time_to_first_byte), MIN(time_to_first_byte), MAX(time_to_first_byte) FROM object WHERE path = '/' GROUP BY DATE_FORMAT(start_time, '%H:%i') ORDER BY DATE_FORMAT(start_time, '%H:%i');
Summary Information, similar to what is seen in the charts created in the Neustar WPM Dashboard:
SELECT tx.script_name, DATE_FORMAT(DATE_ADD(tx.end_time, INTERVAL 1 MINUTE), '%h:%i') AS tx_minute, step.step, step.label, ROUND(AVG(step.time_active)/1000,2) AS time_active FROM step, tx WHERE step.tx_id = tx.tx_id GROUP BY tx.script_name, tx_minute, step.step ORDER BY tx.script_name, tx_minute, step.step
View the number of steps executed per minute, this will be for all scripts.
SELECT COUNT(step), DATE_FORMAT(DATE_ADD(end_time, INTERVAL 1 MINUTE), '%h:%i') AS minutes FROM step GROUP BY minutes
The ";" is purposefully omitted from the end of these SQL queries because WPM adds them by default.
Analyze the avg, max, and min time for each step in a transaction:
SELECT step, COUNT(*), AVG(time_active), MAX(time_active), MIN(time_active) FROM step GROUP BY step
View the average load time of objects in the test that were requested x times, where x is the number followed by HAVING COUNT(*) > x. Depending on the size of this test the number may need to be increased (for larger tests) or decreased (for smaller tests):
SELECT path, COUNT(*), AVG(time_active), MIN(time_active), MAX(time_active) FROM object GROUP BY path HAVING COUNT(*) > 100 ORDER BY AVG(time_active) DESC
Retrieve error messages grouped by error, host, status:
SELECT err_msg, host, status_code, COUNT(*), AVG(time_active) FROM object GROUP BY err_msg, host, status_code
Get a breakdown of a specific path over the duration of the test. The path = '/' will need to be updated with a valid path for the site. To use a full URL replace path with url:
SELECT DATE_FORMAT(start_time, %H:%i'), COUNT(*), AVG(time_active), MIN(time_active), MAX(time_active), AVG(time_to_first_byte), MIN(time_to_first_byte), MAX(time_to_first_byte) FROM object WHERE path = '/' GROUP BY DATE_FORMAT(start_time, '%H:%i') ORDER BY DATE_FORMAT(start_time, '%H:%i');
Summary Information, similar to what is seen in the charts created in the Neustar WPM Dashboard:
SELECT tx.script_name, DATE_FORMAT(DATE_ADD(tx.end_time, INTERVAL 1 MINUTE), '%h:%i') AS tx_minute, step.step, step.label, ROUND(AVG(step.time_active)/1000,2) AS time_active FROM step, tx WHERE step.tx_id = tx.tx_id GROUP BY tx.script_name, tx_minute, step.step ORDER BY tx.script_name, tx_minute, step.step
View the number of steps executed per minute, this will be for all scripts.
SELECT COUNT(step), DATE_FORMAT(DATE_ADD(end_time, INTERVAL 1 MINUTE), '%h:%i') AS minutes FROM step GROUP BY minutes
The ";" is purposefully omitted from the end of these SQL queries because WPM adds them by default.