Table of Contents

  • Load Evaluation of Benchmarks
    • Inspect Result Folder
    • Pick an Experiment and load it
    • Load general Properties into Variables
  • Show Properties of the Workload
    • Show Properties of a DBMS
    • Show Properties of a Query
      • Show Errors
      • Show Warnings
      • Show Query Template
      • Show Query Parameters
      • Show Query as being Run
      • Show Query as being Run by another DBMS
      • Show Result Set
      • Show Result Set from another DBMS
  • Some Measures of the Workload
    • Hardware Metrics
      • List all available Metrics
      • Get Hardware Metrics for Loading Test
      • Get Hardware Metrics per Stream
    • Timing Measures
      • Mean of Means of Timer Run
      • Geometric Mean of Medians of Timer Run
    • Plots
  • Some Measures per Query
    • Timing Measures
      • Means of Timer Runs
      • Maximum of Run Throughput
      • Latency of Timer Execution
      • Mean of Latency of Timer Execution per DBMS
      • Coefficient of Variation of Latency of Timer Execution per DBMS
      • Latency of Timer Connection
      • Latency of Timer Data Transfer
      • Latency of Timer Run - normalized to 1 per Query
      • Size of Result Sets per Query
      • Size of Result Sets per Query - normalized to 1
      • Size of Result Sets per Query - normalized to 1
      • Table of Errors
      • Table of Warnings
      • Total Time [s] per Query
      • Total Time per Query - normalized to 100%
      • Total Time per Query - normalized to 100%
    • Plots
  • Inspect Single Queries
    • Measures
      • Measures of Execution Times
    • Statistics
      • Statistics of Execution Times
    • Plots
      • Timer Run - Line Plot
      • Mean of Timer Run - Bar Plot
      • Timer Run - Boxplot
      • Timer Run Histogram

Load Evaluation of Benchmarks¶

Import some libraries

In [1]:
from dbmsbenchmarker import *
import pandas as pd
pd.set_option("display.max_rows", None)
pd.set_option('display.max_colwidth', None)

# Some plotly figures
import plotly.graph_objects as go
import plotly.figure_factory as ff

# Some nice output
from IPython.display import display, Markdown

import logging
logging.basicConfig(level=logging.INFO)

Inspect Result Folder¶

In [2]:
# path of folder containing experiment results
resultfolder = "/results/"

# create evaluation object for result folder
evaluate = inspector.inspector(resultfolder)

# list of all experiments in folder
# evaluate.list_experiments
# dataframe of experiments
evaluate.get_experiments_preview()
Out[2]:
connections info intro name queries time
1625255968 8 This experiment compares run time and resource consumption of TPC-H queries in different DBMS. This includes the reading queries of TPC-H. TPC-H Queries SF=10 22 2022-06-01 11:55:45

Pick an Experiment and load it¶

In [3]:
# last Experiment
code = evaluate.list_experiments[len(evaluate.list_experiments)-1]

# Specific Experiment
code = '1625255968'

# load it
evaluate.load_experiment(code)

Benchmarking in folder docresult//1625255968
Connections:
MariaDB-2-1
PostgreSQL-1-1
MonetDB-1-1
MySQL-1-1
MariaDB-1-1
MySQL-2-1
PostgreSQL-2-1
MonetDB-2-1
Queries:
0: Q1 = Pricing Summary Report (TPC-H Q1)
1: Q2 = Minimum Cost Supplier Query (TPC-H Q2)
2: Q3 = Shipping Priority (TPC-H Q3)
3: Q4 = Order Priority Checking Query (TPC-H Q4)
4: Q5 = Local Supplier Volume (TPC-H Q5)
5: Q6 = Forecasting Revenue Change (TPC-H Q6)
6: Q7 = Forecasting Revenue Change (TPC-H Q7)
7: Q8 = National Market Share (TPC-H Q8)
8: Q9 = Product Type Profit Measure (TPC-H Q9)
9: Q10 = Forecasting Revenue Change (TPC-H Q10)
10: Q11 = Important Stock Identification (TPC-H Q11)
11: Q12 = Shipping Modes and Order Priority (TPC-H Q12)
12: Q13 = Customer Distribution (TPC-H Q13)
13: Q14 = Forecasting Revenue Change (TPC-H Q14)
14: Q15 = Top Supplier Query (TPC-H Q15)
15: Q16 = Parts/Supplier Relationship (TPC-H Q16)
16: Q17 = Small-Quantity-Order Revenue (TPC-H Q17)
17: Q18 = Large Volume Customer (TPC-H Q18)
18: Q19 = Discounted Revenue (TPC-H Q19)
19: Q20 = Potential Part Promotion (TPC-H Q20)
20: Q21 = Suppliers Who Kept Orders Waiting Query (TPC-H Q21)
21: Q22 = Global Sales Opportunity Query (TPC-H Q22)
Load Evaluation

Load general Properties into Variables¶

In [4]:
# get experiment workflow
df = evaluate.get_experiment_workflow()
#print(df)

# get workload properties
workload_properties = evaluate.get_experiment_workload_properties()
print(workload_properties['name'])
print(workload_properties['info'])

# list queries
list_queries = evaluate.get_experiment_list_queries()

# list connections
list_nodes = evaluate.get_experiment_list_nodes()
list_dbms = evaluate.get_experiment_list_dbms()
list_connections = evaluate.get_experiment_list_connections()
list_connections_node = evaluate.get_experiment_list_connections_by_node()
list_connections_dbms = evaluate.get_experiment_list_connections_by_dbms()
list_connections_clients = evaluate.get_experiment_list_connections_by_connectionmanagement('numProcesses')
list_connections_gpu = evaluate.get_experiment_list_connections_by_hostsystem('GPU')
list_connections_dockerimage = evaluate.get_experiment_list_connections_by_parameter('dockerimage')

# colors by dbms
list_connections_dbms = evaluate.get_experiment_list_connections_by_dbms()
connection_colors = evaluate.get_experiment_list_connection_colors(list_connections_dbms)

# fix some examples:
# first connection, first query, first run
connection = list_connections[0]
numQuery = 1
numRun = 0

TPC-H Queries SF=10
This experiment compares run time and resource consumption of TPC-H queries in different DBMS.

Show Properties of the Workload¶

Show Properties of a DBMS¶

In [5]:
connection = 'MonetDB-1-1'

display(Markdown("### Properties of {}".format(connection)))

evaluator.pretty(evaluate.get_experiment_connection_properties(connection))

Properties of MonetDB-1-1¶

name:MonetDB-1-1
script:SF10-index
docker:MonetDB
docker_alias:Columnwise
version:11.37.11
info:[]
connectionmanagement
  numProcesses:1
  runsPerConnection:Unlimited
  timeout:3600
hostsystem
  RAM:1082007052288
  CPU:AMD EPYC 7742 64-Core Processor
  GPU:
  GPUIDs:[]
  Cores:256
  host:5.4.0-74-generic
  node:cl-worker28
  disk:623220008
  datadisk:10687696
  cuda:
  requests_cpu:4
  requests_memory:16Gi
  limits_cpu:0
  limits_memory:0
worker:[]
times
  load_ms:251632.57140340284
  benchmark_ms:316155.41979367845
prices
  perHour_usd:0.0
  benchmark_usd:0.0
