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
Last activity: Mar 17, 2025, 09:07 AM