Monitor SAI
Use these tools to monitor secondary indexes that were created with Storage-Attached Indexing (SAI), including metrics and options for pre-configured dashboards.
CQL virtual tables
Storage Attached Indexing (SAI) provides CQL-based virtual tables that enable you to discover the current state of system metadata for SAI indices and associated SSTables.
These virtual tables reside in the system_views keyspace.
system_views.indexes
The system_views.indexes virtual table provides information at the column index level.
This table includes the index name, number of indexed SSTables, disk usage, and the index state.
From the index state, the data reveals if the index is currently building, and whether the index can be queried.
Use CQL to view the table’s description. Example:
DESCRIBE TABLE system_views.indexes;/*
Warning: Table system_views.indexes is a virtual table and cannot be re-created with CQL.
Structure, for reference:
VIRTUAL TABLE system_views.indexes (
keyspace_name text,
index_name text,
analyzer text,
cell_count bigint,
column_name text,
indexed_sstable_count int,
is_building boolean,
is_queryable boolean,
is_string boolean,
per_column_disk_size bigint,
per_table_disk_size bigint,
table_name text,
PRIMARY KEY (keyspace_name, index_name)
) WITH CLUSTERING ORDER BY (index_name ASC)
AND comment = 'Storage-attached column index metadata';
*/To determine the current state of a given index, query the system_views.indexes virtual table.
For example:
SELECT is_queryable,is_building FROM system_views.indexes
WHERE keyspace_name='<keyspace>'
AND table_name='<table>'
AND index_name='<index>'; keyspace_name | index_name | analyzer | cell_count | column_name | indexed_sstable_count | is_building | is_queryable | is_string | per_column_disk_size | per_table_disk_size | table_name
---------------+----------------------+-------------------------------------------------------------+------------+--------------+-----------------------+-------------+--------------+-----------+----------------------+---------------------+------------------
cycling | age_sai_idx | NoOpAnalyzer{} | 0 | age | 0 | False | True | False | 0 | 0 | cyclist_semi_pro
cycling | country_sai_idx | NonTokenizingAnalyzer{caseSensitive=false, normalized=true} | 0 | country | 0 | False | True | True | 0 | 0 | cyclist_semi_pro
cycling | lastname_sai_idx | NonTokenizingAnalyzer{caseSensitive=false, normalized=true} | 0 | lastname | 0 | False | True | True | 0 | 0 | cyclist_semi_pro
cycling | registration_sai_idx | NoOpAnalyzer{} | 0 | registration | 0 | False | True | False | 0 | 0 | cyclist_semi_pro
(4 rows)system_views.indexes metadata is as follows:
| Column name | CQL type | Meaning |
|---|---|---|
|
|
The name of the keyspace to which the index belongs. |
|
|
The name of the index. |
|
|
The |
|
|
The number of indexed table cells, or the number of index value-key entries. This is the sum of the number of index entries in each SSTable. |
|
|
The name of the indexed column. |
|
|
The number of indexed SSTables. Note that SSTables without relevant data won’t be indexed or counted here. |
|
|
Whether there is a on going build for the index. |
|
|
Whether it is possible to query the index. It won’t be possible if the initial task build hasn’t finished yet. |
|
|
Whether the index is for a text field ( |
|
|
The on-disk size of the index components that are exclusive to the column, in bytes. |
|
|
The on-disk size of the index components that are shared with other SAI indexes for the same table, in bytes. |
|
|
The name of the table to which the indexed column belongs. |
system_views.sstable_indexes
The system_views.sstable_indexes virtual table has a row per SAI index and SSTable.
This view 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.
Use CQL to view the table’s description. Example:
DESCRIBE TABLE system_views.sstable_indexes;/*
Warning: Table system_views.sstable_indexes is a virtual table and cannot be re-created with CQL.
Structure, for reference:
VIRTUAL TABLE system_views.sstable_indexes (
keyspace_name text,
index_name text,
sstable_name text,
cell_count bigint,
column_name text,
end_token text,
format_version text,
max_row_id bigint,
min_row_id bigint,
per_column_disk_size bigint,
per_table_disk_size bigint,
start_token text,
table_name text,
PRIMARY KEY (keyspace_name, index_name, sstable_name)
) WITH CLUSTERING ORDER BY (index_name ASC, sstable_name ASC)
AND comment = 'SSTable index metadata';
*/To view the current data, submit a query such as:
SELECT * FROM system_views.sstable_indexes;The system_views.sstable_indexes metadata is as follows:
| Column name | CQL type | Meaning |
|---|---|---|
|
|
The name of the keyspace to which the index belongs. |
|
|
The name of the index. |
|
|
The name of the SSTable. |
|
|
The number of indexed table cells, or the number of index value-key entries. |
|
|
The name of the indexed column. |
|
|
The start of the token range covered by the indexed SSTable. |
|
|
The end of the token range covered by the indexed SSTable. |
|
|
The minimum row id in the SSTable index. |
|
|
The maximum row id in the SSTable index. |
|
|
The on-disk size of the SSTable index components that are exclusive to the column, in bytes. |
|
|
The on-disk size of the SSTable index components that are shared with other SAI indexes for the same table, in bytes. |
|
|
The name of the table to which the indexed column belongs. |
system_views.sstable_index_segments
The system_views.sstable_index_segments virtual table has a row per SAI index and SSTable segment.
This view 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.
Use CQL to view the table’s description. Example:
DESCRIBE TABLE system_views.sstable_index_segments;/*
Warning: Table system_views.sstable_index_segments is a virtual table and cannot be re-created with CQL.
Structure, for reference:
VIRTUAL TABLE system_views.sstable_index_segments (
keyspace_name text,
index_name text,
sstable_name text,
segment_row_id_offset bigint,
cell_count bigint,
column_name text,
component_metadata frozen<map<text, map<text, text>>>,
end_token text,
max_sstable_row_id bigint,
max_term text,
min_sstable_row_id bigint,
min_term text,
start_token text,
table_name text,
PRIMARY KEY (keyspace_name, index_name, sstable_name, segment_row_id_offset)
) WITH CLUSTERING ORDER BY (index_name ASC, sstable_name ASC, segment_row_id_offset ASC)
AND comment = 'SSTable index segment metadata';
*/To view the current data, submit a query such as:
SELECT * FROM system_views.sstable_index_segments;The system_views.sstable_index_segments metadata is as follows:
| Column name | CQL type | Meaning |
|---|---|---|
|
|
The name of the keyspace to which the index belongs. |
|
|
The name of the index. |
|
|
The name of the SSTable. |
|
|
The row id offset for the SSTable segment. |
|
|
The number of indexed segments, or the number of index segments value-key entries. |
|
|
The name of the indexed column. |
|
|
The component metadata in the SSTable segment. |
|
|
The end of the token range covered by the SSTable segment. |
|
|
The maximum row id in the SSTable segment. |
|
|
The maximum term in the SSTable segment. |
|
|
The minimum row id in the SSTable segment. |
|
|
The minimum term in the SSTable segment. |
|
|
The start of the token range covered by the SSTable segment. |
|
|
The name of the table to which the SSTable segment belongs. |
DSE tracing
DSE users can access enhanced tracing capability for SAI just like other DSE database components.
Information is captured by the system_traces keyspace.
You can enable tracing in CQL shell with TRACING ON, and you can enable request tracing in Apache Cassandra drivers.
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 the TRACING CQL command.
SAI metrics
SAI provides a number of metrics to help you monitor the health of your indexes.
The metrics are categorized as:
-
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 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 quickstart.
This metric’s ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=cycling,table=cyclist_semi_pro,scope=TableQueryMetrics,name=TotalPartitionReads.
The metrics are exposed via JMX, so any JMX-based tool can be used to monitor. Common tools include Prometheus, Graphite, and Splunk.
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>The global indexing metrics for this node are:
| Metric | Description | ||
|---|---|---|---|
|
The number of individual on-disk column indexes currently being built. |
||
|
The limit on heap used to buffer SSTable index segments during compaction and index builds. +
For example, if there is only one column index building, SAI can buffer up to |
||
|
The heap currently being used to buffer SSTable index segments during compaction and index builds. |
|
At any given time, the minimum size for a flushing segment, in bytes, is ( |
Index group metrics
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=<keyspace>,table=<table>,scope=IndexGroupMetrics,name=<metric>The index group metrics for the given keyspace and table:
| Metric | Description |
|---|---|
|
Size in bytes on disk for the given table’s SAI indices. |
|
Size in bytes of memory used by the on-disk data structure of the per-column indices. |
|
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>The key fetch metrics for the given keyspace and table:
| Metric | Description |
|---|---|
|
All-time chunk cache hit rate for keys during queries against the given table. |
|
All-time chunk cache lookups for keys during queries against the given table. |
|
All-time chunk cache misses for keys during queries against the given table. |
|
The one-minute, five-minute, and fifteen-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>The offset fetch metrics for the given keyspace and table:
| Metric | Description |
|---|---|
|
All-time chunk cache hit rate for partition key SSTable offset fetches during queries against the given table. |
|
All-time chunk cache lookups for partition key SSTable offset fetches during queries against the given table. |
|
All-time chunk cache misses for partition key SSTable offset fetches during queries against the given table. |
|
The one-minute, five-minute, and fifteen-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>The per query metrics for the given keyspace and table include:
| Metric | Description |
|---|---|
|
A histogram of the number of rows post-filtered per query since the node started. |
|
Overall query latency percentiles (in microseconds) and one/five/fifteen minute query throughput. |
|
Histogram over the number of partitions read per query. |
|
Histogram over the number of SSTable indexes read per query. |
|
Histogram over the number of chunk cache lookups while reading kd-tree index files per query. |
|
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>The table query metrics for the given keyspace and table:
| Metric | Description |
|---|---|
|
Total partition reads by all queries since the node started. |
|
Total number of successfully completed queries since the node started. |
|
Total number of timeouts from queries since the node started. |
|
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>The table state metrics for the given keyspace and table:
| Metric | Description |
|---|---|
|
SAI size on Disk as a percentage of table size per table. |
|
Size on-disk in bytes of SAI indices per table. |
|
Status of SAI indices per table currently in the |
|
Total number of SAI indices per table. |
|
Status of SAI indices per table currently in the |
Token fetch metrics
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=<keyspace>,table=<table>,scope=TokenFetch,name=<metric>The token fetch metrics for the given keyspace and table:
| Metric | Description |
|---|---|
|
All-time chunk cache hit rate for partition key token fetches during queries against the given table. |
|
All-time chunk cache lookups for partition key token fetches during queries against the given table. |
|
All-time chunk cache misses for partition key token fetches during queries against the given table. |
|
The one-minute, five-minute, and fifteen-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>The token skipping metrics for the given keyspace and table:
| Metric | Description |
|---|---|
|
Number of cache hits from token skipping in a multi-index |
|
Number of lookups from token skipping a multi-index |
Column query metrics for each numeric index
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=<keyspace>,table=<table>,index=<index>,scope=ColumnQueryMetrics,name=<metric>The column query metrics for the given keyspace, table, and index include:
| Metric | Description | ||
|---|---|---|---|
|
For numeric indexes, such as
|
Column query metrics for each string index
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=<keyspace>,table=<table>,index=<index>,scope=ColumnQueryMetrics,name=<metric>The column query metrics for the given keyspace, table, and index include:
| Metric | Description |
|---|---|
|
For string indexes, such as |
Terms metrics for each string index
ObjectName: org.apache.cassandra.metrics:type=StorageAttachedIndex,keyspace=<keyspace>,table=<table>,index=<index>,scope=Terms,name=<metric>For string indexes, the terms metrics for the given keyspace, table, and index:
| Metric | Description |
|---|---|
|
All-time chunk cache hit rate for terms during string index queries that used the given index. |
|
All-time chunk cache lookups for terms during string index queries that used the given index. |
|
All-time chunk cache misses for terms during string index queries that used the given index. |
|
The one-minute, five-minute, and fifteen-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=ClientRequest,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.
Latency metric tracks the min, max, mean as well as a set of percentiles for range read requests latency. Timeouts metric tracks the number of timeouts for range read requests.
