wiki:Tutorials/a0Basic/Tutorial3

Analyzing Results

It is important to understand how measurements were collected and organized to be able to interpret them. The ORBIT Measurement Framework? provides tools to insert points to tap available information and to effectively collect that information in a timely manner. The previous part of this tutorial presented information about defining and setting up this information collection points. The collected information and measurements are then stored in a SQLite database.

After the experiment completion, the user have access to the generated experiment database. In general, the results of a given experiment are stored in a single database (users can use more than one databases if required). Different participating nodes populate different tables of this generated database. Usually, user would like to post-process or visualize those raw measurements for further analysis.

A number of different tools are available to interpret experimental results. The choice of tools depends upon their availability on your platform, the nature of the measurements, and your own preferences. Excel and Matlab connections from machines outside Winlab to our database server are blocked by a firewall. We are working on a system to safely and securely export databases using these tools outside Winlab. Until then, please use any of the other following approaches to retrieve your data.

The user will need to know the Experiment ID from a successfully executed OMF script which utilized and OML-enabled application - for example iperf-oml2. The Experiment Id is a long string prefix with default_slice followed by date and time stamp. An example Experiment ID is default_slice-2014-10-20t10.59.11.720-04.00.

Using Result Service

The console has a Result Service running on port 5054. For example on the grid console the service can be accessed at the URL: grid.orbit-lab.org:5054/result. From the console the user can use a non-interactve network downloader (ie. wget) to retrieve results. A web browser (ie. Chrome) on a different machine can also be used however tunneling through the console is required.

To view all the services provided use command:

nilanjan@console.grid:~$ wget -qO- grid.orbit-lab.org:5054/result | xml_pp

The services will be displayed in XML format with a brief description between the info tags and required argument(s).

<?xml version="1.0"?>
<services>
  <serviceGroup name="result" prefix="/result">
    <info>Service to access and query experiment measurement databases</info>

    <service name="dumpDatabase">
      <info>Dump the complete database holding the measurement results for a given experiment</info>
      <args>
        <arg isRequired="true" name="expID" value="ExperimentID">
          <info>ID of the Experiment</info>
        </arg>
      </args>
    </service>

    <service name="getSchema">
      <info>Get the Schema of a given experiment measurement database</info>
      <args>
        <arg isRequired="true" name="expID" value="ExperimentID">
          <info>ID of the Experiment</info>
        </arg>
      </args>
    </service>

    <service name="listTables">
      <info>Get the list of tables in given experiment measurement database</info>
      <args>
        <arg isRequired="true" name="expID" value="ExperimentID">
          <info>ID of the Experiment</info>
        </arg>
      </args>
    </service>

    <service name="queryDatabase">
      <info>Get the Schema of a given experiment measurement database</info>
      <args>
        <arg isRequired="true" name="expID" value="ExperimentID">
          <info>ID of the Experiment</info>
        </arg>
        <arg isRequired="true" name="query" value="SQLquery">
          <info>An SQLite query to run against the database</info>
        </arg>
        <arg isRequired="true" name="format" value="raw | xml | json | cvs | merged">
          <info>Format to return result in.</info>
        </arg>
      </args>
    </service>
  </serviceGroup>

</services>

Listed below are a few example uses of the services for a given Experiment ID (default_slice-2014-10-20t10.59.11.720-04.00). Assume all commands are case-sensitive.

1) Dump the contents of the database in sqlite3 text format:

nilanjan@console.grid:~$ wget -qO- grid.orbit-lab.org:5054/result/dumpDatabase?expID=default_slice-2014-10-20t10.59.11.720-04.00

2) Print all tables in the contained in the database in XML format:

nilanjan@console.grid:~$ wget -qO- grid.orbit-lab.org:5054/result/listTables?expID=default_slice-2014-10-20t10.59.11.720-04.00 | xml_pp

<DATABASE ExperimentID="default_slice-2014-10-20t10.59.11.720-04.00">
  <TABLE>_experiment_metadata</TABLE>
  <TABLE>iperf_connection</TABLE>
  <TABLE>iperf_packets</TABLE>
  <TABLE>_senders</TABLE>
  <TABLE>iperf_jitter</TABLE>
  <TABLE>iperf_settings</TABLE>
  <TABLE>iperf_application</TABLE>
  <TABLE>iperf_losses</TABLE>
  <TABLE>iperf_transfer</TABLE>
</DATABASE>

3) Run a query on a database table and display contents in XML format:

nilanjan@console.grid:~$ wget -qO- grid.orbit-lab.org:5054/result/queryDatabase?expID=default_slice-2014-10-20t10.59.11.720-04.00\&format=xml\&query="select * from iperf_transfer" | xml_pp

