Skip to content

Instantly share code, notes, and snippets.

@PeterCorless

PeterCorless/01-create-table.cql

Last active September 3, 2019 20:07
Show Gist options
  • Save PeterCorless/9013884c21b877713639dddd01121129 to your computer and use it in GitHub Desktop.
Save PeterCorless/9013884c21b877713639dddd01121129 to your computer and use it in GitHub Desktop.
Download ZIP
cache-antipatterns
Raw
01-create-table.cql
CREATE TABLE ks.tbl (
uuid int,
time timestamp,
property text,
PRIMARY KEY (uuid, time))
Raw
02-select-variations.cql
select * from ks.tbl where uuid=1 and time >= '2019-08-22 00:00:00' and time <= <now>;
select * from ks.tbl where uuid=1 and time >= '2019-08-22 00:00:00'
Raw
03-start-with-empty-cache.cql
> select * from ks.tbl where uuid=1 and time = '2019-08-22 00:00:00' ;
uuid | time | property
------+---------------------------------+----------
1 | 2019-08-22 04:00:00.000000+0000 | exists
(1 rows)
Raw
04-trace1.cql
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2019-08-22 16:37:29.315000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing a statement [shard 1] | 2019-08-22 16:37:29.315099 | 127.0.0.1 | -- | 127.0.0.1
Processing a statement [shard 1] | 2019-08-22 16:37:29.315799 | 127.0.0.1 | 700 | 127.0.0.1
Creating read executor for token -4069959284402364209 with all: {127.0.0.1} targets: {127.0.0.1} repair decision: NONE [shard 1] | 2019-08-22 16:37:29.315948 | 127.0.0.1 | 849 | 127.0.0.1
read_data: querying locally [shard 1] | 2019-08-22 16:37:29.315952 | 127.0.0.1 | 852 | 127.0.0.1
Start querying the token range that starts with -4069959284402364209 [shard 0] | 2019-08-22 16:37:29.316036 | 127.0.0.1 | 17 | 127.0.0.1
Reading key {{-4069959284402364209, pk{000400000001}}} from sstable /home/glauber/data/data/ks/tbl-059a1151c51c11e9a6ea000000000000/mc-6-big-Data.db [shard 0] | 2019-08-22 16:37:29.316077 | 127.0.0.1 | 57 | 127.0.0.1
Querying is done [shard 0] | 2019-08-22 16:37:29.321915 | 127.0.0.1 | 5896 | 127.0.0.1
Done processing - preparing a result [shard 1] | 2019-08-22 16:37:29.322444 | 127.0.0.1 | 7345 | 127.0.0.1
Request complete | 2019-08-22 16:37:29.322359 | 127.0.0.1 | 7359 | 127.0.0.1
Raw
05-trace2.cql
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2019-08-22 16:40:32.790000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing a statement [shard 1] | 2019-08-22 16:40:32.790486 | 127.0.0.1 | 1 | 127.0.0.1
Processing a statement [shard 1] | 2019-08-22 16:40:32.791269 | 127.0.0.1 | 783 | 127.0.0.1
Creating read executor for token -4069959284402364209 with all: {127.0.0.1} targets: {127.0.0.1} repair decision: NONE [shard 1] | 2019-08-22 16:40:32.791361 | 127.0.0.1 | 875 | 127.0.0.1
read_data: querying locally [shard 1] | 2019-08-22 16:40:32.791366 | 127.0.0.1 | 880 | 127.0.0.1
Start querying the token range that starts with -4069959284402364209 [shard 0] | 2019-08-22 16:40:32.791487 | 127.0.0.1 | 19 | 127.0.0.1
Querying is done [shard 0] | 2019-08-22 16:40:32.791552 | 127.0.0.1 | 84 | 127.0.0.1
Done processing - preparing a result [shard 1] | 2019-08-22 16:40:32.791798 | 127.0.0.1 | 1312 | 127.0.0.1
Request complete | 2019-08-22 16:40:32.791326 | 127.0.0.1 | 1326 | 127.0.0.1
Raw
06-select-nonexistent-time.cql
cqlsh> select * from ks.tbl where uuid=1 and time = '2019-08-22 01:00:00' ;
uuid | time | property
------+------+----------
(0 rows)
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2019-08-22 16:46:43.334000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing a statement [shard 1] | 2019-08-22 16:46:43.334852 | 127.0.0.1 | -- | 127.0.0.1
Processing a statement [shard 1] | 2019-08-22 16:46:43.335611 | 127.0.0.1 | 760 | 127.0.0.1
Creating read executor for token -4069959284402364209 with all: {127.0.0.1} targets: {127.0.0.1} repair decision: NONE [shard 1] | 2019-08-22 16:46:43.335680 | 127.0.0.1 | 829 | 127.0.0.1
read_data: querying locally [shard 1] | 2019-08-22 16:46:43.335684 | 127.0.0.1 | 832 | 127.0.0.1
Start querying the token range that starts with -4069959284402364209 [shard 0] | 2019-08-22 16:46:43.335751 | 127.0.0.1 | 11 | 127.0.0.1
Reading key {{-4069959284402364209, pk{000400000001}}} from sstable /home/glauber/data/data/ks/tbl-059a1151c51c11e9a6ea000000000000/mc-6-big-Data.db [shard 0] | 2019-08-22 16:46:43.335792 | 127.0.0.1 | 52 | 127.0.0.1
Querying is done [shard 0] | 2019-08-22 16:46:43.337524 | 127.0.0.1 | 1784 | 127.0.0.1
Done processing - preparing a result [shard 1] | 2019-08-22 16:46:43.337730 | 127.0.0.1 | 2878 | 127.0.0.1
Request complete | 2019-08-22 16:46:43.336888 | 127.0.0.1 | 2888 | 127.0.0.1
Raw
07-trace3.cql
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2019-08-22 16:46:47.887000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing a statement [shard 1] | 2019-08-22 16:46:47.887229 | 127.0.0.1 | -- | 127.0.0.1
Processing a statement [shard 1] | 2019-08-22 16:46:47.887611 | 127.0.0.1 | 382 | 127.0.0.1
Creating read executor for token -4069959284402364209 with all: {127.0.0.1} targets: {127.0.0.1} repair decision: NONE [shard 1] | 2019-08-22 16:46:47.887648 | 127.0.0.1 | 419 | 127.0.0.1
read_data: querying locally [shard 1] | 2019-08-22 16:46:47.887650 | 127.0.0.1 | 422 | 127.0.0.1
Start querying the token range that starts with -4069959284402364209 [shard 0] | 2019-08-22 16:46:47.887704 | 127.0.0.1 | 13 | 127.0.0.1
Querying is done [shard 0] | 2019-08-22 16:46:47.887752 | 127.0.0.1 | 61 | 127.0.0.1
Done processing - preparing a result [shard 1] | 2019-08-22 16:46:47.887941 | 127.0.0.1 | 712 | 127.0.0.1
Request complete | 2019-08-22 16:46:47.887720 | 127.0.0.1 | 720 | 127.0.0.1
Raw
08-time-range-scan.cql
cqlsh> select * from ks.tbl where uuid=1 and time >= '2019-08-22 00:00:00' and time <= '2019-08-22 01:00:00';
uuid | time | property
------+---------------------------------+----------
1 | 2019-08-22 04:00:00.000000+0000 | exists
(1 rows)
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2019-08-22 16:52:42.330000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing a statement [shard 1] | 2019-08-22 16:52:42.330543 | 127.0.0.1 | -- | 127.0.0.1
Processing a statement [shard 1] | 2019-08-22 16:52:42.331722 | 127.0.0.1 | 1180 | 127.0.0.1
Creating read executor for token -4069959284402364209 with all: {127.0.0.1} targets: {127.0.0.1} repair decision: NONE [shard 1] | 2019-08-22 16:52:42.331795 | 127.0.0.1 | 1253 | 127.0.0.1
read_data: querying locally [shard 1] | 2019-08-22 16:52:42.331799 | 127.0.0.1 | 1256 | 127.0.0.1
Start querying the token range that starts with -4069959284402364209 [shard 0] | 2019-08-22 16:52:42.331858 | 127.0.0.1 | 15 | 127.0.0.1
Reading key {{-4069959284402364209, pk{000400000001}}} from sstable /home/glauber/data/data/ks/tbl-059a1151c51c11e9a6ea000000000000/mc-6-big-Data.db [shard 0] | 2019-08-22 16:52:42.331918 | 127.0.0.1 | 75 | 127.0.0.1
Querying is done [shard 0] | 2019-08-22 16:52:42.333794 | 127.0.0.1 | 1951 | 127.0.0.1
Done processing - preparing a result [shard 1] | 2019-08-22 16:52:42.334017 | 127.0.0.1 | 3475 | 127.0.0.1
Request complete | 2019-08-22 16:52:42.333488 | 127.0.0.1 | 3488 | 127.0.0.1
Raw
09-trace4.cql
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2019-08-22 16:57:47.181000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing a statement [shard 1] | 2019-08-22 16:57:47.181199 | 127.0.0.1 | -- | 127.0.0.1
Processing a statement [shard 1] | 2019-08-22 16:57:47.182443 | 127.0.0.1 | 1244 | 127.0.0.1
Creating read executor for token -4069959284402364209 with all: {127.0.0.1} targets: {127.0.0.1} repair decision: NONE [shard 1] | 2019-08-22 16:57:47.182514 | 127.0.0.1 | 1315 | 127.0.0.1
read_data: querying locally [shard 1] | 2019-08-22 16:57:47.182518 | 127.0.0.1 | 1319 | 127.0.0.1
Start querying the token range that starts with -4069959284402364209 [shard 0] | 2019-08-22 16:57:47.182602 | 127.0.0.1 | 17 | 127.0.0.1
Querying is done [shard 0] | 2019-08-22 16:57:47.182662 | 127.0.0.1 | 77 | 127.0.0.1
Done processing - preparing a result [shard 1] | 2019-08-22 16:57:47.182905 | 127.0.0.1 | 1706 | 127.0.0.1
Request complete | 2019-08-22 16:57:47.182718 | 127.0.0.1 | 1718 | 127.0.0.1
Raw
10-time-range-scan2.cql
select * from ks.tbl where uuid=1 and time >= '2019-08-22 00:00:00' and time <= '2019-08-22 01:00:00';
Raw
11-first-query.cql
# first query, existing range, expected to be in the cache
cqlsh> select * from ks.tbl where uuid=1 and time >= '2019-08-22 00:00:00' and time <= '2019-08-22 01:00:00';
uuid | time | property
------+---------------------------------+----------
1 | 2019-08-22 04:00:00.000000+0000 | exists
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2019-08-22 17:21:05.653000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing a statement [shard 1] | 2019-08-22 17:21:05.653635 | 127.0.0.1 | -- | 127.0.0.1
Processing a statement [shard 1] | 2019-08-22 17:21:05.654333 | 127.0.0.1 | 698 | 127.0.0.1
Creating read executor for token -4069959284402364209 with all: {127.0.0.1} targets: {127.0.0.1} repair decision: NONE [shard 1] | 2019-08-22 17:21:05.654386 | 127.0.0.1 | 751 | 127.0.0.1
read_data: querying locally [shard 1] | 2019-08-22 17:21:05.654389 | 127.0.0.1 | 754 | 127.0.0.1
Start querying the token range that starts with -4069959284402364209 [shard 0] | 2019-08-22 17:21:05.654477 | 127.0.0.1 | 16 | 127.0.0.1
Querying is done [shard 0] | 2019-08-22 17:21:05.654535 | 127.0.0.1 | 74 | 127.0.0.1
Done processing - preparing a result [shard 1] | 2019-08-22 17:21:05.654763 | 127.0.0.1 | 1128 | 127.0.0.1
Request complete | 2019-08-22 17:21:05.654138 | 127.0.0.1 | 1138 | 127.0.0.1
Raw
12-second-query.cql
# second query, larger range, what will happen with the cache?
cqlsh> select * from ks.tbl where uuid=1 and time >= '2019-08-22 00:00:00' and time <= '2019-08-22 02:00:00';
uuid | time | property
------+---------------------------------+----------
1 | 2019-08-22 04:00:00.000000+0000 | exists
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2019-08-22 17:21:08.541000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing a statement [shard 1] | 2019-08-22 17:21:08.541061 | 127.0.0.1 | -- | 127.0.0.1
Processing a statement [shard 1] | 2019-08-22 17:21:08.542320 | 127.0.0.1 | 1259 | 127.0.0.1
Creating read executor for token -4069959284402364209 with all: {127.0.0.1} targets: {127.0.0.1} repair decision: NONE [shard 1] | 2019-08-22 17:21:08.542384 | 127.0.0.1 | 1323 | 127.0.0.1
read_data: querying locally [shard 1] | 2019-08-22 17:21:08.542388 | 127.0.0.1 | 1327 | 127.0.0.1
Start querying the token range that starts with -4069959284402364209 [shard 0] | 2019-08-22 17:21:08.542448 | 127.0.0.1 | 18 | 127.0.0.1
Reading key {{-4069959284402364209, pk{000400000001}}} from sstable /home/glauber/data/data/ks/tbl-059a1151c51c11e9a6ea000000000000/mc-6-big-Data.db [shard 0] | 2019-08-22 17:21:08.542509 | 127.0.0.1 | 79 | 127.0.0.1
Querying is done [shard 0] | 2019-08-22 17:21:08.548221 | 127.0.0.1 | 5791 | 127.0.0.1
Done processing - preparing a result [shard 1] | 2019-08-22 17:21:08.548440 | 127.0.0.1 | 7379 | 127.0.0.1
Request complete | 2019-08-22 17:21:08.548392 | 127.0.0.1 | 7392 | 127.0.0.1
Raw
13-third-query.cql
# third query, even larger range.
cqlsh> select * from ks.tbl where uuid=1 and time >= '2019-08-22 00:00:00' and time <= '2019-08-22 03:00:00';
uuid | time | property
------+---------------------------------+----------
1 | 2019-08-22 04:00:00.000000+0000 | exists
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2019-08-22 17:21:10.910000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing a statement [shard 1] | 2019-08-22 17:21:10.910369 | 127.0.0.1 | -- | 127.0.0.1
Processing a statement [shard 1] | 2019-08-22 17:21:10.911626 | 127.0.0.1 | 1258 | 127.0.0.1
Creating read executor for token -4069959284402364209 with all: {127.0.0.1} targets: {127.0.0.1} repair decision: NONE [shard 1] | 2019-08-22 17:21:10.911693 | 127.0.0.1 | 1324 | 127.0.0.1
read_data: querying locally [shard 1] | 2019-08-22 17:21:10.911696 | 127.0.0.1 | 1327 | 127.0.0.1
Start querying the token range that starts with -4069959284402364209 [shard 0] | 2019-08-22 17:21:10.911774 | 127.0.0.1 | 14 | 127.0.0.1
Reading key {{-4069959284402364209, pk{000400000001}}} from sstable /home/glauber/data/data/ks/tbl-059a1151c51c11e9a6ea000000000000/mc-6-big-Data.db [shard 0] | 2019-08-22 17:21:10.911826 | 127.0.0.1 | 65 | 127.0.0.1
Querying is done [shard 0] | 2019-08-22 17:21:10.913674 | 127.0.0.1 | 1914 | 127.0.0.1
Done processing - preparing a result [shard 1] | 2019-08-22 17:21:10.913907 | 127.0.0.1 | 3538 | 127.0.0.1
Request complete | 2019-08-22 17:21:10.913549 | 127.0.0.1 | 3549 | 127.0.0.1
Raw
14-no-end-to-query.cql
select * from ks.tbl where uuid=1 and time >= '2019-08-22 00:00:00'
Raw
15-cache-kept-up-to-date.cql
cqlsh> insert into ks.tbl (uuid, time, property) values (1, '2019-08-22 01:00:00', 'exists too');
# between those two operations, nodetool flush was called to make sure data was not in memtables
cqlsh> select * from ks.tbl where uuid=1 and time >= '2019-08-22 00:00:00';
uuid | time | property
------+---------------------------------+------------
1 | 2019-08-22 04:00:00.000000+0000 | exists
1 | 2019-08-22 05:00:00.000000+0000 | exists too
(2 rows)
activity | timestamp | source | source_elapsed | client
----------------------------------------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------+-----------
Execute CQL3 query | 2019-08-22 17:26:34.012000 | 127.0.0.1 | 0 | 127.0.0.1
Parsing a statement [shard 1] | 2019-08-22 17:26:34.012680 | 127.0.0.1 | -- | 127.0.0.1
Processing a statement [shard 1] | 2019-08-22 17:26:34.013489 | 127.0.0.1 | 809 | 127.0.0.1
Creating read executor for token -4069959284402364209 with all: {127.0.0.1} targets: {127.0.0.1} repair decision: NONE [shard 1] | 2019-08-22 17:26:34.013568 | 127.0.0.1 | 888 | 127.0.0.1
read_data: querying locally [shard 1] | 2019-08-22 17:26:34.013571 | 127.0.0.1 | 892 | 127.0.0.1
Start querying the token range that starts with -4069959284402364209 [shard 0] | 2019-08-22 17:26:34.013624 | 127.0.0.1 | 13 | 127.0.0.1
Querying is done [shard 0] | 2019-08-22 17:26:34.013689 | 127.0.0.1 | 78 | 127.0.0.1
Done processing - preparing a result [shard 1] | 2019-08-22 17:26:34.013901 | 127.0.0.1 | 1221 | 127.0.0.1
Request complete | 2019-08-22 17:26:34.013238 | 127.0.0.1 | 1238 | 127.0.0.1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment