Work with tables in ADQM Control

Elena Dvoryadkina

The Tables page of the ADQM Control web interface displays information about data tables in an ADQM cluster connected to the monitoring system. On this page, you can view a list of all cluster tables with general information about them, switch to details on columns of an individual table, and also visually identify changes in sizes of tables using graphs.

The Tables page
The Tables page

At the top of the screen, you can set up the following filters to select which data the Tables page should display:

  • Cluster — ADQM cluster where data tables are located.

  • Time — time period for which information about tables should be displayed. In the window that opens when you click the field, you can select an interval from the offered options on the Range tab or set a custom time range (at least 1 hour) on the Calendar tab.

  • Refresh — frequency of data updates.

ADQM Control supports the following ADQM/ClickHouse table engines (i.e. can show tables based on these engines on the Tables page):

For integration table engines (used to access external data storage systems — for example, MySQL, MongoDB, HDFS, Hive, S3, Kafka, EmbeddedRocksDB, RabbitMQ, PostgreSQL), the correctness and completeness of the information displayed in ADQM Control is currently not guaranteed.

List of tables

The Tables page contains two tabs, each of which displays a list of ADQM cluster tables in a specific way:

  • Separated — tables located on different hosts of a cluster are shown separately.

  • Aggregated — tables are grouped by table name, database name, and engine (that is, each table located on multiple hosts of a cluster is shown as one entry in the list).

The Tables page lists data tables of an ADQM cluster as a table with the following fields.

Field Description

Table name

Name of an ADQM table

Size

  • On the Separated tab: size of compressed data in a table on a host at the end of the selected time interval.

  • On the Aggregated tab: total size of compressed data in a table on all hosts at the end of the selected time interval.

Database

Database to which a table belongs

It is also possible to add more fields with additional information on ADQM tables — to do this, click Customize table and select the required fields in the drop-down list.

Additional fields
Additional fields

The Separated and Aggregated tabs provide different sets of fields for displaying metrics of ADQM tables located on specific hosts and aggregated tables, respectively.

  • Separated

  • Aggregated

Field Description

Exist

Table lifetime (since the start of monitoring the ADQM cluster in ADQM Control)

Row count

Number of data rows in a table on a host at the end of the selected time interval

Request count

Number of queries sent to a table on a host during the selected time interval

Last request

Time the last query to a table on a host was run during the selected time interval

Increase

Change in the size of a table on a host over the selected time interval (a negative value means a decrease in the amount of data in the table)

Host

ADQM cluster host where a table is located. Clicking on the icon link to page half dark link to page half light opens ADQM Notebook at http://<adqm_host_name>:8123/notebook for ADQM starting with the version 25.8.16.34 or the built-in ClickHouse web interface at http://<adqm_host_name>:8123/play for earlier versions of ADQM, where <adqm_host_name> is the ADQM host FQDN (the system should be configured so that is can resolve the host FQDN into the IP address)

Engine

Table engine
Field Description

Exist

Table lifetime (since the start of monitoring the ADQM cluster in ADQM Control)

Row count

Total number of data rows in a table across all hosts at the end of the selected time interval

Request count

Total number of queries sent to a table on all hosts during the selected time interval

Last request

Time the last query to a table was run during the selected time interval

Increase

Change in the total size of a table over the selected time interval (a negative value means a decrease in the amount of data in the table)

Hosts

Number of hosts on which a table is located

Engine

Table engine

Size skew

Data table size skew across different hosts

Rows skew

Skew in the number of rows in a data table across different hosts

Requests skew

Skew in the number of queries sent to a table to different hosts

Size imbalance

Imbalance in distribution of table data between hosts by data size. Values of imbalance metrics are in the [0%, 100%) range — a higher percentage value means a greater imbalance

Rows imbalance

Imbalance in distribution of table data between hosts by the number of rows

Requests imbalance

Imbalance in distribution of queries sent to a table on different hosts. For example, for 4 hosts with a query distribution of [0, 100, 0, 0], the metric value will be 75%, and with a distribution of [0, 10, 50, 100] — approximately 53.13%

Graphs

On the Tables page, you can get graphs to track the dynamics of changes in metrics of ADQM tables. In the columns of metrics for which graphs are supported, there is the icon graph half default dark graph half default light. Click the icon — above the list of tables, ADQM Control will display a panel with a graph built based on the corresponding metric of the ADQM table. You can add up to 8 graphs (inclusive) simultaneously.

To see values of metrics at some point in time, hover the mouse over the graph area. The Detalization option allows you to choose the level of detail (number of points on the graph): Low or High.

Sample graphs by size of data in tables
Sample graphs by size of data in tables

To delete a graph, click the icon corresponding to the graph color in the table (for example, graph on yellow dark graph on yellow light and graph on turq dark graph on turq light in the image above) or the icon delete dark delete light in the legend to the right of the graphs.

