ON DUPLICATE KEY UPDATE 插入慢

TiDBer_vC2lhR9G · 2022 年7 月 18 日 11:47

【 TiDB 使用环境】线上
【 TiDB 版本】5.4
【遇到的问题】

我们业务有批量插入更新的场景，所以用了ON DUPLICATE KEY UPDATE 特性，根据唯一索引不存在则插入，存在则更新。
sql如下：

INSERT INTO qc_report_account_daily (`id`, `advertiser_id`, `out_advertiser_id`, `advertiser_name`, `marketing_goal`, `date`, `stat_cost`, `show_cnt`, `ctr`, `cpm_platform`, `click_cnt`, `pay_order_roi`, `pay_order_count`, `create_order_amount`, `prepay_and_pay_order_roi`, `prepay_order_count`, `prepay_order_amount`, `create_order_count`, `pay_order_amount`, `create_order_roi`, `dy_follow`, `total_play`, `play_duration3s`, `play25_feed_break`, `play50_feed_break`, `play75_feed_break`, `play_over`, `play_over_rate`, `play_duration3s_rate`, `del_flag`, `creator`, `create_time`, `updator`, `update_time`) VALUES (1548995354009075714, 1480189607163617287, 1719743539933197, '二组-城-鸣镝-琳峰小店-KN', 'VIDEO_PROM_GOODS', '2022-07-18 00:00:00.0', 0.0, 0, 0.0, 0.0, 0, 0.0, 0, 0.0, 0.0, 0, 0.0, 0, 0.0, 0.0, 0, 0, 0, 0, 0, 0, 0, 0.0, 0.0, 0, 0, '2022-07-18 19:37:25.995', 0, '2022-07-18 19:37:25.995'), (1548995354009075715, 1480189607163617287, 1719743539933197, '二组-城-鸣镝-琳峰小店-KN', 'LIVE_PROM_GOODS', '2022-07-18 00:00:00.0', 2.95, 124, 0.81, 23.79, 1, 0.0, 0, 0.0, 0.0, 0, 0.0, 0, 0.0, 0.0, 0, 108, 40, 28, 12, 6, 3, 2.78, 0.0, 0, 0, '2022-07-18 19:37:25.995', 0, '2022-07-18 19:37:25.995') ON DUPLICATE KEY UPDATE `advertiser_id` = VALUES(`advertiser_id`), `advertiser_name` = VALUES(`advertiser_name`), `stat_cost` = VALUES(`stat_cost`), `show_cnt` = VALUES(`show_cnt`), `ctr` = VALUES(`ctr`), `cpm_platform` = VALUES(`cpm_platform`), `click_cnt` = VALUES(`click_cnt`), `pay_order_roi` = VALUES(`pay_order_roi`), `pay_order_count` = VALUES(`pay_order_count`), `create_order_amount` = VALUES(`create_order_amount`), `prepay_and_pay_order_roi` = VALUES(`prepay_and_pay_order_roi`), `prepay_order_count` = VALUES(`prepay_order_count`), `prepay_order_amount` = VALUES(`prepay_order_amount`), `create_order_count` = VALUES(`create_order_count`), `pay_order_amount` = VALUES(`pay_order_amount`), `create_order_roi` = VALUES(`create_order_roi`), `dy_follow` = VALUES(`dy_follow`), `total_play` = VALUES(`total_play`), `play_duration3s` = VALUES(`play_duration3s`), `play25_feed_break` = VALUES(`play25_feed_break`), `play50_feed_break` = VALUES(`play50_feed_break`), `play75_feed_break` = VALUES(`play75_feed_break`), `play_over` = VALUES(`play_over`), `play_over_rate` = VALUES(`play_over_rate`), `play_duration3s_rate` = VALUES(`play_duration3s_rate`), `del_flag` = VALUES(`del_flag`), `updator` = VALUES(`updator`), `update_time` = VALUES(`update_time`);

out_advertiser_id + date 两个字段组成唯一索引

执行这个sql基本要350ms。

大佬们麻烦看看是啥问题，谢谢。

机器配置：2台tidb: 16C 32G, 3台tikv: 16C 32G

hey-hoho · 2022 年7 月 18 日 14:38

执行计划发一下

cs58_dba · 2022 年7 月 19 日 00:37

图片里没看到最耗时的阶段啊

Kongdom · 2022 年7 月 19 日 01:23

