Managing indexes

Overview

This page describes the Aerospike tools for managing secondary indexes. These include :

• asadm, the best choice for managing your Aerospike cluster.
• aql, an SQL-like alternative to asadm.

Create and manage index

Create and manage indexes with the manage sindex create command:

manage sindex create BIN_TYPE INDEX_NAME ns NS [set SET] bin BIN_NAME [in INDEX_TYPE] [ctx CONTEXT]

For details on this command, such as the data types supported and more information about the syntax, see asadm – Index Management.

List indexes

Use asadm to list indexes in the database:

Admin+> show sindex
~~~~Secondary Indexes (2021-09-07 22:45:05 UTC)~~~~
  Index|Namespace| Set| Bin|    Bin|Index   |State
   Name|         |    |    |   Type| Type   |     
ind2   |test     |demo|bin2|NUMERIC|DEFAULT |RW   
ind3   |test     |NULL|bin2|NUMERIC|DEFAULT |RW   
ind4   |test     |demo|bin3|NUMERIC|DEFAULT |RW   
ind1   |abcd     |demo|bin1|NUMERIC|DEFAULT |RW   
Number of rows: 4

Use asadm to list information about a single index:

Admin+> show sindex like ind1
~~~~Secondary Indexes (2021-09-07 22:45:05 UTC)~~~~
  Index|Namespace| Set| Bin|    Bin|Index   |State
   Name|         |    |    |   Type| Type   |     
ind1   |abcd     |demo|bin1|NUMERIC|DEFAULT |RW   
Number of rows: 1

Drop indexes

Use asadm to drop indexes from the cluster:

manage sindex delete INDEX_NAME ns NS [set SET]

For more information on the delete command, see asadm – Index Management.

info

Index DDL changes are synchronous operations. When you run an index command, the nodes in the cluster consult each other and then run the command on each node. When the command is complete, verify the state of the index before using it.

info

Avoid creating several indexes at one time, or creating indexes while nodes are migrating.

Set indexes

In Database 5.6 and later, you can index membership of records to their set by creating a set index. Using a set index improves the performance of set-level operations, such as scanning all records in the set.

• For more information on set indexes, see Set Index Architecture.
• To manage set indexes, see Managing Sets in a Namespace

Index memory management

Aerospike secondary indexes exist entirely in memory. If you use secondary indexes, verify that the nodes in the cluster have enough memory for the indexes. For more information on memory allocation for secondary indexes, see capacity planning.

If the namespace size is not configured to account for the memory requirements of secondary indexes, and the memory high water mark is breached, the cluster may evict data or stop writes. You can limit the amount of memory used by secondary indexes to prevent the namespace from running out of memory.

Use the indexes-memory-budget configuration parameter to manage memory usage by all indexes. If indexes-memory-budget is exceeded, the server goes into stop-writes mode and refuses new write operations until memory usage falls below the indexes-memory-budget threshold.

Index monitoring

Important statistics to monitor:

asadm> show statistics sindex

Version 6.0.0 and later

NAME	DESCRIPTION	Comments
entries	Number of objects in the secondary index tree.	For unique indexes, this value should equal the number of keys.
memory_used	Total memory used by a secondary index.	Use this to check system memory usage.
load_pct	Percentage indicating the progress of secondary index populate phase.
load_time	Time taken to populate the secondary index.
stat_gc_recs	Number of records garbage collected from the secondary index.

Version 5.7 and below

NAME	DESCRIPTION	Comments
keys	Number of primary keys indexed to this index.	Statistics to monitor the quantity of unique keys in a secondary index.
entries	Number of objects in the secondary index tree.	For unique indexes, this value should equal the number of keys.
ibtr_memory_used	Total memory used by a secondary index.	Use this to check system memory memory usage.
nbtr_memory_used	Total memory used by a secondary index.	Use this to check system memory memory usage.
stat_gc_recs	Number of records garbage collected from the secondary index.

Displaying performance histograms for secondary indexes

note

Deprecated in Database 6.0 and later.

To enable secondary index performance histograms:

asadm -e 'enable; "asinfo -v "sindex-histogram:ns=NAMESPACE;[set=demo];indexname=INDEX_NAME;enable=true"'

To disable secondary index performance histograms:

asadm -e 'enable; asinfo -v "sindex-histogram:ns=NAMESPACE;[set=demo];indexname=INDEX_NAME;enable=false"'

'enable=true' writes the secondary index performance histogram to the log.

'enable=false' stops writing the secondary index performance histogram to the log.

Index Limits

Refer to Known Limitations