MISC-14: Pull Stats from Collected Log files

Issue Information

Issue Type: Sub-task
 
Priority: Major
Status: Closed

Reported By:
Ben Tasker
Assigned To:
Ben Tasker
Project: Miscellaneous (MISC)
Resolution: Done (2017-07-06 15:54:17)
Affects Version: TorCDN,
Target version: TorCDN,

Created: 2015-12-18 16:14:54
Time Spent Working
Estimated:
 
240 minutes
Remaining:
  
115 minutes
Logged:
  
125 minutes
Child of: MISC-12: Optimising Video Delivery for Tor / Building a Tor based CDN


Description
Once each set of tests is complete, various performance stats need to be pulled from the logfiles (and where appropriate, graphed).

Need to include

- Bitrates requested
- Delivery times
- Cache Disposition
- Bytes Delivered
- Calculates delivery rate (bytes/duration)

Where tests are considered comparable (for example test5 v test 17, or 17 vs 18) output should be generated showing the comparison.


Issue Links

Graphs (Projectsstatic)
Log Entries SQLDump (Projectsstatic)
Toggle State Changes

Activity


2015-12-26 16:32:20
btasker changed timespent from '0 minutes' to '60 minutes'

2015-12-26 16:46:20
Logs have been processed and imported into a MySQL database, with some additional data extracted from the entries themselves.

Table schema is 
CREATE TABLE logentries (
      id INT NOT NULL AUTO_INCREMENT,
      cache VARCHAR(20) NOT NULL,
      test VARCHAR(10) NOT NULL,
      dateepoch INT NOT NULL,
      reqstring VARCHAR(255) NOT NULL,
      reqextension VARCHAR(10) NOT NULL,
      bitrate INT NOT NULL,
      quality VARCHAR(5) NOT NULL,
      seglength INT NOT NULL,
      respstatus INT NOT NULL DEFAULT 0,
      respbytes INT NOT NULL DEFAULT 0,
      cachedisp VARCHAR(50) NOT NULL,
      respduration DECIMAL(16,8) NOT NULL, 
      downstream VARCHAR(20),
      PRIMARY KEY(`id`),
      KEY(`test`,`cache`),
      KEY(`seglength`),
      KEY(`quality`),
      KEY(`bitrate`),
      KEY(`respstatus`),
      KEY(`cachedisp`)
);

and was populated by doing 
      CACHE="origin"
      for i in *.log
      do
      echo "INSERT INTO logentries (cache,test,dateepoch,reqstring,reqextension,bitrate,quality,seglength,respstatus,respbytes,cachedisp,respduration,downstream) VALUES `php ../../Scripts/log_import.php $i $CACHE`;" | mysql -u $MYSQL_USER --password="$MYSQL_PASS" torcdn
      done

So, just need to work on extracting the desired stats from the database.

