--- title: "Manage queries" description: "Manage Aerospike Database queries: tune performance, handle secondary indexes, list/abort jobs, and monitor statistics." --- # Manage queries > For the complete documentation index see: [llms.txt](https://aerospike.com/docs/llms.txt) > > All documentation pages available in markdown. ## Adding and removing secondary indexes You can use the [Aerospike Admin (`asadm`)](https://aerospike.com/docs/database/tools/asadm) tool to [create and drop secondary indexes](https://aerospike.com/docs/database/tools/asadm/live-mode/#sindex). Alternatively, you can use the [`sindex-*`](https://aerospike.com/docs/database/reference/info#sindex-create) info commands to do the same. You can also [add and remove set indexes](https://aerospike.com/docs/database/manage/namespace/sets#adding-a-set-index) 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`](https://aerospike.com/docs/database/reference/config#service__query-threads-limit)). - Set the number of query threads allocated per [long query](https://aerospike.com/docs/develop/learn/queries/#query-runtime-optimization) ([`single-query-threads`](https://aerospike.com/docs/database/reference/config#namespace__single-query-threads)). - Decide whether to run a [short query](https://aerospike.com/docs/develop/learn/queries/#query-runtime-optimization) in the service thread, rather than hand it off to a query thread. Set the [`inline-short-queries`](https://aerospike.com/docs/database/reference/config#namespace__inline-short-queries) configuration parameter to `true` 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`](https://aerospike.com/docs/database/reference/config#namespace__background-query-max-rps)) ### Update query settings The query parameters can be dynamically set in the cluster using asadm, using the following command: Terminal window ```bash asadm -e "enable; manage config service param to " ``` Where `` is the configuration parameter name and `` is the parameter value. ## List Queries Listing and aborting queries is only relevant to _long queries_. ::: note Prior to Database 6.0.0, primary index (PI) queries were called _scans_ and secondary index (SI) queries were called _queries_. Both types of [query](https://aerospike.com/docs/develop/learn/queries) are managed through a unified interface and run on a common subsystem. ::: - [Database 6.0.0 and later](#tab-panel-2504) - [Database 5.7.0 and earlier](#tab-panel-2505) Use the following command to list queries with [`asadm`](https://aerospike.com/docs/database/tools/asadm): Terminal window ```bash asadm -e 'show jobs queries' ``` Configure the number of completed queries to track with the [`query-max-done`](https://aerospike.com/docs/database/reference/config#service__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 ```plaintext 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](https://aerospike.com/docs/database/tools/asinfo): Terminal window ```bash asinfo -v 'query-show' asinfo -v 'query-show:trid=' ``` ### 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: ```plaintext 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: ```plaintext 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 ``` ::: caution The following command was deprecated in Database 5.7.0 and will be removed later. ::: List queries with [asinfo](https://aerospike.com/docs/database/reference/info) on Database 5.7.0 or earlier: Terminal window ```bash 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: ```plaintext 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.0 and later](#tab-panel-2496) - [Database 5.7.0 and earlier](#tab-panel-2497) Kill a running query Terminal window ```bash asadm -e 'enable; manage jobs kill trid ' ``` Kill a running query with [asinfo](https://aerospike.com/docs/database/reference/info) on Database 5.7.0 or earlier: Terminal window ```bash asinfo -v 'query-abort:trid=' ``` ::: caution The following command was deprecated in Database 5.7.0. ::: Kill a running SI query with [asinfo](https://aerospike.com/docs/database/reference/info) on Database 5.7.0 or earlier: Terminal window ```bash asinfo -v 'jobs:module=query;cmd=kill-job;trid=' ``` ## Important Statistics to Monitor ```sql Admin> show stat namespace for test like query ``` - [Version 6.0.0 and later](#tab-panel-2502) - [Version 5.7.0 and earlier](#tab-panel-2503) ### Basic PI Queries | Short Query | Long Query | | --- | --- | | [pi\_query\_short\_basic\_complete](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_short_basic_complete) | [pi\_query\_long\_basic\_complete](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_long_basic_complete) | | [pi\_query\_short\_basic\_error](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_short_basic_error) | [pi\_query\_long\_basic\_error](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_long_basic_error) | | N/A | [pi\_query\_long\_basic\_abort](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_long_basic_abort) | | [pi\_query\_short\_basic\_timeout](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_short_basic_timeout) | N/A | ### Basic SI Queries | Short Query | Long Query | | --- | --- | | [si\_query\_short\_basic\_error](https://aerospike.com/docs/database/reference/metrics#sindex__si_query_short_basic_error) | [si\_query\_long\_basic\_error](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_long_basic_error) | | [si\_query\_short\_basic\_complete](https://aerospike.com/docs/database/reference/metrics#sindex__si_query_short_basic_complete) | [si\_query\_long\_basic\_complete](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_long_basic_complete) | | N/A | [si\_query\_long\_basic\_abort](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_long_basic_abort) | | [si\_query\_short\_basic\_timeout](https://aerospike.com/docs/database/reference/metrics#sindex__si_query_short_basic_timeout) | N/A | ### UDF Background Queries | PI Query | SI Query | | --- | --- | | [pi\_query\_udf\_bg\_complete](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_udf_bg_complete) | [si\_query\_udf\_bg\_complete](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_udf_bg_complete) | | [pi\_query\_udf\_bg\_error](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_udf_bg_error) | [si\_query\_udf\_bg\_error](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_udf_bg_error) | | [pi\_query\_udf\_bg\_abort](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_udf_bg_abort) | [si\_query\_udf\_bg\_abort](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_udf_bg_abort) | ### Operations Background Queries | PI Query | SI Query | | --- | --- | | [pi\_query\_ops\_bg\_complete](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_ops_bg_complete) | [si\_query\_ops\_bg\_complete](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_ops_bg_complete) | | [pi\_query\_ops\_bg\_error](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_ops_bg_error) | [si\_query\_ops\_bg\_error](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_ops_bg_error) | | [pi\_query\_ops\_bg\_abort](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_ops_bg_abort) | [si\_query\_ops\_bg\_abort](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_ops_bg_abort) | ### Aggregation Queries | PI Query | SI Query | | --- | --- | | [pi\_query\_aggr\_complete](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_aggr_complete) | [si\_query\_aggr\_complete](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_aggr_complete) | | [pi\_query\_aggr\_error](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_aggr_error) | [si\_query\_aggr\_error](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_aggr_error) | | [pi\_query\_aggr\_abort](https://aerospike.com/docs/database/reference/metrics#namespace__pi_query_aggr_abort) | [si\_query\_aggr\_abort](https://aerospike.com/docs/database/reference/metrics#namespace__si_query_aggr_abort) | [query\_basic\_error](https://aerospike.com/docs/database/reference/metrics#namespace__query_basic_error) [query\_udf\_bg\_error](https://aerospike.com/docs/database/reference/metrics#namespace__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](https://aerospike.com/docs/database/observe/latency#auto-enabled-benchmarks). - [Version 6.0.0 and later](#tab-panel-2498) - [Version 5.7.0 and earlier](#tab-panel-2499) ```plaintext 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) ``` ```plaintext 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.0 and later](#tab-panel-2500) - [Version 5.7.0 and earlier](#tab-panel-2501) SI query microbenchmarks are not supported from Database 6.0.0 onwards as they are mostly obsolete given the rework of the query subsystem in that version. See [Secondary Index Transaction Analysis](https://aerospike.com/docs/database/observe/latency/#secondary-index-transaction-analysis) for details. See [Secondary Index Transaction Analysis](https://aerospike.com/docs/database/observe/latency/#secondary-index-transaction-analysis) for details. Enable writing microbenchmarks to the logs: ::: note [Tools package 6.0.x](https://aerospike.com/docs/database/tools/release-notes/#tools-603) or later is required to use `asadm`’s [manage config](https://aerospike.com/docs/database/tools/asadm/live-mode/#manage) commands. Otherwise, use the equivalent [asinfo - set-config](https://aerospike.com/docs/database/reference/info#set-config) command. ::: Terminal window ```bash asadm -e "enable; manage config service param query-microbenchmark to true" ``` Stop writing microbenchmarks to the logs: Terminal window ```bash asadm -e "enable; manage config service param query-microbenchmark to false" ``` Enable secondary-index-specific microbenchmarks: Terminal window ```bash asinfo -h [host ip] -v "sindex-histogram:ns=NAMESPACE;indexname=INDEX;enable=true" ``` Stop writing secondary-index-specific benchmarks to the logs: Terminal window ```bash 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 Links [asadm – Killing jobs](https://aerospike.com/docs/database/tools/asadm/live-mode/#jobs) [asinfo - Reference](https://aerospike.com/docs/database/reference/info)