Filter and sort data

In the Table name, Database, Engine, and Host (on the Separated tab) column headers, there are filters that you can use to select the necessary data on the Tables page. To set a filter, click the icon open filter default dark open filter default light and enter a value you are looking for (a table/database/engine name or its part, or full names of hosts). The icon filter dark focus filter light focus means that a filter is defined for the column. To reset all filters by columns, click reset btn dark reset btn light.

Clicking on System filters opens a list of predefined filters that you can activate with the switch toggle on dark toggle on light:

  • Show system.tables — display ADQM system tables from the system database;

  • Only existing tables — display only tables that exist in the cluster at the last point of the specified time interval (tables for which ADQM Control received metrics at the end of this time period);

  • Only existing subtables (on the Aggregated tab) — to calculate data in a row corresponding to a group of tables, take into account only tables that exist in the group at the last point of the specified time interval;

  • Only tables with graph — display only the tables based on which graphs are currently plotted.

It is also possible to sort data on the Tables page by values in any column of the table with the list of ADQM tables. To apply sorting by a column, click the icon asc dark active default asc light active default or desc dark active default desc light active default in its header.

Above the list of tables, there is a panel that shows the sorting (Sorted by) and filtering (System filters, Filtered by) conditions currently applied to data.

Current conditions for sorting and filtering data on the Tables page
Current conditions for sorting and filtering data on the Tables page

You can also view the list of the largest tables by data amount or the list of the most frequently queried tables in the ADQM cluster in the Top 10 tables section on the Dashboard page.

Table details

Open a page with detailed information about an ADQM table on a specific host as follows, depending on which tab on the Tables page you are navigating from.

  • Separated

  • Aggregated

Click a table name in the Table name column.

Select a table from the Separated list
Select a table from the Separated list

Click a name of an aggregated table (group of tables) in the Table name column — this opens a page with a list of tables in the selected group (with a full set of metrics as in the Separated tab).

Select a group of tables from the Aggregated list
Select a group of tables from the Aggregated list

Depending on the Only existing subtables switch state on the page that opens, the list includes all the group tables that existed in the cluster during the specified time interval, or only tables that existed at the last point in time of this interval. The top of the page displays general information about the group of tables (the same as the corresponding row on the Aggregated tab) — Customize table allows changing the set of metrics. The Reads, Writes, Changes, and Consumers cards show statistics on execution of queries sent to the group tables within the selected time interval — to manage card visibility, use the Customize cards menu. On this page, you can also create graphs by metrics of individual tables from a group and metrics of an aggregated table. To do this, use the icon graph half default dark graph half default light in the columns corresponding to the required metrics. The aggregated table graph is marked with the Σ sign in the legend.

To go to the details of a table located on a specific host, click the table name in the corresponding row of the list.

Select a table from a group
Select a specific table from a group

General information about a table

On the page with ADQM table details, there is information about the table — its name and size, as well as the metrics selected via Customize table on the Separated tab. You can build graphs based on the Size, Row count, and Request count metrics — to do this, click the icon graph half default dark graph half default light in the corresponding column of the table with general information about the ADQM table.

A graph by Size may display table revision marks indicating that the following changes were made to an ADQM table during the selected time interval, which could affect the table size:

table revision columns added Columns added — a new column has been added;

table revision columns deleted Columns deleted — a column has been removed;

table revision codecs changed Codecs changed — a compression codec applied to a column has been changed.

Hovering the mouse cursor over a table revision mark on a graph displays a tooltip showing which table column was modified and the time of the change (to pin the tooltip, click a mark). To hide marks (or display them again if necessary), click the name of the corresponding mark type below the graph.

Sample graph by table size with table revisions
Sample graph by table size with table revisions

The Reads, Writes, Changes, and Consumers cards display statistics on table queries executed during the selected time interval — see the description below.

Information about columns

This page also displays a table with the following fields that contain detailed information about each column of the selected ADQM table (to control the visibility of some fields, use the Customize table option).

Field Description

Column name

Column name at the end of the selected time interval

Size

Size of column data in compressed form

Uncompressed Size

Size of column data in uncompressed form

Compression

Percent of disk space saved by data compression

Type

Column data type

Codec

Data compression codecs applied to a column

Comment

Comment for a column

Increase

Change in size of column data over the selected time interval
Information on table columns
Information on table columns

You can filter data in this table by the Column name field (to set a filter, click open filter default dark open filter default light in the column header) and sort by any column except Comment (use the icons asc dark active default asc light active default and desc dark active default desc light active default in column headers).

 
For data visualization, you can build graphs that allow you to see how the size of compressed or uncompressed data in individual columns of an ADQM table changes over the selected time interval, or to analyze effect of applying data compression on the amount of disk space saved. To do this, use the icon graph half default dark graph half default light in the Size, Uncompressed Size, or Compression column, respectively. The graph area can display up to 7 graphs by individual columns and one graph by the entire table size at a time.

