mysql batch insert 慢的问题排查过程

1，发现线上的queue入库很慢

因为我发现

2024-11-14 12:24:22.897 [] [Thread-47] INFO  com.hp.nova.runner.Task3NewStructRedisQueueRunner.run [93] : eisrq.getType()======3

.....

.....

.....

2024-11-14 12:25:57.653 [] [Thread-47] INFO  com.hp.nova.runner.Task3NewStructRedisQueueRunner.run [93] : eisrq.getType()======3

这个线程Task3NewStructRedisQueueRunner    负责batch insert struct表2000多条用了35多秒

一开始怀疑数据库问题，但是发现数据库服务器cpu很低，io也正常，并且show processlist也没有发现blocking的insert语句

然后我就自己写了个batch insert的语句直接在mysql batch insert也很快 不到1秒

 

后来发现了问题，不是mysql的问题，而是java服务的问题，在如下2个info logo之间打印了很多其他job的info，我猜测肯定是这个机器ec2是8核然后

 

2024-11-14 12:24:22.897 [] [Thread-47] INFO  com.hp.nova.runner.Task3NewStructRedisQueueRunner.run [93] : eisrq.getType()======3

.....

.....

.....

2024-11-14 12:25:57.653 [] [Thread-47] INFO  com.hp.nova.runner.Task3NewStructRedisQueueRunner.run [93] : eisrq.getType()======3

 

Task3NewStructRedisQueueRunner刚开始执行的时候就被其他的job thread抢占了，导致Task3NewStructRedisQueueRunner  这个thread被blocking了30多秒

后来终于发现了问题所在，如下所示本来应该一天执行一次的job被错误的配置成了2分钟一次，这个任务的计算量还挺大的，导致8核的cpu一直被它抢占，居然就这样线上2分钟一次

的大量抢占cpu还没有很慢。。。

taskAutoCompleteScheduleJob:
        switch: true
        cron: 0 */2 * * * ?

 

改成

 

taskAutoCompleteScheduleJob:
        switch: true
        cron: 0 0 1 * * ?

 

20241122 终于发现问题

 

终于找到了问题的原因，root cause也不是job的cpu被抢占问题。而是sharding jdbc的问题，后来发现如果用sharding jdbc进行路由分表的insert 就会非常慢

5-50秒  但是如果直接把batch insert的sql放在mysql client里面执行就只需要0.5 秒

也就是说sharding jdbc进行分表路由的时候非常慢，但是非常奇怪的是其他的delete update 包括其他dao里面也分表了的insert也很快，我怀疑是多个po然后多个dao都在用不同的mybatis batch insert进行插入才有这个问题。

比如说下面这个就很快

instanceStructService.addEInstStructBatchTask3(batchInstanceStructList); 

 

但是下面这个就很慢50秒，他们的不同之处就是不同的mybatis dao 然后po也不一样，但是sql语句基本上一样

instanceStructService.addEInstStructBatchUsingEinstDtoTask3(batchStructList);



后来用下面的方法先简单粗暴的解决了问题，也就是说这个方法不走sharding jdbc

import com.zaxxer.hikari.HikariDataSource;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.jdbc.core.JdbcTemplate;
import javax.sql.DataSource;

@Configuration
public class NonShardedDataSourceConfig {

    // Define the non-sharded datasource based on the YML configuration
    @Bean(name = "nonShardedDataSource")
    @ConfigurationProperties(prefix = "spring.datasource-nonshard.non-sharded")
    public DataSource nonShardedDataSource() {
        return new HikariDataSource();
    }

    // Create a JdbcTemplate bean using the non-sharded datasource
    @Bean(name = "nonShardedJdbcTemplate")
    public JdbcTemplate nonShardedJdbcTemplate(@Qualifier("nonShardedDataSource") DataSource nonShardedDataSource) {
        return new JdbcTemplate(nonShardedDataSource);
    }
}

@Service
public class JdbcTask3StructServiceImpl implements JdbcTask3StructService {

    private static final Logger logger = LoggerFactory.getLogger(JdbcTask3StructServiceImpl.class);

    @Autowired
    @Qualifier("nonShardedJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @Override
    public int addEInstStructBatchUsingEinstDtoTask3Jdbc(List<Task3EInstanceStruct> instanceList) {

        StringBuilder sql = new StringBuilder("INSERT INTO e_task3instance_struct_");
        if(instanceList!=null&&instanceList.size()>0)
        {
            logger.info("taskcode======{}",instanceList.get(0).getTaskCode());
            int tabIndex=PartitionByMurmurHash.hashTaskCode(instanceList.get(0).getTaskCode(),20);
            logger.info("tabIndex======{}",tabIndex);

            if(tabIndex>0&&tabIndex<=20) {
                sql.append(tabIndex);
            }
            else {
                return 0;
            }

        }





        sql.append("""
         (
            struct_id, task_code, instance_code, case_code, case_ver, 
            plan_code, plan_ver, order_num, plan_casever_guid, comments,
            score, fail_cycle, total_cycle, attended, unattended, 
            result, createdate, createuser, updatedate, updateuser, 
            obsflag, comments_tl, is_template, task_version, blocktype,
            customatt, customuatt, runcycle, is_atc_test
        ) VALUES 
    """);


        logger.info("sql======{}",sql.toString());

        // Append placeholders for each row
        for (int i = 0; i < instanceList.size(); i++) {
            sql.append("(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            if (i < instanceList.size() - 1) {
                sql.append(", ");
            }
        }

        // Flatten all parameters into an array
        Object[] params = instanceList.stream()
                .flatMap(struct -> Stream.of(
                        struct.getStructId(),
                        struct.getTaskCode(),
                        struct.getInstanceCode(),
                        struct.getCaseCode(),
                        struct.getCaseVer(),
                        struct.getPlanCode(),
                        struct.getPlanVer(),
                        struct.getOrderNum(),
                        struct.getPlanCaseVerGuid(),
                        struct.getComments(),
                        struct.getScore(),
                        struct.getFailCycle(),
                        struct.getTotalCycle(),
                        struct.getAttended(),
                        struct.getUnattended(),
                        struct.getResult(),
                        struct.getCreatedate() ,
                        struct.getCreateuser(),
                        struct.getUpdatedate() ,
                        struct.getUpdateuser(),
                        struct.getObsflag(),
                        struct.getCommentsTl(),
                        struct.getIsTemplate(),
                        struct.getTaskVersion(),
                        struct.getBlocktype(),
                        struct.getCustomatt(),
                        struct.getCustomuatt(),
                        struct.getRuncycle(),
                        struct.getIsAtcTest()
                ))
                .toArray();

        // Execute the batch insert
        return jdbcTemplate.update(sql.toString(), params);





    }
}