hardwaremetrics
  total_cpu_memory:0
  total_cpu_memory_cached:0
  total_cpu_util:0
  total_gpu_util:0
  total_gpu_power:0
  total_gpu_memory:0
  total_cpu_throttled:0
  total_cpu_util_others:0
  total_cpu_util_s:0
  total_cpu_util_user_s:0
  total_cpu_util_sys_s:0
  total_cpu_throttled_s:0
  total_cpu_util_others_s:0
  total_network_rx:0
  total_network_tx:0
  total_fs_read:0
  total_fs_write:0
  total_gpu_energy:0.0
metrics
  totaltime_ms:3470.0931945159236
  throughput_run_total_ps:0.8645300952554098
  throughput_run_total_ph:3112.308342919476
  throughput_session_total_ps:0.28817669841846993
  throughput_session_total_ph:1037.436114306492
  throughput_run_mean_ps:0.924158961737484
  throughput_run_mean_ph:3326.972262254943
  latency_run_mean_ms:1082.0649275747212
  throughput_session_mean_ps:0.30805298724582797
  throughput_session_mean_ph:1108.990754084981
  latency_session_mean_ms:3246.194782724164
  queuesize_run:0.9354776949087117
  queuesize_run_percent:93.54776949087119
  queuesize_session:0.9354776949087117
  queuesize_session_percent:93.54776949087118

Show Properties of a Query¶

In [6]:
connection = list_connections[0]
numQuery = 1
numRun = 0

Show Errors¶

In [7]:
list_errors = evaluate.get_error(numQuery)

display(Markdown("### Errors of Query {}".format(numQuery)))
pd.DataFrame.from_dict(list_errors, orient='index').sort_index()

Errors of Query 1¶

Out[7]:
0
MariaDB-1-1
MariaDB-2-1
MonetDB-1-1
MonetDB-2-1
MySQL-1-1
MySQL-2-1
PostgreSQL-1-1
PostgreSQL-2-1

Show Warnings¶

In [8]:
list_errors = evaluate.get_warning(numQuery)

display(Markdown("### Warnings of Query {}".format(numQuery)))
pd.DataFrame.from_dict(list_errors, orient='index').sort_index()

Warnings of Query 1¶

Out[8]:
0
MariaDB-1-1
MariaDB-2-1
MonetDB-1-1 Different at run #1
MonetDB-2-1 Different at run #1
MySQL-1-1
MySQL-2-1
PostgreSQL-1-1
PostgreSQL-2-1

Show Query Template¶

In [9]:
query_properties = evaluate.get_experiment_query_properties()

display(Markdown("#### Show Query Template {} - {}".format(numQuery, query_properties[str(numQuery)]['config']['title'])))
print(query_properties[str(numQuery)]['config']['query'])

Show Query Template 1 - Pricing Summary Report (TPC-H Q1)¶

select
l_returnflag,
l_linestatus,
cast(sum(l_quantity) as bigint) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date '1998-12-01' - interval '{DELTA}'  day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus

Show Query Parameters¶

In [10]:
display(Markdown("#### Show Parameters of Query {} - {}".format(numQuery, query_properties[str(numQuery)]['config']['title'])))

df = evaluate.get_parameter_df(numQuery)
df

Show Parameters of Query 1 - Pricing Summary Report (TPC-H Q1)¶

Out[10]:
DELTA numRun
1 71 0
2 97 1
3 73 2

Show Query as being Run¶

In [11]:
display(Markdown("#### Show Query {} as run by {} - Run number {}".format(numQuery, connection, numRun)))

query_string = evaluate.get_querystring(numQuery, connection, numRun)
print(query_string)

Show Query 1 as run by MariaDB-1-1 - Run number 0¶

select
l_returnflag,
l_linestatus,
cast(sum(l_quantity) as unsigned int) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date('1998-12-01') - interval '71'  day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus
limit 10000000

Show Query as being Run by another DBMS¶

In [12]:
display(Markdown("#### Show Query {} as run by {} - Run number {}".format(numQuery, "MonetDB-1-1", numRun)))

query_string = evaluate.get_querystring(numQuery, connection, numRun)
print(query_string)

Show Query 1 as run by MonetDB-1-1 - Run number 0¶

select
l_returnflag,
l_linestatus,
cast(sum(l_quantity) as unsigned int) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date('1998-12-01') - interval '71'  day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus
limit 10000000

Show Result Set¶

In [13]:
display(Markdown("#### Show Result Set of Query {} as run by {} - Run number {}".format(numQuery, connection, numRun)))

df = evaluate.get_datastorage_df(numQuery, numRun)
df

Show Result Set of Query 1 as run by MariaDB-1-1 - Run number 0¶

Out[13]:
L_RETURNFLAG L_LINESTATUS SUM_QTY SUM_BASE_PRICE SUM_DISC_PRICE SUM_CHARGE AVG_QTY AVG_PRICE AVG_DISC COUNT_ORDER
1 A F 377518399 566065727797.0 537759104278.0 559276670892.0 26.0 38237.0 0.0 14804077
2 N F 9851614 14767438399.0 14028805792.0 14590490998.0 26.0 38258.0 0.0 385998
3 N O 751203243 1126406784135.0 1070081467451.0 1112897846350.0 25.0 38234.0 0.0 29460795
4 R F 377732830 566431054976.0 538110922665.0 559634780885.0 26.0 38251.0 0.0 14808183

Show Result Set from another DBMS¶

In [14]:
display(Markdown("#### Show Result Set of Query {} as run by {} - Run number {}".format(numQuery, "MonetDB-1-1", numRun)))

df = evaluate.get_resultset_df(numQuery, "MonetDB-1-1", numRun)
df

Show Result Set of Query 1 as run by MonetDB-1-1 - Run number 0¶

Out[14]:
L_RETURNFLAG L_LINESTATUS SUM_QTY SUM_BASE_PRICE SUM_DISC_PRICE SUM_CHARGE AVG_QTY AVG_PRICE AVG_DISC COUNT_ORDER
1 A F 377518399 566065727797.0 537759104278.0 559276670892.0 26.0 38237.0 0.0 14804077
2 N F 9851614 14767438399.0 14028805792.0 14590490998.0 26.0 38258.0 0.0 385998
3 N O 751203243 1126406784135.0 1070081467451.0 1112897846350.0 26.0 38234.0 0.0 29460795
4 R F 377732830 566431054976.0 538110922665.0 559634780885.0 26.0 38251.0 0.0 14808183

Some Measures of the Workload¶

Hardware Metrics¶

List all available Metrics¶

In [15]:
display(Markdown("### Hardware Metrics"))

pd.DataFrame(monitor.metrics.metrics).T

Hardware Metrics¶

