因为分区造成了写热点?

【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 硬盘实际空间占用

image image image

【问题描述】:

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

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

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

  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 插件进行截屏保存

1赞

image

image

image


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

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


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


上传 三个 tikv 日志

tikv_32.log.gz (1.9 MB)

tikv_33.log.gz (4.0 MB)

tikv_34.log.gz (1.8 MB)

image

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

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

这会不会和TiDB默认是,悲观锁有关系?

问题原因找到了,不是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%以上了,处理速度非常的慢 image 之前是 92%都红了,释放内存后,在看kettle, 10分钟处理100多万数据转换,这个就符合我的要求了

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


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


只是我现在还有些不懂

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

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

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

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

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

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

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

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

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

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

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

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