Insert会产生大量TIME_WAIT #2178

New Issue

ha666 · 2022-09-09T08:21:06Z

ha666 commented

2022-09-09 08:21:06 +00:00

系统：CentOS Linux release 7.9.2009 (Core)
golang版本：go version go1.17.11
xorm版本：v1.2.5

数据库ddl：

CREATE TABLE `action_log` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一自增主键',
  `union_id` varchar(32) NOT NULL COMMENT '操作用户id',
  `union_type` smallint(4) NOT NULL COMMENT '操作用户类型 1 管理员 2 普通用户',
  `resource_type` varchar(16) NOT NULL COMMENT '资源类型',
  `resource_id` varchar(32) NOT NULL COMMENT '资源id',
  `action_type` smallint(4) NOT NULL COMMENT '动作类型 1 登录 2 修改 3 新增 4 删除',
  `action_desc` varchar(32) NOT NULL COMMENT '动作描述',
  `effect_desc` varchar(64) NOT NULL COMMENT '影响',
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB AUTO_INCREMENT=1567001 COMMENT='日志表';

代码：

package main

import (
	"fmt"
	"log"
	"sync"
	"time"

	"xorm.io/xorm"
)

type ActionLog struct {
	Id           int64     `xorm:"pk autoincr comment('唯一自增主键') BIGINT(20)"`
	UnionId      string    `xorm:"not null comment('操作用户id') VARCHAR(32)"`
	UnionType    int       `xorm:"not null comment('操作用户类型 1 管理员 2 普通用户') SMALLINT(4)"`
	ResourceType string    `xorm:"not null comment('资源类型') VARCHAR(16)"`
	ResourceId   string    `xorm:"not null comment('资源id') VARCHAR(32)"`
	ActionType   int       `xorm:"not null comment('动作类型 1 登录 2 修改 3 新增 4 删除') SMALLINT(4)"`
	ActionDesc   string    `xorm:"not null comment('动作描述') VARCHAR(32)"`
	EffectDesc   string    `xorm:"not null comment('影响') VARCHAR(64)"`
	CreateTime   time.Time `xorm:"not null default CURRENT_TIMESTAMP comment('创建时间') TIMESTAMP"`
}

var (
	engine *xorm.Engine
)

func initDB() {
	var err error
	engine, err = xorm.NewEngine("mysql", fmt.Sprintf("root:234asdf34t@tcp(10.9.1.1:4000)/%s?charset=utf8&parseTime=true&loc=Asia%%2fShanghai", "aaa_bbb_ccc"))
	if err != nil {
		log.Fatalf("connection failed: %v", err)
		return
	}
	engine.SetMaxIdleConns(4)
	engine.SetMaxOpenConns(60)
	engine.SetConnMaxLifetime(time.Hour)
	if err = engine.Ping(); err != nil {
		log.Fatalf("ping failed: %v", err)
		return
	}
	log.Println("ping success")
}

func main() {
	initDB()
	log.Println("start")
	wg := sync.WaitGroup{}
	for i := 0; i < 30; i++ {
		wg.Add(1)
		go func(ii int) {
			for n := 0; n < 5000; n++ {
				unionId := fmt.Sprintf("union-%s-%05d", time.Now().Format("20060102150405"), ii*10000+n)
				if err := insert(unionId); err != nil {
					log.Fatalf("insert error:%s", err.Error())
					return
				}
			}
			wg.Done()
		}(i)
	}
	wg.Wait()
	log.Println("end")
}

func insert(unionId string) error {
	actionLog := &ActionLog{
		UnionId:      unionId,
		UnionType:    0,
		ResourceType: "a",
		ResourceId:   "b",
		ActionType:   0,
		ActionDesc:   "c",
		EffectDesc:   "d",
		CreateTime:   time.Now(),
	}
	_, err := engine.Insert(actionLog)
	if err != nil {
		return err
	}
	return nil
}

表现：在执行时会产生大量TIME_WAIT

系统：CentOS Linux release 7.9.2009 (Core) golang版本：go version go1.17.11 xorm版本：v1.2.5 数据库ddl： ```sql CREATE TABLE `action_log` ( `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一自增主键', `union_id` varchar(32) NOT NULL COMMENT '操作用户id', `union_type` smallint(4) NOT NULL COMMENT '操作用户类型 1 管理员 2 普通用户', `resource_type` varchar(16) NOT NULL COMMENT '资源类型', `resource_id` varchar(32) NOT NULL COMMENT '资源id', `action_type` smallint(4) NOT NULL COMMENT '动作类型 1 登录 2 修改 3 新增 4 删除', `action_desc` varchar(32) NOT NULL COMMENT '动作描述', `effect_desc` varchar(64) NOT NULL COMMENT '影响', `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB AUTO_INCREMENT=1567001 COMMENT='日志表'; ``` 代码： ```go package main import ( "fmt" "log" "sync" "time" "xorm.io/xorm" ) type ActionLog struct { Id int64 `xorm:"pk autoincr comment('唯一自增主键') BIGINT(20)"` UnionId string `xorm:"not null comment('操作用户id') VARCHAR(32)"` UnionType int `xorm:"not null comment('操作用户类型 1 管理员 2 普通用户') SMALLINT(4)"` ResourceType string `xorm:"not null comment('资源类型') VARCHAR(16)"` ResourceId string `xorm:"not null comment('资源id') VARCHAR(32)"` ActionType int `xorm:"not null comment('动作类型 1 登录 2 修改 3 新增 4 删除') SMALLINT(4)"` ActionDesc string `xorm:"not null comment('动作描述') VARCHAR(32)"` EffectDesc string `xorm:"not null comment('影响') VARCHAR(64)"` CreateTime time.Time `xorm:"not null default CURRENT_TIMESTAMP comment('创建时间') TIMESTAMP"` } var ( engine *xorm.Engine ) func initDB() { var err error engine, err = xorm.NewEngine("mysql", fmt.Sprintf("root:234asdf34t@tcp(10.9.1.1:4000)/%s?charset=utf8&parseTime=true&loc=Asia%%2fShanghai", "aaa_bbb_ccc")) if err != nil { log.Fatalf("connection failed: %v", err) return } engine.SetMaxIdleConns(4) engine.SetMaxOpenConns(60) engine.SetConnMaxLifetime(time.Hour) if err = engine.Ping(); err != nil { log.Fatalf("ping failed: %v", err) return } log.Println("ping success") } func main() { initDB() log.Println("start") wg := sync.WaitGroup{} for i := 0; i < 30; i++ { wg.Add(1) go func(ii int) { for n := 0; n < 5000; n++ { unionId := fmt.Sprintf("union-%s-%05d", time.Now().Format("20060102150405"), ii*10000+n) if err := insert(unionId); err != nil { log.Fatalf("insert error:%s", err.Error()) return } } wg.Done() }(i) } wg.Wait() log.Println("end") } func insert(unionId string) error { actionLog := &ActionLog{ UnionId: unionId, UnionType: 0, ResourceType: "a", ResourceId: "b", ActionType: 0, ActionDesc: "c", EffectDesc: "d", CreateTime: time.Now(), } _, err := engine.Insert(actionLog) if err != nil { return err } return nil } ``` 表现：在执行时会产生大量TIME_WAIT

QQ20220909-162013.png

159 KiB

Sign in to join this conversation.

No Milestone

No Assignees

1 Participants

Notifications

Due Date

The due date is invalid or out of range. Please use the format 'yyyy-mm-dd'.

No due date set.

Dependencies

No dependencies set.

Reference: xorm/xorm#2178