Background
This page describes the proceeduring in converting over 10 years of PingER data into a format which is more suitable for analysis and presentation using the perfSONAR PingER MA formats.
PingER Data File Format
All of the old pinger data is stored under:
pinger:/nfs/slac/g/net/pinger/pingerdata/
Each file contains the same data output format; each line represents one probe (of pings) with data stored in the following format:
www.nbi.dk 130.225.212.55 lns62.lns.cornell.edu 128.84.47.62 100 1041466552 10 10 143 144 148 0 1 2 3 4 5 6 7 8 9 143 144 143 143 143 143 148 145 144 143
Field |
Data (above) |
Description |
|---|---|---|
0 |
www.nbi.dk |
dns name of source of ping |
1 |
130.225.212.55 |
ip addresss of source of ping |
2 |
lns62.lns.cornell.edu |
destination dns name of ping |
3 |
128.84.47.62 |
destination ip address of ping |
4 |
100 |
ICMP packet size of ping |
5 |
1041466552 |
Epoch time stamp of test (start or end?) |
6 |
10 |
Number of pings sent |
7 |
10 |
Number of pings recieved |
8 |
143 |
Minimum Rtt of all pings |
9 |
144 |
Mean Rtt of all pings |
10 |
148 |
Maximum Rtt of all pings |
11-20 |
0 1 2 3 4 5 6 7 8 9 |
Sequence number in the recieving order of pings |
21-30 |
143 144 143 143 143 143 148 145 144 143 |
Individual ping singleton values |
The complexity in the data format lies in the sequence number and ping singleton values which must be derived from the number of pings recieved.
SQL Storage Format
Three tables are required for the storage of the data into SQL tables.
Table holds information about a single ping node.
CREATE TABLE ipaddr_intervals ( ip_name varchar(52) NOT NULL, ip_number varchar(64) NOT NULL, active_start bigint(12) NOT NULL, active_end bigint(12) NOT NULL, comments text, PRIMARY KEY (ip_name, ip_number, active_end));
The metadata table holds constants of the test settings, such as the source and destination
CREATE TABLE metaData ( metaID BIGINT NOT NULL AUTO_INCREMENT, ip_name_src varchar(52), ip_name_dst varchar(52), packetSize smallint NOT NULL, count smallint NOT NULL, protocol varchar(10) NOT NULL, period smallint, deadline smallint, ttl smallint, INDEX (ip_name_src, ip_name_dst, packetSize, count), FOREIGN KEY (ip_name_src) references ipaddr_intervals (ip_name), FOREIGN KEY (ip_name_dst) references ipaddr_intervals (ip_name), PRIMARY KEY (metaID));
# pinger data table, some fields have names differnt from XML schema since there where # inherited from the current pinger data table # its named data_yyyyMM to separate from old format - pairs_yyyyMM CREATE TABLE data_200707 ( metaID BIGINT NOT NULL, minRtt float, meanRtt float, medianRtt float, maxRtt float, timestamp bigint(12) NOT NULL, minIpd float, meanIpd float, maxIpd float, duplicates tinyint(1), outOfOrder tinyint(1), clp float, iqrIpd float, lossPercent float, INDEX (meanRtt, medianRtt, lossPercent, meanIpd, clp), FOREIGN KEY (metaID) references metaData (metaID), PRIMARY KEY (metaID, timestamp));