Skip to content

Instantly share code, notes, and snippets.

@vicenteg

vicenteg/queries.sql

Last active October 8, 2016 09:48
Show Gist options
  • Save vicenteg/be9571f06c4aab9ae8828088ca830c70 to your computer and use it in GitHub Desktop.
Save vicenteg/be9571f06c4aab9ae8828088ca830c70 to your computer and use it in GitHub Desktop.
Download ZIP
Some pre-install validation tests JSONified and queried with Drill.
Raw
gistfile1.txt
0: jdbc:drill:zk=localhost:2181> -- network performance with rpctest, node to node, round robin
0: jdbc:drill:zk=localhost:2181> select type,count(1) runs,avg(t.rateMBps) avgRateMBPS,avg(t.rpcspersecond) avgrpcspersecond from (select i.type, f.* from dfs.vr.instances_view i join dfs.vr.rpctest_1_1_view f on f.`time` = i.`time` and f.host = i.host) t group by type,`time` order by type ;
+--------------+-------+---------------------+---------------------+
| type | runs | avgRateMBPS | avgrpcspersecond |
+--------------+-------+---------------------+---------------------+
| d2.2xlarge | 12 | 273.3066660563151 | 2085.1624857584634 |
| d2.2xlarge | 12 | 233.72499974568686 | 1783.183344523112 |
| d2.2xlarge | 12 | 195.93666712443033 | 1494.8716837565105 |
| d2.2xlarge | 12 | 239.15333557128906 | 1824.5950215657551 |
| d2.2xlarge | 12 | 275.3549982706706 | 2100.788319905599 |
| d2.4xlarge | 12 | 316.5691680908203 | 2415.2241719563804 |
| d2.4xlarge | 12 | 389.0424995422363 | 2968.13916015625 |
| d2.xlarge | 12 | 107.25 | 818.2428283691406 |
| d2.xlarge | 12 | 128.1233345667521 | 977.4955139160156 |
| d2.xlarge | 4 | null | null |
| d2.xlarge | 4 | null | null |
| d2.xlarge | 4 | null | null |
| d2.xlarge | 4 | null | null |
| m4.10xlarge | 20 | 373.21300048828124 | 2847.389508056641 |
| m4.10xlarge | 20 | 380.6169975280762 | 2903.8789916992187 |
| m4.10xlarge | 20 | 387.7560012817383 | 2958.3465209960937 |
| m4.2xlarge | 20 | 100.20399951934814 | 764.4963500976562 |
| m4.2xlarge | 20 | 91.21250095367432 | 695.9034439086914 |
| m4.4xlarge | 20 | 204.56449966430665 | 1560.7076507568358 |
| m4.xlarge | 4 | null | null |
| m4.xlarge | 4 | null | null |
| m4.xlarge | 12 | 66.71333312988281 | 508.97474670410156 |
| m4.xlarge | 12 | 71.93416627248128 | 548.8011627197266 |
| m4.xlarge | 4 | null | null |
| m4.xlarge | 4 | null | null |
+--------------+-------+---------------------+---------------------+
25 rows selected (3.091 seconds)
0: jdbc:drill:zk=localhost:2181>
0: jdbc:drill:zk=localhost:2181> -- network performance with rpctest, one to many
0: jdbc:drill:zk=localhost:2181> select type,count(1) runs,avg(t.rateMBps) avgRateMBPS,avg(t.rpcspersecond) avgrpcspersecond from (select i.type, f.* from dfs.vr.instances_view i join dfs.vr.rpctest_1_n_view f on f.`time` = i.`time` and f.host = i.host) t group by type,`time` order by type ;
+--------------+-------+---------------------+---------------------+
| type | runs | avgRateMBPS | avgrpcspersecond |
+--------------+-------+---------------------+---------------------+
| d2.2xlarge | 4 | 434.4499969482422 | 3314.5975341796875 |
| d2.2xlarge | 4 | 434.50499725341797 | 3315.0074462890625 |
| d2.2xlarge | 4 | 435.2050094604492 | 3320.3550415039062 |
| d2.2xlarge | 4 | 434.8849868774414 | 3317.9175415039062 |
| d2.2xlarge | 4 | 435.58499908447266 | 3323.24755859375 |
| d2.4xlarge | 4 | 691.5299987792969 | 5275.93994140625 |
| d2.4xlarge | 4 | 652.197509765625 | 4975.8575439453125 |
| d2.xlarge | 4 | 240.91250228881836 | 1838.0174865722656 |
| d2.xlarge | 4 | 240.99750137329102 | 1838.6749877929688 |
| m4.10xlarge | 5 | 623.747998046875 | 4758.81806640625 |
| m4.10xlarge | 5 | 610.6840087890625 | 4659.16396484375 |
| m4.10xlarge | 5 | 637.1300048828125 | 4860.901953125 |
| m4.2xlarge | 5 | 162.3019989013672 | 1238.2760009765625 |
| m4.2xlarge | 5 | 162.5799987792969 | 1240.385986328125 |
| m4.4xlarge | 5 | 343.7219970703125 | 2622.3759765625 |
| m4.xlarge | 4 | 143.5349998474121 | 1095.0899810791016 |
| m4.xlarge | 4 | 146.82749938964844 | 1120.2099914550781 |
+--------------+-------+---------------------+---------------------+
17 rows selected (1.372 seconds)
0: jdbc:drill:zk=localhost:2181>
0: jdbc:drill:zk=localhost:2181> -- memory throughput with triad
0: jdbc:drill:zk=localhost:2181> select type,count(1) runs,avg(triad_throughput_MB) avgtriadthroughputMB from (select i.type, f.* from dfs.vr.instances_view i join dfs.vr.triad_view f on f.`time` = i.`time` and f.host = i.host) group by type,`time` order by type ;
+--------------+-------+-----------------------+
| type | runs | avgtriadthroughputMB |
+--------------+-------+-----------------------+
| d2.2xlarge | 4 | 42757.4916 |
| d2.2xlarge | 4 | 35530.073449999996 |
| d2.2xlarge | 4 | 36054.0682 |
| d2.2xlarge | 4 | 36070.157275 |
| d2.2xlarge | 4 | 36059.6868 |
| d2.4xlarge | 4 | 44175.51885 |
| d2.4xlarge | 4 | 44049.124175000004 |
| d2.xlarge | 4 | 30592.646200000003 |
| d2.xlarge | 4 | 31530.40365 |
| d2.xlarge | 4 | 33929.915 |
| d2.xlarge | 4 | 33601.98267499999 |
| d2.xlarge | 4 | 33719.38395 |
| d2.xlarge | 4 | 33759.4582 |
| m4.10xlarge | 5 | 84963.97760000001 |
| m4.10xlarge | 5 | 84757.67932 |
| m4.10xlarge | 5 | 84686.3965 |
| m4.2xlarge | 5 | 38891.8978 |
| m4.2xlarge | 5 | 40923.26814 |
| m4.4xlarge | 5 | 46911.24192 |
| m4.xlarge | 4 | 31592.164825 |
| m4.xlarge | 4 | 29770.586000000003 |
| m4.xlarge | 4 | 33893.39445 |
| m4.xlarge | 4 | 33843.3224 |
| m4.xlarge | 4 | 34303.0118 |
| m4.xlarge | 4 | 31008.004925 |
+--------------+-------+-----------------------+
25 rows selected (0.94 seconds)
Raw
queries.sql
create or replace view maprfs.validation.fio_view as
select host,`year`,`month`,`day`,`time`,t.job.`job options`.bs blocksize, t.job.`job options`.size size, t.job.`job options`.iodepth iodepth, t.job.write.bw write_bw, t.job.read.bw read_bw from (select dir0 as host, dir1 as `year`,dir2 as `month`,dir3 as `day`,dir4 as `time`,flatten(jobs) job from maprfs.validation.fio) t;
create or replace view maprfs.validation.triad_view as
select dir0 as host,dir1 as `year`,dir2 as `month`,dir3 as `day`,dir4 as `time`,triad_throughput_MB from maprfs.validation.triad;
create or replace view maprfs.validation.rpctest_1_n_view as
select dir0 as host,dir1 as `year`,dir2 as `month`,dir3 as `day`,dir4 as `time`,cast(columns[0] as float) as rateMBps,columns[1] as elapsed,columns[2] as rpcs,cast(columns[3] as float) as rpcspersecond from maprfs.validation.rpctest_1_n;
create or replace view maprfs.validation.rpctest_1_1_view as
select dir0 as host,dir1 as `year`,dir2 as `month`,dir3 as `day`,dir4 as `time`,columns[0] as desthost,cast(columns[1] as float) as rateMBps,columns[2] as elapsed,columns[3] as rpcs,cast(columns[4] as float) as rpcspersecond from maprfs.validation.rpctest_1_1 order by `time`;
create or replace view maprfs.validation.instances_view as
select host,`time`,tt.instance.KeyName keyname,tt.instance.PrivateDnsName fqdn, tt.instance.InstanceType type from (select host,`time`,flatten(t.reservation.Instances) instance from (select dir0 as host, dir4 as `time`,flatten(Reservations) reservation from maprfs.validation.instances) t) tt ;
select i.type, f.* from maprfs.validation.instances_view i join maprfs.validation.fio_view f on f.`time` = i.`time` and f.host = i.host ;
select i.type, f.* from maprfs.validation.instances_view i join maprfs.validation.rpctest_1_1_view f on f.`time` = i.`time` and f.host = i.host ;
select i.type, f.* from maprfs.validation.instances_view i join maprfs.validation.rpctest_1_n_view f on f.`time` = i.`time` and f.host = i.host ;
select i.type, f.* from maprfs.validation.instances_view i join maprfs.validation.triad_view f on f.`time` = i.`time` and f.host = i.host ;
-- disk performance with fio. sequential, 100% read/write
select type,count(1) runs,avg(t.write_bw) avg_write_bw,avg(t.read_bw) avg_read_bw from (select i.type, f.* from maprfs.validation.instances_view i join maprfs.validation.fio_view f on f.`time` = i.`time` and f.host = i.host) t group by type,`time` order by type ;
-- network performance with rpctest, node to node, round robin
select type,count(1) runs,avg(t.rateMBps) avgRateMBPS,avg(t.rpcspersecond) avgrpcspersecond from (select i.type, f.* from maprfs.validation.instances_view i join maprfs.validation.rpctest_1_1_view f on f.`time` = i.`time` and f.host = i.host) t group by type,`time` order by type ;
-- network performance with rpctest, one to many
select type,count(1) runs,avg(t.rateMBps) avgRateMBPS,avg(t.rpcspersecond) avgrpcspersecond from (select i.type, f.* from maprfs.validation.instances_view i join maprfs.validation.rpctest_1_n_view f on f.`time` = i.`time` and f.host = i.host) t group by type,`time` order by type ;
-- memory throughput with triad
select type,count(1) runs,avg(triad_throughput_MB) avgtriadthroughputMB from (select i.type, f.* from maprfs.validation.instances_view i join maprfs.validation.triad_view f on f.`time` = i.`time` and f.host = i.host) group by type,`time` order by type ;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment