OB删除1.5亿数据耗费2小时

发布于：2024-12-22 ⋅ 阅读:(16) ⋅ 点赞:(0)
微信交流群：https://732903.hlcode.cn/?id=NK1fWUR

目录

回顾：mysql是怎么删除数据的？

删除方案

代码实现

执行结果

结论

本篇是实际操作 批量处理数据以及线程池线程数设置 记录学习

背景：有一张用户标签表，存储数据量达4个亿，使用OceanBase存储，由于其中一种标签类型tagType1刷数时逻辑错误，所以需要将该类型数据全部删掉

回顾：mysql是怎么删除数据的？

首先我们得知道mysql是怎么删除数据的？

MySQL 删除操作使用延迟清理的方式。当使用 DELETE 语句删除数据时，MySQL 会将删除的数据标记为已删除，并在需要时将其从磁盘上清理掉。

如果我们要删除id=2这行数据，mysql执行流程如下

所以删除一行数据，需要先查询数据，然后再将数据标记已删除更新

删除方案

批量删除数据，按照以往经验，按照自增ID分页并发删除最快：1. 查询不需要回表；2. 更新可以避免数据库行锁

由于写数据时，是多个标签任务同时写入的，所以需要删掉的tagType1数据并不是连续的，意味着删除语句必须筛选tag_type = "tagType1"

可以查询出tagType1的最小ID和最大ID，然后进行分页查询

代码实现

public static final int endId = 237655461;
public static final int batchSize = 1000;
public static final int startId = 85427001;
public static final int threadNum = 6;

@Override
public void tmpDeleteClueTag() {
    log.info("deleteUserTag start");

    for (long i = startId; i < endId; i += batchSize * threadNum) {
        for (int j = 0; j < threadNum; j++) {
            final long from = i + j * batchSize;
            final long to = from + batchSize;
            executorService.submit(() -> {
                long l = System.currentTimeMillis();
                int result = clueUserTagGateway.deleteUserTag(from, to);
                log.info("deleteUserTag,{},{}->{},res:{},time:{}", Thread.currentThread().getName(), from, to,
                        result, System.currentTimeMillis() - l);
            });
        }
    }

    log.info("deleteUserTag success");
}

// 执行语句
DELETE FROM clue_user_tag where tag_type = 'spu' and id >= #{from} and id &lt; #{to}

// 线程池定义
public class CustomizeThreadPoolConfig {

    public static final int CORE_SIZE = 6;
    public static final int MAX_SIZE = 6;
    public static final int KEEP_ALIVE_TIME = 5;
    public static final int QUEUE_SIZE = 200;

    @Bean(name = "customerTagExecutor", destroyMethod = "shutdown")
    public ExecutorService CustomerTagExecutor() {
        return new TraceableThreadPoolExecutor(CORE_SIZE, MAX_SIZE, KEEP_ALIVE_TIME, TimeUnit.MINUTES,
                new LinkedBlockingQueue<>(QUEUE_SIZE), new CustomizableThreadFactory("pushExecutor-"),
                new ThreadPoolExecutor.CallerRunsPolicy());
    }

    public static class TraceableThreadPoolExecutor extends ThreadPoolExecutor {

        public TraceableThreadPoolExecutor(int corePoolSize, int maximumPoolSize, long keepAliveTime, TimeUnit unit,
                BlockingQueue<Runnable> workQueue, ThreadFactory threadFactory, RejectedExecutionHandler handler) {
            super(corePoolSize, maximumPoolSize, keepAliveTime, unit, workQueue, threadFactory, handler);
        }
    }
}
  • 一次处理1000条，将id拆分开扔给线程池执行删除，可以避免数据库表行锁
  • 使用tag_type标签where删除无伤大雅，执行计划会根据id去优先查询的，查询出来后再从1000条中筛选出有tag_type匹配的情况，内存匹配是非常快的
  • 开启6个线程是因为我们数据平台只有6个线程去执行数据，如果线程数太多会造成数据平台侧资源争抢，一次删除请求会变慢

执行结果

