Analyse der HANA - SSBM Benchmark Log Dateien

Einstellungen

Bitte passen sie die Einstellungen bei Bedarf an.



In [103]:

    
from util import Analyser
LOG_PATH_ROOT="./"
analyser = Analyser(LOG_PATH_ROOT + "logMain.log")

Allgemein:

Im folgenden wird die allgemeine Laufzeit des Benchmarks analysiert mit folgenden Testspezifikationen

Wiederholungen:



In [104]:

    
print(analyser.get_repetitions())

CPU: Intel i7 4.2ghz

CPU Kerne: 6

RAM: 8GB

Zeilenbasiert

Im folgenden wird die allgemeine Laufzeit des Benchmarks bei einem zeilenbasierten Schmata betrachtet.



In [105]:

    
benchmark = analyser.get_row_benchmark()
benchmark.print_stats()









    







Time in msec




Samples
250


Average
1800


Min
1716


Max
2010


Median
1794


Standard Deviation
44


Total
450104



Table: General Data - Row Benchmark

Spaltenbasiert

Im folgenden wird die allgemeine Laufzeit des Benchmarks bei einem spaltenbasierten Schemata betrachtet



In [106]:

    
benchmark = analyser.get_column_benchmark()
benchmark.print_stats()









    







Time in msec




Samples
250


Average
376


Min
360


Max
480


Median
373


Standard Deviation
12


Total
93879



Table: General Data - Column Benchmark

Vergleich Zeilenbasiert vs. Spaltenbasiert



In [107]:

    
from util import Comparison
import re

column_benchmark = analyser.get_column_benchmark()
row_benchmark = analyser.get_row_benchmark()
comparison = Comparison(column_benchmark, row_benchmark)

comparison.set_title("Comparison Row- vs. Column Store")
comparison.compare_visually()



In [108]:

    
comparison.set_title("Comparison Row- vs. Column Store in usec")
comparison.compare_raw()









    







Column Benchmark
Row Benchmark




Standard Deviation
12
44


Max
480
2010


Total
93879
450104


Average
376
1800


Median
373
1794


Samples
250
250


Min
360
1716



Table: Comparison Row- vs. Column Store in usec



In [109]:

    
comparison.compare()









    







Column Benchmark
Row Benchmark




Standard Deviation
0.0
1.0


Max
0.0
1.0


Total
0.0
1.0


Average
0.0
1.0


Median
0.0
1.0


Samples
250
250


Min
0.0
1.0



Table: Comparison Row- vs. Column Store in usec



In [110]:

    
comparison.set_title("Comparison Row- vs. Column Store in msec")
comparison.compare(lambda x: x / 1000)









    







Column Benchmark
Row Benchmark




Standard Deviation
0.012
0.044


Max
0.48
2.01


Total
93.879
450.104


Average
0.376
1.8


Median
0.373
1.794


Samples
250
250


Min
0.36
1.716



Table: Comparison Row- vs. Column Store in msec

Vergleich Row Column



In [111]:

    
column_benchmark.set_prefix("column")
row_benchmark.set_prefix("row")
query_stats = column_benchmark.get_query_stats(lambda x: not re.search("\\.", x))
query_stats += row_benchmark.get_query_stats(lambda x: not re.search("\\.", x))
comparison = Comparison(*query_stats)
comparison.set_title("Query Groups - Column vs. Row")
comparison.compare_visually()



In [112]:

    
comparison.set_title("Query Groups - Column vs. Row in msec")
comparison.compare(lambda x: round(x / 1000, 1))









    







column q1
column q2
column q3
column q4
row q1
row q2
row q3
row q4




Standard Deviation
0.0
0.0
0.0
0.0
0.0
0.0
0.0
0.0


Max
0.2
0.1
0.1
0.1
0.5
0.4
0.6
0.6


Total
26.2
15.5
24.1
28.1
105.6
87.9
129.7
126.9


Average
0.1
0.1
0.1
0.1
0.4
0.4
0.5
0.5


Median
0.1
0.1
0.1
0.1
0.4
0.3
0.5
0.5


Samples
250
250
250
250
250
250
250
250


Min
0.1
0.1
0.1
0.1
0.4
0.3
0.5
0.5



Table: Query Groups - Column vs. Row in msec



In [113]:

    
comparison.set_title("Normalized Query Groups - Column vs. Row")
comparison.compare()









    







column q1
column q2
column q3
column q4
row q1
row q2
row q3
row q4




Standard Deviation
0.2
0.0
0.0
0.07
0.8
0.93
1.0
0.67


Max
0.13
0.0
0.05
0.08
0.79
0.65
1.0
0.88


Total
0.09
0.0
0.07
0.11
0.79
0.63
1.0
0.98


Average
0.09
0.0
0.07
0.11
0.79
0.63
1.0
0.97


Median
0.09
0.0
0.07
0.11
0.79
0.63
1.0
0.98


Samples
250
250
250
250
250
250
250
250


Min
0.1
0.0
0.08
0.12
0.8
0.61
1.0
0.98



Table: Normalized Query Groups - Column vs. Row



In [114]:

    
row_benchmark = analyser.get_row_benchmark()

query_stats = row_benchmark.get_query_stats(lambda x: not re.search("\\.", x))
comparison = Comparison(row_benchmark,*query_stats)
comparison.compare_raw()









    







