PolarDB-X Best Practice Series (12): Best Practices for Data and Traffic Skew Analysis (Part 3)

By Mocheng

Background

"Best Practices for Data and Traffic Skew Analysis (Part 1)" and "Best Practices for Data and Traffic Skew Analysis (Part 2)" introduced how to analyze and confirm data skew and traffic skew respectively. This article continues to introduce the operation steps for skew optimization. The optimization ideas for data skew and traffic skew are the same. For ease of description, "skew" is used below to uniformly represent data skew and traffic skew.

Skew Optimization

Single Tables

View the data volume and read and write traffic of a single table

As with skew analysis, you can use the following SQL to retrieve the data volume and read and write traffic information of the table from the system view INFORMATION_SCHEMA.TABLE_DETAIL:

SELECT t.table_schema,
       t.table_name,
       t.table_total_read * 100 / t_all_table.total_read                 AS table_read_pct,
       t.table_total_write * 100 / t_all_table.total_write               AS table_write_pct,
       t.table_total_data_size_gb * 100 / t_all_table.total_data_size_gb AS table_data_size_pct,
       100 / t_all_table.total_logical_table_count                       AS logical_table_avg_pct,
       t.table_total_read,
       t.table_total_write,
       t.table_total_data_size_gb
FROM (SELECT table_schema,
      table_name,
      storage_inst_id,
      part_count,
      inst_table_total_read  AS table_total_read,
      inst_table_total_write AS table_total_write,
      data_size_gb           AS table_total_data_size_gb
      FROM (SELECT table_schema,
            table_name,
            FIRST_VALUE(storage_inst_id) OVER (PARTITION BY table_schema, table_name) AS storage_inst_id,
            COUNT(1) OVER (PARTITION BY table_schema, table_name)                     AS part_count,
            SUM(ROWS_READ)                                                            AS inst_table_total_read,
            SUM(ROWS_INSERTED + ROWS_UPDATED + ROWS_DELETED)                          AS inst_table_total_write,
            SUM(DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / (1024 * 1024 * 1024)        AS data_size_gb
            FROM information_schema.table_detail
            GROUP BY table_schema, table_name, storage_inst_id) t_part_total
      WHERE table_schema = "db_xxx"
      AND table_name = "tb_xxx") AS t
         JOIN (SELECT storage_inst_id,
               COUNT(DISTINCT table_schema, table_name, index_name)               AS total_logical_table_count,
               SUM(ROWS_READ)                                                     AS total_read,
               SUM(ROWS_INSERTED + ROWS_UPDATED + ROWS_DELETED)                   AS total_write,
               SUM(DATA_LENGTH + INDEX_LENGTH + DATA_FREE) / (1024 * 1024 * 1024) AS total_data_size_gb
               FROM information_schema.table_detail
               GROUP BY storage_inst_id) AS t_all_table
              ON t.storage_inst_id = t_all_table.storage_inst_id

Select an optimization solution

Based on the different proportions of data and read and write traffic on Data Nodes (DNs), there are three skew optimization ideas for single tables.

1. Switch to a partitioned table

• Applicable scenarios: A single table with skewed data volume or write traffic. A common business scenario is a large single table.

• Operation steps:

You can change the table type and partitioning strategy (AUTO mode).
For information about how to select a partition key, you can refer to the "Partitioned tables" - "Change the partition key" section below.

2. You can switch to a broadcast table and enable random routing for broadcast tables (enabled by default).

• Applicable scenarios: A single table with skew only in read traffic. A common business scenario is a configuration table.

• Operation steps:

You can change the table type and partitioning strategy (AUTO mode).
Compute layer parameters You can search for ENABLE_BROADCAST_RANDOM_READ.

3. You can migrate the table to other DNs through single table scattering and Locality.

• Applicable scenarios: The read and write traffic and data volume of a single table are not large, but a large number of single tables causes overall skew.

• Operation steps:

For data and write skew, you need to switch to a partitioned table and eliminate the skew by scattering the writes to different partitions. For read traffic skew, you can switch to a broadcast table and scatter the reads to different DNs through random routing. You need to note that because a broadcast table keeps a replica on each DN, it has a higher storage cost. At the same time, to maintain these replicas, distributed transactions are automatically enabled when data is written to the broadcast table. Therefore, a broadcast table is more suitable for tables with low data volume and write volume. If you look at each single table independently and there is no obvious skew, but the skew is caused by all single tables being concentrated on one DN, you need to scatter the single tables to other DNs by specifying Locality. You also need to enable single table scattering so that subsequently created single tables are evenly distributed across all DNs.

For ease of description, we take 10% as the dividing line for skew as an example. We consider that a single table whose read traffic proportion, write traffic proportion, or data volume proportion exceeds 10% causes skew. You can refer to the following table to select an appropriate optimization solution.

Read traffic ratio	Write traffic ratio	Data volume ratio	Optimization solution
--	--	Higher than 10%	You can switch to a partitioned table.
--	Higher than 10%	--	You can switch to a partitioned table.
Higher than 10%	Lower than 10%	Lower than 10%	You can switch to a broadcast table.
Lower than 10%	Lower than 10%	Lower than 10%	If there are many single tables, you can scatter the single tables and migrate them to other Data Nodes (DNs) by using Locality.

Partitioned Tables

Change the partition key

Selecting a new partition key requires considering the existing read and write traffic and column data distribution characteristics. You can refer to the following steps for screening.

1. Analyze the SQL templates in INFORMATION_SCHEMA.PLAN_CACHE

The system view INFORMATION_SCHEMA.PLAN_CACHE provides information about the execution plan cache. This view is used to monitor and manage cached execution plans. Through PLAN_CACHE, you can analyze the overall characteristics of business SQL, including the following key content:

Column name	Description	Remarks
COMPUTE_NODE	Compute node information
SCHEMA_NAME	Database name
TABLE_NAMES	Table name list	For SQL statements that involve multiple tables, such as JOIN, the table names are deduplicated and separated by commas.
HIT_COUNT	Cache hit count	This represents the execution frequency of this SQL template.
SQL	SQL template
TYPE_DIGEST	Parameter type summary	For the same SQL template, there may be multiple PLAN_CACHE records depending on the types of the passed parameters.
PLAN	Execution plan

You can use the following SQL to query PLAN_CACHE to calculate statistics on high-frequency SQL templates.

SELECT sql_type, rn, hit_cnt, `sql`
FROM (
  SELECT sql_type, `sql`, hit_cnt, ROW_NUMBER() OVER (PARTITION BY sql_type ORDER BY hit_cnt DESC) AS rn
  FROM (SELECT `sql`,
          SUM(hit_count) AS hit_cnt,
          CASE
          WHEN `sql` LIKE "select %" THEN "SELECT"
          WHEN `sql` LIKE "update %" THEN "UPDATE"
          WHEN `sql` LIKE "delete %" THEN "DELETE"
          WHEN `sql` LIKE "% on duplicate key update %" THEN "UPSERT"
          WHEN `sql` LIKE "insert %" THEN "INSERT"
          END        AS sql_type
          FROM INFORMATION_SCHEMA.PLAN_CACHE
          WHERE SCHEMA_NAME = 'db_xxx'
          AND find_in_set("tb_xxx", TABLE_NAMES)
          GROUP BY `sql`) sql_with_cnt
  WHERE sql_type IN ("SELECT", "UPDATE", "DELETE", "UPSERT")
  AND hit_cnt > 0
  GROUP BY sql_type, sql, hit_cnt
) sql_with_rn
where rn < 10 
ORDER BY sql_type, rn;

With the SQL template information, the idea of selecting a partition key can be simply summarized as "1 do and 2 don'ts, prioritize high-frequency SQL, and pay attention to skew and indexes".

• "1 do" means:

o For SELECT, UPDATE, and DELETE statements, pay attention to the conditions in the WHERE clause. First, you must select the columns that appear in all WHERE clauses as the partition key.

• "2 don'ts" means:

o For UPDATE and UPSERT statements, you can pay attention to the SET clause and the ON DUPLICATE KEY UPDATE clause. If the partition key appears in these two clauses, the value of the partition key may be updated. Updating the partition key requires locking the row record first, deleting the record in the old partition, and inserting the record in the new partition. The overhead is large. You can try not to select columns that appear in the SET clause or the ON DUPLICATE KEY UPDATE clause as partition keys.

o For SELECT, UPDATE, and DELETE statements that contain multiple tables, you can also pay attention to the join conditions of the JOIN clause. Ensure that the use of the new partition key does not affect JOIN pushdown.

• "Prioritizing high-frequency Structured Query Language (SQL) statements" means:

o If you cannot find a column that satisfies condition 1 and condition 2 for all SQL templates, you can prioritize selecting a column that satisfies the condition "1 required and 2 avoided" for statements with rn < 3 as the partition key. You can also consider adding Global Secondary Indexes (GSIs) for other SQL statements to optimize performance.

• "Paying attention to skew and indexes" means:

o You can ensure that the selected partition key is not skewed. At the same time, you can ensure that the local Unique Key (UK) on the old partition key is converted to a global UK after the partition key is changed.

2. Determining whether the partition key is skewed

Column-level statistics are recorded in the system table metadb.column_statistics. These statistics are used by the optimizer to generate execution plans based on costs. They can also be used to analyze whether columns are skewed. The statistics include the following key content:

Column name	Description	Remarks
SCHEMA_NAME	Database name
TABLE_NAME	Table name
COLUMN_NAME	Column name
CARDINALITY	Cardinality	A larger number indicates more unique values.
NULL_COUNT	Number of NULL values	The number of NULL values in the data of this column.
HISTOGRAM	Histogram	The distribution quantity of the data in this column across different value ranges.
TOPN	Top N data list	The most frequently occurring values in the data of this column, such as: {"countArr":[30,14,14,12,13,30],"valueArr":[7327,7559,8802,9684,7224,8955],"type":"Date","sampleRate":0.92}

You can use the following SQL statement to view the statistics of columns.

select * 
from metadb.column_statistics 
where schema_name = "db_xxx" 
  and table_name = 'tb_xxx' 
  and column_name = 'col_xxx'\G

A reasonable partition key must meet the following requirements:

CARDINALITY is greater than the number of partitions.
The occurrences listed in countArr in the TOPN data list are all in the same order of magnitude.
NULL_COUNT is less than 100000 / number of partitions. (The number of sampled rows is 100000. If the number of null values exceeds 100000 / number of partitions, it indicates that skew exists.)
Determining whether index optimization is required

• Determining whether you need to add or delete indexes: For high-frequency statements whose query conditions or JOIN conditions do not contain partition keys, you can consider adding GSIs to improve performance. A common example is adding a GSI to the primary key to optimize UPDATE statements that only contain primary key conditions.

• Paying attention to whether the local UK needs to be converted into a global UK: If the local UK contains all old partition keys and does not contain all new partition keys, you can first create a global UK and then delete the local UK before the partition change. This ensures that the original unique constraints are maintained during and after the partition change.

Hotspot scattering

If the partition key has a large amount of data or a large amount of access traffic on some values, these values are called "hotspots". A hotspot is a specific situation of skew. It causes the amount of data on some partitions to be significantly higher than that on other partitions. You can use the column statistics introduced in the "Change the partition key" - "Determine whether the partition key is skewed" section to help confirm whether hotspot values exist.

Hotspot scattering refers to scattering hot partitions to all Data Nodes (DNs) by adding secondary partitions. It is often used to solve the hotspot problem on the RANGE partition of the time column. For the operation steps, refer to: Hotspot hashing

Note that the selection of the partition key for the secondary partition must also comply with the principles in the "Change the partition key" section.

Summary

This document focuses on how to optimize storage and traffic skew. It provides general operation steps for single tables and partitioned tables. The following figure summarizes the flowchart for optimizing storage and traffic skew:

"How to select a partition key" is a large topic. You must consider the impact on read and write requests, data distribution, and unique constraints. This document provides ideas for selecting partition keys from the perspective of data and traffic balance. It lists the aspects that need to be considered as much as possible. However, it cannot guarantee to cover all business scenarios. In actual operations, you can pay attention to controlling the business scope affected by a single change. You can also formulate grayscale and rollback plans.

Q&As

Q: Are all SQL templates recorded in the PLAN_CACHE view?

A: This view records 4000 SQL templates by default. It is refreshed every 12 hours. The SQL template is the result of normalizing and parameterizing SQL. For general business, it can cover all high-frequency SQL statements. For periodically executed SQL statements, such as data analysis or extraction tasks executed in the early morning, if you view the PLAN_CACHE view during the day, you may miss these SQL statements. You can pay attention to viewing them multiple times at different times.

Q: Is it safe and reliable to query the PLAN_CACHE view and the column_statistics system table?

A: The PLAN_CACHE is stored in the memory of the Compute Node (CN), and the column_statistics is stored in the Global Meta Service (GMS) node. It is safe to query them. However, you need to avoid high-concurrency queries (such as performing a large number of queries on them in a program).

Q: How can you prevent data and traffic skew in advance during the early business design process?

A: Refer to the previous article, "PolarDB-X Best Practice Series (7): Partition Design"

Community

PolarDB-X Best Practice Series (12): Best Practices for Data and Traffic Skew Analysis (Part 3)

Background

Skew Optimization

Single Tables

View the data volume and read and write traffic of a single table

Select an optimization solution

Partitioned Tables

Change the partition key

Hotspot scattering

Summary

Q&As

Read previous post:

Read next post:

ApsaraDB

You may also like

Comments

ApsaraDB

Related Products

Best Practices

PolarDB for PostgreSQL

PolarDB for Xscale

PolarDB for MySQL