刚开始，并不知道数据平台6个线程的限制，所以设置了20个线程

2024-12-16 15:47:46,478|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:4
2024-12-16 15:47:46,481|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-12|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-12,res:0,time:5
2024-12-16 15:47:46,481|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-14|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-14,res:0,time:3
2024-12-16 15:47:46,482|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:4
2024-12-16 15:47:46,483|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-12|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-12,res:0,time:2
2024-12-16 15:47:46,573|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-19|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-19,res:0,time:3098
2024-12-16 15:47:46,575|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-14|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-14,res:0,time:94
2024-12-16 15:47:46,577|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:95
2024-12-16 15:47:46,579|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-15|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-15,res:0,time:3295
2024-12-16 15:47:46,580|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-19|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-19,res:0,time:7
2024-12-16 15:47:46,581|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-12|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-12,res:0,time:98
2024-12-16 15:47:46,583|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:6
2024-12-16 15:47:46,584|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-15|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-15,res:0,time:5
2024-12-16 15:47:46,584|INFO |796ffea717b93262a1e1fb8799631878||c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=,res:1000,time:3404
2024-12-16 15:47:46,589|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-8|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-8,res:1000,time:3008
2024-12-16 15:47:46,672|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-19|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-19,res:0,time:92
2024-12-16 15:47:46,673|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-14|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-14,res:0,time:98
2024-12-16 15:47:46,675|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-12|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-12,res:0,time:94
2024-12-16 15:47:46,675|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-17|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-17,res:0,time:3390
2024-12-16 15:47:46,676|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:93
2024-12-16 15:47:46,677|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-8|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-8,res:0,time:88
2024-12-16 15:47:46,677|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-14|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-14,res:0,time:4
2024-12-16 15:47:46,679|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-15|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-15,res:0,time:95
2024-12-16 15:47:46,679|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-3,res:0,time:3306
2024-12-16 15:47:46,679|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-5,res:0,time:3372
2024-12-16 15:47:46,680|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-19|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-19,res:0,time:8
2024-12-16 15:47:46,680|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-12|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-12,res:0,time:5
2024-12-16 15:47:46,681|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:5
2024-12-16 15:47:46,682|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-17|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-17,res:0,time:7
2024-12-16 15:47:46,682|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-8|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-8,res:0,time:5
2024-12-16 15:47:46,774|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-3,res:0,time:95
2024-12-16 15:47:46,776|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-5,res:0,time:97
2024-12-16 15:47:46,778|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-19|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-19,res:0,time:98
2024-12-16 15:47:46,780|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-12|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-12,res:0,time:100
2024-12-16 15:47:46,781|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:100
2024-12-16 15:47:46,782|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-17|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-17,res:0,time:100
2024-12-16 15:47:46,784|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-8|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-8,res:0,time:102
2024-12-16 15:47:46,785|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-3,res:0,time:11
2024-12-16 15:47:47,079|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-5,res:0,time:303
2024-12-16 15:47:47,082|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-12|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-12,res:0,time:302
2024-12-16 15:47:47,082|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-19|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-19,res:0,time:304
2024-12-16 15:47:47,083|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-17|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-17,res:0,time:301
2024-12-16 15:47:47,084|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:303
2024-12-16 15:47:47,084|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-1|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-1,res:1000,time:3307
2024-12-16 15:47:47,085|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-3,res:0,time:300
2024-12-16 15:47:47,085|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-8|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-8,res:0,time:301
2024-12-16 15:47:47,184|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-5,res:0,time:105
2024-12-16 15:47:47,185|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-2|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-2,res:1000,time:3409
2024-12-16 15:47:47,185|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-12|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-12,res:0,time:103
2024-12-16 15:47:47,187|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:103
2024-12-16 15:47:47,188|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-17|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-17,res:0,time:105
2024-12-16 15:47:47,189|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-19|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-19,res:0,time:107
2024-12-16 15:47:47,273|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-3,res:0,time:188
2024-12-16 15:47:47,275|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-1|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-1,res:0,time:191
2024-12-16 15:47:47,277|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-8|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-8,res:0,time:192
2024-12-16 15:47:47,280|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-5,res:0,time:96
2024-12-16 15:47:47,281|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-12|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-12,res:0,time:96
2024-12-16 15:47:47,283|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-2|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-2,res:0,time:98
2024-12-16 15:47:47,285|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:98
2024-12-16 15:47:47,286|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-17|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-17,res:0,time:98
2024-12-16 15:47:47,288|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-19|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-19,res:0,time:99
2024-12-16 15:47:47,289|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-3,res:0,time:15
2024-12-16 15:47:47,374|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-1|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-1,res:0,time:99
2024-12-16 15:47:47,375|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-8|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-8,res:0,time:98
2024-12-16 15:47:47,485|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-2|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-2,res:0,time:202
2024-12-16 15:47:47,573|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-12|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-12,res:0,time:292
2024-12-16 15:47:47,575|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-5,res:0,time:295
2024-12-16 15:47:47,577|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-6,res:0,time:292
2024-12-16 15:47:47,578|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-17|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-17,res:0,time:292
2024-12-16 15:47:47,580|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-19|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-19,res:0,time:291
2024-12-16 15:47:47,581|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-3,res:0,time:292
2024-12-16 15:47:47,583|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-1|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-1,res:0,time:209
2024-12-16 15:47:47,584|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-8|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-8,res:0,time:209
2024-12-16 15:47:47,883|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-20|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-20,res:1000,time:3405
2024-12-16 15:47:47,987|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-8|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-8,res:0,time:403
2024-12-16 15:47:48,881|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-13|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-13,res:1000,time:3194
2024-12-16 15:47:49,184|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-7|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-7,res:1000,time:3211
2024-12-16 15:47:49,278|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-16|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-16,res:1000,time:3193
2024-12-16 15:47:49,278|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-4|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-4,res:0,time:3390
2024-12-16 15:47:49,286|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-11|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-11,res:1000,time:3312
2024-12-16 15:47:49,484|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-9|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-9,res:0,time:3112
2024-12-16 15:47:49,488|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-16|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-16,res:0,time:210
2024-12-16 15:47:49,580|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-10|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-10,res:1000,time:3205
2024-12-16 15:47:49,584|INFO |796ffea717b93262a1e1fb8799631878|pushExecutor-18|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,threadName=pushExecutor-18,res:1000,time:3204