<DATABASE ExperimentID="default_slice-2014-10-20t10.59.11.720-04.00">
  <QUERY>select * from iperf_transfer</QUERY>
  <RESULT>
    <FIELDS>oml_tuple_id oml_sender_id oml_seq oml_ts_client oml_ts_server pid connection_id begin_interval end_interval size</FIELDS>
    <ROW>1 1 1 5.741186998784542 5.747786 1511 6 0.0 5.0 11534336</ROW>
    <ROW>2 1 2 10.765060991048813 10.771641 1511 6 5.0 10.0 12058624</ROW>
    <ROW>3 1 3 15.74006099998951 15.746616 1511 6 10.0 15.0 12189696</ROW>
    <ROW>4 1 4 20.73482397198677 20.741354 1511 6 15.0 20.0 12713984</ROW>
    :
    :
    :
    <ROW>48 1 24 181.04624199867249 181.170476 1535 7 115.0 120.0 12320768</ROW>
  </RESULT>
</DATABASE>

Using Direct Database Access

The sqlite database is store in /var/lib/oml2 of the console. Use the command line front-end tool (sqlite3) to query the database. For a detailed overview on sqlite3 CLI please refer http://www.sqlite.org/cli.html. To get started a few examples are shown below using sqlite3 command line options from the console:

Use the following command to view the entire database contents. This dumps out all the information in sqlite3 text format. A truncated version of the output is shown.

nilanjan@console.grid:~/EXP$ sqlite3 /var/lib/oml2/default_slice-2014-10-20t10.59.11.720-04.00.sq3 ".dump"
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE _senders (name TEXT PRIMARY KEY, id INTEGER UNIQUE);
INSERT INTO "_senders" VALUES('default_slice-2014-10-20t10.25.22.955-04.00',1);
CREATE TABLE "_experiment_metadata" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "subject" TEXT, "key" TEXT, "value" TEXT);
INSERT INTO "_experiment_metadata" VALUES(1,NULL,NULL,NULL,NULL,NULL,'table__experiment_metadata','0 _experiment_metadata subject:string key:string value:string');
INSERT INTO "_experiment_metadata" VALUES(2,NULL,NULL,NULL,NULL,NULL,'start_time','1413815140');
INSERT INTO "_experiment_metadata" VALUES(3,NULL,NULL,NULL,NULL,NULL,'table_iperf_packets','1 iperf_packets pid:int32 connection_id:int32 packet_id:int32 packet_size:int32 packet_time_s:int32 packet_time_us:int32 packet_sent_time_s:int32 packet_sent_time_us:int32');
INSERT INTO "_experiment_metadata" VALUES(4,NULL,NULL,NULL,NULL,NULL,'table_iperf_jitter','2 iperf_jitter pid:int32 connection_id:int32 begin_interval:double end_interval:double jitter:double');
INSERT INTO "_experiment_metadata" VALUES(5,NULL,NULL,NULL,NULL,NULL,'table_iperf_losses','3 iperf_losses pid:int32 connection_id:int32 begin_interval:double end_interval:double total_datagrams:int32 lost_datagrams:int32');
INSERT INTO "_experiment_metadata" VALUES(6,NULL,NULL,NULL,NULL,NULL,'table_iperf_transfer','4 iperf_transfer pid:int32 connection_id:int32 begin_interval:double end_interval:double size:uint64');
INSERT INTO "_experiment_metadata" VALUES(7,NULL,NULL,NULL,NULL,NULL,'table_iperf_connection','5 iperf_connection pid:int32 connection_id:int32 local_address:string local_port:int32 remote_address:string remote_port:int32');
INSERT INTO "_experiment_metadata" VALUES(8,NULL,NULL,NULL,NULL,NULL,'table_iperf_settings','6 iperf_settings pid:int32 server_mode:int32 bind_address:string multicast:int32 multicast_ttl:int32 transport_protocol:int32 window_size:int32 buffer_size:int32');
INSERT INTO "_experiment_metadata" VALUES(9,NULL,NULL,NULL,NULL,NULL,'table_iperf_application','7 iperf_application pid:int32 version:string cmdline:string starttime_s:int32 starttime_us:int32');
CREATE TABLE "iperf_packets" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "connection_id" INTEGER, "packet_id" INTEGER, "packet_size" INTEGER, "packet_time_s" INTEGER, "packet_time_us" INTEGER, "packet_sent_time_s" INTEGER, "packet_sent_time_us" INTEGER);
INSERT INTO "iperf_packets" VALUES(1,1,1,0.411181999836117,0.415859,1236,6,0,131072,1413815140,411180,-1243478864,0);
INSERT INTO "iperf_packets" VALUES(2,1,2,0.474676999729127,0.479372,1236,6,0,131072,1413815140,474676,-1243478864,0);
INSERT INTO "iperf_packets" VALUES(3,1,3,0.538958999328315,0.543652,1236,6,0,131072,1413815140,538958,-1243478864,0);
:
:
:
INSERT INTO "iperf_packets" VALUES(3429,1,1722,184.396865963936,184.390128,1261,6,0,131072,1413815324,396865,-1243478864,0);
CREATE TABLE "iperf_jitter" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "connection_id" INTEGER, "begin_interval" REAL, "end_interval" REAL, "jitter" REAL);
CREATE TABLE "iperf_losses" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "connection_id" INTEGER, "begin_interval" REAL, "end_interval" REAL, "total_datagrams" INTEGER, "lost_datagrams" INTEGER);
CREATE TABLE "iperf_transfer" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "connection_id" INTEGER, "begin_interval" REAL, "end_interval" REAL, "size" UNSIGNED BIGINT);
INSERT INTO "iperf_transfer" VALUES(1,1,1,5.37069699913263,5.375371,1236,6,0.0,5.0,12451840);
INSERT INTO "iperf_transfer" VALUES(2,1,2,10.3784499913454,10.383083,1236,6,5.0,10.0,12713984);
:
:
:
INSERT INTO "iperf_transfer" VALUES(47,1,23,175.713173985481,175.706422,1261,6,110.0,115.0,12976128);
INSERT INTO "iperf_transfer" VALUES(48,1,24,180.751036882401,180.744307,1261,6,115.0,120.0,12713984);
CREATE TABLE "iperf_connection" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "connection_id" INTEGER, "local_address" TEXT, "local_port" INTEGER, "remote_address" TEXT, "remote_port" INTEGER);
INSERT INTO "iperf_connection" VALUES(1,1,1,0.324951999820769,0.33002,1236,6,'192.168.0.1',57694,'192.168.0.254',5001);
INSERT INTO "iperf_connection" VALUES(2,1,1,60.7023439994082,60.695348,1261,6,'192.168.0.2',49302,'192.168.0.254',5001);
CREATE TABLE "iperf_settings" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "server_mode" INTEGER, "bind_address" TEXT, "multicast" INTEGER, "multicast_ttl" INTEGER, "transport_protocol" INTEGER, "window_size" INTEGER, "buffer_size" INTEGER);
INSERT INTO "iperf_settings" VALUES(1,1,1,0.324930999893695,0.32958,1236,1,'0.0.0.0',0,0,6,21480,131072);
INSERT INTO "iperf_settings" VALUES(2,1,1,60.7023199992254,60.695187,1261,1,'0.0.0.0',0,0,6,21480,131072);
CREATE TABLE "iperf_application" (oml_tuple_id INTEGER PRIMARY KEY, oml_sender_id INTEGER, oml_seq INTEGER, oml_ts_client REAL, oml_ts_server REAL, "pid" INTEGER, "version" TEXT, "cmdline" TEXT, "starttime_s" INTEGER, "starttime_us" INTEGER);
INSERT INTO "iperf_application" VALUES(1,1,1,0.320253999903798,0.329126,1236,'2.0.5+oml2.9.pre0','/usr/bin/iperf-oml2 -c 192.168.0.254 -i 5 -y O -t 99999',1413815140,320246);
INSERT INTO "iperf_application" VALUES(2,1,1,60.3850499996915,60.378299,1261,'2.0.5+oml2.9.pre0','/usr/bin/iperf-oml2 -c 192.168.0.254 -i 5 -y O -t 99999',1413815200,385046);
COMMIT;

