Configuring Hot and Cold Isolation Partition Strategy, Adding Label disk=hhd Causes Slow Data Writing and High CPU Usage

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 配置冷热隔离分区策略,新增lable disk=hhd 出现数据写入慢,cpu拉的很高

| username: 最强王者

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
6.5.2
Some unexpected issues occurred last night while configuring the hot and cold strategy:

  1. First step: set the SSD and HDD strategies, second step: set all historical data to the specified SSD strategy, for example, DATABASE estadver “CONSTRAINTS=”“[+disk=ssd]”“” SCHEDULED.
  2. Added 2 new KV instances with labels set to disk=hhd.
    Phenomenon: After an hour, the CPU usage of the cluster increased significantly, real-time offline data writing became very slow, and some data started to migrate to the KV nodes labeled as hhd. After scaling down the newly added hhd, it returned to normal.

Process:
First step:
Configured strategy: CREATE PLACEMENT POLICY onssd CONSTRAINTS=“[+disk=ssd]”;
CREATE PLACEMENT POLICY onhdd CONSTRAINTS=“[+disk=hhd]”;
Second step: Set all historical databases to the SSD strategy to avoid data being written to hhd, DATABASE estadver “CONSTRAINTS=”“[+disk=ssd]”“” SCHEDULED.
Third step: Added and expanded KV nodes with the label set to hhd.

Impact: After observing for an hour, data was being written to the hhd disk (which should not have happened), the CPU usage of the machines labeled as hhd was very high, and real-time offline writing was very slow.

[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Logs/Monitoring]

| username: Fly-bird | Original post link

The disk I/O is not good, right?

| username: dba-kit | Original post link

Execute show placement; to see if there are only database rules and no table rules?

| username: dba-kit | Original post link

To fix the placement rule for existing tables, you need to set it for each table individually. Setting it only for the database will only be effective for new tables.

| username: dba-kit | Original post link

I suggest referring to this post and directly modifying the placement rules of the default group through pd-ctl. This way, you can avoid setting each table individually.

| username: 像风一样的男子 | Original post link

Master, please write a column for everyone to observe.

| username: dba-kit | Original post link

Setting a Flag :cn:, I’ll write a post about hot and cold data separation by the end of the year and try to get an MVA :rofl:

| username: 最强王者 | Original post link

Very impressive.

| username: dba-kit | Original post link

Actually, this reply should be set as the “Best Answer,” so if others have the same issue, they can directly see the solution when they click into the post. Currently, when you click in, it shows this Flag, and you have to scroll through the post to find the useful information. (PS: Fewer people will see it, and if the Flag isn’t completed, it won’t be too embarrassing :monkey:)

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.