q1
q2
q3
q4
Row Benchmark




Standard Deviation
16
18
19
14
44


Max
507
435
615
553
2010


Total
105632
87928
129672
126873
450104


Average
423
352
519
507
1800


Median
421
350
517
506
1794


Samples
250
250
250
250
250


Min
394
315
480
473
1716



In [115]:

    
column_benchmark = analyser.get_column_benchmark()
row_benchmark = analyser.get_row_benchmark()

row_query_stats = row_benchmark.get_query_stats(lambda x: not re.search("\\.", x))
row_comparison = Comparison(*row_query_stats, name="Row Store")
column_query_stats = column_benchmark.get_query_stats(lambda x: not re.search("\\.", x))
column_comparison = Comparison(*column_query_stats, name="Column Store")

row_comparison.set_title("Queries - Row vs. Column in msec")
row_comparison.compare_with(column_comparison, "Average")









    







q1
q2
q3
q4




Row Store
423
352
519
507


Column Store
105
62
96
112


Difference
318
290
423
395


in %
75.2
82.4
81.5
77.9



Table: Queries - Row vs. Column in msec



In [116]:

    
column_benchmark = analyser.get_column_benchmark()
row_benchmark = analyser.get_row_benchmark()

row_query_stats = row_benchmark.get_query_stats(lambda x: re.search("\\.", x))
row_comparison = Comparison(*row_query_stats, name="Row Store")
column_query_stats = column_benchmark.get_query_stats(lambda x: re.search("\\.", x))
column_comparison = Comparison(*column_query_stats, name="Column Store")

row_comparison.set_title("Queries - Row vs. Column in msec")
row_comparison.compare_with(column_comparison, "Average",
                            f=lambda x: round(x / 1000, 2),
                            ignore=lambda x: x == "in %")









    







q1.1
q1.2
q1.3
q2.1
q2.2
q2.3
q3.1
q3.2
q3.3
q3.4
q4.1
q4.2
q4.3




Row Store
0.16
0.13
0.13
0.14
0.11
0.1
0.23
0.12
0.09
0.09
0.2
0.17
0.12


Column Store
0.04
0.05
0.02
0.03
0.03
0.01
0.03
0.02
0.02
0.02
0.05
0.04
0.02


Difference
0.13
0.09
0.11
0.11
0.09
0.09
0.19
0.09
0.07
0.07
0.15
0.13
0.1


in %
77.9
63.9
83.5
79.1
76.6
92.2
86.2
79.3
75.9
76.4
76.0
75.4
81.6



Table: Queries - Row vs. Column in msec

Stabilität des Benchmarks

Spaltenbasiert



In [117]:

    
from matplotlib import pyplot as plt, mlab

benchmark = analyser.get_column_benchmark()
plt.plot(benchmark.get_times())
plt.show()

Zeilenbasiert



In [118]:

    
benchmark = analyser.get_row_benchmark()
plt.plot(benchmark.get_times())
plt.show()

Betrachtung einzelner Queries

Im folgenden werden die einzelnen Queries des SSBM Benchmarks betrachtet.

Spaltenbasiert



In [119]:

    
from util import display_table
import re

benchmark = analyser.get_column_benchmark()

query_stats = benchmark.get_query_stats(lambda x: not re.search("\\.", x))
comparison = Comparison(*query_stats)
comparison.set_title("Query Group Duration - Column Store")
comparison.compare_visually()



In [120]:

    
query_stats = benchmark.get_query_stats()
comparison = Comparison(*query_stats)
comparison.set_title("Queries Duration - Column Store")
comparison.compare_visually()



In [121]:

    
comparison.set_title("Queries Duration in usec - Column Store")
comparison.compare_raw()









    







q1.1
q1.2
q1.3
q2.1
q2.2
q2.3
q3.1
q3.2
q3.3
q3.4
q4.1
q4.2
q4.3




Standard Deviation
3
4
3
3
2
2
3
3
3
2
4
4
2


Max
57
81
36
45
46
24
47
41
44
33
69
69
36


Total
9028
11964
5325
7149
6476
2021
7837
6025
5337
5148
11974
10813
5695


Average
36
48
21
29
26
8
31
24
21
21
48
43
23


Median
35
46
20
28
25
8
31
23
20
20
46
42
22


Samples
250
250
250
250
250
250
250
250
250
250
250
250
250


Min
34
46
20
26
25
7
29
23
20
20
44
41
22



Table: Queries Duration in usec - Column Store



In [122]:

    
comparison.set_title("Normalized Queries Duration - Column Store")
comparison.compare()









    







q1.1
q1.2
q1.3
q2.1
q2.2
q2.3
q3.1
q3.2
q3.3
q3.4
q4.1
q4.2
q4.3




Standard Deviation
0.5
1.0
0.5
0.5
0.0
0.0
0.5
0.5
0.5
0.0
1.0
1.0
0.0


Max
0.58
1.0
0.21
0.37
0.39
0.0
0.4
0.3
0.35
0.16
0.79
0.79
0.21


Total
0.7
1.0
0.33
0.52
0.45
0.0
0.58
0.4
0.33
0.31
1.0
0.88
0.37


Average
0.7
1.0
0.33
0.53
0.45
0.0
0.57
0.4
0.33
0.33
1.0
0.88
0.38


