title: Extremely poor performance with Tableau + Spark + Cassandra
body: <p>Currently I am in the process of investigating the possibility of using Cassandra in combination with Spark and Tableau for data analysis. However, the performance that I am currently experiencing with this setup is so poor that I cannot imagine using it for production purposes. As I am reading about how great the performance of the combination of Cassandra + Spark must be, I am obviously doing something wrong, yet I cannot find out what.</p>
  
  <p>My test data:</p>
  
  <ul>
  <li>All data is stored on a single node</li>
  <li>Queries are performed on a single table with 50MB (interval data)</li>
  <li>Columns used in selection criteria have an index on it</li>
  </ul>
  
  <p>My test setup:</p>
  
  <ul>
  <li>MacBook 2015, 1.1 GHz, 8GB memory, SSD, OS X El Capitan</li>
  <li>Virtual Box, 4GB memory, Ubuntu 14.04</li>
  <li>Single node wit Datastax Enterprise 4.8.4:
  
  <ul>
  <li>Apache Cassandra 2.1.12.1046</li>
  <li>Apache Spark 1.4.2.2</li>
  <li>Spark Connector 1.4.1</li>
  <li>Apache Thrift 0.9.3</li>
  <li>Hive Connector 0.2.11</li>
  </ul></li>
  <li>Tableau (Connected through ODBC)</li>
  </ul>
  
  <p>Findings:</p>
  
  <ul>
  <li>When a change in Tableau requires loading data from the database, it takes anywhere between 40s and 1.4 mins. to retrieve the data (which is basically unworkable)</li>
  <li>When I use Tableau in combination with Oracle instead of Cassandra + Spark, but on the same virtual box, I get the results almost instantaneously</li>
  </ul>
  
  <p>Here is the table definition used for the queries:</p>
  
  <pre><code>CREATE TABLE key.activity (
      interval timestamp,
      id bigint,
      activity_name text,
      begin_ts timestamp,
      busy_ms bigint,
      container_code text,
      duration_ms bigint,
      end_location_code text,
      end_ts timestamp,
      pallet_code text,
      src_location_code text,
      start_location_code text,
      success boolean,
      tgt_location_code text,
      transporter_name text,
      PRIMARY KEY (interval, id)
  ) WITH CLUSTERING ORDER BY (id ASC)
      AND bloom_filter_fp_chance = 0.01
      AND caching = '{"keys":"ALL", "rows_per_partition":"ALL"}'
      AND comment = ''
      AND compaction = {'class': 'org.apache.cassandra.db.compaction.SizeTieredCompactionStrategy'}
      AND compression = {'sstable_compression': 'org.apache.cassandra.io.compress.LZ4Compressor'}
      AND dclocal_read_repair_chance = 0.1
      AND default_time_to_live = 0
      AND gc_grace_seconds = 864000
      AND max_index_interval = 2048
      AND memtable_flush_period_in_ms = 0
      AND min_index_interval = 128
      AND read_repair_chance = 0.0
      AND speculative_retry = '99.0PERCENTILE';
  CREATE INDEX activity_activity_name_idx ON key.activity (activity_name);
  CREATE INDEX activity_success_idx ON key.activity (success);
  CREATE INDEX activity_transporter_name_idx ON key.activity (transporter_name);
  </code></pre>
  
  <p>Here is an example of a query produced by Tableau:</p>
  
  <pre><code>INFO  2016-02-10 20:22:21 org.apache.spark.sql.hive.thriftserver.SparkExecuteStatementOperation: Running query 'SELECT CASE WHEN 4 &gt;= 0 THEN SUBSTRING(`activity`.`transporter_name`,1,CAST(4 AS INT)) ELSE NULL END AS `calculation_185421691185008640`,
    AVG(CAST(`activity`.`busy_ms` AS DOUBLE)) AS `avg_busy_ms_ok`,
    CAST((MONTH(`activity`.`interval`) - 1) / 3 + 1 AS BIGINT) AS `qr_interval_ok`,
    `activity`.`transporter_name` AS `transporter_name`,
    YEAR(`activity`.`interval`) AS `yr_interval_ok`
  FROM `key`.`activity` `activity`
  GROUP BY CASE WHEN 4 &gt;= 0 THEN SUBSTRING(`activity`.`transporter_name`,1,CAST(4 AS INT)) ELSE NULL END,
    CAST((MONTH(`activity`.`interval`) - 1) / 3 + 1 AS BIGINT),
    `activity`.`transporter_name`,
    YEAR(`activity`.`interval`)'
  </code></pre>
  
  <p>Here is an example on statistics of a 52s query:</p>
  
  <p><a href="http://i.stack.imgur.com/IJkVI.png" rel="nofollow">Spark statistics on query taken 52 secs. to complete</a></p>
  
  <p>I've tried playing around with the partition keys as mentioned in other posts, but did not see a significant difference. I've also tried to enable row caching (Cassandra config + table property), but this also did not have any effect (although perhaps I have overlooked something there).</p>
  
  <p>I would have expected to get at least a factor 10x-20x better performance out of the box, even without fiddling around with all these parameters and I've run out of ideas what to do.</p>
  
  <p>What am I doing wrong? What performance should I expect?</p>