[JBoss] Why does fetch performance degrade over time in JBoss with MariaDB and how to properly...

I’m running an application on JBoss 7 connected to a MariaDB database hosted on a separate AWS EC2 instance (c5.9xlarge). The SQL query execution itself is extremely fast (around 57ms), but when fetching large result sets (e.g., 20,000 rows), the fetch performance progressively degrades over hours of uptime. Initially, fetching rows is very fast, but after 6-8 hours, fetching the same query results takes several seconds.

I can see that takes the time in

while (resultSet.next()) {

....


I have try some configurations like

statement = connection.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
if ((withIndex && noches > 1) || noches >= 5){
statement.setFetchSize(Integer.MIN_VALUE);
} else {
statement.setFetchSize(2000);
}




but no luck.

Restarting JBoss instantly restores fast fetch performance.

The instance is a c5.9xlarge instance provides consistent CPU performance with no CPU credits limitation, and network/EBS bandwidth limitations are fixed, so infrastructure throttling is unlikely the cause.

Does anybody have any issue like that? The system can execute a million queries very fast, but the system became unusable.

Continue reading...