Median
0.71
1.0
0.32
0.53
0.45
0.0
0.61
0.39
0.32
0.32
1.0
0.89
0.37


Samples
250
250
250
250
250
250
250
250
250
250
250
250
250


Min
0.69
1.0
0.33
0.49
0.46
0.0
0.56
0.41
0.33
0.33
0.95
0.87
0.38



Table: Normalized Queries Duration - Column Store



In [123]:

    
query_stats = benchmark.get_query_stats(lambda x: re.search("[13]\\.", x))
comparison = Comparison(*query_stats)
comparison.set_title("Q1, Q3 Queries Duration - Column Store")
comparison.compare_visually()



In [124]:

    
comparison.set_title("Q1, Q3 Queries Duration in usec - Column Store")
comparison.compare_raw()









    







q1.1
q1.2
q1.3
q3.1
q3.2
q3.3
q3.4




Standard Deviation
3
4
3
3
3
3
2


Max
57
81
36
47
41
44
33


Total
9028
11964
5325
7837
6025
5337
5148


Average
36
48
21
31
24
21
21


Median
35
46
20
31
23
20
20


Samples
250
250
250
250
250
250
250


Min
34
46
20
29
23
20
20



Table: Q1, Q3 Queries Duration in usec - Column Store



In [125]:

    
comparison.set_title("Normalized Q1, Q3 Queries Duration - Column Store")
comparison.compare()









    







q1.1
q1.2
q1.3
q3.1
q3.2
q3.3
q3.4




Standard Deviation
0.5
1.0
0.5
0.5
0.5
0.5
0.0


Max
0.5
1.0
0.06
0.29
0.17
0.23
0.0


Total
0.57
1.0
0.03
0.39
0.13
0.03
0.0


Average
0.56
1.0
0.0
0.37
0.11
0.0
0.0


Median
0.58
1.0
0.0
0.42
0.12
0.0
0.0


Samples
250
250
250
250
250
250
250


Min
0.54
1.0
0.0
0.35
0.12
0.0
0.0



Table: Normalized Q1, Q3 Queries Duration - Column Store



In [126]:

    
query_stats = benchmark.get_query_stats(lambda x: re.search("1\\.", x))
comparison = Comparison(*query_stats)
comparison.set_title("Q1 Queries Duration - Column Store")
comparison.compare_visually()



In [127]:

    
comparison.set_title("Q1 Queries Duration in usec - Column Store")
comparison.compare_raw()









    







q1.1
q1.2
q1.3




Standard Deviation
3
4
3


Max
57
81
36


Total
9028
11964
5325


Average
36
48
21


Median
35
46
20


Samples
250
250
250


Min
34
46
20



Table: Q1 Queries Duration in usec - Column Store



In [128]:

    
comparison.set_title("Normalized Q1 Queries Duration - Column Store")
comparison.compare()









    







q1.1
q1.2
q1.3




Standard Deviation
0.0
1.0
0.0


Max
0.47
1.0
0.0


Total
0.56
1.0
0.0


Average
0.56
1.0
0.0


Median
0.58
1.0
0.0


Samples
250
250
250


Min
0.54
1.0
0.0



Table: Normalized Q1 Queries Duration - Column Store



In [129]:

    
query_stats = benchmark.get_query_stats(lambda x: re.search("3\\.", x))
comparison = Comparison(*query_stats)
comparison.set_title("Q3 Queries Duration - Column Store")
comparison.compare_visually()



In [130]:

    
comparison.set_title("Q3 Queries Duration in usec - Column Store")
comparison.compare_raw()









    







q3.1
q3.2
q3.3
q3.4




Standard Deviation
3
3
3
2


Max
47
41
44
33


Total
7837
6025
5337
5148


Average
31
24
21
21


Median
31
23
20
20


Samples
250
250
250
250


Min
29
23
20
20



Table: Q3 Queries Duration in usec - Column Store



In [131]:

    
comparison.set_title("Normalized Q3 Queries Duration - Column Store")
comparison.compare()









    







q3.1
q3.2
q3.3
q3.4




Standard Deviation
1.0
1.0
1.0
0.0


Max
1.0
0.57
0.79
0.0


Total
1.0
0.33
0.07
0.0


Average
1.0
0.3
0.0
0.0


Median
1.0
0.27
0.0
0.0


Samples
250
250
250
250


Min
1.0
0.33
0.0
0.0



Table: Normalized Q3 Queries Duration - Column Store

Zeilenbasiert



In [132]:

    
benchmark = analyser.get_row_benchmark()
query_stats = benchmark.get_query_stats(lambda x: not re.search("\\.", x))
comparison = Comparison(*query_stats)
comparison.set_title("Query Group Duration - Row Store")
comparison.compare_visually()



In [133]:

    
query_stats = benchmark.get_query_stats()
comparison = Comparison(*query_stats)
comparison.set_title("Queries Duration - Row Store")
comparison.compare_visually()



In [134]:

    
comparison.set_title("Queries Duration in usec - Row Store")
comparison.compare_raw()









    







q1.1
q1.2
q1.3
q2.1
q2.2
q2.3
q3.1
q3.2
q3.3
q3.4
q4.1
q4.2
q4.3




Standard Deviation
11
9
9
11
10
10
11
8
8
8
9
10
9


Max
200
160
165
168
143
133
276
145
116
121
236
219
154


