Heatmap - PolarDB - Alibaba Cloud Documentation Center

PolarDB-X provides the heatmap feature to generate heatmaps to display the statistics of queried data rows in partitions and data distribution across partitions in databases. This feature can help you quickly identify hot partitions.

Precautions

Only PolarDB-X V5.4.14 and later versions support the heatmap feature.
Only the data distribution of databases that use the automatic partitioning mode in the current instance is displayed in the heatmap of the instance.
By default, if the number of total partitions in a single logical database exceeds 8,000, the system does not collect statistics of queried data rows in partitions of the database. To change this limit, change the value of the PARTITIONS_HEATMAP_COLLECTION_MAX_SINGLE_LOGIC_SCHEMA_COUNT parameter for the kernel configuration.
By default, if the number of partitions that are specified in a collection task exceeds 8,000, the system collects statistics of queried data rows in only 8,000 partitions. To change this limit, change the value of the PARTITIONS_HEATMAP_COLLECTION_MAX_SCAN parameter for the kernel configuration.

Procedure

Log on to the PolarDB for Xscale console.
In the top navigation bar, select the region where the target instance is located.
On the Instances page, click the PolarDB-X 2.0 tab.
Find the target instance and click its ID.
In the left-side navigation pane, choose Diagnosis and optimization > Heatmap. On the page that appears, view the data distribution and statistics of queried data rows in each partition in the instance.
Turn on Data Node View to view statistics based on data nodes. This way, you can analyze whether data is evenly distributed among the physical data nodes and whether hot data exists on specific data nodes.
- Left-side histogram: The rectangles in this section represent the logical databases, logical tables, and logical partitions in the current instance. In data node view mode, the rectangles in this section represent the storage nodes, logical databases, logical tables, and logical partitions. The length of rectangles for data nodes and logical partitions indicates the size of data in the data nodes and logical partitions. If the rectangle for a data node or logical partition is long, the data node or logical partition contains a large volume of data.
- Bottom histogram: This section displays the sum of queried data rows based on time. You can specify a point in time to view the total number of data rows that are queried in all partitions at the point in time.
- Right-side histogram: This section displays the sum of queried data rows based on partitions. You can specify a partition to view the total number of data rows that are queried in the partition.
- heatmap: The luminance of colors in the heatmap in the central part of the page indicate the levels of data query frequency of partitions. A brighter color indicates a higher level. Bright yellow indicates the highest level, and black indicates the lowest level.
  Note
  If no data is collected, NO DATA is displayed on the heatmap.
  If the number of partitions that are scanned exceeds 3,200, TOO BIG is displayed on the heatmap.
  If the total number of original partitions that are scanned exceeds 1,600, the system merges the statistics. The merge operation merges only the statistics that are collected from the partitions and does not merge the original partitions. The system merges statistics that are collected from partitions in the same logical table on the same data node. If the data volumes in partitions are small, the scope of partitions that can be merged is large. If the total number of partitions exceeds 3,200 after the merge operation is complete, TOO BIG is displayed on the heatmap.
Move the pointer over a specific point in the heatmap to view the details of the statistics. The following table describes the parameters that are included in the details:
- Colored box at the top: The color of the box is the same as the color of the point in the heatmap. The following information is displayed in the box: the number of data rows in the partition that are queried per minute within the period of time that is specified by Started At and Ended At.
- Started At: the start time of the collection period.
- Ended At: the end time of the collection period.
- Current Number of Rows: the number of data rows in the partition.
  Note In the parameter name, Current indicates the point in time when the heatmap is opened and does not indicate the period of time that is specified by Started At and Ended At.
- Data Node: the data node on which the partition resides.
  Note If - is displayed, the partition no longer exists. This may be caused by DDL operations or the merge operation that is performed during the collection of statistics.
- Database: the name of the logical database to which the partition belongs.
- Table: the name of the logical table to which the partition belongs.
- Partition: the name of the logical partition.

Supported operations

Disable the heatmap data collection feature

set ENABLE_SET_GLOBAL = true;
set global ENABLE_PARTITIONS_HEATMAP_COLLECTION = false;

Note The information that was collected before the feature is disabled is still displayed in the heatmap.

Collect statistics of queried data rows in partitions of a specified logical table in a specified database

set ENABLE_SET_GLOBAL = true;
set global PARTITIONS_HEATMAP_COLLECTION_ONLY = '{database1}#{table1}&{table2},{database12}#{table1}&{table2}';

Examples

The following configuration is used to collect statistics of queried data rows in partitions of the customer table and order table in the tpcc database, and partitions in the nation table in tpch database:
```
set ENABLE_SET_GLOBAL = true;
set global PARTITIONS_HEATMAP_COLLECTION_ONLY = 'tpcc#customer&order,tpch#nation';
```
The following configuration is used to collect statistics of queried data rows in partitions of all logical tables in the tpcc database and tpch database:
```
set ENABLE_SET_GLOBAL = true;
set global PARTITIONS_HEATMAP_COLLECTION_ONLY = 'tpcc,tpch';
```

The following configuration is used to collect statistics of queried data rows in partitions of the customer table and order table in all logical databases:

set ENABLE_SET_GLOBAL = true;
set global PARTITIONS_HEATMAP_COLLECTION_ONLY = '#customer,#order';
#### or #####
set global PARTITIONS_HEATMAP_COLLECTION_ONLY = '#customer&order';

The following configuration is used to collect statistics of queried data rows in partitions of all logical tables in all logical databases:
```
set ENABLE_SET_GLOBAL = true;
set global PARTITIONS_HEATMAP_COLLECTION_ONLY = '';
```