Mysql – Queries of MySQL from Java are executed serially from multiple threads

javaMySQL

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.

Best Answer

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:

Using MariaDB -> innodb_thread_concurrency = 0 (Auto) works the best way
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.

Related Solutions

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Mysql – Getting a Deadlock,

The system is starting to complain about the number of comments.

I'd be interested in seeing the statements as they are sent to the server - i.e. the fully generated SQL statements, i.e. with real values, not ? parameter place holders.

The statement that appears to be causing the issue is this one.

INSERT INTO `inventoryitems` VALUES (DEFAULT, 4, '595', null, 5390001, 5, 1, 1, '', -1, 0, -1, '')

Now, inventory items has a FOREIGN KEY constraint

CONSTRAINT FK_inventoryitems_1 FOREIGN KEY (characterid) REFERENCES characters (id) ON DELETE CASCADE

What is the result of the following query

SELECT COUNT(*) FROM characters WHERE characters.id = 595;

The values here are also of interest.

ps = con.prepareStatement("INSERT INTO `inventoryitems` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
pse = con.prepareStatement("INSERT INTO `inventoryequipment` VALUES (DEFAULT, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

[EDIT]

I'm starting to get some insight into what may be going on. You appear to be using some sort of framework for your database access (that's the reason why your SQL has '595' for an integer insert instead of 595.

It's possible that the transaction isolation level is being set by the framework to something other than read committed (see here). Try and ensure that your transactions use read committed - set the transaction level. Try adding this code to yours after the line

Connection con = DatabaseConnection.getConnection(); con.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED); // Added line.

Also, check out this and the rest of the thread.

[EDIT] in response to "How do i check it? , can you guide me?"

What I meant was for you to add the line above to your code and rerun your system to the point where you got your original error. Do you still get an error? If you do, then is it different to the original error? If it is different, then post the error text here. HTH.

Could you take a dump of your database and put it online? You can anonymise anything sensitive. Also, zip up your Java code and I'll run that here against your schema. My Java's a bit rusty, but running it on a different system might give us some clues! :-)

[EDIT] in response to OP's comment "can you give me an example of indexes [code example] ?".

See here (you can also use the KEY keyword as a synonym for INDEX - see the docco here. You should add to your inventoryitems CREATE TABLE statement lines such as

INDEX `ii_type_ix` (type), 
INDEX `ii_account_id_ix` (accountid),

As for changing your Java code - I'm not 100% sure, but the problem lies with the database so your efforts should be concentrated there. Where you can change the code is in varying the order of your queries (but keeping the original functionality).

Best Answer

Related Solutions

Mysql – Finding rows for a specified date range

Mysql – Getting a Deadlock,

Related Question