I'm using Quarkus + Panache Reactive + MySQL for my project. Since it's reactive, my connection pool uses Vertx SQL Pool. I've also enabled metrics to monitor the system status.
I have a scheduled task like this:
@WithTransaction @Scheduled(cron = "0 * * * * ?") public Uni<Void> saveData() { return xxxxClient.getData(appId, appSecret).onItem() .transform(CommonResponse::data).onItem() .transform(i -> { List<xxx> list = new ArrayList<>(); for (xxxxVo item : i) { list.add(voToXxx(item)); } return list; }).onItem().transformToUni( list -> xxxRepository.persist(list) ).replaceWithVoid(); } Where xxxxClient.getData requests an external API and saves the returned data to the database. It has been running stably for a long time.
Problem
However, recently this external API encountered an issue where a single request returned nearly 100,000 records, causing my database table to accumulate over 30 million records, resulting in severe performance problems.
At this point, Quarkus keeps outputting logs:
io.vertx.sqlclient.ClosedConnectionException: Failed to read any response from the server, the underlying connection may have been lost unexpectedly.
When I checked the monitoring data on Prometheus, I found:
- sql_pool_active: 20 (my configured max)
- sql_pool_waiting: 0
- sql_pool_idle: 20
- sql_pool_ratio: 0%
- sql_pool_queue_size: continuously rising since the API issue started, reaching 60,000+ Even after clearing the corresponding database table, the problem persists.
Analysis
My leader told me the issue is that the database request packet is too large, exceeding MySQL's default max_allowed_packet configuration. When MySQL receives half the data, it resets the connection. However, Quarkus's connection pool is unaware of this, and subsequent connections continue using the already-reset MySQL connections.
I thought of using the validation.query configuration available in Spring + Hibernate, which executes an SQL statement before querying to verify the connection is valid. I checked the documentation and found that JDBC drivers support this, but the reactive driver doesn't seem to have this configuration.
How can I validate the connection before executing queries in the reactive driver? Or is my problem not caused by this?
xxxRepository.persist(List)the default implementation that comes with Panache repository?public class xxxRepository implements PanacheRepository<xxx> {}