User:Armenzg/Data
- How to determine how many test machines we need to handle our capacity
- How much CPU all tests triggered by the opt & debug builds?
- How to determine monthly infrastructure load
- Load https://secure.pub.build.mozilla.org/buildapi/reports/pushes
- Select the first day and last days of the month
- Select the interval to be "day"
- Adjust the "starttime" and "endtime" by adding multiple of 3600 seconds
- Save the page as "YYYY-MM pushes report.html"
- scp -r *\ pushes\ report* people.mozilla.org:~/public_html/reports
- upload the json dump as YYYYMM.json (append &format=json to the URL)
- fill the data for the month analyzed in the "Mozilla's pushes report & AWS costs" Google Doc
- write blog post
- use the "Mozilla pushes template" doc as a template to your post
- add link to HTML report & the json file (append &format=json)
- add screenshots from the buildapi report
- add screenshot from the chart in "Mozilla's pushes report" Google Doc
Sql Queries
select year(builds.starttime) as Year, month(builds.starttime) as Month, round(sum(unix_timestamp(builds.endtime)-unix_timestamp(builds.starttime))) as "Total CPU sum", count(*) as Jobs, round(sum(unix_timestamp(builds.endtime)-unix_timestamp(builds.starttime))) DIV count(*) as Ratio from builds left join (slaves, builders) on (builds.slave_id=slaves.id and builds.builder_id=builders.id) where builds.starttime between '2014-01-01' and '2014-01-31' and slaves.name like "t-w732-%" group by Year, Month order by Year, Month;
select builders.name, round(avg(unix_timestamp(builds.endtime)-unix_timestamp(builds.starttime))) as seconds from builds left join (slaves, builders) on (builds.slave_id=slaves.id and builds.builder_id=builders.id) where builds.starttime between '2014-01-01' and '2014-01-31' and slaves.name like "bld-linux64-ec2%" group by builders.name order by builders.category, builders.name limit 10;
+---------------------+---------+ | name | seconds | +---------------------+---------+ | alder-linux | 4721 | | alder-linux-debug | 5594 | | alder-linux64 | 4572 | | alder-linux64-debug | 5240 | | ash-android | 3715 | | ash-android-x86 | 3729 | | ash-linux | 4270 | | ash-linux-debug | 7319 | | ash-linux32_gecko | 5859 | | ash-linux64 | 4458 | +---------------------+---------+ 10 rows in set (2.18 sec)
select builders.name, round(sum(unix_timestamp(builds.endtime)-unix_timestamp(builds.starttime))) as total_cpu_sum, count(*) as number_jobs from builds left join (slaves, builders) on (builds.slave_id=slaves.id and builds.builder_id=builders.id) where builds.starttime between '2014-01-01' and '2014-01-31' and slaves.name like "try-linux64-spot%" group by builders.name order by builders.category, builders.name;
+---------------------------------------------+---------------+-------------+ | name | total_cpu_sum | number_jobs | +---------------------------------------------+---------------+-------------+ | b2g_try_emulator-debug_dep | 7440171 | 1071 | | b2g_try_emulator-jb-debug_dep | 7358452 | 926 | | b2g_try_emulator-jb_dep | 7433185 | 982 | | b2g_try_emulator_dep | 7599371 | 1140 | | linux64-br-haz_try_dep | 4214821 | 915 | | linux64-sh-haz_try_dep | 16248 | 9 | | try-android | 3252972 | 882 | | try-android-armv6 | 2969866 | 795 | | try-android-debug | 3921638 | 764 | | try-android-x86 | 2940553 | 797 | | try-linux | 4519388 | 991 | | try-linux-debug | 6238952 | 918 | | try-linux32_gecko | 4102965 | 636 | | try-linux64 | 5673650 | 1477 | | try-linux64-asan | 3349292 | 704 | | try-linux64-asan-debug | 6021367 | 687 | | try-linux64-debug | 5917343 | 935 | | try-linux64-st-an-debug | 2842870 | 707 | | try-linux64-valgrind | 2312107 | 793 | | try-linux64_gecko | 4336260 | 653 | | try_linux64-debug_spidermonkey-exactrooting | 215255 | 78 | | try_linux64-debug_spidermonkey-generational | 696388 | 227 | | try_linux64-debug_spidermonkey-rootanalysis | 932143 | 229 | | try-comm-central-linux | 55167 | 10 | | try-comm-central-linux-debug | 78605 | 13 | | try-comm-central-linux64 | 51194 | 10 | | try-comm-central-linux64-debug | 67658 | 10 | +---------------------------------------------+---------------+-------------+ 27 rows in set (0.30 sec)
SELECT name, green/total*100 as green_pct, orange/total*100 as orange_pct, red/total*100 as red_pct, (total-green-orange-red)/total*100 as other_pct FROM (SELECT builders.name AS name, SUM(CASE result WHEN 0 THEN 1 ELSE 0 END) AS green, SUM(CASE result WHEN 1 THEN 1 ELSE 0 END) AS orange, SUM(CASE result WHEN 2 THEN 1 ELSE 0 END) AS red, count(*) AS total FROM builds LEFT JOIN (slaves, builders) ON (builds.slave_id=slaves.id AND builds.builder_id=builders.id) WHERE builders.category="mozilla-inbound" AND builds.starttime BETWEEN '2014-01-01' AND '2014-01-31' AND slaves.name LIKE "t-w732-%" GROUP BY builders.name) AS derived_table ORDER BY other_pct DESC limit 10;
+-------------------------------------------------------------+-----------+------------+---------+-----------+ | name | green_pct | orange_pct | red_pct | other_pct | +-------------------------------------------------------------+-----------+------------+---------+-----------+ | mozilla-inbound_win7-ix-debug_test-mochitest-browser-chrome | 89.9179 | 7.7374 | 0.0000 | 2.3447 | | mozilla-inbound_win7-ix_test-jetpack | 97.9762 | 0.2381 | 0.4762 | 1.3095 | | mozilla-inbound_win7-ix-debug_test-jetpack | 98.3412 | 0.1185 | 0.3555 | 1.1848 | | mozilla-inbound_win7-ix_test-mochitest-1 | 94.0968 | 4.8406 | 0.0000 | 1.0626 | | mozilla-inbound_win7-ix_test-mochitest-browser-chrome | 95.5952 | 3.4524 | 0.0000 | 0.9524 | | mozilla-inbound_win7-ix_test-jsreftest | 98.0975 | 0.9512 | 0.0000 | 0.9512 | | mozilla-inbound_win7-ix-debug_test-reftest | 95.3791 | 3.6730 | 0.0000 | 0.9479 | | mozilla-inbound_win7-ix-debug_test-mochitest-1 | 94.6698 | 4.4032 | 0.0000 | 0.9270 | | mozilla-inbound_win7-ix_test-xpcshell | 97.5030 | 1.6647 | 0.0000 | 0.8323 | | mozilla-inbound_win7-ix_test-reftest-no-accel | 96.5517 | 2.6159 | 0.0000 | 0.8323 | +-------------------------------------------------------------+-----------+------------+---------+-----------+ 10 rows in set (2.90 sec)
