How to get tombstone count for a cql query?

Question

I am trying to evaluate number of tombstones getting created in one of tables in our application. For that I am trying to use nodetool cfstats. Here is how I am doing it:

create table demo.test(a int, b int, c int, primary key (a));
insert into demo.test(a, b, c) values(1,2,3);

Now I am making the same insert as above. So I expect 3 tombstones to be created. But on running cfstats for this columnfamily, I still see that there are no tombstones created.

nodetool cfstats demo.test
Average live cells per slice (last five minutes): 0.0
Average tombstones per slice (last five minutes): 0.0

Now I tried deleting the record, but still I don't see any tombstones getting created. Is there any thing that I am missing here? Please suggest.

BTW a few other details, * We are using version 2.1.1 of the Java driver * We are running against Cassandra 2.1.0

Solution

For tombstone counts on a query your best bet is to enable tracing. This will give you the in depth history of a query including how many tombstones had to be read to complete it. This won't give you the total tombstone count, but is most likely more relevant for performance tuning.

In cqlsh you can enable this with

cqlsh> tracing on;
Now tracing requests.
cqlsh> SELECT * FROM ascii_ks.ascii_cs  where pkey = 'One';

 pkey | ckey1 | data1
------+-------+-------
  One |   One |   One

(1 rows)


Tracing session: 2569d580-719b-11e4-9dd6-557d7f833b69

 activity                                                                 | timestamp    | source    | source_elapsed
--------------------------------------------------------------------------+--------------+-----------+----------------
                                                       execute_cql3_query | 08:26:28,953 | 127.0.0.1 |              0
 Parsing SELECT * FROM ascii_ks.ascii_cs  where pkey = 'One' LIMIT 10000; | 08:26:28,956 | 127.0.0.1 |           2635
                                                      Preparing statement | 08:26:28,960 | 127.0.0.1 |           6951
                             Executing single-partition query on ascii_cs | 08:26:28,962 | 127.0.0.1 |           9097
                                             Acquiring sstable references | 08:26:28,963 | 127.0.0.1 |          10576
                                                Merging memtable contents | 08:26:28,963 | 127.0.0.1 |          10618
                                              Merging data from sstable 1 | 08:26:28,965 | 127.0.0.1 |          12146
                                              Key cache hit for sstable 1 | 08:26:28,965 | 127.0.0.1 |          12257
                                                    Collating all results | 08:26:28,965 | 127.0.0.1 |          12402
                                                         Request complete | 08:26:28,965 | 127.0.0.1 |          12638

http://www.datastax.com/dev/blog/tracing-in-cassandra-1-2

Comments

[Prasanth]

Thanks RussS for the reply. But I really did not understand which part of the tracing actually speaks about the number of tombstones that are read. Can you please provide a little more detail on this?

[phact]

you can also get Average tombstones per slice (last five minutes) from nodetool cfstats

[Prasanth]

yeah.. but that does not work.. And that's the reason why RussS has suggested to turn on query tracing..

[8forty]

@PrasanthNath: the answer's example doesn't show it, but the trace output will have tombstone info, e.g.: Read 101 live and 85 tombstone cells [SharedPool-Worker-4] | 2015-07-29 14:57:36.895000 | 192.168.12.93 | 25264

[actf]

So why are there no tombstones shown in the trace output? Like @PrasanthNath suggested in his original question, I would expect to see 3 tombstones in the trace output.

[Mr'Black]

Guys, just run some "nodetool cfstats keyspace | tee tombstone.txt", analyze the content and identify the tables with tombstones. Enable tracing, run a query like RussS` and you`ll notice in the tracing output how the read process occurs, how many tombstones are actually parsed to fulfill a query.