观察日志可见，刚开始执行，是个位数毫秒，耗时在逐步增加 ，询问数据平台得知，仅有6个线程在处理数据，所以造成资源争抢，产生请求堆积。

优化：修改线程池数量为6个（最终方案）

执行时间段：17:04 ~ 19:00

刚开始执行日志

2024-12-16 17:04:08,526|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-2|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-2,85452001->85453001,res:0,time:5
2024-12-16 17:04:08,526|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-6,85454001->85455001,res:0,time:3
2024-12-16 17:04:08,526|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-5,85455001->85456001,res:0,time:3
2024-12-16 17:04:08,527|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-1|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-1,85457001->85458001,res:0,time:3
2024-12-16 17:04:08,527|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-4|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-4,85456001->85457001,res:0,time:3
2024-12-16 17:04:08,579|INFO |5e7bda947e8bce91351c806dcbe821da||c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,,85633001->85634001,res:0,time:75
2024-12-16 17:04:08,986|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-3,85458001->85459001,res:0,time:461
2024-12-16 17:04:09,011|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-5,85461001->85462001,res:0,time:485
2024-12-16 17:04:09,079|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-2|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-2,85459001->85460001,res:0,time:553
2024-12-16 17:04:09,083|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-2|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-2,85466001->85467001,res:0,time:4
2024-12-16 17:04:09,084|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-6,85460001->85461001,res:0,time:558
2024-12-16 17:04:09,089|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-1|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-1,85462001->85463001,res:0,time:562
2024-12-16 17:04:09,102|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-4|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-4,85463001->85464001,res:0,time:575
2024-12-16 17:04:09,582|INFO |5e7bda947e8bce91351c806dcbe821da||c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,,85665001->85666001,res:1000,time:1003
2024-12-16 17:04:09,893|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-3,85464001->85465001,res:1000,time:907
2024-12-16 17:04:09,972|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-5,85465001->85466001,res:1000,time:961
2024-12-16 17:04:10,002|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-1|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-1,85469001->85470001,res:1000,time:913
2024-12-16 17:04:10,006|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-2|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-2,85467001->85468001,res:1000,time:923
2024-12-16 17:04:10,009|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-4|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-4,85470001->85471001,res:1000,time:907
2024-12-16 17:04:10,011|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-6,85468001->85469001,res:1000,time:927
2024-12-16 17:04:10,518|INFO |5e7bda947e8bce91351c806dcbe821da||c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,,85673001->85674001,res:1000,time:936