Out[15]:
query title
total_cpu_memory (node_memory_MemTotal_bytes-node_memory_MemFree_bytes-node_memory_Buffers_bytes-node_memory_Cached_bytes)/1024/1024 CPU Memory [MiB]
total_cpu_memory_cached (node_memory_Cached_bytes)/1024/1024 CPU Memory Cached [MiB]
total_cpu_util 100 - (avg by (instance) (irate(node_cpu_seconds_total{mode="idle"}[5m])) * 100) CPU Util [%]
total_gpu_util sum(dcgm_gpu_utilization) GPU Util [%]
total_gpu_power sum(dcgm_power_usage) GPU Power Usage [W]
total_gpu_memory sum(dcgm_fb_used) GPU Memory [MiB]
total_cpu_throttled sum(rate(container_cpu_cfs_throttled_seconds_total{{job="monitor-node", container_label_io_kubernetes_container_name="dbms"}}[1m])) CPU Throttle [%]
total_cpu_util_others sum(irate(container_cpu_usage_seconds_total{{job="monitor-node", container_label_io_kubernetes_container_name!="dbms",id!="/"}}[1m])) CPU Others [%]
total_cpu_util_s sum(container_cpu_usage_seconds_total{{job="monitor-node", container_label_io_kubernetes_container_name="dbms"}}) CPU Util [s]
total_cpu_util_user_s sum(container_cpu_user_seconds_total{{job="monitor-node", container_label_io_kubernetes_container_name="dbms"}}) CPU Util User [s]
total_cpu_util_sys_s sum(container_cpu_system_seconds_total{{job="monitor-node", container_label_io_kubernetes_container_name="dbms"}}) CPU Util Sys [s]
total_cpu_throttled_s sum(container_cpu_cfs_throttled_seconds_total{{job="monitor-node", container_label_io_kubernetes_container_name="dbms"}}) CPU Throttle [s]
total_cpu_util_others_s sum(container_cpu_usage_seconds_total{{job="monitor-node", container_label_io_kubernetes_container_name!="dbms",id!="/"}}) CPU Util Others [s]
total_network_rx sum(container_network_receive_bytes_total{{container_label_app="dbmsbenchmarker", job="monitor-node"}}) Net Rx [b]
total_network_tx sum(container_network_transmit_bytes_total{{container_label_app="dbmsbenchmarker", job="monitor-node"}}) Net Tx [b]
total_fs_read sum(container_fs_reads_bytes_total{{job="monitor-node", container_label_io_kubernetes_container_name="dbms"}}) FS Read [b]
total_fs_write sum(container_fs_writes_bytes_total{{job="monitor-node", container_label_io_kubernetes_container_name="dbms"}}) FS Write [b]

Get Hardware Metrics for Loading Test¶

In [16]:
df = evaluate.get_loading_metrics('total_cpu_memory')
df = evaluate.get_loading_metrics('total_cpu_util_s')
df = df.T.max().sort_index() - df.T.min().sort_index() # compute difference of counter

display(Markdown("### CPU of Ingestion (via counter)"))
pd.DataFrame(df)

CPU of Ingestion (via counter)¶

Out[16]:
0
DBMS
MariaDB-1 1526.198404
MariaDB-2 1526.198404
MonetDB-1 1931.119997
MonetDB-2 1931.119997
MySQL-1 1963.867765
MySQL-2 1963.867765
PostgreSQL-1 472.864693
PostgreSQL-2 472.864693
In [17]:
df = evaluate.get_loading_metrics('total_cpu_util')
df = df.T.sum().sort_index() # computer sum of rates

display(Markdown("### CPU of Ingestion (via rate)"))
pd.DataFrame(df)

CPU of Ingestion (via rate)¶

Out[17]:
0
DBMS
MariaDB-1 1526.761004
MariaDB-2 1526.761004
MonetDB-1 1887.024304
MonetDB-2 1887.024304
MySQL-1 1962.119638
MySQL-2 1962.119638
PostgreSQL-1 464.746812
PostgreSQL-2 464.746812

Get Hardware Metrics per Stream¶

In [18]:
df = evaluate.get_streaming_metrics('total_cpu_memory')
df = evaluate.get_streaming_metrics('total_cpu_util_s')
df = df.T.max().sort_index() - df.T.min().sort_index() # compute difference of counter

display(Markdown("### CPU of Stream (via counter)"))
pd.DataFrame(df)

CPU of Stream (via counter)¶

Out[18]:
0
DBMS
MariaDB-1-1 5564.712371
MariaDB-2-1 5497.702421
MonetDB-1-1 1356.184954
MonetDB-2-1 1218.550218
MySQL-1-1 3011.705555
MySQL-2-1 2896.443679
PostgreSQL-1-1 1436.189579
PostgreSQL-2-1 1278.603392
In [19]:
df = evaluate.get_streaming_metrics('total_cpu_util')
df = df.T.sum().sort_index() # computer sum of rates

display(Markdown("### CPU of Stream (via rate)"))
pd.DataFrame(df)

CPU of Stream (via rate)¶

Out[19]:
0
DBMS
MariaDB-1-1 5580.313389
MariaDB-2-1 5519.121645
MonetDB-1-1 1508.432769
MonetDB-2-1 1368.634461
MySQL-1-1 3029.972219
MySQL-2-1 2915.922365
PostgreSQL-1-1 1492.224116
PostgreSQL-2-1 1331.659788

Timing Measures¶

Mean of Means of Timer Run¶

In [20]:
df = evaluate.get_aggregated_experiment_statistics(type='timer', name='run', query_aggregate='Mean', total_aggregate='Mean')
df = (df/1000.0).sort_index()

display(Markdown("### Mean of Means of Timer Run [s]"))
df

Mean of Means of Timer Run [s]¶

Out[20]:
total_timer_run
DBMS
MariaDB-1-1 84.432242
MariaDB-2-1 83.348424
MonetDB-1-1 4.717192
MonetDB-2-1 4.458597
MySQL-1-1 45.625206
MySQL-2-1 43.901729
PostgreSQL-1-1 14.941245
PostgreSQL-2-1 11.454957

Geometric Mean of Medians of Timer Run¶

In [21]:
df = evaluate.get_aggregated_experiment_statistics(type='timer', name='run', query_aggregate='Median', total_aggregate='Geo')
df = (df/1000.0).sort_index()

display(Markdown("### Geometric Mean of Medians of Timer Run [s]"))
df

Geometric Mean of Medians of Timer Run [s]¶

Out[21]:
total_timer_run
DBMS
MariaDB-1-1 31.517934
MariaDB-2-1 31.257281
MonetDB-1-1 0.752332
MonetDB-2-1 0.792416
MySQL-1-1 23.157694
MySQL-2-1 22.424075
PostgreSQL-1-1 7.769965
PostgreSQL-2-1 7.347514

Plots¶

In [22]:
%matplotlib inline
In [23]:
df = evaluate.get_aggregated_experiment_statistics(type='timer', name='run', query_aggregate='Median', total_aggregate='Geo')
df = df.sort_index()

fig = go.Figure()
for i in range(len(df.index)):
    t = fig.add_trace(go.Bar(x=[df.index[i]], y=df.iloc[i], name=df.index[i], marker=dict(color=connection_colors[df.index[i]])))

fig.update_layout(title_text='Geometric Mean of Medians of Timer Run [s]')
fig.show()

Some Measures per Query¶

Timing Measures¶

Means of Timer Runs¶

In [24]:
df = evaluate.get_aggregated_query_statistics(type='timer', name='run', query_aggregate='Mean').sort_index().T

display(Markdown("### Means of Timer Runs [ms]"))
df

Means of Timer Runs [ms]¶

