JMeter Groovy SQL Batch Update very slow

Question

Hi I am trying to update multiple MYSQL Tables from data set in DB. I am updating 3 tables using batch if I update a single row it works fine and update records in all 3 tables within 1-2seconds but if I fetch 3 records and update then suddenly it can take between 13-25 seconds. I have tried quite a lot of things somehow just not managed to get it working faster

Here's my code in JSR223 PostProcessor

import groovy.sql.Sql;
import groovy.sql.*
import java.sql.ResultSet;
def dburl = 'jdbc:mysql://${__P(${env}_host)}:${__P(${env}_port)}/${__P(${env}_db)}?verifyServerCertificate=false&requireSSL=false&useSSL=false&useServerPrepStmts=false&rewriteBatchedStatements=true'
def user = '${__P(${env}_username)}'
def password = '${__P(${env}_password)}'
def driver = '${__P(${env}_driver)}'

def sql = Sql.newInstance(dburl, user, password, driver)
sql.connection.autoCommit = false
if(vars.getObject("resultSet").size() != 0) {
    
ArrayList results = vars.getObject("resultSet");
sql.withBatch 
      { stmt ->
          results.each 
      { 
      rs ->
             stmt.addBatch("""UPDATE Table1 SET column1 = '${rs.column1}' 
             WHERE column2 = ${rs.column2}""")
             stmt.addBatch("""UPDATE Table2 SET column1 = '${rs.column1}' 
             WHERE column2 = ${rs.column2}""")
             stmt.addBatch("""UPDATE Table3 SET column1 = '${rs.column1}' 
             WHERE column2 = ${rs.column2}""")

          }
          stmt.executeBatch();        
}
log.info("End")
}
sql.commit()

Thanks

Please provide the generated SQL; I don't know what `addBatch` does. — Rick James, Aug 23 '20 at 14:29
Please give us some hints as to whether `rs.column1` is always the same column and whether `rs.column2` is also constant. — Rick James, Aug 23 '20 at 14:30
It sounds like poor schema design if you need to do the same thing to 3 tables. — Rick James, Aug 23 '20 at 14:31
Hi @RickJames the I am not 100% sure what addBatch does but guessing it append multiple update statements UPDATE Table1 SET column1 = '66768967' WHERE column2 = 6666666; UPDATE Table1 SET column1 = '78578967' WHERE column2 = 7777777; And commit together which is why doesn't have to make multiple trips to DB. Values in column1 and column2 will be different in each iteration from results.each. But in each iteration the values will be same to be updated in all 3 tables if that helps. — Sandeep Sharma, Aug 24 '20 at 01:48
Have you searched for mysql and batch slowness in generell? I found a bug report in which -- at the end -- the mysql driver was found to be the culprit (https://github.com/brettwooldridge/HikariCP/issues/1118). — Felix Schumacher, Aug 24 '20 at 19:48

score 0 · Accepted Answer · answered Aug 25 '20 at 23:38

Thanks all. I know the DB Table structure is not the best but this is my internal requirement to have it like this.

I managed to improve the performance by tweaking couple of my.ini settings

Mainly innodb_buffer_pool_size=2G i don't remember what it was before but after changing to 2G performance improved massively.

Thanks

JMeter Groovy SQL Batch Update very slow

1 Answers1