Manage queries

This page describes how to manage Aerospike Database queries: create and remove indexes, tune query performance, list and abort long-running query jobs, and monitor query statistics and histograms.

Adding and removing secondary indexes

Queries do not require a secondary index. Any query can run as a primary index (PI) query, which examines every record in the namespace or set. A secondary index (SI) optimizes queries by indexing bin values so the server reads only matching records. You can create secondary indexes customized to your data to minimize query cost and latency. The query optimizer and Developer SDK AEL path select indexes for matching queries. Adding or dropping an index changes which indexes are available.

How you create and drop secondary indexes depends on your workflow:

• Use Aerospike Admin (asadm) manage sindex commands when you manage indexes interactively. asadm gives you tab completion for parameter entry, pairs with show sindex for listing, and with enable --warn names the nodes a command affects and requires confirmation before it runs.
• Use sindex-* info commands when you need to script index changes, call the server directly from automation, or integrate index create/drop into your own tooling.
• Use set indexes when PI queries target a specific set and you want to avoid querying records outside that set.

Caution

Dropping an SI can change which index the query optimizer selects, or send the query to the primary index when no other index can serve it. Queries that name the dropped index explicitly still return AS_ERR_SINDEX_NOT_FOUND (201) or AS_ERR_SINDEX_NOT_READABLE (203). Background and aggregation queries always require an explicit index filter. Review dependent queries after index changes.

Note

How you detect PI fallback depends on the query’s expected duration, which the client declares. Long queries (the default) are tracked, so monitor show jobs queries for unexpected PI fallback (sindex-name = --). Short queries are not tracked and never appear there. Watch query statistics and query histograms for rising PI load.

Tuning queries

Tune query performance with these configuration parameters:

• Cap query threads per node: query-threads-limit
• Set threads per long query: single-query-threads
• Run short queries in service threads for lower latency: inline-short-queries
• Limit background query throughput: background-query-max-rps

Update query settings

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

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.

For long foreground queries:

• SI queries with an explicit index filter populate sindex-name in show jobs queries (since Database 6.1.0).
• PI queries omit sindex-name (-- in asadm output). Unexpected -- on a workload that previously used an SI indicates PI fallback.
• A query that carries no AEL and names no index runs against the primary index. See Query optimizer.

Note

Prior to Database 6.0.0, PI queries were called scans and SI queries were called queries. Both types of query are managed through a unified interface and run on a common subsystem.

Database 6.0.0 and later

Use the following command to list 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 the trid (query transaction id) will list all active queries and up to query-max-done most recently completed long queries. On busy clusters, use show jobs modifiers such as -flip, -where, and for to filter and project output instead of listing every job.

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:

asinfo -v 'query-show'
asinfo -v 'query-show:trid=JOB_ID'

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

Database 5.7.0 and earlier

Caution

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

List queries with asinfo on Database 5.7.0 or earlier:

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

In Database 5.7.0 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

Stop queries

Stop a long-running query job on one or more cluster nodes when it consumes too many resources or runs longer than expected.

Stop a query job

Database 6.0.0 and later

Stop a running query

asadm -e 'enable; manage jobs kill trids JOB_ID'

Database 5.7.0 and earlier

Stop a running query with asinfo on Database 5.7.0 or earlier:

asinfo -v 'query-abort:trid=JOB_ID'

Caution

The following command was deprecated in Database 5.7.0.

Stop a running SI query with asinfo on Database 5.7.0 or earlier:

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

Important statistics to monitor

asadm -e 'show stat namespace for test like query'

Database 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_error	si_query_long_basic_error
si_query_short_basic_complete	si_query_long_basic_complete
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

Database 5.7.0 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.

Database 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)

Database 5.7.0 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

Database 6.0.0 and later

SI query microbenchmarks are not supported in Database 6.0.0 and later as they are mostly superseded given the rework of the query subsystem in that version. See Secondary Index Transaction Analysis for details.

Database 5.7.0 and earlier

See Secondary Index Transaction Analysis for details.

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"

Note

Enable query microbenchmarks only if you need to debug long-running SI queries in versions prior to Database 6.0.0.

Related documentation

Query optimizer

Queries

asadm – Stopping jobs

asinfo - Reference