Out[24]:
DBMS MariaDB-1-1 MariaDB-2-1 MonetDB-1-1 MonetDB-2-1 MySQL-1-1 MySQL-2-1 PostgreSQL-1-1 PostgreSQL-2-1
Q1 115989.630678 113481.054475 5563.435273 4517.950883 139965.852845 139650.894833 20039.383417 19866.305729
Q2 13202.835330 13171.131305 423.855536 351.280993 1790.965649 1577.399513 7225.602556 17500.866503
Q3 58803.801925 55998.118738 1121.951630 286.680120 60652.883762 60329.661458 15167.515143 19752.016812
Q4 10906.610491 10700.938849 167.959029 157.038979 10353.898270 10227.802497 63276.056357 3660.092083
Q5 43173.049879 41958.968865 369.634689 383.102751 44849.054096 40186.950882 5783.143584 6885.665454
Q6 19219.394048 19288.650417 1785.649381 129.628404 24070.415718 24026.994607 2917.860596 2886.094683
Q7 35227.129593 33578.747254 2198.463472 3050.601307 27260.769050 24198.993758 11244.580511 13545.375023
Q8 74788.585443 73309.150578 1092.181380 3154.597746 117483.457010 106573.850883 12783.577640 13338.918216
Q9 141097.449787 139187.680818 4918.470218 3963.573644 124488.035032 112492.537863 44068.426727 42587.264411
Q10 141403.858324 142090.442053 908.977431 803.503769 20992.730243 20748.897447 20831.573940 16731.910749
Q11 4148.042627 4293.732452 567.795084 415.515498 5663.032641 5696.939546 3892.319345 2169.532531
Q12 132080.983062 128807.095058 3773.747000 191.421537 34564.011301 34269.547826 15897.897638 7168.397244
Q13 114272.807927 115454.660663 3590.687924 3181.238493 161980.722418 160750.343010 18784.685568 7771.261669
Q14 511364.874354 497102.462734 181.559546 136.743135 29699.836106 29359.513897 8136.222391 3260.906603
Q15 38717.798192 38992.169477 270.773724 282.713636 53015.246627 52652.230879 10828.831254 7963.955605
Q16 4946.922447 4957.202488 1839.745415 1857.860934 5847.584373 5991.898077 7068.259177 6408.576533
Q17 1276.979283 1253.035080 1891.926599 2360.170470 13071.677511 12453.737342 11935.246834 13523.998653
Q18 126016.121606 129203.891398 479.290318 490.452279 30790.192968 30442.444819 33132.461344 29488.654058
Q19 2384.714820 2344.720237 251.887020 248.384294 3420.121102 3349.518520 568.638627 492.559332
Q20 20530.713325 20667.318521 263.041606 233.055218 10182.912607 9965.629668 7626.458291 9463.541198
Q21 246130.899464 245986.749306 71514.882144 70924.262200 81238.430244 78553.948823 6737.272611 6798.701014
Q22 1826.111431 1837.417374 602.300994 969.356511 2372.703644 2338.294653 761.376779 744.457357

Maximum of Run Throughput¶

In [25]:
df = (evaluate.get_aggregated_query_statistics(type='throughput', name='run', query_aggregate='Max')).sort_index().T

display(Markdown("### Maximum of Run Throughput [1/s]"))
df

Maximum of Run Throughput [1/s]¶

Out[25]:
DBMS MariaDB-1-1 MariaDB-2-1 MonetDB-1-1 MonetDB-2-1 MySQL-1-1 MySQL-2-1 PostgreSQL-1-1 PostgreSQL-2-1
Q1 0.008810 0.008846 0.220207 0.231232 0.007174 0.007189 0.050163 0.050691
Q2 0.076588 0.076907 2.789275 3.175859 0.566798 0.639963 0.139435 0.061638
Q3 0.017364 0.018087 3.736227 3.912092 0.016533 0.016633 0.082372 0.052391
Q4 0.092310 0.094059 6.605401 6.746199 0.097566 0.098876 0.407549 0.294164
Q5 0.023296 0.023917 3.608333 3.462481 0.022400 0.024953 0.247183 0.159519
Q6 0.052784 0.052485 7.631666 8.164754 0.041805 0.041873 0.344342 0.349700
Q7 0.028540 0.029938 0.563871 0.485066 0.038508 0.043290 0.099798 0.078069
Q8 0.013432 0.013729 0.959255 0.387856 0.008518 0.009407 0.091922 0.087958
Q9 0.007132 0.007206 0.782999 0.323114 0.008129 0.009099 0.023413 0.023625
Q10 0.007085 0.007049 1.238689 1.262583 0.047705 0.048243 0.050442 0.068517
Q11 0.243337 0.234186 1.938101 2.797194 0.177257 0.177560 0.306996 0.467223
Q12 0.007574 0.007771 4.941322 5.652018 0.029141 0.029371 0.075461 0.145088
Q13 0.008790 0.008695 0.322902 0.343424 0.006207 0.006254 0.054629 0.138944
Q14 0.001958 0.002015 7.144495 7.884286 0.034125 0.034448 0.160541 0.309416
Q15 0.025941 0.025866 3.821016 3.587965 0.019004 0.019073 0.117023 0.126468
Q16 0.205247 0.203905 0.555683 0.565030 0.173094 0.169006 0.147405 0.156668
Q17 0.788392 0.809581 0.695906 0.457047 0.081641 0.084269 0.083890 0.075591
Q18 0.007945 0.007753 2.198075 2.107396 0.032550 0.033012 0.032164 0.036029
Q19 0.429370 0.433899 4.087997 4.146425 0.300284 0.304113 2.180537 2.117862
Q20 0.048879 0.048549 4.019059 4.688444 0.098711 0.100542 0.132065 0.106847
Q21 0.004067 0.004068 0.014502 0.014320 0.012437 0.012804 0.148926 0.149333
Q22 0.550611 0.550844 1.988697 1.171185 0.429374 0.434330 1.372679 1.436696

Latency of Timer Execution¶

In [26]:
df = evaluate.get_aggregated_query_statistics(type='latency', name='execution', query_aggregate='Mean').sort_index().T

display(Markdown("### Latency of Timer Execution [ms]"))
df

Latency of Timer Execution [ms]¶

Out[26]:
DBMS MariaDB-1-1 MariaDB-2-1 MonetDB-1-1 MonetDB-2-1 MySQL-1-1 MySQL-2-1 PostgreSQL-1-1 PostgreSQL-2-1
Q1 115984.029097 113477.396985 5559.138363 4515.038655 139963.454816 139644.360665 20034.833936 19861.297945
Q2 13189.844139 13157.687652 413.282013 340.652305 1782.370459 1560.817159 7211.191122 17486.172169
Q3 58800.386259 55994.774677 1118.389753 283.285230 60650.473856 60324.399922 15162.580179 19747.151356
Q4 10906.366797 10700.690025 167.731983 156.817029 10353.680159 10227.460235 63275.764168 3659.762128
Q5 43172.769596 41958.683499 369.404377 382.882849 44848.791658 40186.573100 5782.834073 6885.320396
Q6 19219.304236 19288.558926 1785.574434 129.571968 24070.314904 24026.849064 2917.765952 2885.997845
Q7 35226.790069 33578.401355 2198.102969 3050.271067 27260.520071 24198.575423 11244.196273 13544.882668
Q8 74788.269761 73308.950132 1091.897862 3154.334292 117483.286765 106573.598974 12783.235100 13338.417255
Q9 141087.924653 139178.495053 4908.692492 3954.063788 124482.944844 112482.017125 44059.743012 42577.751900
Q10 141401.555155 142088.174021 906.461463 801.192185 20991.290802 20745.993865 20828.577831 16729.723187
Q11 3943.081469 4038.932427 260.222192 114.384230 5474.876384 5460.062068 3688.202220 1934.406897
Q12 132080.785154 128806.873278 3773.600698 191.276890 34563.874573 34269.322594 15897.675939 7168.201096
Q13 114271.550681 115453.225490 3589.015730 3179.584549 161979.926707 160748.794775 18783.155397 7769.695470
Q14 511364.784783 497102.376689 181.431396 136.607647 29699.769745 29359.402171 8135.799106 3260.492730
Q15 38717.798192 38992.169477 270.773724 282.713636 53015.246627 52652.230879 10828.831254 7963.955605
Q16 4027.831425 4120.540270 686.746840 691.220624 5078.042286 5088.938285 6058.316758 5587.771454
Q17 1276.899524 1252.970511 1891.844391 2360.095346 13071.609235 12453.614161 11934.943291 13523.710171
Q18 126002.332541 129191.025399 465.953219 477.032062 30781.519091 30425.720738 33115.222733 29473.692810
Q19 2384.642124 2344.654581 251.829252 248.328930 3420.048188 3349.401808 568.557070 492.506636
Q20 20484.981706 20624.654693 230.338829 208.398596 10160.403924 9924.714682 7587.395355 9418.309655
Q21 246127.850226 245983.909848 71511.727077 70921.302693 81236.928218 78550.780588 6734.338372 6795.589572
Q22 1825.728476 1837.013443 600.653826 967.767178 2372.459627 2337.835912 761.021456 744.030803

