Vertx - Database update query takes few minutes

Question

I have a very simple update database operation in my Java Vertx application but for some reason it takes araround 3 minutes. I have attached the console output showing the time. Can somebody please help me to debug why is it so.

2022-07-19 11:54:32.039+0430 [vert.x-eventloop-thread-1] DEBUG com.job.PrintJobHandler -  onSuccess for startPrintJob()   Job Completed Successfully!!!!
2022-07-19 11:54:32.039+0430 [vert.x-eventloop-thread-1] DEBUG com.dao.JobDao - Inside setJobFinishedDate() 14
2022-07-19 11:54:32.039+0430 [vert.x-eventloop-thread-1] DEBUG com.dao.JobDao - Tuple for Parent UID values: [2022-07-19 11:54:32,14]
2022-07-19 11:57:45.733+0430 [vert.x-eventloop-thread-1] DEBUG com.dao.JobDao - Updated job table's finished_on column!!!!!!!!!!!!!!!!!!!!!!!!! 
2022-07-19 11:57:45.733+0430 [vert.x-eventloop-thread-1] DEBUG com.job.PrintJobHandler - Job Completed Successfully!!!!
2022-07-19 11:57:45.734+0430 [vert.x-eventloop-thread-1] DEBUG com.job.PrintJobHandler -  onSuccess for startPrintJob()   Server sent msg --- Finished printing Job ID::::: 14

code:

startPrintJob(jobID, context)
            .onFailure(error -> {
                LOG.debug("startTest() Failed: ", error);
            })
            .onSuccess(res -> { 
                LOG.debug(" onSuccess for startPrintJob()   " + res);
                jobDao.setJobFinishedDate(jobID)
                .onSuccess(result -> {
                    LOG.debug(res);
                    String updatedStatus = "Server sent msg --- Finished printing Job ID::::: "+jobID;
                    
                    context.response()
                    .setStatusCode(200)
                    .putHeader("content-type", "application/json; charset=utf-8")
                    .end(Json.encodePrettily(updatedStatus));
                    
                    LOG.debug(" onSuccess for startPrintJob()   " + updatedStatus);
                });             
            });

public Future<String> setJobFinishedDate(int jobID) {
        
        Promise<String> promise = Promise.promise();
        
        LOG.debug("Inside setJobFinishedDate() "+jobID);
        LocalDateTime issuedAt = LocalDateTime.now();
        DateTimeFormatter format = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");      
        Tuple batch = Tuple.of(issuedAt.format(format), jobID);
        LOG.debug("Tuple for Parent UID values: " + batch.deepToString());

        // Execute the query
        db.preparedQuery("UPDATE job SET finished_on=? WHERE id=?;").execute(batch, res -> {
            if (res.succeeded()) {
                LOG.debug("Updated job table's finished_on column!!!!!!!!!!!!!!!!!!!!!!!!! ");
                promise.complete("Updated job table's finished_on column!!");
            } else {
                System.out.println("Batch failed for UPDATE job table's finished_on column!!" + res.cause());
            }
        });
        return promise.future();
    }

The problem look like not vertex related, but rather database related. Please, provide more info about the db engine you are using. You should analyze the query, maybe there is missing index on id column? Or maybe you have index on finished_on and 3 trillions rows and it took time to update the index? If you are using mysql there is https://dev.mysql.com/doc/refman/8.0/en/explain.html statement that will show you all details. You you use different engine, there should be something similar — Mateusz, Jul 27 '22 at 20:40
I am using MySQL database and there is only 2 rows in the table. The table is also indexed. Please see my comments I added below in @Asad Awadia commentss — ZAJ, Jul 28 '22 at 08:20

score 1 · Accepted Answer · answered Jul 28 '22 at 03:40

1

Are you implementing a job queue pattern in a DB?

I think your row is locked and that is why the update hangs for multiple minutes while the job that is currently processing that ID has the row lock

What does the startPrintJob do? Does it return after the job is finished or does it async launch the job and return immediately ?

Another lead could be that the DB pool is exhausted and it is hanging on getting a connection since all are already in use

answered Jul 28 '22 at 03:40

Asad Awadia

1,417
2
9
15

Here is the complete class https://ln5.sync.com/dl/c4c366f40/i9fz3wyg-gmztybrd-ppwphsia-ir8c5wd8 – ZAJ Jul 28 '22 at 08:58
>>What does the startPrintJob do? it is the main business logic where I get the job related inoramtion from db and start printing labels. The labels can be of varies sizes for e.g Now I am testing with 8000 rows of label. Then I have a SockJS communication going on in the background(which I have issue with it in the sense it is not sending message to the client) also for every label that is printed I send sockjs message to the client telling it how many labels have printed. – ZAJ Jul 28 '22 at 09:08
The sockjs issue that I have is the messages are being send after I send the response to the client in the onSuccess. Which is weird and just dont know what is causing this behavior – ZAJ Jul 28 '22 at 09:09

Vertx - Database update query takes few minutes

1 Answers1