1

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.

Improve this question
3
  • Maybe you have some kind of resource leak? stackoverflow.com/questions/321418/… Commented Oct 31 at 23:31
  • I have tested it with JProfile and we can't detect any leak, all the connections , Statements an ResultSets closes every query. Commented Nov 1 at 8:19
  • Please provide (1) the generated SQL and (2) the SHOW CREATE TABLE for the table(s) involved. Commented Nov 10 at 2:15
Related questions
32
Google Cloud SQL is slow
1
Does TextBox.Clear()'s performance degrade over time?
9
Performance issues with App Engine memcache / ndb.get_multi
Load 4 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.