Manage queries

Queries can be initiated in parallel, and each query uses its own dedicated threads so that queries will not interfere with each other.

Long queries can be independently throttled with a specified requested records per second RPS value.

Note

Prior to Database 6.0, primary index (PI) queries were called scans and secondary index (SI) queries were called queries. Both types of query are managed through a unified interface and run on a common subsystem.

Tuning queries

You can tune various parameters that control the query subsystem, including:

• The number of threads allocated per long query. See single-query-threads
• The overall query threads allowed (query-threads-limit)
• Max RPS for background queries (background-query-max-rps)

Update query settings

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

Note

Tools package 6.0.x or later is required to use asadm’s manage config commands. Otherwise, use the equivalent asinfo - set-config command.

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

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

Query Job Management

Query job management is only relevant to long queries.

List Queries

Database 6.0.0 and later

Use the following command to list long queries with asadm:

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 a trid (transaction id) will list all active queries and up to query-max-done most recently completed long queries.

Example for an SI 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 against Database 6.0 or later:

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

Examples (against Database 6.2 — Database 6.1 does not display the sindex-name):

• 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

Database 5.7 and earlier

The following command was deprecated in Database 5.7 and will be removed later.

List queries with asinfo on Database 5.7 or earlier:

asinfo -v 'jobs:module=query'
asinfo -v 'jobs:module=scan'

In Database 5.7 and earlier, only active SI long queries are tracked. Completed SI long queries cannot be listed. Completed scans are listed. scans-max-done configures the number of completed scans to display.

Fields returned by the asinfo ‘jobs:’ command:

Admin+> asinfo -v "jobs:"
jupyter-aerospike-2dexamp-2dctive-2dnotebooks-2dulhcwu6s:3000 (10.56.2.49) returned:
module=scan:trid=4736363721677119439:job-type=basic:ns=sandbox:priority=0:n-pids-requested=4096:rps=0:active-threads=0:status=done(ok):job-progress=100.00:run-time=482:time-since-done=337626:recs-throttled=5000:recs-filtered-meta=0:recs-filtered-bins=0:recs-succeeded=5000:recs-failed=0:net-io-bytes=5456714:socket-timeout=30000:from=127.0.0.1+60188

Abort Queries

Kill a Query Job:

Database 6.0 and later

Kill a running query:

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

Database 5.7 and earlier

Kill a running query with asinfo on Database 5.7 or earlier:

asinfo -v 'query-abort:trid=<jobid>'

The following command was deprecated in Database 5.7.

Kill a running SI query with asinfo on Database 5.7 or earlier:

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

Important Statistics to Monitor

Admin> show stat namespace for test like query

Version 6.0.0 and later

** Basic PI Queries **

Short Query	Long Query
pi_query_short_basic_complete	pi_query_long_basic_complete
pi_query_short_basic_error	pi_query_long_basic_error
N/A	pi_query_long_basic_abort
pi_query_short_basic_timeout	N/A

** Basic SI Queries **

Short Query	Long Query
si_query_short_basic_complete	si_query_long_basic_complete
si_query_short_basic_error	si_query_long_basic_error
N/A	si_query_long_basic_abort
si_query_short_basic_timeout	N/A

** UDF Background Queries **

PI Query	SI Query
pi_query_udf_bg_complete	si_query_udf_bg_complete
pi_query_udf_bg_error	si_query_udf_bg_error
pi_query_udf_bg_abort	si_query_udf_bg_abort

** Operations Background Queries **

PI Query	SI Query
pi_query_ops_bg_complete	si_query_ops_bg_complete
pi_query_ops_bg_error	si_query_ops_bg_error
pi_query_ops_bg_abort	si_query_ops_bg_abort

** Aggregation Queries **

PI Query	SI Query
pi_query_aggr_complete	si_query_aggr_complete
pi_query_aggr_error	si_query_aggr_error
pi_query_aggr_abort	si_query_aggr_abort

Version 5.7 and earlier

query_basic_error
query_udf_bg_error

Query Histograms

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

Version 6.0.0 and later

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)

Version 5.7 and earlier

Jun 15 2022 23:37:48 GMT: INFO (info): (hist.c:321) histogram dump: {test}-query (1 total) msec
Jun 15 2022 23:37:48 GMT: INFO (info): (hist.c:340)  (03: 0000000001)
Jun 15 2022 23:37:48 GMT: INFO (info): (hist.c:321) histogram dump: {test}-query-rec-count (1 total) count
Jun 15 2022 23:37:48 GMT: INFO (info): (hist.c:340)  (10: 0000000001)

SI Query Microbenchmarks

Version 6.0 and later

SI query microbenchmarks are not supported from Database 6.0 onwards.

Version 5.7 and earlier

` Enable writing microbenchmarks to the logs:

Note

Tools package 6.0.x or later is required to use asadm’s manage config commands. Otherwise, use the equivalent asinfo - set-config command.

asadm -e "enable; manage config service param query-microbenchmark to true"

Stop writing microbenchmarks to the logs:

asadm -e "enable; manage config service param query-microbenchmark to false"

Enable secondary-index-specific microbenchmarks:

asinfo -h [host ip] -v "sindex-histogram:ns=NAMESPACE;indexname=INDEX;enable=true"

Stop writing secondary-index-specific benchmarks to the logs:

asinfo -h [host ip] -v "sindex-histogram:ns=NAMESPACE;indexname=INDEX;enable=false"

Recommendations

Enable tracking only if you run long-running SI queries or for debugging.

Related Links

asadm – Killing jobs
asinfo - Reference