Sample Header Ad - 728x90

Queries of MySQL from Java are executed serially from multiple threads

0 votes
1 answer
673 views
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.
Asked by Dawid Bielarski (11 rep)
Aug 3, 2020, 06:37 PM
Last activity: Mar 17, 2025, 09:07 AM