使用tidb批量插入数据慢sql

  • 【TiDB 版本】:4.0.4

  • 【问题描述】:我们的业务需要实时批量upsert 插入数据,每条数据执行一次addBatch(), 每三千条执行一次executeBatch()和commit(), 如果出现重复的主键就对记录进行更新,但是job运行2个小时后就开始出现慢sql,
    sql语句如下:

    INSERT into wap_meeting_user(confid, siteid, nodeid, uid, gid, day, inmeetingtag, confname, jointime, leavetime, duration, durationmin, country, username, clientipaddr, gatewayaddr, clienttype, userid,ostype, browsertype, meetingtype, joinmeetingtype, durationinwaitingroom, durationinlobbyroom, confnum, sitename, cbserver, componentaddress, localmeetingdomain, sitever, componentver, iscreator, lasttopcbaddr, tpdevicetype, host, correlationid, mcsreason, joinedclient, sessarrivetime, sharingtag, sharingtag_arrivetime, recordingtag, recordingtag_arrivetime, useremail, joinmethod, servicetype, useros, userbrowser, userjmt, usermode)\nVALUES (?,?,?,.....) ON DUPLICATE KEY UPDATE inmeetingtag=VALUES(inmeetingtag), confname=VALUES(confname),jointime=VALUES(jointime), ..... ;

目前一秒钟会产生2000条新数据,如果单纯写入时间是够的,但运行2个小时后,就会出现慢sql的情况。

  1. 麻烦上传慢日志和这个时间段的 tidb.log
  2. 慢日志发生时间段的监控信息也请上传, over-view , tidb, detail-tikv