Sample graphs by the size of compressed data in individual columns of an ADQM table
Sample graphs by the size of compressed data in individual columns of an ADQM table
Sample graph by the amount of disk space saved with data compression
Sample graph by the amount of disk space saved with data compression

A graph by an ADQM table column can display column revision marks, which indicate the following actions performed on that column during the selected time period:

table revision columns added Columns added — the column has been added;

table revision columns deleted Columns deleted — the column has been removed;

table revision codecs changed Codecs changed — a compression codec applied to the column has been changed.

Hovering the mouse cursor over a column revision mark on a graph displays a tooltip with information about the time of the corresponding column change (you can pin the tooltip by clicking a mark). To hide marks (or display them again if necessary), click the name of the corresponding mark type below the graph.

Sample graph for a table column with column revisions
Sample graph for a table column with column revisions
CAUTION

In the following cases, graph lines may be interrupted, which can lead to an inaccurate representation of column states and confuse users:

  • a column was renamed and then reverted to its original name — all this time, the column (with any name) continued to exist and store data, and its metrics were collected;

  • a column was dropped and then re-created with the same name — in fact, these are two different columns existing at different time intervals.

The rendering of graphs for such cases will be fixed in future releases of ADQM Control.

From the Tables page (as well as from the page with ADQM table details), you can access the list of queries sent to a specific ADQM table for a selected time interval. To do this, click the icon query btn default dark query btn default light in the Table name cell corresponding to the desired data table — this opens the Queries page with automatically configured filters by table name, database, and host.

Switch to the list of queries sent to the ADQM table
Switch to the list of queries sent to the ADQM table

On the page with details of a separate or aggregated ADQM table, you can view statistics on queries to the table in the Reads, Writes, Changes, and Consumers cards.

Cards with statistics on queries sent to an ADQM table
Cards with statistics on queries sent to an ADQM table

The cards contain the following information.

Card Description

Reads

Data on the execution of Read queries sent to an ADQM table for the specified time interval:

  • Runs — number of queries;

  • Avg / p95 — average query execution time and the 95th quantile of query execution time;

  • Rows per run — number of rows read by a single query (some inaccuracy in this value is allowed in cases when queries join multiple tables using the JOIN operator);

  • Scan share — percentage of read table rows relative to the total number of rows in the table (this value may also be inaccurate if the JOIN operator was used in queries);

  • Failed — number of failed query executions.

Writes

Data on the execution of Write queries sent to an ADQM table for the specified time interval:

  • Runs — number of queries;

  • Avg / p95 — average query execution time and 95th quantile of query execution time;

  • Rows per run — number of rows written in a single query;

  • Async flushes — percentage of data flushes from the buffer to the database storage (with enabled asynchronous inserts) relative to the number of queries to write data to the table;

  • Failed — number of failed query executions.

Changes

Data on the execution of queries related to table management:

  • Runs — total number of queries for managing the table, with or without changing its structure;

  • Schema — number of queries that modify the table schema (such as CREATE, RENAME, ALTER with adding or modifying columns, DROP, UNDROP, etc.);

  • Maintenance — number of table maintenance queries that do not change its structure (such as OPTIMIZE, CHECK, system commands referencing the table name, etc.);

  • Failed — number of failed table management queries.

Consumers

Data about users who accessed a table:

  • Users — number of users who accessed the table in their queries;

  • Query groups — number of query groups sent to the table;

  • Time share — percentage of the total time spent on queries to the table relative to the total time spent on all queries executed on the same hosts during the specified time interval;

  • Peak memory — maximum memory consumption by a query accessing the table.

Clicking the icon query btn default dark query btn default light in the top-right corner of the Reads or Writes card opens the All queries page, where relevant filters for analyzing table queries are automatically applied, and the Kind graph is built to show the number of executed queries of each type (if more than 8 query types were executed during the specified period, the 8 most frequent types are selected for display). Clicking the same icon in the Consumers card opens the All queries page with the User load graph, which displays users whose queries created the most load on the cluster (maximum 8 users; the ∅ symbol on the graph indicates system-level query execution on individual cluster hosts during the processing of distributed queries).

To show or hide cards with statistics for queries sent to a table, click Customize cards and select the required cards from the drop-down list. For an aggregated table, this menu also allows you to choose a mode that specifies which queries should be counted in the statistics: Client (only queries actually run by users) or Cluster (all queries to a table executed across all cluster hosts).

Manage cards with query statistics for the ADQM table
Manage cards with query statistics for the ADQM table
Found a mistake? Seleсt text and press Ctrl+Enter to report it