Mean of Latency of Timer Execution per DBMS¶

In [27]:
df = evaluate.get_aggregated_query_statistics(type='latency', name='execution', query_aggregate='Mean').sort_index()
df = evaluate.get_aggregated_by_connection(df, list_connections_dbms, connection_aggregate='Mean').T

display(Markdown("### Mean of Latency of Timer Execution per DBMS [ms]"))
df

Mean of Latency of Timer Execution per DBMS [ms]¶

Out[27]:
MariaDB MonetDB MySQL PostgreSQL
Q1 114730.713041 5037.088509 139803.907740 19948.065941
Q2 13173.765895 376.967159 1671.593809 12348.681645
Q3 57397.580468 700.837491 60487.436889 17454.865768
Q4 10803.528411 162.274506 10290.570197 33467.763148
Q5 42565.726548 376.143613 42517.682379 6334.077235
Q6 19253.931581 957.573201 24048.581984 2901.881899
Q7 34402.595712 2624.187018 25729.547747 12394.539471
Q8 74048.609946 2123.116077 112028.442870 13060.826178
Q9 140133.209853 4431.378140 118482.480984 43318.747456
Q10 141744.864588 853.826824 20868.642333 18779.150509
Q11 3991.006948 187.303211 5467.469226 2811.304559
Q12 130443.829216 1982.438794 34416.598583 11532.938518
Q13 114862.388085 3384.300139 161364.360741 13276.425433
Q14 504233.580736 159.019522 29529.585958 5698.145918
Q15 38854.983834 276.743680 52833.738753 9396.393429
Q16 4074.185848 688.983732 5083.490285 5823.044106
Q17 1264.935018 2125.969868 12762.611698 12729.326731
Q18 127596.678970 471.492640 30603.619914 31294.457771
Q19 2364.648353 250.079091 3384.724998 530.531853
Q20 20554.818200 219.368712 10042.559303 8502.852505
Q21 246055.880037 71216.514885 79893.854403 6764.963972
Q22 1831.370959 784.210502 2355.147769 752.526129

Coefficient of Variation of Latency of Timer Execution per DBMS¶

In [28]:
df = evaluate.get_aggregated_query_statistics(type='latency', name='execution', query_aggregate='Mean').sort_index()
df = evaluate.get_aggregated_by_connection(df, list_connections_dbms, connection_aggregate='cv [%]').T

display(Markdown("### CV of Latency of Timer Execution per DBMS [%]"))
df

CV of Latency of Timer Execution per DBMS [%]¶

Out[28]:
MariaDB MonetDB MySQL PostgreSQL
Q1 1.092398 10.364119 0.114122 0.434969
Q2 0.122047 9.633426 6.627008 41.603555
Q3 2.444016 59.579042 0.269539 13.132645
Q4 0.951896 3.363114 0.613280 89.064814
Q5 1.426131 1.791666 5.482682 8.702817
Q6 0.179846 86.468714 0.090371 0.547371
Q7 2.395733 16.236802 5.950250 9.281048
Q8 0.998884 48.570977 4.869160 2.125372
Q9 0.681291 10.771239 5.064431 1.710566
Q10 0.242202 6.164557 0.587717 10.913312
Q11 1.200837 38.930983 0.135477 31.191841
Q12 1.254913 90.351435 0.427921 37.845840
Q13 0.514387 6.048979 0.381476 41.477505
Q14 1.414266 14.093788 0.576316 42.779761
Q15 0.353071 2.157215 0.343545 15.244549
Q16 1.137759 0.324665 0.107170 4.040372
Q17 0.945859 11.012643 2.421115 6.240577
Q18 1.249520 1.174869 0.581301 5.818171
Q19 0.845528 0.699843 1.043606 7.167377
Q20 0.339757 5.000766 1.173452 10.766471
Q21 0.029250 0.414528 1.681073 0.452709
Q22 0.308102 23.406557 0.735065 1.128908

Latency of Timer Connection¶

In [29]:
df = evaluate.get_aggregated_query_statistics(type='timer', name='connection', query_aggregate='Mean').T

display(Markdown("### Latency of Timer Connection [ms]"))
df

No data

Latency of Timer Connection [ms]¶

Out[29]:

Latency of Timer Data Transfer¶

In [30]:
df = evaluate.get_aggregated_query_statistics(type='timer', name='datatransfer', query_aggregate='Mean').T

display(Markdown("### Latency of Timer Data Transfer [ms]"))
df

No data

Latency of Timer Data Transfer [ms]¶

Out[30]:

Latency of Timer Run - normalized to 1 per Query¶

In [31]:
df = evaluate.get_aggregated_query_statistics(type='timer', name='run', query_aggregate='factor').sort_index().T

display(Markdown("### Latency of Timer Run - normalized to 1 per Query"))
df

Latency of Timer Run - normalized to 1 per Query¶

Out[31]:
DBMS MariaDB-1-1 MariaDB-2-1 MonetDB-1-1 MonetDB-2-1 MySQL-1-1 MySQL-2-1 PostgreSQL-1-1 PostgreSQL-2-1
Q1 25.6731 25.1178 1.2314 1.0000 30.9799 30.9102 4.4355 4.3972
Q2 37.5848 37.4946 1.2066 1.0000 5.0984 4.4904 20.5693 49.8201
Q3 205.1199 195.3331 3.9136 1.0000 211.5699 210.4424 52.9075 68.8992
Q4 69.4516 68.1419 1.0695 1.0000 65.9320 65.1291 402.9322 23.3069
Q5 116.7992 113.5147 1.0000 1.0364 121.3335 108.7207 15.6456 18.6283
Q6 148.2653 148.7996 13.7751 1.0000 185.6878 185.3529 22.5094 22.2644
Q7 16.0235 15.2737 1.0000 1.3876 12.3999 11.0072 5.1147 6.1613
Q8 68.4763 67.1218 1.0000 2.8883 107.5677 97.5789 11.7046 12.2131
Q9 35.5985 35.1167 1.2409 1.0000 31.4080 28.3816 11.1184 10.7447
Q10 175.9841 176.8386 1.1313 1.0000 26.1265 25.8230 25.9259 20.8237
Q11 9.9829 10.3335 1.3665 1.0000 13.6289 13.7105 9.3674 5.2213
Q12 690.0006 672.8976 19.7143 1.0000 180.5649 179.0266 83.0518 37.4482
Q13 35.9209 36.2924 1.1287 1.0000 50.9175 50.5307 5.9048 2.4428
Q14 3739.6018 3635.3011 1.3277 1.0000 217.1943 214.7056 59.5000 23.8469
Q15 142.9895 144.0028 1.0000 1.0441 195.7917 194.4510 39.9922 29.4118
Q16 2.6889 2.6945 1.0000 1.0098 3.1785 3.2569 3.8420 3.4834
Q17 1.0191 1.0000 1.5099 1.8836 10.4320 9.9389 9.5251 10.7930
Q18 262.9223 269.5733 1.0000 1.0233 64.2412 63.5157 69.1282 61.5257
Q19 9.6009 9.4399 1.0141 1.0000 13.7695 13.4852 2.2894 1.9831
Q20 88.0938 88.6799 1.1287 1.0000 43.6931 42.7608 32.7238 40.6064
Q21 36.5327 36.5113 10.6148 10.5271 12.0581 11.6596 1.0000 1.0091
Q22 3.0319 3.0507 1.0000 1.6094 3.9394 3.8823 1.2641 1.2360

