Skip to content
Webinar - July 10: Cut Infra Costs by 80% with Smarter DRAM, NVMe & Cloud Storage StrategyRegister now

Manage queries

Adding and removing secondary indexes

You can use the Aerospike Admin (asadm) tool to create and drop secondary indexes. Alternatively, you can use the sindex-* info commands to do the same.

You can also add and remove set indexes with asadm.

Tuning queries

You can tune the query subsystem using several configuration parameters to:

Update query settings

The query parameters can be dynamically set in the cluster using asadm, using the following command:

Terminal window
asadm -e "enable; manage config service param <name> to <value>"

Where <name> is the configuration parameter name and <value> is the parameter value.

List Queries

Listing and aborting queries is only relevant to long queries.

Use the following command to list queries with asadm:

Terminal window
asadm -e 'show jobs queries'

Configure the number of completed queries to track with the query-max-done parameter. Active long queries are always tracked. Not specifying the trid (query transaction id) will list all active queries and up to query-max-done most recently completed long queries.

Example: query returning a single record

Admin+> show jobs queries trid 15021089193528544137
~~~~~~~~~~~~~Query Jobs (2022-11-30 23:21:42 UTC)~~~~~~~~~~~~~
Node              |mycluster-1:3000    |172.17.0.2:3000
Namespace         |test                |test
Module            |query               |query
Type              |basic               |basic
Progress %        |100.0               |100.0
Transaction ID    |15021089193528544137|15021089193528544137
Time Since Done   |00:12:51            |00:12:51
active-threads    |0                   |0
from              |127.0.0.1+60036     |172.17.0.3+55372
n-pids-requested  |2.048 K             |2.048 K
net-io-bytes      |30.000 B            |149.000 B
net-io-time       |00:00:00            |00:00:00
recs-failed       |0.000               |0.000
recs-filtered-bins|0.000               |0.000
recs-filtered-meta|0.000               |0.000
recs-succeeded    |0.000               |1.000
recs-throttled    |0.000               |0.000
rps               |0.000               |0.000
run-time          |00:00:00            |00:00:00
set               |testset             |testset
sindex-name       |mysindex            |mysindex
socket-timeout    |00:00:30            |00:00:30
status            |done(ok)            |done(ok)
Number of rows: 23

List queries with asinfo:

Terminal window
asinfo -v 'query-show'
asinfo -v 'query-show:trid=<jobid>'

Examples:

  • This example shows a PI query that times out on the client side. The default timeout of 1 second is used on aql causing the server to fail returning all the records. Instead, the query returns a subset of the 1M records that were on the namespace:
Admin+> asinfo -v 'query-show:trid=15648753051941266254'
mycluster-1:3000 (172.17.0.3) returned:
trid=15648753051941266254:job-type=basic:ns=test:n-pids-requested=2048:rps=0:active-threads=0:status=done(abandoned-response-timeout):job-progress=100.00:run-time=1066:time-since-done=14817072:recs-throttled=0:recs-filtered-meta=0:recs-filtered-bins=0:recs-succeeded=250319:recs-failed=0:net-io-bytes=17826771:net-io-time=856:socket-timeout=30000:from=127.0.0.1+59856


172.17.0.2:3000 (172.17.0.2) returned:
trid=15648753051941266254:job-type=basic:ns=test:n-pids-requested=2048:rps=0:active-threads=0:status=done(abandoned-response-timeout):job-progress=100.00:run-time=1055:time-since-done=14816025:recs-throttled=0:recs-filtered-meta=0:recs-filtered-bins=0:recs-succeeded=276896:recs-failed=0:net-io-bytes=20972559:net-io-time=854:socket-timeout=30000:from=172.17.0.3+55192
  • This example shows an SI query that returns a single record:
Admin+> asinfo -v 'query-show:trid=15021089193528544137'
mycluster-1:3000 (172.17.0.3) returned:
trid=15021089193528544137:job-type=basic:ns=test:set=testset:sindex-name=mysindex:n-pids-requested=2048:rps=0:active-threads=0:status=done(ok):job-progress=100.00:run-time=29:time-since-done=502877:recs-throttled=0:recs-filtered-meta=0:recs-filtered-bins=0:recs-succeeded=0:recs-failed=0:net-io-bytes=30:net-io-time=0:socket-timeout=30000:from=127.0.0.1+60036


172.17.0.2:3000 (172.17.0.2) returned:
trid=15021089193528544137:job-type=basic:ns=test:set=testset:sindex-name=mysindex:n-pids-requested=2048:rps=0:active-threads=0:status=done(ok):job-progress=100.00:run-time=24:time-since-done=502880:recs-throttled=0:recs-filtered-meta=0:recs-filtered-bins=0:recs-succeeded=1:recs-failed=0:net-io-bytes=149:net-io-time=0:socket-timeout=30000:from=172.17.0.3+55372

Abort Queries

Kill a Query Job

Kill a running query

Terminal window
asadm -e 'enable; manage jobs kill trid <jobid>'

Important Statistics to Monitor

Admin> show stat namespace for test like query

Query Histograms

An overall query histogram is written to the log file every 10 seconds. For more details refer histograms page.

Jun 16 2022 17:02:22 GMT: INFO (info): (hist.c:321) histogram dump: {test}-pi-query (1 total) msec
Jun 16 2022 17:02:22 GMT: INFO (info): (hist.c:340)  (07: 0000000001)
Jun 16 2022 17:02:22 GMT: INFO (info): (hist.c:321) histogram dump: {test}-pi-query-rec-count (1 total) count
Jun 16 2022 17:02:22 GMT: INFO (info): (hist.c:340)  (10: 0000000001)
Jun 16 2022 17:02:22 GMT: INFO (info): (hist.c:321) histogram dump: {test}-si-query (1 total) msec
Jun 16 2022 17:02:22 GMT: INFO (info): (hist.c:340)  (02: 0000000001)
Jun 16 2022 17:02:22 GMT: INFO (info): (hist.c:321) histogram dump: {test}-si-query-rec-count (1 total) count
Jun 16 2022 17:02:22 GMT: INFO (info): (hist.c:340)  (10: 0000000001)

SI Query Microbenchmarks

SI query microbenchmarks are not supported from Database 6.0 onwards as they are mostly obsolete given the rework of the query subsystem in that version. See Secondary Index Transaction Analaysis for details.

asadm – Killing jobs
asinfo - Reference

Feedback