How to Terminate and simulate a long running query?


#1

How to Terminate a long running query

Context

It may be necessary to terminate a long running query in aql to free up ressources on a strained server.

Method

The following commands can be used to terminate a long running query:

aql command to list active queries:

aql -c "show queries"

asinfo command to kill a query by trid:

asinfo -v 'jobs:module=query;cmd=kill-job;trid=<TRID>'

One liner (for those who are slow to capture the trid):

aql -c "show queries";asadm -e "asinfo -v 'jobs:module=query;cmd=kill-job;trid=<$TRID>'";aql -c "show queries"

Example simulating and terminating a long running query

  • Create secondary Index:
aql -c "CREATE INDEX idx_foo ON test.demo (bin1) STRING"
  • Create 5000000 keys with a secondary index key of same value
for i in {1..5000000}; do echo "insert into test.demo(PK,bin1) values ('$i','hello')">>store.dat; done;
  • Load data:
aql -f store.dat
  • Query for the key:
aql -c "select * from test.demo where bin1='hello'" -T 100000
  • Use asadm and asinfo to terminate the query on all nodes:

Show Queries list and get the trid to terminate:

aql -c "show queries"

Example output:

+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
| ns | sindex-name | mem-usage | trid | job-progress | set | priority | module | status | recs-read | run-time | net-io-bytes | time-since-done |
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
| "test" | "idx_bin1" | 0 | -1105315606016573265 | "0.00" | "demo" | 10 | "query" | "active" | 538410 | 3666 | 37747512 | 0 |
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
1 row in set (0.000 secs)
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
| ns | sindex-name | mem-usage | trid | job-progress | set | priority | module | status | recs-read | run-time | net-io-bytes | time-since-done |
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
| "test" | "idx_bin1" | 0 | -1105315606016573265 | "0.00" | "demo" | 10 | "query" | "active" | 514110 | 3665 | 35650428 | 0 |
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
1 row in set (0.000 secs)
OK

Kill the query:

asinfo -v 'jobs:module=query;cmd=kill-job;trid=-1105315606016573265'

Example Output:

testvm1.citrusleaf.local:3000 (192.168.120.195) returned:
OK

OR a One-liner script (in case the query finished executing prior to issuing individual kill command) :

myvar=$(aql -c "show queries"|grep test|awk '{print $8}'|head -n1);echo $myvar;aql -c "show queries";asadm -e "asinfo -v 'jobs:module=query;cmd=kill-job;trid=$myvar'";aql -c "show queries"

Example output:

-1105315606016573265
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
| ns | sindex-name | mem-usage | trid | job-progress | set | priority | module | status | recs-read | run-time | net-io-bytes | time-since-done |
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
| "test" | "idx_bin1" | 0 | -1105315606016573265 | "0.00" | "demo" | 10 | "query" | "active" | 538410 | 3666 | 37747512 | 0 |
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
1 row in set (0.000 secs)
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
| ns | sindex-name | mem-usage | trid | job-progress | set | priority | module | status | recs-read | run-time | net-io-bytes | time-since-done |
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
| "test" | "idx_bin1" | 0 | -1105315606016573265 | "0.00" | "demo" | 10 | "query" | "active" | 514110 | 3665 | 35650428 | 0 |
+--------+-------------+-----------+----------------------+--------------+--------+----------+---------+----------+-----------+----------+--------------+-----------------+
1 row in set (0.000 secs)
OK


testvm1.citrusleaf.local:3000 (192.168.120.195) returned:
OK

testvm5.citrusleaf.local:3000 (192.168.120.208) returned:
OK

OK


Notes

asinfo should be ran on all nodes or should be used within asadm to ensure that command for terminating query has ran on all nodes.

Reference Links

Keywords

long running query kill terminate

Timestamp

11/15/2016