Total
40695
33236
31729
34644
27643
25475
56133
29046
21640
22253
50067
43774
31311


Average
163
133
127
139
111
102
225
116
87
89
200
175
125


Median
162
132
125
138
110
101
223
115
84
87
199
173
124


Samples
250
250
250
250
250
250
250
250
250
250
250
250
250


Min
141
116
109
119
96
88
206
102
78
77
181
155
109



Table: Queries Duration in usec - Row Store



In [135]:

    
comparison.set_title("Normalized Queries Duration - Row Store")
comparison.compare()









    







q1.1
q1.2
q1.3
q2.1
q2.2
q2.3
q3.1
q3.2
q3.3
q3.4
q4.1
q4.2
q4.3




Standard Deviation
1.0
0.33
0.33
1.0
0.67
0.67
1.0
0.0
0.0
0.0
0.33
0.67
0.33


Max
0.53
0.28
0.31
0.33
0.17
0.11
1.0
0.18
0.0
0.03
0.75
0.64
0.24


Total
0.55
0.34
0.29
0.38
0.17
0.11
1.0
0.21
0.0
0.02
0.82
0.64
0.28


Average
0.55
0.33
0.29
0.38
0.17
0.11
1.0
0.21
0.0
0.01
0.82
0.64
0.28


Median
0.56
0.35
0.29
0.39
0.19
0.12
1.0
0.22
0.0
0.02
0.83
0.64
0.29


Samples
250
250
250
250
250
250
250
250
250
250
250
250
250


Min
0.5
0.3
0.25
0.33
0.15
0.09
1.0
0.19
0.01
0.0
0.81
0.6
0.25



Table: Normalized Queries Duration - Row Store



In [136]:

    
query_stats = benchmark.get_query_stats(lambda x: re.search("[13]\\.", x))
comparison = Comparison(*query_stats)
comparison.set_title("Q1, Q3 Queries Duration - Row Store")
comparison.compare_visually()



In [137]:

    
comparison.set_title("Q1, Q3 Queries Duration in usec - Row Store")
comparison.compare_raw()









    







q1.1
q1.2
q1.3
q3.1
q3.2
q3.3
q3.4




Standard Deviation
11
9
9
11
8
8
8


Max
200
160
165
276
145
116
121


Total
40695
33236
31729
56133
29046
21640
22253


Average
163
133
127
225
116
87
89


Median
162
132
125
223
115
84
87


Samples
250
250
250
250
250
250
250


Min
141
116
109
206
102
78
77



Table: Q1, Q3 Queries Duration in usec - Row Store



In [138]:

    
comparison.set_title("Normalized Q1, Q3 Queries Duration - Row Store")
comparison.compare()









    







q1.1
q1.2
q1.3
q3.1
q3.2
q3.3
q3.4




Standard Deviation
1.0
0.33
0.33
1.0
0.0
0.0
0.0


Max
0.53
0.28
0.31
1.0
0.18
0.0
0.03


Total
0.55
0.34
0.29
1.0
0.21
0.0
0.02


Average
0.55
0.33
0.29
1.0
0.21
0.0
0.01


Median
0.56
0.35
0.29
1.0
0.22
0.0
0.02


Samples
250
250
250
250
250
250
250


Min
0.5
0.3
0.25
1.0
0.19
0.01
0.0



Table: Normalized Q1, Q3 Queries Duration - Row Store



In [139]:

    
query_stats = benchmark.get_query_stats(lambda x: re.search("3\\.", x))
comparison = Comparison(*query_stats)
comparison.set_title("Q3 Queries Duration - Row Store")
comparison.compare_visually()



In [140]:

    
comparison.set_title("Q3 Queries Duration in usec - Row Store")
comparison.compare_raw()









    







q3.1
q3.2
q3.3
q3.4




Standard Deviation
11
8
8
8


Max
276
145
116
121


Total
56133
29046
21640
22253


Average
225
116
87
89


Median
223
115
84
87


Samples
250
250
250
250


Min
206
102
78
77



Table: Q3 Queries Duration in usec - Row Store



In [141]:

    
comparison.set_title("Normalized Q3 Queries Duration - Column Store")
comparison.compare()









    







q3.1
q3.2
q3.3
q3.4




Standard Deviation
1.0
0.0
0.0
0.0


Max
1.0
0.18
0.0
0.03


Total
1.0
0.21
0.0
0.02


Average
1.0
0.21
0.0
0.01


Median
1.0
0.22
0.0
0.02


Samples
250
250
250
250


Min
1.0
0.19
0.01
0.0



Table: Normalized Q3 Queries Duration - Column Store



In [142]:

    
query_stats = benchmark.get_query_stats(lambda x: re.search("1\\.", x))
comparison = Comparison(*query_stats)
comparison.set_title("Q1 Queries Duration - Row Store")
comparison.compare_visually()



In [143]:

    
comparison.set_title("Q1 Queries Duration in usec - Row Store")
comparison.compare_raw()









    







q1.1
q1.2
q1.3




Standard Deviation
11
9
9


Max
200
160
165


Total
40695
33236
31729


Average
163
133
127


Median
162
132
125


Samples
250
250
250


Min
141
116
109



Table: Q1 Queries Duration in usec - Row Store



In [144]:

    
comparison.set_title("Normalized Q1 Queries Duration - Column Store")
comparison.compare()









    







