Google Git
Sign in
apache / druid-website / refs/heads/20docs / . / blog / 2014 / 03 / 17 / benchmarking-druid.html
blob: f5dddda52f3b2a181867afa56af4bcc659c6813a [file] [log] [blame]
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8" />
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="Apache Druid">
<meta name="keywords" content="druid,kafka,database,analytics,streaming,real-time,real time,apache,open source">
<meta name="author" content="Apache Software Foundation">
<title>Druid | Benchmarking Druid</title>
<link rel="alternate" type="application/atom+xml" href="/feed">
<link rel="shortcut icon" href="/img/favicon.png">
<link rel="stylesheet" href="https://use.fontawesome.com/releases/v5.7.2/css/all.css" integrity="sha384-fnmOCqbTlWIlj8LyTjo7mOUStjsKC4pOpQbqyi7RrhN7udi9RwhKkMHpvLbHG9Sr" crossorigin="anonymous">
<link href='//fonts.googleapis.com/css?family=Open+Sans+Condensed:300,700,300italic|Open+Sans:300italic,400italic,600italic,400,300,600,700' rel='stylesheet' type='text/css'>
<link rel="stylesheet" href="/css/bootstrap-pure.css?v=1.1">
<link rel="stylesheet" href="/css/base.css?v=1.1">
<link rel="stylesheet" href="/css/header.css?v=1.1">
<link rel="stylesheet" href="/css/footer.css?v=1.1">
<link rel="stylesheet" href="/css/syntax.css?v=1.1">
<link rel="stylesheet" href="/css/docs.css?v=1.1">
<script>
(function() {
var cx = '000162378814775985090:molvbm0vggm';
var gcse = document.createElement('script');
gcse.type = 'text/javascript';
gcse.async = true;
gcse.src = (document.location.protocol == 'https:' ? 'https:' : 'http:') +
'//cse.google.com/cse.js?cx=' + cx;
var s = document.getElementsByTagName('script')[0];
s.parentNode.insertBefore(gcse, s);
})();
</script>
</head>
<body>
<!-- Start page_header include -->
<script src="//ajax.googleapis.com/ajax/libs/jquery/2.2.4/jquery.min.js"></script>
<div class="top-navigator">
<div class="container">
<div class="left-cont">
<a class="logo" href="/"><span class="druid-logo"></span></a>
</div>
<div class="right-cont">
<ul class="links">
<li class=""><a href="/technology">Technology</a></li>
<li class=""><a href="/use-cases">Use Cases</a></li>
<li class=""><a href="/druid-powered">Powered By</a></li>
<li class=""><a href="/docs/latest/design/">Docs</a></li>
<li class=""><a href="/community/">Community</a></li>
<li class="header-dropdown">
<a>Apache</a>
<div class="header-dropdown-menu">
<a href="https://www.apache.org/" target="_blank">Foundation</a>
<a href="https://www.apache.org/events/current-event" target="_blank">Events</a>
<a href="https://www.apache.org/licenses/" target="_blank">License</a>
<a href="https://www.apache.org/foundation/thanks.html" target="_blank">Thanks</a>
<a href="https://www.apache.org/security/" target="_blank">Security</a>
<a href="https://www.apache.org/foundation/sponsorship.html" target="_blank">Sponsorship</a>
</div>
</li>
<li class=" button-link"><a href="/downloads.html">Download</a></li>
</ul>
</div>
</div>
<div class="action-button menu-icon">
<span class="fa fa-bars"></span> MENU
</div>
<div class="action-button menu-icon-close">
<span class="fa fa-times"></span> MENU
</div>
</div>
<script type="text/javascript">
var $menu = $('.right-cont');
var $menuIcon = $('.menu-icon');
var $menuIconClose = $('.menu-icon-close');
function showMenu() {
$menu.fadeIn(100);
$menuIcon.fadeOut(100);
$menuIconClose.fadeIn(100);
}
$menuIcon.click(showMenu);
function hideMenu() {
$menu.fadeOut(100);
$menuIconClose.fadeOut(100);
$menuIcon.fadeIn(100);
}
$menuIconClose.click(hideMenu);
$(window).resize(function() {
if ($(window).width() >= 840) {
$menu.fadeIn(100);
$menuIcon.fadeOut(100);
$menuIconClose.fadeOut(100);
}
else {
$menu.fadeOut(100);
$menuIcon.fadeIn(100);
$menuIconClose.fadeOut(100);
}
});
</script>
<!-- Stop page_header include -->
<link rel="stylesheet" href="/css/blogs.css">
<div class="blog druid-header">
<div class="row">
<div class="col-md-8 col-md-offset-2">
<div class="title-image-wrap">
</div>
</div>
</div>
</div>
<div class="container blog">
<div class="row">
<div class="col-md-8 col-md-offset-2">
<div class="blog-entry">
<h1>Benchmarking Druid</h1>
<p class="text-muted">by <span class="author text-uppercase">Xavier Léauté</span> · March 17, 2014</p>
<p>We often get asked how fast Druid is. Despite having published some benchmark
numbers in <a href="/blog/2012/01/19/scaling-the-druid-data-store.html">previous blog posts</a>, as well as in our <a href="https://speakerdeck.com/druidio/">talks</a>,
until now, we have not actually published any data to back those claims up in in a
reproducible way. This post intends to address this and make it easier for
anyone to evaluate Druid and compare it to other systems out there.</p>
<p>Hopefully this blog post will help people get an idea of where Druid stands in
terms of query performance, how it performs under different scenarios, and what
the limiting factors may be under different configurations.</p>
<p>The objective of our benchmark is to showcase how Druid performs on the types
of workload it was designed for. We chose to benchmark Druid against MySQL
mainly because of its popularity, and to provide a point of comparison with
a storage engine that most users will be familiar with.</p>
<p>All the code to run the benchmarks as well as the <a href="https://github.com/druid-io/druid-benchmark/tree/master/results">raw result data</a> are
available on GitHub in the <a href="https://github.com/apache/incubator-druid-benchmark">druid-benchmark</a> repository.</p>
<blockquote>
<p>We would like to encourage our readers to run the benchmarks themselves and
share results for different data stores and hardware setups, as well as any
other optimizations that may prove valuable to the rest of the community or
make this benchmark more representative.</p>
</blockquote>
<h2 id="the-data">The Data</h2>
<p>Our objective is to make this benchmark reproducible, so we want a data set
that is readily available or that could easily be re-generated. The <a href="http://www.tpc.org/tpch/">TPC-H
benchmark</a> is commonly used to assess database performance, and the generated
data set can be of any size, which makes it attractive to understand how Druid
performs at various scales.</p>
<p>The majority of the data consists of time-based event records, which are
relatively simple to map to the Druid data model and also suits the type of
workload Druid was designed for.</p>
<p>The events in question span several years of daily data and include a varied set
of dimensions and metrics, including both very high cardinality and low
cardinality dimensions. For instance, the <code>l_partkey</code> column has 20,272,236
unique values, and <code>l_commitdate</code> has 2466 distinct dates in the 100GB dat
set.</p>
<p>The 1GB and 100GB data sets represent a total of 6,001,215 rows and 600,037,902
rows respectively.</p>
<h2 id="the-queries">The Queries</h2>
<p>Since Druid was built to solve a specific type of problem, we chose a set of
benchmarks typical of Druid&#39;s workload that covers the majority of queries we
observe in production. Why not use the TPC-H benchmark queries, you may ask?
Most of those queries do not directly apply to Druid, and we would have to
largely modify the queries or the data to fit the Druid model.</p>
<p>We put together three sets queries:</p>
<ul>
<li> Simple <code>select count(*)</code> queries over very large time ranges, covering
almost all the data set</li>
<li> Aggregate queries with one or several metrics, spanning the entire set of
rows, as well subsets on both time ranges and filtered dimension values.</li>
<li> Top-N queries on both high and low cardinality dimensions, with various
number of aggretions and filters.</li>
</ul>
<p>The SQL equivalent of the Druid queries is shown below. Druid queries are
generated directly in our <a href="https://github.com/apache/incubator-druid-benchmark/blob/master/benchmark-druid.R">benchmarking script</a> using <a href="https://github.com/metamx/RDruid/">RDruid</a>.</p>
<script src="https://gist.github.com/xvrl/9552286.js?file=queries.sql"></script>
<h2 id="generating-the-data">Generating the data</h2>
<p>We used the suite of <a href="http://www.tpc.org/tpch/spec/tpch_2_16_1.zip">TPC-H tools</a> suite of tools to generate two datasets, with
a target size of 1GB and 100GB respectively. The actual size of the resulting
table are 725MB and 74GB respectively, since we only generate the largest table
in the benchmark data set. The 100GB dataset is split into 1GB chunks to make
it easier to process.</p>
<p>All the generated data is available for download directly, so anyone can reproduce
our results, without having to go throught the trouble of compiling and running
the TPC-H tools.</p>
<script src="https://gist.github.com/xvrl/9552286.js?file=download-data.sh"></script>
<blockquote>
<p>If you would like to generate the datasets from scratch, or try out different
sizes, download and compile the <a href="http://www.tpc.org/tpch/spec/tpch_2_16_1.zip">TPC-H tools</a>, and use the <code>dbgen</code> tool to
generate the <code>lineitem</code> table.</p>
<div class="highlight"><pre><code class="language-sh" data-lang="sh"><span></span>./dbgen -TL -s1 <span class="c1"># 1GB</span>
./dbgen -TL -s100 -C100 <span class="c1"># 100GB / 100 chunks</span>
</code></pre></div>
<p>There is also a <a href="https://github.com/apache/incubator-druid-benchmark/blob/master/generate-data.sh"><code>generate-data.sh</code></a> script in our repository to help write
compressed data directly when generating large data sets.</p>
</blockquote>
<h2 id="setup">Setup</h2>
<h3 id="druid-cluster">Druid Cluster</h3>
<p>We are running the benchmark against Druid 0.6.62, which includes bug fixes for
some date parsing and case-sensitivity issues that cropped up while loading the
benchmark data.</p>
<p>Druid Compute nodes are running Amazon EC2 <code>m3.2xlarge</code> instances (8 cores, Intel Xeon
E5-2670 v2 @ 2.50GHz with 160GB SSD and 30GB of RAM) and broker nodes use
<code>c3.2xlarge</code> nodes (8 cores, Intel Xeon E5-2680 v2 @ 2.80GHz and 15GB of RAM).
Both run a standard Ubuntu 12.04.4 LTS and a 3.11 kernel.</p>
<p>For the 1GB data set, we run queries directly against a single compute node,
since the broker is unnecessary in that type of setup. For the 100GB data
set we first run against a single compute node and then scale out the
cluster to 6 compute nodes and issue queries against one broker node.</p>
<p>Compute nodes are configured with 8 processing threads (one per core), as
well as the default 1GB compute buffer, and 6GB of JVM heap. That leaves about
15GB of memory for memory mapping segment data, if we allow about 1GB for the
operating system and other overhead.</p>
<p>Broker nodes are configured with 12GB of JVM heap, and query chunking has been
disabled. This ensures queries do not get split up into sequential queries
and always run fully parallelized.</p>
<blockquote>
<p>Note: Interval chunking is turned on by default to prevent long interval
queries from taking up all compute resources at once. By default the maximum
interval that a single chunk can span is set to 1 month, which works well
for most production data sets Druid is being used for.</p>
<p>Interval chunking can be disabled by setting <code>druid.query.chunkPeriod</code> and
<code>druid.query.topN.chunkPeriod</code> to a very large value compared to the time
range of the data (in this case we used <code>P10Y</code>).</p>
</blockquote>
<p>Besides those settings, no other particular performance optimizations have been
made, and segment replication has been turned off in the datasource <a href="/docs/latest/Rule-Configuration.html">load
rules</a>.</p>
<p>Complete <a href="https://github.com/druid-io/druid-benchmark/tree/master/config">Druid and JVM configuration parameters</a> are published in our
<a href="https://github.com/apache/incubator-druid-benchmark">repository</a>.</p>
<h3 id="mysql">MySQL</h3>
<p>Our MySQL setup is an Amazon RDS instance (version 5.6.13) running on the same
instance type as Druid compute nodes (<code>m3.2xlarge</code>) using the MyISAM engine.</p>
<p>We used the default Amazon settings, although we experimented with enabling
memory mapping (<code>myisam_use_mmap</code>). However, this appeared to degrade
performance significantly, so our results are with memory mapping turned off.</p>
<blockquote>
<p>Note: We also ran some test against the InnoDB engine, but it appeared to be
quite a bit slower when compared to MyISAM. This was the case for all the
benchmark queries except for the <code>count_star_interval</code> query, even when
setting <code>innodb_buffer_pool_size</code> to very large values.</p>
</blockquote>
<h2 id="loading-the-data">Loading the data</h2>
<h3 id="druid">Druid</h3>
<p>We use the <a href="/docs/latest/Indexing-Service.html">Druid indexing service</a> configured to use
an Amazon EMR Hadoop cluster to load the data and create the necessary Druid
segments. The data is being loaded off of S3, so you will have to adjust the
input paths in the <a href="https://github.com/apache/incubator-druid-benchmark/blob/master/lineitem.task.json">task descriptor files</a> to point to your
own hadoop input path, as well as provide your own hadoop coordinates artifact.</p>
<script src="https://gist.github.com/xvrl/9552286.js?file=load-druid.sh"></script>
<p>For the larger data set we configure the <a href="http://druid.io/docs/latest/Tasks.html">hadoop index task</a> to
create monthly segments, each of which is sharded into partitions of at most
5,000,000 rows if necessary. We chose those settings in order to achieve
similar segment sizes for both data sets, thus giving us roughly constant
segment scan time which gives us good scaling properties and makes comparison
easier.</p>
<p>The resulting Druid segments consist of:</p>
<ul>
<li> a single 589MB segment for the 1GB data set,</li>
<li> 161 segments totaling 83.4GB (average segment size of 530MB) for the 100GB
data set.</li>
</ul>
<p>In our case the indexing service took about 25 minutes per segment for both
datasets. The additional sharding step for the larger data set only adds a few
minutes, so with the right amount of Hadoop resources, loading could take as
little as half an hour.</p>
<h1 id="mysql">MySQL</h1>
<p>Loading the data into MySQL is fairly simple using the client&#39;s local file
option. Assuming the <code>tpch</code> database already exists on the server, the
following command creates the necessary table, indices, loads the data and
optimizes the table. Keep in mind you will first need to uncompress the data
files prior to loading them.</p>
<script src="https://gist.github.com/xvrl/9552286.js?file=load-mysql.sh"></script>
<p>Loading the data itself is relatively fast, but it may take several hours to
create the necessary indices and optimizing the table on the larger data set.
In our case it took several attempts to complete the indexing and table
optimization steps.</p>
<h2 id="running-the-benchmarks">Running the Benchmarks</h2>
<h3 id="druid">Druid</h3>
<p>Running the Druid benchmark requires <a href="http://cran.rstudio.com/">R</a>, as well as a couple of packages,
including <a href="https://github.com/metamx/RDruid/"><code>RDruid</code></a>, <code>microbenchmark</code>, as well as <code>ggplot2</code> if you would
like to generate the plots.</p>
<script src="https://gist.github.com/xvrl/9552286.js?file=benchmark-druid.sh"></script>
<h3 id="mysql">MySQL</h3>
<p>The SQL queries for the benchmark are stored in the <code>queries-mysql.sql</code> file, and we provide a convenient script to run all or part of the benchmark.</p>
<script src="https://gist.github.com/xvrl/9552286.js?file=benchmark-mysql.sh"></script>
<h2 id="benchmark-results">Benchmark Results</h2>
<h3 id="1gb-data-set">1GB data set</h3>
<p>In a single node configuration, with a single segment, Druid will only use a
single processing thread, so neither MySQL nor Druid benefit from more than one
core in this case.</p>
<p><img src="/assets/druid-benchmark-1gb-median.png" alt=""></p>
<p>We see that Druid performs almost all the queries in less than one second and
is anywhere between 2x and 15x faster than vanilla MySQL. On <code>select count(*)</code>
queries it achieves scan rates of 53,539,211 rows/second, and 36,246,533
rows/second for aggregate <code>select sum(float)</code> queries.</p>
<p>Since Druid uses column-oriented storage, it performs better on queries using
fewer columns, and as more columns become part of the query it is expected to
lose some of its advantage compared to row-oriented storage engines.</p>
<div class="highlight"><pre><code class="language-text" data-lang="text"><span></span># 1GB data set
# median query times (seconds) over 100 runs
query druid mysql
count_star_interval 0.1112114 1.770
sum_all 0.6074772 2.400
sum_all_filter 0.5058156 2.415
sum_all_year 0.6100049 3.440
sum_price 0.1655666 2.070
top_100_commitdate 0.4150540 3.880
top_100_parts 0.5897905 3.850
top_100_parts_details 1.3785018 4.540
top_100_parts_filter 0.7332013 3.550
</code></pre></div>
<h3 id="100gb-data-set-on-a-single-node">100GB data set on a single node</h3>
<p>For the much larger data set, on a single node, Druid can take advantage of all
8 cores, parallelizing workload across multiple segments at once. Since only
about 15GB of RAM is available for segment data, not all of it can be paged into
memory at once, especially when querying multiple columns at a time. Segments
will get paged in and out by the operating system, and having SSD storage in this
case significantly helps to reduce the amount of time spent paging data in.</p>
<p>Druid really shines at this scale, even on a single node. The comparison may be
a little unfair, since MySQL can only take advantage of a single core per
query, but even considering that, Druid is still between 45x and 145x faster.</p>
<p>Compared to the 1GB data set, a simple <code>select sum(float)</code> query takes only 26
times as long, even though we have 100 times the number of rows.</p>
<p>The most expensive type of queries for Druid are Top-N queries over very high
cardinality dimensions (exceeding 20 million for the <code>top_x_parts</code> queries).
Those types of queries may require multiple passes over the data in case
compute buffers are not large enough to hold all the results.</p>
<p>MySQL essentially becomes unusable for interactive queries at this scale. Most
queries take at least 10 minutes to complete, while more expensive ones can
take several hours.</p>
<p><img src="/assets/druid-benchmark-100gb-median.png" alt=""></p>
<div class="highlight"><pre><code class="language-text" data-lang="text"><span></span># 100GB data set (single node)
# median query times (seconds) - 20 runs Druid, 3-5 runs MySQL
query druid mysql
count_star_interval 2.632399 177.95
sum_all 14.503592 663.93
sum_all_filter 10.202358 590.96
sum_all_year 14.481295 673.97
sum_price 4.240469 624.21
top_100_commitdate 7.402270 706.64
top_100_parts 113.565130 9961.12
top_100_parts_details 181.108950 12173.46
top_100_parts_filter 57.717676 5516.37
</code></pre></div>
<h1 id="scaling-up-druid">Scaling up Druid</h1>
<p>Druid makes it very straightforward to scale the cluster and take advantage of
additional nodes. Simply firing up more compute nodes will trigger the
coordinator to redistribute the data among the additional nodes, and within a
few minutes, the workload will be distributed, without requiring any downtime.</p>
<p>We see that Druid scales almost linearly for queries that involve mainly column
scans, with queries performing 5x to 6x faster than on a single core.</p>
<p>For Top-N queries the speedup is less, between 4x and 5x, which is expected,
since a fair amount of merging work has to be done at the broker level to merge
results for those type of queries on high-cardinality dimensions.</p>
<p><img src="/assets/druid-benchmark-scaling.png" alt=""></p>
<div class="highlight"><pre><code class="language-text" data-lang="text"><span></span># median query times (seconds) over 20 runs
query druid (1 node) druid (6 nodes)
count_star_interval 2.632399 0.4061503
sum_all 14.503592 2.2583412
sum_all_filter 10.202358 1.9062494
sum_all_year 14.481295 2.2554939
sum_price 4.240469 0.6515721
top_100_commitdate 7.402270 1.4426543
top_100_parts 113.565130 22.9146193
top_100_parts_details 181.108950 32.9310563
top_100_parts_filter 57.717676 14.3942355
</code></pre></div>
<h2 id="conclusions-and-future-work">Conclusions and future work</h2>
<p>In publishing a reproducible benchmark, as well as our data and methodology, we
hope we gave more tangible evidence of Druid&#39;s performance characteristics, as
well as a reference comparison with a more familiar database. We hope the
community will contribute benchmarks for other data stores in the future.</p>
<p>Unsurprisingly, a conventional data store such as MySQL quickly breaks down at
the scale of data that is increasingly becoming the norm these days. Druid was
designed to solve a specific set of problems where other generic solutions stop
working.</p>
<p>We have shown that Druid performs well whether in single or multi-node
configurations and is able to take full advantage of modern hardware with many
cores and large amounts of memory. Its ability to quickly scale horizontally
allows to adapt to various workloads, with query performance scaling almost
linearly for typical production workloads.</p>
<p>That being said, Druid still requires a fair amount of knowledge to choose
optimal configuration settings and pick good segment size/sharding properties.
We are planning to write a blog post dedicated to performance tuning where we
will address those questions more directly.</p>
</div>
</div>
</div>
</div>
<!-- Start page_footer include -->
<footer class="druid-footer">
<div class="container">
<div class="text-center">
<p>
<a href="/technology">Technology</a>&ensp;·&ensp;
<a href="/use-cases">Use Cases</a>&ensp;·&ensp;
<a href="/druid-powered">Powered by Druid</a>&ensp;·&ensp;
<a href="/docs/latest/">Docs</a>&ensp;·&ensp;
<a href="/community/">Community</a>&ensp;·&ensp;
<a href="/downloads.html">Download</a>&ensp;·&ensp;
<a href="/faq">FAQ</a>
</p>
</div>
<div class="text-center">
<a title="Join the user group" href="https://groups.google.com/forum/#!forum/druid-user" target="_blank"><span class="fa fa-comments"></span></a>&ensp;·&ensp;
<a title="Follow Druid" href="https://twitter.com/druidio" target="_blank"><span class="fab fa-twitter"></span></a>&ensp;·&ensp;
<a title="GitHub" href="https://github.com/apache/druid" target="_blank"><span class="fab fa-github"></span></a>
</div>
<div class="text-center license">
Copyright © 2020 <a href="https://www.apache.org/" target="_blank">Apache Software Foundation</a>.<br>
Except where otherwise noted, licensed under <a rel="license" href="http://creativecommons.org/licenses/by-sa/4.0/">CC BY-SA 4.0</a>.<br>
Apache Druid, Druid, and the Druid logo are either registered trademarks or trademarks of The Apache Software Foundation in the United States and other countries.
</div>
</div>
</footer>
<script async src="https://www.googletagmanager.com/gtag/js?id=UA-131010415-1"></script>
<script>
window.dataLayer = window.dataLayer || [];
function gtag(){dataLayer.push(arguments);}
gtag('js', new Date());
gtag('config', 'UA-131010415-1');
</script>
<script>
function trackDownload(type, url) {
ga('send', 'event', 'download', type, url);
}
</script>
<script src="//code.jquery.com/jquery.min.js"></script>
<script src="//maxcdn.bootstrapcdn.com/bootstrap/3.2.0/js/bootstrap.min.js"></script>
<script src="/assets/js/druid.js"></script>
<!-- stop page_footer include -->
</body>
</html>