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.

You can enable tracing through the driver as well. Depends what you are trying to measure. — Sotirios Delimanolis, Dec 03 '15 at 20:28

Aaron · Answer 1 · 2015-12-04T16:01:33.667

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!

Aaron, thanks for your reply... I have used tracing in cqlsh. but my table has about 50,000 records so tracing logs report is very long and passed away so that's why I can't get the starting time of the query execution but ending time is displayed. can anyone please tell me how can I access the starting point of the tracing report or can I store this tracing log report to some file...? I will also welcome to any other solution to my problem. thanks — Bilal Ehsan, Dec 04 '15 at 11:42

score 1 · Answer 2 · edited Jun 20 '20 at 09:12

1

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

edited Jun 20 '20 at 09:12

Community

1
1

answered Dec 03 '15 at 20:30

Fundhor

3,369
1
25
47

Nice! I didn't know you could do that from DevCenter. – Aaron Dec 03 '15 at 22:09

score 1 · Answer 3 · answered Oct 26 '16 at 02:59

(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';

score 1 · Answer 4 · answered Nov 18 '20 at 16:20

I attempted to use the tracing approach suggested by other answers, but something about the nature of my query meant that tracing was taking a very, very long time to return.

Since it was just a one-off timing for me to compare query performance between two options, I instead wrote a file with SQL commands to execute the command, getting timestamp before & after executing my query. I then copied the timestamps into Microsoft Excel and used it to calculate the difference in seconds (after stripping off the +0000 time zone information so Excel could understand it).

timing.sql

SELECT toTimestamp(now()) FROM system.local;
SELECT * from TABLE_1;
SELECT toTimestamp(now()) FROM system.local;
SELECT * from TABLE_2;
SELECT toTimestamp(now()) FROM system.local;

Execute timing.sql

cqlsh example.com -u my_user --file=timing.sql

Current time output

SELECT toTimestamp(now()) FROM system.local;

 system.totimestamp(system.now())
----------------------------------
  2020-11-18 16:10:35.745000+0000

Excel date difference

=(C1-B1)*60*60*24