Database system tables

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

    • 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

    List of DB tables with in-flight sizes and loads

    1. SELECT
    2.     Path,
    3.     COUNT(*) as Partitions,
    4.     SUM(RowCount) as Rows,
    5.     SUM(DataSize) as Size,
    6.     SUM(CPUCores) as CPU
    7. GROUP BY Path
    • 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

    System views - 图3

    2.     IntervalEnd,
    3.     QueryText,
    4.     ReadBytes,
    5.     ReadRows,
    6.     Partitions
    7. WHERE Rank = 1

    • 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

    System views - 图5

    Recent queries that read the most bytes per minute:

    1. SELECT
    3.     SumReadBytes,
    4.     MinReadBytes,
    5.     SumReadBytes / Count as AvgReadBytes,
    6.     MaxReadBytes,
    7.     QueryText
    8. FROM `.sys/query_metrics_one_minute`
    9. WHERE SumReadBytes > 0
    10. ORDER BY IntervalEnd DESC, SumReadBytes DESC

    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.