For a quick look at max/min/means etc 
mysql> select test,cache, seglength, quality, MIN(respduration) as min, MAX(respduration) as max, avg(respduration) as mean from logentries where reqextension='ts' GROUP BY test,cache ORDER BY cast(test as unsigned) ASC ,cache DESC;
+------+----------+-----------+---------+------------+-------------+-----------------+
| test | cache    | seglength | quality | min        | max         | mean            |
+------+----------+-----------+---------+------------+-------------+-----------------+
| 1    | origin   |         2 | 1080p   | 0.02400000 |  0.75500000 |  0.176227488152 |
| 1    | Midtier1 |         2 | 1080p   | 0.09800000 |  0.88300000 |  0.269388625592 |
| 1    | Edge1    |         2 | 1080p   | 0.23400000 | 12.29000000 |  4.105542452830 |
| 2    | origin   |         2 | 1080p   | 0.07100000 |  0.07100000 |  0.071000000000 |
| 2    | Midtier1 |         2 | 1080p   | 0.12300000 |  0.12300000 |  0.123000000000 |
| 2    | Edge1    |         2 | 1080p   | 0.00000000 |  3.47200000 |  0.386325471698 |
| 3    | origin   |         2 | 1080p   | 0.00000000 |  0.00000000 |  0.000000000000 |
| 3    | Midtier1 |         2 | 1080p   | 0.01100000 |  4.68100000 |  1.230679245283 |
| 3    | Edge2    |         2 | 1080p   | 0.57500000 |  5.75600000 |  1.853872641509 |
| 5    | origin   |         2 | 1080p   | 0.00000000 |  2.32200000 |  0.756469483568 |
| 6    | origin   |         2 | 1080p   | 0.02300000 |  0.54100000 |  0.178370892019 |
| 6    | Midtier1 |         2 | 1080p   | 0.09500000 |  3.91100000 |  0.278126760563 |
| 7    | origin   |         2 | 1080p   | 0.17400000 |  0.17400000 |  0.174000000000 |
| 7    | Midtier1 |         2 | 1080p   | 0.00000000 |  2.65500000 |  0.093518072289 |
| 8    | Midtier1 |         2 | 1080p   | 0.00200000 |  4.59200000 |  1.524441860465 |
| 8    | Edge2    |         2 | 1080p   | 1.25000000 | 18.85200000 |  2.864051162791 |
| 9    | origin   |         2 | 1080p   | 0.00000000 |  0.00000000 |  0.000000000000 |
| 9    | Midtier1 |         2 | 1080p   | 0.65700000 |  0.65700000 |  0.657000000000 |
| 9    | Edge2    |         2 | 1080p   | 0.00000000 |  2.93700000 |  0.038084070796 |
| 10   | origin   |         2 | 1080p   | 0.02100000 |  0.43800000 |  0.135696261682 |
| 10   | Midtier1 |         2 | 1080p   | 0.08400000 |  5.43000000 |  2.145766355140 |
| 10   | Edge2    |         2 | 1080p   | 1.26700000 | 10.29600000 |  3.964976635514 |
| 11   | origin   |         2 | 1080p   | 0.00000000 |  0.20800000 |  0.012945945946 |
| 11   | Midtier1 |         2 | 1080p   | 0.00500000 |  5.00200000 |  1.320966996700 |
| 11   | Edge2    |         2 | 1080p   | 0.00000000 | 10.29800000 |  1.873470862471 |
| 12   | origin   |         2 | 1080p   | 0.00000000 |  0.42800000 |  0.132634020619 |
| 12   | Midtier1 |         2 | 1080p   | 0.07700000 |  3.77300000 |  0.874139175258 |
| 12   | Edge2    |         2 | 1080p   | 0.00000000 |  4.19700000 |  0.588513953488 |
| 13   | origin   |         2 | 1080p   | 0.01500000 |  0.50600000 |  0.130845070423 |
| 13   | Midtier1 |         2 | 1080p   | 0.08700000 |  1.30200000 |  0.207605633803 |
| 13   | Edge1    |         2 | 1080p   | 0.44400000 | 10.91700000 |  2.996065727700 |
| 14   | origin   |         2 | 1080p   | 0.09600000 |  0.76100000 |  0.220625000000 |
| 14   | Midtier1 |         2 | 1080p   | 0.16000000 |  0.87200000 |  0.319187500000 |
| 14   | Edge1    |         2 | 1080p   | 0.00000000 | 10.15200000 |  0.347000000000 |
| 15   | origin   |         2 | 1080p   | 0.00000000 |  0.16900000 |  0.000775229358 |
| 15   | Midtier1 |         2 | 1080p   | 0.00000000 |  1.35800000 |  0.085899193548 |
| 15   | Edge1    |         2 | 1080p   | 0.00000000 |  8.87800000 |  0.638016431925 |
| 16   | origin   |         2 | 1080p   | 0.00000000 |  0.00000000 |  0.000000000000 |
| 16   | Midtier1 |         2 | 1080p   | 0.03500000 |  0.31200000 |  0.064404109589 |
| 16   | Edge1    |         2 | 1080p   | 0.00000000 |  5.49600000 |  0.205518965517 |
| 17   | origin   |        10 | 1080p   | 0.84000000 |  4.64600000 |  2.287772727273 |
| 18   | origin   |        10 | 1080p   | 0.11000000 |  1.42200000 |  0.400885714286 |
| 18   | Midtier1 |        10 | 1080p   | 0.17200000 | 12.63100000 |  5.506828571429 |
| 19   | origin   |        10 | 1080p   | 0.14700000 |  0.63500000 |  0.311833333333 |
| 19   | Midtier1 |        10 | 1080p   | 0.22600000 | 10.60300000 |  5.022476923077 |
| 20   | origin   |        10 | 1080p   | 0.11200000 |  1.10100000 |  0.257580645161 |
| 20   | Midtier1 |        10 | 1080p   | 0.19000000 | 14.42000000 |  1.944415384615 |
| 20   | Edge1    |        10 | 1080p   | 1.44900000 | 24.91400000 | 11.277538461538 |
| 21   | origin   |        10 | 1080p   | 0.13700000 |  0.65800000 |  0.339363636364 |
| 21   | Midtier1 |        10 | 1080p   | 0.06500000 | 17.15600000 |  4.635533333333 |
| 21   | Edge1    |        10 | 1080p   | 0.00000000 | 49.89700000 |  8.374724137931 |
| 22   | origin   |        10 | 1080p   | 0.10300000 |  0.93600000 |  0.293626666667 |
| 22   | Midtier1 |        10 | 1080p   | 0.16800000 | 13.71500000 |  2.533826666667 |
| 22   | Edge1    |        10 | 1080p   | 3.27200000 | 24.35900000 |  7.494120000000 |
| 23   | origin   |        10 | 1080p   | 0.08400000 |  0.67700000 |  0.348172413793 |
| 23   | Midtier1 |        10 | 1080p   | 0.15700000 | 16.91500000 |  4.396586206897 |
| 23   | Edge1    |        10 | 1080p   | 0.79500000 | 26.52100000 |  7.346583333333 |
| 24   | Edge1    |        10 | 1080p   | 0.40000000 | 11.36500000 |  5.376318840580 |
| 25   | origin   |        10 | 1080p   | 0.00000000 |  0.67300000 |  0.005427419355 |
| 25   | Midtier1 |        10 | 1080p   | 0.04000000 | 15.55800000 |  2.959730769231 |
| 25   | Edge1    |        10 | 1080p   | 0.00000000 | 23.75200000 |  8.362006711409 |
| 26   | origin   |        10 | 1080p   | 0.00000000 |  0.21400000 |  0.007379310345 |
| 26   | Midtier1 |        10 | 1080p   | 0.10900000 | 21.44000000 |  7.127548387097 |
| 26   | Edge1    |        10 | 1080p   | 0.00000000 | 34.71800000 |  6.985536585366 |
| 27   | origin   |        10 | 480p_   | 0.45100000 |  4.55400000 |  2.322725806452 |
| 28   | origin   |        10 | 480p_   | 0.10500000 |  2.16500000 |  0.356043010753 |
| 28   | Midtier1 |        10 | 480p_   | 0.01700000 |  9.72600000 |  2.579320000000 |
| 29   | origin   |        10 | 480p_   | 0.10000000 |  2.15900000 |  0.404000000000 |
| 29   | Midtier1 |        10 | 480p_   | 0.00000000 | 15.64100000 |  3.328119047619 |
| 30   | origin   |        10 | 480p_   | 0.09400000 |  0.41600000 |  0.223680851064 |
| 30   | Midtier1 |        10 | 480p_   | 0.00000000 |  7.33800000 |  1.021985714286 |
| 30   | Edge1    |        10 | 480p_   | 0.00000000 | 19.84900000 |  3.723097222222 |
| 31   | origin   |        10 | 480p_   | 0.13100000 |  1.34700000 |  0.297333333333 |
| 31   | Midtier1 |        10 | 480p_   | 0.19000000 | 12.42500000 |  3.798400000000 |
| 31   | Edge1    |        10 | 480p_   | 0.00000000 | 17.07500000 |  4.042428571429 |
| 32   | origin   |        10 | 480p_   | 0.09500000 |  0.55800000 |  0.230986666667 |
| 32   | Midtier1 |        10 | 480p_   | 0.15200000 | 17.05000000 |  3.639302631579 |
| 32   | Edge1    |        10 | 480p_   | 0.00000000 | 26.56700000 |  9.286948051948 |
| 33   | origin   |        10 | 480p_   | 0.00000000 |  0.00000000 |  0.000000000000 |
| 33   | Midtier1 |        10 | 480p_   | 0.10700000 |  0.51300000 |  0.157916666667 |
| 33   | Edge1    |        10 | 480p_   | 0.00000000 | 11.52500000 |  3.208166666667 |
| 34   | origin   |        10 | 480p_   | 0.00000000 |  0.21400000 |  0.007642857143 |
| 34   | Midtier1 |        10 | 480p_   | 0.17500000 |  0.42000000 |  0.237714285714 |
| 34   | Edge1    |        10 | 480p_   | 0.00000000 | 74.10600000 |  4.362655737705 |
| 35   | origin   |        10 | 480p_   | 0.10600000 |  0.65200000 |  0.231137500000 |
| 35   | Midtier1 |        10 | 480p_   | 0.00000000 | 21.67800000 |  1.946314285714 |
| 35   | Edge1    |        10 | 480p_   | 0.00000000 | 32.61000000 |  8.749844961240 |
| 36   | origin   |        10 | 480p_   | 0.11100000 |  1.76200000 |  0.299736842105 |
| 36   | Midtier1 |        10 | 480p_   | 0.17600000 | 31.09700000 |  5.207483333333 |
| 36   | Edge1    |        10 | 480p_   | 0.00000000 | 39.24400000 |  4.943588235294 |
| 37   | origin   |        10 | 480p_   | 0.10400000 |  1.12300000 |  0.261351351351 |
| 37   | Midtier1 |        10 | 480p_   | 0.16300000 | 16.88900000 |  5.853918918919 |
| 37   | Edge1    |        10 | 480p_   | 0.00000000 | 24.50900000 |  3.521120481928 |
| 38   | origin   |        10 | 480p_   | 0.20200000 |  0.34100000 |  0.256500000000 |
| 38   | Midtier1 |        10 | 480p_   | 9.91600000 | 26.02100000 | 16.292600000000 |
| 38   | Edge1    |        10 | 480p_   | 0.00000000 | 42.00300000 |  3.654086956522 |
+------+----------+-----------+---------+------------+-------------+-----------------+

