Slow Insertions

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

Original topic: 插入慢

| username: Hacker_lBbigVlk

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.1.2
[Reproduction Path] Stress Test
[Encountered Problem: Phenomenon and Impact]
Slow insertion
[Resource Configuration]
3 tidb (virtual machine) + 3 pd (virtual machine) + 3 tikv (physical machine)
Virtual machine configuration: 32 CPU + 64G memory + 1T SATA SSD
Physical machine: 48 CPU + 256G memory + 3.5T SATA SSD
[Attachments: Screenshots/Logs/Monitoring]

Table structure:
CREATE TABLE news_bak (
id bigint(20) NOT NULL COMMENT ‘News ID’,
class_id int(11) NOT NULL COMMENT ‘Category’,
dealer_id int(11) NOT NULL COMMENT ‘Dealer ID’,
title varchar(256) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘Title’,
short_title varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘Short Title’,
author varchar(128) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘Author’,
summary varchar(2000) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘Summary’,
publish_time datetime(3) DEFAULT NULL COMMENT ‘Publish Time’,
show400Num tinyint(4) DEFAULT NULL COMMENT ‘Show 400’,
show_auto_call tinyint(4) DEFAULT NULL COMMENT ‘Show Auto Call’,
show_sale_addr tinyint(4) DEFAULT NULL COMMENT ‘Show Sale Address’,
show_map tinyint(4) DEFAULT NULL COMMENT ‘Show Map’,
source int(11) DEFAULT NULL COMMENT ‘News Source: 0 Old Data, 1 Dealer News, 2 Imported News, 3 Manufacturer News Plan, 4 Yiche Soft Article, 5 Nissan Car Buying Assistant System. Records with operation anomalies set isactive to 0, and this field is set to 10000’,
imported_car tinyint(4) DEFAULT NULL COMMENT ‘Imported Car News’,
url varchar(1024) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘URL’,
external_id int(11) DEFAULT NULL COMMENT ‘External ID’,
dealer_account_id int(11) DEFAULT ‘0’ COMMENT ‘Dealer Account ID’,
last_update_dealer_account_id int(11) DEFAULT ‘0’ COMMENT ‘Last Update Dealer Account’,
source_app_id int(11) DEFAULT NULL COMMENT ‘Source APP ID’,
template_news tinyint(4) DEFAULT NULL COMMENT ‘Template News’,
energy_type int(11) DEFAULT NULL COMMENT ‘Energy Type, 1 New Energy News, 0: Hybrid New Energy News’,
status int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Status 1 Under Review, 2 Published (Post-Review: Default to 2; Pre-Review to 1), 3 Rejected, 4 Draft’,
check_status int(11) NOT NULL DEFAULT ‘0’ COMMENT ‘Approval Status 1 Under Review, 2 Approved, 3 Rejected’,
reason varchar(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT ‘Reason’,
uuid varchar(64) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘UUID’,
deleted tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘Deleted 0 Not Deleted, 1 Deleted’,
created_by varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘Created By’,
created_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) COMMENT ‘Record Creation Time’,
updated_by varchar(32) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT ‘Updated By’,
updated_time datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3) COMMENT ‘Record Update Time’,
PRIMARY KEY (id) /*T![clustered_index] CLUSTERED */,
KEY ix_news_dealer_id_createtime_deleted (dealer_id,deleted,created_time),
KEY ixnews_template_news (dealer_id,template_news,deleted,publish_time,check_status,class_id),
KEY ix_news_template_deleted (template_news,deleted,publish_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT=‘News’;

Slow query:
INSERT INTO news_bak ( id, class_id, dealer_id, title, author, summary, publish_time, show400Num, show_auto_call, show_sale_addr, show_map, source, imported_car, url, dealer_account_id, last_update_dealer_account_id, source_app_id, template_news, energy_type, status, check_status, uuid, deleted, created_by, created_time, updated_by, updated_time ) VALUES ( 167514265806, 4, 100056451, ‘Wuling Hongguang MINI GAMEBOY Trendy New Colors Hot Delivery’, ‘100056451’, ‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX, favored by users, orders continue to rise, offline delivery is booming. The new car is paired with trendy new colors, and has advantages such as fast charging and free charging piles, bringing young people a more comfortable travel experience. (GAMEBOY Lime Soda & Cherry Blossom) Trendy new colors are online, meeting users’ new expressions of individuality. Since the launch of GAMEBOY, with more and more users joining the trendy play camp, GAMEBOY has launched two new colors, Lime Soda and Cherry Blossom, to meet the personalized needs of more groups. At the same time, this new launch is sincere, adding colors without increasing the price, gaining the favor of many car owners, and hot delivery continues nationwide. (GAMEBOY Cherry Blossom Owner) To bring users more interactive surprises’, ‘2023-01-16 09:08:00’, 1, 0, 1, 1, 0, 0, ‘/100056451/news/202301/877334359.html’, 1050422, 0, 1, 0, 2, 1, 1, ‘70A9615C-4352-8976-5AB8-37416B498654’, 0, ‘1050422’, ‘2023-01-16 09:08:00’, ‘0’, ‘2023-01-16 09:08:00’ ) ON DUPLICATE KEY UPDATE created_time=NOW(),updated_time=NOW(),summary=‘XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX. The new car is paired with trendy new colors, and has advantages such as fast charging and free charging piles, bringing young people a more comfortable travel experience. (GAMEBOY Lime Soda & Cherry Blossom) Trendy new colors are online, meeting users’ new expressions of individuality. Since the launch of GAMEBOY, with more and more users joining the trendy play camp, GAMEBOY has launched two new colors, Lime Soda and Cherry Blossom, to meet the personalized needs of more groups. At the same time, this new launch is sincere, adding colors without increasing the price, gaining the favor of many car owners, and hot delivery continues nationwide. (GAMEBOY Cherry Blossom Owner) To bring users more interactive surprises’;

Time consumption at each stage:

How to optimize?

| username: leoones | Original post link

Check the Duration monitoring in Performance-Overview.

| username: xfworld | Original post link

Is this insert a slow query?

Has batch insert improved?

| username: 胡杨树旁 | Original post link

It looks like there is a lock conflict based on the screenshot.

| username: tidb菜鸟一只 | Original post link

Check if there are any locks or hot spots. How is the primary key ID of this table generated on the application side? If it is sequentially increasing, it can easily cause hot spots. Additionally, if multiple threads are processing the same row of data simultaneously, it can easily lead to locks.

| username: xingzhenxiang | Original post link

This is an update, not an insert.

| username: ealam_小羽 | Original post link

How is the overall cluster situation? Previously, we also had simple insert statements that were slow when IO was high.
Also, is it only this insert into that is a slow query (if it’s just this one, you might consider batch inserts as suggested above, as too many single inserts are waiting)? What about other slow queries? Sometimes they can affect each other. This SQL might not be slow by itself, but is just being affected.

| username: BraveChen | Original post link

Go check the heatmap to see if there are any hotspots on this table at the time this SQL was executed. If there are hotspots, split the region. Then, analyze the SQL to see if there are many operations on this table at the current time point. It could be that there are conflicts with inserts or updates.