0

I have REST API developed in spring + java and hosted on Tomcat server. I have a MySQL database where i store all my data. API makes just simple select from db as descripted below. I failed performance tests because of executing query one by one. The problem is: I run 100 threads to query my API (outside program) and my logs in API shows that each request stops on the execution of the query and waits for it's turn to ask database. It seems like MySQL can do one query at a time. Is it true ? I'm setting connection.setReadOnly(true) - to the transaction should be marked as read only.

Body of a API request that is executed:

Long start = System.currentTimeMillis();
    System.out.println("Starting thread: "+idx);

 String sql = "SELECT * \n" +
         "FROM EXERCISES\n" +
         " WHERE 1 = 1 \n" +
         " AND exercises.STARTTIME  \n" +
         " BETWEEN '2020-08-03 00:00:00.000' \n" +
         " AND '2020-10-10 23:59:59.999'\n" +
         "and exercises.SALEVISIBILITY in ('E','B') \n" +
         " AND exercises.STARTTIME > current_timestamp()\n";

    MysqlDataSource dataSource = new MysqlDataSource();


    try {
        dataSource.setUrl("jdbc:mysql://127.0.0.1:3306/sampleDB?useUnicode=yes&characterEncoding=UTF-8");
        dataSource.setUser("test");
        dataSource.setPassword("test");
        dataSource.setLoginTimeout(1000);
        dataSource.setAllowMultiQueries(true);
        System.out.println("Before connection "+(System.currentTimeMillis()-start));
        Connection connection = dataSource.getConnection();
        System.out.println("After connection "+(System.currentTimeMillis()-start));
        connection.setReadOnly(true);
        System.out.println("After readOnly "+(System.currentTimeMillis()-start));
        Statement stmt = connection.createStatement();
        System.out.println("After stmt "+(System.currentTimeMillis()-start));
        ResultSet rs = stmt.executeQuery(sql);
        System.out.println("After ResultSet "+(System.currentTimeMillis()-start));
        
        System.out.println("End task: "+idx+" time: "+(System.currentTimeMillis()-start));

        if(!rs.isClosed())
        {
            rs.close();
        }

        if(!stmt.isClosed()){
            stmt.close();
        }

        if(!connection.isClosed()){
            connection.close();
        }

    } catch (Exception ex) {
        ex.printStackTrace();
    }

I've put here the MysqlDataSource to make sure each request has it's own connection and also to show my connection parameters (maybe here is something wrong).

And the result of the test is:

... many similar lines above
After connection 5457
After readOnly 5457
After stmt 5457
After connection 5509
After readOnly 5509
After stmt 5509
After connection 5509
After readOnly 5509
After stmt 5509
After connection 5555
After readOnly 5555
After connection 5553
After readOnly 5553
After stmt 5555
After stmt 5553
After connection 5504
After readOnly 5504
After stmt 5504
After connection 5607
After readOnly 5607
After stmt 5607
After ResultSet 7112
End task: 32 time: 7112
After ResultSet 7304
End task: 12 time: 7304
After ResultSet 6702
End task: 59 time: 6702
After ResultSet 6829
End task: 52 time: 6829
After ResultSet 7360
End task: 24 time: 7360
After ResultSet 6857
End task: 56 time: 6857
After ResultSet 7625
End task: 20 time: 7625
After ResultSet 7661
End task: 36 time: 7661
After ResultSet 7822
End task: 15 time: 7822
After ResultSet 7758
End task: 27 time: 7758
After ResultSet 7691
End task: 39 time: 7691
After ResultSet 7651
End task: 64 time: 7651
After ResultSet 8430
End task: 51 time: 8430
After ResultSet 8852
End task: 28 time: 8852
After ResultSet 8052
End task: 75 time: 8052
After ResultSet 8840
End task: 43 time: 8840
After ResultSet 7678
End task: 98 time: 7678
After ResultSet 8985
End task: 40 time: 8985
After ResultSet 8521
End task: 60 time: 8521
After ResultSet 9159
End task: 23 time: 9159
After ResultSet 8836
... and many the same lines below

All the request executes very fast to line: System.out.println("After stmt "+(System.currentTimeMillis()-start)); and then all freeze to be executed one by one on line: ResultSet rs = stmt.executeQuery(sql);

My max connections is set to 3000. I'm using MySQL 5.7.25, single select executes in 300-350 ms. Max threads on tomcat is set to 600.

If anyone could help me - please! I would be very gratefull:)

First edit:

Create table code:

