Skip to main content

Managing Queries

info

Prior to Server 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.

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, via a specified requested records per second (rps) value.

Tuning Queries

Aerospike allows you to tune various parameters that control the query subsystem, including:

For more information regarding these configuration parameters and their default values, please see Configuration Reference.

Updating Query Settings

The above 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

List long queries with Tools package 6.2.x or later:

asadm -e 'show jobs query'

List queries with asinfo on Server 5.7 or later:

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

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

List queries with asinfo on Server 5.6 or earlier:

Fields returned by the aql 'show queries' command:

Abort Queries

Kill a Query Job:

Kill a running query with Tools package 6.2.x or later:

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

Kill a running query with asinfo on Server 5.7 or later:

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

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

Kill a running SI query with asinfo on Server 5.6 or earlier:

asinfo -v 'jobs:module=query;cmd=kill-job;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 Server version 6.0 onwards.

Recommendations

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

By default, query_in_transaction_thread and query_req_in_query_thread are set to '0'. Set them to '1' when the database is in memory or when the SI query returns fewer records.

asadm – Killing Jobs
asinfo - Reference
aql – Query and Scan Management