2015-12-27 04:43:19
btasker changed timespent from '60 minutes' to '95 minutes'

2015-12-28 22:33:00
The initial comparison graphs should be

- Test 17 vs Test 27
- Test 18 vs Test 28
- Test 19 vs Test 29
- Test 20 vs Test 30
- Test 21 vs Test 31
- Test 25 vs Test 35
- Test 26 vs Test 36


As one graph:

Test 35,36,37 and 38

As one 39,40.

Initial display will be durations and cache dispositions

2016-01-10 21:16:02
btasker changed Project from 'BenTasker.co.uk' to 'Miscellaneous'

2016-01-10 21:16:02
btasker changed Key from 'BEN-603' to 'MISC-14'

2016-01-10 21:16:59
btasker added 'TorCDN' to Version

2016-01-10 21:17:05
btasker added 'TorCDN' to Fix Version

2016-01-10 21:28:15
The graphs so far are fairly rudimentary, but are available at

Overviews:

- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/dispositions.html
- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/durations.html
- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/bitrates.html


Comparisons:

- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/17v27.html
- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/18v28.html
- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/19v29.html
- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/20v30.html
- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/21v31.html
- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/25v35.html
- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/26v36.html
- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/35-36-37-38.html
- http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/39-40.html

2016-01-10 21:29:25
btasker changed timespent from '95 minutes' to '125 minutes'

2017-07-06 15:54:17
btasker changed status from 'Open' to 'Resolved'

2017-07-06 15:54:17
btasker added 'Done' to resolution

2017-07-06 15:54:21
btasker changed status from 'Resolved' to 'Closed'

Work log

Ben Tasker
 Permalink
2015-12-26 16:32:20

Time Spent: 60 minutes
Log Entry: Importing logs into a MySQL database
Ben Tasker
 Permalink
2015-12-27 04:43:18

Time Spent: 35 minutes
Log Entry: Generating some graphs
Ben Tasker
 Permalink
2016-01-10 21:29:25

Time Spent: 30 minutes
Log Entry: Generating graphs and publishing