DOAN DuyHai created CASSANDRA-8180:
--------------------------------------
Summary: Optimize disk seek using min/max colunm name meta data
when the LIMIT clause is used
Key: CASSANDRA-8180
URL: https://issues.apache.org/jira/browse/CASSANDRA-8180
Project: Cassandra
Issue Type: Improvement
Components: Core
Environment: Cassandra 2.0.10
Reporter: DOAN DuyHai
Priority: Minor
I was working on an example of sensor data table (timeseries) and face a use
case where C* does not optimize read on disk.
{code}
cqlsh:test> CREATE TABLE test(id int, col int, val text, PRIMARY KEY(id,col))
WITH CLUSTERING ORDER BY (col DESC);
cqlsh:test> INSERT INTO test(id, col , val ) VALUES ( 1, 10, '10');
...
>nodetool flush test test
...
cqlsh:test> INSERT INTO test(id, col , val ) VALUES ( 1, 20, '20');
...
>nodetool flush test test
...
cqlsh:test> INSERT INTO test(id, col , val ) VALUES ( 1, 30, '30');
...
>nodetool flush test test
{code}
After that, I activate request tracing:
{code}
cqlsh:test> SELECT * FROM test WHERE id=1 LIMIT 1;
activity |
timestamp | source | source_elapsed
---------------------------------------------------------------------------+--------------+-----------+----------------
execute_cql3_query |
23:48:46,498 | 127.0.0.1 | 0
Parsing SELECT * FROM test WHERE id=1 LIMIT 1; |
23:48:46,498 | 127.0.0.1 | 74
Preparing statement |
23:48:46,499 | 127.0.0.1 | 253
Executing single-partition query on test |
23:48:46,499 | 127.0.0.1 | 930
Acquiring sstable references |
23:48:46,499 | 127.0.0.1 | 943
Merging memtable tombstones |
23:48:46,499 | 127.0.0.1 | 1032
Key cache hit for sstable 3 |
23:48:46,500 | 127.0.0.1 | 1160
Seeking to partition beginning in data file |
23:48:46,500 | 127.0.0.1 | 1173
Key cache hit for sstable 2 |
23:48:46,500 | 127.0.0.1 | 1889
Seeking to partition beginning in data file |
23:48:46,500 | 127.0.0.1 | 1901
Key cache hit for sstable 1 |
23:48:46,501 | 127.0.0.1 | 2373
Seeking to partition beginning in data file |
23:48:46,501 | 127.0.0.1 | 2384
Skipped 0/3 non-slice-intersecting sstables, included 0 due to tombstones |
23:48:46,501 | 127.0.0.1 | 2768
Merging data from memtables and 3 sstables |
23:48:46,501 | 127.0.0.1 | 2784
Read 2 live and 0 tombstoned cells |
23:48:46,501 | 127.0.0.1 | 2976
Request complete |
23:48:46,501 | 127.0.0.1 | 3551
{code}
We can clearly see that C* hits 3 SSTables on disk instead of just one,
although it has the min/max column meta data to decide which SSTable contains
the most recent data.
Funny enough, if we add a clause on the clustering column to the select, this
time C* optimizes the read path:
{code}
cqlsh:test> SELECT * FROM test WHERE id=1 AND col > 25 LIMIT 1;
activity |
timestamp | source | source_elapsed
---------------------------------------------------------------------------+--------------+-----------+----------------
execute_cql3_query |
23:52:31,888 | 127.0.0.1 | 0
Parsing SELECT * FROM test WHERE id=1 AND col > 25 LIMIT 1; |
23:52:31,888 | 127.0.0.1 | 60
Preparing statement |
23:52:31,888 | 127.0.0.1 | 277
Executing single-partition query on test |
23:52:31,889 | 127.0.0.1 | 961
Acquiring sstable references |
23:52:31,889 | 127.0.0.1 | 971
Merging memtable tombstones |
23:52:31,889 | 127.0.0.1 | 1020
Key cache hit for sstable 3 |
23:52:31,889 | 127.0.0.1 | 1108
Seeking to partition beginning in data file |
23:52:31,889 | 127.0.0.1 | 1117
Skipped 2/3 non-slice-intersecting sstables, included 0 due to tombstones |
23:52:31,889 | 127.0.0.1 | 1611
Merging data from memtables and 1 sstables |
23:52:31,890 | 127.0.0.1 | 1624
Read 1 live and 0 tombstoned cells |
23:52:31,890 | 127.0.0.1 | 1700
Request complete |
23:52:31,890 | 127.0.0.1 | 2140
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
