因为分区造成了写热点？

[mao_siyu (毛巳煜)]

【TiDB 版本】：

3.0.11

前置条件

1 表分区数量

PARTITION BY RANGE(TO_DAYS(seller_date)) (
    PARTITION p200001 VALUES LESS THAN ( TO_DAYS('2000-01-01 00:00:00') ),
		PARTITION p201701 VALUES LESS THAN ( TO_DAYS('2017-02-01 00:00:00') ),
		PARTITION p201702 VALUES LESS THAN ( TO_DAYS('2017-03-01 00:00:00') ),
		PARTITION p201703 VALUES LESS THAN ( TO_DAYS('2017-04-01 00:00:00') ),
		PARTITION p201704 VALUES LESS THAN ( TO_DAYS('2017-05-01 00:00:00') ),
		PARTITION p201705 VALUES LESS THAN ( TO_DAYS('2017-06-01 00:00:00') ),
		PARTITION p201706 VALUES LESS THAN ( TO_DAYS('2017-07-01 00:00:00') ),
		PARTITION p201707 VALUES LESS THAN ( TO_DAYS('2017-08-01 00:00:00') ),
		PARTITION p201708 VALUES LESS THAN ( TO_DAYS('2017-09-01 00:00:00') ),
		PARTITION p201709 VALUES LESS THAN ( TO_DAYS('2017-10-01 00:00:00') ),
		PARTITION p201710 VALUES LESS THAN ( TO_DAYS('2017-11-01 00:00:00') ),
		PARTITION p201711 VALUES LESS THAN ( TO_DAYS('2017-12-01 00:00:00') ),
		PARTITION p201712 VALUES LESS THAN ( TO_DAYS('2018-01-01 00:00:00') ),
		PARTITION p201801 VALUES LESS THAN ( TO_DAYS('2018-02-01 00:00:00') ),
		PARTITION p201802 VALUES LESS THAN ( TO_DAYS('2018-03-01 00:00:00') ),
		PARTITION p201803 VALUES LESS THAN ( TO_DAYS('2018-04-01 00:00:00') ),
		PARTITION p201804 VALUES LESS THAN ( TO_DAYS('2018-05-01 00:00:00') ),
		PARTITION p201805 VALUES LESS THAN ( TO_DAYS('2018-06-01 00:00:00') ),
		PARTITION p201806 VALUES LESS THAN ( TO_DAYS('2018-07-01 00:00:00') ),
		PARTITION p201807 VALUES LESS THAN ( TO_DAYS('2018-08-01 00:00:00') ),
		PARTITION p201808 VALUES LESS THAN ( TO_DAYS('2018-09-01 00:00:00') ),
		PARTITION p201809 VALUES LESS THAN ( TO_DAYS('2018-10-01 00:00:00') ),
		PARTITION p201810 VALUES LESS THAN ( TO_DAYS('2018-11-01 00:00:00') ),
		PARTITION p201811 VALUES LESS THAN ( TO_DAYS('2018-12-01 00:00:00') ),
		PARTITION p201812 VALUES LESS THAN ( TO_DAYS('2019-01-01 00:00:00') ),
		PARTITION p201901 VALUES LESS THAN ( TO_DAYS('2019-02-01 00:00:00') ),
		PARTITION p201902 VALUES LESS THAN ( TO_DAYS('2019-03-01 00:00:00') ),
		PARTITION p201903 VALUES LESS THAN ( TO_DAYS('2019-04-01 00:00:00') ),
		PARTITION p201904 VALUES LESS THAN ( TO_DAYS('2019-05-01 00:00:00') ),
		PARTITION p201905 VALUES LESS THAN ( TO_DAYS('2019-06-01 00:00:00') ),
		PARTITION p201906 VALUES LESS THAN ( TO_DAYS('2019-07-01 00:00:00') ),
		PARTITION p201907 VALUES LESS THAN ( TO_DAYS('2019-08-01 00:00:00') ),
		PARTITION p201908 VALUES LESS THAN ( TO_DAYS('2019-09-01 00:00:00') ),
		PARTITION p201909 VALUES LESS THAN ( TO_DAYS('2019-10-01 00:00:00') ),
		PARTITION p201910 VALUES LESS THAN ( TO_DAYS('2019-11-01 00:00:00') ),
		PARTITION p201911 VALUES LESS THAN ( TO_DAYS('2019-12-01 00:00:00') ),
		PARTITION p201912 VALUES LESS THAN ( TO_DAYS('2020-01-01 00:00:00') ),
		PARTITION p202001 VALUES LESS THAN ( TO_DAYS('2020-02-01 00:00:00') ),
		PARTITION p202002 VALUES LESS THAN ( TO_DAYS('2020-03-01 00:00:00') ),
		PARTITION p202003 VALUES LESS THAN ( TO_DAYS('2020-04-01 00:00:00') ),
		PARTITION p202004 VALUES LESS THAN ( TO_DAYS('2020-05-01 00:00:00') ),
		PARTITION p202005 VALUES LESS THAN ( TO_DAYS('2020-06-01 00:00:00') ),
		PARTITION p202006 VALUES LESS THAN ( TO_DAYS('2020-07-01 00:00:00') ),
		PARTITION p202007 VALUES LESS THAN ( TO_DAYS('2020-08-01 00:00:00') ),
		PARTITION p202008 VALUES LESS THAN ( TO_DAYS('2020-09-01 00:00:00') ),
		PARTITION p202009 VALUES LESS THAN ( TO_DAYS('2020-10-01 00:00:00') ),
		PARTITION p202010 VALUES LESS THAN ( TO_DAYS('2020-11-01 00:00:00') ),
		PARTITION p202011 VALUES LESS THAN ( TO_DAYS('2020-12-01 00:00:00') ),
		PARTITION p202012 VALUES LESS THAN ( TO_DAYS('2021-01-01 00:00:00') ),
		PARTITION p202101 VALUES LESS THAN ( TO_DAYS('2021-02-01 00:00:00') ),
		PARTITION p202102 VALUES LESS THAN ( TO_DAYS('2021-03-01 00:00:00') ),
		PARTITION p202103 VALUES LESS THAN ( TO_DAYS('2021-04-01 00:00:00') ),
		PARTITION p202104 VALUES LESS THAN ( TO_DAYS('2021-05-01 00:00:00') ),
		PARTITION p202105 VALUES LESS THAN ( TO_DAYS('2021-06-01 00:00:00') ),
		PARTITION p202106 VALUES LESS THAN ( TO_DAYS('2021-07-01 00:00:00') ),
		PARTITION p202107 VALUES LESS THAN ( TO_DAYS('2021-08-01 00:00:00') ),
		PARTITION p202108 VALUES LESS THAN ( TO_DAYS('2021-09-01 00:00:00') ),
		PARTITION p202109 VALUES LESS THAN ( TO_DAYS('2021-10-01 00:00:00') ),
		PARTITION p202110 VALUES LESS THAN ( TO_DAYS('2021-11-01 00:00:00') ),
		PARTITION p202111 VALUES LESS THAN ( TO_DAYS('2021-12-01 00:00:00') ),
		PARTITION p202112 VALUES LESS THAN ( TO_DAYS('2022-01-01 00:00:00') ),
		PARTITION p202201 VALUES LESS THAN ( TO_DAYS('2022-02-01 00:00:00') ),
		PARTITION p202202 VALUES LESS THAN ( TO_DAYS('2022-03-01 00:00:00') ),
		PARTITION p202203 VALUES LESS THAN ( TO_DAYS('2022-04-01 00:00:00') ),
		PARTITION p202204 VALUES LESS THAN ( TO_DAYS('2022-05-01 00:00:00') ),
		PARTITION p202205 VALUES LESS THAN ( TO_DAYS('2022-06-01 00:00:00') ),
		PARTITION p202206 VALUES LESS THAN ( TO_DAYS('2022-07-01 00:00:00') ),
		PARTITION p202207 VALUES LESS THAN ( TO_DAYS('2022-08-01 00:00:00') ),
		PARTITION p202208 VALUES LESS THAN ( TO_DAYS('2022-09-01 00:00:00') ),
		PARTITION p202209 VALUES LESS THAN ( TO_DAYS('2022-10-01 00:00:00') ),
		PARTITION p202210 VALUES LESS THAN ( TO_DAYS('2022-11-01 00:00:00') ),
		PARTITION p202211 VALUES LESS THAN ( TO_DAYS('2022-12-01 00:00:00') ),
		PARTITION p202212 VALUES LESS THAN ( TO_DAYS('2023-01-01 00:00:00') ),
		PARTITION p202301 VALUES LESS THAN ( TO_DAYS('2023-02-01 00:00:00') ),
		PARTITION p202302 VALUES LESS THAN ( TO_DAYS('2023-03-01 00:00:00') ),
		PARTITION p202303 VALUES LESS THAN ( TO_DAYS('2023-04-01 00:00:00') ),
		PARTITION p202304 VALUES LESS THAN ( TO_DAYS('2023-05-01 00:00:00') ),
		PARTITION p202305 VALUES LESS THAN ( TO_DAYS('2023-06-01 00:00:00') ),
		PARTITION p202306 VALUES LESS THAN ( TO_DAYS('2023-07-01 00:00:00') ),
		PARTITION p202307 VALUES LESS THAN ( TO_DAYS('2023-08-01 00:00:00') ),
		PARTITION p202308 VALUES LESS THAN ( TO_DAYS('2023-09-01 00:00:00') ),
		PARTITION p202309 VALUES LESS THAN ( TO_DAYS('2023-10-01 00:00:00') ),
		PARTITION p202310 VALUES LESS THAN ( TO_DAYS('2023-11-01 00:00:00') ),
		PARTITION p202311 VALUES LESS THAN ( TO_DAYS('2023-12-01 00:00:00') ),
		PARTITION p202312 VALUES LESS THAN ( TO_DAYS('2024-01-01 00:00:00') ),
		PARTITION p202401 VALUES LESS THAN ( TO_DAYS('2024-02-01 00:00:00') ),
		PARTITION p202402 VALUES LESS THAN ( TO_DAYS('2024-03-01 00:00:00') ),
		PARTITION p202403 VALUES LESS THAN ( TO_DAYS('2024-04-01 00:00:00') ),
		PARTITION p202404 VALUES LESS THAN ( TO_DAYS('2024-05-01 00:00:00') ),
		PARTITION p202405 VALUES LESS THAN ( TO_DAYS('2024-06-01 00:00:00') ),
		PARTITION p202406 VALUES LESS THAN ( TO_DAYS('2024-07-01 00:00:00') ),
		PARTITION p202407 VALUES LESS THAN ( TO_DAYS('2024-08-01 00:00:00') ),
		PARTITION p202408 VALUES LESS THAN ( TO_DAYS('2024-09-01 00:00:00') ),
		PARTITION p202409 VALUES LESS THAN ( TO_DAYS('2024-10-01 00:00:00') ),
		PARTITION p202410 VALUES LESS THAN ( TO_DAYS('2024-11-01 00:00:00') ),
		PARTITION p202411 VALUES LESS THAN ( TO_DAYS('2024-12-01 00:00:00') ),
		PARTITION p202412 VALUES LESS THAN ( TO_DAYS('2025-01-01 00:00:00') ),
		PARTITION p202501 VALUES LESS THAN ( TO_DAYS('2025-02-01 00:00:00') ),
		PARTITION p202502 VALUES LESS THAN ( TO_DAYS('2025-03-01 00:00:00') ),
		PARTITION p202503 VALUES LESS THAN ( TO_DAYS('2025-04-01 00:00:00') ),
		PARTITION p202504 VALUES LESS THAN ( TO_DAYS('2025-05-01 00:00:00') ),
		PARTITION p202505 VALUES LESS THAN ( TO_DAYS('2025-06-01 00:00:00') ),
		PARTITION p202506 VALUES LESS THAN ( TO_DAYS('2025-07-01 00:00:00') ),
		PARTITION p202507 VALUES LESS THAN ( TO_DAYS('2025-08-01 00:00:00') ),
		PARTITION p202508 VALUES LESS THAN ( TO_DAYS('2025-09-01 00:00:00') ),
		PARTITION p202509 VALUES LESS THAN ( TO_DAYS('2025-10-01 00:00:00') ),
		PARTITION p202510 VALUES LESS THAN ( TO_DAYS('2025-11-01 00:00:00') ),
		PARTITION p202511 VALUES LESS THAN ( TO_DAYS('2025-12-01 00:00:00') ),
		PARTITION p202512 VALUES LESS THAN ( TO_DAYS('2026-01-01 00:00:00') ),
		PARTITION p202601 VALUES LESS THAN ( TO_DAYS('2026-02-01 00:00:00') ),
		PARTITION p202602 VALUES LESS THAN ( TO_DAYS('2026-03-01 00:00:00') ),
		PARTITION p202603 VALUES LESS THAN ( TO_DAYS('2026-04-01 00:00:00') ),
		PARTITION p202604 VALUES LESS THAN ( TO_DAYS('2026-05-01 00:00:00') ),
		PARTITION p202605 VALUES LESS THAN ( TO_DAYS('2026-06-01 00:00:00') ),
		PARTITION p202606 VALUES LESS THAN ( TO_DAYS('2026-07-01 00:00:00') ),
		PARTITION p202607 VALUES LESS THAN ( TO_DAYS('2026-08-01 00:00:00') ),
		PARTITION p202608 VALUES LESS THAN ( TO_DAYS('2026-09-01 00:00:00') ),
		PARTITION p202609 VALUES LESS THAN ( TO_DAYS('2026-10-01 00:00:00') ),
		PARTITION p202610 VALUES LESS THAN ( TO_DAYS('2026-11-01 00:00:00') ),
		PARTITION p202611 VALUES LESS THAN ( TO_DAYS('2026-12-01 00:00:00') ),
		PARTITION p202612 VALUES LESS THAN ( TO_DAYS('2027-01-01 00:00:00') ),
		PARTITION p202701 VALUES LESS THAN ( TO_DAYS('2027-02-01 00:00:00') ),
		PARTITION p202702 VALUES LESS THAN ( TO_DAYS('2027-03-01 00:00:00') ),
		PARTITION p202703 VALUES LESS THAN ( TO_DAYS('2027-04-01 00:00:00') ),
		PARTITION p202704 VALUES LESS THAN ( TO_DAYS('2027-05-01 00:00:00') ),
		PARTITION p202705 VALUES LESS THAN ( TO_DAYS('2027-06-01 00:00:00') ),
		PARTITION p202706 VALUES LESS THAN ( TO_DAYS('2027-07-01 00:00:00') ),
		PARTITION p202707 VALUES LESS THAN ( TO_DAYS('2027-08-01 00:00:00') ),
		PARTITION p202708 VALUES LESS THAN ( TO_DAYS('2027-09-01 00:00:00') ),
		PARTITION p202709 VALUES LESS THAN ( TO_DAYS('2027-10-01 00:00:00') ),
		PARTITION p202710 VALUES LESS THAN ( TO_DAYS('2027-11-01 00:00:00') ),
		PARTITION p202711 VALUES LESS THAN ( TO_DAYS('2027-12-01 00:00:00') ),

---

2 机器分布图

---

3 region 监控

---

4 硬盘实际空间占用

【问题描述】：

1 数据库只有单一的写入操作，同时向两张表中写入数据，每张表写入数据量5000万~7000万，每次写入5000条数据， 表分区后，按照实际业务场景，每个分区的数据量在 100万~200万 之间

2 数据量与之前相同，但是分区后速度下降的很明显，不知道是不是表分区的原因造成的

3 我改如何改善目前面临的问题

[yilong (yi888long)]

1. 从监控看， region数量应该是相同的， 但是leader节点111这台比较多。

2. 从服务器可用空间看，33上在/home目录下是有其他文件占用了大量空间吗？ cd /home 执行 du -sh * | sort -rn 排序判断下，是否有其他空间占用

3. 对于分区表的问题，先让leader均衡，在看下写入是否有改善

4. 下降的明显，有什么对比吗？ 之前是一张大表，写入多少数据大概多久？ 现在分区表以后，写入相同数据，大概多久？

5. 有没有分区前和分区后的监控信息？ 麻烦上传一份我对比下. 采集一小时就可以了 (1)、chrome 安装这个插件https://chrome.google.com/webstore/detail/full-page-screen-capture/fdpohaocaechififmbbbbbknoalclacl

(2)、鼠标焦点置于 Dashboard 上，按 ?可显示所有快捷键，先按 d 再按 E 可将所有 Rows 的 Panels 打开，需等待一段时间待页面加载完成。

(3)、使用这个 full-page-screen-capture 插件进行截屏保存

[mao_siyu (毛巳煜)]

---

之前使用的是 另一套集群，TiDB群集使用的是虚拟机，机械硬盘

这个集群 同样的数据量，同样的表解构，使用了 20个小时导入 5000万的表，27个小时导入完成 7000万的表

Kettle 将 SQL_Server 转数据到 TiDB

【TiDB 版本】： 3.0.9 3.0.5 【问题描述】： 从SQL_Server 2016 数据库中有 48G的数据，要通过Kettle将一类数据转到 TiDB中。 第一次试验： 将48G数据转入到 3.0.9时，数据转入成功，耗时 17小时 第二次试验： 将48G数据转入到 3.0.5时，数据转入失败，耗时 14小时 2020/03/11 02:18:35 - TiDB…

---

只是我现在的集群 20个小时才导入 500万的数据，这个差距太大了

---

上传 三个 tikv 日志

tikv_32.log.gz (1.9 MB)

tikv_33.log.gz (4.0 MB)

tikv_34.log.gz (1.8 MB)

[tiancaiamao (Genius)]

写入是不是没法打散，每次落到一个分区上面的呀？

[mao_siyu (毛巳煜)]

嗯呐，分区是按照日期来分的，所以没有办法打散

这会不会和TiDB默认是，悲观锁有关系？

[mao_siyu (毛巳煜)]

问题原因找到了，不是TiDB的问题，为了解释清楚，我先说明一下具体的工作流程

SQL Server → kettle(数据转换) → TiDB

1 从 SQL Server 数据库 转换数据 到 TiDB 3.0.11

2 使用 工具 kettle

之所以优先怀疑TiDB的问题，是因为我看到IO监控上，已经达到了 80%~90%，所以想换一个集群在试一下是不是TiDB的问题

当我重新写kettle脚本时，发现kettle查询 SQL Server 时特别慢，这时我才突然想到，是不是SQL Server的问题啊，TiDB写入数据慢也有可能是，因为获取数据时的速度慢导致的

结果查看到 SQL Server 那台主机内存时，发现 内存已经 90%以上了，处理速度非常的慢
之前是 92%都红了，释放内存后，在看kettle, 10分钟处理100多万数据转换，这个就符合我的要求了

3 把默认的悲观锁 改成了 乐观锁

---

以下这是6小时的区间测试

---

只是我现在还有些不懂

情况1： SQL Server 慢   TiDB IO 监控最高峰值 大概在 86%

情况2： SQL Server 快   TiDB IO 监控最高峰值 大概在 56%

为什么在TiDB没有达到瓶颈的情况下， SQL Server 的快慢会影响，TiDB IO 监控的数值呢？

[GangShen]

两者似乎是没有什么关联性 从你的描述 SQL Server 慢的情况下，通过 kettle 向 TiDB 写入的数据量也是少的，但是 IO 高，SQL Server 快的情况下，通过 kettle 向 TiDB 写入的数据量也大，但是 IO 低，这个不太符合逻辑。

建议可以新开一个帖子讨论一下这个问题：

1. 可以用 iotop 看下占用 io 是哪个进程在占用
2. 这个 IO 高指的是 tidb tikv pd 哪个组件的 IO 高
3. SQL Server 慢/快的时候，TiDB 集群负载情况是怎么样的？

[mao_siyu (毛巳煜)]

老师 你好， 因为这些问题都是相互关联的所以没有另开帖子，机器部署图在最上面麻烦老师看一下，从 IO这张图上面可以看到，是TiKV节点的IO的问题

之后我又排查了一遍， 两者区别是为在 分区的原因，

第一次： 我使用的是 悲观锁模式 + 按月表分区

第二次： 我使用的是 乐观锁模式 + 没有表分区

所以自己总结一下，以后导入数据还是尽量按照第二种 方式来做，先将要转换的数据放到比较快的TiDB集群中，然后使用 TiDB的 mydumper/loader 工具做数据迁移，不知道这个想法是否可行

[GangShen]

测试能够满足效率以及不会有数据丢失风险，那么方案是可行的