我们这边也在用这个语法，感觉正常。不过我们update后面没有那么多字段，只更新了几个字段。
还是要发一下执行计划和表健康度才好判断。

wfxxh · 2022 年7 月 19 日 01:38

insert into `qk_chi` . `perio_artical` ( `random_id` , `article_id` , `perio_id` , `issue_id` , `perio_title` , `trans_title` , `doi` , `article_seq` , `article_title` , `en_title` , `authors_name` , `trans_authors` , `authors_unit` , `column_name` , `trans_column` , `publish_year` , `volume` , `issue_num` , `abstract` , `orig_abstract` , `trans_abstract` , `text_segment` , `language` , `trans_language` , `keywords` , `orig_keys` , `trans_keys` , `auto_keys` , `head_words` , `head_table` , `class_code` , `orig_classcode` , `orig_class` , `auto_classcode` , `page_range` , `page_cnt` , `doc_num` , `literature_code` , `received_date` , `revised_date` , `orig_pub_date` , `pro_pub_date` , `abst_webdate` , `full_pubdate` , `abstract_url` , `full_url` , `is_fulltext` , `is_oa` , `service_model` , `cn` , `issn` , `hxkbj_istic` , `hxkbj_pku` , `hxkbj_nd` , `hxkbj_ei` , `hxkbj_sci` , `refdoc_cnt` , `linkdoc_cnt` , `cited_cnt` , `fund_info` , `core_perio` , `is_nstl` , `first_publish` , `source_db` , `first_authors` , `batch_id` , `classname_1` , `classname_2` , `classname_3` , `down_count` , `view_count` , `org_name` , `new_org` , `auth_area` , `auth_city` , `auth_county` , `education_code` , `projectid` , `fundgroupnamezh` , `projectgrantno` , `isthirdservice` ) values ( ... ) on duplicate key update `random_id` = values ( `random_id` ) , `perio_id` = values ( `perio_id` ) , `issue_id` = values ( `issue_id` ) , `perio_title` = values ( `perio_title` ) , `trans_title` = values ( `trans_title` ) , `doi` = values ( `doi` ) , `article_seq` = values ( `article_seq` ) , `article_title` = values ( `article_title` ) , `en_title` = values ( `en_title` ) , `authors_name` = values ( `authors_name` ) , `trans_authors` = values ( `trans_authors` ) , `authors_unit` = values ( `authors_unit` ) , `column_name` = values ( `column_name` ) , `trans_column` = values ( `trans_column` ) , `publish_year` = values ( `publish_year` ) , `volume` = values ( `volume` ) , `issue_num` = values ( `issue_num` ) , `abstract` = values ( `abstract` ) , `orig_abstract` = values ( `orig_abstract` ) , `trans_abstract` = values ( `trans_abstract` ) , `text_segment` = values ( `text_segment` ) , `language` = values ( `language` ) , `trans_language` = values ( `trans_language` ) , `keywords` = values ( `keywords` ) , `orig_keys` = values ( `orig_keys` ) , `trans_keys` = values ( `trans_keys` ) , `auto_keys` = values ( `auto_keys` ) , `head_words` = values ( `head_words` ) , `head_table` = values ( `head_table` ) , `class_code` = values ( `class_code` ) , `orig_classcode` = values ( `orig_classcode` ) , `orig_class` = values ( `orig_class` ) , `auto_classcode` = values ( `auto_classcode` ) , `page_range` = values ( `page_range` ) , `page_cnt` = values ( `page_cnt` ) , `doc_num` = values ( `doc_num` ) , `literature_code` = values ( `literature_code` ) , `received_date` = values ( `received_date` ) , `revised_date` = values ( `revised_date` ) , `orig_pub_date` = values ( `orig_pub_date` ) , `pro_pub_date` = values ( `pro_pub_date` ) , `abst_webdate` = values ( `abst_webdate` ) , `full_pubdate` = values ( `full_pubdate` ) , `abstract_url` = values ( `abstract_url` ) , `full_url` = values ( `full_url` ) , `is_fulltext` = values ( `is_fulltext` ) , `is_oa` = values ( `is_oa` ) , `service_model` = values ( `service_model` ) , `cn` = values ( `cn` ) , `issn` = values ( `issn` ) , `hxkbj_istic` = values ( `hxkbj_istic` ) , `hxkbj_pku` = values ( `hxkbj_pku` ) , `hxkbj_nd` = values ( `hxkbj_nd` ) , `hxkbj_ei` = values ( `hxkbj_ei` ) , `hxkbj_sci` = values ( `hxkbj_sci` ) , `refdoc_cnt` = values ( `refdoc_cnt` ) , `linkdoc_cnt` = values ( `linkdoc_cnt` ) , `cited_cnt` = values ( `cited_cnt` ) , `fund_info` = values ( `fund_info` ) , `core_perio` = values ( `core_perio` ) , `is_nstl` = values ( `is_nstl` ) , `first_publish` = values ( `first_publish` ) , `source_db` = values ( `source_db` ) , `first_authors` = values ( `first_authors` ) , `batch_id` = values ( `batch_id` ) , `classname_1` = values ( `classname_1` ) , `classname_2` = values ( `classname_2` ) , `classname_3` = values(len:4660)