CREATE TABLE `exercises` (
    `REPLOCDB` INT(11) NOT NULL DEFAULT -1,
    `REPLOCID` INT(11) NOT NULL AUTO_INCREMENT,
    `NAME` VARCHAR(100) NOT NULL DEFAULT '',
    `CAPACITY` INT(11) NOT NULL DEFAULT 0,
    `RESERVED` INT(11) NOT NULL DEFAULT 0,
    `STARTTIME` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
    `CLOSETIME` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
    `FKZONREPLOCDB` INT(11) NOT NULL DEFAULT 0,
    `FKZONREPLOCID` INT(11) NOT NULL DEFAULT 0,
    `FKEXGREPLOCDB` INT(11) NOT NULL DEFAULT 0,
    `FKEXGREPLOCID` INT(11) NOT NULL DEFAULT 0,
    `FKHUMREPLOCDB` INT(11) NULL DEFAULT NULL,
    `FKHUMREPLOCID` INT(11) NULL DEFAULT NULL,
    `SALEFROM` TIMESTAMP NULL DEFAULT NULL,
    `ENGLISHNAME` VARCHAR(100) NOT NULL DEFAULT '',
    `ENTRYFROM` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
    `ENTRYTO` TIMESTAMP NOT NULL DEFAULT current_timestamp(),
    `SALETO` TIMESTAMP NULL DEFAULT NULL,
    `STATUS` CHAR(1) NOT NULL DEFAULT 'A',
    `SALEVISIBILITY` CHAR(1) NOT NULL DEFAULT 'A',
    `DESCRIPTION` TEXT NOT NULL,
    `WAITLISTQUANTITY` INT(11) NOT NULL DEFAULT 0,
    `LOCATION` VARCHAR(160) NOT NULL DEFAULT '',
    `EVENTKEEPERTNAME` VARCHAR(160) NOT NULL DEFAULT '',
    `FKASSREPLOCDB` INT(11) NULL DEFAULT NULL,
    `FKASSREPLOCID` INT(11) NULL DEFAULT NULL,
    `ASSORTNAME` VARCHAR(160) NOT NULL DEFAULT '',
    `GROSSPRICE` DECIMAL(18,6) NOT NULL DEFAULT 0.000000,
    `FKCUSREPLOCDB` INT(11) NULL DEFAULT NULL,
    `FKCUSREPLOCID` INT(11) NULL DEFAULT NULL,
    `CUSTOMERNAME` VARCHAR(160) NOT NULL DEFAULT '',
    `CUSTOMERPHONE` VARCHAR(160) NOT NULL DEFAULT '',
    `CAPTION` TEXT NULL DEFAULT NULL,
    `EVENTMESSAGE` TEXT NULL DEFAULT NULL,
    `REMINDERMINUTESBEFORESTART` INT(11) NOT NULL DEFAULT 0,
    `FKDHUREPLOCDB` INT(11) NULL DEFAULT NULL,
    `FKDHUREPLOCID` INT(11) NULL DEFAULT NULL,
    `LNG` DECIMAL(12,9) NULL DEFAULT NULL,
    `LAT` DECIMAL(12,9) NULL DEFAULT NULL,
    PRIMARY KEY (`REPLOCID`, `REPLOCDB`),
    INDEX `FK_EXERCISES2` (`FKEXGREPLOCID`, `FKEXGREPLOCDB`),
    INDEX `FK_EXERCISES3` (`FKHUMREPLOCID`, `FKHUMREPLOCDB`),
    INDEX `FK_EXERCISES_1` (`FKZONREPLOCID`, `FKZONREPLOCDB`),
    INDEX `INDEX_RB_FKEXGREPLOCID_NAME` (`FKEXGREPLOCID`, `NAME`) USING BTREE,
    INDEX `INDEX_RB_SALEVISIBILITY` (`SALEVISIBILITY`) USING BTREE,
    INDEX `INDEX_RB_NAME` (`NAME`) USING BTREE,
    INDEX `EXERCISES_ASSORT_FK` (`FKASSREPLOCID`, `FKASSREPLOCDB`),
    INDEX `EXERCISES_CUSTOMER_FK` (`FKCUSREPLOCID`, `FKCUSREPLOCDB`),
    INDEX `FK_EXERCISES_DEPUTY_HUMAN` (`FKDHUREPLOCID`, `FKDHUREPLOCDB`),
    CONSTRAINT `EXERCISES_ASSORT_FK` FOREIGN KEY (`FKASSREPLOCID`, `FKASSREPLOCDB`) REFERENCES `assorts` (`REPLOCID`, `REPLOCDB`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `EXERCISES_CUSTOMER_FK` FOREIGN KEY (`FKCUSREPLOCID`, `FKCUSREPLOCDB`) REFERENCES `customers` (`REPLOCID`, `REPLOCDB`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `FK_EXERCISES2` FOREIGN KEY (`FKEXGREPLOCID`, `FKEXGREPLOCDB`) REFERENCES `exercisegroup` (`REPLOCID`, `REPLOCDB`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `FK_EXERCISES3` FOREIGN KEY (`FKHUMREPLOCID`, `FKHUMREPLOCDB`) REFERENCES `humans` (`REPLOCID`, `REPLOCDB`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `FK_EXERCISES_1` FOREIGN KEY (`FKZONREPLOCID`, `FKZONREPLOCDB`) REFERENCES `zones` (`REPLOCID`, `REPLOCDB`) ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT `FK_EXERCISES_DEPUTY_HUMAN` FOREIGN KEY (`FKDHUREPLOCID`, `FKDHUREPLOCDB`) REFERENCES `humans` (`REPLOCID`, `REPLOCDB`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=272857
;

Explain to this query:

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "EXERCISES" "ALL"   "INDEX_RB_SALEVISIBILITY"   \N  \N  \N  "117212"    "Using where"

Yes, i know that it should be not blocked and i use locks in different part of the system and it works like a charm but i didnt expect this to happen here. I cant figure out why those select are blocked by each other.

I dont use here a connection pool because i want to show you all my connection properties and make sure each java thread has separate connection. It's just for test purpose.

  • every connection has its own thread that handles its own queries, as long they are blocked by lock. Show us the CREATE TABLE and an EXPLAIN of your query. – nbk Aug 3 at 19:28
  • Looks like your queries block each other, or there's something else that blocks them all. Also, why are you not using a connection pool? – mustaccio Aug 3 at 19:37
  • I've edited my previous post and added create table + explain. I'm not using a connection pool to make sure eatch java thread has it's own connection - it's just for tests. – Dawid Bielarski Aug 4 at 9:04
0

As its turns out - query_cache in mysql is single threaded and that was a problem. Solution to my case was using SQL_NO_CACHE to specific requests and setting properly "innodb_thread_concurrency".

What's little bit weird:

  1. Using MariaDB -> innodb_thread_concurrency = 0 (Auto) works the best way
  2. Using MySQL -> innodb_thread_concurrency = 0 (Auto) kills all cores, innodb_thread_concurrency = 4 works the best way, greater then 4 works the same as auto.
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.