Size of Result Sets per Query¶

In [32]:
df = evaluate.get_total_resultsize().T

display(Markdown("### Size of Result Sets per Query"))
df

Size of Result Sets per Query¶

Out[32]:
MariaDB-1-1 MariaDB-2-1 MonetDB-1-1 MonetDB-2-1 MySQL-1-1 MySQL-2-1 PostgreSQL-1-1 PostgreSQL-2-1
Q1 1344 1344 1344 1344 1344 1344 1344 1344
Q2 19584 19584 19584 19584 19584 19584 19584 19584
Q3 1344 1344 1344 1344 1344 1344 1344 1344
Q4 624 624 624 624 624 624 624 624
Q5 624 624 624 624 624 624 624 624
Q6 408 408 408 408 408 408 408 408
Q7 768 768 768 768 768 768 768 768
Q8 480 480 480 480 480 480 480 480
Q9 12984 12984 12984 12984 12984 12984 12984 12984
Q10 4224 4224 4224 4224 4224 4224 4224 4224
Q11 455728 455728 455728 455728 455728 455728 455728 455728
Q12 528 528 528 528 528 528 528 528
Q13 2544 2544 2544 2544 2544 2544 2544 2544
Q14 408 408 408 408 408 408 408 408
Q15 0 0 0 0 0 0 0 0
Q16 2746656 2746656 2746656 2746656 2746656 2746656 2746656 2746656
Q17 408 408 408 408 408 408 408 408
Q18 13488 13488 13488 13488 13488 13488 13488 13488
Q19 408 408 408 408 408 408 408 408
Q20 85568 85568 85568 85568 85568 85568 85568 85568
Q21 5184 5184 5184 5184 5184 5184 5184 5184
Q22 888 888 888 888 888 888 888 888

Size of Result Sets per Query - normalized to 1¶

Size of Result Sets per Query - normalized to 1¶

In [33]:
df = evaluate.get_total_resultsize_normalized()

display(Markdown("### Size of Result Sets per Query - normalized to 1"))
df

Size of Result Sets per Query - normalized to 1¶

Out[33]:
Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 ... Q13 Q14 Q15 Q16 Q17 Q18 Q19 Q20 Q21 Q22
MariaDB-1-1 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 ... 1.0 1.0 NaN 1.0 1.0 1.0 1.0 1.0 1.0 1.0
MariaDB-2-1 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 ... 1.0 1.0 NaN 1.0 1.0 1.0 1.0 1.0 1.0 1.0
MonetDB-1-1 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 ... 1.0 1.0 NaN 1.0 1.0 1.0 1.0 1.0 1.0 1.0
MonetDB-2-1 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 ... 1.0 1.0 NaN 1.0 1.0 1.0 1.0 1.0 1.0 1.0
MySQL-1-1 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 ... 1.0 1.0 NaN 1.0 1.0 1.0 1.0 1.0 1.0 1.0
MySQL-2-1 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 ... 1.0 1.0 NaN 1.0 1.0 1.0 1.0 1.0 1.0 1.0
PostgreSQL-1-1 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 ... 1.0 1.0 NaN 1.0 1.0 1.0 1.0 1.0 1.0 1.0
PostgreSQL-2-1 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 1.0 ... 1.0 1.0 NaN 1.0 1.0 1.0 1.0 1.0 1.0 1.0

8 rows × 22 columns

Table of Errors¶

In [34]:
df = evaluate.get_total_errors().T

display(Markdown("### Table of Errors"))
df

Table of Errors¶

Out[34]:
MariaDB-1-1 MariaDB-2-1 MonetDB-1-1 MonetDB-2-1 MySQL-1-1 MySQL-2-1 PostgreSQL-1-1 PostgreSQL-2-1
Q1 False False False False False False False False
Q2 False False False False False False False False
Q3 False False False False False False False False
Q4 False False False False False False False False
Q5 False False False False False False False False
Q6 False False False False False False False False
Q7 False False False False False False False False
Q8 False False False False False False False False
Q9 False False False False False False False False
Q10 False False False False False False False False
Q11 False False False False False False False False
Q12 False False False False False False False False
Q13 False False False False False False False False
Q14 False False False False False False False False
Q15 False False False False False False False False
Q16 False False False False False False False False
Q17 False False False False False False False False
Q18 False False False False False False False False
Q19 False False False False False False False False
Q20 False False False False False False False False
Q21 False False False False False False False False
Q22 False False False False False False False False

Table of Warnings¶

In [35]:
df = evaluate.get_total_warnings().T

display(Markdown("### Table of Warnings"))
df

Table of Warnings¶

Out[35]:
MariaDB-1-1 MariaDB-2-1 MonetDB-1-1 MonetDB-2-1 MySQL-1-1 MySQL-2-1 PostgreSQL-1-1 PostgreSQL-2-1
Q1 False False True True False False False False
Q2 False False False False False False False False
Q3 False False False False False False False False
Q4 False False False False False False False False
Q5 False False False False False False False False
Q6 False False False False False False False False
Q7 False False False False False False False False
Q8 False False False False False False False False
Q9 False False False False False False False False
Q10 False False False False False False False False
Q11 False False False False False False False False
Q12 False False False False False False False False
Q13 False False False False False False False False
Q14 False False False False False False False False
Q15 True False True True True True True True
Q16 False False False False False False False False
Q17 False False False False False False False False
Q18 False False False False False False False False
Q19 False False False False False False False False
Q20 False False False False False False False False
Q21 False False False False False False False False
Q22 False False False False False False False False

Total Time [s] per Query¶

In [36]:
df = evaluate.get_total_times().T/1000.0

display(Markdown("### Total Time [s] per Query"))
df

Total Time [s] per Query¶

