1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
|
$run_id_limit = CAST(CurrentUtcTimestamp() AS Uint64) - 30UL * 86400UL * 1000000UL;
$fail_tests = SELECT
Db,
Suite,
RunId,
ListConcat(
ListSort(
AGG_LIST(
IF(
Test = "_Verification",
"Infrastructure error",
IF(
StartsWith(Test, 'Query'),
SubString(CAST(Test AS String), 5U),
CAST(Test AS String)
)
)
)
)
, ", "
) AS FailTests,
FROM `perfomance/olap/tests_results`
WHERE
RunId / 1000 > $run_id_limit
AND COALESCE(CAST(JSON_VALUE(Stats, '$.FailsCount') AS int), 1 - Success) > 0
GROUP BY RunId, Db, Suite;
$diff_tests = SELECT
Db,
Suite,
RunId,
ListConcat(
ListSort(
AGG_LIST(
IF(
StartsWith(Test, 'Query'),
SubString(CAST(Test AS String), 5U),
CAST(Test AS String)
)
)
),
", "
) AS DiffTests,
FROM `perfomance/olap/tests_results`
WHERE
RunId / 1000 > $run_id_limit
AND CAST(JSON_VALUE(Stats, '$.DiffsCount') AS int) > 0
GROUP BY RunId, Db, Suite;
$suites = SELECT
Db,
Suite,
RunId,
MAX_BY(JSON_VALUE(Info, "$.cluster.version"), Success) AS Version,
MAX_BY(JSON_VALUE(Info, "$.ci_version"), Success) AS CiVersion,
MAX_BY(JSON_VALUE(Info, "$.test_tools_version"), Success) AS TestToolsVersion,
MAX_BY(JSON_VALUE(Info, "$.report_url"), Success) AS Report,
SUM_IF(MeanDuration, Success > 0 AND Test not in {"_Verification", "Sum"} AND JSON_VALUE(Stats, '$.import_time') IS NULL) / 1000. AS YdbSumMeans,
SUM_IF(MeanDuration * CAST(JSON_VALUE(Stats, '$.SuccessCount') AS int), Success > 0 AND Test not in {"_Verification", "Sum"} AND JSON_VALUE(Stats, '$.import_time') IS NULL) / 1000. AS QuasiGrossTime,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.time_with_compaction') AS Float)), Test not in {"_Verification", "Sum"}) AS SumImportWithCompactionTime,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.import_time') AS Float)), Test not in {"_Verification", "Sum"}) AS SumImportTime,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.CompilationAvg') AS Float)), Test not in {"_Verification", "Sum"}) AS SumCompilationTime,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.CompilationCPUTime') AS Float)), Test not in {"_Verification", "Sum"}) AS CompilationCPUTime,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.ProcessCPUTime') AS Float)), Test not in {"_Verification", "Sum"}) AS ProcessCPUTime,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.compacted_bytes') AS Float)), Test not in {"_Verification", "Sum"}) AS SumCompactedBytes,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.written_bytes') AS Float)), Test not in {"_Verification", "Sum"}) AS SumWrittenBytes,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.GrossTime') AS float)), Test = 'Sum') AS GrossTime,
COUNTIF(COALESCE(CAST(JSON_VALUE(Stats, '$.FailsCount') AS int), 1 - Success) = 0) AS SuccessCount,
COUNTIF(COALESCE(CAST(JSON_VALUE(Stats, '$.FailsCount') AS int), 1 - Success) > 0) AS FailCount,
AVG_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.import_speed') AS float)), Success > 0 AND Test not in {"_Verification", "Sum"}) / 1. AS AvgImportSpeed,
AVG_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.cpu_cores') AS float)), Success > 0 AND Test not in {"_Verification", "Sum"}) / 1. AS AvgCpuCores,
AVG_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.cpu_time') AS float)), Success > 0 AND Test not in {"_Verification", "Sum"}) / 1. AS AvgCpuTime,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.satisfaction_avg_test_pool_30') AS float)), Success > 0 AND Test not in {"_Verification", "Sum"}) AS Satisfaction30,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.satisfaction_avg_test_pool_40') AS float)), Success > 0 AND Test not in {"_Verification", "Sum"}) AS Satisfaction40,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.satisfaction_avg_test_pool_50') AS float)), Success > 0 AND Test not in {"_Verification", "Sum"}) AS Satisfaction50,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.satisfaction_avg_test_pool_100') AS float)), Success > 0 AND Test not in {"_Verification", "Sum"}) AS Satisfaction100,
SUM_IF(COALESCE(CAST(JSON_VALUE(Stats, '$.tpcc_efficiency') AS float)), Success > 0 AND Test not in {"_Verification", "Sum"}) AS TpccEfficiency,
Min(MIN_OF(Timestamp, CAST(RunId/1000 AS Timestamp))) AS Begin,
Max(Timestamp) AS End,
FROM `perfomance/olap/tests_results`
WHERE RunId / 1000 > $run_id_limit
GROUP BY RunId, Db, Suite;
SELECT
s.Db AS Db,
s.Suite AS Suite,
CAST(s.RunId/1000 AS Timestamp) AS RunTs,
s.Version AS Version,
s.CiVersion AS CiVersion,
s.TestToolsVersion AS TestToolsVersion,
s.Report AS Report,
s.YdbSumMeans AS YdbSumMeans,
s.SumImportTime AS SumImportTime,
s.SumCompilationTime AS SumCompilationTime,
s.CompilationCPUTime AS CompilationCPUTime,
s.ProcessCPUTime AS ProcessCPUTime,
s.SumImportWithCompactionTime AS SumImportWithCompactionTime,
s.SumCompactedBytes AS SumCompactedBytes,
s.SumWrittenBytes AS SumWrittenBytes,
IF(COALESCE(s.GrossTime) > 0, s.GrossTime, s.QuasiGrossTime) AS GrossTime,
s.SuccessCount AS SuccessCount,
s.FailCount AS FailCount,
s.AvgImportSpeed AS AvgImportSpeed,
s.AvgCpuCores AS AvgCpuCores,
s.AvgCpuTime AS AvgCpuTime,
s.Satisfaction30 * IF(s.Satisfaction30 > 1., 1.e-6, 1.) AS Satisfaction30,
s.Satisfaction40 * IF(s.Satisfaction40 > 1., 1.e-6, 1.) AS Satisfaction40,
s.Satisfaction50 * IF(s.Satisfaction50 > 1., 1.e-6, 1.) AS Satisfaction50,
s.Satisfaction100 * IF(s.Satisfaction100 > 1., 1.e-6, 1.) AS Satisfaction100,
s.TpccEfficiency AS TpccEfficiency,
s.Begin AS Begin,
s.End AS End,
d.DiffTests AS DiffTests,
f.FailTests AS FailTests,
CASE
WHEN s.Db LIKE '%sas%' THEN 'sas'
WHEN s.Db LIKE '%vla%' THEN 'vla'
WHEN s.Db LIKE '%klg%' THEN 'klg'
WHEN s.Db LIKE '%etn0vb1kg3p016q1tp3t%' THEN 'cloud'
ELSE 'other'
END AS DbDc,
CASE
WHEN s.Db LIKE '%load%' THEN 'column'
WHEN s.Db LIKE '%/s3%' THEN 's3'
WHEN s.Db LIKE '%/row%' THEN 'row'
ELSE 'other'
END AS DbType,
CASE
WHEN s.Db LIKE '%sas-daily%' THEN 'sas_small_'
WHEN s.Db LIKE '%sas-perf%' THEN 'sas_big_'
WHEN s.Db LIKE '%vla-acceptance%' THEN 'vla_small_'
WHEN s.Db LIKE '%vla-perf%' THEN 'vla_big_'
WHEN s.Db LIKE '%vla4-8154%' OR s.Db LIKE '%vla4-8158%' THEN 'vla_2_node_'
WHEN s.Db LIKE '%vla4-8157%' THEN 'vla_1_node_'
WHEN s.Db LIKE '%vla4-8163%' OR s.Db LIKE '%vla4-8171%' OR s.Db LIKE '%vla4-8174%' THEN 'vla_3_node_'
WHEN s.Db LIKE '%etn0vb1kg3p016q1tp3t%b1ggceeul2pkher8vhb6/etn0vb1kg3p016q1tp3t%' THEN 'cloud_slonnn_128_'
WHEN s.Db LIKE '%etntj9d0t8v7ud2hrqho%b1ggceeul2pkher8vhb6/etntj9d0t8v7ud2hrqho%' THEN 'cloud_slonnn_64_'
WHEN s.Db LIKE '%static-node-1.ydb-cluster.com/Root/db%' THEN 'ansible_'
WHEN s.Db LIKE '%ydb-vla-dev04-002%' THEN 'oltp-vla-perf1_'
WHEN s.Db LIKE '%ydb-vla-dev04-005%' THEN 'oltp-vla-perf2_'
WHEN s.Db LIKE '%ydb-qa-01-klg-010%' THEN 'oltp-klg-perf3_'
WHEN s.Db LIKE '%ydb-qa-01-klg-014%' THEN 'oltp-klg-perf4_'
WHEN s.Db LIKE '%ydb-qa-01-klg-018%' THEN 'oltp-klg-perf5_'
WHEN s.Db LIKE '%ydb-qa-01-sas-000%' THEN 'oltp-3dc-perf6_'
WHEN s.Db LIKE '%ydb-qa-01-klg-021%' THEN 'oltp-klg-perf7_'
WHEN s.Db LIKE '%ydb-qa-01-klg-030%' THEN 'oltp-klg-perf9_'
WHEN s.Db LIKE '%sas%' THEN 'sas_'
WHEN s.Db LIKE '%vla%' THEN 'vla_'
WHEN s.Db LIKE '%klg%' THEN 'klg_'
ELSE 'new_db_'
END || CASE
WHEN s.Db LIKE '%load%' THEN 'column'
WHEN s.Db LIKE '%/s3%' THEN 's3'
WHEN s.Db LIKE '%/row%' THEN 'row'
ELSE 'other'
END AS DbAlias,
COALESCE(SubString(CAST(s.Version AS String), 0U, RFIND(CAST(s.Version AS String), '.')), 'unknown') As Branch,
COALESCE(SubString(CAST(s.CiVersion AS String), 0U, RFIND(CAST(s.CiVersion AS String), '.')), 'unknown') As CiBranch,
COALESCE(SubString(CAST(s.TestToolsVersion AS String), 0U, RFIND(CAST(s.TestToolsVersion AS String), '.')), 'unknown') As TestToolsBranch
FROM $suites AS s
LEFT JOIN $diff_tests AS d ON s.RunId = d.RunId AND s.Db = d.Db AND s.Suite = d.Suite
LEFT JOIN $fail_tests AS f ON s.RunId = f.RunId AND s.Db = f.Db AND s.Suite = f.Suite
|
