Database system views

You can make queries to special service tables (system views) to monitor the DB status. These tables are accessible from the root of the database tree and use the .sys system path prefix.

You can find the corresponding table's primary key field index in the descriptions of available fields below.

DB system views contain:

• Details of individual DB table partitions.
• Top queries by certain characteristics.
• Query details.
• History of overloaded partitions.

Partitions

The following system view stores detailed information about individual partitions of all DB tables:

• partition_stats: Contains information about instant metrics and cumulative operation counters. Instant metrics are, for example, CPU load or count of in-flight transactions. Cumulative counters, for example, count the total number of rows read.

The system view is designed to detect various irregularities in the load on a table partition or show the size of table partition data.

Cumulative fields (RowReads, RowUpdates, and so on) store the accumulated values since the last start of the tablet serving the partition.

Table structure:

Example queries

Top 5 of most loaded partitions among all DB tables:

SELECT
    Path,
    PartIdx,
    CPUCores
FROM `.sys/partition_stats`
ORDER BY CPUCores DESC
LIMIT 5

List of DB tables with in-flight sizes and loads:

SELECT
    Path,
    COUNT(*) as Partitions,
    SUM(RowCount) as Rows,
    SUM(DataSize) as Size,
    SUM(CPUCores) as CPU
FROM `.sys/partition_stats`
GROUP BY Path

Top queries

The following system views store data for analyzing the flow of user queries:

• top_queries_by_duration_one_minute: Data is split into one-minute intervals, contains Top 5 queries with the maximum total execution time for the last 6 hours.
• top_queries_by_duration_one_hour: Data is split into one-hour intervals, contains Top 5 queries with the maximum total execution time for the last 2 weeks.
• top_queries_by_read_bytes_one_minute: Data is split into one-minute intervals, contains Top 5 queries with the maximum number of bytes read from the table for the last 6 hours.
• top_queries_by_read_bytes_one_hour: Data is split into one-hour intervals, contains Top 5 queries with the maximum number of bytes read from the table for the last 2 weeks.
• top_queries_by_cpu_time_one_minute: Data is split into one-minute intervals, contains Top 5 queries with the maximum CPU time used for the last 6 hours.
• top_queries_by_cpu_time_one_hour: Data is split into one-hour intervals, contains Top 5 queries with the maximum CPU time used for the last 2 weeks.

Different runs of a query with the same text are deduplicated. The top list contains information about a specific run with the maximum value of the corresponding query metric within a single interval.

Fields that provide information about the used CPU time (...CPUTime) are expressed in microseconds.

Query text limit is 4 KB.

All tables have the same set of fields:

Example queries

Top queries by execution time for the last minute when queries were made:

PRAGMA AnsiInForEmptyOrNullableItemsCollections;
$last = (
    SELECT
        MAX(IntervalEnd)
    FROM `.sys/top_queries_by_duration_one_minute`
);
SELECT
    IntervalEnd,
    Rank,
    QueryText,
    Duration
FROM `.sys/top_queries_by_duration_one_minute`
WHERE IntervalEnd IN $last

Queries that read the most bytes, broken down by minute:

SELECT
    IntervalEnd,
    QueryText,
    ReadBytes,
    ReadRows,
    Partitions
FROM `.sys/top_queries_by_read_bytes_one_minute`
WHERE Rank = 1

Query details

The following system view stores detailed information about queries:

• query_metrics_one_minute: Data is split into one-minute intervals, contains up to 256 queries for the last 6 hours.

Each table row contains information about a set of queries with identical text that were made during one minute. The table fields provide the minimum, maximum, and total values for each query metric tracked. Within the interval, queries are sorted in descending order of the total CPU time used.

Restrictions:

• Query text limit is 4 KB.
• Statistics may be incomplete if the database is under heavy load.

Table structure:

Example queries

Top 10 queries for the last 6 hours by the total number of rows updated per minute:

SELECT
    SumUpdateRows,
    Count,
    QueryText,
    IntervalEnd
FROM `.sys/query_metrics_one_minute`
ORDER BY SumUpdateRows DESC LIMIT 10

Recent queries that read the most bytes per minute:

SELECT
    IntervalEnd,
    SumReadBytes,
    MinReadBytes,
    SumReadBytes / Count as AvgReadBytes,
    MaxReadBytes,
    QueryText
FROM `.sys/query_metrics_one_minute`
WHERE SumReadBytes > 0
ORDER BY IntervalEnd DESC, SumReadBytes DESC
LIMIT 100

History of overloaded partitions

The following system views (tables) store the history of points in time when the load on individual DB table partitions was high:

• top_partitions_one_minute: The data is split into one-minute intervals, contains the history for the last 6 hours.
• top_partitions_one_hour: The data is split into one-hour intervals, contains the history for the last 2 weeks.

These tables contain partitions with peak loads of more than 70% (CPUCores > 0.7). Partitions within a single interval are ranked by peak load value.

Both tables have the same set of fields:

Example queries

The following query returns partitions with CPU usage of more than 70% in the specified interval, with tablet IDs and sizes as of the time when the percentage was exceeded. The query is made to the .sys/top_partitions_one_minute table with data over the last six hours split into one-minute intervals:

SELECT
   IntervalEnd,
   CPUCores,
   Path,
   TabletId,
   DataSize
FROM `.sys/top_partitions_one_minute`
WHERE CPUCores > 0.7
AND IntervalEnd BETWEEN Timestamp("YYYY-MM-DDThh:mm:ss.uuuuuuZ") AND Timestamp("YYYY-MM-DDThh:mm:ss.uuuuuuZ")
ORDER BY IntervalEnd desc, CPUCores desc

• "YYYY-MM-DDTHH:MM:SS.UUUUUUZ": Time in the UTC 0 zone (YYYY stands for year, MM, for month, DD, for date, hh, for hours, mm, for minutes, ss, for seconds, and uuuuuu, for microseconds). For example, "2023-01-26T13:00:00.000000Z".

The following query returns partitions with CPU usage of over 90% in the specified interval, with tablet IDs and sizes as of the time when the percentage was exceeded. The query is made to the .sys/top_partitions_one_hour table with data over the last two weeks split into one-hour intervals:

SELECT
   IntervalEnd,
   CPUCores,
   Path,
   TabletId,
   DataSize
FROM `.sys/top_partitions_one_hour`
WHERE CPUCores > 0.9
AND IntervalEnd BETWEEN Timestamp("YYYY-MM-DDThh:mm:ss.uuuuuuZ") AND Timestamp("YYYY-MM-DDThh:mm:ss.uuuuuuZ")
ORDER BY IntervalEnd desc, CPUCores desc

• "YYYY-MM-DDTHH:MM:SS.UUUUUUZ": Time in the UTC 0 zone (YYYY stands for year, MM, for month, DD, for date, hh, for hours, mm, for minutes, ss, for seconds, and uuuuuu, for microseconds). For example, "2023-01-26T13:00:00.000000Z".