Virtual Threads and MySQL: Unlocking Performance Gains with mysql-connector-J/9.0.0

Ghanshyam Verma
Naukri Engineering
Published in
3 min readAug 5, 2024

In my previous blog post on Virtual Thread Performance Gain for Microservices, we deep dive into the remarkable capabilities of virtual thread. How virtual threads break Little’s law, and how virtual threads are special as they internally provide non-blocking IO with the existing blocking programming paradigm.
But we also experience the blocking behavior of virtual threads with MySQL. Let’s reiterate the use case:

API Performance testing exposed over JPA

@GetMapping
@RequestMapping(value = "/slowDBcall" , method = RequestMethod.GET)
public ResponseEntity<String> dbCall() throws InterruptedException, URISyntaxException {
userRepository.executeSleep();
return ResponseEntity.ok("success");
}
@Repository
public interface UserRepository extends CrudRepository<User, Long> {

@Query(value = "select sleep(1)", nativeQuery = true)
public int executeSleep();
}

We mimic the slowness of the database with a sleep query.

Figure1. Concurrency and tps for database exposed API.

In the above figure, the default Tomcat configuration with 200 traditional platform worker threads throttles at that limit. The virtual thread pool as a worker pool throttles at 16 (although it should handle large concurrency without impacting response time). As there were 16 platform threads and the TPS throttled at that number, it clearly indicates that the blocking of the carrier thread caused this throttling. This observation was validated via the thread dump.

This behavior can be validated official MySQL bug -> https://bugs.mysql.com/bug.php?id=109346

A few lines from bug: 
There is a limition when code is executed in a synchronized block. In a synchronized block the carrier thread will be pinned and won't be available to perform other work.
These synchronized blocks where IO is being done, should be changed and use a ReentrantLock.

With mysql-connector-j/9.0.0 release it is fixed. Release notes: https://dev.mysql.com/doc/relnotes/connector-j/en/news-9-0-0.html

Synchronized blocks in the Connector/J code were replaced with ReentrantLocks. This allows carrier threads to unmount virtual threads when they are waiting on IO operations, making Connector/J virtual-thread friendly.
Github commit changes of same: https://github.com/mysql/mysql-connector-j/commit/00d43c5e8b24f1d516f93eea900b3487c15a489c

Now, it’s time to validate the same and do performance testing:

Changes: Upgrading mysql-connector-java to mysql-connector-j.

From:

 <dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.31</version>
</dependency>

To:

<dependency>
<groupId>com.mysql</groupId>
<artifactId>mysql-connector-j</artifactId>
<version>9.0.0</version>
</dependency>
Concurrency and tps with mysql-connector-j/9.0.0

With upgraded mysql-connector-j/9.0.0 we were able to get a tps of 1000 but throttled on it, and that is because of Hikari max-connection pool (spring.datasource.hikari.maximum-pool-size=1000). And we anyways needed this to protect downstream databases from infinite API bombarding after virtual thread. So Hikari connection pool acts like a protecting bulkhead for MySQL.

We also validate that the connection pool is not blocking, i.e if the max connection is 1000, and if concurrency > 1000, it should not pin the thread and should not impact other API.

Conclusion:
Before MySQL-connector-J/9.0.0 change, we can opt virtual thread only for Rest aggregator application with zero database interaction. However, this upgrade has been a game changer, addressing these issues by replacing synchronized blocks with ReentrantLocks, thereby making the connector more compatible with virtual threads.

--

--