You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@crail.apache.org by at...@apache.org on 2018/08/08 14:43:27 UTC
incubator-crail-website git commit: SQL blog post,
part 1 about reading input data
Repository: incubator-crail-website
Updated Branches:
refs/heads/master 4a55efee8 -> cf945fa9e
SQL blog post, part 1 about reading input data
Project: http://git-wip-us.apache.org/repos/asf/incubator-crail-website/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-crail-website/commit/cf945fa9
Tree: http://git-wip-us.apache.org/repos/asf/incubator-crail-website/tree/cf945fa9
Diff: http://git-wip-us.apache.org/repos/asf/incubator-crail-website/diff/cf945fa9
Branch: refs/heads/master
Commit: cf945fa9ee355b5e1be91465df4ee220417a81b1
Parents: 4a55efe
Author: Animesh Trivedi <an...@gmail.com>
Authored: Wed Aug 8 16:42:15 2018 +0200
Committer: Animesh Trivedi <an...@gmail.com>
Committed: Wed Aug 8 16:42:15 2018 +0200
----------------------------------------------------------------------
site/_posts/blog/2018-08-15-sql-p1.md | 105 +++++++++++++++++++++++++++++
1 file changed, 105 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-crail-website/blob/cf945fa9/site/_posts/blog/2018-08-15-sql-p1.md
----------------------------------------------------------------------
diff --git a/site/_posts/blog/2018-08-15-sql-p1.md b/site/_posts/blog/2018-08-15-sql-p1.md
new file mode 100644
index 0000000..4ac463a
--- /dev/null
+++ b/site/_posts/blog/2018-08-15-sql-p1.md
@@ -0,0 +1,105 @@
+---
+layout: post
+title: "SQL Performance: Part 1 - Input File Formats"
+author: Animesh Trivedi, Patrick Stuedi, Jonas Pfefferle, Adrian Schuepbach, and Bernard Metzler
+category: blog
+comments: true
+---
+
+<div style="text-align: justify">
+<p>
+This is the first blog post in a multi-part series where we will focus on relational data processing performance (e.g., SQL) in presence of high-performance network and storage device - the kind of devices that Crail targets. Relational data processing is one of the most popular and versatile workloads people run in the cloud. The general idea is that data is stored in tables with a schema, and it is processed using a domain specific language like SQL. Examples of some popular system that support such relational data analytics in the cloud are <a href="https://spark.apache.org/sql/">Apache Spark/SQL</a>, <a href="https://hive.apache.org/">Apache Hive</a>, <a href="https://impala.apache.org/">Apache Impala</a>, etc. In this post, we will discuss the important first step in relational data processing, which is reading of input data tables.
+</p>
+</div>
+
+### Hardware and Software Configuration
+
+The specific cluster configuration used for the experiments in this blog:
+
+* Cluster
+ * 4 compute + 1 management node x86_64 cluster
+* Node configuration
+ * CPU: 2 x Intel(R) Xeon(R) CPU E5-2690 0 @ 2.90GHz
+ * DRAM: 256 GB DDR3
+ * Network: 1x100Gbit/s Mellanox ConnectX-5
+* Software
+ * Ubuntu 16.04.3 LTS (Xenial Xerus) with Linux kernel version 4.10.0-33-generic
+ * Apache HDFS (2.7.3)
+ * Apache Paruqet (1.8), Apache ORC (1.4), Apache Arrow (0.8), Apache Avro (1.4)
+ * <a href="https://github.com/apache/incubator-crail/">Apache Crail (incubating) with NVMeF support</a>, commit 64e635e5ce9411041bf47fac5d7fadcb83a84355 (since then Crail has a stable source release v1.0 with a newer NVMeF code-base)
+
+### Overview
+In a typical cloud-based relational data processing setup, the input data is stored on an external data storage solution like HDFS or AWS S3. Data tables and associated schema is converted into a storage friendly format to give optimal performance to a workload. Example of some very popular and familar file formats are [Apache Parquet](https://parquet.apache.org/), [Apache ORC](https://orc.apache.org/), [Apache Avro](https://avro.apache.org/), [JSON](https://en.wikipedia.org/wiki/JSON), etc. More recently, Apache Arrow is introduced to standardize the in-memory column data represenation between multiple frameworks. There is no one size fit all, and all these formtas have their own strengths, weaknesses, and features. In this blog, we are specifically interested in the performance of these formats on modern high-performance networking and storage devices.
+
+
+To benchmark the performance of file formats, we write a set of micro-benchmarks which are openly available at [https://github.com/zrlio/fileformat-benchmarks](https://github.com/zrlio/fileformat-benchmarks). We cannot use typical SQL micro-benchmarks because every SQL engine has its own favorite file format, on which it performs the best. Hence, in order to ensure parity, we decouple the performance of reading the input file format from the SQL query processing by writing a simple micro-benchmark. Our benchmark reads in the store_sales table from the TPC-DS dataset (scale factor 100), and calculates a sum of values present in the table. The table contains 23 columns of integers, doubles, and longs.
+
+<figure><div style="text-align:center"><img src ="{{ site.base }}/img/blog/sql-p1/performance-all.svg" width="550"/><figcaption>Figure 1: Performance of JSON, Avro, Parquet, ORC, and Avro on 100 Gbps network and NVMe flash.<p></p></figcaption></div></figure>
+
+We evaluated the performance of this benchmark on a 3 node HDFS cluster connected using 100 Gbps RoCE network. One datanode in HDFS contained 4 NVMe devices with a collective aggregate bandwidth of 12.5 GB/sec (equals to 100 Gbps, hence, we have a balanced network and storage performance). Figure 1 shows our results where none of the file format are able to deliver the full hardware performance for reading input files. A third of performance is already lost in HDFS (maximum throughput 74.9 Gbps out of possible 100 Gbps). The rest of the performance is lost inside a file format implementation, which needs to deal with encoding, buffer and I/O management, compression, etc. The best performer is Apache Arrow which is designed for in-memory columnar datasets. The performance of these file format are bounded by the performance of the CPU, which is 100% loaded during the experiment. For a detailed analysis of the file formats, please refer to out paper - [Albis: High-Performance File Form
at for Big Data Systems (USENIX, ATC’18)](https://www.usenix.org/conference/atc18/presentation/trivedi).
+
+### Albis: High-Performance File Format for Big Data Systems
+
+Based on these findings, we have proposed to use a new file format called Albis. Albis is built on similar design choices as Crail. The top-level idea is to leverage the performance of high-performance devices without being bottleneck by the performance of the CPU. Hence, in designing Albis we revisit many outdated assumptions about the nature of I/O in a distributed setting, and came up with the following ideas:
+
+ * No compression or encoding: modern network and storage devices are fast. Hence, there is no need to trade CPU cycles for performance. A 4 byte integer is stored as a 4 byte value.
+ * Keep the data/metadata management simple: Albis splits a table into row and column groups which are stored directly as it is to the hierarchical file and directory names on the underlying file system (e.g., HDFS or Crail).
+ * Careful object materialization using a binary API: to optimize the runtime representation in managed runtimes like JVM, only objects which are necessary for SQL processing are materizlized. Otherwise a 4 byte integer can be passed around as a byte array (using the binary API).
+
+
+<figure><div style="text-align:center"><img src ="{{ site.base }}/img/blog/sql-p1/core-scalability.svg" width="550"/><figcaption>Figure 2: Core scalability of JSON, Avro, Parquet, ORC, and Avro and Albis.<p></p></figcaption></div></figure>
+
+Using the Albis format, we revised our previous experiment where we read the input store_sales table from HDFS. In the figure above, we show the performance as the core scalability of file formats. At the right hand of the x-axis, we have performance with all 16 cores engaged, hence, representing the peak possible performance. As evident, Albis delivered 59.9 Gbps out of 74.9 Gbps possible bandwidth with HDFS over NVMe. Albis performance is 1.9-21.4x better than other formats that we have tested. To give an impression where the performance is coming from, in the table below we show some micro-architectural features for file formats. Our previously discussed designed ideas in Albis result in a shorter code path (shown as less instructions required for each row), better cache performance (shows as cache misses per row), and clearly better performance (shown as Nanoseconds required per row for processing). For detailed evaluation of Albis please refer to our paper.
+
+<table style="width:100%">
+ <caption> Table 1: Micro-architectual analysis for Parquet, ORC, Arrow, and Albis on a 16-core Xeon machine.<p></p></caption>
+ <tr>
+ <th></th>
+ <th>Parquet</th>
+ <th>ORC</th>
+ <th>Arrow</th>
+ <th>Albis</th>
+ </tr>
+ <tr>
+ <th>Instructions/row</th>
+ <td>6.6K</td>
+ <td>4.9K</td>
+ <td>1.9K</td>
+ <td>1.6K</td>
+ </tr>
+ <tr>
+ <th>Cache misses/row</th>
+ <td>9.2</td>
+ <td>4.6</td>
+ <td>5.1</td>
+ <td>3.0</td>
+ </tr>
+ <tr>
+ <th>Nanoseconds/row</th>
+ <td>105.3</td>
+ <td>63.0</td>
+ <td>31.2</td>
+ <td>20.8</td>
+ </tr>
+</table>
+<p></p>
+
+### Apache Crail (Incubating) with Albis
+
+For our final experiment, we try to answer the question what it would take to deliver the full 100 Gbps bandwidth for Albis. Certainly, the first bottleneck is to improve the base storage layer performance. Here we use Apache Crail (Incubating) with its NVMeF storage tier. As we shown in previous blog posts that Crail's NVMeF tier delivers performance (97.8 Gbps) very close to the full performance of NVMe devices over the RDMA network. Hence, Crail with Albis is a perfect setup to deliver the full 100 Gbps bandwidth. Before we get there, lets get some calculations right. The store_sales table in the TPC-DS dataset has a data density of 93.9% (out of 100 bytes, only 93.9 is data. Others are null values). As we measure the goodput, the expected performance of Albis on Crail is 93.9% of 97.8 Gbps bandwidth = 91.8 Gbps. In our experiments, Crail delivered 85.5 Gbps. Figure 2 shows more detailed results.
+
+<figure><div style="text-align:center"><img src ="{{ site.base }}/img/blog/sql-p1/albis-crail.svg" width="550"/><figcaption>Figure 2: Performance of Albis on Crail.<p></p></figcaption></div></figure>
+
+The left half of the figure shows the scaling performance of Albis on Crail from 1 core performance (8.9 Gbps) to 16 cores (85.5 Gbps). In comparison, the right half of the figure shows the performance of HDFS/NVMe at 59.9 Gbps and Crail/NVMe at 85.5 Gbps. The last bar shows the performance of Albis if the benchmark does not materialize Java object values. In this configuration, Albis on Crail delivers 91.3 Gbps, which is very close to the expected peak of 91.8 Gbps.
+
+### Summary
+<div style="text-align: justify">
+<p>
+In this first blog of a multipart series, we have looked at the data ingestion performance of file formats on high-performance networking and storage devices. We found that popular file formats are in need for a performance revision. Based on our analysis, we designed and implemeted Albis - a new file format for storing relational data. Albis and Crail share many design choices. Their combined performance of 85+ Gbps on a 100 Gbps link, gives us confidence in our apparoch and underlying software philosophy for both, Crail and Albis.
+</p>
+
+<p>
+Stay tuned for the next part where we look at workload-level performance in Spark/SQL on modern high-performance networking and stroage devices. Meanwhile lets us know if you have any feedback or comments.
+</p>
+</div>
+