Monitor SAI indexes

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

dse.yaml

The location of the dse.yaml file depends on the type of installation:
Package installations /etc/dse/dse.yaml
Tarball installations installation_location/resources/dse/conf/dse.yaml

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

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 tables for SAI indexes and SSTables.

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().

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.

For details about DSE tracing, refer to CQL TRACING.

SAI metrics

SAI provides a number of helpful metrics to help you monitor the health of your indexes.

Use JConsole, which is included with the JDK, to view the metric values. For information about using JConsole with a running DSE process, refer to Using JConsole.

With a DSE process running on a local node, start JConsole and select com.datastax.bdp.DseModule at the New Connection dialog and click Connect.

JConsole Open Connection dialog with com.datastax.bdp.DseModule active and an available Connect button

On the MBeans tab, the categorized metrics include:
  • Global indexing metrics
  • Table query metrics
  • Per query metrics
  • Key fetch metrics
  • Offset fetch metrics
  • Token fetch metrics
  • Column query metrics per index
  • Terms metrics per index
  • Range slice metrics

For example, use JConsole's hierarchical navigation to locate the current count of total partition reads since the node started for cycling.cyclist_semi_pro. The keyspace and table were defined in SAI quick start. This metric's ObjectName: com.datastax.bdp:type=metrics,scope=index,keyspace=cycling,table=cyclist_semi_pro,metricType=TableQueryMetrics,name=TotalPartitionReads.

JConsole hierarchical navigation to MBean tab and path to total partition reads metric

Global indexing metrics

ObjectName: com.datastax.bdp:type=metrics,scope=index,metricType=metric-type-name
The global indexing metrics for this node are:
  • ColumnIndexBuildsInProgress – The number of individual on-disk column indexes currently being built.
  • SegmentBufferSpaceLimitBytes – The limit on heap used to buffer SSTable index segments during compaction and index builds.
    Tip: In dse.yaml, segment_write_buffer_space_mb limits the amount of heap used to build on-disk column indexes during compaction and initial builds. The default is 1024 MB. For example, if there is only one column index building, SAI can buffer up to segment_write_buffer_space_mb. If there is one column index building per table across 8 compactors, each index will be eligible to flush once it reaches (segment_write_buffer_space_mb / 8) MBs.
  • SegmentBufferSpaceUsedBytes – The heap currently being used to buffer SSTable index segments during compaction and index builds.
Note: At any given time, the minimum size for a flushing segment, in bytes, is (SegmentBufferSpaceLimitBytes / ColumnIndexBuildsInProgress).

Table query metrics

ObjectName: com.datastax.bdp:type=metrics,scope=index,keyspace=keyspace,table=table,metricType=TableQueryMetrics,name=metric
The table query metrics for the given keyspace and table are:
  • TotalPartitionReads – Total partition reads by all queries since the node started.
  • TotalQueriesCompleted – Total number of successfully completed queries since the node started.
  • TotalQueryTimeouts – Added in 6.8.1, total number of timeouts from queries since the node started.
  • TotalRowsFiltered – Total number of rows post-filtered by all queries since the node started.

Per query metrics

ObjectName: com.datastax.bdp:type=metrics,scope=index,keyspace=keyspace,table=table,metricType=PerQuery,name=metric
The per query metrics for the given keyspace and table include:
  • RowsFiltered – A histogram of the number of rows post-filtered per query since the node started.
  • QueryLatency – Overall query latency percentiles (in microseconds) and one/five/fifteen minute query throughput.
  • PartitionReads – Histogram over the number of partitions read and post-filtered per query.
  • SSTableIndexesHit – Histogram over the number of SSTable indexes read per query.
  • KDTreeChunkCacheLookups – Histogram over the number of chunk cache lookups while reading kd-tree index files per query.
  • KDTreeChunkCacheMisses – Histogram over the number of chunk cache misses while reading kd-tree index files per query.

Key fetch metrics

ObjectName: com.datastax.bdp:type=metrics,scope=index,keyspace=keyspace,table=table,metricType=KeyFetch,name=metric
The key fetch metrics for the given keyspace and table include:

  • ChunkCacheHitRate – All-time chunk cache hit rate for postings during queries against the given table.

  • TotalChunkCacheLookups – All-time chunk cache lookups for postings during queries against the given table.

  • TotalChunkCacheMisses – All-time chunk cache misses for postings during queries against the given table.

  • ChunkCache(One|Five|Fifteen)HitRate – N-minute chunk cache hit rate for postings during queries against the given table.

Offset fetch metrics

ObjectName: com.datastax.bdp:type=metrics,scope=index,keyspace=keyspace,table=table,metricType=OffsetFetch,name=metric
The offset fetch metrics for the given keyspace and table include:

  • ChunkCacheHitRate – All-time chunk cache hit rate for partition key SSTable offset fetches during queries against the given table.

  • TotalChunkCacheLookups – All-time chunk cache lookups for partition key SSTable offset fetches during queries against the given table.

  • TotalChunkCacheMisses – All-time chunk cache misses for partition key SSTable offset fetches during queries against the given table.

  • ChunkCache(One|Five|Fifteen)HitRate – N-minute chunk cache hit rate for partition key SSTable offset fetches during queries against the given table.

Token fetch metrics

ObjectName: com.datastax.bdp:type=metrics,scope=index,keyspace=keyspace,table=table,metricType=TokenFetch,name=metric
The token fetch metrics for the given keyspace and table include:

  • ChunkCacheHitRate – All-time chunk cache hit rate for partition key token offset fetches during queries against the given table.

  • TotalChunkCacheLookups – All-time chunk cache lookups for partition key token offset fetches during queries against the given table.

  • TotalChunkCacheMisses – All-time chunk cache misses for partition key token offset fetches during queries against the given table.

  • ChunkCache(One|Five|Fifteen)HitRate – N-minute chunk cache hit rate for partition key token offset fetches during queries against the given table.

Column query metrics

ObjectName: com.datastax.bdp:type=metrics,scope=index,keyspace=keyspace,table=table,index=index,metricType=ColumnQueryMetrics,name=metric
The column query metrics for the given keyspace, table, and index include:
  • KDTreeNiceTryLatency – For numeric indexes, such as age_sai_idx in the quick start examples, this metric may be used to present 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: The throughputs are zero if there are no cache misses during kd-tree intersection.

  • TermsLookupLatency – For string indexes, such as country_sai_idx in the quick start examples, this metric shows terms lookup latency percentiles (in microseconds) per one/five/fifteen minute query throughput.

Terms metrics

ObjectName: com.datastax.bdp:type=metrics,scope=index,keyspace=keyspace,table=table,index=index,metricType=Terms,name=metric
For string indexes, the terms metrics for the given keyspace, table, and index include:

  • ChunkCacheHitRate – All-time chunk cache hit rate for postings during term queries that used the given index.

  • TotalChunkCacheLookups – All-time chunk cache lookups for postings during term queries that used the given index.

  • TotalChunkCacheMisses – All-time chunk cache misses for postings during term queries that used the given index.

  • ChunkCache(One|Five|Fifteen)HitRate – N-minute chunk cache hit rate for postings during term queries that used the given index.

Range slice metrics

ObjectName: org.apache.cassandra.metrics:type=ClientRequests,scope=RangeSlice,name=metric

The RoundTripsPerReadHistogram metric tracks the number of round-trip requests sent for range query commands. Fewer requests typically mean the server is operating more efficiently than ones requiring more requests to satisfy the same range queries.