System database views

To obtain system information about the database state, you can refer to system views. They are available 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 database tables:

• partition_stats — contains information about instantaneous metrics and cumulative counters of operations. The former include, for example, CPU load data or the number of running transactions. The latter include the total number of rows read.

It is intended, for example, for identifying unevenly loaded partitions or displaying the size of data in them.

Instantaneous metrics (NodeId, AccessTime, CPUCores, etc.) contain instantaneous values.
Cumulative (non-instantaneous) metrics (RowReads, RowUpdates, LocksAcquired, etc.) store accumulated values since the last start of the tablet (StartTime) serving the partition.

View structure:

Query examples

Top 5 most loaded partitions among all database tables:

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

List of database tables with sizes and current load:

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 database tables with the highest 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 user queries.

Maximum total query execution time:

• top_queries_by_duration_one_minute — data is divided into minute intervals, contains data for the last 6 hours.
• top_queries_by_duration_one_hour: data is split into hourly intervals, contains data for the last 2 weeks.

The largest number of bytes read from the table:

• top_queries_by_read_bytes_one_minute — data is split into minute intervals, contains data for the last 6 hours.
• top_queries_by_read_bytes_one_hour — data is split into hour intervals, contains data for the last 2 weeks.

Maximum CPU time spent:

• top_queries_by_cpu_time_one_minute — data is split into minute intervals, contains data for the last 6 hours.
• top_queries_by_cpu_time_one_hour — data is split into hour intervals, contains data for the last 2 weeks.

Queries with the same text are combined, and 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 executed in that time interval.

Fields providing information about CPU time spent (...CPUTime) are expressed in microseconds.

Query text is limited to 10 kilobytes.

All views have the same structure:

Query examples

Top queries by execution time. The query is run against 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 run against 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

Detailed query information

The following system view contains detailed query information:

• query_metrics_one_minute — data is split into minute intervals, contains up to 256 queries for the last 6 hours.

Each row of the view contains information about multiple queries with the same text that occurred during the interval. The view fields contain the minimum, maximum, and total values for each tracked query characteristic. Within the interval, queries are sorted in descending order of total CPU time spent.

Limitations:

• query text is limited to 10 kilobytes.
• statistics may be incomplete if the database is under heavy load.

View structure:

Query examples

Top 10 queries over the last 6 hours by total number of rows written in a minute interval:

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

Detailed information about session queries

The following system view contains detailed information about current sessions and the queries being executed in them.

• .sys/query_sessions — contains information about sessions and the queries being executed in them.

Possible values of the WmState field:

• NONE - Not being processed.
• PENDING - Being processed (in classification/routing).
• DELAYED - In queue.
• EXITED - Submitted for execution.

Possible values of the State field:

• IDLE - Session is waiting for a query.
• EXECUTING - Query is being executed.

Query examples

View all active sessions:

SELECT * FROM `.sys/query_sessions`

Top 20 longest running queries:

SELECT
    Query,
    SessionId,
    NodeId,
    QueryStartAt
FROM `.sys/query_sessions`
WHERE State = 'EXECUTING'
ORDER BY QueryStartAt ASC
LIMIT 20

Search for application sessions filtered by Workload Manager pool:

SELECT
    SessionId,
    Query,
    State,
    WmState,
    ClientAddress
FROM `.sys/query_sessions`
WHERE ApplicationName = 'my_analytics_app'
  AND WmPoolId = 'heavy_queries'

Query compilation cache

The following system view contains information about queries stored in the compilation cache on all cluster nodes:

• compile_cache_queries — contains information about queries in the compilation cache of all cluster nodes.

Each cluster node has its own cache of compiled queries. This cache is used by all sessions running on that node: if during query execution its text is already in the cache, no additional compilation is required.

The system view compile_cache_queries provides data on the cache state on all or selected cluster nodes. When querying this view, requests are sent to each node unless a restriction on NodeId is specified in the WHERE condition. The response returns current information about the cache contents for each node, and the resulting table combines the received data.

View structure:

Example queries

View all queries in the compilation cache:

SELECT * FROM `.sys/compile_cache_queries`

Top 20 most popular queries by number of accesses from 3 nodes:

SELECT
    Query,
    SUM(AccessCount) AS Hits
FROM `.sys/compile_cache_queries`
WHERE NodeId IN (50000, 50001, 50003)
GROUP BY Query
ORDER BY Hits DESC
LIMIT 20

Activity statistics by user:

