Monitoring SAI indexes

Use these tools to monitor secondary indexes that were created with Storage-Attached Indexing (SAI).

DSE tracing

SAI provides tracing capability just like other DSE database components. Information is captured by the system_traces keyspace. You can enable tracing in CQLSH with TRACING ON, or in the DataStax driver with statement.enableTracing().

For details about DSE tracing, refer to CQL TRACING.

DSE metrics

There are two metrics available at the TableQueryMetrics level to verify the number of rows filtered during the post-filtering phase of SAI queries:

TotalRowsFiltered - The total number of rows post-filtered by all queries since node startup.
RowsFilteredPerQuery - A histogram of the number of rows post-filtered per query since node startup.

There is also a metric at the ColumnQueryMetrics level, KDTreeNiceTryLatency. It presents a histogram of the times spent waiting for chunk cache misses during kd-tree intersection (in microseconds) and one/five/fifteen minute chunk miss throughputs.

Note: These throughputs will be zero if there are no cache misses during kd-tree intersection.

In addition, the number of rows filtered by a particular query will show up in the CQL query trace. Example:

Index query accessed memtable indexes, 2 SSTable indexes, and 2 segments, post-filtered 14 rows in 14 partitions, and took 88582 microseconds.

DSE virtual tables

You can refer to data in the following DSE virtual tables to determine the status of indexes created with SAI:

system_views.indexes – provides information at the column index level, including the index name, number of indexed SSTables, disk usage, and index state. From the index state, the data reveals if the index is currently building, and whether the index can be queried.
system_views.sstable_indexes – describes individual SSTable indexes, and includes information around disk size, min/max row ID, the min/max ring token, and the write-time version of the index.
system_views.sstable_index_segments – describes the segments of the SSTable indexes. It exposes the segment row ID offset and most of the information in the SSTable-level virtual table, specifically at a segment granularity.

For details, refer to About virtual keyspaces and tables.