q1.1
q1.2
q1.3




Standard Deviation
1.0
0.0
0.0


Max
1.0
0.0
0.12


Total
1.0
0.17
0.0


Average
1.0
0.17
0.0


Median
1.0
0.19
0.0


Samples
250
250
250


Min
1.0
0.22
0.0



Table: Normalized Q1 Queries Duration - Column Store

Indizes

Vergleich von allen Messwerten



In [145]:

    
column_benchmark = analyser.get_column_benchmark()
index_column_benchmark = analyser.get_column_benchmark_I()
row_benchmark = analyser.get_row_benchmark()
index_row_benchmark = analyser.get_row_benchmark_I()
comparison = Comparison(column_benchmark, index_column_benchmark, row_benchmark, index_row_benchmark)

comparison.compare_raw()









    







Column Benchmark
Column Benchmark with Index
Row Benchmark
Row Benchmark with Index




Standard Deviation
12
12
44
36


Max
480
354
2010
1701


Total
93879
72371
450104
378304


Average
376
289
1800
1513


Median
373
286
1794
1508


Samples
250
250
250
250


Min
360
272
1716
1456



In [146]:

    
comparison.compare()









    







Column Benchmark
Column Benchmark with Index
Row Benchmark
Row Benchmark with Index




Standard Deviation
0.0
0.0
1.0
0.75


Max
0.08
0.0
1.0
0.81


Total
0.06
0.0
1.0
0.81


Average
0.06
0.0
1.0
0.81


Median
0.06
0.0
1.0
0.81


Samples
250
250
250
250


Min
0.06
0.0
1.0
0.82

Spaltenbasiert



In [147]:

    
column_benchmark = analyser.get_column_benchmark()
index_column_benchmark = analyser.get_column_benchmark_I()
comparison = Comparison(column_benchmark, index_column_benchmark)

comparison.set_title("Comparison of Indices - Column Store")
comparison.compare_visually()



In [148]:

    
comparison.set_title("Comparison of Indices in usec - Column Store")
comparison.compare_raw()









    







Column Benchmark
Column Benchmark with Index




Standard Deviation
12
12


Max
480
354


Total
93879
72371


Average
376
289


Median
373
286


Samples
250
250


Min
360
272



Table: Comparison of Indices in usec - Column Store



In [149]:

    
from util import Analyser
LOG_PATH_ROOT="./"
analyser2 = Analyser(LOG_PATH_ROOT + "logOlap.log")

column_benchmark = analyser2.get_column_benchmark()
index_column_benchmark = analyser2.get_column_benchmark_I()
column_benchmark_o = analyser2.get_column_benchmark_olap()
index_column_benchmark_o = analyser2.get_column_benchmark_I_olap()

query_stats_no = column_benchmark.get_query_stats(lambda x: re.search("1\\.2", x))
query_stats = index_column_benchmark.get_query_stats(lambda x: re.search("1\\.2", x))
query_stats_no_olap = column_benchmark_o.get_query_stats(lambda x: re.search("1\\.2", x))
query_stats_olap = index_column_benchmark_o.get_query_stats(lambda x: re.search("1\\.2", x))
comparison = Comparison(*query_stats_no)
comparison.set_title("No index, no OLAP")
comparison.compare_raw()

comparison2 = Comparison(*query_stats)
comparison2.set_title("Index, no OLAP")
comparison2.compare_raw()

comparison3 = Comparison(*query_stats_no_olap)
comparison3.set_title("No index, OLAP")
comparison3.compare_raw()

comparison4 = Comparison(*query_stats_olap)
comparison4.set_title("Index, OLAP")
comparison4.compare_raw()









    







q1.2




Standard Deviation
10


Max
44


Total
1605


Average
16


Median
11


Samples
100


Min
10



Table: No index, no OLAP







    







q1.2




Standard Deviation
0


Max
9


Total
647


Average
6


Median
6


Samples
100


Min
6



Table: Index, no OLAP







    







q1.2




Standard Deviation
0


Max
8


Total
639


Average
6


Median
6


Samples
100


Min
6



Table: No index, OLAP







    







q1.2




Standard Deviation
0


Max
8


Total
647


Average
6


Median
6


Samples
100


Min
6



Table: Index, OLAP



In [150]:

    
comparison = Comparison(index_column_benchmark,column_benchmark, column_benchmark_o, index_column_benchmark_o)
comparison.compare_raw()









    







Column Benchmark
Column Benchmark OLAP
Column Benchmark with Index
Column Benchmark with Index OLAP




Standard Deviation
133
8
7
7


Max
1145
221
275
216


Total
29649
18685
22645
18847


Average
296
187
226
188


Median
243
187
225
189


Samples
100
100
100
100


Min
228
171
216
174

Zeilenbasiert



In [166]:

    
from util import Analyser
LOG_PATH_ROOT="./"
analyser = Analyser(LOG_PATH_ROOT + "logMain.log")

r_b = analyser.get_row_benchmark()
i_r_b = analyser.get_row_benchmark_I()
col_b = analyser.get_column_benchmark()
i_col_b = analyser.get_column_benchmark_I()
comparison = Comparison(r_b, i_r_b)

comparison.set_title("Comparison of Indices - Row Store")
comparison.compare_visually()



In [172]:

    
comparison.set_title("Comparison of Indices in msec - Row Store")
comparison.compare_raw()