SELECT
    UserSID,
    COUNT(DISTINCT QueryId) AS Plans,
    SUM(AccessCount) AS Hits,
    AVG(CompilationDurationMs) AS AvgCompileMs
FROM `.sys/compile_cache_queries`
GROUP BY UserSID
ORDER BY Hits DESC

Search for queries with long compilation:

SELECT
    Query,
    NodeId,
    CompilationDurationMs,
    AccessCount
FROM `.sys/compile_cache_queries`
WHERE CompilationDurationMs > 1000
ORDER BY CompilationDurationMs DESC

History of overloaded partitions

The following system views contain a history of high load moments on individual partitions of database tables:

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

Partitions with peak load over 70% (CPUCores > 0.7) are included in the views. Within one interval, partitions are ranked by peak load value.

Both views contain the same set of fields:

The view keys are:

• IntervalEnd — interval end time;
• Rank — partition rank by peak load CPUCores in this interval.

For example, if a table has 10 partitions, then 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 Rank from 1 to 10 and the same IntervalEnd "20.12.2024 11:00".

Example queries

The following query outputs partitions with CPU consumption over 70% in the specified time interval, with tablet IDs and their sizes at the time of exceeding. The query is run against the .sys/top_partitions_one_minute view, which contains data for the last 6 hours broken down by minute intervals:

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 outputs partitions with CPU consumption over 90% in the specified time interval, with tablet IDs and their sizes at the time of exceeding. The query is run against the .sys/top_partitions_one_hour view, which contains data for the last 2 weeks broken down by hourly intervals:

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

Partition history with broken locks

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

• top_partitions_by_tli_one_minute — data is broken down into minute intervals, contains history for the last 6 hours.
• top_partitions_by_tli_one_hour — data is broken down into hourly intervals, contains history for the last 2 weeks.

The views output 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 view keys are:

• IntervalEnd - the end time of the interval.
• 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 an hourly interval "20.12.2024 10:00-11:00" will output 10 rows sorted in descending order of LocksBroken. They will have Rank from 1 to 10 and the same IntervalEnd "20.12.2024 11:00".

Both views contain the same set of fields:

Query examples

The following query outputs partitions in the specified time interval, with tablet IDs and the number of broken locks. The query is run against 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

Resource pool information

The resource_pools system view contains information about settings of resource pools.

System view structure:

Example

The following query outputs information about the settings of the resource pool named default:

SELECT
    Name,
    ConcurrentQueryLimit,
    QueueSize,
    DatabaseLoadCpuThreshold,
    ResourceWeight,
    TotalCpuLimitPercentPerNode,
    QueryCpuLimitPercentPerNode,
    QueryMemoryLimitPercentPerNode
FROM `.sys/resource_pools`
WHERE Name = "default";

Resource pool classifier information

The resource_pools_classifiers system view contains information about settings of resource pool classifiers.

System view structure:

Example

The following query outputs information about the settings of the resource pool classifier named olap:

SELECT
    Name,
    Rank,
    MemberName,
    ResourcePool
FROM `.sys/resource_pools_classifiers`
WHERE Name = "olap";

Users, groups, and access rights

The following system views contain information about users, access groups, user membership in groups, and access rights granted to groups or directly to users.

Information about users

The auth_users view contains a list of local users YDB. It does not include users authenticated through external systems such as LDAP.

Administrators have full access to this view. Regular users can only view their own data.

Table structure:

Information about groups

The auth_groups view contains a list of access groups.

Only administrators have access to this view.

Table structure:

Information about group membership

The auth_group_members view contains information about membership in access groups.

Only administrators have access to this view.

Table structure:

Information about access rights

The views contain a list of granted access rights.

Include two views:

• auth_permissions: Explicitly granted access rights.
• auth_effective_permissions: Effective access rights considering inheritance.

In this view, the user sees only those access objects for which they have the ydb.granular.describe_schema right.

Table structure:

Example queries

Getting explicitly granted rights on an access object - the my_table table:

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

Getting effective rights on an access object - the my_table table:

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

Getting rights granted to user user3:

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

Information about owners of access objects

The auth_owners view displays information about owners of access objects.

In this view, the user sees only those access objects for which they have been granted the ydb.granular.describe_schema right.

Table structure:

Streaming queries

Viewing information about streaming queries

The system view streaming_queries contains information about all created streaming queries.

In this view, the user sees only those streaming queries for which they have been granted the ydb.granular.describe_schema right.

Table structure: