Ad-hoc SQL Queries

BrowserMob’s supports detailed reporting data far beyond what most load testing tools provide. Using these detailed reports, you are able to drill down to transaction, step, and even object-level details, including start/end time, bytes transfered, and response code.

Our detailed reports are nothing more than a dedicated MySQL database. You are free to download a snapshot of the database after the test completes (allowing you to hook up your own reporting tools to it), or you can allow us set up the database for you automatically. If you choose to let us do it, you will find that you can execute arbitrary SQL, just as if the database were your own.

Downloading the Database

Once a test has completed and finished cleaning up, you will be given the option of downloading the MySQL database dump. Consult the MySQL documentation or your own DBA for information on how to load the database in to your local environment.

Under the “Test Details” section of the test report, you will see the current state of the test details. To get the file, simply click the “download” link in that section:

Using the Hosted Database

If you’d prefer not to run your own database and instead take advantage of the chart integration that BrowserMob provides, you can let us host the database for you. Besides avoiding having to host your own database, it also allows you to click on test statistics and individual data points in the charts and see the underlying data for those values. This makes it easy to drill down from high-level reports to low-level data in a very simple user interface.

After a test completes, you have free access to the hosted database for up to an hour. Once that hour is over, you will need to pay a small amount of credits for us to host the database again. You can click on the “activate” link to initiate that process and pay the credits. If you don’t have enough credits, you’ll be asked to purchase some credits first.

As soon as you activate the test details, you’ll see the Test Details section change:

During this time BrowserMob is setting up a dedicated database. After a few minutes, the Test Details section will change again to the following:

At this point you are free to execute your own SQL queries, click on points in the charts, or selecting statistics such as the number of error screenshots running so far in the test.

Database Schema

Regardless of whether you download the database dump file or let us host it for you, the database schema is the same. There are three tables:

• tx – one record for every transaction executed in the load test
• step – one record for every step attempted in the load test
• object – one record for every object downloaded in the load test

Transaction Table (tx)

The transaction table (tx) has the following columns:

• tx_id – the unique ID for an individual transaction
• bytes – the total bytes received during the transaction
• end_time – the time the transaction completed
• err_line_num – the line number in the test script where the failure occurred (if success = false)
• err_msg – the exact error message that occurred at err_line_num (if success = false)
• err_screenshot_id – an ID that represents the URL of where a screenshot of the browser is (if success = false)
• instance_id – the internal BrowserMob ID of the machine that ran the transaction
• script_id – the unique script ID that the transaction was running
• start_time – the time the transaction started
• step_count – the total number of steps that were attempted
• success – whether the transaction was a succeeded or failed
• time_active – the time (in milliseconds) spent actively download objects, waiting for the transaction to finish loading, and interacting with all web pages (click, type, etc)
• time_paused – the time (in milliseconds) spent passively waiting due to the setSpeed and pause commands
• browser_num – the sequential ID of the virtual user that executed this transaction, which can be used in scripts with the #{browserNum} token

Most of these columns are fairly simple, but a few need some additional explanation.

The err_screenshot_id is a simple ID that helps you locate the actual screenshot of the browser. It is important to know that not every error will have a screenshot. Because screenshots are extremely expensive operations, we only take screenshots for the first 50 screenshots for each virtual user. After the 50th error for a particular virtual user, no more screenshots will be taken.

If you’ve downloaded the database locally, you’ll likely also want to know how to download a screenshot based on the err_screenshot_id value. You’ll notice that the value is not a complete URL but rather a simple string such as “5-9″. You can reconstruct the screenshot URL using the following template:

http://browsermob.s3.amazonaws.com/tests/[collector_id]/[instance_id]/[err_screenshot_id].jpeg

Where instance_id and err_screenshot_id are extracted from the database table. To complete the URL, you’ll need to also know the collector_id, which you can identify by looking at the URL for the MySQL database dump itself. If you’re unsure how to reconstruct this URL, you are always welcome to contact us for assistance.

The step_count column represents the number of steps that are associated with the transaction. In other words, it’s the same value you’d get if you executed the following query:

SELECT COUNT(*) FROM step WHERE step.tx_id = TX_ID

Where TX_ID is the same value found in the tx table. If you are using the hosted database, the step_count column will be hyperlinked. Clicking that link will automatically generate a query that will show all the steps in the transaction.

Thescript_id column represents the unique identifier used internally for the script that defined the transaction. You can determine which script each ID maps to by viewing the load test. For each script, BrowserMob provides the script name, a link to download the script, and it’s unique ID.

Step Table (step)

The step table (step) has the following columns:

• step_id – the unique ID of the step
• bytes – the total bytes received during this step
• end_time – the time the step finished executing
• obj_count – the total number of objects downloaded in this step
• start_time – the time the step started executing
• step – the numeric index of the step (1, 2, 3, …)
• tx_id – the transaction ID that this step is associated with
• time_active – the time (in milliseconds) spent actively download objects, waiting for the page to finish loading, and interacting with this web page (click, type, etc)
• time_paused – the time (in milliseconds) spent passively waiting due to the setSpeed and pause commands for this page

Just as the step_count column maps to the number of steps associated with the tx table, the obj_count column maps the number of objects associated with this step_id. Likewise, if you’re using the hosted database option, the obj_count column will be hyperlinked, allowing for easy navigation of the detailed data.

Object Table (object)

The object table (object) has the following columns:

• obj_id – the unique ID of this object downloaded
• bytes – the total bytes downloaded for this object
• end_time – the time the object completed downloading
• host – the host (ie: yahoo.com) that this object came from
• obj_num – the numerical sequence/index of this object for the step (1, 2, 3, …)
• method – the HTTP verb used in the request (ie: POST or GET)
• path – the path (ie: /foo/bar/baz.png) that this object came from
• protocol – the protocol used to download the object (usually http or https)
• query_string – the query string associated with the object (ie: ?bananas=5&apples=4)
• start_time – the time the object started downloading
• status_code – the HTTP status code returned when downloading the object
• url – the complete URL of the object downloaded
• step_id – the step ID that this object is associated with
• time_active – the time it took, in milliseconds, to completely download the object
• time_to_first_byte – the time it took, in milliseconds, to receive the first byte of the object

You may notice some duplicate data in here. The URL of the object is provided in complete form in the url column, but also has most of the relevant information represented in the protocol, host, path, and query_string columns. We do this so that it is easy for you to execute complex queries, such as GROUP BY queries, against parts of the URL that might be interesting to you.

The status_code is almost always a normal HTTP status code, but sometimes you may notice values such as -998 and -999. These values effectively mean that the object was not able to be downloaded before the test completed, usually due to some sort of error, such as a timeout. These codes are important as they can tell you which objects are likely to slow down as additional users are added to the system.

Name/Value Pairs Table (name_value_pairs)

The name/value pairs table (name_value_pairs) has the following columns:

• id – the unique Name/Value pair (NVP) ID for the row
• tx_id – the ID of the transaction that the NVP is associated with
• step_id – the ID of the step that the NVP is associated with, or 0 if the NVP wasn’t associated with a step
• name – the name of the NVP
• value – the value of the NVP

Tweet This Post 

1 comment