To list all the the tables in the database:

nilanjan@console.grid:~/EXP$ sqlite3 /var/lib/oml2/default_slice-2014-10-20t10.25.22.955-04.00.sq3 ".tables"
_experiment_metadata  iperf_connection      iperf_packets
_senders              iperf_jitter          iperf_settings
iperf_application     iperf_losses          iperf_transfer

Let's view the contents in the iperf_transfer table:

nilanjan@console.grid:~/EXP$ sqlite3 -header -column /var/lib/oml2/default_slice-2014-10-20t10.25.22.955-04.00.sq3 "select * from iperf_transfer"
oml_tuple_id  oml_sender_id  oml_seq     oml_ts_client     oml_ts_server  pid         connection_id  begin_interval  end_interval  size
------------  -------------  ----------  ----------------  -------------  ----------  -------------  --------------  ------------  ----------
1             1              1           5.37069699913263  5.375371       1236        6              0.0             5.0           12451840
2             1              2           10.3784499913454  10.383083      1236        6              5.0             10.0          12713984
3             1              3           15.3470119982958  15.3516        1236        6              10.0            15.0          12320768
4             1              4           20.3752109706402  20.379763      1236        6              15.0            20.0          12845056
5             1              5           25.3491049706936  25.353631      1236        6              20.0            25.0          12582912
6             1              6           30.3527579903603  30.35724       1236        6              25.0            30.0          12320768
7             1              7           35.3587439656258  35.363198      1236        6              30.0            35.0          12713984
8             1              8           40.3642519712448  40.368689      1236        6              35.0            40.0          12582912
9             1              9           45.3395289778709  45.343928      1236        6              40.0            45.0          11665408
10            1              10          50.3661079406738  50.370467      1236        6              45.0            50.0          11534336
11            1              11          55.3729719519615  55.377313      1236        6              50.0            55.0          11534336
12            1              12          60.3800199627876  60.384375      1236        6              55.0            60.0          11403264
13            1              13          65.3507549762726  65.355045      1236        6              60.0            65.0          8126464
14            1              1           65.7505009993911  65.743343      1261        6              0.0             5.0           3538944
15            1              14          70.3302088975906  70.33448       1236        6              65.0            70.0          5898240
16            1              2           70.7465729862452  70.739399      1261        6              5.0             10.0          5898240
17            1              15          75.3316609859467  75.33591       1236        6              70.0            75.0          5767168
18            1              3           75.8462129980326  75.83902       1261        6              10.0            15.0          5898240
19            1              16          80.4429728984833  80.447197      1236        6              75.0            80.0          5898240
20            1              4           80.8001589775085  80.792952      1261        6              15.0            20.0          5767168
21            1              17          85.4174538850784  85.421657      1236        6              80.0            85.0          5767168
22            1              5           85.7791629731655  85.771938      1261        6              20.0            25.0          5767168
23            1              18          90.3622509241104  90.366441      1236        6              85.0            90.0          5898240
24            1              6           90.7223399877548  90.715142      1261        6              25.0            30.0          5898240
25            1              19          95.3966519832611  95.400828      1236        6              90.0            95.0          5898240
26            1              7           95.8051519989967  95.797987      1261        6              30.0            35.0          5898240
27            1              20          100.39771091938   100.4019       1236        6              95.0            100.0         5767168
28            1              8           100.76890796423   100.761771     1261        6              35.0            40.0          5767168
29            1              21          105.401404976845  105.405547     1236        6              100.0           105.0         5767168
30            1              9           105.723350942135  105.716245     1261        6              40.0            45.0          5636096
31            1              22          110.330290913582  110.334424     1236        6              105.0           110.0         5767168
32            1              10          110.759348988533  110.752272     1261        6              45.0            50.0          6029312
33            1              23          115.358618974686  115.362742     1236        6              110.0           115.0         6029312
34            1              11          115.765810966492  115.758763     1261        6              50.0            55.0          5898240
35            1              24          120.341752886772  120.345869     1236        6              115.0           120.0         5767168
36            1              12          120.729885995388  120.722867     1261        6              55.0            60.0          5767168
37            1              13          125.745458960533  125.738468     1261        6              60.0            65.0          7340032
38            1              14          130.762330889702  130.755367     1261        6              65.0            70.0          12976128
39            1              15          135.714812994003  135.707874     1261        6              70.0            75.0          12451840
40            1              16          140.713323950768  140.706414     1261        6              75.0            80.0          12582912
41            1              17          145.724961996079  145.718074     1261        6              80.0            85.0          12845056
42            1              18          150.709739923477  150.702877     1261        6              85.0            90.0          13238272
43            1              19          155.721814990044  155.714976     1261        6              90.0            95.0          12976128
44            1              20          160.733428955078  160.726618     1261        6              95.0            100.0         12845056
45            1              21          165.726515889168  165.719722     1261        6              100.0           105.0         12713984
46            1              22          170.711465954781  170.704687     1261        6              105.0           110.0         12976128
47            1              23          175.713173985481  175.706422     1261        6              110.0           115.0         12976128
48            1              24          180.751036882401  180.744307     1261        6              115.0           120.0         12713984

Microsoft Excel Example

Alternatively Excel Sheet can be used to fetch data associated with an Experiment ID. ResultServiceVBA.xlsm (under the attachment) is an example to access experiment data previously run on the grid console. To retrieve data using this file:

1) Before running the file use Putty to set up a tunnel from localhost:6064 to grid.orbit-lab.org:5054.

2) Once the tunnel is set up, run the ResultServiceVBA file and enable any macros/ActiveX controls when prompted.

3) In cell B1 (under Sheet1) copy and paste your Experiment ID, then click on Fetch to retrieve the data.

Sheet1 will contain the List of Tables that are available. A new sheet will be created for each table and populated with available data. A couple of snapshots are shown below.

This excel file was run using Microsoft Office 2010 on Windows 7 64-bit. A successful execution of this file may be affected by several factors - Window Operating System, Microsoft Office version, Windows security updates, permission level on User Account, ActiveX controls configuration/updates, etc…

Matlab Example

Last modified 3 years ago Last modified on 12/22/14 11:01:50

Attachments (5)

Download all attachments as: .zip