oceanbase 4.3.5 insert插入慢

[AntTech_BWTMBC]

【 使用环境 】生产环境
【 OB or 其他组件 】oceanbase 社区版
【 使用版本 】4.3.5版本
【问题描述】insert插入慢，表是频繁的插入 更新， 因此设置的buffer表 级别为extreme 和super级别，发现插入耗时高。

和历史合并的情况

[淇铭]

哪些表插入慢呢？是所有的表 都插入慢么？能根据插入的语句 查一下gv$ob_sql_audit信息么？取一下ASH报告

[耶一耶]

666

[AntTech_BWTMBC]

ash.zip (23.9 KB)

[AntTech_BWTMBC]

之前sql_audit信息冲没了，找不到昨天的了。

[longzh85]

确实看上去比较慢，还没有用过buffer表了

[淇铭]

从ash报告 来看 label_product 的 id 列使用自增时，高并发 INSERT 都需要向 OceanBase 的 GAIS（Global Auto Increment Service） 申请 ID。所有会话竞争同一把 GAIS latch，形成严重串行化。 全局自增 ID 服务锁竞争：latch: auto increment GAIS lock wait 占 21.78%

查一下表定义信息
SHOW CREATE TABLE label_product;

[AntTech_BWTMBC]

昨天这个表的设置表属性为extreme，现在降级为queuing
CREATE TABLE label_product (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘自增id’,
product_id bigint(20) NOT NULL
label_id bigint(20) NOT NULL,
label_value varchar(500) DEFAULT ‘’ ,
is_delete tinyint(3) DEFAULT ‘0’,
create_time bigint(20) NOT NULL,
PRIMARY KEY (id, product_id),
UNIQUE KEY uniq_productid_labelid (product_id, label_id),
KEY idx_labelid (label_id)
) AUTO_INCREMENT = 2080174649953751553 DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC
partition by key(product_id)
(partition p0,
partition p1,
partition p2,
partition p3,
partition p4,
partition p5,
partition p6,
partition p7,
partition p8,
partition p9,
partition p10,
partition p11,
partition p12,
partition p13,
partition p14,
partition p15,
partition p16,
partition p17,
partition p18,
partition p19,
partition p20,
partition p21,
partition p22,
partition p23,
partition p24,
partition p25,
partition p26,
partition p27,
partition p28,
partition p29,
partition p30,
partition p31,
partition p32,
partition p33,
partition p34,
partition p35,
partition p36,
partition p37,
partition p38,
partition p39,
partition p40,
partition p41,
partition p42,
partition p43,
partition p44,
partition p45,
partition p46,
partition p47,
partition p48,
partition p49,
partition p50,
partition p51,
partition p52,
partition p53,
partition p54,
partition p55,
partition p56,
partition p57,
partition p58,
partition p59,
partition p60,
partition p61,
partition p62,
partition p63,
partition p64,
partition p65,
partition p66,
partition p67,
partition p68,
partition p69,
partition p70,
partition p71,
partition p72,
partition p73,
partition p74,
partition p75,
partition p76,
partition p77,
partition p78,
partition p79,
partition p80,
partition p81,
partition p82,
partition p83,
partition p84,
partition p85,
partition p86,
partition p87,
partition p88,
partition p89,
partition p90,
partition p91,
partition p92,
partition p93,
partition p94,
partition p95,
partition p96,
partition p97,
partition p98,
partition p99,
partition p100,
partition p101,
partition p102,
partition p103,
partition p104,
partition p105,
partition p106,
partition p107,
partition p108,
partition p109,
partition p110,
partition p111,
partition p112,
partition p113,
partition p114,
partition p115,
partition p116,
partition p117,
partition p118,
partition p119,
partition p120,
partition p121,
partition p122,
partition p123,
partition p124,
partition p125,
partition p126,
partition p127,
partition p128,
partition p129,
partition p130,
partition p131,
partition p132,
partition p133,
partition p134,
partition p135,
partition p136,
partition p137,
partition p138,
partition p139,
partition p140,
partition p141,
partition p142,
partition p143,
partition p144,
partition p145,
partition p146,
partition p147,
partition p148,
partition p149,
partition p150,
partition p151,
partition p152,
partition p153,
partition p154,
partition p155,
partition p156,
partition p157,
partition p158,
partition p159,
partition p160,
partition p161,
partition p162,
partition p163,
partition p164,
partition p165,
partition p166,
partition p167,
partition p168,
partition p169,
partition p170,
partition p171,
partition p172,
partition p173,
partition p174,
partition p175,
partition p176,
partition p177,
partition p178,
partition p179,
partition p180,
partition p181,
partition p182,
partition p183,
partition p184,
partition p185,
partition p186,
partition p187,
partition p188,
partition p189,
partition p190,
partition p191,
partition p192,
partition p193,
partition p194,
partition p195,
partition p196,
partition p197,
partition p198,
partition p199,
partition p200,
partition p201,
partition p202,
partition p203,
partition p204,
partition p205,
partition p206,
partition p207,
partition p208,
partition p209,
partition p210,
partition p211,
partition p212,
partition p213,
partition p214,
partition p215,
partition p216,
partition p217,
partition p218,
partition p219,
partition p220,
partition p221,
partition p222,
partition p223,
partition p224,
partition p225,
partition p226,
partition p227,
partition p228,
partition p229,
partition p230,
partition p231,
partition p232,
partition p233,
partition p234,
partition p235,
partition p236,
partition p237,
partition p238,
partition p239,
partition p240,
partition p241,
partition p242,
partition p243,
partition p244,
partition p245,
partition p246,
partition p247,
partition p248,
partition p249,
partition p250,
partition p251,
partition p252,
partition p253,
partition p254,
partition p255)

[AntTech_BWTMBC]

这是昨晚改完表模式后，今天上午的ash报告
ash_改表模式后.zip (24.7 KB)

[淇铭]

@AntTech_BWTMBC

昨天这个表的设置表属性为extreme，现在降级为queuing

看着latch: auto increment GAIS lock wait 降低了 之前是主要因素 不过还有瓶颈 不是主要因素了 现在是tx commiting wait事务提交等待成为新的主要瓶颈。
建议是：

1. 批量提交：减少单条 INSERT 单独 commit 的频率
2. 合并写操作：同一 product_id 的 DELETE + INSERT 放在同一事务，减少多次 commit
3. 检查 autocommit：确认应用是否每条 SQL 都隐式提交

queuing 缓解但未消除，仍建议：

• 应用侧生成 id，去掉 AUTO_INCREMENT 依赖
• 或增大 AUTO_INCREMENT_CACHE_SIZE

[AntTech_YC3PB6]

学习

[AntTech_BKPSAR]

666

[AntTech_6PMM8I]

666

[AntTech_8QELKG]

有没有好的思路

[Amber_www]

关于partition的问题，id确实是一个重要的考虑因素。在实际应用中，我发现label方法通常更有效。

[obtp]

学习了

[zwm8168]

Partition问题比较复杂的。