q = r_b.get_query_stats(lambda x: not re.search("\\.", x))
q_i = i_r_b.get_query_stats(lambda x: not re.search("\\.", x))

q2 = r_b.get_query_stats(lambda x: re.search("2\\.", x))
q_i2 = i_r_b.get_query_stats(lambda x: re.search("2\\.", x))

q4 = r_b.get_query_stats(lambda x: re.search("4\\.", x))
q_i4 = i_r_b.get_query_stats(lambda x: re.search("4\\.", x))

compQ = Comparison(*q)
compQ.set_title("Queries without indizes")
compQi = Comparison(*q_i)
compQi.set_title("Queries with indizes")
                               
compQ2 = Comparison(*q2)
compQ2.set_title("Query 2 without indizes")
compQi2 = Comparison(*q_i2)
compQi2.set_title("Query 2 with indizes")
                               
compQ4 = Comparison(*q4)
compQ4.set_title("Query 4 without indizes")
compQi4 = Comparison(*q_i4)
compQi4.set_title("Query 4 with indizes")

compQ.compare_raw()
compQi.compare_raw()
                               
compQ2.compare_raw()
compQi2.compare_raw()
                               
compQ4.compare_raw()
compQi4.compare_raw()









    







Row Benchmark
Row Benchmark with Index




Standard Deviation
44
36


Max
2010
1701


Total
450104
378304


Average
1800
1513


Median
1794
1508


Samples
250
250


Min
1716
1456



Table: Comparison of Indices in msec - Row Store







    







q1
q2
q3
q4




Standard Deviation
16
18
19
14


Max
507
435
615
553


Total
105632
87928
129672
126873


Average
423
352
519
507


Median
421
350
517
506


Samples
250
250
250
250


Min
394
315
480
473



Table: Queries without indizes







    







q1
q2
q3
q4




Standard Deviation
10
8
18
24


Max
206
126
561
899


Total
42414
22615
118288
194987


Average
170
90
473
780


Median
170
87
469
776


Samples
250
250
250
250


Min
149
82
435
740



Table: Queries with indizes







    







q2.1
q2.2
q2.3




Standard Deviation
11
10
10


Max
168
143
133


Total
34644
27643
25475


Average
139
111
102


Median
138
110
101


Samples
250
250
250


Min
119
96
88



Table: Query 2 without indizes







    







q2.1
q2.2
q2.3




Standard Deviation
8
4
1


Max
102
38
15


Total
18220
4015
888


Average
73
16
4


Median
69
15
3


Samples
250
250
250


Min
65
14
3



Table: Query 2 with indizes







    







q4.1
q4.2
q4.3




Standard Deviation
9
10
9


Max
236
219
154


Total
50067
43774
31311


Average
200
175
125


Median
199
173
124


Samples
250
250
250


Min
181
155
109



Table: Query 4 without indizes







    







q4.1
q4.2
q4.3




Standard Deviation
13
14
5


Max
468
371
70


Total
105261
80002
9617


Average
421
320
38


Median
419
319
37


Samples
250
250
250


Min
390
281
36



Table: Query 4 with indizes

compCol = Comparison(col_b, i_col_b)

compCol.set_title("Comparison of Indices - Column Store") compCol.compare_visually() compCol.compare_raw()

q = col_b.get_query_stats(lambda x: not re.search("\.", x)) q_i = i_col_b.get_query_stats(lambda x: not re.search("\.", x))

q2 = col_b.get_query_stats(lambda x: re.search("2\.", x)) q_i2 = i_col_b.get_query_stats(lambda x: re.search("2\.", x))

q4 = col_b.get_query_stats(lambda x: re.search("4\.", x)) q_i4 = i_col_b.get_query_stats(lambda x: re.search("4\.", x))

compQ = Comparison(q) compQ.set_title("Queries without indizes") compQi = Comparison(q_i) compQi.set_title("Queries with indizes")

compQ2 = Comparison(q2) compQ2.set_title("Query 2 without indizes") compQi2 = Comparison(q_i2) compQi2.set_title("Query 2 with indizes")

compQ4 = Comparison(q4) compQ4.set_title("Query 4 without indizes") compQi4 = Comparison(q_i4) compQi4.set_title("Query 4 with indizes")

compQ.compare_raw() compQi.compare_raw()

compQ2.compare_raw() compQi2.compare_raw()

compQ4.compare_raw() compQi4.compare_raw()

Hardware Vergleich

Einstellungen



In [ ]:

    
a6 = Analyser(LOG_PATH_ROOT + "12gb6cores.log")
a4 = Analyser(LOG_PATH_ROOT + "i7-4core.log")
a2 = Analyser(LOG_PATH_ROOT + "i7-2core.log")

aGb12 = Analyser(LOG_PATH_ROOT + "12gb6cores.log")
aGb8 = Analyser(LOG_PATH_ROOT + "8gb6cores.log")
aGb6 = Analyser(LOG_PATH_ROOT + "6gb6cores.log")

Spaltenbasiert

CPU



In [ ]:

    
b6 = a6.get_column_benchmark()
b4 = a4.get_column_benchmark()
b2 = a2.get_column_benchmark()

b6.set_name("6 cores")
b4.set_name("4 cores")
b2.set_name("2 cores")

comparison = Comparison(b2, b4, b6)
comparison.set_title("Comparison of CPUs - Column Store")
comparison.compare_visually()



