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
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.Tip:When youDROP
/ recreate an SAI index, you are not blocked from entering queries that do not use the index. However, you cannot use that SAI index (based on the same column) until it has finished building and is queryable. To determine the current state of a given index, query thesystem_views.indexes
virtual table. Example:SELECT is_queryable,is_building FROM system_views.indexes WHERE keyspace_name='keyspace' AND table_name='table' AND index_name='index';
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 more details, refer to 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 metrics to help you monitor the health of your indexes.
- 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, you can use metrics to get 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
:
org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=cycling,table=cyclist_semi_pro,scope=TableQueryMetrics,name=TotalPartitionReads
.
Preconfigured Grafana dashboards
DataStax provides preconfigured Grafana dashboards on this GitHub site that integrate with DSE Metrics Collector. Use DSE Metrics Collector to export DSE metrics to a monitoring tool like Prometheus, and then visualize the DSE metrics in Grafana dashboards.
Use Docker and modify the provided Prometheus configuration file, or manually export DSE metrics to an existing Prometheus server.
While the GitHub-hosted DataStax examples use Prometheus as the monitoring tool, you can export the aggregated metrics to other tools like Graphite and Splunk.
Global indexing metrics
ObjectName: org.apache.cassandra.metrics,type=StorageAttachedIndex,name=metric
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 tosegment_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.
SegmentBufferSpaceLimitBytes /
ColumnIndexBuildsInProgress
).Index group metrics
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,scope=IndexGroupMetrics,name=metric
New starting in DSE 6.8.3.
DiskUsedBytes
– Size in bytes on disk for the given table's SAI indices.IndexFileCacheBytes
– Size in bytes of memory used by the on-disk data structure of the per-column indices.OpenIndexFiles
– Number of open index files for the given table's SAI indices.
Key fetch metrics
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,scope=KeyFetch,name=metric
-
ChunkCacheHitRate
– All-time chunk cache hit rate for keys during queries against the given table. -
TotalChunkCacheLookups
– All-time chunk cache lookups for keys during queries against the given table. -
TotalChunkCacheMisses
– All-time chunk cache misses for keys during queries against the given table. -
ChunkCache(One|Five|Fifteen)HitRate
– N-minute chunk cache hit rate for keys during queries against the given table.
Offset fetch metrics
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,scope=OffsetFetch,name=metric
-
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.
Per query metrics
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,scope=PerQuery,name=metric
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 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.
Table query metrics
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,scope=TableQueryMetrics,name=metric
TotalPartitionReads
– Total partition reads by all queries since the node started.TotalQueriesCompleted
– Total number of successfully completed queries since the node started.TotalQueryTimeouts
– Total number of timeouts from queries since the node started.TotalRowsFiltered
– Total number of rows post-filtered by all queries since the node started.
Table state metrics
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,scope=TableStateMetrics,name=metric
New starting in DSE 6.8.3.
DiskPercentageOfBaseTable
– SAI size on Disk as a percentage of table size per table.DiskUsedBytes
– Size on-disk in bytes of SAI indices per table.TotalIndexBuildsInProgress
– Status of SAI indices per table currently in theis_building
state.TotalIndexCount
– Total number of SAI indices per table.TotalQueryableIndexCount
– Status of SAI indices per table currently in theis_querable
state.
Token fetch metrics
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,scope=TokenFetch,name=metric
-
ChunkCacheHitRate
– All-time chunk cache hit rate for partition key token fetches during queries against the given table. -
TotalChunkCacheLookups
– All-time chunk cache lookups for partition key token fetches during queries against the given table. -
TotalChunkCacheMisses
– All-time chunk cache misses for partition key token fetches during queries against the given table. -
ChunkCache(One|Five|Fifteen)HitRate
– N-minute chunk cache hit rate for partition key token fetches during queries against the given table.
Token skipping metrics
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,scope=TokenSkipping,name=metric
-
CacheHits
– Number of cache hits from token skipping in a multi-indexAND
query. Lookups
– Number of lookups from token skipping a multi-indexAND
query.
Column query metrics for each numeric index
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,index=index,scope=ColumnQueryMetrics,name=metric
-
KDTreeNiceTryLatency
– For numeric indexes, such asage_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.
Column query metrics for each string index
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,index=index,scope=ColumnQueryMetrics,name=metric
TermsLookupLatency
– For string indexes, such ascountry_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 for each string index
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=keyspace,table=table,index=index,scope=Terms,name=metric
-
ChunkCacheHitRate
– All-time chunk cache hit rate for terms during string index queries that used the given index. -
TotalChunkCacheLookups
– All-time chunk cache lookups for terms during string index queries that used the given index. -
TotalChunkCacheMisses
– All-time chunk cache misses for terms during string index queries that used the given index. -
ChunkCache(One|Five|Fifteen)HitRate
– N-minute chunk cache hit rate for terms during string index 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 from the coordinator. Fewer requests typically mean
the server is operating more efficiently than ones requiring more requests to satisfy the
same range queries.
What's next?
The next topic walks through the write path and read path of SAI indexes.