########################################################################################## MISC-14: Pull Stats from Collected Log files ########################################################################################## Issue Type: Sub-task ----------------------------------------------------------------------------------------- Issue Information ==================== Priority: Major Status: Closed Resolution: Done (2017-07-06 15:54:17) Project: Miscellaneous (MISC) Reported By: btasker Assigned To: btasker Child of: MISC-12 - Optimising Video Delivery for Tor / Building a Tor based CDN Affected Versions: - TorCDN Targeted for fix in version: - TorCDN Time Estimate: 115 minutes Time Logged: 125 minutes ----------------------------------------------------------------------------------------- Issue 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 Relations ================ - Graphs (Projectsstatic) (http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/Graphs/) - Log Entries SQLDump (Projectsstatic) (http://projectsstatic.bentasker.co.uk/MISC/MISC12/Logs/video_logs.sql.gz) ----------------------------------------------------------------------------------------- Activity ========== ----------------------------------------------------------------------------------------- 2015-12-26 16:32:20 ----------------------------------------------------------------------------------------- btasker changed timespent from '0 minutes' to '60 minutes' ----------------------------------------------------------------------------------------- 2015-12-26 16:46:20 btasker ----------------------------------------------------------------------------------------- Logs have been processed and imported into a MySQL database, with some additional data extracted from the entries themselves. Table schema is -- BEGIN SNIPPET -- 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`) ); -- END SNIPPET -- and was populated by doing -- BEGIN SNIPPET -- 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 -- END SNIPPET -- So, just need to work on extracting the desired stats from the database. For a quick look at max/min/means etc -- BEGIN SNIPPET -- 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 | +------+----------+-----------+---------+------------+-------------+-----------------+ -- END SNIPPET -- ----------------------------------------------------------------------------------------- 2015-12-27 04:43:19 ----------------------------------------------------------------------------------------- btasker changed timespent from '60 minutes' to '95 minutes' ----------------------------------------------------------------------------------------- 2015-12-28 22:33:00 btasker ----------------------------------------------------------------------------------------- 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 btasker ----------------------------------------------------------------------------------------- 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' ----------------------------------------------------------------------------------------- Worklog ======== ----------------------------------------------------------------------------------------- 2015-12-26 16:32:20 btasker 60 minutes ----------------------------------------------------------------------------------------- Importing logs into a MySQL database ----------------------------------------------------------------------------------------- 2015-12-27 04:43:18 btasker 35 minutes ----------------------------------------------------------------------------------------- Generating some graphs ----------------------------------------------------------------------------------------- 2016-01-10 21:29:25 btasker 30 minutes ----------------------------------------------------------------------------------------- Generating graphs and publishing