执行一个多小时后日志

2024-12-16 18:59:58,076|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-6,134119001->134120001,res:0,time:2
2024-12-16 18:59:58,190|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-3,134115001->134116001,res:0,time:966
2024-12-16 18:59:58,192|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-2|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-2,134114001->134115001,res:0,time:974
2024-12-16 18:59:58,217|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-5,134116001->134117001,res:0,time:918
2024-12-16 18:59:58,299|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-1|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-1,134117001->134118001,res:0,time:887
2024-12-16 18:59:58,300|INFO |5e7bda947e8bce91351c806dcbe821da||c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,,134117001->134118001,res:0,time:684
2024-12-16 18:59:58,540|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-4|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-4,134118001->134119001,res:1000,time:925
2024-12-16 18:59:58,629|INFO |5e7bda947e8bce91351c806dcbe821da||c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,,134205001->134206001,res:1000,time:1006
2024-12-16 18:59:58,632|INFO |5e7bda947e8bce91351c806dcbe821da||c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,,134207001->134208001,res:0,time:3
2024-12-16 18:59:59,000|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-6,134120001->134121001,res:0,time:924
2024-12-16 18:59:59,183|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-2|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-2,134122001->134123001,res:1000,time:991
2024-12-16 18:59:59,185|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-3,134121001->134122001,res:1000,time:995
2024-12-16 18:59:59,188|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-3|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-3,134128001->134129001,res:0,time:3
2024-12-16 18:59:59,217|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-5|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-5,134123001->134124001,res:1000,time:1000
2024-12-16 18:59:59,220|INFO |5e7bda947e8bce91351c806dcbe821da||c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,,134124001->134125001,res:0,time:920
2024-12-16 18:59:59,273|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-1|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-1,134124001->134125001,res:1000,time:974
2024-12-16 18:59:59,573|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-4|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-4,134125001->134126001,res:1000,time:1033
2024-12-16 18:59:59,632|INFO |5e7bda947e8bce91351c806dcbe821da||c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,,134208001->134209001,res:1000,time:1000
2024-12-16 18:59:59,975|INFO |5e7bda947e8bce91351c806dcbe821da|pushExecutor-6|c.x.n.x.c.s.impl.CustomerClueServiceImpl|deleteUserTag,pushExecutor-6,134126001->134127001,res:1000,time:975

可以看到，请求耗时基本是平稳到 1s左右的，说明数据平台侧完全可以处理当前的并发量，资源是足够的

结论

由于我们对接OB没有监控平台，所以观察不了，本例通过最简单的日志打印观察执行情况

  • 如果处理耗时较一开始慢慢增大，表示并发量太大，处理不过来了，请求开始堆积了，那么就要减小并发量；
  • 如果处理耗时很平稳一直很快，可以尝试增加并发量观察，直到达到最大并发量即可。

本例没有其他操作，只是一直循环执行删除操作，那么业务并发量就与数据侧的处理线程持平上下浮动，对标CPU密集型任务；如果业务还需要查询第三方之类的操作再去删除，那么业务并发量就要先大设置观察，对标IO密集型任务

网站公告

今日签到

点亮在社区的每一天
去签到

热门文章

最新发布