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:
- Declare the total number of query threads allowed (
query-threads-limit
). - Set the number of query threads allocated per long query (
single-query-threads
). - Decide whether to run a short query in the service thread, rather than hand it off to a query thread. Set the
inline-short-queries
configuration parameter totrue
if your use case prioritizes short query latency over single-record command latency. - Throttle the max records per-second rate of a background query (
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.
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.
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:3000Namespace |test |testModule |query |queryType |basic |basicProgress % |100.0 |100.0Transaction ID |15021089193528544137|15021089193528544137Time Since Done |00:12:51 |00:12:51active-threads |0 |0from |127.0.0.1+60036 |172.17.0.3+55372n-pids-requested |2.048 K |2.048 Knet-io-bytes |30.000 B |149.000 Bnet-io-time |00:00:00 |00:00:00recs-failed |0.000 |0.000recs-filtered-bins|0.000 |0.000recs-filtered-meta|0.000 |0.000recs-succeeded |0.000 |1.000recs-throttled |0.000 |0.000rps |0.000 |0.000run-time |00:00:00 |00:00:00set |testset |testsetsindex-name |mysindex |mysindexsocket-timeout |00:00:30 |00:00:30status |done(ok) |done(ok)Number of rows: 23
List queries with asinfo:
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
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
Kill a running query
asadm -e 'enable; manage jobs kill trid <jobid>'
Kill a running query with asinfo on Database 5.7 or earlier:
asinfo -v 'query-abort:trid=<jobid>'
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
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 |
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) msecJun 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) countJun 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) msecJun 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) countJun 16 2022 17:02:22 GMT: INFO (info): (hist.c:340) (10: 0000000001)
Jun 15 2022 23:37:48 GMT: INFO (info): (hist.c:321) histogram dump: {test}-query (1 total) msecJun 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) countJun 15 2022 23:37:48 GMT: INFO (info): (hist.c:340) (10: 0000000001)
SI Query Microbenchmarks
SI query microbenchmarks are not supported from Database 6.0 onwards.
` Enable writing microbenchmarks to the logs:
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"