In [ ]:

    
comparison.set_title("Comparison of CPUs in usec - Column Store")
comparison.compare_raw()



In [ ]:

    
comparison.set_title("Normalized Comparison of CPUs - Column Store")
comparison.compare()

RAM



In [ ]:

    
b12 = aGb12.get_column_benchmark()
b8 = aGb8.get_column_benchmark()
b6 = aGb6.get_column_benchmark()

b12.set_name("12 Gb")
b8.set_name("8 Gb")
b6.set_name("6 Gb")

comparison = Comparison(b6, b8, b12)
comparison.set_title("Comparison of RAM - Column Store")
comparison.compare_visually()



In [ ]:

    
comparison.set_title("Comparison of RAM in usec - Column Store")
comparison.compare_raw()



In [ ]:

    
comparison.set_title("Normalized Comparison of RAM - Column Store")
comparison.compare()

Zeilenbasiert

CPU



In [ ]:

    
b6 = a6.get_row_benchmark()
b4 = a4.get_row_benchmark()
b2 = a2.get_row_benchmark()

b6.set_name("6 cores")
b4.set_name("4 cores")
b2.set_name("2 cores")

comparison = Comparison(b2, b4, b6)
comparison.set_title("Comparison of CPUs - Row Store")
comparison.compare_visually()



In [ ]:

    
comparison.set_title("Comparison of CPUs in usec - Row Store")
comparison.compare_raw()

RAM



In [ ]:

    
b12 = aGb12.get_row_benchmark()
b8 = aGb8.get_row_benchmark()
b6 = aGb6.get_row_benchmark()

b12.set_name("12 Gb")
b8.set_name("8 Gb")
b6.set_name("6 Gb")

comparison = Comparison(b6, b8, b12)
comparison.set_title("Comparison of RAM - Row Store")
comparison.compare_visually()



In [ ]:

    
comparison.set_title("Comparison of RAM in usec - Row Store")
comparison.compare_raw()



In [ ]:

    
comparison.set_title("Normalized Comparison of RAM - Row Store")
comparison.compare()

	Time in msec
Samples	250
Average	1800
Min	1716
Max	2010
Median	1794
Standard Deviation	44
Total	450104

	Column Benchmark	Row Benchmark
Standard Deviation	0.012	0.044
Max	0.48	2.01
Total	93.879	450.104
Average	0.376	1.8
Median	0.373	1.794
Samples	250	250
Min	0.36	1.716

	column q1	column q2	column q3	column q4	row q1	row q2	row q3	row q4
Standard Deviation	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Max	0.2	0.1	0.1	0.1	0.5	0.4	0.6	0.6
Total	26.2	15.5	24.1	28.1	105.6	87.9	129.7	126.9
Average	0.1	0.1	0.1	0.1	0.4	0.4	0.5	0.5
Median	0.1	0.1	0.1	0.1	0.4	0.3	0.5	0.5
Samples	250	250	250	250	250	250	250	250
Min	0.1	0.1	0.1	0.1	0.4	0.3	0.5	0.5

	column q1	column q2	column q3	column q4	row q1	row q2	row q3	row q4
Standard Deviation	0.2	0.0	0.0	0.07	0.8	0.93	1.0	0.67
Max	0.13	0.0	0.05	0.08	0.79	0.65	1.0	0.88
Total	0.09	0.0	0.07	0.11	0.79	0.63	1.0	0.98
Average	0.09	0.0	0.07	0.11	0.79	0.63	1.0	0.97
Median	0.09	0.0	0.07	0.11	0.79	0.63	1.0	0.98
Samples	250	250	250	250	250	250	250	250
Min	0.1	0.0	0.08	0.12	0.8	0.61	1.0	0.98

	q1	q2	q3	q4
Row Store	423	352	519	507
Column Store	105	62	96	112
Difference	318	290	423	395
in %	75.2	82.4	81.5	77.9

	q1.1	q1.2	q1.3	q2.1	q2.2	q2.3	q3.1	q3.2	q3.3	q3.4	q4.1	q4.2	q4.3
Row Store	0.16	0.13	0.13	0.14	0.11	0.1	0.23	0.12	0.09	0.09	0.2	0.17	0.12
Column Store	0.04	0.05	0.02	0.03	0.03	0.01	0.03	0.02	0.02	0.02	0.05	0.04	0.02
Difference	0.13	0.09	0.11	0.11	0.09	0.09	0.19	0.09	0.07	0.07	0.15	0.13	0.1
in %	77.9	63.9	83.5	79.1	76.6	92.2	86.2	79.3	75.9	76.4	76.0	75.4	81.6

	q1.1	q1.2	q1.3	q2.1	q2.2	q2.3	q3.1	q3.2	q3.3	q3.4	q4.1	q4.2	q4.3
Standard Deviation	3	4	3	3	2	2	3	3	3	2	4	4	2
Max	57	81	36	45	46	24	47	41	44	33	69	69	36
Total	9028	11964	5325	7149	6476	2021	7837	6025	5337	5148	11974	10813	5695
Average	36	48	21	29	26	8	31	24	21	21	48	43	23
Median	35	46	20	28	25	8	31	23	20	20	46	42	22
Samples	250	250	250	250	250	250	250	250	250	250	250	250	250
Min	34	46	20	26	25	7	29	23	20	20	44	41	22

	q1.1	q1.2	q1.3	q2.1	q2.2	q2.3	q3.1	q3.2	q3.3	q3.4	q4.1	q4.2	q4.3