我们也有这样的语句，但是并不慢

Mark · 2022 年7 月 19 日 01:46

看insert的量，大批量操作还要看下是否有锁导致的类似gap lock这种，可以多发一些相关信息出来看看

TiDBer_vC2lhR9G · 2022 年7 月 19 日 02:07

id      	task	estRows	operator info	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	memory 	disk
Insert_1	root	0      	N/A          	0      	time:1.01s, loops:1, prepare: 934.3ms, check_insert: {total_time: 73.1ms, mem_insert_time: 152.9µs, prefetch: 73ms, rpc:{BatchGet:{num_rpc:3, total_time:77.7ms}, total_wait_time: 77ms, scan_detail: {total_process_keys: 4, total_process_keys_size: 886, total_keys: 6, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 63, read_count: 1, read_byte: 4.80 KB}}}}}, commit_txn: {prewrite:2.67ms, region_num:2, write_keys:6, write_byte:887}	6.67 KB	N/A

TiDBer_vC2lhR9G · 2022 年7 月 19 日 02:08

insert 量不多，values 只有2个，表数据大概在200万左右。执行计划发在下面了，麻烦帮忙瞅一眼

TiDBer_vC2lhR9G · 2022 年7 月 19 日 02:10

我也懵啊，确实没看到，但是确实第一个sql执行时间报了3百多毫秒

cs58_dba · 2022 年7 月 19 日 02:12

确实需要看一下表的健康程度

TiDBer_vC2lhR9G · 2022 年7 月 19 日 02:13

INSERT INTO qc_report_account_daily (`id`, `advertiser_id`, `out_advertiser_id`, `advertiser_name`, `marketing_goal`, `date`, `stat_cost`, `show_cnt`, `ctr`, `cpm_platform`, `click_cnt`, `pay_order_roi`, `pay_order_count`, `create_order_amount`, `prepay_and_pay_order_roi`, `prepay_order_count`, `prepay_order_amount`, `create_order_count`, `pay_order_amount`, `create_order_roi`, `dy_follow`, `total_play`, `play_duration3s`, `play25_feed_break`, `play50_feed_break`, `play75_feed_break`, `play_over`, `play_over_rate`, `play_duration3s_rate`, `del_flag`, `creator`, `create_time`, `updator`, `update_time`) VALUES (1549213867491524615, 1503288568338362376, 1726159148436557, '80-至趋-广州卡黛希化妆品有限公司', 'VIDEO_PROM_GOODS', '2022-07-19 00:00:00.0', 0.0, 0, 0.0, 0.0, 0, 0.0, 0, 0.0, 0.0, 0, 0.0, 0, 0.0, 0.0, 0, 0, 0, 0, 0, 0, 0, 0.0, 0.0, 0, 0, '2022-07-19 10:05:43.67', 0, '2022-07-19 10:05:43.67'), (1549213867491524616, 1503288568338362376, 1726159148436557, '80-至趋-广州卡黛希化妆品有限公司', 'LIVE_PROM_GOODS', '2022-07-19 00:00:00.0', 0.0, 0, 0.0, 0.0, 0, 0.0, 0, 0.0, 0.0, 0, 0.0, 0, 0.0, 0.0, 0, 0, 0, 0, 0, 0, 0, 0.0, 0.0, 0, 0, '2022-07-19 10:05:43.67', 0, '2022-07-19 10:05:43.67') ON DUPLICATE KEY UPDATE `advertiser_id` = VALUES(`advertiser_id`), `advertiser_name` = VALUES(`advertiser_name`), `stat_cost` = VALUES(`stat_cost`), `show_cnt` = VALUES(`show_cnt`), `ctr` = VALUES(`ctr`), `cpm_platform` = VALUES(`cpm_platform`), `click_cnt` = VALUES(`click_cnt`), `pay_order_roi` = VALUES(`pay_order_roi`), `pay_order_count` = VALUES(`pay_order_count`), `create_order_amount` = VALUES(`create_order_amount`), `prepay_and_pay_order_roi` = VALUES(`prepay_and_pay_order_roi`), `prepay_order_count` = VALUES(`prepay_order_count`), `prepay_order_amount` = VALUES(`prepay_order_amount`), `create_order_count` = VALUES(`create_order_count`), `pay_order_amount` = VALUES(`pay_order_amount`), `create_order_roi` = VALUES(`create_order_roi`), `dy_follow` = VALUES(`dy_follow`), `total_play` = VALUES(`total_play`), `play_duration3s` = VALUES(`play_duration3s`), `play25_feed_break` = VALUES(`play25_feed_break`), `play50_feed_break` = VALUES(`play50_feed_break`), `play75_feed_break` = VALUES(`play75_feed_break`), `play_over` = VALUES(`play_over`), `play_over_rate` = VALUES(`play_over_rate`), `play_duration3s_rate` = VALUES(`play_duration3s_rate`), `del_flag` = VALUES(`del_flag`), `updator` = VALUES(`updator`), `update_time` = VALUES(`update_time`);

