Java MySQL query thread keeps increasing memory usage

Question

I have a method called QueryTest that queries a MySQL database over the network to get a table called output. From the start this method, when run, steadily keeps eating more memory until it eventually runs into an out of memory error. I have removed everything I can think of even putting the query in a loop with a 500ms time out but still no luck.

The program is meant to be run on a low memory device so I cannot have it continue increasing in memory usage.

Please see my code below. This class is called by another that only contains new Thread (new QueryTest ()).start () and has no other effect.

EDIT 1: If I add System.gc (); just before Thread.sleep it adds about 7mb to my program but the problem goes away. I know that method can only hint at the Garbage Collector so would this be a reliable workaround?

// Add the main package
package DBTest;


// Import List
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;


public class QueryTest implements Runnable
{
    // Constant Objects
    public static Connection connection = null;
    ResultSet rs;
    ResultSetMetaData rsmd;
    Statement s;


    // Variables
    int portA = 0;
    int size = 0;
    String outputs [][] = new String [0][5];
    boolean isRunning = true;
    boolean isPaused = false;



    // The main method for starting the thread
    public void run ()
    {       
        // Try to connect to the database and query the updates
        try
        {       
            // Load the database driver
            Class.forName("com.mysql.jdbc.Driver").newInstance();       

            // Connect to the database
            connection = DriverManager.getConnection ("jdbc:mysql://192.168.1.103/noah", "root", "0004e5dcb6a");
            System.out.println ("Connection Made");

            // let the loop run while the thread is allowed to run
            while (isRunning)
            {       
                // Query the database if the thread is not paused
                if (!isPaused)
                {
                    // Create a prepared statement
                    s = connection.createStatement ();

                    // Execute the query and store the results
                    rs =  s.executeQuery ("SELECT * FROM outputs");

                    // Get the result set meta data
                    rsmd = rs.getMetaData();

                    // Set the result set to the last row
                    rs.last();

                    // Get the last row number
                    size = rs.getRow();

                    // Set the result set to the last row
                    rs.first();

                    /*// Get port A values
                    for (int a = 0; a < 8; a ++)
                    {
                        // Check if the output is active
                        if (Integer.parseInt(outputs [a][4]) == 1)
                        {
                            // Add to the port
                            portA = portA + Integer.parseInt (outputs [a][3]);
                            System.out.println (portA);
                        }
                    }*/

                    // Set the value of portA
                    portA = 0;
                }

                // Let the thread sleep
                Thread.sleep (500);
            }   

            // Close the connection
            connection.close();
            System.out.println ("Connection Closed");   
        }

        // Catch a error
        catch (Exception queryDatabaseErr)
        {
            System.out.println (queryDatabaseErr);
        }
    }
}

Community · Accepted Answer · 2017-05-23 10:32:10Z

1

Close the statements, and free the result sets when you dont need that. At the and of the loop.

    ...
    rs.close();  //free result set
    s.close();   //close the statement
    Thread.sleep(500)
}

I had the same propbem in my java applications. Sometimes the solution i write below does not help. But after i began to use other Database Library: http://sourceforge.net/projects/c3p0/, the memory usage decreesed radically, and the application worked faster. It use persistent connection, so i recommend it only console or desktop applications, not with Java Servlet Pages.

I recommend to read this topic: Java MySQL JDBC Memory Leak ;)

After you installed or added the C3P0 lib to your project just import:

import com.mchange.v2.c3p0.ComboPooledDataSource;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

After this you does not have to change your code (as i remember but you should read the manual of this lib http://www.mchange.com/projects/c3p0/apidocs/com/mchange/v2/c3p0/ComboPooledDataSource.html)

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Mar 30, 2013 at 12:23

Kovge

2,0191 gold badge14 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Griffin Over a year ago

After trying so many things I completely forgot to put that back but it still makes no difference. There still remains a rather constant memory increase.

Kovge Over a year ago

Sometimes that is not enough. stackoverflow.com/questions/5765990/java-mysql-jdbc-memory-leak <- here i red that there might be a bug in java db library, and there is a memory leak, so after this i started to use the lib i wrote you in the answer.

Griffin Over a year ago

Here is something funny. If I hint at the Garbage Collector using System.gc (); just before I execute sleep it adds about 7mb to my memory usage but the problem goes away. The problem with this is I know gc (); can only hint at the Garbage Collector so I don't know how reliable this will be in the long run.

Kovge Over a year ago

Yes the Garbage Colelctor could help you, but in long run i have not experiances too. I use the C3P0 lib in long run, /5-6,000,000 sql queryes/ And the speed was 10x faster , and the memory usage was constat. The other thing, if you debug your code in your IDE, or if you run code from commandline/console the memory usage results may be different. I always test memory usage in console, because there can be bugs in IDE debuggers too, what can cause memory usage increasement.

Griffin Over a year ago

Thanks. Tried the lib as mentioned and it made a nice noticeable difference.

Collectives™ on Stack Overflow

Java MySQL query thread keeps increasing memory usage

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related