Standard Deviation	0.5	1.0	0.5	0.5	0.0	0.0	0.5	0.5	0.5	0.0	1.0	1.0	0.0
Max	0.58	1.0	0.21	0.37	0.39	0.0	0.4	0.3	0.35	0.16	0.79	0.79	0.21
Total	0.7	1.0	0.33	0.52	0.45	0.0	0.58	0.4	0.33	0.31	1.0	0.88	0.37
Average	0.7	1.0	0.33	0.53	0.45	0.0	0.57	0.4	0.33	0.33	1.0	0.88	0.38
Median	0.71	1.0	0.32	0.53	0.45	0.0	0.61	0.39	0.32	0.32	1.0	0.89	0.37
Samples	250	250	250	250	250	250	250	250	250	250	250	250	250
Min	0.69	1.0	0.33	0.49	0.46	0.0	0.56	0.41	0.33	0.33	0.95	0.87	0.38

	q1.1	q1.2	q1.3	q3.1	q3.2	q3.3	q3.4
Standard Deviation	0.5	1.0	0.5	0.5	0.5	0.5	0.0
Max	0.5	1.0	0.06	0.29	0.17	0.23	0.0
Total	0.57	1.0	0.03	0.39	0.13	0.03	0.0
Average	0.56	1.0	0.0	0.37	0.11	0.0	0.0
Median	0.58	1.0	0.0	0.42	0.12	0.0	0.0
Samples	250	250	250	250	250	250	250
Min	0.54	1.0	0.0	0.35	0.12	0.0	0.0

	q1.1	q1.2	q1.3
Standard Deviation	0.0	1.0	0.0
Max	0.47	1.0	0.0
Total	0.56	1.0	0.0
Average	0.56	1.0	0.0
Median	0.58	1.0	0.0
Samples	250	250	250
Min	0.54	1.0	0.0

	q1.1	q1.2	q1.3	q2.1	q2.2	q2.3	q3.1	q3.2	q3.3	q3.4	q4.1	q4.2	q4.3
Standard Deviation	11	9	9	11	10	10	11	8	8	8	9	10	9
Max	200	160	165	168	143	133	276	145	116	121	236	219	154
Total	40695	33236	31729	34644	27643	25475	56133	29046	21640	22253	50067	43774	31311
Average	163	133	127	139	111	102	225	116	87	89	200	175	125
Median	162	132	125	138	110	101	223	115	84	87	199	173	124
Samples	250	250	250	250	250	250	250	250	250	250	250	250	250
Min	141	116	109	119	96	88	206	102	78	77	181	155	109

	q3.1	q3.2	q3.3	q3.4
Standard Deviation	1.0	0.0	0.0	0.0
Max	1.0	0.18	0.0	0.03
Total	1.0	0.21	0.0	0.02
Average	1.0	0.21	0.0	0.01
Median	1.0	0.22	0.0	0.02
Samples	250	250	250	250
Min	1.0	0.19	0.01	0.0

	Column Benchmark	Column Benchmark with Index	Row Benchmark	Row Benchmark with Index
Standard Deviation	12	12	44	36
Max	480	354	2010	1701
Total	93879	72371	450104	378304
Average	376	289	1800	1513
Median	373	286	1794	1508
Samples	250	250	250	250
Min	360	272	1716	1456

	Column Benchmark	Column Benchmark with Index	Row Benchmark	Row Benchmark with Index
Standard Deviation	0.0	0.0	1.0	0.75
Max	0.08	0.0	1.0	0.81
Total	0.06	0.0	1.0	0.81
Average	0.06	0.0	1.0	0.81
Median	0.06	0.0	1.0	0.81
Samples	250	250	250	250
Min	0.06	0.0	1.0	0.82

	q1.2
Standard Deviation	10
Max	44
Total	1605
Average	16
Median	11
Samples	100
Min	10

	Column Benchmark	Column Benchmark OLAP	Column Benchmark with Index	Column Benchmark with Index OLAP
Standard Deviation	133	8	7	7
Max	1145	221	275	216
Total	29649	18685	22645	18847
Average	296	187	226	188
Median	243	187	225	189
Samples	100	100	100	100
Min	228	171	216	174

	q1	q2	q3	q4
Standard Deviation	16	18	19	14
Max	507	435	615	553
Total	105632	87928	129672	126873
Average	423	352	519	507
Median	421	350	517	506
Samples	250	250	250	250
Min	394	315	480	473

	q1	q2	q3	q4
Standard Deviation	10	8	18	24
Max	206	126	561	899
Total	42414	22615	118288	194987
Average	170	90	473	780
Median	170	87	469	776
Samples	250	250	250	250
Min	149	82	435	740

	q2.1	q2.2	q2.3
Standard Deviation	8	4	1
Max	102	38	15
Total	18220	4015	888
Average	73	16	4
Median	69	15	3
Samples	250	250	250
Min	65	14	3

	q4.1	q4.2	q4.3
Standard Deviation	13	14	5
Max	468	371	70
Total	105261	80002	9617
Average	421	320	38
Median	419	319	37
Samples	250	250	250
Min	390	281	36