Cassandra Query execution time analysis

Question

I am new with Cassandra CQL, I want to get the Cassandra query execution time. Can i do it in CQL shell by storing the current time in the variable, execute the query and then store the current time in another variable and calculate the actual execution time by taking the difference of both variables. Can anyone guide me.

Answer 1

From within cqlsh, your best option is probably to use tracing (output shortened for brevity):

aploetz@cqlsh:stackoverflow> tracing on;
Now Tracing is enabled
aploetz@cqlsh:stackoverflow> SELECT * FROM sujata WHERE id=2;

 id | roll_number | age
----+-------------+-----
  2 |          10 |  26
  2 |          20 |  26

(2 rows)

Tracing session: 35072590-99fb-11e5-beaa-8b496c707234

 activity                                                                                        | timestamp                  | source    | source_elapsed
-------------------------------------------------------------------------------------------------+----------------------------+-----------+----------------
                                                                              Execute CQL3 query | 2015-12-03 14:19:51.027000 | 127.0.0.1 |              0
                                  Parsing SELECT * FROM sujata WHERE id=2; [SharedPool-Worker-1] | 2015-12-03 14:19:51.034000 | 127.0.0.1 |          12378
                                                       Preparing statement [SharedPool-Worker-1] | 2015-12-03 14:19:51.035000 | 127.0.0.1 |          13415
                                 Executing single-partition query on roles [SharedPool-Worker-2] | 2015-12-03 14:19:51.036000 | 127.0.0.1 |          14052
    .................................................
                                         Read 2 live and 0 tombstone cells [SharedPool-Worker-2] | 2015-12-03 14:19:51.054001 | 127.0.0.1 |          32768
                                                                                Request complete | 2015-12-03 14:19:51.063069 | 127.0.0.1 |          36069

Edit:

can I store this tracing log report to some file...?

Yes, you can. If I were to run the above trace from the Linux command line, and output that to a file, I would start by creating a file to hold my cqlsh commands:

aploetz@dockingBay94:~/cql$ cat traceSujata.cql 

use stackoverflow;
tracing on;
SELECT * FROM sujata WHERE id=2;

Then, I'd use the -f flag on cqlsh to run commands from that file, and then redirect the output to another text file.

aploetz@dockingBay94:~/cql$ cqlsh -f traceSujata.cql > queryTrace_20151204.txt

Now you can peruse the query trace file at your leisure!

Answer 2

Option A

With datastax devcenter you directly have access to the request used time.

Go in the "query_trace" tab, just next to "Results".

More info : http://docs.datastax.com/en/developer/devcenter/doc/devcenter/dcQueryTrace.html

Option B

tracing on

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

Nb : Option A uses Option B

Answer 3

(1) If query is a small, just use like:

use nishi;
tracing on;
select * from family where name='nkantkumar';

(2) If query statement is very big say 1k, 5k select statement at a time.

cd <CASSANDRA_HOME>/bin
cqlsh -f '/apps/nkantkumar/query.txt' > '/apps/nkantkumar/traceQuery.cql'

here your query file will be like:-

use nishi;

tracing on;

select * from family where name='nkantkumar';

select * from family where name='nkantkumar1';

select * from family where name='nkantkumar2';