Out[36]:
MariaDB-1-1 MariaDB-2-1 MonetDB-1-1 MonetDB-2-1 MySQL-1-1 MySQL-2-1 PostgreSQL-1-1 PostgreSQL-2-1
Q1 347.973955 340.447334 16.845194 13.651159 419.900614 418.959277 60.128597 59.610011
Q2 39.615576 39.520509 1.823718 1.384478 5.376375 4.740327 21.686897 52.512813
Q3 176.414484 167.996884 3.498522 0.976042 181.960522 180.991944 45.508243 59.262894
Q4 32.722405 32.105238 0.549320 0.513998 31.063408 30.685955 189.832833 10.984629
Q5 129.521532 125.879449 1.155652 1.228914 134.548819 120.563560 17.351747 20.659699
Q6 57.660592 57.868211 5.364403 0.395960 72.212753 72.083435 8.756129 8.660637
Q7 105.684266 100.738883 6.706806 12.678075 81.784196 72.600099 33.736559 40.639116
Q8 224.372421 219.930033 3.325128 9.827672 352.452032 319.723975 38.353090 40.019183
Q9 423.299235 417.569854 15.697046 15.665570 373.467877 337.484744 132.212075 127.769052
Q10 424.215546 426.275221 3.142779 3.154876 62.980595 62.250786 62.501992 50.201007
Q11 12.636782 13.036927 1.998065 1.483094 17.120079 17.255366 11.835612 6.707820
Q12 396.245450 386.423815 11.375542 0.624792 103.693792 102.811258 47.698360 21.510053
Q13 342.821134 346.367399 10.778061 9.548804 485.944128 482.254182 56.357074 23.317058
Q14 1534.097118 1491.309947 0.554613 0.420054 89.101176 88.081257 24.410784 9.784663
Q15 116.154487 116.977704 0.821195 0.856493 159.046659 157.957757 32.487656 23.893080
Q16 15.720677 15.581426 6.722981 6.640638 18.174277 18.795835 22.066576 19.974487
Q17 3.832843 3.760958 5.683618 7.087494 39.216798 37.363785 35.808117 40.574290
Q18 378.056357 387.620047 1.627102 3.110095 92.376161 91.482406 99.406812 88.473750
Q19 7.156457 7.036084 0.770288 0.758979 10.261899 10.050805 1.708093 1.479426
Q20 61.613432 62.027928 0.891396 0.792113 30.721453 30.143051 23.013910 28.578599
Q21 738.396596 737.964908 214.951881 214.146436 243.717694 235.666268 20.215939 20.400054
Q22 5.480514 5.514622 1.872112 3.753415 7.119875 7.017448 2.286109 2.236404

Total Time per Query - normalized to 100%¶

In [37]:
df = evaluate.get_total_times_normalized().T

display(Markdown("### Total Time per Query - normalized to 100%"))
df

Total Time per Query - normalized to 100%¶

Out[37]:
MariaDB-1-1 MariaDB-2-1 MonetDB-1-1 MonetDB-2-1 MySQL-1-1 MySQL-2-1 PostgreSQL-1-1 PostgreSQL-2-1
Q1 20.743404 20.294728 1.004175 0.813772 25.031092 24.974977 3.584383 3.553469
Q2 23.770197 23.713155 1.094270 0.830717 3.225941 2.844298 13.012605 31.508817
Q3 21.603285 20.572486 0.428420 0.119524 22.282439 22.163829 5.572828 7.257189
Q4 9.962439 9.774540 0.167242 0.156488 9.457352 9.342435 57.795200 3.344305
Q5 23.510497 22.849393 0.209772 0.223070 24.423041 21.884463 3.149655 3.750109
Q6 20.374615 20.447978 1.895534 0.139914 25.516683 25.470988 3.094016 3.060273
Q7 23.249385 22.161455 1.475424 2.789038 17.991631 15.971230 7.421675 8.940162
Q8 18.573822 18.206075 0.275258 0.813547 29.176407 26.467139 3.174915 3.312837
Q9 22.965884 22.655039 0.851635 0.849927 20.262309 18.310062 7.173099 6.932045
Q10 38.750956 38.939101 0.287084 0.288190 5.753109 5.686443 5.709390 4.585728
Q11 15.396863 15.884406 2.434475 1.807026 20.859386 21.024221 14.420704 8.172919
Q12 37.019032 36.101451 1.062754 0.058371 9.687540 9.605090 4.456195 2.009566
Q13 19.507426 19.709218 0.613300 0.543352 27.651502 27.441534 3.206866 1.326802
Q14 47.381440 46.059934 0.017130 0.012974 2.751939 2.720438 0.753941 0.302205
Q15 19.098230 19.233584 0.135022 0.140825 26.150602 25.971563 5.341651 3.928523
Q16 12.711086 12.598494 5.435923 5.369344 14.694965 15.197531 17.842116 16.150540
Q17 2.211325 2.169851 3.279113 4.089067 22.625785 21.556705 20.659177 23.408978
Q18 33.100333 33.937672 0.142459 0.272301 8.087899 8.009647 8.703460 7.746228
Q19 18.246015 17.939112 1.963916 1.935083 26.163610 25.625407 4.354933 3.771925
Q20 25.911744 26.086061 0.374880 0.333126 12.920014 12.676765 9.678580 12.018829
Q21 30.443572 30.425774 8.862315 8.829107 10.048309 9.716354 0.833489 0.841080
Q22 15.534117 15.630793 5.306365 10.638782 20.180767 19.890444 6.479809 6.338923

Total Time per Query - normalized to 100%¶

In [38]:
df = evaluate.get_total_times_relative().T

display(Markdown("### Total Time per Query - normalized to 100%"))
df

Total Time per Query - normalized to 100%¶

Out[38]:
MariaDB-1-1 MariaDB-2-1 MonetDB-1-1 MonetDB-2-1 MySQL-1-1 MySQL-2-1 PostgreSQL-1-1 PostgreSQL-2-1
Q1 165.947234 162.357822 8.033398 6.510177 200.248739 199.799819 28.675061 28.427749
Q2 190.161578 189.705241 8.754162 6.645734 25.807525 22.754386 104.100838 252.070536
Q3 172.826279 164.579890 3.427363 0.956189 178.259513 177.310635 44.582622 58.057509
Q4 79.699508 78.196322 1.337938 1.251906 75.658813 74.739478 462.361598 26.754437
Q5 188.083977 182.795146 1.678173 1.784561 195.384324 175.075706 25.197243 30.000868
Q6 162.996918 163.583822 15.164276 1.119312 204.133462 203.767902 24.752124 24.482184
Q7 185.995083 177.291640 11.803393 22.312305 143.933047 127.769837 59.373398 71.521296
Q8 148.590573 145.648602 2.202065 6.508373 233.411259 211.737113 25.399323 26.502692
Q9 183.727070 181.240313 6.813082 6.799420 162.098471 146.480499 57.384788 55.456357
Q10 310.007644 311.512811 2.296676 2.305516 46.024871 45.491542 45.675118 36.685822
Q11 123.174905 127.075251 19.475797 14.456209 166.875087 168.193769 115.365632 65.383349
Q12 296.152257 288.811606 8.502034 0.466967 77.500324 76.840721 35.649562 16.076527
Q13 156.059409 157.673743 4.906400 4.346817 221.212014 219.532272 25.654928 10.614417
Q14 379.051518 368.479474 0.137036 0.103789 22.015514 21.763508 6.031525 2.417638
Q15 152.785842 153.868675 1.080173 1.126603 209.204813 207.772505 42.733208 31.428182
Q16 101.688692 100.787949 43.487385 42.954751 117.559722 121.580250 142.736932 129.204320
Q17 17.690600 17.358810 26.232903 32.712534 181.006278 172.453643 165.273412 187.271821
Q18 264.802664 271.501376 1.139674 2.178409 64.703193 64.077179 69.627684 61.969821
Q19 145.968117 143.512895 15.711325 15.480666 209.308878 205.003257 34.839461 30.175402
Q20 207.293950 208.688491 2.999039 2.665008 103.360114 101.414122 77.428642 96.150635
Q21 243.548577 243.406191 70.898518 70.632855 80.386472 77.730835 6.667912 6.728639
Q22 124.272938 125.046343 42.450923 85.110253 161.446139 159.123549 51.838470 50.711385

Plots¶

In [39]:
df = (evaluate.get_aggregated_query_statistics(type='timer', name='run', query_aggregate='Mean').T/1000.0).round(2)
#.sort_index(ascending=False)
#df=df.T
#df=df.round(2)

fig1 = ff.create_annotated_heatmap(
    x=list(df.columns),
    y=list(df.index),
    z=df.values.tolist(),
    showscale=True,
    colorscale='Reds',
    xgap=1,
    ygap=1,
    )