id      	task	estRows	operator info	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                     	memory 	disk
Insert_1	root	0      	N/A          	0      	time:1.01s, loops:1, prepare: 934.3ms, check_insert: {total_time: 73.1ms, mem_insert_time: 152.9µs, prefetch: 73ms, rpc:{BatchGet:{num_rpc:3, total_time:77.7ms}, total_wait_time: 77ms, scan_detail: {total_process_keys: 4, total_process_keys_size: 886, total_keys: 6, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 63, read_count: 1, read_byte: 4.80 KB}}}}}, commit_txn: {prewrite:2.67ms, region_num:2, write_keys:6, write_byte:887}	6.67 KB	N/A

这是另外一条，1秒，我看执行计划，prepare: 934.3ms, 这是问题？

wfxxh · 2022 年7 月 19 日 02:25

你们集群当前的负载状况如何？

ealam_小羽 · 2022 年7 月 19 日 02:28

看下 CPU、内存、IO、网络情况，感觉可能是机器问题

TiDBer_vC2lhR9G · 2022 年7 月 19 日 02:44

表健康度

Kongdom · 2022 年7 月 19 日 02:46

健康度正常，有将主键重复的语句和主键不重复的语句执行对比看看么？

TiDBer_vC2lhR9G · 2022 年7 月 19 日 02:47

感觉负载还行吧

wfxxh · 2022 年7 月 19 日 02:54

兄台你们线上pd就一个节点吗

TiDBer_vC2lhR9G · 2022 年7 月 19 日 05:36

是的，感觉也没啥压力，PD CPU和内存一直很稳定

TiDBer_vC2lhR9G · 2022 年7 月 19 日 07:28

我直接在tibd-server本机执行sql，发现了一个规律，在业务并发插入的时候，大部分还是比较快的，0.02秒左右就能执行完成。但是偶尔出现插入慢

执行过程：

	id      	task	estRows	operator info	actRows	execution info                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  	memory 	disk
	Insert_1	root	0      	N/A          	0      	time:329.9ms, loops:1, prepare: 313.4ms, check_insert: {total_time: 16.5ms, mem_insert_time: 175.2µs, prefetch: 16.3ms, rpc:{BatchGet:{num_rpc:3, total_time:18.3ms}, total_process_time: 1ms, total_wait_time: 17ms, scan_detail: {total_process_keys: 4, total_process_keys_size: 845, total_keys: 6, rocksdb: {delete_skipped_count: 0, key_skipped_count: 0, block: {cache_hit_count: 61, read_count: 1, read_byte: 15.9 KB}}}}}, commit_txn: {prewrite:4.49ms, region_num:2, write_keys:6, write_byte:846}	6.59 KB	N/A

Kongdom · 2022 年7 月 19 日 07:32

如果是偶发性的，不排除是并发导致走update了，或者是选择了错误的执行计划。