Spring XD internal database logging

Question

I am looking to diminish what is kept in the Spring-XD internal database, because after 500 000 entries in total it overload the capacity of the server; too much IO from the logging of the many jobs execution which are scheduled every hour, minute, or second

Therefore, I have to truncate, as below, every 4 days:

truncate springxd.batch_step_execution_context;
truncate springxd.batch_step_execution;
truncate springxd.batch_job_execution_context;
truncate springxd.batch_job_execution_params;
truncate springxd.batch_job_execution;

Any suggestion? is there any springxd configuration which will persist only 2 days?

Thanks

Answer 1

You may want to write a job that does that. There are available tasklets that you may want to reuse modifying little. You can check here

Following code is from article - disclaimer im not the author but we have done similar stuff.

    import javax.sql.DataSource;
    import java.text.DateFormat;
    import java.text.SimpleDateFormat;
    import java.util.Date;
    import org.apache.commons.lang3.time.DateUtils;
    import org.apache.log4j.Logger;
    import org.springframework.batch.core.StepContribution;
    import org.springframework.batch.core.scope.context.ChunkContext;
    import org.springframework.batch.core.step.tasklet.Tasklet;
    import org.springframework.batch.repeat.RepeatStatus;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.jdbc.core.JdbcTemplate;

    public class DeleteAllArchives implements Tasklet{

        private static Logger log = Logger.getLogger(DeleteAllArchives.class);

        private static final String SQL_DELETE_BATCH_STEP_EXECUTION_CONTEXT = “DELETE FROM BATCH_STEP_EXECUTION_CONTEXT WHERE STEP_EXECUTION_ID IN (SELECT STEP_EXECUTION_ID FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM  BATCH_JOB_EXECUTION where CREATE_TIME < ?))”;
        private static final String SQL_DELETE_BATCH_STEP_EXECUTION = “DELETE FROM BATCH_STEP_EXECUTION WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION where CREATE_TIME < ?)”;
        private static final String SQL_DELETE_BATCH_JOB_EXECUTION_CONTEXT = “DELETE FROM BATCH_JOB_EXECUTION_CONTEXT WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM  BATCH_JOB_EXECUTION where CREATE_TIME < ?)”;
        private static final String SQL_DELETE_BATCH_JOB_EXECUTION_PARAMS = “DELETE FROM BATCH_JOB_EXECUTION_PARAMS WHERE JOB_EXECUTION_ID IN (SELECT JOB_EXECUTION_ID FROM BATCH_JOB_EXECUTION where CREATE_TIME < ?)”;
        private static final String SQL_DELETE_BATCH_JOB_EXECUTION = “DELETE FROM BATCH_JOB_EXECUTION where CREATE_TIME < ?”;
        private static final String SQL_DELETE_BATCH_JOB_INSTANCE = “DELETE FROM BATCH_JOB_INSTANCE WHERE JOB_INSTANCE_ID NOT IN (SELECT JOB_INSTANCE_ID FROM BATCH_JOB_EXECUTION)”;
        private static final Integer DEFAULT_RETENTION_MONTH = 1;
        private Integer historicRetentionMonth = DEFAULT_RETENTION_MONTH;

        @Autowired
        @Qualifier(“dataSource”)
        private DataSource dataSource;

        @Override
        public RepeatStatus execute(StepContribution contribution, ChunkContext context)
        throws Exception {

            deleteEmailLetterArchive(contribution, new JdbcTemplate(dataSource));
            return RepeatStatus.FINISHED;
        }

        private void deleteEmailLetterArchive(StepContribution contribution, JdbcTemplate template) {

            int totalCount = 0;
            Date date = DateUtils.addMonths(new Date(), -historicRetentionMonth);
            DateFormat df = new SimpleDateFormat();
            log.info(“Remove the Spring Batch history before the { ” + df.format(date));

            int rowCount = template.update(SQL_DELETE_BATCH_STEP_EXECUTION_CONTEXT, date);
            log.info(“Deleted rows number from the BATCH_STEP_EXECUTION_CONTEXT table: { ” + rowCount + ” }”);
            totalCount += rowCount;

            rowCount = template.update(SQL_DELETE_BATCH_STEP_EXECUTION, date);
            log.info(“Deleted rows number from the BATCH_STEP_EXECUTION table: { ” + rowCount + ” }”);
            totalCount += rowCount;

            rowCount = template.update(SQL_DELETE_BATCH_JOB_EXECUTION_CONTEXT, date);
            log.info(“Deleted rows number from the BATCH_JOB_EXECUTION_CONTEXT table: { ” + rowCount + ” }”);
            totalCount += rowCount;

            rowCount = template.update(SQL_DELETE_BATCH_JOB_EXECUTION_PARAMS, date);
            log.info(“Deleted rows number from the BATCH_JOB_EXECUTION_PARAMS table: { ” + rowCount + ” }”);
            totalCount += rowCount;

            rowCount = template.update(SQL_DELETE_BATCH_JOB_EXECUTION, date);
            log.info(“Deleted rows number from the BATCH_JOB_EXECUTION table: { ” + rowCount + ” }”);
            totalCount += rowCount;

            rowCount = template.update(SQL_DELETE_BATCH_JOB_INSTANCE);
            log.info(“Deleted rows number from the BATCH_JOB_INSTANCE table: { ” + rowCount + ” }”);
            totalCount += rowCount;

            contribution.incrementWriteCount(totalCount);
        }
    }