fig1.update_layout(title_text='Timer Run - Mean per Query [s]')
fig1.layout.xaxis.type = 'category'
fig1.layout.yaxis.type = 'category'

fig1.show()
In [40]:
df = (evaluate.get_aggregated_query_statistics(type='timer', name='run', query_aggregate='Std Dev').T/1000.0).round(2)
#.sort_index(ascending=False)
#df=df.T
#df=df.round(2)

fig1 = ff.create_annotated_heatmap(
    x=list(df.columns),
    y=list(df.index),
    z=df.values.tolist(),
    showscale=True,
    colorscale='Reds',
    xgap=1,
    ygap=1,
    )

fig1.update_layout(title_text='Timer Run - Std Dev per Query [s]')
fig1.layout.xaxis.type = 'category'
fig1.layout.yaxis.type = 'category'

fig1.show()
In [41]:
df = evaluate.get_aggregated_query_statistics(type='timer', name='run', query_aggregate='factor').round(2)
df=df.sort_index(ascending=True).T
#df=df.T
#df=df.round(2)

fig1 = ff.create_annotated_heatmap(
    x=list(df.columns),
    y=list(df.index),
    z=df.values.tolist(),
    showscale=True,
    colorscale='Reds',
    xgap=1,
    ygap=1,
    )

fig1.update_layout(title_text='Timer Run - Factor per Query [s]')
fig1.layout.xaxis.type = 'category'
fig1.layout.yaxis.type = 'category'

fig1.show()

Inspect Single Queries¶

Measures¶

In [42]:
numQuery = 4

Measures of Execution Times¶

In [43]:
df1, df2 = evaluate.get_measures_and_statistics(numQuery, type='timer', name='execution')

display(Markdown("### Measures of Execution Times - {} Runs of Query {}".format(len(df1.columns), numQuery)))
df1.sort_index()

Measures of Execution Times - 3 Runs of Query 4¶

Out[43]:
0 1 2
DBMS
MariaDB-1-1 10832.749509 10974.072207 10912.278674
MariaDB-2-1 10631.296530 10786.618634 10684.154912
MonetDB-1-1 188.181439 151.178077 163.836433
MonetDB-2-1 167.151919 148.022101 155.277067
MySQL-1-1 10249.201583 10490.168055 10321.670839
MySQL-2-1 10113.206561 10378.973982 10190.200162
PostgreSQL-1-1 184856.501106 2517.352709 2453.438690
PostgreSQL-2-1 3520.991707 3399.093443 4059.201233

Statistics¶

Statistics of Execution Times¶

In [44]:
display(Markdown("### Statistics of Execution Times - {} Runs of Query {}".format(len(df1.columns), numQuery)))
df2.sort_index()

Statistics of Execution Times - 3 Runs of Query 4¶

Out[44]:
factor n Mean Std Dev cv [%] Median iqr qcod [%] Min Max Range Geo 1st Last Sum P25 P75 P90 P95
DBMS
MariaDB-1-1 69.5484 3.0 10906.366797 57.845996 0.530387 10912.278674 70.661349 0.323902 10832.749509 10974.072207 141.322698 10906.213308 10832.749509 10912.278674 32719.100390 10872.514091 10943.175440 10961.713500 10967.892854
MariaDB-2-1 68.2368 3.0 10700.690025 64.478919 0.602568 10684.154912 77.661052 0.363019 10631.296530 10786.618634 155.322104 10700.496044 10631.296530 10684.154912 32102.070076 10657.725721 10735.386773 10766.125890 10776.372262
MonetDB-1-1 1.0696 3.0 167.731983 15.355643 9.154869 163.836433 18.501681 5.547461 151.178077 188.181439 37.003362 167.041877 188.181439 163.836433 503.195949 157.507255 176.008936 183.312438 185.746938
MonetDB-2-1 1.0000 3.0 156.817029 7.885265 5.028322 155.277067 9.564909 3.057209 148.022101 167.151919 19.129818 156.620432 167.151919 155.277067 470.451087 151.649584 161.214493 164.776949 165.964434
MySQL-1-1 66.0240 3.0 10353.680159 100.944399 0.974962 10321.670839 120.483236 0.582288 10249.201583 10490.168055 240.966472 10353.189470 10249.201583 10321.670839 31061.040476 10285.436211 10405.919447 10456.468611 10473.318333
MySQL-2-1 65.2191 3.0 10227.460235 111.652184 1.091690 10190.200162 132.883710 0.650234 10113.206561 10378.973982 265.767421 10226.852806 10113.206561 10190.200162 30682.380705 10151.703362 10284.587072 10341.219218 10360.096600
PostgreSQL-1-1 403.5006 3.0 63275.764168 85970.567510 135.866502 2517.352709 91201.531208 94.831370 2453.438690 184856.501106 182403.062416 10451.645460 184856.501106 2453.438690 189827.292505 2485.395700 93686.926907 148388.671427 166622.586266
PostgreSQL-2-1 23.3378 3.0 3659.762128 286.796671 7.836484 3520.991707 330.053895 4.552380 3399.093443 4059.201233 660.107790 3648.851839 3520.991707 4059.201233 10979.286383 3460.042575 3790.096470 3951.559328 4005.380280

Plots¶

Timer Run - Line Plot¶

In [45]:
df1,df2=evaluate.get_measures_and_statistics(numQuery, type='timer', name='run', warmup=0)
df1 = df1.sort_index()

# Plots
fig = go.Figure()
for i in range(len(df1.index)):
    t = fig.add_trace(go.Scatter(x=df1.T.index, y=df1.iloc[i], name=df1.index[i], line=dict(color=connection_colors[df1.index[i]], width=1)))

fig.update_layout(title_text='Timer Run [ms] - Query {} ({} Measures)'.format(numQuery, len(df1.columns)))
fig.show()

Mean of Timer Run - Bar Plot¶

In [46]:
# Bar
df1, df2 = evaluate.get_measures_and_statistics(numQuery, type='timer', name='run')
df = tools.dataframehelper.collect(df2, 'Mean', 'timer_run_mean').sort_index()

fig = go.Figure()
for i in range(len(df.index)):
    t = fig.add_trace(go.Bar(x=[df.index[i]], y=df.iloc[i], name=df.index[i], marker=dict(color=connection_colors[df.index[i]])))

fig.update_layout(title_text='Mean of Timer Run [s] - Query {}'.format(numQuery))
fig.show()

Timer Run - Boxplot¶

In [47]:
# Boxplots
df1, df2 = evaluate.get_measures_and_statistics(numQuery, type='timer', name='run')
df1 = df1.sort_index()

fig = go.Figure()
for i in range(len(df1.index)):
    t = fig.add_trace(go.Box(y=df1.iloc[i], name=df1.index[i], line=dict(color=connection_colors[df1.index[i]], width=1), boxmean='sd'))

fig.update_layout(title_text='Timer Run [ms] - Query {}'.format(numQuery))
fig.show()

Timer Run Histogram¶

In [48]:
# Histogram
numQuery = 1

df1, df2 = evaluate.get_measures_and_statistics(numQuery, type='timer', name='run')
df1=df1.sort_index()

fig = go.Figure(layout = go.Layout(barmode='overlay'))
for i in range(len(df1.index)):
    t = fig.add_trace(go.Histogram(x=df1.iloc[i], name=df1.index[i], opacity=0.75, marker=dict(color=connection_colors[df1.index[i]])))

fig.update_layout(title_text='Timer Run Histogram - Query {}'.format(numQuery))
fig.show()
In [ ]: