DOI: 10.5281/zenodo.3608382
title: CC20 Artifact - Automatic Fusion

description: 1. Getting started The title of our paper submitted to CC20 is <strong>Improving Database Query Performance with Automatic Fusion</strong> This repository is created for showing the reproducibility of our experiments in this paper. We provide the details of scripts and original data used in the experiments. There are mainly two systems: <em>HorsePower</em> and <em>RDBMS MonetDB</em>. We supply step-by-step instructions to configure and deploy both systems in the experiments. On this page, you will see: how to run experiments (Section 2); and the results used in the paper (Section 3); 2. Experiments All experiments were run on a server called <code>sable-intel</code> equipped with Ubuntu 16.04.6 LTS (64-bit) 4 Intel Xeon E7-4850 2.00 GHz total 40 cores with 80 threads 128GB RAM Docker setup Download the docker image: cc20-docker.tar (About 13GB) <pre><code>docker load &lt; cc20-docker.tar </code></pre> Generate a named container (then exit) <pre><code>docker run --hostname sableintel -it --name=container-cc20 wukefe/cc20-docker exit </code></pre> Then, you can run the container <pre><code>docker start -ai container-cc20 </code></pre> Open a new terminal to access the container (optional) <pre><code>docker exec -it container-cc20 /bin/bash </code></pre> Introduction to MonetDB Work directory for MonetDB <pre><code>/home/hanfeng/cc20/monetdb </code></pre> Start MonetDB (use all available threads) <pre><code>./run.sh start </code></pre> Login MonetDB using its client tool, <code>mclient</code> <pre><code>mclient -d tpch1 ## ... MonetDB version v11.33.3 (Apr2019) sql&gt; SELECT 'Hello world'; +-------------+ | L2 | +=============+ | Hello world | +-------------+ 1 tuple </code></pre> Show the list of tables in the current database <pre><code>sql&gt; \d TABLE sys.customer TABLE sys.lineitem TABLE sys.nation TABLE sys.orders TABLE sys.part TABLE sys.partsupp TABLE sys.region TABLE sys.supplier </code></pre> Leave the session <pre><code>sql&gt; \q </code></pre> Stop MonetDB before we can continue our experiments <pre><code>./run.sh stop </code></pre> Reference: How to install MonetDB and the introduction of server and client programs. Run MonetDB with TPC-H queries MonetDB: server mode Invoke MonetDB with a specific number of threads (e.g. 1) <pre><code>mserver5 --set embedded_py=true --dbpath=/home/hanfeng/datafarm/2019/tpch1 --set monet_vault_key=/home/hanfeng/datafarm/2019/tpch1/.vaultkey --set gdk_nr_threads=1 </code></pre> Open a new terminal <pre><code>docker exec -it container-cc20 /bin/bash cd cc20/monetdb </code></pre> <strong><em>Note</em></strong>: Type <code>\q</code> to exit the server mode. Run with a specific number of threads (Two terminals required) 1 thread <pre><code>## terminal 1 mserver5 --set embedded_py=true --dbpath=/home/hanfeng/datafarm/2019/tpch1 --set monet_vault_key=/home/hanfeng/datafarm/2019/tpch1/.vaultkey --set gdk_nr_threads=1 ## terminal 2 (time ./runtest | mclient -d tpch1) &&gt; "log/log_thread_1.log" </code></pre> 2 threads <pre><code>## terminal 1 mserver5 --set embedded_py=true --dbpath=/home/hanfeng/datafarm/2019/tpch1 --set monet_vault_key=/home/hanfeng/datafarm/2019/tpch1/.vaultkey --set gdk_nr_threads=2 ## terminal 2 (time ./runtest | mclient -d tpch1) &&gt; "log/log_thread_2.log" </code></pre> 4 threads <pre><code>## terminal 1 mserver5 --set embedded_py=true --dbpath=/home/hanfeng/datafarm/2019/tpch1 --set monet_vault_key=/home/hanfeng/datafarm/2019/tpch1/.vaultkey --set gdk_nr_threads=4 ## terminal 2 (time ./runtest | mclient -d tpch1) &&gt; "log/log_thread_4.log" </code></pre> 8 threads <pre><code>## terminal 1 mserver5 --set embedded_py=true --dbpath=/home/hanfeng/datafarm/2019/tpch1 --set monet_vault_key=/home/hanfeng/datafarm/2019/tpch1/.vaultkey --set gdk_nr_threads=8 ## terminal 2 (time ./runtest | mclient -d tpch1) &&gt; "log/log_thread_8.log" </code></pre> 16 threads <pre><code>## terminal 1 mserver5 --set embedded_py=true --dbpath=/home/hanfeng/datafarm/2019/tpch1 --set monet_vault_key=/home/hanfeng/datafarm/2019/tpch1/.vaultkey --set gdk_nr_threads=16 ## terminal 2 (time ./runtest | mclient -d tpch1) &&gt; "log/log_thread_16.log" </code></pre> 32 threads <pre><code>## terminal 1 mserver5 --set embedded_py=true --dbpath=/home/hanfeng/datafarm/2019/tpch1 --set monet_vault_key=/home/hanfeng/datafarm/2019/tpch1/.vaultkey --set gdk_nr_threads=32 ## terminal 2 (time ./runtest | mclient -d tpch1) &&gt; "log/log_thread_32.log" </code></pre> 64 threads <pre><code>## terminal 1 mserver5 --set embedded_py=true --dbpath=/home/hanfeng/datafarm/2019/tpch1 --set monet_vault_key=/home/hanfeng/datafarm/2019/tpch1/.vaultkey --set gdk_nr_threads=64 ## terminal 2 (time ./runtest | mclient -d tpch1) &&gt; "log/log_thread_64.log" </code></pre> Post data processing - MonetDB Fetch average execution time (ms) <pre><code>grep -A 3 avg_query log/log_thread_1.log | python cut.py 699.834133333 // q1 85.9178666667 // q4 65.0172 // q6 101.730666667 // q12 58.212 // q14 60.1138666667 // q16 248.926466667 // q19 77.6482 // q22 grep -A 3 avg_query log/log_thread_2.log | python cut.py grep -A 3 avg_query log/log_thread_4.log | python cut.py grep -A 3 avg_query log/log_thread_8.log | python cut.py grep -A 3 avg_query log/log_thread_16.log | python cut.py grep -A 3 avg_query log/log_thread_32.log | python cut.py grep -A 3 avg_query log/log_thread_64.log | python cut.py </code></pre> <strong><em>Note</em></strong>: The above numbers can be copied to an Excel file for further analysis before plotting figures. Details can be found in Section 3. Run with HorseIR The HorsePower project can be found on GitHub. In the docker image, it has been placed in <code>/home/hanfeng/cc20/horse</code>. <pre><code>https://github.com/Sable/HorsePower </code></pre> Execution time We then run each query 15 times to get the average execution time (ms). <pre><code>(cd /home/hanfeng/cc20/horse/ && time ./run_all.sh) </code></pre> The script <code>run_all.sh</code> runs over three versions of generated C code based on different levels of optimizations. <pre><code>- naive : no optimization - opt1 : with optimizations - opt2 : with automatic fusion </code></pre> In each version, it first compiles its C code and runs the generated binary with a different number of threads (i.e. 1/2/4/8/16/32/64). Each run computes a query 15 times and returns the average. As a result, all output is saved into a log file, for example, <code>log/naive/log_q6.log</code> contains the result of query 6 in the naive version with all different number of threads. Log file structures <pre><code>log/naive/*.txt log/opt1/*.txt log/opt2/*.txt </code></pre> Fetch a brief summary of execution time from a log file <pre><code>cat log/naive/log_q6.txt | grep -E 'Run with 15 times' q06&gt;&gt; Run with 15 times, last 15 average (ms): 266.638 | 278.999 266.134 266.417 &lt;12 more&gt; # 1 thread q06&gt;&gt; Run with 15 times, last 15 average (ms): 138.556 | 144.474 137.837 137.579 &lt;12 more&gt; # 2 threads q06&gt;&gt; Run with 15 times, last 15 average (ms): 71.8851 | 75.339 72.102 72.341 &lt;12 more&gt; # 4 threads q06&gt;&gt; Run with 15 times, last 15 average (ms): 73.111 | 75.867 72.53 72.936 &lt;12 more&gt; # 8 threads q06&gt;&gt; Run with 15 times, last 15 average (ms): 56.1003 | 59.263 56.057 56.039 &lt;12 more&gt; # 16 threads q06&gt;&gt; Run with 15 times, last 15 average (ms): 56.8858 | 59.466 56.651 57.109 &lt;12 more&gt; # 32 threads q06&gt;&gt; Run with 15 times, last 15 average (ms): 53.4254 | 55.884 54.457 52.878 &lt;12 more&gt; # 64 threads </code></pre> It may become verbose when you have to extract information for all queries over three different kinds of versions. We provide a simple solution for it. <pre><code>./run.sh fetch log | python gen_for_copy.py </code></pre> Output data in the following format <pre><code>// query id | naive | opt1 | opt2 | ----------------------- | ... | ... | ... | # 1 thread | ... | ... | ... | # 2 threads ... ... ... | ... | ... | ... | # 64 threads </code></pre> Note that we copy the generated numbers into an Excel described in Section 3. Within an Excel file, we compare the performance difference in MonetDB and different versions of the generated C code. Compilation time Work directory <pre><code>/home/hanfeng/cc20/horse/codegen </code></pre> Fetch compilation time for different kinds of C code <pre><code>./run.sh compile naive &&gt; log_cc20_compile_naive.txt ./run.sh compile opt1 &&gt; log_cc20_compile_opt1.txt ./run.sh compile opt2 &&gt; log_cc20_compile_opt2.txt </code></pre> Let's look into the result of query 1 in the log file <code>log_cc20_compile_naive.txt</code>. <pre><code>Time variable usr sys wall GGC phase setup : 0.00 ( 0%) 0.00 ( 0%) 0.01 ( 5%) 1266 kB ( 18%) phase parsing : 0.07 ( 54%) 0.07 ( 88%) 0.14 ( 64%) 3897 kB ( 55%) phase opt and generate : 0.06 ( 46%) 0.01 ( 12%) 0.07 ( 32%) 1899 kB ( 27%) dump files : 0.00 ( 0%) 0.00 ( 0%) 0.02 ( 9%) 0 kB ( 0%) df reg dead/unused notes : 0.01 ( 8%) 0.00 ( 0%) 0.00 ( 0%) 31 kB ( 0%) register information : 0.00 ( 0%) 0.00 ( 0%) 0.01 ( 5%) 0 kB ( 0%) preprocessing : 0.03 ( 23%) 0.02 ( 25%) 0.08 ( 36%) 1468 kB ( 21%) lexical analysis : 0.00 ( 0%) 0.03 ( 38%) 0.05 ( 23%) 0 kB ( 0%) parser (global) : 0.04 ( 31%) 0.02 ( 25%) 0.01 ( 5%) 2039 kB ( 29%) tree SSA other : 0.00 ( 0%) 0.01 ( 12%) 0.00 ( 0%) 3 kB ( 0%) integrated RA : 0.01 ( 8%) 0.00 ( 0%) 0.01 ( 5%) 726 kB ( 10%) thread pro- & epilogue : 0.02 ( 15%) 0.00 ( 0%) 0.00 ( 0%) 41 kB ( 1%) shorten branches : 0.00 ( 0%) 0.00 ( 0%) 0.01 ( 5%) 0 kB ( 0%) final : 0.00 ( 0%) 0.00 ( 0%) 0.01 ( 5%) 56 kB ( 1%) initialize rtl : 0.01 ( 8%) 0.00 ( 0%) 0.01 ( 5%) 12 kB ( 0%) rest of compilation : 0.01 ( 8%) 0.00 ( 0%) 0.00 ( 0%) 62 kB ( 1%) TOTAL : 0.13 0.08 0.22 7072 kB </code></pre> The whole compilation time is split into many parts. We take the total wall time as the actual time spent on the code compilation. In this query, it needs 0.22 seconds to complete the whole compilation. (Note that manual work is required for retrieving the compilation time.) 3. Results We have a lot of numbers generated by our experiments. We use R and Excel to process these performance numbers and R for plotting figures. There are two kinds of data: (1) the data can be used in R directly, such as the execution time of various versions of C code with different number of threads; and (2) the data needs to pre-processed in Excel before it is sent to R. In order to make it easy for further data analysis, we intentionally provide scripts to make an Excel-friendly format that allows us to copy and paste numbers easily. Work directory <pre><code>/home/hanfeng/cc20/plot </code></pre> For example, the R script for generating figure 10 <pre><code>fig10/plot-gmeans.R </code></pre> The Excel file <pre><code>result-book.xlsx </code></pre> Note: We use RStudio Version 1.2.1335 to generate figures from our R scripts. Since RStudio is a GUI-based software, you can install it on another machine with GUI and run scripts from it.
description: See also the docker image on DockerHub: https://hub.docker.com/r/wukefe/cc20-docker.