Database system views

To obtain service information about the state of the database, you can access system views. They are accessible from the root of the database tree and use the system path prefix .sys.

Partitions

The following system view stores detailed information about partitions of 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.

Instant metrics (NodeID, AccessTime, CPUCores, etc.) contain instantaneous values.
Cumulative metrics (RowReads, RowUpdate, LockAcquired, etc.) store accumulated values since the last launch (StartTime) 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

List of DB tables with the largest number of broken locks:

SELECT
    Path,
    COUNT(*) as Partitions,
    SUM(LocksBroken) as TotalLocksBroken
FROM `.sys/partition_stats`
GROUP BY Path
ORDER BY TotalLocksBroken DESC

Top queries

The following system views store data for analyzing the user queries.

Maximum total execution time:

• top_queries_by_duration_one_minute: data is split into one-minute intervals, contains the history for the last 6 hours;
• top_queries_by_duration_one_hour: data is split into one-hour intervals, contains the history for the last 2 weeks.

Maximum number of bytes read from the table:

• top_queries_by_read_bytes_one_minute: data is split into one-minute intervals, contains the history for the last 6 hours;
• top_queries_by_read_bytes_one_hour: Data is split into one-hour intervals, contains the history for the last 2 weeks.

Maximum CPU time:

• top_queries_by_cpu_time_one_minute: Data is split into one-minute intervals, contains the history for the last 6 hours;
• top_queries_by_cpu_time_one_hour: Data is split into one-hour intervals, contains the history for the last 2 weeks.

Different runs of a query with the same text are deduplicated. The query with the maximum value of the corresponding metric is included in the output.
Each time interval (minute or hour) contains the TOP 5 queries completed in that time interval.

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

Query text limit is 10 KB.

All tables have the same structure:

Example queries

Top queries by execution time. The query is made to the .sys/top_queries_by_duration_one_minute view:

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. The query is made to the .sys/top_queries_by_read_bytes_one_minute view:

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 10 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 views contain partitions with peak loads of more than 70% (CPUCores > 0.7). Partitions within a single interval are ranked by peak load value.

The keys of the views are:

• IntervalEnd - the moment when the interval is closed;
• Rank - the rank of the partition according to the peak load of CPUCores in this interval.

For example, if a table has 10 partitions than top_partitions_one_hour for the hour interval "20.12.2024 10:00-11:00" will return 10 rows sorted in descending order of CPUCores. They will have a Rank from 1 to 10 and the same IntervalEnd "20.12.2024 11:00".

All tables have the same structure:

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 view:

SELECT
   IntervalEnd,
   CPUCores,
   Path,
   TabletId,
   DataSize
FROM `.sys/top_partitions_one_minute`
WHERE CPUCores > 0.7
AND IntervalEnd BETWEEN Timestamp("2000-01-01T00:00:00Z") AND Timestamp("2099-12-31T00:00:00Z")
ORDER BY IntervalEnd desc, CPUCores desc

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 view:

SELECT
   IntervalEnd,
   CPUCores,
   Path,
   TabletId,
   DataSize
FROM `.sys/top_partitions_one_hour`
WHERE CPUCores > 0.9
AND IntervalEnd BETWEEN Timestamp("2000-01-01T00:00:00Z") AND Timestamp("2099-12-31T00:00:00Z")
ORDER BY IntervalEnd desc, CPUCores desc

History of partitions with broken locks

The following system views contain a history of moments with a non-zero number of broken locks LocksBroken in individual partitions of DB tables:

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

The views provide the top 10 partitions with a non-zero number of broken locks LocksBroken. Within a single interval, partitions are ranked by the number of broken locks LocksBroken.

The keys of the views are:

• IntervalEnd - the moment of interval closure;
• Rank - the rank of the partition by the number of broken locks LocksBroken in this interval.

For example, top_partitions_by_tli_one_hour for the hourly interval "20.12.2024 10:00-11:00" will output 10 rows, sorted in descending order by LocksBroken. They will have Rank from 1 to 10 and the same IntervalEnd "20.12.2024 11:00".

All tables have the same structure:

Example queries

The following query returns partitions in the specified time interval, with tablet identifiers and the number of broken locks. The query is made to the .sys/top_partitions_by_tli_one_minute view:

SELECT
    IntervalEnd,
    LocksBroken,
    Path,
    TabletId
FROM `.sys/top_partitions_by_tli_one_hour`
WHERE IntervalEnd BETWEEN Timestamp("2000-01-01T00:00:00Z") AND Timestamp("2099-12-31T00:00:00Z")
ORDER BY IntervalEnd desc, LocksBroken desc

Auth users, groups, permissions

Auth users

The auth_users view lists internal YDB users. It does not include users authenticated through external systems such as LDAP.

This view can be fully accessed by administrators, while regular users can only view their own details.

Table structure:

Auth groups

The auth_groups view lists access groups.

This view can be accessed only by administrators.

Table structure:

Auth group members

The auth_group_members view lists membership details within access groups.

This view can be accessed only by administrators.

Table structure:

Auth permissions

The auth permissions views list assigned access rights.

Contains two views:

• auth_permissions: Directly assigned access rights.
• auth_effective_permissions: Effective access rights, accounting for inheritance.

A user can view an access object in the results if they have the ydb.granular.describe_schema permission on it.

Table structure:

Example queries

All the directly assigned permissions for the table located at the path my_table:

SELECT *
FROM `.sys/auth_permissions`
WHERE Path = "my_table"

All the effective permissions for the table located at the path my_table, including inherited permissions:

SELECT *
FROM `.sys/auth_effective_permissions`
WHERE Path = "my_table"

All permissions directly assigned to the user identified as user3:

SELECT *
FROM `.sys/auth_permissions`
WHERE Sid = "user3"

Auth owners

The auth_owners view lists details of access objects ownership.

A user can view an access object in the results if they have the ydb.granular.describe_schema permission on it.

Table structure: