Sometimes pods take tasks slower than usually

[nicolask1992]

Hello @kagkarlsson, how are you?

At the company I work for, we decided to adopt your library and are very pleased with the results. However, we occasionally run into some issues.

At specific times of the day, we experience spikes in tasks (between 8 and 10 AM and between 5 and 7 PM), and sometimes (approximately once a week) the processing becomes slow, with the only remedy being a redeployment of the application.

For additional context, our company uses four types of workers/pods to process different kinds of tasks.

Upon reviewing the pods’ logs, we didn’t find anything unusual except for one query that appears most frequently when the problem occurs:

SELECT * FROM scheduled_tasks WHERE picked = ? AND execution_time <= ? AND task_name NOT IN (...) ORDER BY execution_time ASC LIMIT ?

For example, one complete query looks like this:
select * from scheduled_tasks where picked = 0 and execution_time <= '2025-03-24 08:49:53.184045' and task_name not in ('generica....','periodica....','generica....','periodica....','generica....','generica....','periodica....','generica....','generica...','generica....','registrotiempo....','periodica....','periodica....','periodica....','generica....') order by execution_time asc LIMIT 15

Here, “periodica”, “generica”, and “registrotiempo” represent the different workers that process the tasks. I’m not sure if using a NOT IN clause for these tasks is appropriate, but what is strange is that when this query appears at the top of our database, it's because we're in trouble.

I wanted to ask if you have any idea what might be causing this behavior. We have considered that it could be due to database session limits, or perhaps the default values for pollUsingFetchAndLockOnExecute (Defaults: 0, 5, 3.0) might not be optimal for our situation.

To provide further context, between 8 and 10 AM, 200,000 tasks are created (Instant.NOW()), with the peak occurring around 9 AM. The average execution time for each task is 60 ms.

After a redeploy the tasks are executed very fast

Here are some relevant implementation details:

package ...

....

@Slf4j
class SchedulerConfig {

    @Autowired
    DataSource dataSource
    @Autowired
    List<TareaCola> tareas

    private final String colaTask = Holders.getGrailsApplication().config.colaTask
    private final String intervaloBusqueda = Holders.getGrailsApplication().config.intervaloBusquedaTareas
    private final Boolean workerDbScheduler = Holders.getGrailsApplication().config.workerDbScheduler ? Boolean.parseBoolean(Holders.getGrailsApplication().config.workerDbScheduler?.toString()) : false
    private final String cantidadThreadsWorkerDbScheduler = Holders.getGrailsApplication().config.cantidadThreadsWorkerDbScheduler
    private final JacksonSerializer serializer = new JacksonSerializer()
    private final Snowflake snowflake = new Snowflake()

    @Bean
    Scheduler clienteScheduler() {
        Integer intervaloBusquedaTareas = intervaloBusqueda ? intervaloBusqueda as Integer : 5
        Integer cantidadThreadsWorkerDbScheduler = cantidadThreadsWorkerDbScheduler ? cantidadThreadsWorkerDbScheduler as Integer : 10
        List<TareaCola> tareasAInicializar = tareasAInicializar()

        final JdbcLogRepository jdbcLogRepository = jdbcLogRepositoryTaskLog()

        Scheduler scheduler = Scheduler
            .create(dataSource, tareasAInicializar)
            .threads(cantidadThreadsWorkerDbScheduler)
            .heartbeatInterval(Duration.ofSeconds(60))
            .pollingInterval(Duration.ofSeconds(intervaloBusquedaTareas))
            .shutdownMaxWait(Duration.ofMinutes(10))
            .registerShutdownHook()
            .statsRegistry(new LogStatsPlainRegistry(jdbcLogRepository))
            .serializer(serializer)
            .build()

        return scheduler
    }

    @Bean
    JdbcLogRepository jdbcLogRepositoryTaskLog() {
        new CustomJdbcLogRepository(dataSource, serializer, ScheduledTaskLog.NOMBRE_TABLA_TASK_LOG, snowflake)
    }

    @EventListener
    void inicializarProcesamientoScheduler(ApplicationReadyEvent are) {
        boolean esTest = Environment.current == Environment.TEST
        if (workerDbScheduler && (TipoCola.getByValue(colaTask) || esInstanciaAcualEfimero()) && !esTest ) {
            log.info("la aplicacion esta lista, se procede a inicializar la busqueda y procesamiento de tareas")
            clienteScheduler().start()
        }
    }

    private List<TareaCola> tareasAInicializar() {
        if (esInstanciaAcualEfimero()) {
            log.info("se inicializan todas las tareas")
            return tareas
        }
        if (!TipoCola.getByValue(colaTask)) {
            log.info("no es un pod apto para tomar tareas nuevo esquema tasks")
            return Collections.EMPTY_LIST
        }
        List<TareaCola> tareasAInicializar = tareas.findAll { it.tipoCola().value == colaTask }
        log.info("tareas inicializadas: {}", tareasAInicializar)
        return tareasAInicializar
    }

    private boolean esInstanciaAcualEfimero() {
        Ambiente.instanciaActualEsAmbiente(Ambiente.EFIMERO)
    }
}

build.gradle

compile group: 'com.github.kagkarlsson', name: 'db-scheduler-spring-boot-starter', version: '13.0.0'

Thanks in advance

Nicolas Kloster

[kagkarlsson]

Not exactly sure what is happening in your case, but running multiple schedulers with different task-lists against the same table is outside intended use. You should setup different tables for that case (though might not be straight-forward using the spring boot starter).
Considering adding a configuration option to have a Scheduler handle only a given set of tasks, but currently not supported