...
| 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 |
|---|
|
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
|