Database system tables

• Table partitions
• Top queries
• Query details
• Notes

To enable internal introspection of the DB state, the user can make queries to special service tables (system views). These tables are accessible from the root of the database tree and use the .sys system path prefix.

Hereinafter, in the descriptions of available fields, the Key column contains the corresponding table’s primary key field index.

Table partitions

• partition_stats

A system view that provides detailed information about individual partitions of all DB tables. Contains information about instant metrics, such as CPU load or count of in-flight transactions, as well as cumulative counters of a variety of operations on a partition (for example, total number of rows read). Primarily designed for detecting various irregularities in the load on a table partition or in the size of table partition data.

Table structure:

Restrictions:

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

Examples:

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

• top_queries_by_duration_one_minute
• top_queries_by_duration_one_hour
• top_queries_by_read_bytes_one_minute
• top_queries_by_read_bytes_one_hour
• top_queries_by_cpu_time_one_minute
• top_queries_by_cpu_time_one_hour

A group of system views for analyzing the flow of user queries. They let you see a time-limited query history divided into intervals. Within a single interval, the top 5 queries by a specific metric are saved. Currently, minute and hour intervals are available, and the top list can be made based on the total query execution time (the slowest), the number of bytes read from the table (the widest), and the total CPU time used (the heaviest).

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 ms.

Table structure:

Restrictions:

• Query text limit is 4 KB.
• Tables with minute intervals contain the history for the last 6 hours.
• Tables with hourly intervals contain the history for the last 2 weeks.

Examples:

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

• query_metrics_one_minute

Detailed information about queries, broken down by minute. 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.

Table structure:

Restrictions:

• Query text limit is 4 KB.
• The table contains the history for the last 6 hours.
• Within the interval, information is provided for no more than 256 different queries.
• Statistics may be incomplete if the database is under heavy load.

Examples:

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

Notes

Please keep in mind that load caused by accessing system views is more analytical in nature, and therefore making frequent queries to them in large DBs will consume a lot of system resources. A load of about 1-2 rps is quite acceptable.