...
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.
As some of the PingER files were scattered, they were unified into the following location (on pinger.slac.stanford.edu):
| Code Block |
|---|
/nfs/slac/g/net/pinger/pingerdata/hep/data/<YYYY>/<source>/ping-<YYYY>-<MM>-<DD>.txt.gz
|
SQL Storage Format
Three tables are required for the storage of the data into SQL tables.
Table holds information about a single ping node.
| Code Block |
|---|
CREATE TABLE host 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
...
| Code Block |
|---|
# 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));
|
Creation
A database was set up on a host for testing and initial loading purposed. all relevant permmisisons were granted to allow the pinger.slac.stanford.edu host write access to this database.
| Code Block |
|---|
mysql> create database pinger;
Query OK, 1 row affected (0.00 sec)
|
| Code Block |
|---|
mysql -D pinger -p pinger < /u/sf/ytl/Work/perfSONAR/perfSONAR-PS/trunk/perfSONAR-PS/MA/PingER/create_pingerMA_MySQL.sql
|
Script
A script, pinger-ma_conversion.pl was created (in PingER svn bin) which reads in the raw output from these log files and inserts them into a database.
| Code Block |
|---|
|
$ perl pinger-ma_conversion.pl --help
Parses PingER flat file data into the specified databaseUsage: cat <PingER data file> | pinger-ma_conversion.pl [--db
Options:
--host=s hostname of database location
--port=s port number for database
--db=s database name
--user=s username for database
--password=s password for database
--verbose provide statistical data of inserts
--help this help message
|
| Code Block |
|---|
|
zcat /nfs/slac/g/net/pinger/pingerdata/1997/ping-1997-07.txt.gz | perl pinger-ma_conversion.pl --host=<hostname> --port=<port> --user=<dbuser> --password=<dbpassword> --db=<dbname>
|
Conversion
The log configuratoin file was changed to the following for the conversion process:
| Code Block |
|---|
|
log4perl.logger = INFO, A1
log4perl.appender.A1=Log::Dispatch::File
log4perl.appender.A1.filename=/tmp/pinger-ma_conversion.log
log4perl.appender.A1.mode=append
log4perl.appender.A1.layout=Log::Log4perl::Layout::PatternLayout
log4perl.appender.A1.layout.ConversionPattern=%d %m%n
|
A file containing a list of all of the txt.gz files was created and a simple bash command was created to run through the list:
| Code Block |
|---|
for f in `cat processing_list`; do echo "Analysing $f"; echo $f >> processed_data_files; zcat $f | perl pinger-ma_conversion.pl --host=<host> --port=<port> --user=<user> --password=<password> --db=<dbname>; done
|