Question 19515 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Multithreaded Spring Boot Application leaves MySQL processes in sleep state

I have a Spring Boot Application for cron tasks. One of those tasks is executed as a group of several threads executed asynchronously. Each thread inserts ~200K data in MySQL compatible AWS Aurora Serverless DB. Before the task is finished, all the threads are being shutdown upon their completion, but the corresponding processes in MySQL remain in the process list with ‘sleep’ status. Here is the shorten sample:

@SpringBootApplication
@EnableScheduling
public class ScheduledServiceApp implements SchedulingConfigurer {
    private static final int POOL_SIZE = 20;

    public static void main(String[] args) {
       SpringApplication.run(ScheduledServiceApp.class, args);
    }


    @Override
    public void configureTasks(ScheduledTaskRegistrar scheduledTaskRegistrar) {
        ThreadPoolTaskScheduler threadPoolTaskScheduler = new ThreadPoolTaskScheduler();

        threadPoolTaskScheduler.setPoolSize(POOL_SIZE);
        threadPoolTaskScheduler.setThreadNamePrefix("scheduled-task-pool-");
        threadPoolTaskScheduler.initialize();

        scheduledTaskRegistrar.setTaskScheduler(threadPoolTaskScheduler);
     }
}


@Component
public class ScheduledTask {

    // .....

    @Scheduled(cron = "${task.cron.expression}", zone="UTC")
    // @Modifying(clearAutomatically = true, flushAutomatically = true)
    @Transactional
    public void runTask() {
        try {
            // .....

            ThreadPoolExecutor executor = (ThreadPoolExecutor) Executors.newFixedThreadPool(THREAD_COUNT);

            for (int i = 0; i < THREAD_COUNT; ++i) {
                Runnable task = new TaskThread();
                executor.execute(task);
            }

            executor.shutdown();

            while (!executor.awaitTermination(1, TimeUnit.SECONDS)) {
                // wait until the processes in the executor will be terminated
            }

            log.info("Task is completed successfully");
        } catch (Exception e) {
            // log the error
        }
    }
}


public class TaskThread implements Runnable {
    // .....

    public void run() {
        dataService.insertData(/* parameters */);
    }
}

Each separate thread execution may last for ~30min because of the huge amount of data to be processed and after the task’s completion, in MySQL console I still see the corresponding processes which are in ‘sleep’ mode and which are being terminated only after the timeout value reaches.

As you can see in the code, @Modifying annotation with its parameters didn’t help either. Also, I don’t want to decrease the timeout value since that may affect on queries running directly in MySQL as well. But I would like to terminate those processes since they affect on application’s overall performance.

So my questions is: is there a solution which will make the MySQL processes to be terminated instead of being slept when my task is finished?

Update: Forgot to mention that the application is completely container managed and running under Tomcat. JDBC/ODBC bridge for MySQL (Aurora Serverless MySQL 5.7 compatible).

Tags:

3

Answers


  1. 0

    all the threads are being shutdown upon their completion, but the corresponding processes in MySQL remain in the process list with ‘sleep’ status

    Spring boot uses HikariCP as a Default DB Connection Pool. When the transactions are closed, Connections are not closed. Rather, DB connections are returned to the Connection Pool. So, MySQL must be showing those idle connections.

    So my questions is: is there a solution which will make the MySQL processes to be terminated instead of being slept when my task is finished?

    If the connection pool is not used, then all the connections are opened when required and closed when transactions are completed. MySQL won’t show those sleep processes then. Please try setting below to disable connection pooling –

    spring.datasource.type=org.springframework.jdbc.datasource.SimpleDriverDataSource

    But, if you disable DB Connection Pool, there are downsides to it –

    1. MySQL will refuse connections when max connection limit on MySQL is exhausted.
    2. You won’t get those benefits of connection pooling like connection reuse and a cap on maximum open DB Connections by the application.

    I suggest you find how many concurrent connections are needed by your application. Then fine tune HikariCP connection pool property spring.datasource.hikari.maximum-pool-size so that connection pool is not impacting application performance.

    Login or Signup to reply.
  2. 0

    By default, Spring Boot apps use HikariCP for connection pooling. So basically the HikariCP is in charge of DB connection lifecycle. So when one of your threads tries to execute any DB-related action, like read or write data to DB, it asks HikariCP to give it a connection, as the connection creation process is pretty slow and requires lots of resources, HikariCP by default keeps the connection in the pool for a configured time. So you will need to refer to the maxLifetime and idleTimeout properties from HikariCP Documentation.

    And as you are on spring boot, you may control the HikariCP from you application.properties file with the following config options.

    spring.datasource.hikari.minimum-idle=XXXX
spring.datasource.hikari.max-lifetime=XXX
    Login or Signup to reply.
  3. 0

    You can try using these connection pool properties, and set the following in your application.properties file.

    spring.datasource.hikari.idle-timeout=XXXXX
spring.datasource.hikari.maximum-pool-size=XX
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search